電腦操作系列培訓(xùn)課程(函數(shù)與公式)

1、電腦操作系列培訓(xùn)課程(函數(shù)與公式)第1頁，共27頁，2022年，5月20日，7點17分，星期五目 錄前 言1常用函數(shù)與公式2綜合應(yīng)用案例3結(jié) 語415分鐘30分鐘60分鐘60分鐘第2頁，共27頁，2022年，5月20日，7點17分，星期五導(dǎo) 讀前 言1常用函數(shù)與公式2綜合應(yīng)用案例3結(jié) 語4第3頁，共27頁，2022年，5月20日，7點17分，星期五前 言1、課堂規(guī)則：1）要求：“空杯心態(tài)” 不掉隊、勤動手腦、記錄要點、互幫互助、課后五多 （多實踐、多思考、多總結(jié)、多探索、多研討）。2）規(guī)則：“養(yǎng)機場” “部落先知負責制”第4頁，共27頁，2022年，5月20日，7點17分，星期五前 言2、課前

2、提問：1）關(guān)于EXCEL公式與函數(shù)，講講你知道的；2）講講你想知道的。第5頁，共27頁，2022年，5月20日，7點17分，星期五前 言3、函數(shù)與公式概述：第6頁，共27頁，2022年，5月20日，7點17分，星期五前 言1）函數(shù)與公式學(xué)習(xí)的六個階段Add Your Text高級階段中級階段第二道門檻第一道門檻初級階段揭開神秘面紗數(shù)組公式與多維引用嵌套函數(shù)與合并公式查找與匹配函數(shù)條件函數(shù)的運用操作界面、簡單函數(shù)提升工作效率的工具sum() 、today()習(xí)慣使用F1、Baidu知道、EXCELhome求助 If() 的運用 vlookup() 的運用數(shù)字小寫變大寫、生成日歷動態(tài)轉(zhuǎn)置等第7頁，

3、共27頁，2022年，5月20日，7點17分，星期五前 言2）函數(shù)、公式的定義公式：以等號開頭，通過運算符按一定的順序組合進行數(shù)據(jù)運算處理的等式。請判斷以下哪些是公式? A、 =15*3+20*2 B、 =A1*3+A2*2 C、 =單價*數(shù)量 D、 =SUM(A1*3,A2*2)答案：全是，通常公式可以有含數(shù)值運算、含單元格引用運算、含名稱運算、含函數(shù)的運算四種形式。第8頁，共27頁，2022年，5月20日，7點17分，星期五前 言如：=SUM(A1,A2,A3)表示A1,A2,A3這三個單元格數(shù)值相加的結(jié)果。以逗號分隔的3個參數(shù)函數(shù)名稱嵌套函數(shù)=IF(B2=60,”合格”,”不合格”),”

4、優(yōu)秀”)函數(shù)的結(jié)構(gòu)如下圖函數(shù)：按照特定的算法執(zhí)行計算并返回計算結(jié)果的預(yù)定義的特殊公式。第9頁，共27頁，2022年，5月20日，7點17分，星期五導(dǎo) 讀綜合應(yīng)用案例3結(jié) 語4常用函數(shù)與公式2前 言1第10頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式常用函數(shù)與嵌套函數(shù)的舉例（含部分工具欄、鍵操作說明與常用小技巧）類型知識點說明備注對應(yīng)示例號鍵說明F5快速選定工作表中所有帶有公式的單元格，F(xiàn)5鍵定位條件定位公式即可略鍵說明Ctr+BacksPace快速返回選中區(qū)域，按Ctr+BacksPae（即退格鍵）略鍵說明shift+F5查找，ctrl+F略鍵說明ctrl+:返回當前

5、日期2010-5-14略鍵說明ctrl+shift:返回當前時間17:17略鍵說明ctrl+方向鍵:定位空格略第11頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號鍵說明ctrl+shift+光標選中非空區(qū)域略鍵說明ctrl+shift+將光標放在表格中任一不為空的單元，按下組合鍵后選中上下左右方形非空區(qū)域略鍵說明ctrl+shift+enter數(shù)組公式（動態(tài)轉(zhuǎn)置）選定轉(zhuǎn)置后區(qū)域后插入數(shù)組函數(shù)transpose,選區(qū)域后同時按下該組合鍵即可改動源數(shù)據(jù)則轉(zhuǎn)置后的數(shù)據(jù)自動更改略鍵說明ctrl+D復(fù)制相臨行略鍵說明*/?單個字符*多個字符略鍵說明F2

