☰

ある範囲から別の範囲にない値を抽出する

tag-icon • tag-icon microsoft-excel vlookup array
ある範囲から別の範囲にない値を抽出する

Excel には、以下に示すように、「ROSTER」と「PRESENT」という 2 つの列があります。

列イメージ

「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

数式を入力するには、「Not Here」列のセルを選択します（私の場合は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)),"")}

等...

これは冗長な解決策のように見えますが、ワークシート内のどこにテーブルを配置しても機能します。また、#numExcel 2007 を使用している場合は、この方法でエラーも解消されます。

関連情報