【Excel教學】如何輕鬆製作每月自動化應收對帳單？陣列公式搞定！

每到月底，會計或業務人員最頭痛的工作之一，就是要為數十家甚至上百家客戶製作應收帳款對帳單。傳統的做法通常是從總明細表中，一筆一筆複製貼上，不僅耗時費力，也容易出錯。

今天，我們將分享一個進階技巧，利用 Excel 的陣列公式，讓這項繁瑣的工作「自動化」。想像一下，你只需要建立一個對帳單範本，之後每增加一間新公司，只要**「複製工作表」並「改個公司名稱」**，整張對帳單的明細就會自動更新！

核心概念

這次的教學將會運用到以下幾個關鍵函數，並將它們組合成一個強大的「陣列公式」：

1. IF：進行條件判斷，篩選出符合特定公司的資料。

2. ROW：取得資料所在的列號。

3. SMALL：從符合條件的列號中，依序找出第1筆、第2筆、第3筆...的資料。

4. INDEX：根據找到的列號，從原始資料中取出對應的內容。

5. IFERROR：美化報表，將找不到資料時出現的錯誤訊息變為空白。

6. 陣列公式：這是本次教學的靈魂，需要使用 [Ctrl] + [Shift] + [Enter] 來完成輸入。

範例檔案

為了方便學習，您可以下載本次教學的範例檔案，跟著步驟一起操作。
範例檔案下載： https://drive.google.com/file/d/1ap45b2sSRLCUqK6V10Fc3ECUbJLiUjCE/view

步驟詳解

我們的目標是，在各家公司的對帳單工作表中，自動抓取「明細」工作表裡的相關資料。

首先，我們要找出在「明細」表中，哪些資料是屬於目前這家公司的。

• 邏輯： 如果「明細」工作表的公司名稱欄位，等於這張對帳單的公司名稱，就傳回該筆資料的「列號」，否則就傳回一個極大的數字。

• 公式： IF(明細!$B$1:$B$99=$B$4, ROW(明細!$B$1:$B$99), 10^99)

這個公式會產生一個陣列，裡面包含了所有符合條件的列號以及許多極大的數字。

接著，我們需要從上一步產生的陣列中，一筆一筆地把有效的列號取出來。

• 邏輯： 使用 SMALL 函數，搭配 ROW(A1) 這個小技巧，來取得陣列中「第k小」的數值。當公式向下拖曳時，ROW(A1) 會變成 ROW(A2)、ROW(A3)...，進而依序取得第1小、第2小、第3小的列號。

• 公式： SMALL(IF(...), ROW(A1))

有了正確的列號，我們就可以用 INDEX 函數像查字典一樣，把資料取出來。

• 邏輯： INDEX 函數能根據指定的「列號」和「欄號」，從一個範圍中傳回對應的儲存格內容。

• 公式： INDEX(明細!A$1:A$99, SMALL(...))

這裡我們用 INDEX 去查詢「明細」表的A欄（發票日期），並傳回由 SMALL 函數找到的列號所對應的日期。

當所有符合的資料都抓完後，後續的儲存格會因為找不到更多資料而顯示錯誤訊息 (如 #NUM! 或 #REF!)。為了讓報表更乾淨，我們用 IFERROR 把它們變成空白。

• 邏輯： 如果公式計算結果是錯誤的，就顯示為 "" (空白)，否則正常顯示計算結果。

• 公式： IFERROR(INDEX(...), "")

最終公式

現在，我們將所有步驟組合起來。在對帳單的第一筆資料儲存格（例如 A10）輸入以下完整公式：

=IFERROR(INDEX(明細!A$1:A$99,SMALL(IF(明細!$B$1:$B$99=$B$4,ROW(明細!$B$1:$B$99)),ROW(A1))),"")

❗❗❗ 最重要的提醒 ❗❗❗
輸入完公式後，請務必按下 ，而不是只有 Enter。成功後，你會在公式編輯列看到公式前後被大括號 {} 包起來，這才代表它已成為陣列公式。

完成第一個儲存格後，就可以將公式向右、再向下拖曳，整張對帳單的資料就神奇地自動完成了！

總結

透過這個 INDEX + SMALL + IF 的強大陣列公式組合，我們成功建立了一個可重複利用的自動化對帳單範本。未來無論客戶如何增減，你都能在幾秒鐘內完成對帳單的製作，大幅提升工作效率，告別繁瑣的複製貼上地獄！