☰

从一个范围中提取另一个范围中缺失的值

tag-icon • tag-icon microsoft-excel vlookup array
从一个范围中提取另一个范围中缺失的值

Excel 中有两列“ROSTER”和“PRESENT”，如下所示：

柱状图

是否有公式可以实现“不在此处”列？我尝试使用VLOOKUP()和https://superuser.com/a/289653/135912无济于事 =(

任何帮助，将不胜感激！

谢谢！

答案1

没有内置函数可以单独完成此任务。

您可以在“不在此处”列中尝试此数组公式（MS Excel 2007+）

=IFERROR(INDEX(roster,SMALL(IF(COUNTIF(present,roster)=0,ROW()-1,""),ROW()-1),1),"")

在我的示例中，
roster其中引用的是命名范围，$A$2:$A$21
present它引用的是命名范围$B$2:$B$21

要输入公式，请选择“不在此处”列中的单元格（在我的情况下是C2向下C21），输入公式，然后按Ctrl+ Shift+Enter

在此处输入图片描述

答案2

这可能有点过头了，但确实有效。希望您不介意在得到最终结果（Not Here 2）之前有一个带空格的中间“Not Here”列。

工作溶液图片

幕后花絮：

正在使用的命名范围：

  • 名单：（B3：B19）
  • 现在：（C3:C19）
  • 不在这里：（F3：F19）

数组公式输入到范围 (D3:D19)...

{=IF(ISERROR(MATCH(Roster,Present,0)),Roster,"")}

数组公式输入单元格（E3:E19）...

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A1)),COLUMN(A1)),"")}

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A2)),COLUMN(A2)),"")}

ETC...

虽然这看起来是一个冗长的解决方案，但无论表格放在工作表中的什么位置，它都能发挥作用。#num如果您使用的是 Excel 2007 版本，它还可以消除其中的错误。

相关内容