【Excel進階教學】如何使用陣列公式計算團購總金額（變化型）

 

前言

接續先前的教學，今天我們要處理一個在團購表單中更常見的進階問題。有時候，為了方便使用者填寫，我們會用「*」之類的特殊符號來標示「本日可訂購」的品項。然而，這也給我們的加總計算帶來了麻煩。

如果直接使用陣列公式將「單價」與「數量」相乘，一旦遇到「*」這個文字符號，Excel就會因為無法進行數學運算而回傳 #VALUE! 錯誤。本文將教您如何巧妙地結合 IF 函數與陣列公式，完美解決這個問題。

核心觀念：為何會出錯與解決思路

1. 問題根源：Excel的陣列公式會試圖將範圍內的儲存格逐一運算。當它執行「數字 x 文字」（例如 180 * "*"）時，就會產生 #VALUE! 錯誤，導致整個公式計算失敗。

2. 解決方案：我們需要在進行乘法運算之前，先對「數量」欄位進行一次判斷。我們的邏輯很簡單：

   如果儲存格的內容是「*」，就把它當作數字 0 來計算；如果不是，就使用它原本的數值。

   這個邏輯，我們可以使用 IF 函數來實現。

完整公式與操作步驟

現在，我們將這個 IF 判斷式整合到 SUM 陣列公式中。

步驟一：建立公式架構

首先，寫下基本的陣列公式架構，將兩個團購區域的金額相加。
=SUM( 左側價格範圍 * 左側數量範圍 + 右側價格範圍 * 右側數量範圍 )

步驟二：插入 IF 函數處理特殊符號

將公式中的「數量範圍」用 IF 函數的邏輯來取代。如果數量欄位是「*」，就回傳 0，否則就回傳原本的數量。

完整的公式如下：

=SUM(D2:D21*IF(E2:I21="*",0,E2:I21) + M2:M21*IF(N2:R21="*",0,N2:R21))

步驟三：輸入陣列公式（關鍵步驟！）

在儲存格輸入完上述公式後，切記不能直接按 Enter 鍵。

您必須同時按下  +  這三個鍵來完成輸入。

成功後，您會看到公式列中的公式，前後被自動加上了大括號 {}，如下圖所示。這代表它已成為一個陣列公式，可以正確計算出總金額。

重點整理

在學習這個技巧時，可以記住以下幾個關於陣列公式與 SUM 函數的重點：

1. 處理特殊符號：若陣列公式的計算範圍內包含文字或特殊符號，可以使用 IF 函數來預先處理，將其轉換為 0 或其他數值，以避免計算錯誤。

2. 搭配 SUM 函數：陣列公式若沒有與 SUM 這類的彙總函數一起使用，只會回傳範圍中的第一個計算結果。

3.  函數會忽略非數字：SUM(範圍) 會自動忽略範圍中的文字儲存格，但若是在公式內部進行運算（如乘法）時遇到文字，仍會出現 #VALUE! 錯誤。

4. 公式的好處：一般公式（例如 A1+A2）在計算時，有時會自動將「文字型」的數字轉換為數值再計算，但 SUM 函數不會。

5. 輸入方式：輸入陣列公式後，務必按下 Ctrl+Shift+Enter。

6. 成功標記：若輸入成功，公式前後會自動出現大括號 {}。

透過這個方法，您就可以設計出更有彈性、更人性化的團購表單，同時確保後端計算的準確性。