1. Home » 
2. エクセル関数の技 » 
3. 関数で複数条件で検索して複数データを取り出す

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

• VLOOKUP関数で検索条件と一致したデータを取り出すことができますが、一致するデータが複数あるときには先頭だけしか取り出せません。
  オートフィルタ(データ抽出)　フィルタオプションの設定(データ抽出)などの機能を使えばよいのですが操作が分からない人も使うので、関数で取り出したいというケースがあります。
  そのようなケースではどのようにしたら良いか・・・ということについて書いてみます。

更新:2022/2/24;作成2009/9/14

サンプルデータ

• ある条件と合致するデータを(複数)取り出したい・・・という時の考え方の例です。
• 下表のようなデータで、ある日付のデータをすべて抽出する方法を考えてみます。
  
  
  コピーして練習に使えるように表を追記しておきます。
  

  日付	商品	販売数
  5月1日	みかん	100
  5月2日	りんご	120
  5月3日	バナナ	110
  5月4日	桃	50
  5月1日	バナナ	140
  5月2日	桃	110
  5月3日	みかん	105
  5月4日	りんご	150
  5月1日	バナナ	120
  5月2日	桃	140

Filter関数を使う

• Excel for Microsoft 365,Excel2021では　Filter関数を使うことができます。
  Filter関数の使い方は　FILTER関数でデータを抽出する:Excel関数　をご覧ください。
• FILTER関数の構文　=FILTER(配列,含む,[空の場合])
• 「5月4日」のデータを抽出してみます。
  F5セルに =FILTER(A2:C11,A2:A11=G2) と入力しています。
  次項のように、作業列や配列数式を使う必要がなくなり、関数を使って簡単にデータを抽出できるようになりました。
  なお、F5セル以降のセルは日付の表示形式にします。
  
• 商品が「バナナ」のデータを抽出し、さらに、日付の昇順に並べ替えてみます。
  F5セルに　=SORT(FILTER(A2:C11,B2:B11=G2))　と入力するだけです。
  • SORT関数の使い方は　SORT関数でデータを並べ替える:Excel関数　をご覧ください。
  

複数条件の場合です

• 日付が 5月1日 で商品が バナナ という複数の条件でデータを抽出します。
  And条件なので、論理積(*)で論理式を作成します。
  F5セルに　=FILTER(A2:C11,(A2:A11=G2)*(B2:B11=H2)) と入力します。
  
• 日付が 5月1日 と 5月3日のデータを抽出します。
  Or条件なので、論理和(+)で論理式を作成します。
  F5セルに　=FILTER(A2:C11,(A2:A11=F2)+(A2:A11=F3))　と入力します。
  

配列数式を使う

• 作業列を使いたくないというケースがあります。そのようなときは配列数式を利用することができます。
• 【考え方】条件と一致する行は何行目かを計算します。計算された行数の小さい方から順番にデータを取り出します。
• G5セルに
  =IF(COUNTIF($A$2:$A$11,$G$2)<ROW(A1),"",
  INDEX(A$2:A$11,SMALL(IF($A$2:$A$11=$G$2,ROW($A$1:$A$10)),ROW(A1))))　と入力して
  [Shift]+[Ctrl]+[Enter]で入力を確定します。(配列数式にします)
  G5セルをI5セルまでコピーし、更にG5:I5セルを下方向へ必要なだけコピーします。
  G2セルの検索値を5月2日に変えた結果の例です。
  

数式の解説

• INDEX(A$2:A$11,SMALL(IF($A$2:$A$11=$G$2,ROW($A$1:$A$10)),ROW(A1)))) 部分の説明
• IF($A$2:$A$11=$G$2,ROW($A$1:$A$10))の部分でG2セルの値と完全一致するセルの行位置を調べています。
  この部分は配列で処理しています。
  {FALSE;2;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;10}となっています。
  G2セルと同じでないときはFALSE、同じ時はROW($A$1:$A$10)から行位置を得られるようになっています。
  【注意】なお、データのセル範囲の行数$A$2:$A$11は10ですので、ROW($A$1:$A$10)と1〜10になるようにします。
  たとえば、セル範囲がA3:A10と8行のときは、ROW($A$1:$A$8)のように一致させます。
  SMALL(・・・・・・・,ROW(A1))で、小さな値(行位置)から順番に取り出すようにしています。
