干貨史上最全的Excel常見函數用法

發(fā)布:未知文本批量工具

干貨!史上最全的excel常見函數用法!

在計算機二級office考試中，excel函數是重難點，很多小伙伴都表示無從下手，下面零壹學長就給大家分享一些常考的Excel函數公式，以備不時之需。

ABS()

功能：求絕對值

用法：ABS(number)，求參數number的絕對值。

例子：ABS(-3)的結果為3，ABS(3)的結果為3。

INT()

功能：取整

用法：INT(number)，取一個不大于參數number的最大整數。

例子：INT(3.9)的結果為3，INT(-3.9)的結果為-4。

ROUND()

功能：將數字進行四舍五入

用法：ROUND(number,n),其中n為整數，函數按指定n位數，對number進行四舍五入。當n>0,數字將被四舍五入到指定位數;當n=0,數字將被四舍五入成整數;當n<0,數字將被四舍五入到小數點左邊的指定位數。

例子：Round(11.43,1)，Round(11.43,0)，Round(11.43,-1)的結果分別為11.4，11，10。

MOD()

功能：求余數

用法：MOD(number1,number2),返回兩數相除的余數，結果的正負號與除數相同，其中number1為被除數,number2為除數

例子：MOD(4,3)等于1，MOD(-4,3)等于2，MOD(4,-3)等于-2，MOD(-4,-3)等于-1。

SQRT()

功能：求平方根

用法：SQRT(number)，求一個正數number的平方根

例子：SQRT(9)的結果為3。

SUM()

功能：求和

用法：SUM(數值區(qū)域),將指定的區(qū)域求和相加，參數可以是值、單元格引用或者區(qū)域

例子：例如=SUM(A2:A9)對A2到A9所有單元格的值進行求和,=SUM(A2:A9,C2:C9)將A2到A9的區(qū)域與C2到C9的區(qū)域相加進行求和。

SUMIF()

功能：條件求和

用法：SUMIF(區(qū)域1，條件，區(qū)域1)，區(qū)域1滿足條件的情況下對區(qū)域2進行求和。

例子：例如,公式 =SUMIF(A2:A5,”Tom”,C2:C5)只對區(qū)域C2:C5中在區(qū)域A2:A5中所對應的單元格等于“Tom”的值求和。

SUMPRODUCT()

功能：乘積之和

用法：SUMPRODUCT(數組1,數組2, …)

例子：SUMPRODUCT(B2:B11,C2:C11)=B2*C2+B3*C3+B4*C4+······+B11*C11

SUMIFS

功能：多條件求和

用法：SUMIFS(求和區(qū)域,條件區(qū)域1,條件1，[條件區(qū)域2，條件2],…)

例子：例如=SUMIFS(A2:A10,B2:B10,B12,C2:C10,A12),表示對單元格A2到A10中的某些值進行求和，其中這些值所在行中B列的值等于B12，C列的值等于A12。

AVERAGE()

功能：求平均值

用法：AVERAGE(number1,number2)，參數可以是數值、單元格或區(qū)域。

例子：=AVERAGE(A1:A6)將返回A1到A6這些數字的平均值。

AVERAGEIF()

功能：求給定條件的平均值

用法：AVERAGEIF(列1,值，列2)，列1滿足值的情況下，對列2求平均值

例子：假如E3:E17存放員工性別，F3:F17存放員工工資，若要求男性員工的平均工資可以輸入公式=AVERAGEIF(E3:E17,”男”,F3:F17)。

AVERAGEIFS()

功能：多條件求平均值

用法：AVERAGEIFS(求平均值區(qū)域，區(qū)域1，值1，區(qū)域2，值2)，找區(qū)域1=值1，并且區(qū)域2=值2的行，對求平均區(qū)域求平均值

例子：假如B2:B9存放性別，C2:C9存放班級，D2：D9存放成績，若要統(tǒng)計1班所有女生的平均分，則可以輸入公式=AVERAGEIFS(D2:D9,B2:B9,”女”,C2:C9,”1班”)。

COUNT()

功能：計數

用法：COUNT(value1,value2,…),對單元格區(qū)域中數據的個數進行計數。value1 是必需參數，value2,…為可選參數。

例子：=COUNT(A1:A20)。在這個示例中，如果此區(qū)域中有5個單元格包含數字，則答案就為5。

COUNTIF()

功能：條件計數

用法：COUNTIF(區(qū)域,條件),統(tǒng)計給定區(qū)域中符合指定條件的單元格數目。

