☰

從一個範圍中提取另一個範圍中缺少的值

tag-icon • tag-icon microsoft-excel vlookup array
從一個範圍中提取另一個範圍中缺少的值

我在 Excel 中有兩列，“ROSTER”和“PRESENT”，如下所示：

列影像

有沒有一個公式可以實現“NOT HERE”列？我嘗試使用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 版本，它還會消除該版本中的錯誤。

相關內容