Excel函數(shù) | 數(shù)據(jù)分析最常用這16個！

楓雨書軒 2021-11-19

展開全文

作者：Old iron

文末領(lǐng)取Excel學(xué)習(xí)大禮包

（超全Excel常用模板+函數(shù)+快捷鍵）

下面是我在平時中經(jīng)常用到的16個函數(shù)，可能有一些大家經(jīng)常用到的我沒提到，也可能有一些寫的不周全的地方或者錯誤的地方，希望與大家一起多多學(xué)習(xí)哈~

MID函數(shù)

函數(shù)定義：從一個文本字符串的指定位置開始,截取指定數(shù)目的字符

使用格式：MID(text, start_num, num_chars)

例子：

CONCATENATE函數(shù)

函數(shù)定義：將多個字符文本或單元格中的數(shù)據(jù)連接在一起,顯示在一個單元格中

使用格式：CONCATENATE(text1,text2,……)

重點：也可以用&（和號）運算符代替函數(shù)CONCATENATE實現(xiàn)文本項的合并

例子：

AND函數(shù)

函數(shù)定義：檢測所有的條件是否為真

使用格式：AND(logical1,logical2,……logical30)

注意事項：
如果指定的區(qū)域中不包含邏輯值或數(shù)值時,函數(shù)AND返回錯誤值#VALUE!.
Logical1,logical2,……logical30表示待檢測的1到30個條件值,各條件值可為TRUE或FALSE

例子：

可以與IF函數(shù)連用

IF函數(shù)

函數(shù)定義：根據(jù)條件滿足與否返回不同的值

使用格式：

IF(logical_test,value_if_true,value_if_false)

白話：IF(條件,與條件一樣時運算這個,與條件不同時運算這個)

例子：

常規(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))))))

DATEDIF函數(shù)

函數(shù)定義：計算期間內(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é)束日期,要計算的單位)

注意：
y:計算滿年數(shù),返回值為0以上的整數(shù)
m:計算滿月數(shù),返回值為0以上的整數(shù);d:計算滿日數(shù),返回值為0以上的整數(shù)
ym:計算不滿一年的月數(shù),返回值為1~11之間的整數(shù)
yd計算不滿一年的天數(shù),返回值為0~365之間的整數(shù)
md:計算不滿意一個月的天數(shù),返回值為0~30之間的整數(shù)

例子：

COUNTIF函數(shù)

函數(shù)定義：計算滿足條件的單元格計數(shù)

使用格式：COUNTIF(range,criteria)

白話：COUNTIF(要找的內(nèi)容所在的區(qū)域,要找的內(nèi)容)

注意事項：
指定的條件必須用 ' ' (雙引號括起來),如 '>=100、'男' 等.但,當(dāng)指定條件為引用單元格時無需雙引號括住.通配符使用參看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,'*')
求包含六個字符內(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)計區(qū)域內(nèi)不重復(fù)數(shù)據(jù)數(shù)

公式：'{=SUM(1/COUNTIF(D5:D14,D5:D14))}

這里其實輸入的是=SUM(1/COUNTIF(D5:D14,D5:D14))，然后按ctrl+shift+enter三鍵結(jié)束。

下面公式加了IF判斷是否是空格的嵌套,避免出現(xiàn)#DIV/0!錯誤.

'{{=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é)束。

SUMIF函數(shù)

函數(shù)定義：對滿足條件的單元格的數(shù)值求和

使用格式：SUMIF(range,criteria,sum_range)

參數(shù)解釋：

range：為用于條件判斷的單元格區(qū)域.指定作為搜索對象的單元格區(qū)域
Criteria：為確定哪些單元格將被相加求和的條件,其形式可以為數(shù)字、表達(dá)式、文本或通配符
Sum_range：是需要求和的實際單元格

幾種基本用法：

1. 以“文胸”開頭的任意文本的銷量

=SUMIF($B$4:$B$14,'文胸*',$C$4:$C$14)

