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

電腦操作系列培訓(xùn)課程(函數(shù)與公式)第1頁，課件共27頁，創(chuàng)作于2023年2月目錄前言1常用函數(shù)與公式2綜合應(yīng)用案例3結(jié)語415分鐘30分鐘60分鐘60分鐘第2頁，課件共27頁，創(chuàng)作于2023年2月導(dǎo)讀前言1常用函數(shù)與公式2綜合應(yīng)用案例3結(jié)語4第3頁，課件共27頁，創(chuàng)作于2023年2月前言1、課堂規(guī)則：1）要求：“空杯心態(tài)”不掉隊(duì)、勤動手腦、記錄要點(diǎn)、互幫互助、課后五多（多實(shí)踐、多思考、多總結(jié)、多探索、多研討）。2）規(guī)則：“養(yǎng)機(jī)場”“部落先知負(fù)責(zé)制”第4頁，課件共27頁，創(chuàng)作于2023年2月前言2、課前提問：1）關(guān)于EXCEL公式與函數(shù)，講講你知道的；2）講講你想知道的。第5頁，課件共27頁，創(chuàng)作于2023年2月前言3、函數(shù)與公式概述：第6頁，課件共27頁，創(chuàng)作于2023年2月前言1）函數(shù)與公式學(xué)習(xí)的六個階段AddYourText高級階段中級階段第二道門檻第一道門檻初級階段揭開神秘面紗數(shù)組公式與多維引用嵌套函數(shù)與合并公式查找與匹配函數(shù)條件函數(shù)的運(yùn)用操作界面、簡單函數(shù)提升工作效率的工具sum()、today()……習(xí)慣使用F1、Baidu知道、EXCELhome求助If()的運(yùn)用vlookup()的運(yùn)用數(shù)字小寫變大寫、生成日歷動態(tài)轉(zhuǎn)置等第7頁，課件共27頁，創(chuàng)作于2023年2月前言2）函數(shù)、公式的定義公式：以等號開頭，通過運(yùn)算符按一定的順序組合進(jìn)行數(shù)據(jù)運(yùn)算處理的等式。請判斷以下哪些是公式??A、=15*3+20*2B、=A1*3+A2*2

C、=單價*數(shù)量D、=SUM(A1*3,A2*2)答案：全是，通常公式可以有含數(shù)值運(yùn)算、含單元格引用運(yùn)算、含名稱運(yùn)算、含函數(shù)的運(yùn)算四種形式。第8頁，課件共27頁，創(chuàng)作于2023年2月前言如：=SUM(A1,A2,A3)表示A1,A2,A3這三個單元格數(shù)值相加的結(jié)果。以逗號分隔的3個參數(shù)函數(shù)名稱嵌套函數(shù)=IF(B2<90,if(B2>=60,”合格”,”不合格”),”優(yōu)秀”)函數(shù)的結(jié)構(gòu)如下圖函數(shù)：按照特定的算法執(zhí)行計(jì)算并返回計(jì)算結(jié)果的預(yù)定義的特殊公式。第9頁，課件共27頁，創(chuàng)作于2023年2月導(dǎo)讀綜合應(yīng)用案例3結(jié)語4常用函數(shù)與公式2前言1第10頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式常用函數(shù)與嵌套函數(shù)的舉例（含部分工具欄、鍵操作說明與常用小技巧）類型知識點(diǎn)說明備注對應(yīng)示例號鍵說明F5快速選定工作表中所有帶有公式的單元格，F(xiàn)5鍵定位—條件定位—公式即可

略鍵說明Ctr+BacksPace快速返回選中區(qū)域，按Ctr+BacksPae（即退格鍵）

略鍵說明shift+F5查找，ctrl+F

略鍵說明ctrl+:返回當(dāng)前日期2010-5-14略鍵說明ctrl+shift:返回當(dāng)前時間17:17略鍵說明ctrl+方向鍵:定位空格

略第11頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(yīng)示例號鍵說明ctrl+shift+光標(biāo)選中非空區(qū)域

