Excel公式與函數(shù)

1、Excel系列培訓(xùn)之系列培訓(xùn)之- 函數(shù)與公式函數(shù)與公式 1:57:27目錄2前言前言公式與函數(shù)簡介公式與函數(shù)簡介常用函數(shù)實(shí)戰(zhàn)技巧常用函數(shù)實(shí)戰(zhàn)技巧1:57:28前言31:57:284幫助解讀按按F1進(jìn)入幫助進(jìn)入幫助1:57:285幫助解讀用途用途語法語法說明說明示例示例函數(shù)名函數(shù)名參閱部分，了解相關(guān)函數(shù)參閱部分，了解相關(guān)函數(shù)/公式公式1:57:28公式與函數(shù)簡介61:57:28公式與函數(shù)7什么是公式？ Excel中的公式是一種對(duì)工作表中的數(shù)值進(jìn)行計(jì)算的等式，它可以幫助用戶快速的完成各種復(fù)雜的運(yùn)算。公式以“=”開始，其后是公式的表達(dá)式，如“=A1+A2”什么是函數(shù)？ 預(yù)先定義的預(yù)先定義的執(zhí)行計(jì)算、

2、執(zhí)行計(jì)算、分析等處理分析等處理數(shù)據(jù)任務(wù)的數(shù)據(jù)任務(wù)的特殊公式特殊公式1:57:28公式的組成要素8單元格引用單元格引用=(A2+67)/SUM(B2:F2)*單價(jià)單價(jià)除法除法運(yùn)算符運(yùn)算符函數(shù)函數(shù)加法運(yùn)算符加法運(yùn)算符常量常量單元格區(qū)域引用單元格區(qū)域引用名稱名稱特征特征公式的輸入是公式的輸入是以以“ =”開始開始，公式的計(jì)算，公式的計(jì)算結(jié)果顯示在單元格結(jié)果顯示在單元格中，中，公式本身顯示在編輯欄公式本身顯示在編輯欄中。中。1:57:28公式的操作9公式的輸入與編輯公式的輸入與編輯選中公式所在單元格，直接輸入公式；選中公式所在單元格，并按下鍵輸入公式；雙擊公式所在單元格輸入公式選中公式所在單元格，單擊

3、列標(biāo)上方的編輯欄輸入公式。公式的刪除公式的刪除使用鍵與鍵可清除單元格中的內(nèi)容。公式的復(fù)制與填充公式的復(fù)制與填充5種方法實(shí)現(xiàn)公式的復(fù)制與填充方法一：拖拽填充；方法二：雙擊填充柄；方法三：快捷鍵填充，選中需要填充的區(qū)域，按；方法四：選擇性粘貼；方法五：多單元格同時(shí)輸入；1:57:28運(yùn)算符10類型類型符符 號(hào)號(hào)說說 明明實(shí)實(shí) 例例算術(shù)運(yùn)算符+,-,%, ,*,/負(fù)號(hào),百分號(hào),乘冪,乘和除加和減=8*-5 結(jié)果為-40=60*5% 結(jié)果為3=103 結(jié)果為1000=3*2/4 結(jié)果為1.5 比較運(yùn)算符=，,=,=等于，不等于，大于，小于，大于等于和小于等于=（A1=A2） 判斷A1與A2相等=（B1

4、“ABC”） 判斷B1不等于ABC=（C1=5） 判斷C1大于等于5文本運(yùn)算符&連接文本=“CP”&“IC” 返回“CPIC”區(qū)域運(yùn)算符:冒號(hào)=SUM（A1：A10） 引用A1至A10區(qū)域交叉運(yùn)算符_(空格)單個(gè)空格=SUM（A1：B5 A4：D9） 引用交叉區(qū)域聯(lián)合運(yùn)算符，逗號(hào)=if（3+2=5，true，false） 分隔參數(shù)1:57:28常量參數(shù)115種常量數(shù)值常量，如：=（1+2）*3。日期常量，如：=DATEDIF（“2014-5-20”,NOW（），“m”）。文本常量，如：=“中南”&“機(jī)誠”。邏輯值常量，如：=VLOOKUP（“張三”，A:D，2，F(xiàn)ALS

