Excel 常用技巧培训

Excel常用技巧培訓主講:黃威文日期:2023-01-16第1页培訓大綱Excel小貼士Excel常用快捷鍵Excel常用公式Excel常見錯誤說明利用Excel製作柏拉圖Excel注意事項2第2页Excel小貼士1.身份证号码输入(设置单元格属性为“文本”或者前面加’)2.分数输入(直接输1/3時EXCEL默以为1月3日，应写成“0空格1/3”)3.平方、立方输入(右键选择单元格格式，选上标、下标)3第3页Excel常用快捷鍵迅速达到列头或尾Ctrl+↓或↑

迅速达到行头或尾Ctrl+←或→

列单元格迅速选用Ctrl+Shift+↓或↑行单元格迅速选用Ctrl+Shift+←或→

×乘号AIt+215÷除号Alt+247

自用输入求和公式Alt+=

在单元格中输入回车Alt+Enter输入目前日期Ctrl+;输入目前时间Ctrl+Shift+;插入批注Shift+F2打开“插入函数”对话框Shift+F3批量錄入相同內容Ctrl+Enter相对引用与绝对引用转换F4修改单元格F24第4页Excel常用公式IF(logical_test,value_if_true,value_if_false)檢查是否符合某一條件，且若為true，則傳回某值，若為false則傳回另一值。Excel最多支持七層套嵌函數.提醒:有多少個if套嵌,則對應有多少個收尾括號.5第5页Excel常用公式OR/AND/NOTOR(logical1,logical2,...)假如有任何一個引數邏輯值為TRUE，即傳回TRUE；唯有所有引數邏輯值均為FALSE時，才會傳回FALSE。AND(logical1,logical2,...)假如它所有引數都是TRUE，則傳回TRUE。假如有一個或多個引數是FALSE，則傳回FALSE。NOT(logical)將引數之數值给予反轉，Logical為可產生TRUE或FALSE結果任何數值或關係式。備註：以上函數常與其他函數一併使用。6第6页Excel常用公式COUNT/COUNTA/COUNTBLANKCOUNT(value1,value2,...)計算包括數字及所有引數里具有數值資料儲存格數目.COUNTA(value1,value2,...)計算具有非空白資料且其值位於引數清單中儲存格數目.COUNTBLANK(range)計算指定範圍內空白儲存格數目.7第7页Excel常用公式SUMIF/COUNTIFSUMIF(range,criteria,sum_range)計算所有符合指定條件儲存格總和.COUNTIF(range,criteria)計算一範圍內符合指定條件儲存格數目.8第8页Excel常用公式SUM/SUBTOTALSUM(number1,number2,...)傳回儲存格範圍中所有數值總和.SUBTOTAL(function_num,ref1,ref2,...)傳回清單或資料庫內之小計.Function_num包括隱藏值Function_num

忽视隱藏值函數1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP9第9页Excel常用公式SMALL/LARGE&MIN/MAXSMALL(array,k)傳回資料組中第k小值LARGE(array,k)傳回資料組中第k大值MIN(number1,number2,...)傳回引數串列中最小值MAX(number1,number2,...)傳回一組數值中最大值RANK(number,ref,order)傳回某數字在某串列數字中之順序,即數字相對於清單中其他數值大小10第10页Excel常用公式ROUND/ROUNDUP/ROUNDDOWNROUND(number,num_digits)依所指定位數，將數字四捨五入ROUNDUP(number,num_digits)將數值做無條件進位ROUNDDOWN(number,num_digits)將數值作無條件捨去數值保存位數ROUNDROUNDUPROUNDDOWN3.2034376.907777763.1415933.1423.1423.141-3.141591-3.1-3.2-3.131415.2654-231400315003140011第11页Excel常用公式ABS/SIGN/TRUNC/INTABS(number)傳回數字絕對值,就是不含符號數字SIGN(number)傳回代表數字正負號代碼：1代表正數，0代表數字為零，-1代表負數TRUNC(number,num_digits)將數字中小數部份捨去而成為整數INT(number)傳回無條件捨去後整數值12第12页Excel常用公式LEFT/RIGHT/MIDLEFT(text,num_chars)從文字串第一個字元傳回特定長度之間所有字元RIGHT(text,num_chars)從文字串最後一個字元傳回特定長度之間所有字元MID(text,start_num,num_chars)傳回從文字串中某個起始位置到指定長度之間字元字元組指定長度LEFTRIGHTMID(4,3)ABCDEFG4ABCDDEFGDEF13第13页Excel常見錯誤說明#DIV/0!当数字被零(0)除时，出现错误。#N/A没有可用数值．一般情况是，您将数值直接输入某些工作表单元格．虽然这些单元格中会包括数据，但目前尚无数据．引用这些单元格公式将返回而不会计算数值．#NAME?当MicrosoftExcel未识别公式中文本时，出现错误。#NULL!指定两个区域不相交.#NUM!公式或函数中使用无效数字值时，出现这种错误。#REF!公式引用了无效单元格．#####当列不够宽，或者使用了负日期或负时间时，出现错误。#VALUE!当使用参数或操作数类型错误时，出现这种错误14第14页利用Excel製作柏拉圖Style

款號Line

組別Quantity

數量Percent

占總數比Accumulation

累計百分比TA0587114&1515,41825.70%25.70%TA06448811,02318.37%44.08%TA063145&68,53614.23%58.30%TA0633077,25612.10%70.40%TA0643215,1698.62%79.02%TA0635422&233,5215.87%84.89%TA06348253,1065.18%90.06%TA06326162,6034.34%94.40%TA06249182,1003.50%97.90%TA0660431,2582.10%100.00%TOTAL1059,990100.00%100.00%第一步：將需統計款，按遞減順序進行排列，并計算累計百分比。15第15页利用Excel製作柏拉圖第二步：選定款號+數量+累計百分比三欄，并插入“自訂類型”中“雙軸折線圖加直條圖”16第16页利用Excel製作柏拉圖第三步：建立初步圖表時，直接點擊“下一步”，直至“完成”。17第17页利用Excel製作柏拉圖上圖為雙軸折線圖加直條圖建立完成后效果，需作進一步調整。18第18页利用Excel製作柏拉圖第四步：對左側座標軸進行刻度調整，將最大值調為“數量總數”。19第19页利用Excel製作柏拉圖第五步：對右側座標軸進行刻度調整，將最大值調為“1”。20第20页利用Excel製作柏拉圖第六步：對直條柱進行調整，將其間距調整為“0”，并分色區別。21第21页利用Excel製作柏拉圖第七步：對折線圖進行圖表類別調整為“XY散佈圖”，并選擇“帶有折線XY散佈圖”。22第22页利用Excel製作柏拉圖第八步：在圖表中右擊彈出菜單中，選擇“圖表選項”。23第23页利用Excel製作柏拉圖第九步：在圖表選項“座標軸”欄目中，點選中“數值X軸”。24第24页利用Excel製作柏拉圖第十步：右擊上方座標軸進行格式調整。25第25页利用Excel製作柏拉圖第十一步：將座標軸格式刻度最大值調整為“款數總數”.26第26页利用Excel製作柏拉圖第十二步：將座標軸格式圖樣刻度選項都調整為“無”。27第27页利用Excel製作柏拉圖最後，需對圖表進行美化處理，上圖為完整柏拉圖。28第28页Excel注意事項案例：100與