編按：

Excel里有400多個(gè)函數(shù)，每個(gè)函數(shù)都是針對(duì)某一種問(wèn)題而設(shè)計(jì)的，但是在這些函數(shù)中，有的函數(shù)身兼數(shù)能，一個(gè)函數(shù)就能具備很多種功能，因此被廣大的函數(shù)愛(ài)好者冠以“萬(wàn)能函數(shù)”的稱(chēng)號(hào)，今天就來(lái)盤(pán)點(diǎn)一下Excel中都有哪些“萬(wàn)能函數(shù)”。

萬(wàn)能函數(shù)之一：SUBTOTAL

功能1.動(dòng)態(tài)求和

公式為=SUBTOTAL(9,D2:D21)，具體效果看動(dòng)圖演示。

注意對(duì)比公式求和的結(jié)果與下方狀態(tài)欄的求和結(jié)果始終保持一致。

功能2.動(dòng)態(tài)求平均值

公式為=SUBTOTAL(1,D2:D21)，具體效果看動(dòng)圖演示

注意對(duì)比公式求平均值的結(jié)果與下方狀態(tài)欄的求平均值結(jié)果始終保持一致。

功能3.動(dòng)態(tài)求最大值

公式為=SUBTOTAL(4,D2:D21)，有興趣的同學(xué)可以自己測(cè)試。

功能4.動(dòng)態(tài)求最小值

公式為=SUBTOTAL(5,D2:D21)，有興趣的同學(xué)可以自己測(cè)試。

這時(shí)候可能有些細(xì)心的伙伴已經(jīng)發(fā)現(xiàn)了，通過(guò)修改SUBTOTAL的第一參數(shù)就能實(shí)現(xiàn)不同的功能，也就是說(shuō)第一參數(shù)有多少選項(xiàng)這個(gè)函數(shù)就有多少個(gè)功能。

的確是這樣的，SUBTOTAL函數(shù)的第一參數(shù)就能實(shí)現(xiàn)11種函數(shù)的功能，具體對(duì)照關(guān)系如圖所示。

每種功能還有兩種情況，1-11是對(duì)包含隱藏的數(shù)據(jù)進(jìn)行求和，101-111是忽略隱藏的數(shù)據(jù)進(jìn)行求和，隱藏的意思就是通過(guò)右鍵隱藏行。雖然對(duì)于使用者來(lái)說(shuō)隱藏和篩選都可以讓某些行看不見(jiàn)，但是對(duì)于這個(gè)函數(shù)來(lái)說(shuō)是有區(qū)別的，詳見(jiàn)動(dòng)圖。

可以看到使用9和109在有隱藏行的情況下求和結(jié)果不同，但對(duì)于篩選求和則無(wú)影響。

這是第一個(gè)萬(wàn)能函數(shù)，具備了22種功能的SUBTOTAL。

萬(wàn)能函數(shù)之二：AGGREGATE

這是一個(gè)比SUBTOTAL更為強(qiáng)大的萬(wàn)能函數(shù)，它具備19種函數(shù)的功能，8種應(yīng)用場(chǎng)景（可以選擇忽略哪些類(lèi)型的值）。如果要把功能和場(chǎng)景組合起來(lái)的話，能實(shí)現(xiàn)一百多種統(tǒng)計(jì)效果?？梢哉f(shuō)SUBTOTAL會(huì)的AGGREGATE都會(huì)，但是AGGREGATE會(huì)的SUBTOTAL很多都不會(huì)。

AGGREGATE的基本格式為：= AGGREGATE（統(tǒng)計(jì)功能，忽略哪些值，數(shù)據(jù)區(qū)域，k值）

下圖列舉了AGGREGATE的19種功能代碼：

可以看出AGGREGATE函數(shù)的統(tǒng)計(jì)功能確實(shí)要比SUBTOTAL函數(shù)更加豐富。

還有8種應(yīng)用場(chǎng)景：