略鍵說明ctrl+shift+﹡將光標(biāo)放在表格中任一不為空的單元，按下組合鍵后選中上下左右方形非空區(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不用鼠標(biāo),點(diǎn)進(jìn)光標(biāo)所在單元格,或是在地址欄輸入單元格名,Enter再點(diǎn)F2

略第12頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(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")

小技巧保護(hù)工作區(qū)全選后在單元格格式里把上鎖標(biāo)記全去掉，然后選定目標(biāo)區(qū)域后標(biāo)記，再用工具欄-選項(xiàng)-安全性，加密碼保護(hù)

小技巧排序三個以上的排序，先加輔助列=A1&"/"&B1&"/"&C1&"/"D1,再把該列排序即是按Ａ－Ｂ－Ｃ－Ｄ排序

第13頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(yīng)示例號小技巧高級篩選選把數(shù)據(jù)區(qū)標(biāo)題行復(fù)制到條件區(qū)，輸入條件（同行：與and，同列不同行：or;文本：*/?,數(shù)：＜／＞／＝）,按高級篩選（條件區(qū)：標(biāo)題與條件全部選入）

小技巧同時對一個工作簿中多個表格進(jìn)行頁面設(shè)置在工作表的表名處選定多個表格或全部表格，再進(jìn)行設(shè)置，即對多個表格進(jìn)行了設(shè)置。

小技巧自動保存文件在EXCEL中如何使它象WORD一樣的自動定時保存文件？

點(diǎn)擊“工具”—“選項(xiàng)”—“保存”設(shè)置自動保存時間隔

小技巧定位快速選定工作表中所有帶有公式的單元格，F(xiàn)5鍵定位—條件定位—公式即可

小技巧有效性在幾個人名間進(jìn)行填寫，有效性選序列，條件中人名間用英文狀態(tài)下的＂,＂用工具—選項(xiàng)預(yù)設(shè)序列時也是用英文狀態(tài)下的","

小技巧改寫鍵入文字會取代了原有文字，在狀態(tài)欄內(nèi)點(diǎn)擊“改寫”（或直接按鍵盤上的insert）

第14頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(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)例2第15頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(yīng)示例號函數(shù)fixed=FIXED(2016.349,-1)表示四舍五入到小數(shù)點(diǎn)左邊第一位

=FIXED(2016.349,-1,true)表示返回的結(jié)果是真實(shí)值?！?020”無千分號2,020例3函數(shù)countcount計(jì)算非空單元格個數(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ì)算其對應(yīng)的E列的單元格值之和。例4函數(shù)len、mid、left、right=len(A1),=mid(A1,第幾位，取幾位)去掉單元格中的后一位字符=LEFT(A1,LEN(A1)-1)例5函數(shù)日期函數(shù)=date(年，月，日),=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點(diǎn)08分53秒",1,2),":",MID("20點(diǎn)08分53秒",4,2),":",MID("20點(diǎn)08分53秒",7,2))

第16頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(yīng)示例號函數(shù)if=IF(A3>=80,"優(yōu)秀",IF(A3>=60,"及格",IF(A3<60,"不及格")))

例7函數(shù)rank=rank(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ū)域,劃分標(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,2,0)例10函數(shù)INDEX自動搜索符合條件的數(shù)值=INDEX($E$19:$E$35,MATCH(COLUMN()-2,$AG$19:$AG$35,0),1)例11第17頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(yīng)示例號函數(shù)身份證號碼身份證號碼：

1、第17位是代表性別，雙為女，奇數(shù)為男

mod(A1,B)表示A1被B除后的余數(shù)。=IF(MOD(MID(,17,1),2)=1,"男","女")例12函數(shù)身份證號碼2、7-12位數(shù)段表示生日，判斷年齡=YEAR(TODAY())-YEAR(TEXT(MID(,7,8),"#-00-00"))

函數(shù)upper與lower字母變大小寫=UPPER("Excel函數(shù)")

=LOWER("Excel函數(shù)")

函數(shù)char顯示函數(shù)=CHAR(ROW(A1)),row(A1)表示1，也就是char(1)返回一個對應(yīng)的符號，1-128會返回不同的符號，可以在做表格之前先把他們?nèi)旁谝涣?，直接取用，免得在插入菜單中一個個的選擇、點(diǎn)擊。見右示例，拖動生成人員A-人員Z序列。橫向生成用="人員"&CHAR(COLUMN(A1)+64)；縱向生成裝Column(A1)換成ROW(A1)人員B例13第18頁，課件共27頁，創(chuàng)作于2023年2月常用函數(shù)與公式類型知識點(diǎn)說明備注對應(yīng)示例號提升生成文本首字母返回指定文本的第一個字的拼音首字母=LOOKUP(CODE("張虹"),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，并在公式處輸入=get.cell(24,A1)—再插入一新列，在第一個單元格內(nèi)=A向下拖即可列出該列單元格的文本顏色參數(shù)，再做篩選

第19頁，課件共27頁，創(chuàng)作于2023年2月導(dǎo)讀常用函數(shù)與公式2結(jié)語4綜合應(yīng)用案例3前言1第20頁，課件共27頁，創(chuàng)作于2023年2月綜合應(yīng)用案例1、某業(yè)務(wù)部門業(yè)務(wù)員績效得分考核銷售收入、毛利兩項(xiàng)指標(biāo)，請你用系統(tǒng)導(dǎo)出的該部門4月銷售商品明細(xì)、客戶對應(yīng)業(yè)務(wù)員名單、業(yè)務(wù)員所轄客戶指標(biāo)表三張表格來核算業(yè)務(wù)員績效得分。（20分鐘）4月銷售商品明細(xì)客戶對應(yīng)業(yè)務(wù)員名單業(yè)務(wù)員所轄客戶指標(biāo)表業(yè)務(wù)員績效排序與分?jǐn)?shù)第21頁，課件共27頁，創(chuàng)作于2023年2月綜合應(yīng)用案例2、請運(yùn)用本課所學(xué)，在EXCEL中完成如下電子月歷（20分鐘）：要求：1）隨電腦內(nèi)置日歷變換；2）當(dāng)月1號位于A2；3）當(dāng)天日期格式自動顯示如圖；4）有“#月份”的自動顯示；5）其他格式如圖設(shè)置。第22頁，課件共27頁，創(chuàng)作于2023年2月綜合應(yīng)用案例3、請運(yùn)用本課所學(xué)，在EXCEL中制出如下一張“部門工作進(jìn)度表”（30分鐘）：要求：1）日期隨月份變換自行變換；2