Excel跨檔案VLOOKUP教學：告別複雜公式，初學者也能輕鬆搞定的簡單技巧

在使用Excel處理資料時，常常會遇到需要從另一個Excel檔案查找對應資料並貼回目前檔案的情況。最常用的工具就是VLOOKUP函數。然而，當涉及到跨檔案查找，並且需要處理查無資料的狀況（#N/A錯誤）時，常常需要結合IF、ISNA等函數，或是處理相對/絕對參照位置，這對於Excel初學者來說可能會感到有些複雜且困難。

今天的教學影片（來源：[原影片，雖然連結未提供，但可在此註明來源]）提供了一個相對簡單的方法，特別適用於只需要查找並貼回的欄位數量不多的情況。這種方法只使用單一的VLOOKUP函數，透過一些小技巧來簡化複製公式的過程。

適用情境：

你有兩個Excel檔案：

1. 檔案A (主資料檔)：包含大量資料，但缺少某些欄位的資訊。

2. 檔案B (查找來源檔)：包含檔案A所缺少的欄位資訊，可以透過某個共通的「關鍵值」（例如：准考證號碼、員工編號、產品代碼等）來與檔案A進行關聯。

目標：

將檔案B中的特定欄位資料，根據共通關鍵值，查找並填入檔案A對應的空白欄位中。

傳統方法的挑戰：

• 需要設定絕對參照（$符號）避免公式複製時範圍跑掉。

• 處理查無資料（#N/A）時，需額外使用IFERROR或IF(ISNA(...))等函數包裝，公式變長。

本篇教學的「簡單版」步驟：

這個方法的精髓在於巧妙地複製「公式文字」，而非直接複製「儲存格」，以簡化修改過程。

1. 前置作業： 請務必同時開啟檔案A和檔案B兩個Excel檔案。

2. 建立第一個VLOOKUP公式：

   • 在檔案A中，找到第一個需要填入資料的目標儲存格（例如AL2）。

   • 輸入=VLOOKUP(。

   • lookup_value (查找值)：點選檔案A同一列的「關鍵值」儲存格（例如B2，假設是報名編號）。

   • table_array (查找範圍)：切換到檔案B，選取包含「關鍵值欄位」作為第一欄開始，一直到包含所有需要查找欄位的整個資料範圍（例如 範例B!$C$1:$L$6）。 注意：跨檔案選取時，Excel通常會自動加上絕對參照

   • col_index_num (回傳欄位)：輸入第一個目標欄位在查找範圍中是第幾欄的數字（例如 7）。

   • range_lookup (比對方式)：輸入 FALSE 或 0 代表需要精確符合。

   • 完成公式，按Enter。例如：=VLOOKUP(B2,'[範例B.xls]工作表1'!$C$1:$L$6,7,FALSE)

3. 填滿第一欄： 將剛剛建立好公式的儲存格（AL2）向下填滿（點兩下右下角填滿控點）。

4. 複製「公式文字」（關鍵步驟）：

   • 點選第一個公式儲存格（AL2）。

   • 進入「編輯模式」（方法：按F2鍵、或點兩下儲存格、或點擊上方的資料編輯列）。

   • 在資料編輯列中，選取所有的公式文字（按Ctrl+A）。

   • 複製選取的文字（按Ctrl+C）。

   • 【非常重要！】按下鍵盤左上角的 。這會取消儲存格的編輯狀態和選取框，但剛才複製的公式文字仍在剪貼簿中。

5. 貼上並修改後續欄位公式：

   • 點選下一個目標欄位的第一個儲存格（例如AM2）。

   • 進入「編輯模式」（同步驟4）。

   • 在資料編輯列中，貼上剛才複製的公式文字（按Ctrl+V）。

   • 只修改公式最後的 col_index_num (回傳欄位) 數字，改成下一個欄位對應的編號（例如從7改成8）。

   • 按Enter。

6. 重複貼上與修改： 重複步驟5，為剩下的目標欄位（例如AN2, AO2）貼上公式並修改對應的回傳欄位編號（例如9, 10）。

7. 填滿後續欄位： 選取剛剛修改好的AM2, AN2, AO2等儲存格，一起向下填滿。

8. （建議）移除公式，保留數值：

   • 選取所有剛剛用公式填入資料的範圍（AL, AM, AN, AO欄）。

   • 複製（Ctrl+C）。

   • 在原地按右鍵，選擇「選擇性貼上」->「值」。這樣可以移除公式連結，只保留查找到的結果，避免未來來源檔案B關閉或移動時產生錯誤。

9. （選擇性）清除#N/A錯誤：

   • 如果希望將查無資料的#N/A清空。

   • 選取結果範圍。

   • 使用「尋找與取代」功能（Ctrl+H）。

   • 尋找目標：輸入 #N/A。

   • 取代為：保留空白（不要輸入任何東西）。

   • 點選「全部取代」。

總結：

這個方法利用了「複製公式文字」並搭配「ESC取消選取」的技巧，避免了直接複製儲存格可能導致的參照位移問題，也省去了為每個欄位重新輸入大部分相同VLOOKUP語法的麻煩。雖然最終還是需要手動清除#N/A（或者在轉換成值之後再處理），但對於只需要查找少量欄位、且對複雜公式感到困擾的初學者來說，是一個值得嘗試的簡便作法。

範例檔案下載：

您可以從以下連結下載影片中使用的範例檔案來練習：

https://drive.google.com/drive/folders/13Axv8fiW1rQzvyQj336dTAtlHReP3NMu

(請下載 vlookup_2_file.zip 並解壓縮)

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 中需要動態參照不同工作表（特別是按月份命名）資料的問題，大幅提升報表製作的效率與正確性。