• 最初のIF関数、=IF(COUNTIF($A$2:$A$11,$G$2)<ROW(A1),""　の部分は、一致するデータ数を超える行は空白になるようにしています。

作業列を使う方法

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

• 【考え方】条件と一致しているか否かを調べます(作業列を利用します)。一致しているものについては連番を付けてやります。
  この連番順にデータを取り出せばよさそう・・・と考えています。
• 検索する値はG2セルに入力するものとします。D列を作業列として使用します。
  

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)))　と入力します。
   • INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0))
     表示する対象A$2:A$11は検索するセル範囲の1列目、2列目と右方向へコピーするごとに変化しますので、複合参照にします。(行番号だけを絶対参照にします)
     作業列の1,2・・・を探すのはMATCH(ROW(A1),$D$2:$D$11,0)の部分です。
     ROW(A1)で「1」と完全一致する行を$D$2:$D$11の範囲で捜しています。
     ROW(A1)を使うのは下方向へコピーして使うとき、1,2,3 と連番を発生させるためです。
     D列の連番よりROW(A1)が大きくなると、一致するデータは無いので、エラーとなります。
     エラーを表示しないように　=IF(MAX($D$2:$D$11)<ROW(A1),"",　として空白にします。
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関数で取り出す方法

• Excel for Microsoft 365やExcel2021では XLOOKUP関数やSEQUENCE関数を使うことができます。
  XLOOKUP関数の使い方は　XLOOKUP関数で範囲や配列を検索する:Excel関数　をご覧ください。
  SEQUENCE関数の使い方は　SEQUENCE関数で連続した数値の配列を作成する:Excel関数　をご覧ください。
• 数式がシンプルなものになります。しかし、Filter関数が使えるので・・・あくまでも使い方の一例ということです。

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関数で取り出す方法

• 作業列をデータリストの左端列に設けることができると、なじみの多いVLOOKUP関数で取り出すことができます。
  Filter関数を使うことができない場合は、左端列に作業列を作れるのであれば非常に簡単な数式となります。

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)を生成するように小細工をしています。
   • IFERROR関数はExcel2007で追加された関数です。
     詳細な使い方は　IFERROR関数の使い方(数式がエラーの時に指定の値を返す):Excel関数　をご覧ください。
   

複数条件の場合です

• 日付が 5月1日 で商品が バナナ という複数の条件でデータを抽出します。
  And条件なので、COUNTIFS関数を利用します。
  A1セルに　=IF(AND(B2=$G$2,C2=$H$2),COUNTIFS($B$2:B2,$G$2,$C$2:C2,$H$2),"")　と入力して、下方向へ数式をコピーします。
  G5セルには　=IFERROR(VLOOKUP(ROW(A1),$A$2:$D$11,COLUMN(B1),FALSE),"")　と入力します。
  I5セルまで数式をコピーし、更に下方向へ数式をコピーします。
  

• 日付が 5月1日 と 5月3日のデータを抽出します。
  OR条件なので、COUNTIF関数でそれぞれの個数をカウントして足し算します。
  作業列のA2セルには
  　=IF(OR(B2=$G$2,B2=$G$3),COUNTIF($B$2:B2,$G$2)+COUNTIF($B$2:B2,$G$3),"")
  と入力して、数式を下方向へコピーします。
  G5セルには　=IFERROR(VLOOKUP(ROW(A1),$A$2:$D$11,COLUMN(B1),FALSE),"")　と入力します。
  I5セルまで数式をコピーし、更に下方向へ数式をコピーします。
  

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27