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单元格中输入以下公式,然后按 输入 得到结果:

=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 公式将返回 特质.
  • 如果(国际标准协会(比赛(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 中输入以下公式,然后按 输入 得到结果:

= if(isna(vlookup)(vlookup(30002,$ B $ 6:$ B $ 10,1,FALSE)),"丢失","找到")

或者,使用单元格引用使公式动态:

= if(isna(vlookup)(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 FALSE 强制 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 公式将返回 特质.
  • 如果(国际标准协会(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 中输入以下公式,然后按 输入 得到结果:

=如果(计数($ 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   产品特性
建议地点