作者：Old iron
下面是我在平時(shí)中經(jīng)常用到的16個(gè)函數(shù)，可能有一些大家經(jīng)常用到的我沒提到，也可能有一些寫的不周全的地方或者錯(cuò)誤的地方，希望與大家一起多多學(xué)習(xí)哈~
01
MID函數(shù)
函數(shù)定義：從一個(gè)文本字符串的指定位置開始,截取指定數(shù)目的字符
使用格式：MID(text, start_num, num_chars)
例子：
02
CONCATENATE函數(shù)
函數(shù)定義：將多個(gè)字符文本或單元格中的數(shù)據(jù)連接在一起,顯示在一個(gè)單元格中
使用格式：CONCATENATE(text1,text2,……)
重點(diǎn)：也可以用&（和號）運(yùn)算符代替函數(shù)CONCATENATE實(shí)現(xiàn)文本項(xiàng)的合并
例子：
03
AND函數(shù)
函數(shù)定義：檢測所有的條件是否為真
使用格式：AND(logical1,logical2,……logical30)
注意事項(xiàng)：
如果指定的區(qū)域中不包含邏輯值或數(shù)值時(shí),函數(shù)AND返回錯(cuò)誤值#VALUE!.
Logical1,logical2,……logical30表示待檢測的1到30個(gè)條件值,各條件值可為TRUE或FALSE
例子：
可以與IF函數(shù)連用
04
IF函數(shù)
函數(shù)定義：根據(jù)條件滿足與否返回不同的值
使用格式：
IF(logical_test,value_if_true,value_if_false)
白話：IF(條件,與條件一樣時(shí)運(yùn)算這個(gè),與條件不同時(shí)運(yùn)算這個(gè))
例子：
常規(guī)用法
嵌套使用
公式如下：
'=IF(C13>=$H$10,$I$10,IF(C13>=$H$9,$I$9,IF(C13>=$H$8,$I$8,IF(C13>=$H$7,$I$7,IF(C13>=$H$6,$I$6,IF(C13>=$H$5,$I$5,0))))))
05
DATEDIF函數(shù)
函數(shù)定義：計(jì)算期間內(nèi)的年數(shù)、月數(shù)、天數(shù)
使用格式：
DATEDIF(start_date,end_date,'y')
=DATEDIF(start_date,end_date,'m')
=DATEDIF(start_date,end_date,'d')
=DATEDIF(start_date,end_date,'ym')
=DATEDIF(start_date,end_date,'yd')
=DATEDIF(date1,date2,'md')
白話：DATEDIF(開始日期,結(jié)束日期,要計(jì)算的單位)
注意：
y:計(jì)算滿年數(shù),返回值為0以上的整數(shù)
m:計(jì)算滿月數(shù),返回值為0以上的整數(shù);d:計(jì)算滿日數(shù),返回值為0以上的整數(shù)
ym:計(jì)算不滿一年的月數(shù),返回值為1~11之間的整數(shù)
yd計(jì)算不滿一年的天數(shù),返回值為0~365之間的整數(shù)
md:計(jì)算不滿意一個(gè)月的天數(shù),返回值為0~30之間的整數(shù)
例子：
06
COUNTIF函數(shù)
函數(shù)定義：計(jì)算滿足條件的單元格計(jì)數(shù)
使用格式：COUNTIF(range,criteria)
白話：COUNTIF(要找的內(nèi)容所在的區(qū)域,要找的內(nèi)容)
注意事項(xiàng)：
指定的條件必須用 ' ' (雙引號括起來),如 '>=100、'男' 等.但,當(dāng)指定條件為引用單元格時(shí)無需雙引號括住.通配符使用參看SUMIF函數(shù)中的通配符說明
例子：
COUNTIF函數(shù)幾種用法：
求包含值139的單元格數(shù)量 '=COUNTIF($D$4:$D$14,139)
求包含負(fù)值的單元格數(shù)量 '=COUNTIF($C$4:$C$14,'<0')
求不等于0 的單元格數(shù)量 '=COUNTIF($C$4:$C$14,'<>0')
求大于等于5的單元格數(shù)量 '=COUNTIF($C$4:$C$14,'>=5')
求等于單元格B45中內(nèi)容的單元格數(shù)量 '=COUNTIF($B$4:$B$14,B4)
求大于單元格E45中內(nèi)容的單元格數(shù)量 '=COUNTIF($E$4:$E$14,'>'&E4)
求包含文本內(nèi)容的單元格數(shù)量 '=COUNTIF($B$4:$B$14,'*')
求包含六個(gè)字符內(nèi)容的單元格數(shù)量 '=COUNTIF($B$4:$B$14,'??????')
求在文本中任何位置包含單詞'文胸'字符內(nèi)容的單元格數(shù)量 '=COUNTIF($B$4:$B$14,'*文胸*')
求包含以英文'D'(不分大小寫)開頭內(nèi)容的單元格數(shù)量 '=COUNTIF($B$4:$B$14,'D*')
求包含當(dāng)前日期的單元格數(shù)量 '=COUNTIF($E$4:$E$14,TODAY())
求大于平均值的單元格數(shù)量 '=COUNTIF($D$4:$D$14,'>'&AVERAGE($D$5:$D$14))
與IF連用的幾種查找方式
統(tǒng)計(jì)區(qū)域內(nèi)不重復(fù)數(shù)據(jù)數(shù)
公式：'{=SUM(1/COUNTIF(D5:D14,D5:D14))}
這里其實(shí)輸入的是=SUM(1/COUNTIF(D5:D14,D5:D14))，然后按ctrl+shift+enter三鍵結(jié)束。
下面公式加了IF判斷是否是空格的嵌套,避免出現(xiàn)#DIV/0!錯(cuò)誤.
'{{=SUM(IF(D5:D14<>'',1/COUNTIF(D5:D14,D5:D14)))}}
或者可以用
=SUM(IF(ISBLANK(D5:D14),'',1/COUNTIF(D5:D14,D5:D14))),然后按ctrl+shift+enter三鍵結(jié)束。
07
SUMIF函數(shù)
函數(shù)定義：對滿足條件的單元格的數(shù)值求和
使用格式：SUMIF(range,criteria,sum_range)
參數(shù)解釋：
range：為用于條件判斷的單元格區(qū)域.指定作為搜索對象的單元格區(qū)域
Criteria：為確定哪些單元格將被相加求和的條件,其形式可以為數(shù)字、表達(dá)式、文本或通配符
Sum_range：是需要求和的實(shí)際單元格
幾種基本用法：
1. 以“文胸”開頭的任意文本的銷量
=SUMIF($B$4:$B$14,'文胸*',$C$4:$C$14)
注意：如果是“文胸~*”，則此時(shí)的“*”就是字符，不是通配符，需要準(zhǔn)確查找文本為“文胸*”的銷量合計(jì)
2. “文胸”后面一定是三個(gè)字符的文本的銷量
=SUMIF($B$4:$B$4,'文胸???',$C$4:$C$4)
注意：如果是“文胸~???”，則此時(shí)的“?”就是字符，不是通配符，需要準(zhǔn)確查找文本為“文胸???”的銷量合計(jì)
3. 銷售大于等于5件的銷售合計(jì)
=SUMIF($C$4:$C$14,'>=5',$C$4:$C$14)
4. 查找內(nèi)容為c20的銷售合計(jì)
=SUMIF($B$4:$B$14,C20,$C$4:$C$14)
08
DCOUNT函數(shù)
函數(shù)定義：計(jì)算滿足條件的數(shù)值的個(gè)數(shù)
使用格式：DCOUNT(database,field,criteria)
參數(shù)定義：
database: 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域.數(shù)據(jù)庫是包含一組相關(guān)數(shù)據(jù)的列表,其中包含相關(guān)信息的行為記錄,而包含數(shù)據(jù)的列為字段.列表的第一行包含著每一列的標(biāo)志項(xiàng).
Field: 指定函數(shù)所使用的數(shù)據(jù)列.列表中的數(shù)據(jù)列必須在第一行具有標(biāo)志項(xiàng).Field可以是文本,即兩端帶引號的標(biāo)志項(xiàng),如'使用年數(shù)'或'產(chǎn)量'；此外,Field也可以是代表列表中數(shù)據(jù)列位置的數(shù)字：1表示第一列,2表示第二列,等等.
Criteria: 為一組包含給定條件的單元格區(qū)域.可以為參數(shù)criteria指定任意區(qū)域,只要它至少包含一個(gè)列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格.
注意：
參數(shù)field為可選項(xiàng),如果省略,函數(shù)DCOUNT返回?cái)?shù)據(jù)庫中滿足條件criteria的所有記錄數(shù)
例子：
公式：
1. 指定項(xiàng)目的有效支出
=DCOUNT(B3:E9,E3,G3:G4)
這里的結(jié)果是2，返回的是10月8日和10月10日的值。
2. 指定項(xiàng)目的支出
=DCOUNT(B3:E9,,G3:G4)
參數(shù)field為可選項(xiàng),如果省略,函數(shù)DCOUNT返回?cái)?shù)據(jù)庫中滿足條件criteria的所有記錄數(shù)
這里的結(jié)果是3，返回的是10月8日，10月10日和10月15日的值。
09
ISERROR函數(shù)
函數(shù)定義：查看是否為錯(cuò)誤，查看是否值為任意錯(cuò)誤值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!).
使用格式：ISERROR(value)
參數(shù)定義：value為需要進(jìn)行檢驗(yàn)的數(shù)值。
注意事項(xiàng)：
S類函數(shù)的參數(shù)value是不可轉(zhuǎn)換的
IS類函數(shù)在用公式檢驗(yàn)計(jì)算結(jié)果時(shí)十分有用，當(dāng)它與函數(shù)IF結(jié)合在一起使用時(shí)，可以提供一種方法用來在公式中查出錯(cuò)誤值
這個(gè)函數(shù)比較簡單，大家可以隨意試一下。
10
VLOOKUP函數(shù)
（敲黑板，重點(diǎn)?。。?/strong>
函數(shù)定義：按照垂直方向搜索區(qū)域
使用格式：
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
參數(shù)定義：
Lookup_value：為需要在數(shù)組第一列中查找的數(shù)值.Lookup_value可以為數(shù)值、引用或文本字符串.
Table_array: 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表.可以使用對區(qū)域或區(qū)域名稱的引用,例如數(shù)據(jù)庫或列表.
Col_index: 為table_array中待返回的匹配值的列序號. Col_index_num為1時(shí),返回table_array第一列中的數(shù)值；col_index_num為2,返回table_array第二列中的數(shù)值,以此類推.如果col_index_num小于1,函數(shù)VLOOKUP返回錯(cuò)誤值值#VALUE!；如果col_index_num大于table_array的列數(shù), 函數(shù)VLOOKUP返回錯(cuò)誤值#REF!
Range_lookup：為一邏輯值,指明函數(shù)VLOOKUP返回時(shí)是精確匹配還是近似匹配.如果為TRUE或省略,則返回近似匹配值.也就是說.如果找不到精確匹配值,則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE,函數(shù)VLOOKUP將返回精確匹配值.如果找不到,則返回錯(cuò)誤值#N/A
注意：
如果range_lookup為TRUE,則table_array的第一列中的數(shù)值必須按升序排列：…、 -2、-1、0、1、2、…、-Z、FALSE、TRUE；否則,函數(shù)VLOOKUP不能返回正確的數(shù)值.如果range_lookup為FALSE,table_array不必進(jìn)行排序
Table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值
文本不區(qū)分大小寫
如果函數(shù)VLOOKUP找不到lookup_value,且range_lookup為TRUE,則使用小于等于lookup_value的最大值
如果lookup_value小于table_array第一列中的最小數(shù)值,函數(shù)VLOOKUP返回錯(cuò)誤值#N/A
如果函數(shù)VLOOKUP找不到lookup_value且range_lookup為FALSE，函數(shù)VLOOKUP返回錯(cuò)誤值#N/A
若有多個(gè)符合條件的情況:vlookup返回的是第一個(gè)滿足條件的值,lookup返回的是最后一個(gè)滿足條件的值
例子：
公式：=VLOOKUP(H5,B5:D13,3,0)
精確查找是vlookup最基本也是最常用的功能，對于數(shù)據(jù)量大的查找,其速度比菜單中的查找還快.設(shè)置vlookup第四個(gè)參數(shù)為false或0，即為精確查找。
11
LEN函數(shù)
函數(shù)定義：統(tǒng)計(jì)文本字符串中字符數(shù)目(計(jì)算文本的長度)
使用格式：LEN(text)
參數(shù)定義：是要查找其長度的文本.空格將作為字符進(jìn)行計(jì)數(shù).
例子：
=LEN(B13)，結(jié)果為7
12
SEARCH函數(shù)
函數(shù)定義：檢索字符位置(不區(qū)分大小寫)
使用格式：SEARCH(find_text,within_text,start_num)
參數(shù)定義：
Find_text：是要查找的文本.可以在find_text中使用通配符,包括問號(?)和星號(*).問號可匹配任意的單個(gè)字符,星號可匹配任意一串字符.如果要查找真正的問號或星號,請?jiān)谠撟址版I入波形符(~).
Within_text：是要在其中查找find_text的文本.
Start_num：是within_text中開始查找的字符的編號.
要點(diǎn)：使用start_num可跳過指定數(shù)目的字符.例如,假定使用文本字符 AYF0093.YoungMensApparel, 如果要查找文本字符串中說明部分的第一個(gè)Y的編號,則可將start_num設(shè)置為8,這樣就不會查找文本的序列號部分.SEARCH將從第8個(gè)字符開始查找,而在下一個(gè)字符處即可找到find_text,于是返回編號9.SEARCH總是從within_text的起始處返回字符編號，如果start_num大于1,也會對跳過的字符進(jìn)行計(jì)數(shù)
注意事項(xiàng)：
SEARCH和SEARCHB在查找文本時(shí)不區(qū)分大小寫
SEARCH和SEARCHB類似于FIND和FINDB,但FIND和FINDB區(qū)分大小寫
如果沒有找到find_text,則返回錯(cuò)誤值#VALUE!
如果忽略start_num,則假定其為1
如果start_num不大于0(零)或大于within_text,則返回錯(cuò)誤值#VALUE!
13
FREQUENCY函數(shù)
函數(shù)定義：計(jì)算區(qū)間里所含數(shù)值的個(gè)數(shù)
使用格式：FREQUENCY(data_array,bins_array)
參數(shù)定義：
Data_array：為一數(shù)組或?qū)σ唤M數(shù)值的引用,用來計(jì)算頻率.如果data_array中不包含任何數(shù)值,函數(shù)FREQUENCY返回零數(shù)組.
Bins_array:為間隔的數(shù)組或?qū)﹂g隔的引用,該間隔用于對data_array中的數(shù)值進(jìn)行分組.如果bins_array中不包含任何數(shù)值,函數(shù)FREQUENCY返回data_array中元素的個(gè)數(shù).
例子：
公式：{=FREQUENCY($C$19:$C$30,$E$19:$E$23)}
14
INDEX函數(shù)
1.INDEX函數(shù)（數(shù)組形式）
函數(shù)定義：(數(shù)組形式)返回行和列交叉位置的值
使用格式：INDEX(array,row_num,column_num)
參數(shù)定義：
Array: 為單元格區(qū)域或數(shù)組常量.如果數(shù)組只包含一行或一列,則相對應(yīng)的參數(shù)row_num或column_num為可選.如果數(shù)組有多行和多列,但只使用row_num或column_num,函數(shù)INDEX返回?cái)?shù)組中的整行或整列,且返回值也為數(shù)組.
Row_num: 數(shù)組中某行的行序號,函數(shù)從該行返回?cái)?shù)值.如果省略row_num,則必須有column_num.
Column_num: 數(shù)組中某列的列序號,函數(shù)從該列返回?cái)?shù)值.如果省略column_num,則必須有row_num.
要點(diǎn)：
INDEX函數(shù)有兩種語法形式：數(shù)組和引用.數(shù)組形式通常返回?cái)?shù)值或數(shù)值數(shù)組,引用形式通常返回引用.當(dāng)函數(shù)INDEX的第一個(gè)參數(shù)為數(shù)組常數(shù)時(shí),使用數(shù)組形式.
此處的行序號參數(shù)(row_num)和列序號參數(shù)(column_num)是相對于所引用的單元格區(qū)域而言的,不是Excel工作表中的行或列序號.
注意：
如果同時(shí)使用row_num和column_num,函數(shù)INDEX返回row_num和column_num交叉處的單元格的數(shù)值
如果將row_num或column_num設(shè)置為0,函數(shù)INDEX則分別返回整個(gè)列或行的數(shù)組數(shù)值.若要使用以數(shù)組形式返回的值,請將INDEX函數(shù)以數(shù)組公式形式輸入,對于行以水平單元格區(qū)域的形式輸入,對于列以垂直單元格區(qū)域的形式輸入.若要輸入數(shù)組公式,請按Ctrl+Shift+Enter
Row_num和column_num必須指向array中的某一單元格；否則,函數(shù)INDEX返回錯(cuò)誤值#REF!
2.INDEX函數(shù)（引用形式）
函數(shù)定義：(單元格引用方式)返回行和列交差位置的單元格引用
使用格式：
INDEX(reference,row_num,column_num,area_num)
參數(shù)定義：
Reference：對一個(gè)或多個(gè)單元格區(qū)域的引用,如果為引用輸入一個(gè)不連續(xù)的區(qū)域,必須用括號括起來.如果引用中的每個(gè)區(qū)域只包含一行或一列,則相應(yīng)的參數(shù)row_num或column_num分別為可選項(xiàng).例如,對于單行的引用,可以使用函數(shù)INDEX(reference,,column_num).
Row_num：引用中某行的行序號,函數(shù)從該行返回一個(gè)引用.
Column_num：引用中某列的列序號,函數(shù)從該列返回一個(gè)引用.
Area_num：選擇引用中的一個(gè)區(qū)域,并返回該區(qū)域中row_num和column_num的交叉區(qū)域.選中或輸入的第一個(gè)區(qū)域序號為1,第二個(gè)為2,以此類推.如果省略area_num,函數(shù)INDEX使用區(qū)域1.
注意事項(xiàng)：
在通過reference和area_num選擇了特定的區(qū)域后,row_num和column_num將進(jìn)一步選擇指定的單元格：row_num1為區(qū)域的首行,column_num1為首列,以此類推.函數(shù)INDEX返回的引用即為row_num和column_num的交叉區(qū)域
如果將row_num或column_num設(shè)置為0,函數(shù)INDEX分別返回對整個(gè)列或行的引用
Row_num、column_num和area_num必須指向reference中的單元格；否則,函數(shù)INDEX返回錯(cuò)誤值#REF!.如果省略row_num和column_num,函數(shù)INDEX返回由area_num所指定的區(qū)域
函數(shù)INDEX的結(jié)果為一個(gè)引用,且在其他公式中也被解釋為引用.根據(jù)公式的需要,函數(shù)INDEX的返回值可以作為引用或是數(shù)值.例如,公式CELL('width',INDEX(A1:B2,1,2))等價(jià)于公式CELL('width',B1).CELL函數(shù)將函數(shù)INDEX的返回值作為單元格引用.而在另一方面,公式2*INDEX(A1:B2,1,2)將函數(shù)INDEX的返回值解釋為B1單元格中的數(shù)字
例子：
公式：=INDEX((B4:D12,G4:H5),2,2,2)
返回的是第二個(gè)區(qū)域的值。
15
MATCH函數(shù)
函數(shù)定義：返回搜索值的相對位置
引用形式：
MATCH(lookup_value,lookup_array,match_type)
參數(shù)定義：
Lookup_value：為需要在數(shù)據(jù)表中查找的數(shù)值.為需要在Look_array中查找的數(shù)值.可以為數(shù)值(數(shù)字、文本或邏輯值)或?qū)?shù)字、文本或邏輯值的單元格引用.
Lookup_array：可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域.Lookup_array應(yīng)為數(shù)組或數(shù)組引用.
Match_type：為數(shù)字-1、0或1.Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value.
要點(diǎn)：函數(shù)MATCH返回lookup_array中目標(biāo)值的位置,而不是數(shù)值本身。例如,MATCH('b',{'a','b','c'},0)返回2,即“b”在數(shù)組{'a','b','c'}中的相應(yīng)位置
注意事項(xiàng)：
如果match_type為1,函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值.Lookup_array必須按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE
如果match_type為0,函數(shù)MATCH查找等于lookup_value的第一個(gè)數(shù)值.Lookup_array可以按任何順序排列
如果match_type為-1,函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值. Lookup_array必須按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...,等等
如果省略match_type,則假設(shè)為1
查找文本值時(shí),函數(shù)MATCH不區(qū)分大小寫字母
如果函數(shù)MATCH查找不成功,則返回錯(cuò)誤值#N/A
如果match_type為0且lookup_value為文本,lookup_value可以包含通配符、星號(*)和問號(?).星號可以匹配任何字符序列；問號可以匹配單個(gè)字符
例子：
公式：=MATCH(F5,B5:B14,1)
16
OFFSET函數(shù)
函數(shù)定義：計(jì)算指定位置的單元格引用
使用格式：OFFSET(reference,rows,cols,height,width)
參數(shù)定義：
Reference：作為偏移量參照系的引用區(qū)域.Reference必須為對單元格或相連單元格區(qū)域的引用；否則,函數(shù)OFFSET返回錯(cuò)誤值#VALUE!
Rows：相對于偏移量參照系的左上角單元格,上(下)偏移的行數(shù).如果使用5作為參數(shù)Rows,則說明目標(biāo)引用區(qū)域的左上角單元格比reference低5行.行數(shù)可為正數(shù)(代表在起始引用的下方)或負(fù)數(shù)(代表在起始引用的上方)
Cols：相對于偏移量參照系的左上角單元格,左(右)偏移的列數(shù).如果使用5作為參數(shù)Cols,則說明目標(biāo)引用區(qū)域的左上角的單元格比reference靠右5列.列數(shù)可為正數(shù)(代表在起始引用的右邊)或負(fù)數(shù)(代表在起始引用的左邊)
Height：高度,即所要返回的引用區(qū)域的行數(shù).Height必須為正數(shù)
Width：寬度,即所要返回的引用區(qū)域的列數(shù).Width必須為正數(shù)
注意事項(xiàng)：
如果行數(shù)和列數(shù)偏移量超出工作表邊緣,函數(shù)OFFSET返回錯(cuò)誤值#REF!
如果省略height或width,則假設(shè)其高度或?qū)挾扰creference相同
函數(shù)OFFSET實(shí)際上并不移動任何單元格或更改選定區(qū)域,它只是返回一個(gè)引用.函數(shù)OFFSET可用于任何需要將引用作為參數(shù)的函數(shù).例如,公式SUM(OFFSET(C2,1,2,3,1))將計(jì)算比單元格C2靠下1行并靠右2列的3行1列的區(qū)域的總值
公式：
=OFFSET(B5,6,2)
=SUM(OFFSET(B5,6,2,1,2))
本文為轉(zhuǎn)載分享，若侵權(quán)請聯(lián)系后臺刪除
互動話題“說出一種你常用的Excel函數(shù)”
聯(lián)系客服