在這8個(gè)應(yīng)用場(chǎng)景中，前四個(gè)比較少用到，個(gè)人覺(jué)得最有用的是6（忽略錯(cuò)誤值），舉個(gè)例子來(lái)說(shuō)。

在數(shù)據(jù)源中有個(gè)錯(cuò)誤值#N/A，這時(shí)候用公式=SUBTOTAL(9,D2:D21)就無(wú)法得到合計(jì)金額，而用公式=AGGREGATE(9,6,D2:D21)則不受影響，因?yàn)榈诙?shù)我們使用了6。

關(guān)于AGGREGATE的更多用法可以參閱往期教程：

https://mp.weixin.qq.com/s/lxE7OPuMiY6wgz6LrcxCjQ

這里要給大家強(qiáng)調(diào)的是：AGGREGATE雖說(shuō)有四個(gè)參數(shù)，但是只有第一、三兩個(gè)參數(shù)是必須要填的，這時(shí)候第二參數(shù)就默認(rèn)為0，例如=AGGREGATE(9, ,D2:D21)。另外一點(diǎn)就是當(dāng)?shù)谝粎?shù)填14-19的時(shí)候，第四參數(shù)就必須要填了，例如=AGGREGATE(14,7,D2:D21,2)，就表示10月1日的第二高金額。

萬(wàn)能函數(shù)之三：SUMPRODUCT

SUMPRODUCT函數(shù)可以實(shí)現(xiàn)的功能有：各種情況下的條件求和、條件計(jì)數(shù)；排名次、分組排名次等等統(tǒng)計(jì)功能。與前兩個(gè)函數(shù)的不同之處在于，SUMPRODUCT函數(shù)的參數(shù)中并沒(méi)有一個(gè)選項(xiàng)是決定函數(shù)功能的，這個(gè)函數(shù)玩的就是套路，下面舉幾個(gè)例子讓大家看看。

示例1.條件求和

公式為=SUMPRODUCT((A2:A21="一分店")*D2:D21)

用SUMPRODUCT條件求和的套路為：=SUMPRODUCT((條件區(qū)域=條件)*數(shù)據(jù)區(qū)域)

示例2.多條件求和

公式為=SUMPRODUCT((A2:A21="一分店")*(B2:B21="朱毓華")*D2:D21)

用SUMPRODUCT多條件求和的套路為：=SUMPRODUCT((條件區(qū)域1=條件1)* (條件區(qū)域2=條件2)*數(shù)據(jù)區(qū)域)

示例3.按金額排名次

公式為=SUMPRODUCT(($D$2:$D$21>=D15)*1)

這個(gè)結(jié)果和公式=RANK(E2,$E$2:$E$21,1)一致，如果只是單純排名次的話，SUMPRODUCT并沒(méi)有太大的優(yōu)勢(shì)，但如果要做分組排名的話，RANK就無(wú)能為力了。

示例4.按門(mén)店分組排名次

公式為=SUMPRODUCT(($D$2:$D$21>=D2)*($A$2:$A$21=A2))

從結(jié)果不難看出，每個(gè)門(mén)店的金額都是單獨(dú)排名次的。

關(guān)于SUMPRODUCT函數(shù)的更多示例介紹，可以參考之前的教程：

https://mp.weixin.qq.com/s/65Kf6oPOJW2TbJsNgIaQ4w

以上介紹的三個(gè)萬(wàn)能函數(shù)都是統(tǒng)計(jì)類(lèi)的，SUBTOTAL和AGGREGATE的統(tǒng)計(jì)功能更強(qiáng)，是通過(guò)篩選或者隱藏來(lái)實(shí)現(xiàn)按條件統(tǒng)計(jì)的，這也是兩個(gè)函數(shù)獨(dú)一無(wú)二的特性。而SUMPRODUCT的各種使用套路中，幾乎都出現(xiàn)了一個(gè)*號(hào)，關(guān)于*號(hào)和這個(gè)函數(shù)之間的秘密，之前也有專(zhuān)門(mén)的教程，有興趣的朋友可以去看看。