6、不用鼠標,點進光標所在單元格,或是在地址欄輸入單元格名,Enter再點F2略第12頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號小技巧批量輸入ctrl下選取單元格，輸入內(nèi)容，enter;或是先復(fù)制，多個選中后再粘貼小技巧多個數(shù)據(jù)個位變?nèi)f位在空格中輸10000，復(fù)制，選中要調(diào)位數(shù)的區(qū)域，選擇性粘貼除（可同時選擇數(shù)值）小技巧文本變數(shù)值在一個單元格中輸入1,復(fù)制,選中要變數(shù)值的文本單元格,選擇性粘貼乘以小技巧表格要求無合并單元格、行列連續(xù)、有加列序號如何顯示0008,三種方法，0008，改為文本格式，=text(8,0000)小技巧保護工作區(qū)全選后

7、在單元格格式里把上鎖標記全去掉，然后選定目標區(qū)域后標記，再用工具欄-選項-安全性，加密碼保護小技巧排序三個以上的排序，先加輔助列=A1&/&B1&/&C1&/D1,再把該列排序即是按排序第13頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號小技巧高級篩選選把數(shù)據(jù)區(qū)標題行復(fù)制到條件區(qū)，輸入條件（同行：與and，同列不同行：or;文本：*/?,數(shù)：）,按高級篩選（條件區(qū)：標題與條件全部選入）小技巧同時對一個工作簿中多個表格進行頁面設(shè)置在工作表的表名處選定多個表格或全部表格，再進行設(shè)置，即對多個表格進行了設(shè)置。小技巧自動保存文件在EXCEL中如何使它

8、象WORD一樣的自動定時保存文件？ 點擊“工具”“選項”“保存”設(shè)置自動保存時間隔小技巧定位快速選定工作表中所有帶有公式的單元格，F(xiàn)5鍵定位條件定位公式即可小技巧有效性在幾個人名間進行填寫，有效性選序列，條件中人名間用英文狀態(tài)下的,用工具選項預(yù)設(shè)序列時也是用英文狀態(tài)下的,小技巧改寫鍵入文字會取代了原有文字，在狀態(tài)欄內(nèi)點擊“改寫”（或直接按鍵盤上的insert）第14頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)F4相對絕對單元格的轉(zhuǎn)換鎖定行列函數(shù)Sqrt返回正平方根=SQRT(ABS(-9)例1函數(shù)trim去除單元格內(nèi)字符兩邊的空格函數(shù)t

9、ype字符類型,1數(shù)值，2文本 =type(E20)函數(shù)text變換數(shù)字大小寫=TEXT(A1,DBNUM2)例2函數(shù)ABS絕對值=ABS(E20)例2函數(shù)int取整=INT(E20)例2第15頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)fixed=FIXED(2016.349,-1)表示四舍五入到小數(shù)點左邊第一位=FIXED(2016.349,-1,true)表示返回的結(jié)果是真實值?！?020”無千分號2,020例3函數(shù)countcount計算非空單元格個數(shù)（不計算文本）,counta計算文本=COUNT(D21:D29)與=COUN

