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