Excel 高手之路！一次搞懂跨表格的陣列公式，解決團購金額計算難題！

今天我們要來探討一個在 Excel 中非常實用的進階技巧，這個問題延伸自我們先前的教學。上次我們學會了如何使用陣列公式計算單一欄位的團購總金額，而今天，我們要挑戰的是更複雜的情境：如何計算跨越兩個不連續表格的總金額？

許多朋友在嘗試自行延伸應用時，常常會遇到計算結果不正確的問題。就像我的同事一樣，他試著將單欄公式應用在兩欄上，結果算出來的金額是 3180，但正確答案應該是 3170。究竟問題出在哪裡呢？讓我們一步步拆解，並學習正確的解決方案。

問題情境：錯誤的公式與結果

首先，讓我們看看這個案例。我們有兩個團購表格，分別羅列了不同品項的價格與訂購數量。

同事所使用的公式如下：

={SUM(D2:D21)*SUM(E2:I21)+SUM(M2:M21)*SUM(N2:R21)}

這個公式的邏輯是「將第一個表格的價格總和乘以數量總和，再加上第二個表格的價格總和乘以數量總和」。這個思路乍看之下似乎合理，但實際上是錯誤的。我們需要的是將**每一列的「單價 × 數量」**計算出來後，再將所有結果相加，而不是「總價 × 總量」。這就是金額產生誤差的根本原因。

此外，這個方法還有一個致命傷：如果數量欄位中包含非數字的文字（例如本例中的星號 *），SUM 函數會直接忽略它們，導致數量加總錯誤，進而使得整個計算失準。

正確解法三步驟

要正確地解決這個問題，我們需要結合資料清理與正確的陣列公式。

陣列公式在進行乘法運算時，範圍內不能有文字。因此，我們必須先將數量欄位中的星號 * 清除。

注意！ 你不能直接使用「尋找與取代」功能來取代 *，因為在 Excel 中，星號是「萬用字元」，代表任何字串。如果你直接取代 *，會導致工作表內所有資料都被清空！

正確的做法是：

1. 按下 Ctrl + H 開啟「尋找與取代」對話框。

2. 在「尋找目標(N)」欄位中輸入 ~* （波浪號 + 星號）。波浪號 ~ 是個跳脫字元，它能告訴 Excel 我們要尋找的是「星號本身」，而不是將它當作萬用字元。

3. 「取代成(E)」欄位保持空白。

4. 點擊「全部取代」，即可安全地將所有星號清除。

資料清理完畢後，就可以來撰寫正確的公式了。我們需要一個能夠處理兩組「價格 × 數量」運算，並將結果匯總的陣列公式。

正確的公式結構如下：
=SUM(價格範圍1*數量範圍1, 價格範圍2*數量範圍2)

套用到我們的範例中，公式即為：
=SUM(D2:D21*E2:I21, M2:M21*N2:R21)

這個公式的運作原理是：

1. D2:D21*E2:I21：將左邊表格的價格陣列與數量陣列進行逐一相乘，產生一個新的加總前陣列。

2. M2:M21*N2:R21：對右邊表格做同樣的運算。

3. SUM(...)：最後，SUM 函數會將這兩組運算後的所有數值全部加總起來，得到最終的正確金額。

這是使用陣列公式最重要的一步！在輸入完公式後，絕對不能直接按 。你必須同時按下 Ctrl + Shift + Enter 這三個鍵。

成功後，你會看到公式的前後被 Excel 自動加上了大括號 {}，如下所示，並且儲存格會顯示正確的總金額 3170。

{=SUM(D2:D21*E2:I21, M2:M21*N2:R21)}

總結

今天我們學到了幾個非常重要的 Excel 觀念：

1. 陣列公式的邏輯：要計算總金額，應該使用 SUM(價格範圍*數量範圍) 的結構，而非 SUM(價格範圍)*SUM(數量範圍)。

2. 處理特殊字元：在「尋找與取代」中，若要尋找 * 或 ? 等萬用字元本身，需在前面加上跳脫字元 ~。

3. SUM 函數的多重陣列：SUM 函數可以透過逗號來分隔，同時加總多個陣列運算的結果。

4. 陣列公式的輸入：永遠記得要使用 Ctrl + Shift + Enter 來完成輸入。

希望這個教學能幫助大家更深入地理解 Excel 陣列公式的強大之處，並避免在實務中犯下同樣的錯誤。