例子：=COUNTIF(A1:A10,”>=60″)統(tǒng)計A1到A10區(qū)域中大于等于60的單元格數目。

COUNTIFS()

功能：多條件計數

用法：COUNTIF(區(qū)域1,條件1，[區(qū)域2,條件2],…),多區(qū)域計數，計算同時滿足各個判斷條件的數量。

例子：=COUNTIFS(A1:A10,”>8″,B1:B10,”<20″)表示統(tǒng)計A1到A10大于8且同一行B1到B10中數值小于20的單元格個數。

COUNTA()

功能：統(tǒng)計非空單元格個數

用法：COUNTA(值1, [值2], …)。值1是必需的，值2是可選的。用來計算范圍中不為空的單元格的個數。

例子：=COUNTA(A2:A7)表示計算單元格區(qū)域 A2 到 A7 中非空單元格的個數。

MAX()

功能：求最大值

用法：MAX(number1,number2,…), 求出一組數中的最大值，number1是必需的后續(xù)數字是可選的，參數可以是數值、單元格或區(qū)域

例子：如果A2:A6包含12,6,9,26,2，則=MAX(A2:A6,40)區(qū)域A2:A6和數值40之中的最大值，結果為40。

MIN()

功能：求最小值

用法：MIN(number1,number2,…), 求出一組數中的最大值，number1是必需的后續(xù)數字是可選的，參數可以是數值、單元格或區(qū)域。

例子：如果A2:A6包含12,6,9,26,2，則=MIN(A2:A6,40)區(qū)域A2:A6和數值40之中的最小值，結果為2。

RANK()

功能：數據排序

用法：RANK(值,序列/區(qū)域，排序方式)。求出某一數值在某個區(qū)域內的大小排位。排序方式為1指升序，為0指降序。

例子：例如，=RANK(A3,A2:A6,1)返回A3單元格中的數值在A2:A6范圍內的排位。

LARGE()

功能：取區(qū)域中第N大的值

用法：LARGE(區(qū)域,名次)。得到區(qū)域里的第N大的值

例子：假如B2:B6存放10,19,21,11,13，則=LARGE(B2:B6,3)表示B2:B6中第3大的值為13

TEXT()

功能：轉換成文本

用法：TEXT(數值, 格式)。用于把數字、日期轉為文本，轉換時要指定格式。

例子：=TEXT(A2,”yyyy年mm月dd日”)就將A2單元格轉換成yyyy年mm月dd日的格式。

TRIm()

功能：去掉字符前后空格

用法：TRIm(Text)。Text 為要去掉空格的文本。刪除字符前后的空格，字符與字符之間僅保留一個空格。

例子：假如去掉字符串” TRIM函數 “前后的空格，可以輸入公式 =TRIm(” TRIM函數 “)，則字符串” TRIM函數 “前后的空格都被去掉。

FIND()

功能：查找字符位置

用法：FIND(查找文本,源文本, [查找開始位置])。

例子：假如A1單元格存放字符串”excel中find函數怎么用”，要從A1單元格查找字符串”怎么”，則可以輸入公式=FIND(“怎么”,A1,5)，結果返回13。

LEFt()

功能：截取字符

用法：LEFt(文本字符串,提取的數量)。從文本字符串的第一個字符開始，截取指定個數的字符。

例子：假定A2單元格內容為”ABCDE”，則=LEFt(A2,4)表示截取A2的前4個字符，結果為ABCD。

RIGHt()

功能：截取字符

用法：RIGHt(文本字符串,提取的數量)。從文本字符串的最后一個字符開始，截取指定個數的字符。

例子：假定A2單元格內容為”ABCDE”，則=RIGHt(A2,4)表示截取A2的后4個字符，結果為BCDE。

MId()

功能：取字符串

用法：MId(文本字符串,起始位置,截取的數量)。返回文本字符串中從指定位置開始的特定數目的字符。

例子：假定A2單元格內容為”ABCDE”，則=MId(A2,2,3)表示從第2個位置開始截取A2的3個字符，結果為BCD。

LEN()

功能：統(tǒng)計字符數

用法：LEN(文本字符串)。統(tǒng)計文本字符串中的字符數。

例子：例如=LEN(“北京Beijing”)返回9。

LENB()

功能：統(tǒng)計字節(jié)數

用法：LEN(文本字符串)。統(tǒng)計字符串中的字節(jié)數。

例子：例如=LEN(“北京Beijing”)返回11。

IF()

功能：條件判斷

