Excelで複数の条件に基づいてテーブルからデータを抽出する方法

必要なタイミングでデータを取り出せることは、スプレッドシートを活用するうえで最も重要な要素です。Excelも例外ではありません。私たちはデータを表形式でExcelに保存し、必要に応じてそれらを抽出します。本記事では、複数の条件（criteria）に基づいてテーブルからデータを抽出する方法を詳しく解説します。

まずはじめに、例のベースとなるデータセットを確認しておきましょう。

ここには、ジャンル・主演俳優・公開年とともに映画のリストがまとめられたテーブルがあります。このデータセットを使用して、複数の条件に基づいたデータ抽出を行います。

なお、説明をシンプルにするために基本的なテーブルを使用していますが、実務ではより大規模で複雑なデータセットを扱うことも多いでしょう。その場合でも、以下で紹介する手法はそのまま応用できます。

練習用ワークブック

以下のリンクから練習用ワークブックをダウンロードして、実際に手を動かしながら学ぶことができます。

複数の条件に基づいてテーブルからデータを抽出する

ここでは例として、「ジャンル」と「俳優名」を条件として指定し、その条件に一致する「映画名」を抽出します。

1. 単一の値を返す方法

このセクションでは、条件に一致する単一の値を返す方法を紹介します。条件を満たす結果が1つだけ取得されるパターンです。順番に見ていきましょう。

I. INDEX-MATCH 配列数式

INDEX関数とMATCH関数を組み合わせて使うことができます。INDEXは指定した範囲内の特定の位置にある値を返し、MATCHは範囲内で検索値がどの位置にあるかを見つけます。

これらの関数の詳細については、INDEXおよびMATCHの解説記事をご覧ください。

まず条件値を設定しましょう。ここでは、ジャンルに「スリラー（Thriller）」、俳優フィールドに「ヒュー・ジャックマン」を指定します。

使用する数式は次のとおりです。

=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0))

B4:B19は値を抽出する元となる配列です。そしてMATCH関数が、取得すべき行番号を決定します。

ご覧のとおり、MATCHのlookup_valueには「1」を指定しています。また、lookup_arrayは各条件の一致判定を掛け合わせることで生成されます。

$H$4=$C$4:$C$19でジャンルの一致を確認し、$H$5=$D$4:$D$19で俳優名の一致を確認しています。

掛け算の結果の配列の中から「1」が見つかると、その行番号が返され、INDEX関数が対応する映画名を返します。

これは配列数式なので、実行するにはCtrl + Shift + Enterを押す必要があります。

条件値を変更すると、結果も自動的に更新されることを確認できます。

関連記事： Excelでセルから特定のデータを抽出する方法（3つの例）

II. INDEX-MATCH 非配列数式

INDEXとMATCHを組み合わせた非配列数式を作ることもできます。

まずは数式を見てみましょう。

=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0))

ここでは、2つのINDEX関数を使用しています。外側のINDEX関数が値の抽出を担当し、内側のINDEX関数が行番号の検出をサポートします。

内側のINDEXの中で条件値のチェックを行っています。ここでは2つの論理演算がINDEX内で掛け合わされ、配列参照として機能しています。

この数式は、通常のEnterキーを押すだけで実行できます。

自由に条件値を変更してみてください。結果も更新されます。

関連記事： Excelで条件に基づいてデータを抽出する方法（5つの方法）

III. INDEX-MATCH-IF の組み合わせ

前のセクションでは、条件をチェックして掛け合わせることで、複数条件を同時に扱いました。IF関数を使えば、掛け算を使わずに同じことが実現できます。

IFは論理テストを実行し、結果としてブール値（TRUEまたはFALSE）を返します。関数の詳細についてはIF関数の解説記事をご覧ください。

使用する数式は次のとおりです。

=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))

