幾個常用excel函數(shù)用法

1、文檔來源為：從網(wǎng)絡(luò)收集整理.word 版本可編輯.歡迎下載支持1=IF(ISNA(VL00KUP(A3,錄入請用字段!$A$2:$C$9,2,0),VLOOKUP(A3,錄入請用字段!$A$2:$C$9,2,0)2=IF(ISNA(VLOOKUP(A3,錄入請用字段!$A$3:$C$9,3,0),VLOOKUP(A3,錄入請用字段!$A$3:$C$9,3,0)3=IF(A3=,J3+D3+30)4=IF(A3=,F3-M3)5=IF(K3=,(TODA Y()-K3)6=IF(J3=,IF(K3=TODAY(),未到期,到期)7=IF(O3=0,N3,)8=IF(030,0330,03=60)

2、,N3,0)一、求各種類型單元格的個數(shù)(1)求真空單元格個數(shù) :=COUNTIF(數(shù)據(jù)區(qū),=)(2)非真空單元格個數(shù)：=COUNTIF(數(shù)據(jù)區(qū),)相當(dāng)于 counta()函數(shù)(3)文本型單元格個數(shù)：=COUNTIF(數(shù)據(jù)區(qū),*) 假空單元格也是文本型 單元格(4)區(qū)域內(nèi)所有單元格個數(shù)：=COUNTIF(數(shù)據(jù)區(qū),”)(5)邏輯值為 TRUE 的單元格數(shù)量 =COUNTIF(數(shù)據(jù)區(qū)，TRUE)小說明：EXCEL 單元格 內(nèi)數(shù)據(jù)主要有以下幾類：數(shù)值型，文本型，邏輯型，錯誤值型。其中時間類型也是一種特殊的數(shù)值。文本類型的數(shù)字是文本型?？諉卧瘢褐甘裁磧?nèi)容也沒有的單元格，姑且稱之為真空。假空單元格：指

3、0 字符的空文本，一般是由網(wǎng)上下載來的或公式得來的，姑且稱之為假空。date 指單元格區(qū)域，該參數(shù)不能是數(shù)組二、求 50)(2)等于 50=COUNTIF 數(shù)據(jù)區(qū),50)(3)小于 50 = COUNTIR 數(shù)據(jù)區(qū),=50)文檔來源為：從網(wǎng)絡(luò)收集整理.word 版本可編輯.歡迎下載支持11=IF(AND(O330,O390,N3,0)=A1 &明細(xì)表 =IF(ROW(錄入表！1:1)COUNTIF(錄入表!$A:$A,$A$1),INDEX(錄入表!A:A ,SMALL(IF(錄入表!$A$3:$A$1000=$A$1,ROW(錄入 表!$A$3:$A$1000),ROW(錄入表!1:

4、1)=A1 &明細(xì)表=IF(ROW(錄入表！1:1)COUNTIF(錄入表!$C:$C,$A$1),INDEX(錄入表!A:A,SMALL(IF(錄入表!$C$3:$C$200=$A$1,ROW(錄入表!$C$3:$C$200),ROW(錄入表!1：1)(5)小于或等于 50 =COUNTIF(數(shù)據(jù)區(qū),&$E$5)(7)等于 E5 單元格的值=COUNTIH 數(shù)據(jù)區(qū),$E$5)(8)小于 E5 單元格的值=COUNTIF(數(shù)據(jù)區(qū),=&$E$5)(10) 小于或等于 E5 單元格的值 =COUNTIF(數(shù)據(jù)區(qū),10 并且 &10,15)*1,-1)(2) =10

5、并且 =&10,15)*1,-1)(3) =10 并且 =10,15)*1,-1)文檔來源為：從網(wǎng)絡(luò)收集整理.word 版本可編輯.歡迎下載支持(4)10 并且 10,=15)*1,-1)注：一般多條件計數(shù)使用SUMPRODUC 函數(shù)，以上方法較少使用，僅供參考。補(bǔ)充：三個區(qū)域計數(shù)：三個區(qū)域中=60=SUM(COUNTIF(INDIRECT(a46:a48,b48:b50,c47:c48),=60)五、各種特殊統(tǒng)計要求計算A2: A32 為存放數(shù)據(jù)的區(qū)域(1 )非空文本(僅包括可鍵入文本)=COUNTIRA2:A32,=!)(2)所有非空文本=COUNTIF(A2:A32,=!)+CO

6、UNTIF(A2:A32,=&CHAR(1)-COUNTIF(A2:A32, =)或=SUM(COUNTIF(A2:A32,=&!,)*1,-1)+COUNTIF(A2:A32,=&CHAR(1)(3 )全部可見單元格=SUM(N(IF(ISERROR(A2:A32),1,SUBSTITUTE(A2:A32, ,)(4 )有效可見單元格=COUNTIF(A2:A32,=!)+COUNTIF(A2:A32,=&CHAR(1)-COUNTIF(A2:A32,=)+COUNT(A2:A32)(5 )全部不見 單元格(真空+空格+空文本)=COUNTIF(A2:A32,

7、)-COUNTIF(A2:A32,=!)+COUNTIF(A2:A32,=)(6) 空格 =COUNTIF(A2:A32,= )-COUNTIF(A2:A32,=!)(7) 空文本=COUNTIF(A2:A32,)-COUNTIF(A2:A32,=)(8) 邏輯與錯誤=COUNTIF(A2:A32,)-COUNTIF(A2:A32,*)-COUNT(A2:A32)ISNA 函數(shù)使用說明在使用 vlookup 函數(shù)時，如果查詢表單中的第一列在被查詢表單的第一列中沒有 時，則返回“#N/A”，表示返回值不可用。這帶來一個問題，就是“#N/A”單元格所在的列和行都不能進(jìn)行加和(如sum()或其他計算

8、了，因為最后的計算結(jié)果肯定也是“ #N/A”。因此，必須將 “#N/A通過某種方法改成空或0，在 excel 中一般需要用 if 函數(shù)結(jié)合 isna 函數(shù)來進(jìn)行。比如我在 3 月這個 sheet 里面的第 2 列中查詢，以達(dá)到返回和 B46 一樣名稱的數(shù) 值，就要用到這個函數(shù)： VLOOKUP(B46,3 月!B:C,2,FALSE)。如果經(jīng)過查詢，發(fā)現(xiàn) B46 這個指標(biāo)在 3月這個 sheet 中沒有，則返回#N/A。前面已經(jīng)說了， #N/A 是不能進(jìn)行加減等運(yùn)算的，這樣就將函數(shù)改寫成：=IF(ISNA(VLOOKUP(B46,3 月!B:C,2,FALSE),VLOOKUP(B46,3 月

9、 !B:C,2,FALSE)意思是：如果 vlookup 返回的是#N/A，則總體上返回空。如果vlookup 返回值是有效值，則返回 vlookup 函數(shù)的返回值。VLOOKUP(lookup_value,table_array,col_ in dex_ nu m,ra nge_lookup)參數(shù)簡單說明輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域coln dex_ num返回數(shù)據(jù)在區(qū)域的第幾列數(shù)正整數(shù)ran ge_lookup模糊匹配TRUE(或不填)/FALSELookup_value 參數(shù)說明Lookup_value為

10、需要在數(shù)據(jù)表第一列中進(jìn)行查找的數(shù)值。Lookup_value可以為數(shù)值、引用或文本字符串。Table_array參數(shù)說明Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。使用對區(qū)域或區(qū)域名稱的引用。coln dex_ num 參數(shù)說明col_index_num為 table_array中待返回的匹配值的列序號。coln dex_ num 為 1 時，返回 table_array第一列的數(shù)值，col_i ndex_ num為 2 時，返回 table_array第二列的數(shù)值，以此類推。如果col_index_num小于 1，函數(shù) VLOOKUP 返回錯誤值 #VALUE!;如果 col_ind

11、ex_num 大于 table_array 的列數(shù)，函數(shù) VLOOKUP 返回錯誤值#REF!。Ran ge_lookup 參數(shù)說明Ran ge_lookup 為一邏輯值，指明函數(shù)VLOOKUP 查找時是精確匹配，還是近似匹配。如果為 true 或省略，則返回近似匹配值。也就是說，如果找不到 精確匹配值，則返回小于lookup_value的最大數(shù)值。如果ran ge_lookup為false 或 0，函數(shù) VLOOKUP 將查找精確匹配值，如果找不到，則返回錯誤值#N/A。使用舉例如圖vlookup 函數(shù)示例100003、100004、100005、100007、100010I4:I8 中。一

12、個一個的手動查找在數(shù)據(jù)VLOOKUP!數(shù)演示：首先在 I4 單元格輸入“ =Vlookup( ”，此時 Excel 就會提示 4 個參數(shù)。所示，我們要在A2:F12 區(qū)域中提取五人的全年總計銷量， 并對應(yīng)的輸入到 量大的時候十分繁瑣，因此這里使用文檔來源為：從網(wǎng)絡(luò)收集整理.word 版本可編輯.歡迎下載支持Vlookup 結(jié)果演示第一個參數(shù)，很顯然，我們要讓100003 對應(yīng)的是 I4，這里就輸入“H4, ”文檔來源為：從網(wǎng)絡(luò)收集整理.word 版本可編輯.歡迎下載支持第二個參數(shù)，這里輸入我們要查找的區(qū)域(絕對引用)，即“YA$2:$F$12, ”；第三個參數(shù)，“全年總計”是區(qū)域的第六列，所以

13、這里輸入“6 ”，輸入“ 5”就會輸入第四季度的項目了；第四個參數(shù)，因為我們要精確的查找 工號，所以輸入“FALSE、 “0”。最后補(bǔ)全最后的右括號“)”，得到公式“ =VLOOKUP(H4,$A$2:$F$12,6,0)”，使用填充柄填充其他單元格即可完成查找操作。VLOOKUP 函數(shù)使用注意事項說到 VLOOKUF數(shù)，相信大家都會使用，而且都使用得很熟練了。不過，有幾個細(xì)節(jié)問題，大家在使用時還是留心一下的好。一.VLOOKUP 勺語法VLOOKUF數(shù)的完整語法是這樣的：VLOOKUP(lookup_value,table_array,col_ in dex_ nu m,ra nge_loo

14、kup)1 .括號里有四個參數(shù)，是必需的。最后一個參數(shù)ran ge_lookup 是個邏輯值，我們常常輸入一個 0 字，或者 False;其實(shí)也可以輸入一個1 字，或者 true。兩者有什么區(qū)別呢？前者表示的是完整尋找，找不到就傳回錯誤值#N/A ;后者先是找一模一樣的，找不到再去找很接近的值，還找不到也只好傳回錯誤值 #N/A。這對我們其實(shí)也沒有什么實(shí)際意義，只是滿足好奇而已，有興趣的朋友 可以去體驗體驗。2.Lookup_value 是一個很重要的參數(shù)，它可以是數(shù)值、文字字符串、或 參照地址。我們常常用的是參照地址。用這個參數(shù)時，有三點(diǎn)要特別提醒：A)參照地址的 單元格格式類別與去搜尋的單

15、元格格式的類別要一致，否則 的話有時明明看到有資料，就是抓不過來。特別是參照地址的值是數(shù)字時，最為明顯，若搜尋的 單元格 格式類別為文字，雖然看起來都是123，但是就是抓不出東西來的。而且格式類別在未輸入數(shù)據(jù)時就要先確定好，如果數(shù)據(jù)都輸入進(jìn)去了，發(fā) 現(xiàn)格式不符，已為時已晚，若還想去抓，則需重新輸入。B) 第二點(diǎn)提醒的，是使用時一個方便實(shí)用的小技巧，相信不少人早就知道了的。我們在使用參照地址時，有時需要將 lookup_value 的值固定在一個格子內(nèi)，而又要使用下拉方式(或復(fù)制)將函數(shù)添加到新的單元格中去，這里就要用到“ $”這個符號了，這是一個起固定作用的符號。比如說我始終想以D5 格式來抓

16、數(shù)據(jù)，則可以把D5 弄成這樣：$D$5，則不論你如何拉、復(fù)制，函數(shù)始終都會以 D5 的值來抓數(shù)據(jù)。C)用“ &連接若干個 單元格的內(nèi)容作為查找的參數(shù)。在查找的數(shù)據(jù)有類 似的情況下可以做到事半功倍。3.Table_array是搜尋的范圍，col_index_num是范圍內(nèi)的欄數(shù)。Col_i ndex_num不能小于 1，其實(shí)等于 1 也沒有什么實(shí)際用的。如果出現(xiàn)一個這樣的錯誤的值 #REF!，則可能是 colndex_num 的值超過范圍的總字段數(shù)。4. 在使用該函數(shù)時，lookup_value 的值必須在 table_array中處于第一列。二.VLOOKUP 勺錯誤值 處理。我們都知

17、道，如果找不到數(shù)據(jù)，函數(shù)總會傳回一個這樣的錯誤值#N/A，這錯誤值其實(shí)也很有用的。比方說，如果我們想這樣來作處理：如果找到的話， 就傳回相應(yīng)的值，如果找不到的話，我就自動設(shè)定它的值等于0，那函數(shù)就可以寫成這樣：=if(iserror(vlookup(1,2,3,0),0,vlookup(1,2,3,0)在 Excel 2007以上版本中，以上公式等價于=IFERROR(vlookup(1,2,3,0),0)這句話的意思是這樣的：如果VLOOKUP!數(shù)返回的值是個錯誤值的話(找不到數(shù)據(jù))，就等于0,否則，就等于VLOOKUR1數(shù)返回的值(即找到的相應(yīng)的值)。這里面又用了兩個函數(shù)。第一個是 ise

18、rror函數(shù)。它的語法是iserror(value)，即判斷括號內(nèi)的值是否為錯誤值，如果是，就等于 true，不是，就等于false。文檔來源為：從網(wǎng)絡(luò)收集整理.word 版本可編輯.歡迎下載支持第二個是 if 函數(shù)，這也是一個常用的函數(shù)的，后面有機(jī)會再跟大家詳細(xì)講解。它的語法是if(條件判斷式，結(jié)果 1，結(jié)果 2)。如果條件判斷式是對的，就執(zhí)行結(jié)果 1,否則就執(zhí)行結(jié)果2。舉個例子：=if(D2= ” , ”空的”，”有東西”)，意思是如D2 這個格子里是空的值，就顯示文字“空的”，否則，就顯示“有東西”。(看起來簡單吧？其實(shí)編程序，也就是這樣子判斷來判斷去的。)文檔來源為：從網(wǎng)絡(luò)收集整理.w

19、ord 版本可編輯.歡迎下載支持在 Excel 2007以上版本中，可以使用iferror（value, value_if_error）代替以上兩個函數(shù)的組合，該函數(shù)判斷value 表達(dá)式是否為錯誤值，如果是，則返回 value_if_error ，如果不是，則返回 value 表達(dá)式自身的值。三.含有 VLOOKUP!數(shù)的工作表檔案的處理。一般來說，含有VLOOKUF 函數(shù)的 工作表，如果又是在別的檔案里抓取數(shù)據(jù)的話，檔案往往是比較大的。尤其是當(dāng)你使用的檔案本身就很大的時候，那每 次開啟和存盤都是很受傷的事情。有沒有辦法把 文件壓縮 一下，加快開啟和存盤的速度呢。這里提供一個小 小的經(jīng)驗。在

20、工作表 里，點(diǎn)擊工具一一選項一一計算，把上面的更新遠(yuǎn)程參照和儲存 外部連結(jié)的勾去掉，再保存檔案，則會加速不少，不信你可以試試。下面詳細(xì)的說一下它的原理。1 .含有 VLOOKUF 函數(shù)的工作表，每次在保存檔案時，會同時保存一份其外部連結(jié)的檔案。這樣即使在單獨(dú)打開這個工作表時，VLOOKUF 函數(shù)一樣可以抓取到數(shù)值。2 .在工作表打開時，微軟會提示你，是否要更新遠(yuǎn)程參照。意思是說，你要不要連接最新的外部檔案，好讓你的VLOOKUF 函數(shù)抓到最新的值。如果你有足夠的耐心，不妨試試。3 .了解到這點(diǎn)，我們應(yīng)該知道，每次單獨(dú)打開含有VLOOKUF 函數(shù)的 工作表時，里面抓取外部檔案的數(shù)值，只是上次我們

21、存盤時保存的值。若要連結(jié)最新的值，必須要把外部檔案同時打開。IF（logical_test,valuef_true,value_if_false）Logical_test表示計算結(jié)果為TRUE 或 FALSE 的任意值或表達(dá)式。例如，A10=100 就是一個 邏輯表達(dá)式，如果 單元格 A10 中的值等于 100， 表達(dá)式即為TRUE，否則為 FALSE。本參數(shù)可使用任何比較運(yùn)算符（一個標(biāo)記 或符號，指定 表達(dá)式內(nèi)執(zhí)行的計算的類型。有數(shù)學(xué)、比較、邏輯和引用運(yùn)算符 等。）。Value_if_true logical_test為 TRUE 時返回的值。例如，如果本參數(shù)為文本字符串預(yù)算內(nèi)”而且 log

22、ical_test參數(shù)值為TRUE 貝 U IF 函數(shù)將顯示文本“預(yù)算內(nèi)”。如果logical_test 為 TRUE 而value_if_true 為空，則本參數(shù)返回 0 （零）。如果要顯示TRUE，則請為本參數(shù)使用邏輯值TRUE。value_if_true也可以是其他公式。Value_if_false logical_test為 FALSE 時返回的值。例如，如果本參數(shù)為文本字符串超出預(yù)算”而且logical_test參數(shù)值為 FALSE，貝 U IF 函數(shù)將顯示文本“超出預(yù)算”。如果logical_test為FALSE 且忽略了 value_if_false （即 value_if_tr

23、ue后沒有逗號），則會返回邏輯值 FALSE。如果 logical_test 為 FALSE 且 value_if_false為空（即value_if_true后有逗號，并緊跟著右括號），則本參數(shù)返回0 （零）。VALUE_if_false也可以是其他公式。說明函數(shù) IF可以嵌套七層，用value_if_false及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測條件。請參閱下面最后一個示例。在計算參數(shù)value_if_true 和 value_if_false后，函數(shù) IF 返回相應(yīng)語句執(zhí)行后的返回值。如果函數(shù) IF的參數(shù)包含數(shù)組（用于建立可生成多個結(jié)果或可對在行和列中排列的一組參數(shù)進(jìn)行

24、運(yùn)算的單個公式。數(shù)組區(qū)域共用一個公式；數(shù)組 常量是用作參數(shù)的一組常量），則在執(zhí)行IF 語句時，數(shù)組中的每一個元素都將計算。 WPS 表格還提供了其他一些函數(shù)，可依據(jù)條件來分析數(shù)據(jù)。例如，如 果要計算單元格區(qū)域中某個文本字符串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIf工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本字符串或數(shù)字求和，則可使 用 SUMIf 工作表函數(shù)。請了解關(guān)于根據(jù)條件計算值。如果判斷標(biāo)準(zhǔn)有漢字內(nèi)容，則在漢字前后加上英文狀態(tài)下的雙引號G2（例如:IF（G2=成都,400,200）示例如果您將示例復(fù)制到空白工作表中，可能會更易于理解該示例。示例A1數(shù)據(jù)250公式說明（結(jié)果）=IF(A28

25、9,A,IF(A279,B,IF(A269,C,IF(A259,D,F)為第一個 成績指定 一個字母等級(F)=IF(A389,A,IF(A379,B,IF(A369,C,IF(A359,D,F)為第二個 成績指定 一個字母 等級(A)=IF(A489,A,IF(A479,B,IF(A469,C,IF(A459,D,F)為第三個 成績指定 一個字母等級(C)在上例中，第二個 IF語句同時也是第一個IF 語句的參數(shù) value_if_false同樣，第三個 IF語句是第二個IF 語句的參數(shù) value_if_false 。例如，如果第一個 logical_test (AVERAGE 89)為 TRUE，則返回“ A”；如果第個 logical_test 為 FALSE，則計算第二個IF 語句，以此類推。用下列關(guān)鍵字指定數(shù)字的字母等級。A1成績2 V45390478示例三示例二AB1實(shí)際費(fèi)用預(yù)算費(fèi)用2150090035009004500925公式說明(結(jié)果)=IF(A2B2,OverBudg