用法：IF(條件判斷，結果為真返回值,結果為假返回值)。根據指定條件來判斷其真或假。函數IF最多可以嵌套7層

例子：假如我們要計算出B2單元格中分數超過90的為優(yōu)秀，80分到90分的是合格，80分以下的是不合格，那么就可以輸入公式：=IF(B2>=90,”優(yōu)秀”,if(B2>=80,”合格”,”不合格”))。

IFERROR()

功能：判定是否是錯誤值

用法：IFERROR(值，出錯時返回的值)。根據給定的值進行檢查，如果計算結果返回錯誤，就返回給定的值

例子：假如A3=13,B3=0，在C3單元格輸入公式=IFERROR(A3/B3,”錯誤”)，因為B3是0，不能做除數，所以會返回“錯誤”字符。

VLOOKUP()

功能：查找值

用法：VLOOKUP(值,區(qū)域,第N列,匹配方式)。在區(qū)域首列查找值，得到查找區(qū)域第N列那個值，最后一個參數為0則表示精確匹配，為1則表示模糊匹配。

例子：=VLOOKUP(A1,$B$1:$B$10,2,0)意思是在B1:B10中查找A1，找到后返回同行的第2列的值。

LOOKUP()

功能：查找值

用法：LOOKUP(值,查找區(qū)域,返回值的范圍)。在查找區(qū)域中查找值，并返回另一個范圍中對應位置的值。

例子：例如：若C2:C8存放成績，B2:B8存放學生姓名，則=LOOKUP(76,C2:C8,B2:B8)表示得分76的學生的姓名。

INDEX()

功能：數據定位

用法：INDEX(區(qū)域,第N行,第M列)。返回區(qū)域中第N行第M列的值。

例子：如圖所示，在F2輸入=INDEX(A2:D10,4,2)，則會顯示出A2:D10區(qū)域中第4行第2列的值。

MATCH()

功能：匹配位置

用法：=MATCH(查找值，查找的范圍，查找方式)。確定列表中某個值的位置。查找的范圍只能為一列或一行，查找方式為0表示查找等于指定內容的第一個數值，為1則查找小于或等于指定內容的最大值，而且指定區(qū)域必須按升序排列，為-1則查找大于或等于指定內容的最小值，而且指定區(qū)域必須降序排列

例子：如圖所示，在F2輸入=MATCH(B5,B2:B10,0)，則結果為4。

AND()

功能：邏輯與

用法：AND(條件1,條件2, …)。滿足條件1且滿足條件2，即滿足所有判斷條件時返回TRUE;當條件中有一項或多項不滿足，則返回FALSE

例子：=AND(9-5=4,7>2)為TRUE;

=AND(9-5=4,5>8)為FALSE。

OR()

功能：邏輯或

用法：OR(條件1,條件2, …)。在參數中，任何一個參數邏輯為真，即返回邏輯值TRUE;只有全部為假時，才返回FALSE。

例子：=OR(TRUE,3+5=8)為TRUE;

=OR(1=1=3,2=3=6)為FALSE。

NOT()

功能：求相反值

用法：NOT(判斷條件)。

例子：=NOT(FALSE)為TRUE;=NOT(1+4=5)為FALSE。

DATE()

功能：轉換日期

用法：DATE(year,month,day)。返回指定日期的序列數

例子：=DATE(2019,9,29)為43737，返回代表2019年9月29日的序列數。

DATEDIF()

功能：計算日期之差

用法：DATEDIF(開始日期,結束日期,unit)。計算兩個日期之間的年數、月數、天數。unit為返回結果的代碼，”Y”返回整年數;”M”返回整月數;”D”返回整天數;”MD”返回日期時間段中天數的差，忽略年和月;”YD” 返回日期時間段中天數的差，忽略年;”YM” 返回日期時間段中月數的差，忽略日和年。

例子：計算出生日期為1976-4-1人的年齡，可以輸入：=DATEDIF(“1976-4-1″,TODAY(),”Y”);

若要計算日期為1976-4-1和當前日期的間隔月份數，可以輸入：=DATEDIF(“1976-4-1″,TODAY(),”M”)。

YEAR()

功能：求年份

用法：YEAR(number)。返回某日期的年份

例子：=YEAR(43737)為2019;=YEAR(“2019年9月29日”)返回2019。

MonTH()

功能：求月份

用法：MonTH(number)。返回序列數number相對應的某日期的月份數。

例子：=MonTH(“2019年9月29日”)將返回9。

DAY()

功能：求天數