関数で複数条件で検索して複数データを取り出す:Excel関数の技

スポンサードリンク


更新 2021/06/09

Filter関数を使って複数データを取り出す 配列数式を使って複数データを取り出す
作業列を使って複数データを取り出す
INDEX関数とMATCH関数で取り出す方法 XLOOKUP関数とSEQUENCE関数で取り出す方法
VLOOKUP関数で取り出す方法

サンプルデータ

Filter関数を使う

配列数式を使う

数式の解説

作業列を使う方法

INDEX関数とMATCH関数で取り出す方法

  1. 条件と一致した行に連番を入れます。
    D2セルに=IF(A2=$G$2,COUNTIF($A$2:A2,$G$2),"")と入力して、下方向へD11セルまでコピーします。
    A2セルとG2セルが同じなら、同じデータはA2セルから数えて何個目かを表示しています。
    COUNTIF($A$2:A2,$G$2) の範囲が絶対参照と相対参照になっています。
    =IF(A2=$G$2,COUNTIF($A$2:A2,$G$2),"") A2セルとG2セルが同じでないときは空白にしています。
  2. 作業列の連番順にデータを取り出します。
    G5セルは=IF(MAX($D$2:$D$11)<ROW(A1),"",INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0))) と入力します。
  3. G5セルに入力した数式を右方向、および下方向へコピーすると完成です。
    1. G5セルに=IF(MAX($D$2:$D$11)<ROW(A1),"",INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0))) と入力します。
    2. G5セルを選択し、フィルハンドルを右方向へ、I5セルまでドラッグして数式をコピーします。
    3. G5:I5セルを選択して、フィルハンドルを下方向へドラッグして数式をコピーします。
      下方向へは必要なだけコピーします。一致するデータが表示しきれるだけの行にあらかじめコピーします。

XLOOKUP関数とSEQUENCE関数で取り出す方法

  1. 作業列には、上記と同様に、D2セルに=IF(A2=$G$2,COUNTIF($A$2:A2,$G$2),"")と入力して、下方向へD11セルまでコピーします。
  2. G5セルには =XLOOKUP(SEQUENCE(5),$D$2:$D$11,A2:A11,"") と入力します。
    SEQUENCE(5)は連続した数値の配列(1,2,3,4,5)を作成しています。該当するデータが5個程度を想定しています。多くのデータが該当すると思われる場合はこの値を調整してください。
  3. G5セルの数式を H5セル、I5セルにコピーします。
    H5セルの数式は =XLOOKUP(SEQUENCE(5),$D$2:$D$11,B2:B11,"") となります。
  4. I5セルの数式は =XLOOKUP(SEQUENCE(5),$D$2:$D$11,C2:C11,"") となります。

作業列を使う(2)

VLOOKUP関数で取り出す方法

  1. データの左端列、A列に作業列を設けます。
    A2セルには =IF(B2=$G$2,COUNTIF($B$2:B2,$G$2),"") と入力して、下方向へA11セルまで数式をコピーします。
  2. G5セルには =IFERROR(VLOOKUP(ROW(A1),$A$2:$D$11,COLUMN(B1),FALSE),"") と入力します。
    この数式を横方向へ I5セルまでコピーし、さらに、G5:I5セルを選択して、下方向へデータが抽出されると想定される行までコピーします。
    VLOOKUP関数では検索値が見つからないとエラーになるので、IFERROR関数でエラー処理をしています。
    VLOOKUP関数の引数の列番号は数式をコピーするのを前提にCOLUMN関数を使って 連番(2,3,4)を生成するように小細工をしています。

スポンサードリンク



よねさんのWordとExcelの小部屋 »  エクセル関数の技 »  関数で複数条件で検索して複数データを取り出す

 PageViewCounter
 Counter
 Since2006/2/27