革新一個舊的函數體系，無外乎從這么幾個方面入手，函數的運行效率、函數的編寫方式以及擴展新的函數功能。今天咱們就學習一下，看看365新函數是如何通過這三個方面打破舊函數條條框框的。

365函數系列推出了一個新的概念，叫做動態數組。它打破了舊數組的概念，不需要按組合鍵即可執行數組運算，不需要選中范圍即可返回多項結果，且結果區域會動態調整，在保持運算高效的同時，靈活性也不差。那么……什么是動態數組？

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

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

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

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

再說一下365函數新功能。

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

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

"數組公式返回的是一組元素；但是Excel一個單元格只能顯示數組元素中的一個結果(默認為數組中的首個元素)。

如果需要顯示數組公式的全部元素呢？——可以使用區域數組公式。

舉個簡單的例子

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

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

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

在365中，這一規則也被打破了。

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

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

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

這有什么好處呢？

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

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

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

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

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

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

我舉個例子。

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

藍色區域是模擬結果。

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

動態數組▼

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

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

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

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

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

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

該函數支持數組運算，可以返回符合條件的一組結果。本例中一班男性一共有三條記錄，那么只需要在F5一個單元格輸入公式，即可獲取全部結果。

系統會自動根據計算結果動態擴展結果區域▼

是不是很酷？

……

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

當然不用這么麻煩。

可以使用以下語法格式。

動態區域首個單元格#

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

=COUNT(F5#)&"人"

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

=SUM(F5#)

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

最后補兩個小貼士：

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

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

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

(未完待續)

圖文制作：看見星光

原載：Excel星球

總結

以上是生活随笔為你收集整理的数组索引必须为正整数或逻辑值_Office 365函数新世界——动态数组的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。