查找缺失值
在某些情況下,您需要比較兩個列表以檢查 Excel 中的列表 B 中是否存在列表 A 的值。 例如,您有一個產品列表,您想檢查您的列表中的產品是否存在於您的供應商提供的產品列表中。 為了完成這項任務,我們在下面列出了三種方法,請隨意選擇您喜歡的一種。
使用 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中 因為你希望它是動態的。 輸入公式後,向下拖動填充手柄以將公式應用於以下單元格。
公式說明
這裡我們以下面的公式為例:
=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中 因為你希望它是動態的。 輸入公式後,向下拖動填充手柄以將公式應用於以下單元格。
公式說明
這裡我們以下面的公式為例:
=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中 因為你希望它是動態的。 輸入公式後,向下拖動填充手柄以將公式應用於以下單元格。
公式說明
這裡我們以下面的公式為例:
=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 時返回的值。
相關功能
IF函數是Excel工作簿中最簡單,最有用的函數之一。 它根據比較結果執行一個簡單的邏輯測試,如果結果為TRUE,則返回一個值,如果結果為FALSE,則返回另一個值。
Excel MATCH 函數在單元格範圍內搜索特定值,並返回該值的相對位置。
Excel VLOOKUP函數通過在表的第一列上進行匹配來搜索值,並從同一行中的特定列返回相應的值。
COUNTIF 函數是 Excel 中的一個統計函數,用於統計符合條件的單元格數量。 它支持邏輯運算符(<>、=、> 和 <),以及用於部分匹配的通配符(? 和 *)。
相關公式
要在 Excel 中的某個範圍內查找包含特定文本字符串的第一個匹配項,您可以使用帶有通配符 - 星號 (*) 和問號 (?) 的 INDEX 和 MATCH 公式。
有時您需要 Excel 根據部分信息檢索數據。 要解決此問題,您可以將 VLOOKUP 公式與通配符 - 星號 (*) 和問號 (?) 結合使用。
有時我們需要在 Excel 中尋找近似匹配來評估員工的表現、給學生評分、根據重量計算郵資等。在本教程中,我們將討論如何使用 INDEX 和 MATCH 函數來檢索我們需要的結果。
在某些情況下,您可能需要根據多個條件查找最接近或近似的匹配值。 結合 INDEX、MATCH 和 IF 函數,您可以在 Excel 中快速完成。
最佳辦公效率工具
Kutools for Excel-幫助您從人群中脫穎而出
🤖 | Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行 | 生成代碼 | 建立自訂公式 | 分析數據並產生圖表 | 呼叫 Kutools 函數... |
熱門特色: 尋找、突出顯示或識別重複項 | 刪除空白行 | 合併列或儲存格而不遺失數據 | 沒有公式的回合 ... | |
超VLookup: 多重標準 | 多重價值 | 跨多頁 | 模糊查詢... | |
副詞。 下拉清單: 簡易下拉列表 | 依賴下拉列表 | 多選下拉列表... | |
欄目經理: 新增特定數量的列 | 移動列 | 切換隱藏列的可見性狀態 | 將列與 選擇相同和不同的單元格 ... | |
特色功能: 網格焦點 | 設計圖 | 大方程式酒吧 | 工作簿和工作表管理器 | 資源庫 (自動文字) | 日期選擇器 | 合併工作表 | 加密/解密單元格 | 按清單發送電子郵件 | 超級濾鏡 | 特殊過濾器 (過濾粗體/斜體/刪除線...)... | |
前 15 個工具集: 12 文本 工具 (添加文本, 刪除字符 ...) | 50+ 圖表 類型 (甘特圖 ...) | 40+ 實用 公式 (根據生日計算年齡 ...) | 19 插入 工具 (插入二維碼, 從路徑插入圖片 ...) | 12 轉化 工具 (數字到單詞, 貨幣兌換 ...) | 7 合併與拆分 工具 (高級合併行, 拆分 Excel 儲存格 ...) | ... 和更多 |
Kutools for Excel 擁有超過 300 個功能, 確保只需點擊一下即可獲得您所需要的...
Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯
- 一秒鐘即可在數十個打開的文檔之間切換!
- 每天為您減少數百次鼠標單擊,告別鼠標手。
- 查看和編輯多個文檔時,將您的工作效率提高 50%。
- 為 Office(包括 Excel)帶來高效的選項卡,就像 Chrome、Edge 和 Firefox 一樣。