doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

INDEXとMATCHでn番目の一致を取得する

スポンサーリンク

INDEXとMATCHでn番目の一致を取得する

今回の数式を利用することで、最初の一致だけではなく、二番目、三番目といった指定での検索が可能になります。

f:id:doodle-on-web:20200817165857p:plain

一般的な式

{=INDEX(配列,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),))}

説明

数式を使用してデータのセットから複数の一致する値を取得するには、IF関数とSMALL関数を使用して、各一致の行番号を計算し、その値をINDEXに渡しています。図に入力されているH7の式は次のとおりです。

{=INDEX(テーブル35[料金],SMALL(IF(テーブル35[ID]=$H$3,ROW(テーブル35[ID])-ROW(INDEX(テーブル35[ID],1,1))+1),G6))}

配列=テーブル35[料金](D4:D14)、vals=テーブル35[ID](C4:C14)、val=(H3)、nth=G6

これは配列数式であり、Ctrl + Shift + Enterで入力する必要があることに注意してください。

この数式の仕組み

この数式は、特定の位置にある配列の値を取得するINDEX関数を利用しています。

この場合のINDEX関数は、取得したいnの値はG列を参照し、その値をもとに行を特定します。このINDEX関数が取得する行数が「n番目」の一致に対応していきます。

さらにIF関数では、一致する行が含まれる行を特定する作業を行い、SMALL関数ではそのリストからn番目の値を返します。IF関数での数式は次のとおりです。

ID = H3

この配列を生成します:

{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}

2番目と6番目の位置で顧客IDが一致し、TRUEと表示されていることに注意してください。IFの「trueの場合の値」引数は 、次の式で相対行番号のリストを生成します。

ROW(ID)-ROW(INDEX(ID,1,1))+1

これはこの配列を生成します:

{1;2;3;4;5;6;7}

次に、この配列は論理テスト結果によって「フィルター」され、IF関数は次の配列結果を返します。

{1;FALSE;FALSE;4;FALSE;FALSE;FALSE}

行1と行2に有効な行番号があることに注意してください。

次に、この配列はSMALLによって処理されます。SMALLは、列Hの値を使用して「n番目」の値を返すように構成されています。SMALL関数は、配列内の論理値TRUEおよびFALSEを自動的に無視します。最終的に、式は次のようになります。

=INDEX(料金,2) // H6, returns \1,200
=INDEX(料金,6) // H7, returns \600

エラーの処理

特定のIDに一致するものがなくなると、SMALL関数は#NUMエラーを返します。このエラーを処理するには、IFERROR関数を使用するか、一致をカウントするロジックを追加し、列Hの数値が一致カウントよりも大きくなると処理を中止します。この例は、1つのアプローチを示しています。