ここでは一致させる条件が2つあるため、IFを2つ使用しています。これらはネストされたIF（入れ子構造）として動作します。外側のIFが最初の条件を確認し、2つ目の条件（内側のIF）が最初のIFのif_true_valueとして機能します。

この数式を実行するには、Ctrl + Shift + Enterを押してください。

条件値を変更すると、結果も更新されることがわかります。

関連記事： Excelシートからデータを抽出する方法（6つの効果的な方法）

IV. LOOKUP 関数

LOOKUP関数を使っても、条件に基づくデータ抽出というタスクを実行できます。

LOOKUP関数は範囲内で一致する検索を行い、対応する値を返します。詳細についてはMicrosoft サポートサイトをご覧ください。

それでは、数式を見てみましょう。

=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))

ここではlookup_valueとして「2」を指定しています。また、1を各条件で割った形の2つの論理演算がlookup_vectorとして機能します。

具体的には、TRUE/FALSEの配列（$C$4:$C$19=$H$4）で1を割り、さらに別のTRUE/FALSEの配列（$D$4:$D$19=$H$5)でも割ります。その結果は「1」または#DIV/0!エラーのいずれかになります。

lookup_valueは、範囲内の数値との一致を指示するものであり、一致が見つかった時点で、配列B4:B19から対応する値が導き出されます。

この方法では、実行時にCtrl + Shift + Enterを押す必要はありません。

他の値に対しても数式が正しく機能するかどうか、条件値を変更して確認してみてください。

なお、lookup_valueとして「2」を使用しましたが、これは1以上の任意の数値で問題ありません。

関連記事： 画像からExcelへデータを抽出する方法（クイックステップ付き）

あわせて読みたい

• テキストファイルをExcelに変換するVBAコード（7つの方法）
• Excel VBA：ウェブサイトから自動的にデータを取得する（2つの方法）
• セキュアなウェブサイトからExcelへデータをインポートする方法（クイックステップ付き）
• VLOOKUPで別のExcelワークシートへ自動的にデータを転送する
• パイプ区切りでExcelをテキストファイルに変換する方法（2つの方法）

2. 複数の値を返す方法

I. INDEX-SMALL の組み合わせ

条件に基づいて複数のデータを抽出するには、さまざまな関数の組み合わせが利用できます。そのひとつがINDEX–SMALLの組み合わせです。

SMALL関数は、値のランク付けされたリスト内での位置に基づいて値を返します。詳細についてはSMALL関数の解説記事をご覧ください。

この2つに加えて、いくつかのヘルパー関数であるIF、ROW、IFERRORが必要になります。詳細はそれぞれの解説記事をご確認ください。

使用する数式は次のとおりです。

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

ここでは各関数がそれぞれ役割を担っています。INDEX関数は配列B2:B17から値を返し、大きなSMALLの部分が取得すべき行番号を提供します。

SMALL内のIFは、条件が一致しているかどうかを判定します。条件が2つあるため、両方の論理演算を掛け合わせて判定しています。そしてROW関数が列のセルを反復処理します。

外側のROWは、SMALL関数のk番目の値を示します。これらの関数が連携して行番号を返し、INDEXが最終的な結果を返します。

IFERRORは、数式から発生しうるエラーを処理するためのものです。エラーの場合は空のセルを表示するように設定しています。

この数式を下方向にドラッグすると、条件に一致するすべての値が取得できます。

関連記事： Excelの数式を使ってリストからデータを抽出する方法（5つの方法）

II. INDEX-AGGREGATE の組み合わせ

ExcelのAGGREGATE関数を使うと、さまざまなタスクを実行できます。1つの関数で複数の操作が可能です。この関数を利用して、複数の条件に基づいて複数の値を返すことができます。

まず関数について簡単に説明すると、AGGREGATE関数はAVERAGE、COUNT、MAXなどの集計計算を返します。

AGGREGATE関数の構文は次のとおりです。

AGGREGATE(function_number,behavior_options, range)