注意：如果是“文胸~*”，則此時的“*”就是字符，不是通配符，需要準(zhǔn)確查找文本為“文胸*”的銷量合計

2. “文胸”后面一定是三個字符的文本的銷量

=SUMIF($B$4:$B$4,'文胸???',$C$4:$C$4)

注意：如果是“文胸~???”，則此時的“?”就是字符，不是通配符，需要準(zhǔn)確查找文本為“文胸???”的銷量合計

3. 銷售大于等于5件的銷售合計

=SUMIF($C$4:$C$14,'>=5',$C$4:$C$14)

4. 查找內(nèi)容為c20的銷售合計

=SUMIF($B$4:$B$14,C20,$C$4:$C$14)

DCOUNT函數(shù)

函數(shù)定義：計算滿足條件的數(shù)值的個數(shù)

使用格式：DCOUNT(database,field,criteria)

參數(shù)定義：

database: 構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域.數(shù)據(jù)庫是包含一組相關(guān)數(shù)據(jù)的列表,其中包含相關(guān)信息的行為記錄,而包含數(shù)據(jù)的列為字段.列表的第一行包含著每一列的標(biāo)志項.

Field: 指定函數(shù)所使用的數(shù)據(jù)列.列表中的數(shù)據(jù)列必須在第一行具有標(biāo)志項.Field可以是文本,即兩端帶引號的標(biāo)志項,如'使用年數(shù)'或'產(chǎn)量'；此外,Field也可以是代表列表中數(shù)據(jù)列位置的數(shù)字：1表示第一列,2表示第二列,等等.

Criteria: 為一組包含給定條件的單元格區(qū)域.可以為參數(shù)criteria指定任意區(qū)域,只要它至少包含一個列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格.

注意：
參數(shù)field為可選項,如果省略,函數(shù)DCOUNT返回數(shù)據(jù)庫中滿足條件criteria的所有記錄數(shù)

例子：

公式：

1. 指定項目的有效支出

=DCOUNT(B3:E9,E3,G3:G4)

這里的結(jié)果是2，返回的是10月8日和10月10日的值。

2. 指定項目的支出

=DCOUNT(B3:E9,,G3:G4)

參數(shù)field為可選項,如果省略,函數(shù)DCOUNT返回數(shù)據(jù)庫中滿足條件criteria的所有記錄數(shù)

這里的結(jié)果是3，返回的是10月8日，10月10日和10月15日的值。

ISERROR函數(shù)

函數(shù)定義：查看是否為錯誤，查看是否值為任意錯誤值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!).

使用格式：ISERROR(value)

參數(shù)定義：value為需要進(jìn)行檢驗的數(shù)值。

注意事項：
S類函數(shù)的參數(shù)value是不可轉(zhuǎn)換的
IS類函數(shù)在用公式檢驗計算結(jié)果時十分有用，當(dāng)它與函數(shù)IF結(jié)合在一起使用時，可以提供一種方法用來在公式中查出錯誤值

這個函數(shù)比較簡單，大家可以隨意試一下。

VLOOKUP函數(shù)

（敲黑板，重點?。。?/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時,返回table_array第一列中的數(shù)值；col_index_num為2,返回table_array第二列中的數(shù)值,以此類推.如果col_index_num小于1,函數(shù)VLOOKUP返回錯誤值值#VALUE!；如果col_index_num大于table_array的列數(shù), 函數(shù)VLOOKUP返回錯誤值#REF!

Range_lookup：為一邏輯值,指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配.如果為TRUE或省略,則返回近似匹配值.也就是說.如果找不到精確匹配值,則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE,函數(shù)VLOOKUP將返回精確匹配值.如果找不到,則返回錯誤值#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返回錯誤值#N/A
如果函數(shù)VLOOKUP找不到lookup_value且range_lookup為FALSE，函數(shù)VLOOKUP返回錯誤值#N/A
若有多個符合條件的情況:vlookup返回的是第一個滿足條件的值,lookup返回的是最后一個滿足條件的值

例子：

公式：=VLOOKUP(H5,B5:D13,3,0)

