Note: The other languages of the website are Google-translated. Back to English

查找缺失值

在某些情況下,您需要比較兩個列表以檢查 Excel 中的列表 B 中是否存在列表 A 的值。 例如,您有一個產品列表,您想檢查您的列表中的產品是否存在於您的供應商提供的產品列表中。 為了完成這項任務,我們在下面列出了三種方法,請隨意選擇您喜歡的一種。

找到缺失值 1

使用 MATCH、ISNA 和 IF 查找缺失值
使用 VLOOKUP、ISNA 和 IF 查找缺失值
使用 COUNTIF 和 IF 查找缺失值


使用 MATCH、ISNA 和 IF 查找缺失值

找出來 如果您列表中的所有產品都存在於您的供應商列表中 如上面的屏幕截圖所示,您可以首先使用 MATCH 函數檢索您的列表中的產品(列表 A 的值)在供應商列表(列表 B)中的位置。 當未找到產品時,MATCH 將返回 #N/A 錯誤。 然後,您可以將結果提供給 ISNA,將 #N/A 錯誤轉換為 TRUE,這意味著這些產品缺失。 然後 IF 函數將返回您期望的結果。

通用語法

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ 注意:您可以根據需要將“Missing”、“Found”更改為任何值。

  • 查找值: 用於檢索其位置的值 MATCH(如果它存在於 查找範圍 或 #N/A 錯誤(如果沒有)。 這裡指的是您列表中的產品。
  • 查找範圍: 要比較的單元格範圍 Lookup_Array中. 這裡指的是供應商的產品清單。

找出來 如果您列表中的所有產品都存在於您的供應商列表中, 請複製或在H6單元格中輸入以下公式,然後按 Enter 得到結果:

=IF(ISNA(匹配(30002,$ B $ 6:$ B $ 10,0)),"丟失","找到")

或者,使用單元格引用使公式動態:

=IF(ISNA(匹配(G6,$ B $ 6:$ B $ 10,0)),"丟失","找到")

√ 注意:上面的美元符號($)表示絕對引用,表示 查找範圍 當您將公式移動或複製到其他單元格時,公式中的內容不會改變。 但是,沒有美元符號添加到 Lookup_Array中 因為你希望它是動態的。 輸入公式後,向下拖動填充手柄以將公式應用於以下單元格。

找到缺失值 2

公式說明

這裡我們以下面的公式為例:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

  • 比賽(G8,$B$6:$B$10,0): 匹配類型 0 強制 MATCH 函數返回一個數值,指示第一個匹配項的位置 3004,單元格 G8 中的值,在數組中 $ B $ 6:$ B $ 10. 但是,在這種情況下,MATCH 找不到查找數組中的值,因此它將返回 #N / A 錯誤。
  • 國際標準協會(比賽(G8,$B$6:$B$10,0)) = 國際標準協會(#N / A): ISNA 致力於找出值是否為“#N/A”錯誤。 如果是,函數將返回TURE; 如果該值不是“#N/A”錯誤,它將返回 FALSE。 所以,這個 ISNA 公式將返回 TURE.
  • 如果(國際標準協會(比賽(G8,$B$6:$B$10,0)),"丟失","找到") = IF(TRUE,"失踪","找到"): 如果 ISNA 和 MATCH 進行的比較為 TRUE,IF 函數將返回 Missing,否則將返回 Found。 所以,公式將返回 失踪.

使用 VLOOKUP、ISNA 和 IF 查找缺失值

要確定您的列表中的所有產品是否都存在於您的供應商列表中,您可以將上面的 MATCH 函數替換為 VLOOKUP,因為它與 MATCH 的工作原理相同,如果該值不存在,它將返回 #N/A 錯誤另一個列表,或者我們說它丟失了。

通用語法

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ 注意:您可以根據需要將“Missing”、“Found”更改為任何值。

  • 查找值: 如果 VLOOKUP 存在於中,則用於檢索其位置的值 查找範圍 或 #N/A 錯誤(如果沒有)。 這裡指的是您列表中的產品。
  • 查找範圍: 要比較的單元格範圍 Lookup_Array中. 這裡指的是供應商的產品清單。

要了解您列表中的所有產品是否都存在於您的供應商列表中,請複製或在單元格 H6 中輸入以下公式,然後按 Enter 得到結果:

=IF(ISNA(VLOOKUP(30002,$ B $ 6:$ B $ 10,1,FALSE)),"丟失","找到")

或者,使用單元格引用使公式動態:

=IF(ISNA(VLOOKUP(G6,$ B $ 6:$ B $ 10,1,FALSE)),"丟失","找到")

√ 注意:上面的美元符號($)表示絕對引用,表示 查找範圍 當您將公式移動或複製到其他單元格時,公式中的內容不會改變。 但是,沒有美元符號添加到 Lookup_Array中 因為你希望它是動態的。 輸入公式後,向下拖動填充手柄以將公式應用於以下單元格。

找到缺失值 3

公式說明

這裡我們以下面的公式為例:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

  • VLOOKUP(G8,$B$6:$B$10,1,FALSE): range_lookup 強制 VLOOKUP 函數查找並返回完全匹配的值 3004,單元格 G8 中的值。 如果lookup_value 3004 存在於 1數組的第 st 列 $ B $ 6:$ B $ 10,VLOOKUP 將返回該值; 否則,它將返回 #N/A 錯誤值。 這裡,數組中不存在 3004,因此,結果將是 #N / A.
  • 國際標準協會(VLOOKUP(G8,$B$6:$B$10,1,FALSE)) = 國際標準協會(#N / A): ISNA 致力於找出值是否為“#N/A”錯誤。 如果是,函數將返回TURE; 如果該值不是“#N/A”錯誤,它將返回 FALSE。 所以,這個 ISNA 公式將返回 TURE.
  • 如果(國際標準協會(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"丟失","找到") = IF(TRUE,"失踪","找到"): 如果 ISNA 和 VLOOKUP 進行的比較為 TRUE,IF 函數將返回 Missing,否則將返回 Found。 所以,公式將返回 失踪.

使用 COUNTIF 和 IF 查找缺失值

要了解您的列表中的所有產品是否都存在於您的供應商列表中,您可以使用帶有 COUNTIF 和 IF 函數的更簡單的公式。 該公式利用了 Excel 會將除零 (0) 以外的任何數字計算為 TRUE 的事實。 因此,如果一個值存在於另一個列表中,則 COUNTIF 函數將返回它在該列表中出現的次數,然後 IF 將該數字作為 TURE; 如果列表中不存在該值,則 COUNTIF 函數將返回 0,IF 會將其視為 FALSE。

通用語法

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ 注意:您可以根據需要將“Found”、“Missing”更改為任何值。

  • 查找範圍: 要比較的單元格範圍 Lookup_Array中. 這裡指的是供應商的產品清單。
  • 查找值: COUNTIF 用於返回其出現次數的值 查找範圍. 這裡指的是您列表中的產品。

要了解您列表中的所有產品是否都存在於您的供應商列表中,請複製或在單元格 H6 中輸入以下公式,然後按 Enter 得到結果:

=如果(計數($ B $ 6:$ B $ 10,30002),"找到","丟失")

或者,使用單元格引用使公式動態:

=如果(計數($ B $ 6:$ B $ 10,G6),"找到","丟失")

√ 注意:上面的美元符號($)表示絕對引用,表示 查找範圍 當您將公式移動或複製到其他單元格時,公式中的內容不會改變。 但是,沒有美元符號添加到 Lookup_Array中 因為你希望它是動態的。 輸入公式後,向下拖動填充手柄以將公式應用於以下單元格。

找到缺失值 4

公式說明

這裡我們以下面的公式為例:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

  • COUNTIF($B$6:$B$10,G8): COUNTIF 函數計算了多少次 3004,單元格 G8 中的值,出現在數組中 $ B $ 6:$ B $ 10. 顯然,數組中不存在 3004,因此,結果將是 0.
  • 如果(COUNTIF($B$6:$B$10,G8),"找到","丟失") = IF(0,"找到","丟失"): IF 函數會將 0 評估為 FALSE。 所以,公式將返回 失踪,當第一次擴充評估為 FALSE 時返回的值。

相關功能

Excel IF功能

IF函數是Excel工作簿中最簡單,最有用的函數之一。 它根據比較結果執行一個簡單的邏輯測試,如果結果為TRUE,則返回一個值,如果結果為FALSE,則返回另一個值。

Excel MATCH功能

Excel MATCH 函數在單元格範圍內搜索特定值,並返回該值的相對位置。

Excel VLOOKUP 函數

Excel VLOOKUP函數通過在表的第一列上進行匹配來搜索值,並從同一行中的特定列返回相應的值。

Excel COUNTIF 函數

COUNTIF 函數是 Excel 中的一個統計函數,用於統計符合條件的單元格數量。 它支持邏輯運算符(<>、=、> 和 <),以及用於部分匹配的通配符(? 和 *)。


相關公式

使用通配符查找包含特定文本的值

要在 Excel 中的某個範圍內查找包含特定文本字符串的第一個匹配項,您可以使用帶有通配符 - 星號 (*) 和問號 (?) 的 INDEX 和 MATCH 公式。

與 VLOOKUP 部分匹配

有時您需要 Excel 根據部分信息檢索數據。 要解決此問題,您可以將 VLOOKUP 公式與通配符 - 星號 (*) 和問號 (?) 結合使用。

與 INDEX 和 MATCH 的近似匹配

有時我們需要在 Excel 中尋找近似匹配來評估員工的表現、給學生評分、根據重量計算郵資等。在本教程中,我們將討論如何使用 INDEX 和 MATCH 函數來檢索我們需要的結果。

查找具有多個條件的最匹配值

在某些情況下,您可能需要根據多個條件查找最接近或近似的匹配值。 結合 INDEX、MATCH 和 IF 函數,您可以在 Excel 中快速完成。


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

您想快速完美地完成日常工作嗎? Kutools for Excel 帶來了 300 種強大的高級功能(合併工作簿、按顏色求和、拆分單元格內容、轉換日期等等......)並為您節省 80% 的時間。

  • 專為1500種工作場景設計,幫你解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,將您的工作效率提高 50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護