2025年4月29日 星期二

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

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

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

適用情境:

你有兩個Excel檔案:

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

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

目標:

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

傳統方法的挑戰:

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

  • 處理查無資料(#N/A)時,需額外使用IFERRORIF(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,為剩下的目標欄位(例如AN2AO2)貼上公式並修改對應的回傳欄位編號(例如910)。

  7. 填滿後續欄位: 選取剛剛修改好的AM2AN2AO2等儲存格,一起向下填滿。

  8. (建議)移除公式,保留數值:

    • 選取所有剛剛用公式填入資料的範圍(ALAMANAO欄)。

    • 複製(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 並解壓縮)

沒有留言:

張貼留言

注意:只有此網誌的成員可以留言。

[教學] MySQL 如何匯入大型 SQL 資料檔?利用 EmEditor 分割檔案與 phpMyAdmin 匯入實戰

在網站維運的過程中,資料庫的備份與還原是家常便飯。但當資料庫成長到一定規模時,單純的匯出匯入就可能變成一場惡夢。這次,我們將分享一個處理大型 MySQL 資料庫(超過 1GB)的實戰經驗,特別是在有資源限制的虛擬主機環境(如 GoDaddy)中,如何巧妙地完成匯入工作。 前言:我...