HI，大家好，我是星光。

話說Excel函數(shù)功能從03版就很穩(wěn)定，之后的版本除了小打小鬧更新幾個新函數(shù)，一直沒有太大的改變，直至MS365版本的降臨……向上看，我們這章的標題叫函數(shù)革命，既然是革命，那就得翻天覆地打破各種陳舊規(guī)則，不然就標題黨了不是？

那么從今天開始，我就用一系列文章給大家展示一下MS365是如何以翻天覆地的姿勢打破舊函數(shù)條條框框的。

革新一個舊的函數(shù)體系，無外乎從這么幾個方面入手，函數(shù)的運行效率、函數(shù)的編寫方式以及擴展新的函數(shù)功能。三者之間既是獨立的，又會相互影響。

我們先來說一下函數(shù)的運行效率。

談到函數(shù)，很多朋友的第一印象是這家伙只適合小數(shù)據(jù)的騰挪躲閃，數(shù)據(jù)量一大，就淪落為卡德斯基先生。比如VLOOKUP函數(shù)，大概處理個2萬左右的數(shù)據(jù)就有點兒卡頓了——但這印象應該被打破。

事實上，從Excel 2016版開始，微軟就對VLOOKUP/HLOOKUP/MATCH等函數(shù)的運算機制進行了強力優(yōu)化，從相同表區(qū)域查找多個列時，將為所搜索的列范圍創(chuàng)建內部緩存索引，后續(xù)查找中，將重用這一緩存的索引——打個響指，365版本中的VLOOKUP函數(shù)即便是計算十幾萬行數(shù)據(jù)也不是什么大問題。

而在365版本中，絕大部分參數(shù)涉及到單元格引用類的函數(shù)都采用了相同的優(yōu)化措施，比如我們所熟悉的SUMIF(S)、AVERAGEIF(S)、COUNTIF(S)、XLOOKUP等等。

此外還涉及到LAA 內存改進、完整列引用情況下減少所占內存和CPU等——也就是說，通常情況下，365版本函數(shù)的運算效率遠遠優(yōu)于普通版本的。

……

然后我們再說一下函數(shù)的編寫方式。

缺少編程中變量的表達形式，是編寫函數(shù)公式最讓人詬病的地方之一，它使函數(shù)的編寫變得非常復雜而臃腫。在Power Pivot的DAX函數(shù)引入VAR定義變量的方式后，工作表函數(shù)終于也開始設計一種全新的編寫結構，這就是LET函數(shù)。

LET函數(shù)的語法結構如下：

全新函數(shù)LET ▼

=LET(變量名1,變量內容1,變量名2,變量內容2……計算表達式)

我舉個簡單的例子。

數(shù)據(jù)如上圖所示▲

公式如下所示▼

=LET(A,1,B,A1+D1,A+B)

上面公式的意思是定義了一個變量，其名為A，其值為1，又定義了一個變量B，其值為單元格A1+D1的和，最后運行計算表達式A+B，也就返回變量A和變量B的合計值，也就是6。

LET函數(shù)的出現(xiàn)使函數(shù)編寫變得簡單許多，即避免了同一表達式重復出現(xiàn)和運算，又避免了函數(shù)嵌套層次過多閱讀困難的問題。

有朋友說，這不是定義名稱的功能嗎？定義名稱A=1，B=A1+D1……兩者確實十分相似，但定義名稱和函數(shù)編寫之間一直是脫節(jié)狀態(tài)，代碼調試和優(yōu)化十分不方便。攤手，承認吧，對大部分人而言，定義名稱就是個雞肋。

不過……LET函數(shù)還處于測試階段，并沒有正式發(fā)布，所以我們過個把月等它正式發(fā)布了再來詳細聊它。

……

最后再說一下365函數(shù)新功能。

這個是重點，照例點杯82年的雪碧先。

365函數(shù)新功能主要表現(xiàn)在兩方面，一個是新函數(shù)，比如排序函數(shù)SORT/SORTBY；去重函數(shù)UNIQUE；高效查詢篩選FILTER以及號稱滅霸的XLOOKUP函數(shù)等等。另外一個就是動態(tài)數(shù)組功能。

我們在教程什么是函數(shù)數(shù)組里講過區(qū)域數(shù)組公式的概念▼

'數(shù)組公式返回的是一組元素；但是Excel一個單元格只能顯示數(shù)組元素中的一個結果（默認為數(shù)組中的首個元素）。如果需要顯示數(shù)組公式的全部元素呢？——可以使用區(qū)域數(shù)組公式。

舉個簡單的例子

如上圖所示的表格，選中D2:D5單元格區(qū)域，在編輯欄編寫公式=B2:B5*C2:C5，然后按Ctrl+Shift+Enter數(shù)組三鍵結束公式輸入，也就在D2:D5區(qū)域內輸入了同一條數(shù)組公式，這就是區(qū)域數(shù)組公式。

該公式返回一個內存數(shù)組{12;70;30;15}，系統(tǒng)會將數(shù)組的每個元素依次顯示在D2:D5區(qū)域中。

在一個單元格中輸入的公式被稱為數(shù)組公式，而所謂區(qū)域數(shù)組公式，也就是在多個單元格中輸入同一數(shù)組公式，它可以有序返回結果數(shù)組中的每個元素。'

在365中，這一規(guī)則也被打破了。

在普通Excel版本，數(shù)組公式需要按Ctrl+Shift+Enter三鍵結束才能啟用多項運算；365版本拋棄了這個鍵，絕大部分數(shù)組公式都被默認執(zhí)行數(shù)組運算，也就不再需要摁三賤。

