工作中最常用的十個函數公式，看看哪個還不熟？

06-19

1、 IF函數

用來完成非此即彼的判斷。

如下圖，要判斷成績是否合格。

公式：

=IF(C2>=60,"及格","不及格")

用法：

=IF(判斷條件,符合條件時返回的值,不符合條件時返回的值)

2、 SUMIF函數

按指定條件求和，報表匯總中的江湖一姐。

如下圖，要在信息表中計算指定部門的總成績。

公式：

=SUMIF(B:B,G3,E:E)

常規用法：

=SUMIF(條件區域,指定的條件,求和區域)

1、指定條件時，可以使用通配符。

例如：=SUMIF(B:B,"*亞",E:E)

不管B列是「櫻井莉亞」還是「小澤瑪利亞」，只要包含字元"亞"，就對E列對應單元格中的數值進行求和匯總。

2、求和區域和條件區域要大小一致，並且要注意兩者的起始位置需保持一致。

3、 COUNTIF

統計符合指定條件的單元格個數，看家本領就是按條件計數。

如下圖，要在信息表中計算蒼老師的課時數。

公式：

=COUNTIF(C2:C10,E3)

常規用法：

=COUNTIFIF(條件區域,指定的條件)

指定條件時，可以使用通配符。

4、常規查詢

如下圖所示，要根據G2單元格姓名，在A~E數據區域中查詢對應的年齡。

公式為：

=VLOOKUP(G2,B1:E6,4,0)

使用方法是：

=VLOOKUP(要找誰,在哪個區域找,找哪一列的內容,匹配公式)

注意第三參數，是指定要返回查詢區域中第幾列的內容，不是整個工作表的第幾列。

5、逆向查詢

如下圖所示，要根據G2單元格姓名，在A~E數據區域中查詢對應的工號。

公式為：

=LOOKUP(1,0/(G2=B2:B6),A2:A6)

使用方法為：

=LOOKUP(1,0/(條件區域=指定條件),要返回的區域)

可以根據需要，將公式中的0/(條件區域=指定條件)，寫成：

0/((條件區域1=指定條件1)*(條件區域2=指定條件2)*……)

從而實現任意角度的多條件查詢。

6、查詢好搭檔

如下圖所示，要根據H2單元格姓名，查詢所在的部門。

公式為：

=INDEX(B1:F1,MATCH(H2,B2:F2,))

由MATCH函數找到查詢值的精確位置，然後由INDEX函數返回指定區域中，對應位置的內容。可以實現上下左右任意方向的查詢。

7、年齡計算

如下圖所示，要根據C列的出生年月計算年齡。

公式為：

=DATEDIF(C2,TODAY(),"y")

DATEDIF函數常用於計算工齡、年齡等日期間隔。

第一參數是開始日期，第二參數是結束日期，第三參數是返回的數據類型。使用Y，表示返回整年數。使用M，則表示返回整月數。

8、身份證計算

如下圖所示，要根據C列的身份證號碼計算出生年月。

公式為：

=--TEXT(MID(C2,7,8),"0-00-00")

先使用MID函數，從C2單元格提取出表示出生年月的8位數字，再用TEXT函數將其轉換為日期樣式的文本。最後使用兩個負號進行運算，變成真正的日期序列值。

9、個人所得稅計算

如下圖所示，要根據E列工資額計算個稅。

公式為：

=ROUND(MAX((E2-3500)*0.1*{0.3,1,2,2.5,3,3.5,4.5}-5*{0,21,111,201,551,1101,2701},0),2)

只要把公式中的E2換成實際的單元格引用即可，其他不用管它。

說說你一個月為國家交多少稅呢？

10、中國式排名

如下圖所示，要對E列的成績進行中國式排名，也就是相同成績不佔用名次。

公式為：

=SUMPRODUCT((E$2:E$6>E2)/COUNTIF(E$2:E$6,E$2:E$6))+1

運算過程稍微複雜，有興趣可以參考今天第二篇的內容。