11.2 ARRAYFORMULA

ARRAYFORMULA 的用途是讓公式中非陣列的輸入轉為支援陣列的形式，說明如下：

簡單來說，ARRAYFORMULA 讓原本只會有單一儲存格輸出的函式變成可以使用陣列輸入 + ARRAYFORMULA 變成多欄多列的輸出，以下以四則運算搭配四個範例說明 ARRAYFORMULA 搭配函式的計算邏輯，如下圖：

1. 因為 + 的前後只能輸入單一一個值，因此若直接使用 =C2:F2+C3:F3 只會輸出第一個值的相加，在此必須在外面包上一層 ARRAYFORMULA，如 A(2) 所示。但如果使用 A(3) 則無法輸出陣列相加，因為 + 並沒有用到 ARRAYFORMULA。
2. 兩個相同大小的陣列運算時，其原理與相加相同，會輸出對應位置兩兩相乘的值。
3. 前面有提及陣列在組合與運算時需要有相同的欄、列數，但若陣列分別為「單欄多列」和「單列多欄」運算時將不受限制，會將其自動轉為多欄、多列重複的值，如上圖會第一列會輸出 5×1~5×4 的值，以此類推。
4. 上述 C. 的例外僅限於「單欄多列」和「單列多欄」運算時，因為在此有兩列多欄，所以後面兩列的值會跳出 #N/A。

   了解陣列的基本運算方式後，接下來將使用 ARRAYFORMULA 完成第 1.3 節的五個範例，不同處在於原本要在整欄輸入相同的公式，但使用 ARRAYFORMULA 後可以直接在第二列輸出整欄的內容：

5. 交易記錄檔 K 欄：計算每列貨品交易的金額

   公式	=ARRAYFORMULA(I2:I*J2:J)
   解說	原公式為 =I2*J2，只要使用 ARRAYFORMULA 後，會自動計算每一列的值，即 I2*J2、I3:J3，以此類推至最後一列。

6. 交易記錄檔 L 欄：計算每筆訂單所有貨品交易金額的總和

   公式	=ARRAYFORMULA(SUMIF(A:A,A2:A,K:K))
   解說	原公式為 =SUMIF(A:A,A2,K:K)，在此可將 條件 從單一值改成使用陣列表示，但須注意超過一個條件時，SUMIFS 並無法使用 ARRAYFORMULA 完成，需要使用其他替代方法完成，將於第 12.3 節介紹。

7. 會員資料檔 J 欄：計算每位會員的年齡

   公式	=ARRAYFORMULA(DATEDIF(B2:B,TODAY(),"Y"))
   解說	原公式為 =DATEDIF(B2,TODAY(),"Y")，在此將 開始日期 設為陣列，但 結束日期 還是單一值，公式會自動將 結束日期 也轉成列數與 開始日期 相同的陣列。

8. 會員資料檔 K 欄：將會員所屬的年齡區間，包括 30 歲以下、31~40 歲、41~50 歲、51~60 歲、61 歲以上

   公式	=ARRAYFORMULA(IFS(J2:J<=30,"30 歲以下",J2:J<=40,"31~40 歲",J2:J<=50,"41~50 歲",J2:J<=60,"51~60 歲",J2:J>60,"61 歲以上"))
   解說	原公式為 =IFS(J2<=30,"30 歲以下",J2<=40,"31~40 歲",J2<=50,"41~50 歲",J2<=60,"51~60 歲",J2>60,"61 歲以上")，將所有 J2 都換成 J2:J 即可。

9. 會員資料檔 L 欄：使用交易記錄檔計算該會員在 2022~2023 年之交易總金額

   公式	=ARRAYFORMULA(SUMIF('交易記錄檔'!B:B,A2:A,'交易記錄檔'!K:K))
   解說	原公式為 =SUMIF('交易記錄檔'!B:B,A2,'交易記錄檔'!K:K)，將 A2 換成 A2:A 即可。

由上面幾個例子可知，通常只要在原本只有單一輸入的儲存格改為多個輸入，再搭配 ARRAYFORMULA 即可完成整列的運算，避免公式被人工更改或刪除，而若要再更進一步避免被更改到第 2 列的資料，可以直接在表頭使用 ARRAYFORMULA 一次完成整欄的資料。以 A. 為例，可以直接在 K1 使用 =ARRAYFORMULA({"金額";I2:I*J2:J})，善用陣列內容可以是單一值、儲存格範圍及公式輸出的特性。此外，ARRAYFORMULA 有快捷鍵 ⌘ / Ctrl + Shift + Enter，可以快速在公式最外面加上一層 ARRAYFORMULA。

雖然 ARRAYFORMULA 很好用，但仍有部分函式不能使用 ARRAYFORMULA，主要原因有以下幾種：

• 函式輸入可以是多欄多列：例如 SUM、AND、OR 等。
• 函式輸出可以是多欄多列：例如 IMPORTRANGE、IMPORTXML、INDEX、OFFSET、UNIQUE、SORT、SORTN、FILTER 等。
• 其他：例如 SPARKLINE、GETPIVOTDATA、SUMIFS、AVERAGEIF 等。

雖然上述函式無法使用 ARRAYFORMULA 完成，但可以使用另一個方式 — LAMBDA + LAMBDA 輔助函式，將在第 12.3 節詳細說明。然而，要純熟的使用這兩種用法之前，必須先對其他的函式足夠熟悉，這兩個函式才有價值。