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

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

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

更新:2025/3/30;作成2009/9/14

サンプルデータ

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

  	B	C	D
  2	日付	商品	販売数
  3	2025/5/1	みかん	100
  4	2025/5/2	りんご	120
  5	2025/5/3	バナナ	110
  6	2025/5/4	桃	50
  7	2025/5/1	バナナ	140
  8	2025/5/2	桃	110
  9	2025/5/3	みかん	105
  10	2025/5/4	りんご	150
  11	2025/5/1	バナナ	120
  12	2025/5/2	桃	140

Filter関数を使う

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

AND条件の場合です

• 日付が 2025/5/1 で商品が バナナ という複数の条件でデータを抽出します。
  And条件なので、論理積(*)で論理式を作成します。
  F3セルに　=FILTER(B3:D12,(B3:B12=DATE(2025,5,1))*(C3:C12="バナナ"))と入力します。
  

OR条件の場合です

• 日付が 5月1日 と 5月3日のデータを抽出します。
  Or条件なので、論理和(+)で論理式を作成します。
  F3セルに　=FILTER(B3:D12,(B3:B12=DATE(2025,5,1))+(B3:B12=DATE(2025,5,3))) と入力します。
  

配列数式を使う

• 作業列を使いたくないというケースがあります。そのようなときは配列数式を利用することができます。
• 【考え方】条件と一致する行は何行目かを計算します。計算された行数の小さい方から順番にデータを取り出します。
• Excel for Microsoft365,Excel2021以降のバージョンであれば、動的配列数式が利用できるので
  G5セルに
  =IF(COUNTIF($B$3:$B$12,$G$2)<ROW(A1),"",
  INDEX(B$3:B$12,SMALL(IF($B$3:$B$12=$G$2,ROW($A$1:$A$10)),ROW(A1))))
  と入力して、I5セルまでコピーし、更にG5:I5セルを下方向へ必要なだけコピーします。
• Excel2019以前のバージョンの場合は
  G5セルに
  =IF(COUNTIF($B$3:$B$12,$G$2)<ROW(A1),"",
  INDEX(B$3:B$12,SMALL(IF($B$3:$B$12=$G$2,ROW($A$1:$A$10)),ROW(A1))))　と入力して
  [Shift]+[Ctrl]+[Enter]で入力を確定します。(配列数式にします)
  G5セルをI5セルまでコピーし、更にG5:I5セルを下方向へ必要なだけコピーします。
  
  

数式の解説

• INDEX(B$3:B$12,SMALL(IF($B$3:$B$12=$G$2,ROW($A$1:$A$10)),ROW(A1))))　部分の説明
• IF($B$3:$B$12=$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)から行位置を得られるようになっています。
  【注意】なお、データのセル範囲の行数$B$3:$B$12は10ですので、ROW($A$1:$A$10)と1〜10になるようにします。
  たとえば、データ範囲がA3:A10と8行のときは、ROW($A$1:$A$8)のように一致させます。
  SMALL(・・・・・・・,ROW(A1))で、小さな値(行位置)から順番に取り出すようにしています。
• 最初のIF関数、=IF(COUNTIF($B$3:$B$12,$G$2)<ROW(A1),"",　の部分は、一致するデータ数を超える行は空白になるようにしています。

作業列を使う方法

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

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

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

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

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

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

1. 作業列には、上記と同様に、
   E3セルに　=IF(B3=$G$2,COUNTIF($B$3:B3,$G$2),"")　と入力して、下方向へE12セルまでコピーします。
   
2. G5セルには　=XLOOKUP(SEQUENCE(5),$E$3:$E$12,B3:B12,"")　と入力します。
   SEQUENCE(5)は連続した数値の配列（1,2,3,4,5)を作成しています。該当するデータが5個程度を想定しています。多くのデータが該当すると思われる場合はこの値を調整してください。
   
3. G5セルの数式を H5セル、I5セルにコピーします。
   H5セルの数式は　=XLOOKUP(SEQUENCE(5),$E$3:$E$12,C3:C12,"")　となります。
   
4. I5セルの数式は　=XLOOKUP(SEQUENCE(5),$E$3:$E$12,D3:D12,"")　となります。
   

作業列を使う(2)

VLOOKUP関数で取り出す方法

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

1. データの左端列、B列に作業列を設けます。
   B3セルには　=IF(C3=$G$2,COUNTIF($C$3:C3,$G$2),"")　と入力して、下方向へB12セルまで数式をコピーします。
   
2. G5セルには　=IFERROR(VLOOKUP(ROW(A1),$B$3:$E$12,COLUMN(B1),FALSE),"")　と入力します。
   この数式を横方向へ I5セルまでコピーし、さらに、G5:I5セルを選択して、下方向へデータが抽出されると想定される行までコピーします。
   VLOOKUP関数では検索値が見つからないとエラーになるので、IFERROR関数でエラー処理をしています。
   VLOOKUP関数の引数の列番号は数式をコピーするのを前提にCOLUMN関数を使って 連番(2,3,4)を生成するように小細工をしています。
   • IFERROR関数はExcel2007で追加された関数です。
     詳細な使い方は　IFERROR関数の使い方(数式がエラーの時に指定の値を返す):Excel関数　をご覧ください。
   

複数条件の場合です

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

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

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27