在兩個工作表中找到相似數據並放入第三個工作表

在兩個工作表中找到相似數據並放入第三個工作表

將兩個不同 Excel 電子表格中的資料合併到第三個 Excel 電子表格中的最佳/最簡單方法是什麼?電子表格 1 將只包含使用者名稱。電子表格 2 將包含大量信息,包括名字、姓氏、部門、用戶名等。

答案1

您要使用的功能是VLOOKUP.如何執行此操作將稍微取決於工作表的排列方式,但所有工作表都將遵循相同的語法:

=VLOOKUP( lookup value, table array, column index number, range lookup)

  • lookup value是您要搜尋的資料。
  • table array定義您要從中擷取資料的儲存格,包括包含搜尋目標的資料列
  • column index numbertable array是要從中提取資訊的列的索引。 (例如:對於 A:E 數組,D 列將為4。)
  • range lookup是一個 TRUE/FALSE 選項,用於指定是否可以接受近似匹配,或者是否需要精確匹配。為了簡單起見,我總是將其設為FALSE。如果您需要更多詳細信息,請在 Excel 中按 F1。

根據資料是否全部位於同一工作簿中,公式的格式會略有不同。我將在下面給出每個例子。

重要的提示:您使用的搜尋字詞lookup value 必須table array可以在VLOOKUP 工作的第一列中找到。


第一個例子:所有資料都將位於同一 Excel 工作簿中,但位於不同的工作表上。第一張表標記為“用戶名”,僅包含用戶名。第二張表稱為“用戶資料”,包含所有用戶詳細資料。我們將第三張表稱為「查找結果」。 「使用者資料」表包含五列,A:E

  1. 確保「使用者資料」電子表格的 A 欄位包含所有使用者名稱。
  2. 將「使用者名稱」中的所有使用者名稱複製到「尋找結果」。
    • 我假設您正在使用標題行,因此「尋找結果」上的第一個使用者名稱將出現在 A2 中。
  3. 「找出結果」中 B2 的公式應為: =VLOOKUP(A2,'User Data'!A:B,2,FALSE)
  4. 在「找出結果」中 B3 的公式應為: =VLOOKUP(A3,'User Data'!A:B,2,FALSE)
  5. 在「找出結果」中 C2 的公式應為: =VLOOKUP(A2,'User Data'!A:C,3,FALSE)

現在您應該看到這裡的模式了。對於每一列,您應該能夠在第一個儲存格(例如:B2)中寫入 VLOOKUP 公式,然後將該公式填入工作表的其餘部分。然而,公式的剪切和粘貼穿過columns 不是那麼簡單 - 您需要更新table arraycolumn index number值。


第二個例子:每個資料集都保存在自己的 Excel 工作簿中。工作簿中的工作表名稱是預設的(即:第一個工作表是「Sheet1」)。工作簿檔案名稱為「Usernames.xlsx」、「User Data.xlsx」和「Lookup Results.xlsx」。這些都位於名為「我的電子表格」的資料夾中,該資料夾位於名為「我」的使用者的桌面上。

  1. 確保在「User Data.xlsx」表的 A 列中包含所有使用者名稱。
  2. 將所有使用者名稱從「Usernames.xlsx」複製到「Lookup Results.xlsx」。
    • 再次假設您使用標題行,這將從 A2 開始。
  3. 「Lookup Result.xlsx」中 B2 的公式應為=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  4. 「Lookup Result.xlsx」中 B3 的公式應為=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  5. 「Lookup Result.xlsx」中 C2 的公式應為=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

同樣,您現在應該能夠看到這裡的模式。根據需要沿行和列剪切/貼上/調整,然後就完成了。


這裡要記住的另一件事是,此表不會因「使用者名稱」資料的變更而自動更新。可以使用此方法拉取「使用者資料」表的更改,但如果您也想追蹤「使用者名稱」中的更改,則需要更高級的技術。

答案2

只有當兩張表中的資料相同時,Vlookup 和類似的工具才會起作用。
我相信您需要的是 Excel 的模糊邏輯插件。這將允許您根據幾個不同的參數找到類似的結果。檢查下載頁面

答案3

HLOOKUP/VLOOKUP - 使用公式從電子表格 1 中獲取用戶名,然後使用用戶名作為鍵,電子表格 2 作為 HLOOKUP/VLOOKUP 一個實例的查找矩陣(我不知道哪個是哪個,因為我是使用非英文Excel 版本)。

答案4

您可以使用 Excel 文件查詢:

  • 定義電子表格 1 中資料集的名稱(「公式」標籤 ->「定義名稱」)
  • 定義電子表格 2 中資料集的名稱
  • 在電子表格 1 中,轉到“資料”選項卡,選擇“來自其他來源”,然後從下拉清單中選擇“來自 Microsoft Query”
  • 選擇另一個電子表格檔案並確認您要手動合併列
  • 在以下視窗“從 Excel 文件查詢”中,將第一個資料集的“用戶名”列拖放到第二個資料集的“用戶名”列中 - 將創建這些列之間的鏈接
  • 前往“檔案”選單,點擊“將資料返回 MS Office Excel”,將彈出“匯入資料”對話框
  • 選擇您想要將符合資料匯入的工作表
  • 按一下「確定」 - 您應該會看到與兩個電子表格中的欄位相符的數據

相關內容