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

1、電腦操作系列培訓(xùn)課程電腦操作系列培訓(xùn)課程 2 2目目 錄錄15分鐘30分鐘60分鐘60分鐘3 3導(dǎo) 讀4 4前前 言言1、課堂規(guī)那么：、課堂規(guī)那么：1要求：要求：“空杯心態(tài)空杯心態(tài) 不落伍、勤動(dòng)手腦、記錄要點(diǎn)、互幫互助、課后五多不落伍、勤動(dòng)手腦、記錄要點(diǎn)、互幫互助、課后五多 多實(shí)際、多思索、多總結(jié)、多探求、多研討。多實(shí)際、多思索、多總結(jié)、多探求、多研討。2規(guī)那么：規(guī)那么：“養(yǎng)機(jī)場(chǎng)養(yǎng)機(jī)場(chǎng) “部落先知擔(dān)任制部落先知擔(dān)任制5 5前前 言言2、課前提問：、課前提問：1關(guān)于關(guān)于EXCEL公式與函數(shù)，講講他知道的；公式與函數(shù)，講講他知道的；2講講他想知道的。講講他想知道的。6 6前前 言言3、函數(shù)與公式概

2、述：7 7前 言1 1函數(shù)與公式學(xué)習(xí)的六個(gè)階段函數(shù)與公式學(xué)習(xí)的六個(gè)階段高級(jí)階段高級(jí)階段中級(jí)階段中級(jí)階段第二道門檻第二道門檻第一道門檻第一道門檻初級(jí)階段初級(jí)階段揭開奧秘面紗揭開奧秘面紗數(shù)組公式與多維援用嵌套函數(shù)與合并公式查找與匹配函數(shù)條件函數(shù)的運(yùn)用操作界面、簡(jiǎn)單函數(shù)提升任務(wù)效率的工具sum() 、today()習(xí)慣運(yùn)用F1、Baidu知道、EXCELhome求助 If() 的運(yùn)用 vlookup() 的運(yùn)用數(shù)字小寫變大寫、生成日歷動(dòng)態(tài)轉(zhuǎn)置等8 8前 言2函數(shù)、公式的定義公式：以等號(hào)開頭，經(jīng)過運(yùn)算符按一定的順序組合進(jìn)展數(shù)公式：以等號(hào)開頭，經(jīng)過運(yùn)算符按一定的順序組合進(jìn)展數(shù)據(jù)運(yùn)算處置的等式。據(jù)運(yùn)算處置

3、的等式。請(qǐng)判別以下哪些是公式? A、 =15*3+20*2 B、 =A1*3+A2*2 C、 =單價(jià)*數(shù)量 D、 =SUM(A1*3,A2*2)答案：全是，通常公式可以有含數(shù)值運(yùn)算、含單元格援用運(yùn)算、含稱號(hào)運(yùn)算、含函數(shù)的運(yùn)算四種方式。9 9前 言如：=SUM(A1,A2,A3)表示A1,A2,A3這三個(gè)單元格數(shù)值相加的結(jié)果。以逗號(hào)分隔的以逗號(hào)分隔的3 3個(gè)參數(shù)個(gè)參數(shù)函數(shù)稱函數(shù)稱號(hào)號(hào)嵌套函數(shù)嵌套函數(shù)=IF(B2=60,=IF(B2=60,合格合格, ,不合格不合格),),優(yōu)秀優(yōu)秀) )函數(shù)的構(gòu)造如以下圖函數(shù)：按照特定的算法執(zhí)行計(jì)算并前往計(jì)算結(jié)果的預(yù)定義的特殊公式。1010導(dǎo) 讀1111常用函數(shù)與

4、公式常用函數(shù)與嵌套函數(shù)的舉例含部分工具欄、鍵操作闡明與常用小技巧類型類型知識(shí)點(diǎn)知識(shí)點(diǎn)說明說明備注備注對(duì)應(yīng)示例對(duì)應(yīng)示例號(hào)號(hào)鍵說明F5快速選定工作表中所有帶有公式的單元格，F(xiàn)5鍵定位條件定位公式即可略鍵說明Ctr+BacksPace快速返回選中區(qū)域，按Ctr+BacksPae（即退格鍵）略鍵說明shift+F5查找，ctrl+F略鍵說明ctrl+:返回當(dāng)前日期2019-5-14略鍵說明ctrl+shift:返回當(dāng)前時(shí)間17:17略鍵說明ctrl+方向鍵:定位空格略1212常用函數(shù)與公式類型類型知識(shí)點(diǎn)知識(shí)點(diǎn)說明說明備注備注對(duì)應(yīng)示例對(duì)應(yīng)示例號(hào)號(hào)鍵說明ctrl+shift+光標(biāo)選中非空區(qū)域略鍵說明ctr