精確查找是vlookup最基本也是最常用的功能，對于數(shù)據(jù)量大的查找,其速度比菜單中的查找還快.設(shè)置vlookup第四個參數(shù)為false或0，即為精確查找。

LEN函數(shù)

函數(shù)定義：統(tǒng)計文本字符串中字符數(shù)目(計算文本的長度)

使用格式：LEN(text)

參數(shù)定義：是要查找其長度的文本.空格將作為字符進(jìn)行計數(shù).

例子：

=LEN(B13)，結(jié)果為7

SEARCH函數(shù)

函數(shù)定義：檢索字符位置(不區(qū)分大小寫)

使用格式：SEARCH(find_text,within_text,start_num)

參數(shù)定義：

Find_text：是要查找的文本.可以在find_text中使用通配符,包括問號(?)和星號(*).問號可匹配任意的單個字符,星號可匹配任意一串字符.如果要查找真正的問號或星號,請在該字符前鍵入波形符(~).
Within_text：是要在其中查找find_text的文本.
Start_num：是within_text中開始查找的字符的編號.

要點：使用start_num可跳過指定數(shù)目的字符.例如,假定使用文本字符 AYF0093.YoungMensApparel, 如果要查找文本字符串中說明部分的第一個Y的編號,則可將start_num設(shè)置為8,這樣就不會查找文本的序列號部分.SEARCH將從第8個字符開始查找,而在下一個字符處即可找到find_text,于是返回編號9.SEARCH總是從within_text的起始處返回字符編號，如果start_num大于1,也會對跳過的字符進(jìn)行計數(shù)

注意事項：
SEARCH和SEARCHB在查找文本時不區(qū)分大小寫
SEARCH和SEARCHB類似于FIND和FINDB,但FIND和FINDB區(qū)分大小寫
如果沒有找到find_text,則返回錯誤值#VALUE!
如果忽略start_num,則假定其為1
如果start_num不大于0(零)或大于within_text,則返回錯誤值#VALUE!

FREQUENCY函數(shù)

函數(shù)定義：計算區(qū)間里所含數(shù)值的個數(shù)

使用格式：FREQUENCY(data_array,bins_array)

參數(shù)定義：

Data_array：為一數(shù)組或?qū)σ唤M數(shù)值的引用,用來計算頻率.如果data_array中不包含任何數(shù)值,函數(shù)FREQUENCY返回零數(shù)組.
Bins_array:為間隔的數(shù)組或?qū)﹂g隔的引用,該間隔用于對data_array中的數(shù)值進(jìn)行分組.如果bins_array中不包含任何數(shù)值,函數(shù)FREQUENCY返回data_array中元素的個數(shù).

例子：

公式：{=FREQUENCY($C$19:$C$30,$E$19:$E$23)}

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返回數(shù)組中的整行或整列,且返回值也為數(shù)組.
Row_num: 數(shù)組中某行的行序號,函數(shù)從該行返回數(shù)值.如果省略row_num,則必須有column_num.
Column_num: 數(shù)組中某列的列序號,函數(shù)從該列返回數(shù)值.如果省略column_num,則必須有row_num.

要點：

INDEX函數(shù)有兩種語法形式：數(shù)組和引用.數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組,引用形式通常返回引用.當(dāng)函數(shù)INDEX的第一個參數(shù)為數(shù)組常數(shù)時,使用數(shù)組形式.
此處的行序號參數(shù)(row_num)和列序號參數(shù)(column_num)是相對于所引用的單元格區(qū)域而言的,不是Excel工作表中的行或列序號.

注意：
如果同時使用row_num和column_num,函數(shù)INDEX返回row_num和column_num交叉處的單元格的數(shù)值
如果將row_num或column_num設(shè)置為0,函數(shù)INDEX則分別返回整個列或行的數(shù)組數(shù)值.若要使用以數(shù)組形式返回的值,請將INDEX函數(shù)以數(shù)組公式形式輸入,對于行以水平單元格區(qū)域的形式輸入,對于列以垂直單元格區(qū)域的形式輸入.若要輸入數(shù)組公式,請按Ctrl+Shift+Enter
Row_num和column_num必須指向array中的某一單元格；否則,函數(shù)INDEX返回錯誤值#REF!

