作者：Excel Home
HI，大家好，我是星光。
話說Excel函數(shù)功能從03版就很穩(wěn)定，之后的版本除了小打小鬧更新幾個新函數(shù)，一直沒有太大的改變，直至MS365版本的降臨……向上看，我們這章的標(biāo)題叫函數(shù)革命，既然是革命，那就得翻天覆地打破各種陳舊規(guī)則，不然就標(biāo)題黨了不是？
那么從今天開始，我就用一系列文章給大家展示一下MS365是如何以翻天覆地的姿勢打破舊函數(shù)條條框框的。
革新一個舊的函數(shù)體系，無外乎從這么幾個方面入手，函數(shù)的運(yùn)行效率、函數(shù)的編寫方式以及擴(kuò)展新的函數(shù)功能。三者之間既是獨(dú)立的，又會相互影響。
我們先來說一下函數(shù)的運(yùn)行效率。
談到函數(shù)，很多朋友的第一印象是這家伙只適合小數(shù)據(jù)的騰挪躲閃，數(shù)據(jù)量一大，就淪落為卡德斯基先生。比如VLOOKUP函數(shù)，大概處理個2萬左右的數(shù)據(jù)就有點(diǎn)兒卡頓了——但這印象應(yīng)該被打破。
事實(shí)上，從Excel 2016版開始，微軟就對VLOOKUP/HLOOKUP/MATCH等函數(shù)的運(yùn)算機(jī)制進(jìn)行了強(qiáng)力優(yōu)化，從相同表區(qū)域查找多個列時，將為所搜索的列范圍創(chuàng)建內(nèi)部緩存索引，后續(xù)查找中，將重用這一緩存的索引——打個響指，365版本中的VLOOKUP函數(shù)即便是計算十幾萬行數(shù)據(jù)也不是什么大問題。
而在365版本中，絕大部分參數(shù)涉及到單元格引用類的函數(shù)都采用了相同的優(yōu)化措施，比如我們所熟悉的SUMIF(S)、AVERAGEIF(S)、COUNTIF(S)、XLOOKUP等等。
此外還涉及到LAA 內(nèi)存改進(jìn)、完整列引用情況下減少所占內(nèi)存和CPU等——也就是說，通常情況下，365版本函數(shù)的運(yùn)算效率遠(yuǎn)遠(yuǎn)優(yōu)于普通版本的。
……
然后我們再說一下函數(shù)的編寫方式。
缺少編程中變量的表達(dá)形式，是編寫函數(shù)公式最讓人詬病的地方之一，它使函數(shù)的編寫變得非常復(fù)雜而臃腫。在Power Pivot的DAX函數(shù)引入VAR定義變量的方式后，工作表函數(shù)終于也開始設(shè)計一種全新的編寫結(jié)構(gòu)，這就是LET函數(shù)。
LET函數(shù)的語法結(jié)構(gòu)如下：
全新函數(shù)LET ▼
=LET(變量名1,變量內(nèi)容1,變量名2,變量內(nèi)容2……計算表達(dá)式)
我舉個簡單的例子。
數(shù)據(jù)如上圖所示▲
公式如下所示▼
=LET(A,1,B,A1+D1,A+B)
上面公式的意思是定義了一個變量，其名為A，其值為1，又定義了一個變量B，其值為單元格A1+D1的和，最后運(yùn)行計算表達(dá)式A+B，也就返回變量A和變量B的合計值，也就是6。
LET函數(shù)的出現(xiàn)使函數(shù)編寫變得簡單許多，即避免了同一表達(dá)式重復(fù)出現(xiàn)和運(yùn)算，又避免了函數(shù)嵌套層次過多閱讀困難的問題。
有朋友說，這不是定義名稱的功能嗎？定義名稱A=1，B=A1+D1……兩者確實(shí)十分相似，但定義名稱和函數(shù)編寫之間一直是脫節(jié)狀態(tài)，代碼調(diào)試和優(yōu)化十分不方便。攤手，承認(rèn)吧，對大部分人而言，定義名稱就是個雞肋。
不過……LET函數(shù)還處于測試階段，并沒有正式發(fā)布，所以我們過個把月等它正式發(fā)布了再來詳細(xì)聊它。
……
最后再說一下365函數(shù)新功能。
這個是重點(diǎn)，照例點(diǎn)杯82年的雪碧先。
365函數(shù)新功能主要表現(xiàn)在兩方面，一個是新函數(shù)，比如排序函數(shù)SORT/SORTBY；去重函數(shù)UNIQUE；高效查詢篩選FILTER以及號稱滅霸的XLOOKUP函數(shù)等等。另外一個就是動態(tài)數(shù)組功能。
我們在教程什么是函數(shù)數(shù)組里講過區(qū)域數(shù)組公式的概念▼
'數(shù)組公式返回的是一組元素；但是Excel一個單元格只能顯示數(shù)組元素中的一個結(jié)果（默認(rèn)為數(shù)組中的首個元素）。如果需要顯示數(shù)組公式的全部元素呢？——可以使用區(qū)域數(shù)組公式。
舉個簡單的例子
如上圖所示的表格，選中D2:D5單元格區(qū)域，在編輯欄編寫公式=B2:B5*C2:C5，然后按Ctrl+Shift+Enter數(shù)組三鍵結(jié)束公式輸入，也就在D2:D5區(qū)域內(nèi)輸入了同一條數(shù)組公式，這就是區(qū)域數(shù)組公式。
該公式返回一個內(nèi)存數(shù)組{12;70;30;15}，系統(tǒng)會將數(shù)組的每個元素依次顯示在D2:D5區(qū)域中。
在一個單元格中輸入的公式被稱為數(shù)組公式，而所謂區(qū)域數(shù)組公式，也就是在多個單元格中輸入同一數(shù)組公式，它可以有序返回結(jié)果數(shù)組中的每個元素。'
在365中，這一規(guī)則也被打破了。
在普通Excel版本，數(shù)組公式需要按Ctrl+Shift+Enter三鍵結(jié)束才能啟用多項(xiàng)運(yùn)算；365版本拋棄了這個鍵，絕大部分?jǐn)?shù)組公式都被默認(rèn)執(zhí)行數(shù)組運(yùn)算，也就不再需要摁三賤。
更重要的是，如果一個函數(shù)公式返回的是多項(xiàng)結(jié)果，365會將多個結(jié)果自動填充到相關(guān)單元格區(qū)域，前提是這些區(qū)域不存在數(shù)據(jù)。
依然以上圖所示數(shù)據(jù)為例，不需要選中D2:D5區(qū)域，只需要在D2單元格輸入公式=B2:B5*C2:C5，系統(tǒng)就會自動將該公式的計算結(jié)果，也就是將內(nèi)存數(shù)組{12;70;30;15}中的元素依次顯示在D2:D5區(qū)域中。
這有什么好處呢？
我們以前一直給函數(shù)新人講，數(shù)組的運(yùn)算效率是優(yōu)于大批量普通函數(shù)的，但一直被打臉，數(shù)組公式用多了Excel都卡的很——
事實(shí)上，數(shù)組運(yùn)算的效率當(dāng)然是高于大批量普通函數(shù)公式。之所以效率低下，是由于在實(shí)際運(yùn)用時，大家總是在每個單元格都輸入數(shù)組公式，每個單元格都在做重復(fù)的數(shù)組運(yùn)算，這不卡就見鬼了不是？
如果一個數(shù)組公式只運(yùn)算一次就可以獲取全部結(jié)果了，那只需要將計算結(jié)果寫入相關(guān)單元格區(qū)域就OK，為什么還需要每個單元格都去做重復(fù)的數(shù)組運(yùn)算呢？
——因?yàn)?strong>區(qū)域數(shù)組公式不好用唄。它需要提前選中結(jié)果區(qū)域，這個區(qū)域還不會隨計算結(jié)果自動擴(kuò)展，即僵硬又麻煩。
而動態(tài)數(shù)組的出現(xiàn)則打破了這一切，它只需要計算一次，就可以返回全部計算結(jié)果，它還會根據(jù)計算結(jié)果，動態(tài)擴(kuò)展相應(yīng)存放結(jié)果的單元格區(qū)域，所以它效率很高，靈活性也不差。
在365中，能用動態(tài)數(shù)組解決的問題，就盡量不使用大批量普通函數(shù)公式——這兩者的計算效率實(shí)在是天差地別。非常不認(rèn)真的說，動態(tài)數(shù)組用的好，函數(shù)的計算效率甚至不弱于VBA編程，簡潔性當(dāng)然是完勝。
我舉個例子。
如下圖所示，A:D是數(shù)據(jù)源，需要根據(jù)F2單元格指定的班級和G2單元格指定的性別，篩選符合條件的名單，并統(tǒng)計總?cè)藬?shù)和成績之和。
藍(lán)色區(qū)域是模擬結(jié)果。
F5單元格輸入以下公式，即可獲取符合條件的明細(xì)記錄。
動態(tài)數(shù)組▼
=FILTER(A2:D8,(A2:A8=F2)*(C2:C8=G2),'')
FILTER是365中的一個新函數(shù)，語法格式如下▼
=FILTER(數(shù)據(jù)源,篩選條件,容錯值)
該函數(shù)第2參數(shù)是篩選條件，返回的結(jié)果須為邏輯值，如果為True則保留相應(yīng)數(shù)據(jù)源記錄，為False則刪除相應(yīng)記錄。
本例中(A2:A8=F2)*(C2:C8=G2)判斷A2:A8的班級是否等于F2單元格指定的班級，同時判斷C2:C8的性別是否等于G2單元格指定的性別。返回一個內(nèi)存數(shù)組{1;1;1;0;0;0;0}，其中0為False，非0數(shù)值為True。
如果該函數(shù)查無符合條件的結(jié)果，會返回錯誤值#CALC!，通過第3參數(shù)指定一個值，可以避免返回該錯誤值，本例第3參數(shù)指定值為假空。
該函數(shù)支持?jǐn)?shù)組運(yùn)算，可以返回符合條件的一組結(jié)果。本例中一班男性一共有三條記錄，那么只需要在F5一個單元格輸入公式，即可獲取全部結(jié)果。系統(tǒng)會自動根據(jù)計算結(jié)果動態(tài)擴(kuò)展結(jié)果區(qū)域▼
是不是很酷？
……
很明顯，動態(tài)數(shù)組的計算結(jié)果是一個動態(tài)區(qū)域，那么如何智能引用這個動態(tài)區(qū)域呢？難道需要使用OFFSET函數(shù)去搭建？
當(dāng)然不用這么麻煩。
可以使用以下語法格式。
動態(tài)區(qū)域首個單元格#
比如，我們需要在I2單元格計算符合條件的人數(shù)，可以使用公式▼
=COUNT(F5#)&'人'
同樣的道理，J2單元格計算總成績，可以使用公式▼
=SUM(F5#)
兩個函數(shù)的運(yùn)算效果參見上面的動圖。
……
困了，夜深，外面雨太大，咱們今天就聊到這吧，反正日子長長又緩緩，咱們后面慢慢聊——咱們下期開始聊365中都有哪些超級實(shí)用的新函數(shù)~不見不散。
最后補(bǔ)兩個小貼士：
1）如果需要取消動態(tài)數(shù)組的溢出功能，可以在等號后輸入符號@。比如輸入以下公式，就只會返回數(shù)組的首個元素。
=@FILTER(A2:D8,(A2:A8=F2)*(C2:C8=G2),'')
2）前面講過，動態(tài)數(shù)組功能會將結(jié)果自動填充到相關(guān)單元格區(qū)域，但前提是這些區(qū)域不存在數(shù)據(jù)，如果這些區(qū)域存在數(shù)據(jù)，動態(tài)數(shù)組會返回一個錯誤值#SPILL!，提示無法填充數(shù)據(jù)。
……
??溫馨小提示▼
公眾號每天會發(fā)布1篇函數(shù)教程+1篇編程教程+1個技巧小視頻，如果你沒有準(zhǔn)時收到我的更新，是由于微信按權(quán)重顯示公眾號而不是實(shí)際更新時間——這時就需要星標(biāo)我一下啦，撒花?
關(guān)注不會迷路
聯(lián)系客服