更重要的是，如果一個函數(shù)公式返回的是多項結果，365會將多個結果自動填充到相關單元格區(qū)域，前提是這些區(qū)域不存在數(shù)據(jù)。

依然以上圖所示數(shù)據(jù)為例，不需要選中D2:D5區(qū)域，只需要在D2單元格輸入公式=B2:B5*C2:C5，系統(tǒng)就會自動將該公式的計算結果，也就是將內存數(shù)組{12;70;30;15}中的元素依次顯示在D2:D5區(qū)域中。

這有什么好處呢？

我們以前一直給函數(shù)新人講，數(shù)組的運算效率是優(yōu)于大批量普通函數(shù)的，但一直被打臉，數(shù)組公式用多了Excel都卡的很——

事實上，數(shù)組運算的效率當然是高于大批量普通函數(shù)公式。之所以效率低下，是由于在實際運用時，大家總是在每個單元格都輸入數(shù)組公式，每個單元格都在做重復的數(shù)組運算，這不卡就見鬼了不是？

如果一個數(shù)組公式只運算一次就可以獲取全部結果了，那只需要將計算結果寫入相關單元格區(qū)域就OK，為什么還需要每個單元格都去做重復的數(shù)組運算呢？

——因為區(qū)域數(shù)組公式不好用唄。它需要提前選中結果區(qū)域，這個區(qū)域還不會隨計算結果自動擴展，即僵硬又麻煩。

而動態(tài)數(shù)組的出現(xiàn)則打破了這一切，它只需要計算一次，就可以返回全部計算結果，它還會根據(jù)計算結果，動態(tài)擴展相應存放結果的單元格區(qū)域，所以它效率很高，靈活性也不差。

在365中，能用動態(tài)數(shù)組解決的問題，就盡量不使用大批量普通函數(shù)公式——這兩者的計算效率實在是天差地別。非常不認真的說，動態(tài)數(shù)組用的好，函數(shù)的計算效率甚至不弱于VBA編程，簡潔性當然是完勝。

我舉個例子。

如下圖所示，A:D是數(shù)據(jù)源，需要根據(jù)F2單元格指定的班級和G2單元格指定的性別，篩選符合條件的名單，并統(tǒng)計總人數(shù)和成績之和。

藍色區(qū)域是模擬結果。

F5單元格輸入以下公式，即可獲取符合條件的明細記錄。

動態(tài)數(shù)組▼

=FILTER(A2:D8,(A2:A8=F2)*(C2:C8=G2),'')

FILTER是365中的一個新函數(shù)，語法格式如下▼

=FILTER(數(shù)據(jù)源,篩選條件,容錯值)

該函數(shù)第2參數(shù)是篩選條件，返回的結果須為邏輯值，如果為True則保留相應數(shù)據(jù)源記錄，為False則刪除相應記錄。

本例中(A2:A8=F2)*(C2:C8=G2)判斷A2:A8的班級是否等于F2單元格指定的班級，同時判斷C2:C8的性別是否等于G2單元格指定的性別。返回一個內存數(shù)組{1;1;1;0;0;0;0}，其中0為False，非0數(shù)值為True。

如果該函數(shù)查無符合條件的結果，會返回錯誤值#CALC!，通過第3參數(shù)指定一個值，可以避免返回該錯誤值，本例第3參數(shù)指定值為假空。

該函數(shù)支持數(shù)組運算，可以返回符合條件的一組結果。本例中一班男性一共有三條記錄，那么只需要在F5一個單元格輸入公式，即可獲取全部結果。系統(tǒng)會自動根據(jù)計算結果動態(tài)擴展結果區(qū)域▼

是不是很酷？

……

很明顯，動態(tài)數(shù)組的計算結果是一個動態(tài)區(qū)域，那么如何智能引用這個動態(tài)區(qū)域呢？難道需要使用OFFSET函數(shù)去搭建？

當然不用這么麻煩。

可以使用以下語法格式。

動態(tài)區(qū)域首個單元格#

比如，我們需要在I2單元格計算符合條件的人數(shù)，可以使用公式▼

=COUNT(F5#)&'人'

同樣的道理，J2單元格計算總成績，可以使用公式▼

=SUM(F5#)

兩個函數(shù)的運算效果參見上面的動圖。

……

困了，夜深，外面雨太大，咱們今天就聊到這吧，反正日子長長又緩緩，咱們后面慢慢聊——咱們下期開始聊365中都有哪些超級實用的新函數(shù)~不見不散。

最后補兩個小貼士：

1）如果需要取消動態(tài)數(shù)組的溢出功能，可以在等號后輸入符號@。比如輸入以下公式，就只會返回數(shù)組的首個元素。

=@FILTER(A2:D8,(A2:A8=F2)*(C2:C8=G2),'')

2）前面講過，動態(tài)數(shù)組功能會將結果自動填充到相關單元格區(qū)域，但前提是這些區(qū)域不存在數(shù)據(jù)，如果這些區(qū)域存在數(shù)據(jù)，動態(tài)數(shù)組會返回一個錯誤值#SPILL!，提示無法填充數(shù)據(jù)。

……

??溫馨小提示▼

公眾號每天會發(fā)布1篇函數(shù)教程+1篇編程教程+1個技巧小視頻，如果你沒有準時收到我的更新，是由于微信按權重顯示公眾號而不是實際更新時間——這時就需要星標我一下啦，撒花?

關注不會迷路