Excel技巧：使用 INDIRECT 動態抓取上個月工作表資料

前言

在管理每月更新的 Excel 報表時（例如：排班表、銷售報表），我們常常需要參考上個月工作表的某些結算數據（如下圖中的「上月結餘」）。如果每個月都手動修改公式中的工作表名稱，不僅耗時，也容易出錯。今天我們將介紹如何利用 INDIRECT 和 TEXT 函數，讓 Excel 自動抓取上一個月工作表的特定儲存格資料。

問題情境

假設我們每個月都會建立一個新的工作表，工作表名稱格式為「109MM」（例如 8 月是 10908，7 月是 10907）。目前 8 月份的工作表 (10908) 需要在 AI5 儲存格顯示 7 月份工作表 (10907) 中 AM5 儲存格的「結餘時數」。

傳統作法可能是在 10908 工作表的 AI5 輸入：

=10907!AM5

這種寫法是「寫死」的。當你複製這個檔案製作 9 月份報表 (10909) 時，這個公式仍然會指向 10907，而不是你期望的 10908，必須手動修改，非常不便。

解決方案：INDIRECT + TEXT 函數

我們可以利用 INDIRECT 函數將文字字串轉換為實際的儲存格參照，再搭配 TEXT 函數動態產生上個月的工作表名稱字串。

步驟說明

1. 取得當前月份： 假設我們在當前工作表 (例如 10908) 的 A1 儲存格存放了當前的月份數字（例如 8）。

2. 計算上個月份： 將 A1 的值減 1，得到上個月的月份數字（8 - 1 = 7）。

3. 格式化月份字串： 使用 TEXT 函數將上個月的數字格式化為兩位數（不足補零）。

   • TEXT(A1-1, "00") 會將 7 轉換為文字 "07"。

4. 組合參照字串： 利用 & 符號將固定的前綴、動態計算的月份字串以及儲存格位址組合起來，形成一個完整的參照文字字串。

   • "109" & TEXT(A1-1, "00") & "!AM5"

   • 以上公式在 A1 為 8 時，會產生文字字串 "10907!AM5"。

5. 使用 INDIRECT 轉換： 最後，將組合好的文字字串放入 INDIRECT 函數中。INDIRECT 會將這個字串解釋為實際的儲存格參照，並抓取該儲存格的值。

最終公式

在 10908 工作表的 AI5 儲存格輸入以下公式：

=INDIRECT("109" & TEXT(A1-1,"00") & "!AM5")

說明：

• A1：存放當前月份數字的儲存格。

• "109"：工作表名稱的固定前綴。

• TEXT(A1-1,"00")：動態產生上個月的兩位數月份字串。

• "!AM5"：要抓取的目標儲存格位址。

優點

• 動態更新： 未來只需要修改 A1 儲存格的月份數字，公式就會自動抓取正確的上個月資料。

• 複製方便： 複製工作表或檔案製作下個月報表時，公式不需修改即可正確運作。

結論

透過 INDIRECT 和 TEXT 函數的組合，我們可以輕鬆解決 Excel 中需要動態參照不同工作表（特別是按月份命名）資料的問題，大幅提升報表製作的效率與正確性。