評論員文章

文：老祝

今天和大家分享一組常用的函數套路，大家遇到類似問題可以直接拿來套用即可。

1、常規查詢

(資料圖片僅供參考)

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

經典套路：

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

套路指南：

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

2、逆向查詢

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

經典套路：

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

套路指南：

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

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

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

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

3、查詢萬金油

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

經典套路：

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

套路指南：

由MATCH函數找到查詢值的精確位置，然后由INDEX函數返回指定區域中，對應位置的內容?？梢詫崿F上下左右任意方向的查詢。

4、年齡計算

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

經典套路：

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

套路指南：

雖是隱藏函數，卻早已是眾人皆知的秘密。

第一參數是開始日期，第二參數是結束日期，第三參數是返回的數據類型。

使用Y，表示返回整年數。

使用M，則表示返回整月數。

5、身份證計算

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

經典套路：

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

套路指南：

先使用MID函數，從C2單元格提取出表示出生年月的8位數字，再用TEXT函數將其轉換為日期樣式的文本。

最后使用兩個負號進行運算，變成真正的日期序列值。

6、個人所得稅計算

如下圖所示，要根據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換成實際的單元格引用即可，其他不用管它。

7、根據個稅倒推稅前工資

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

經典套路：

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

套路指南：

注意個稅額不能為0。

工資還不夠納稅起點的，藍瘦不？

8、中國式排名

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

經典套路：

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

套路指南：

運算過程比較復雜，三言兩語說不清了。

使用的時候，只要把公式中的單元格地址換成實際的數據區域即可。