• function_number： どの計算を行うかを指定する番号です。
• behavior_options： 数値で設定します。関数の動作方法を指定します。
• range： 集計したい範囲です。

AGGREGATE関数は複数のタスクを実行できるため、内部にあらかじめ定義された関数番号があります。よく使われる関数番号をいくつか紹介します。

関数	関数番号
AVERAGE	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
SUM	9
LARGE	14
SMALL	15

関数について詳しく知りたい場合は、Microsoft サポートサイトをご覧ください。

それでは数式を見てみましょう。INDEXとAGGREGATEの組み合わせになります。

=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

ここではAGGREGATEのfunction_numberとして15を指定しています。上記の表からわかるように、15はSMALL関数の操作を呼び出します。ご覧のとおり、AGGREGATE（および関数番号と動作オプション番号）を使用している点を除けば、数式は先ほどのINDEX–SMALLの数式とまったく同じです。

仕組みも同じで、INDEXが配列を保持し、数式のAGGREGATE部分で見つかった一致に基づいて値を返します。

動作オプションには6を指定しています。これはエラー値を無視することを意味します。

この数式を下方向にドラッグすると、条件に一致するすべての値が取得できます。

数式の実行にはCtrl + Shift + Enterを使うことを忘れないでください。

関連記事： Excelで単一の条件に基づいて複数の値を返す方法（3つの選択肢）

III. INDEX-MATCH-COUNTIF の組み合わせ

複数の条件に基づいて複数の値を返すには、INDEX、MATCH、COUNTIFの組み合わせも利用できます。

COUNTIFは、1つの条件を満たす範囲内のセルをカウントします。この関数について詳しくはCOUNTIFの解説記事をご覧ください。

使用する数式は次のとおりです。

=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")

MATCH関数内では、lookup_arrayとして0を指定し、lookup_rangeにはCOUNTIFを含むIF部分を使用しています。

ここでCOUNTIF関数は、すでに取得済みの値を除外します。そして2つのIF関数が2つの条件をチェックします。これらの関数を組み合わせて、lookup_rangeを構成しています。

MATCHの部分は、0が見つかり続ける限り値を返します。この値がINDEXの行番号として機能します。

この数式を下方向にドラッグすると、条件に一致するすべての値が取得できます。

関連記事： Excelでセルからデータを抽出する方法（5つの方法）

IV. FILTER 関数

Excel 365をお使いの場合は、FILTERという組み込み関数ひとつでこのタスクを実行できます。

FILTER関数は、指定した条件に基づいてデータの範囲をフィルタリングし、一致するレコードを抽出します。関数について詳しくはFILTERの解説記事をご覧ください。

それでは数式を見てみましょう。

=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))

B4:B19はフィルタリング対象の配列です。次に、値を抽出する基準となる条件を指定します。2つの条件をチェックする必要があるため、両方を掛け合わせています。

この方法では数式を下にドラッグする必要がなく、一度の入力ですべての値が表示され、リストが完成します。

関連記事： Excelでフィルター済みのデータを別シートに抽出する方法（4つの方法）

まとめ

今回は以上です。複数の条件に基づいてテーブルからデータを抽出するいくつかの方法を紹介しました。この記事が皆さんのお役に立てば幸いです。理解しづらい点があれば、お気軽にコメントしてください。また、ここで紹介しきれていない他のアプローチがあれば、ぜひ教えてください。

さらに学びたい方へ

• ExcelからWordへデータを抽出する方法（4つの方法）
• Excel VBAで別シートからデータを抽出する方法（3つの方法）
• Excel VBAで複数のワークシートからデータを取得する方法
• あるExcelワークシートから別のワークシートへ自動的にデータを転送する
• Excelで条件に基づいて別シートからデータを取得する方法
• Excelマクロ：複数のExcelファイルからデータを抽出する（4つの方法）
• Excelで日付から年を抽出する方法（3つの方法）