2.INDEX函數(shù)（引用形式）

函數(shù)定義：(單元格引用方式)返回行和列交差位置的單元格引用

使用格式：

INDEX(reference,row_num,column_num,area_num)

參數(shù)定義：

Reference：對一個或多個單元格區(qū)域的引用,如果為引用輸入一個不連續(xù)的區(qū)域,必須用括號括起來.如果引用中的每個區(qū)域只包含一行或一列,則相應(yīng)的參數(shù)row_num或column_num分別為可選項.例如,對于單行的引用,可以使用函數(shù)INDEX(reference,,column_num).
Row_num：引用中某行的行序號,函數(shù)從該行返回一個引用.
Column_num：引用中某列的列序號,函數(shù)從該列返回一個引用.
Area_num：選擇引用中的一個區(qū)域,并返回該區(qū)域中row_num和column_num的交叉區(qū)域.選中或輸入的第一個區(qū)域序號為1,第二個為2,以此類推.如果省略area_num,函數(shù)INDEX使用區(qū)域1.

注意事項：
在通過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分別返回對整個列或行的引用
Row_num、column_num和area_num必須指向reference中的單元格；否則,函數(shù)INDEX返回錯誤值#REF!.如果省略row_num和column_num,函數(shù)INDEX返回由area_num所指定的區(qū)域
函數(shù)INDEX的結(jié)果為一個引用,且在其他公式中也被解釋為引用.根據(jù)公式的需要,函數(shù)INDEX的返回值可以作為引用或是數(shù)值.例如,公式CELL('width',INDEX(A1:B2,1,2))等價于公式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)

返回的是第二個區(qū)域的值。

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.

要點：函數(shù)MATCH返回lookup_array中目標(biāo)值的位置,而不是數(shù)值本身。例如,MATCH('b',{'a','b','c'},0)返回2,即“b”在數(shù)組{'a','b','c'}中的相應(yī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的第一個數(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ù)MATCH不區(qū)分大小寫字母
如果函數(shù)MATCH查找不成功,則返回錯誤值#N/A
如果match_type為0且lookup_value為文本,lookup_value可以包含通配符、星號(*)和問號(?).星號可以匹配任何字符序列；問號可以匹配單個字符

例子：

公式：=MATCH(F5,B5:B14,1)

OFFSET函數(shù)

函數(shù)定義：計算指定位置的單元格引用

使用格式：OFFSET(reference,rows,cols,height,width)

參數(shù)定義：

Reference：作為偏移量參照系的引用區(qū)域.Reference必須為對單元格或相連單元格區(qū)域的引用；否則,函數(shù)OFFSET返回錯誤值#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ù)

注意事項：
如果行數(shù)和列數(shù)偏移量超出工作表邊緣,函數(shù)OFFSET返回錯誤值#REF!
如果省略height或width,則假設(shè)其高度或?qū)挾扰creference相同
函數(shù)OFFSET實際上并不移動任何單元格或更改選定區(qū)域,它只是返回一個引用.函數(shù)OFFSET可用于任何需要將引用作為參數(shù)的函數(shù).例如,公式SUM(OFFSET(C2,1,2,3,1))將計算比單元格C2靠下1行并靠右2列的3行1列的區(qū)域的總值

公式：

=OFFSET(B5,6,2)

=SUM(OFFSET(B5,6,2,1,2))

END

本文為轉(zhuǎn)載分享，若侵權(quán)請聯(lián)系后臺刪除

今日福利

互動話題“說出一種你常用的Excel函數(shù)”

評論區(qū)留言分享即可獲得愛數(shù)據(jù)精選Excel學(xué)習(xí)大禮包，超全Excel常用模板+快捷鍵+函數(shù)，人手一份，速速來領(lǐng)哦~