- Home »
- エクセル関数の技 »
- 関数で複数条件で検索して複数データを取り出す
- VLOOKUP関数で検索条件と一致したデータを取り出すことができますが、一致するデータが複数あるときには先頭だけしか取り出せません。
オートフィルタ(データ抽出) フィルタオプションの設定(データ抽出)などの機能を使えばよいのですが操作が分からない人も使うので、関数で取り出したいというケースがあります。
そのようなケースではどのようにしたら良いか・・・ということについて書いてみます。
2022/2/24
サンプルデータ
- ある条件と合致するデータを(複数)取り出したい・・・という時の考え方の例です。
- 下表のようなデータで、ある日付のデータをすべて抽出する方法を考えてみます。
コピーして練習に使えるように表を追記しておきます。
| 日付 |
商品 |
販売数 |
| 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 |
- Microsoft 365では Filter関数を使うことができます。
Filter関数の使い方は FILTER関数でデータを抽出する:Excel関数 をご覧ください。
- 「5月4日」のデータを抽出してみます。
F5セルに =FILTER(A2:C11,A2:A11=G2) と入力しています。
次項のように、作業列や配列数式を使う必要がなくなり、関数を使って簡単にデータを抽出できるようになりました。
なお、F5セル以降のセルは日付の表示形式にします。
- 商品が「バナナ」のデータを抽出し、さらに、日付の昇順に並べ替えてみます。
F5セルに =SORT(FILTER(A2:C11,B2:B11=G2)) と入力するだけです。
複数条件の場合です
- 日付が 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),"" の部分は、一致するデータ数を超える行は空白になるようにしています。
- 【考え方】条件と一致しているか否かを調べます(作業列を利用します)。一致しているものについては連番を付けてやります。
この連番順にデータを取り出せばよさそう・・・と考えています。
- 検索する値はG2セルに入力するものとします。D列を作業列として使用します。
- 条件と一致した行に連番を入れます。
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セルが同じでないときは空白にしています。
- 作業列の連番順にデータを取り出します。
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),"", として空白にします。
- G5セルに入力した数式を右方向、および下方向へコピーすると完成です。
- G5セルに=IF(MAX($D$2:$D$11)<ROW(A1),"",INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0))) と入力します。
- G5セルを選択し、フィルハンドルを右方向へ、I5セルまでドラッグして数式をコピーします。
- G5:I5セルを選択して、フィルハンドルを下方向へドラッグして数式をコピーします。
下方向へは必要なだけコピーします。一致するデータが表示しきれるだけの行にあらかじめコピーします。
-
- 作業列には、上記と同様に、D2セルに=IF(A2=$G$2,COUNTIF($A$2:A2,$G$2),"")と入力して、下方向へD11セルまでコピーします。
- G5セルには =XLOOKUP(SEQUENCE(5),$D$2:$D$11,A2:A11,"") と入力します。
SEQUENCE(5)は連続した数値の配列（1,2,3,4,5)を作成しています。該当するデータが5個程度を想定しています。多くのデータが該当すると思われる場合はこの値を調整してください。
- G5セルの数式を H5セル、I5セルにコピーします。
H5セルの数式は =XLOOKUP(SEQUENCE(5),$D$2:$D$11,B2:B11,"") となります。
- I5セルの数式は =XLOOKUP(SEQUENCE(5),$D$2:$D$11,C2:C11,"") となります。
作業列を使う(2)
- 作業列をデータリストの左端列に設けることができると、なじみの多いVLOOKUP関数で取り出すことができます。
Filter関数を使うことができない場合は、左端列に作業列を作れるのであれば非常に簡単な数式となります。
- データの左端列、A列に作業列を設けます。
A2セルには =IF(B2=$G$2,COUNTIF($B$2:B2,$G$2),"") と入力して、下方向へA11セルまで数式をコピーします。
- G5セルには =IFERROR(VLOOKUP(ROW(A1),$A$2:$D$11,COLUMN(B1),FALSE),"") と入力します。
この数式を横方向へ I5セルまでコピーし、さらに、G5:I5セルを選択して、下方向へデータが抽出されると想定される行までコピーします。
VLOOKUP関数では検索値が見つからないとエラーになるので、IFERROR関数でエラー処理をしています。
VLOOKUP関数の引数の列番号は数式をコピーするのを前提にCOLUMN関数を使って 連番(2,3,4)を生成するように小細工をしています。
複数条件の場合です
- 日付が 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セルまで数式をコピーし、更に下方向へ数式をコピーします。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数の技 » 関数で複数条件で検索して複数データを取り出す
PageViewCounter
Since2006/2/27