電腦操作系列培訓(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) 不落伍、勤動手腦、記錄要點、互幫互助、課后五多不落伍、勤動手腦、記錄要點、互幫互助、課后五多 多實際、多思索、多總結(jié)、多探求、多研討。多實際、多思索、多總結(jié)、多探求、多研討。2規(guī)那么：規(guī)那么：“養(yǎng)機場養(yǎ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í)的六個階段函數(shù)與公式學(xué)習(xí)的六個階段高級階段高級階段中級階段中級階段第二道門檻第二道門檻第一道門檻第一道門檻初級階段初級階段揭開奧秘面紗揭開奧秘面紗數(shù)組公式與多維援用嵌套函數(shù)與合并公式查找與匹配函數(shù)條件函數(shù)的運用操作界面、簡單函數(shù)提升任務(wù)效率的工具sum() 、today()習(xí)慣運用F1、Baidu知道、EXCELhome求助 If() 的運用 vlookup() 的運用數(shù)字小寫變大寫、生成日歷動態(tài)轉(zhuǎn)置等8 8前 言2函數(shù)、公式的定義公式：以等號開頭，經(jīng)過運算符按一定的順序組合進展數(shù)公式：以等號開頭，經(jīng)過運算符按一定的順序組合進展數(shù)據(jù)運算處置的等式。據(jù)運算處置

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

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

5、l+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不用鼠標,點進光標所在單元格,或是在地址欄輸入單元格名,Enter再點F2略1313常用函數(shù)與公式類型類型知識點知識點說明說明備注備注對應(yīng)示例對應(yīng)示例號號小技巧批量輸入ctrl下選取單元格，輸入內(nèi)容，enter;或是先復(fù)制，多個選中后再粘貼小技巧多個數(shù)據(jù)個位變?nèi)f位在空格中

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

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

8、入文字會取代了原有文字，在狀態(tài)欄內(nèi)點擊“改寫”（或直接按鍵盤上的insert）1515常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)F4相對絕對單元格的轉(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絕對值=ABS(E20)例2函數(shù)int取整=INT(E20)例21616常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)fixed=FIXED(2019.349,-1)表示四舍五入到小數(shù)點左邊第一位=FIXED(20

9、19.349,-1,true)表示返回的結(jié)果是真實值?！?020”無千分號2,020例3函數(shù)countcount計算非空單元格個數(shù)（不計算文本）,counta計算文本=COUNT(D21:D29)與=COUNTA(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

10、(年，月，日),=tody()當(dāng)前日期，now()當(dāng)前時間，當(dāng)前年月的頭一天=DATE(YEAR(NOW(),MONTH(NOW(),1)=WEEKDAY(B2)顯示星期幾(結(jié)果直接在單元格式里設(shè)置成日期星期幾)=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)1717常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號函數(shù)if=IF(A3=80,優(yōu)秀,IF(A3=60,及格,IF(A360,不及格)例7函數(shù)rank=r

11、ank(A2,范圍)返回排名號例8函數(shù)ISERROR用于測試函數(shù)式返回的數(shù)值是否有錯。如果有錯，該函數(shù)返回TRUE，反之返回FALSE。=D38/E38,當(dāng)E38是0時返回的是#DIV/0!=IF(ISERROR(D38/E38),D38/E38)當(dāng)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

12、,2,0)例10函數(shù)INDEX自動搜索符合條件的數(shù)值=INDEX($E$19:$E$35,MATCH(COLUMN()-2,$AG$19:$AG$35,0),1)例111818常用函數(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ù)u

13、pper與lower字母變大小寫=UPPER(Excel函數(shù))=LOWER(Excel函數(shù))函數(shù)char顯示函數(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例131919常用函數(shù)與公式類型知識點說明備注對應(yīng)示例號提升生成文本首字母返回指定文本的第一個字的拼音首字母=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ù)組公式（動態(tài)轉(zhuǎn)置）選定轉(zhuǎn)置后區(qū)域后插入數(shù)組函數(shù)transpose,選區(qū)域后同時按下該組合鍵ctrl+shift+enter即可改動源數(shù)據(jù)則轉(zhuǎn)置后的數(shù)據(jù)自動更改提升篩選單元格格式篩選單元格格式;選中要篩選單元格文本顏色的列，插入名稱定義A，

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

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

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