5、l+shift+將光標(biāo)放在表格中任一不為空的單元，按下組合鍵后選中上下左右方形非空區(qū)域略鍵說明ctrl+shift+enter數(shù)組公式（動(dòng)態(tài)轉(zhuǎn)置）選定轉(zhuǎn)置后區(qū)域后插入數(shù)組函數(shù)transpose,選區(qū)域后同時(shí)按下該組合鍵即可改動(dòng)源數(shù)據(jù)則轉(zhuǎn)置后的數(shù)據(jù)自動(dòng)更改略鍵說明ctrl+D復(fù)制相臨行略鍵說明*/?單個(gè)字符*多個(gè)字符略鍵說明F2不用鼠標(biāo),點(diǎn)進(jìn)光標(biāo)所在單元格,或是在地址欄輸入單元格名,Enter再點(diǎn)F2略1313常用函數(shù)與公式類型類型知識(shí)點(diǎn)知識(shí)點(diǎn)說明說明備注備注對(duì)應(yīng)示例對(duì)應(yīng)示例號(hào)號(hào)小技巧批量輸入ctrl下選取單元格，輸入內(nèi)容，enter;或是先復(fù)制，多個(gè)選中后再粘貼小技巧多個(gè)數(shù)據(jù)個(gè)位變?nèi)f位在空格中

6、輸10000，復(fù)制，選中要調(diào)位數(shù)的區(qū)域，選擇性粘貼除（可同時(shí)選擇數(shù)值）小技巧文本變數(shù)值在一個(gè)單元格中輸入1,復(fù)制,選中要變數(shù)值的文本單元格,選擇性粘貼乘以小技巧表格要求無合并單元格、行列連續(xù)、有加列序號(hào)如何顯示0008,三種方法，0008，改為文本格式，=text(8,0000)小技巧保護(hù)工作區(qū)全選后在單元格格式里把上鎖標(biāo)記全去掉，然后選定目標(biāo)區(qū)域后標(biāo)記，再用工具欄-選項(xiàng)-安全性，加密碼保護(hù)小技巧排序三個(gè)以上的排序，先加輔助列=A1&/&B1&/&C1&/D1,再把該列排序即是按排序1414常用函數(shù)與公式類型類型知識(shí)點(diǎn)知識(shí)點(diǎn)說明說明備注備注對(duì)應(yīng)示例號(hào)對(duì)應(yīng)

7、示例號(hào)小技巧高級(jí)篩選選把數(shù)據(jù)區(qū)標(biāo)題行復(fù)制到條件區(qū)，輸入條件（同行：與and，同列不同行：or;文本：*/?,數(shù)：）,按高級(jí)篩選（條件區(qū)：標(biāo)題與條件全部選入）小技巧同時(shí)對(duì)一個(gè)工作簿中多個(gè)表格進(jìn)行頁面設(shè)置在工作表的表名處選定多個(gè)表格或全部表格，再進(jìn)行設(shè)置，即對(duì)多個(gè)表格進(jìn)行了設(shè)置。小技巧自動(dòng)保存文件在EXCEL中如何使它象WORD一樣的自動(dòng)定時(shí)保存文件？ 點(diǎn)擊“工具”“選項(xiàng)”“保存”設(shè)置自動(dòng)保存時(shí)間隔小技巧定位快速選定工作表中所有帶有公式的單元格，F(xiàn)5鍵定位條件定位公式即可小技巧有效性在幾個(gè)人名間進(jìn)行填寫，有效性選序列，條件中人名間用英文狀態(tài)下的,用工具選項(xiàng)預(yù)設(shè)序列時(shí)也是用英文狀態(tài)下的,小技巧改寫鍵

