如何使用 Google 試算表中的陣列公式「ARRAYFORMULA」?

如何使用 Google 試算表中的陣列公式「ARRAYFORMULA」?

在使用 Google 試算表時,如果要對不同的數值重複執行相同的計算時,為了簡化公式,就可以讓陣列公式「ARRAYFORMULA」出馬。

何謂陣列公式「ARRAYFORMULA」

如果以數學上的概念來解釋的話,就像是A*B+A*C+A*D+A*E,這段公式你可以簡化成:A(B+C+D+E),陣列就是類似的概念。因此想對試算表內的特定範圍重複計算的時候,你不必將公式寫得很長很長,只要使用陣列公式 ARRAYFORMULA 就會方便許多!

ARRAYFORMULA 陣列公式的使用方式

陣列公式的使用方式

就像上方舉例的,當你要對不同的數值重複執行相同的計算時,就是可以使用陣列公式「ARRAYFORMULA」的最佳情境。比方說上圖是一個4×5的儲存格表格,若現在你想要計算表格內的每個數字100倍的值是多少,這時候只要輸入「=ARRAYFORMULA(100*A1:D5)」,就可以得到答案。

ARRAYFORMULA陣列計算結果

可以發現,此時陣列的計算,回傳的是多個值而非單一值,且回傳的結果欄數和列數都和原始資料相等。

那如果今天只是想知道這些計算結果的值加總,不需要知道個別結果的話,也可以稍微調整一下陣列的公式「=SUM(ARRAYFORMULA(100*A1:D5))」來得到答案,如下圖。

加總陣列計算結果的值

ARRAYFORMULA的實際應用

看完上述的說明之後,可能還是會有點疑惑,到底什麼時我可以用得上陣列公式呢?接下來就再繼續用個比較具體的例子來當作應用參考。

使用陣列的具體的例子

假如你現在是一間店的店長,你想要計算每個月不同的日期的營收狀況如何,或是好奇每個月月初營收會比較高、月底的營收會比較低嗎?有想要做這種分析的時候,就可以透過 ARRAYFORMULA 來達成目的。

由於我們現在想要計算平均每個月1號的營收,按照之前的做法,你可能會寫以下的公式:=averageif(DAY(A:A),D2,B:B),結果會顯示如下圖的錯誤。

=averageif(DAY(A:A),D2,B:B)顯示錯誤

會產生這個錯誤這是因為「day」這個公式只能用來計算特定的「一個」日期或「一個」儲存格內的資料,沒有辦法一次計算範圍內的所有資料。也因此上述公式的條件判斷結果是不會成立的,才會產生除數是零的錯誤。

難道我們就只能自己手動篩選計算了嗎?不!只要運用 ARRAYFORMULA,就可以解決。只要在原本的公式前面加入 ARRAYFORMULA 公式就可以了。

利用 ARRAYFORMULA 計算

這就是 ARRAYFORMULA 公式最佳的使用時機,有時候你想做的計算是無法在一個範圍內一次計算所有資料的,這時候只要用 ARRAYFORMULA 公式就可以輕鬆方便的達到你想要的計算效果囉!

延伸閱讀

如何把分鐘數轉換成十進位小數點?這些函數能幫你計算工時成本!
老鳥傳說的必備技能「樞紐分析」,用 Google Sheet 也辦得到!