5、E）。錯(cuò)誤值常量，如：=COUNTIF（A:A，#DIV/0!）1:57:28單元格引用12相對(duì)引用相對(duì)引用 從屬單元格與引用單元格的相對(duì)位置從屬單元格與引用單元格的相對(duì)位置不變，當(dāng)復(fù)制或填充公式時(shí)，不變，當(dāng)復(fù)制或填充公式時(shí)，引用單元格將自動(dòng)調(diào)整。引用單元格將自動(dòng)調(diào)整。=A1絕對(duì)引用絕對(duì)引用 公式所引用的單元格絕對(duì)位置不變。公式所引用的單元格絕對(duì)位置不變。=$A$1混合引用混合引用 所引用單元格的行或列方向之一的絕對(duì)位置不變，而另一方向位所引用單元格的行或列方向之一的絕對(duì)位置不變，而另一方向位置發(fā)生變化置發(fā)生變化。=$A1提示提示在引用單元格后可通過按一次或多次在引用單元格后可通過按一次或多次

6、“F4”快速切換引用類型?？焖偾袚Q引用類型。1:57:28名稱的定義與使用13增強(qiáng)公式的可讀性；方便公式的統(tǒng)一修改；代替需要重復(fù)使用的公式，以簡化操作，提高效率；代替單元格區(qū)域存儲(chǔ)常量數(shù)據(jù)；解決數(shù)據(jù)有效性和條件格式中無法使用常量數(shù)組、交叉引用、跨表引用等問題。使用名稱的好處使用名稱的好處定義名稱定義名稱方法一：使用名稱管理器定義名稱方法二：使用名稱框快速創(chuàng)建名稱名稱命名的限制名稱命名的限制不能以數(shù)字開頭、不能以R、C、r、c作為名稱名、不能使用除下劃線、點(diǎn)號(hào)、和反斜線以外的其它符號(hào)、字符不能超過255個(gè)、字母不區(qū)分大小寫。1:57:29公式輸入注意事項(xiàng)14公式中可以包括：數(shù)值和字符、單元格地址

7、、區(qū)域、區(qū)域名字、函數(shù)等不要隨意包含空格公式中的字符要用半角引號(hào)括起來公式中運(yùn)算符兩邊的數(shù)據(jù)類型要相同 如：=“ab”+25 出錯(cuò) #VALUE1:57:29公式的常見錯(cuò)誤15錯(cuò)誤值類型錯(cuò)誤值類型原因原因/含義含義解決方法解決方法/說明說明#當(dāng)列寬不夠顯示數(shù)字，或者使用了負(fù)的日期、時(shí)間時(shí)，出現(xiàn)錯(cuò)誤將鼠標(biāo)指針移動(dòng)到該列的列標(biāo)右側(cè)，雙擊或拖動(dòng)鼠標(biāo)，調(diào)整列寬即可#VALUE當(dāng)使用的參數(shù)或?qū)ο箢愋湾e(cuò)誤或當(dāng)自動(dòng)更改公式功能不能更正公式時(shí)，出現(xiàn)錯(cuò)誤通過公式的錯(cuò)誤檢查發(fā)現(xiàn)問題，修改錯(cuò)誤值#DIV/0！當(dāng)數(shù)字被零（0）除時(shí)，出現(xiàn)錯(cuò)誤檢查錯(cuò)誤公式，修改公式中的單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。

8、#NAME？當(dāng)未識(shí)別公式中的文本時(shí)，如未加載宏或定義名稱，出現(xiàn)錯(cuò)誤出現(xiàn)這種錯(cuò)誤的具體原因也有很多，當(dāng)出現(xiàn)該情況時(shí)，一定要仔細(xì)地檢查公式和函數(shù)，以找出具體的解決方法。 #N/A當(dāng)數(shù)值對(duì)函數(shù)或公式不可用時(shí)，出現(xiàn)錯(cuò)誤修改數(shù)值#REF!當(dāng)單元格引用無效時(shí)，出現(xiàn)錯(cuò)誤重新引用單元格#NUM!公式或函數(shù)中使用無效數(shù)字值時(shí)，出現(xiàn)錯(cuò)誤修改數(shù)值#NULL!當(dāng)用空格表示兩個(gè)引用單元格之間的相交運(yùn)算符，但指定并不相交的兩個(gè)區(qū)域的交點(diǎn)時(shí)，出現(xiàn)錯(cuò)誤修改單元格區(qū)域1:57:29函數(shù)16=IF（A10,”正數(shù)正數(shù)”,IF（A1=18,符合要求,不符合要求)，確信以后，如果C26單元格中的數(shù)值大于或等于18，則C29單元格顯示

9、“符合要求”字樣，反之顯示“不符合要求”字樣。 特別提醒：特別提醒：本文中類似“在C29單元格中輸入公式”中指定的單元格，在使用時(shí)，并不需要受其約束。 IF1:57:29條件函數(shù)SUMIF35主要功能：主要功能：計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。 使用格式：使用格式：SUMIF（Range,Criteria,Sum_Range） 參數(shù)說明：參數(shù)說明：Range代表?xiàng)l件判斷的單元格區(qū)域；Criteria為指定條件表達(dá)式；Sum_Range代表需要計(jì)算的數(shù)值所在的單元格區(qū)域。 應(yīng)用舉例：應(yīng)用舉例：在c40單元格中輸入公式：=SUMIF(D3:D22,男,F3:F22)，確認(rèn)后即可求出“男”生