https://mp.weixin.qq.com/s/8aK-zGSFTDXCVv0ZDCmGzA

接下來(lái)要介紹的則是非統(tǒng)計(jì)類(lèi)的萬(wàn)能函數(shù)。

萬(wàn)能函數(shù)之四：TEXT

TEXT函數(shù)只有兩個(gè)參數(shù)，格式為：=TEXT(要處理的數(shù)據(jù), "要使用的格式代碼")。

函數(shù)的功能說(shuō)起來(lái)也很簡(jiǎn)單，就是將單元格里的數(shù)據(jù)按照指定的格式代碼顯示出來(lái)，但是格式代碼就太多了，如果一種格式代碼算一種功能的話，TEXT函數(shù)到底有多少種功能怕是數(shù)不清了，以下僅為大家分享幾個(gè)有代表性的示例。

示例1. 一個(gè)公式實(shí)現(xiàn)三種功能

在金額前面加上人民幣符號(hào)￥，后面加上單位元，同時(shí)不不顯示小數(shù)部分，公式為：=TEXT(D2,"￥0元")

示例2.處理日期類(lèi)問(wèn)題

將日期顯示完整的年月日，同時(shí)顯示出是星期幾，公式為：=TEXT(C2,"e年mm月dd日 aaaa")

示例3.分情況顯示不同結(jié)果

假如將金額目標(biāo)定為2000元，可以用TEXT實(shí)現(xiàn)判斷超額還是未完成任務(wù)的效果，公式為：=TEXT(D2-2000,"超額0元;還差0元;剛好")

示例4.金額變成大寫(xiě)

將金額變成大寫(xiě)，并且只保留整數(shù)部分，公式為：=TEXT(D2,"[DBNum2]0元")

通過(guò)這四個(gè)例子，相信大家已經(jīng)對(duì)TEXT的功能有所了解，但要真的用好TEXT函數(shù)就必須掌握格式代碼，對(duì)于新手來(lái)說(shuō)，自定義格式是探究格式代碼的一個(gè)捷徑，具體方法可以參考之前的教程：

https://mp.weixin.qq.com/s/uhEd32PrQPzs_yXMIMTAFA

四個(gè)多功能函數(shù)介紹完了，最后要介紹的這個(gè)萬(wàn)能函數(shù)是Excel365才有的，功能主要集中在數(shù)據(jù)匹配方面，估計(jì)有些朋友已經(jīng)猜到了，這個(gè)函數(shù)就是XLOOKUP。

萬(wàn)能函數(shù)之五：XLOOKUP

XLOOKUP函數(shù)一共有六個(gè)參數(shù)，函數(shù)的格式是=XLOOKUP(查找值,查找范圍,結(jié)果范圍,[找不到時(shí)顯示的值],[匹配方式], [查詢模式])。

其中第四參數(shù)[匹配方式]有四個(gè)選項(xiàng)，第五參數(shù)[查詢模式]也有四個(gè)選項(xiàng)，再考慮函數(shù)本身的匹配特性，少了VLOOKUP和LOOKUP的一些限制，是一個(gè)名副其實(shí)的萬(wàn)能匹配函數(shù)。

但是由于這個(gè)函數(shù)僅在Excel365版才有，很多用戶只能先通過(guò)教程來(lái)了解其功能了。

參考教程：https://mp.weixin.qq.com/s/D_zrKppWUlGDlRZQTdOxIQ

今天一共分享了五個(gè)萬(wàn)能函數(shù)，在不考慮與其他函數(shù)嵌套使用的前提下，每個(gè)函數(shù)各有特點(diǎn)，要想能達(dá)到靈活應(yīng)用還需要下一番功夫才行，不知道這五個(gè)函數(shù)你最喜歡哪個(gè)呢？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬(wàn)能！

版權(quán)申明：

本文作者老菜鳥(niǎo)；同時(shí)部落窩教育享有專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。