Excel裏有4 00 多個函式，每個函式都是針對某一種問題而設計的。
但有的函式，天生就是「卷王」，不僅要做自己的本職工作，別人的工作也要搶過來做， 身兼數能，樂此不疲 。
被廣大函式愛好者冠以「 萬能函式 」的雅號。
下面，就讓我們一起來盤點一下吧！
1、 SUBTOTAL
2、 AGGREGATE
3、 SUMPRODUCT
4、 T EXT
5、XLOOKUP
還不會的同學，可以學起來啦~
編輯｜ 六姑娘
作者 | 老菜鳥
萬能函式之一、SUBTOTAL
功能1：動態求和
公式為=SUBTOTAL(9,D2:D21)
如下圖所示，我們透過條件篩選，可以快速得到動態求和的結果。
同理，這個功能也可以延伸 。
動態求 平均值 ：
=SUBTOTAL(1,D2:D21)
動態求 最大值 ：
=SUBTOTAL(4,D2:D21)
動態求
最小值
：
=SUBTOTAL(5,D2:D21)
總結：透過修改SUBTOTAL的第一參數能實作不同的功能， 能實作11種函式的功能 ，具體對照關系如圖所示。
每種功能還有兩種情況， 1-11 是對 包含隱藏的數據進行求和 ， 101-111 是 忽略隱藏的數據進行求和。
萬能函式之二：AGGREGATE
具備了 19種函式 的功能，8種套用場景，如果要把功能和場景組合起來的話，能實作一百多種統計效果。
AGGREGATE的基本格式為：= AGGREGATE（統計功能，忽略哪些值，數據區域，k值）
下圖列舉了AGGREGATE的19種功能程式碼：
還有8種套用場景：
舉個例子：
在資料來源中有個錯誤值#N/A，這時候用公式=SUBTOTAL(9,D2:D21)無法得到合計金額，而用公式=AGGREGATE(9,6,D2:D21)則不受影響，因為第二參數我們使用了6。
關於AGGREGATE的更多詳細用法可參閱往期教程：
萬能函式之三：SUMPRODUCT
SUMPRODUCT函式可以實作的功能有：各種情況下的條件求和、條件計數；排名次、分組排名次等等統計功能。
下面 舉幾個 例子 看看。
範例1、條件求和
=SUMPRODUCT((條件區域=條件)*數據區域 )
公式為=SUMPRODUCT((A2:A21="一分店")*D2:D21)
範例2、多條件求和
=SUMPRODUCT((條件區域1=條件1)* (條件區域2=條件2)*數據區域)
公式為=SUMPRODUCT((A2:A21="一分店")*(B2:B21="朱毓華")*D2:D21)
範例3、按金額排名次
公式為=SUMPRODUCT(($D$2:$D$21>=D15)*1)
範例4、按門店分組排名次
公式為=SUMPRODUCT(($D$2:$D$21>=D2)*($A$2:$A$21=A2))
關於SUMPRODUCT函式的更多詳細用法可檢視教程：
萬能函式之四：TEXT
=TEXT(要處理的數據, "要使用的格式程式碼")。
範例1、 一個公式實作三種功能
在金額前面加上符號¥，後面加上單位 元，同 時 不 顯示小數部份， 公式為： =TEXT(D2,"¥0元")
範例2、處理日期類問題
將日期顯示完整的年月日，同時顯示出是星期幾，公式為：=TEXT(C2,"e年mm月dd日 aaaa")
範例3、分情況顯示不同結果
假如將金額目標定為2000元，可以用TEXT實作判斷超額還是未完成任務的效果，公式為：=TEXT(D2-2000,"超額0元;還差0元;剛好")
範例4、 金額變成大寫
將金額變成大寫，並且只保留整數部份，公式為：=TEXT(D2,"[DBNum2]0元")
萬能函式之五：XLOOKUP
XLOOKUP函式一共有六個參數，函式的格式是=XLOOKUP(尋找值,尋找範圍,結果範圍,[找不到時顯示的值],[匹配方式], [查詢模式])。
篇幅原因，這裏不再展開講解，感興趣的同學可以看之前的教程：
好啦，今天一共分享了五個萬能函式，各有特點。
不過，要想能達到靈活套用，還需要下一番功夫才行！
不知道這五個函式你最喜歡哪個呢？歡迎群裏交流喲~
寵 粉 福 利
2元領取：全套Excel技巧視訊+200套樣版
點"閱讀原文"，學習更多的Excel視訊教程