8、入文字會(huì)取代了原有文字，在狀態(tài)欄內(nèi)點(diǎn)擊“改寫”（或直接按鍵盤上的insert）1515常用函數(shù)與公式類型知識(shí)點(diǎn)說明備注對(duì)應(yīng)示例號(hào)函數(shù)F4相對(duì)絕對(duì)單元格的轉(zhuǎn)換鎖定行列函數(shù)Sqrt返回正平方根=SQRT(ABS(-9)例1函數(shù)trim去除單元格內(nèi)字符兩邊的空格函數(shù)type字符類型,1數(shù)值，2文本 =type(E20)函數(shù)text變換數(shù)字大小寫=TEXT(A1,DBNUM2)例2函數(shù)ABS絕對(duì)值=ABS(E20)例2函數(shù)int取整=INT(E20)例21616常用函數(shù)與公式類型知識(shí)點(diǎn)說明備注對(duì)應(yīng)示例號(hào)函數(shù)fixed=FIXED(2019.349,-1)表示四舍五入到小數(shù)點(diǎn)左邊第一位=FIXED(20

9、19.349,-1,true)表示返回的結(jié)果是真實(shí)值?！?020”無千分號(hào)2,020例3函數(shù)countcount計(jì)算非空單元格個(gè)數(shù)（不計(jì)算文本）,counta計(jì)算文本=COUNT(D21:D29)與=COUNTA(D21:D29)例4函數(shù)sumif/countif條件篩選求和/計(jì)數(shù)=SUMIF(D22:D30,30,E22:E30)指區(qū)域D22:D30中，選取單元格值大于30的單元，計(jì)算其對(duì)應(yīng)的E列的單元格值之和。例4函數(shù)len、mid、left、right=len(A1),=mid(A1,第幾位，取幾位)去掉單元格中的后一位字符=LEFT(A1,LEN(A1)-1)例5函數(shù)日期函數(shù)=date

10、(年，月，日),=tody()當(dāng)前日期，now()當(dāng)前時(shí)間，當(dāng)前年月的頭一天=DATE(YEAR(NOW(),MONTH(NOW(),1)=WEEKDAY(B2)顯示星期幾(結(jié)果直接在單元格式里設(shè)置成日期星期幾)=edate(A1,1)顯示日期的后一個(gè)月的日期例6函數(shù)CONCATENATE字段組合函數(shù)=CONCATENATE(MID(20點(diǎn)08分53秒,1,2),:,MID(20點(diǎn)08分53秒,4,2),:,MID(20點(diǎn)08分53秒,7,2)1717常用函數(shù)與公式類型知識(shí)點(diǎn)說明備注對(duì)應(yīng)示例號(hào)函數(shù)if=IF(A3=80,優(yōu)秀,IF(A3=60,及格,IF(A360,不及格)例7函數(shù)rank=r

11、ank(A2,范圍)返回排名號(hào)例8函數(shù)ISERROR用于測(cè)試函數(shù)式返回的數(shù)值是否有錯(cuò)。如果有錯(cuò)，該函數(shù)返回TRUE，反之返回FALSE。=D38/E38,當(dāng)E38是0時(shí)返回的是#DIV/0!=IF(ISERROR(D38/E38),D38/E38)當(dāng)E38為0返回的的空格函數(shù)frequency以一列垂直數(shù)組返回某個(gè)區(qū)域中數(shù)據(jù)的頻率分布。=FREQUENCY(數(shù)據(jù)區(qū)域,劃分標(biāo)準(zhǔn))函數(shù)fixed=FIXED(要四舍五入的數(shù)字,取整到小數(shù)點(diǎn)右邊第幾位)計(jì)算入司年份=FIXED(TODAY()-A16)/365,1)&年例9函數(shù)vlookup查找函數(shù)=VLOOKUP(A1,Sheet1!A:B

12、,2,0)例10函數(shù)INDEX自動(dòng)搜索符合條件的數(shù)值=INDEX($E$19:$E$35,MATCH(COLUMN()-2,$AG$19:$AG$35,0),1)例111818常用函數(shù)與公式類型知識(shí)點(diǎn)說明備注對(duì)應(yīng)示例號(hào)函數(shù)身份證號(hào)碼身份證號(hào)碼：1、第17位是代表性別，雙為女，奇數(shù)為男mod(A1,B)表示A1被B除后的余數(shù)。=IF(MOD(MID17,1),2)=1,男,女)例12函數(shù)身份證號(hào)碼2、7-12位數(shù)段表示生日，判斷年齡=YEAR(TODAY()-YEAR(TEXT(MID7,8),#-00-00)函數(shù)u

13、pper與lower字母變大小寫=UPPER(Excel函數(shù))=LOWER(Excel函數(shù))函數(shù)char顯示函數(shù)=CHAR(ROW(A1),row(A1)表示1，也就是char(1)返回一個(gè)對(duì)應(yīng)的符號(hào) ，1-128會(huì)返回不同的符號(hào)，可以在做表格之前先把他們?nèi)旁谝涣?，直接取用，免得在插入菜單中一個(gè)個(gè)的選擇、點(diǎn)擊。見右示例，拖動(dòng)生成人員A-人員Z序列。橫向生成用=人員&CHAR(COLUMN(A1)+64)；縱向生成裝Column(A1)換成ROW(A1)人員B例131919常用函數(shù)與公式類型知識(shí)點(diǎn)說明備注對(duì)應(yīng)示例號(hào)提升生成文本首字母返回指定文本的第一個(gè)字的拼音首字母=LOOKUP(CO

14、DE(張虹),45217+0,36,544,1101,1609,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ù)組公式（動(dòng)態(tài)轉(zhuǎn)置）選定轉(zhuǎn)置后區(qū)域后插入數(shù)組函數(shù)transpose,選區(qū)域后同時(shí)按下該組合鍵ctrl+shift+enter即可改動(dòng)源數(shù)據(jù)則轉(zhuǎn)置后的數(shù)據(jù)自動(dòng)更改提升篩選單元格格式篩選單元格格式;選中要篩選單元格文本顏色的列，插入名稱定義A，

15、并在公式處輸入=get.cell(24,A1)再插入一新列，在第一個(gè)單元格內(nèi)=A 向下拖即可列出該列單元格的文本顏色參數(shù)，再做篩選2020導(dǎo) 讀2121綜合運(yùn)用案例1 1、某業(yè)務(wù)部門業(yè)務(wù)員績(jī)效得分考核銷售收入、毛利兩項(xiàng)、某業(yè)務(wù)部門業(yè)務(wù)員績(jī)效得分考核銷售收入、毛利兩項(xiàng)目的，請(qǐng)他用系統(tǒng)導(dǎo)出的該部門目的，請(qǐng)他用系統(tǒng)導(dǎo)出的該部門4 4月銷售商品明細(xì)、客戶月銷售商品明細(xì)、客戶對(duì)應(yīng)業(yè)務(wù)員名單、業(yè)務(wù)員所轄客戶目的表三張表格來核對(duì)應(yīng)業(yè)務(wù)員名單、業(yè)務(wù)員所轄客戶目的表三張表格來核算業(yè)務(wù)員績(jī)效得分。算業(yè)務(wù)員績(jī)效得分。2020分鐘分鐘4月銷售商品明細(xì)客戶對(duì)應(yīng)業(yè)務(wù)員名單業(yè)務(wù)員所轄客戶目的表業(yè)務(wù)員績(jī)效排序與分?jǐn)?shù)2222綜

16、合運(yùn)用案例2、請(qǐng)運(yùn)用本課所學(xué)，在EXCEL中完成如下電子月歷20分鐘：要 求：1隨電腦內(nèi)置日歷變換；2當(dāng)月1號(hào)位于A2；3當(dāng)天日期格式自動(dòng)顯示如圖；4有“#月份的自動(dòng)顯示；5其他格式如圖設(shè)置。2323綜合運(yùn)用案例3 3、請(qǐng)運(yùn)用本課所學(xué)，在、請(qǐng)運(yùn)用本課所學(xué)，在EXCELEXCEL中制出如下一張中制出如下一張“部門部門任務(wù)進(jìn)度表任務(wù)進(jìn)度表3030分鐘：分鐘：要要 求：求：1 1日期隨月份變換自行變換；日期隨月份變換自行變換；2 2紫色條顯示當(dāng)前義務(wù)；紫色條顯示當(dāng)前義務(wù)；3 3填入每一事項(xiàng)的完成率即可自動(dòng)生填入每一事項(xiàng)的完成率即可自動(dòng)生“執(zhí)行執(zhí)行 進(jìn)度已完成、進(jìn)展中、超前、滯后；進(jìn)度已完成、進(jìn)展中、

17、超前、滯后；4 4后面自動(dòng)生成甘特圖工程管理圖。后面自動(dòng)生成甘特圖工程管理圖。2424綜合運(yùn)用案例自動(dòng)判別執(zhí)行形狀表中一切日期自動(dòng)更新自動(dòng)用桔條顯示當(dāng)前義務(wù)甘特圖中藍(lán)色塊表示當(dāng)日對(duì)應(yīng)任務(wù)項(xiàng)已完成甘特圖中灰色塊表示當(dāng)日對(duì)應(yīng)任務(wù)項(xiàng)未完成2525導(dǎo) 讀2626結(jié) 語 函數(shù)的學(xué)習(xí)內(nèi)容很多，普通來說需求三天的時(shí)間才干完成常用知識(shí)函數(shù)的學(xué)習(xí)內(nèi)容很多，普通來說需求三天的時(shí)間才干完成常用知識(shí)學(xué)習(xí)。限于課堂時(shí)間有限，僅將相關(guān)常識(shí)與最常見的少數(shù)函數(shù)給大家作學(xué)習(xí)。限于課堂時(shí)間有限，僅將相關(guān)常識(shí)與最常見的少數(shù)函數(shù)給大家作了簡(jiǎn)要引見，在后面的時(shí)間里面，我們還將對(duì)運(yùn)用范圍較廣的了簡(jiǎn)要引見，在后面的時(shí)間里面，我們還將對(duì)運(yùn)用范圍較廣的IFIF、VLOOKUPVLOOKUP、INDEXINDEX、MATCHMATCH等函數(shù)與嵌套函數(shù)、組公式、簡(jiǎn)單等函數(shù)與嵌套函數(shù)、組公式、簡(jiǎn)單的宏等進(jìn)展更深一層次的引見，并會(huì)向大家講解如何在實(shí)踐任務(wù)中運(yùn)用的宏等進(jìn)展更深一層次的引見，并會(huì)向大家講解如何在實(shí)踐任