10、的語文成績和。d3性別計(jì)算：=IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女,男),IF(MOD(MID(C3,15,1),2)=0,女,男)特別提醒：特別提醒：如果把上述公式修改為： =SUMIF(D3:D22,”女,F3:F22)，即可求出“女”生的語文成績和； “男”和“女” 是文本型。 SUMIF1:57:29條件函數(shù)COUNTIF36主要功能：主要功能：統(tǒng)計(jì)某個(gè)單元格區(qū)域中符合指定條件的單元格數(shù)目。 使用格式：使用格式：COUNTIF(Range,Criteria) 參數(shù)說明：參數(shù)說明：Range代表要統(tǒng)計(jì)的單元格區(qū)域；Criteria表示指定的條件

11、表達(dá)式。 應(yīng)用舉例：應(yīng)用舉例：在C17單元格中輸入公式：=COUNTIF(f3:f22,”=80”)，確認(rèn)后，即可統(tǒng)計(jì)出f3至f22單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。 假如d3:d22區(qū)域內(nèi)存放著員工的性別，則公式“=COUNTIF(d3:d22，”女”)”統(tǒng)計(jì)其中的女職工數(shù)量 特別提醒：特別提醒：允許引用的單元格區(qū)域中有空白單元格出現(xiàn) COUNTIF1:57:29查找函數(shù)行列號(hào)37主要功能：主要功能：顯示所引用單元格的行/列標(biāo)號(hào)值。 使用格式：使用格式：ROW/COLUMN(reference) 參數(shù)說明：參數(shù)說明：reference為引用的單元格。 應(yīng)用舉例：應(yīng)用舉例：在C11單

12、元格中輸入公式：=COLUMN(B11)，確認(rèn)后顯示為2（即B列）。 特別提醒：特別提醒：如果在B11單元格中輸入公式：=COLUMN()，也顯示出2ROW/COLUMN1:57:29查找函數(shù)元素返回38主要功能：返回列表或數(shù)組中的元素值，此元素由行序號(hào)和列序號(hào)的索引值進(jìn)行確定。 使用格式：INDEX(array,row_num,column_num) 參數(shù)說明：Array代表單元格區(qū)域或數(shù)組常量；Row_num表示指定的行序號(hào)（如果省略row_num，則必須有 column_num）；Column_num表示指定的列序號(hào)（如果省略column_num，則必須有 row_num）。 應(yīng)用舉例：

13、如圖3所示，在F8單元格中輸入公式：=INDEX(A1:D11,4,3)，確認(rèn)后則顯示出A1至D11單元格區(qū)域中，第4行和第3列交叉處的單元格（即C4）中的內(nèi)容。 特別提醒：此處的行序號(hào)參數(shù)（row_num）和列序號(hào)參數(shù)（column_num）是相對(duì)于所引用的單元格區(qū)域而言的，不是Excel工作表中的行或列序號(hào)。 INDEX1:57:29查找函數(shù)位置查找39功能：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。 格式：MATCH(lookup_value,lookup_array,match_type) 參數(shù)說明：Lookup_value代表需要在數(shù)據(jù)表中查找的數(shù)值；Lookup_arr

14、ay表示可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域；Match_type表示查找方式的值（-1、0或1）。如果match_type為-1，查找大于或等于 lookup_value的最小數(shù)值。Lookup_array 必須按降序排列；如果match_type為1，查找小于或等于 lookup_value 的最大數(shù)值，Lookup_array 必須按升序排列；如果match_type為0，查找等于lookup_value 的第一個(gè)數(shù)值，Lookup_array 可以按任何順序排列；如果省略match_type，則默認(rèn)為1。 應(yīng)用舉例：如圖4所示，在F2單元格中輸入公式：=MATCH(E2,B1:B11

15、,0)，確認(rèn)后則返回查找的結(jié)果“9”。 特別提醒：Lookup_array只能為一列或一行。 MATCH1:57:30查找函數(shù)列查找40功能：在數(shù)據(jù)表的首列首列查找指定的數(shù)值，并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(shù)值。 格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 參數(shù)說明：Lookup_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號(hào)（當(dāng)Col_index_num為2時(shí),返回table_array第2列中的數(shù)值，為3時(shí)，返回第3列的值）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；