10、TA(D21:D29)例4函數(shù)sumif/countif條件篩選求和/計數(shù)=SUMIF(D22:D30,30,E22:E30)指區(qū)域D22:D30中，選取單元格值大于30的單元，計算其對應(yīng)的E列的單元格值之和。例4函數(shù)len、mid、left、right=len(A1),=mid(A1,第幾位，取幾位)去掉單元格中的后一位字符=LEFT(A1,LEN(A1)-1)例5函數(shù)日期函數(shù)=date(年，月，日),=tody()當前日期，now()當前時間，當前年月的頭一天=DATE(YEAR(NOW(),MONTH(NOW(),1)=WEEKDAY(B2)顯示星期幾(結(jié)果直接在單元格式里設(shè)置成日期星期

11、幾)=edate(A1,1)顯示日期的后一個月的日期例6函數(shù)CONCATENATE字段組合函數(shù)=CONCATENATE(MID(20點08分53秒,1,2),:,MID(20點08分53秒,4,2),:,MID(20點08分53秒,7,2)第16頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)if=IF(A3=80,優(yōu)秀,IF(A3=60,及格,IF(A360,不及格)例7函數(shù)rank=rank(A2,范圍)返回排名號例8函數(shù)ISERROR用于測試函數(shù)式返回的數(shù)值是否有錯。如果有錯，該函數(shù)返回TRUE，反之返回FALSE。=D38/E38,

12、當E38是0時返回的是#DIV/0!=IF(ISERROR(D38/E38),D38/E38)當E38為0返回的的空格函數(shù)frequency以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。=FREQUENCY(數(shù)據(jù)區(qū)域,劃分標準)函數(shù)fixed=FIXED(要四舍五入的數(shù)字,取整到小數(shù)點右邊第幾位)計算入司年份=FIXED(TODAY()-A16)/365,1)&年例9函數(shù)vlookup查找函數(shù)=VLOOKUP(A1,Sheet1!A:B,2,0)例10函數(shù)INDEX自動搜索符合條件的數(shù)值=INDEX($E$19:$E$35,MATCH(COLUMN()-2,$AG$19:$AG$35,0),1)

13、例11第17頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)身份證號碼身份證號碼：1、第17位是代表性別，雙為女，奇數(shù)為男mod(A1,B)表示A1被B除后的余數(shù)。=IF(MOD(MID17,1),2)=1,男,女)例12函數(shù)身份證號碼2、7-12位數(shù)段表示生日，判斷年齡=YEAR(TODAY()-YEAR(TEXT(MID7,8),#-00-00)函數(shù)upper與lower字母變大小寫=UPPER(Excel函數(shù))=LOWER(Excel函數(shù))函數(shù)char顯示

14、函數(shù)=CHAR(ROW(A1),row(A1)表示1，也就是char(1)返回一個對應(yīng)的符號 ，1-128會返回不同的符號，可以在做表格之前先把他們?nèi)旁谝涣?，直接取用，免得在插入菜單中一個個的選擇、點擊。見右示例，拖動生成人員A-人員Z序列。橫向生成用=人員&CHAR(COLUMN(A1)+64)；縱向生成裝Column(A1)換成ROW(A1)人員B例13第18頁，共27頁，2022年，5月20日，7點17分，星期五常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號提升生成文本首字母返回指定文本的第一個字的拼音首字母=LOOKUP(CODE(張虹),45217+0,36,544,1101,1609

15、,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264,A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,W,X,Y,Z)提升transposer數(shù)組公式（動態(tài)轉(zhuǎn)置）選定轉(zhuǎn)置后區(qū)域后插入數(shù)組函數(shù)transpose,選區(qū)域后同時按下該組合鍵ctrl+shift+enter即可改動源數(shù)據(jù)則轉(zhuǎn)置后的數(shù)據(jù)自動更改提升篩選單元格格式篩選單元格格式;選中要篩選單元格文本顏色的列，插入名稱定義A，并在公式處輸入=get.cell(24,A1)再插入一新列，在

16、第一個單元格內(nèi)=A 向下拖即可列出該列單元格的文本顏色參數(shù)，再做篩選第19頁，共27頁，2022年，5月20日，7點17分，星期五導(dǎo) 讀常用函數(shù)與公式2結(jié) 語4綜合應(yīng)用案例3前 言1第20頁，共27頁，2022年，5月20日，7點17分，星期五綜合應(yīng)用案例1、某業(yè)務(wù)部門業(yè)務(wù)員績效得分考核銷售收入、毛利兩項指標，請你用系統(tǒng)導(dǎo)出的該部門4月銷售商品明細、客戶對應(yīng)業(yè)務(wù)員名單、業(yè)務(wù)員所轄客戶指標表三張表格來核算業(yè)務(wù)員績效得分。（20分鐘）4月銷售商品明細客戶對應(yīng)業(yè)務(wù)員名單業(yè)務(wù)員所轄客戶指標表業(yè)務(wù)員績效排序與分數(shù)第21頁，共27頁，2022年，5月20日，7點17分，星期五綜合應(yīng)用案例2、請運用本課所學(xué)

17、，在EXCEL中完成如下電子月歷（20分鐘）：要 求：1）隨電腦內(nèi)置日歷變換；2）當月1號位于A2；3）當天日期格式自動顯示如圖；4）有“#月份”的自動顯示；5）其他格式如圖設(shè)置。第22頁，共27頁，2022年，5月20日，7點17分，星期五綜合應(yīng)用案例3、請運用本課所學(xué)，在EXCEL中制出如下一張“部門工作進度表”（30分鐘）：要 求：1）日期隨月份變換自行變換；2）紫色條顯示當前任務(wù)；3）填入每一事項的完成率即可自動生“執(zhí)行 進度”（已完成、進行中、超前、滯后）；4）后面自動生成甘特圖（項目管理圖）。第23頁，共27頁，2022年，5月20日，7點17分，星期五綜合應(yīng)用案例自動判斷執(zhí)行狀態(tài)表中所有日期自動更新自動用桔條顯示當前任務(wù)甘特圖中藍色塊表示當日對應(yīng)工作項已完成甘特圖中灰色塊表示當日對應(yīng)工作項未完成第24頁，共27頁，2022年，5月20日，7點17分，星期五導(dǎo) 讀常用函數(shù)與公式2綜合應(yīng)用案例3結(jié) 語4前 言1第25頁，共27頁，2022年，5月20日，7點17分，星期五結(jié) 語 函數(shù)的學(xué)習(xí)內(nèi)容很多，一般來說需要三天的時間才能完成常用知識學(xué)習(xí)。限于課堂時間有限，僅將相關(guān)常識與最常見的少數(shù)函數(shù)給大家作了簡要介紹，在后面的時間里面，我們還將對應(yīng)用范圍較廣的I