2022年9月13日Excelで複数条件に合うデータを抽出するには？

Excelでデータをまとめたり、集計したりするとき、1つの条件だけでなく、複数の条件に合うデータを抽出したいと思うことがあるでしょう。そこで、本記事では複数条件に合うデータを抽出する方法3つと、複数条件に合うデータを抽出するために便利な関数を紹介します。

フィルターオプション、FILTER関数などを使えば、複数条件を簡単に抽出できます。それぞれの手順を説明します。

フィルターオプションを使う

そもそもExcelの標準機能として備わっている「フィルター」を使えば、複数条件に一致したデータを簡単に抽出できます。

• 1.抽出したいデータ範囲の左上のセルを選択し、「データ」タブから「フィルター」の「詳細設定」をクリック
• 2.「フィルター オプションの設定」のダイアログボックスが表示されるため、「指定した範囲」を選択
• 3.「リスト範囲」を選択し、抽出したいデータの範囲を入力
• 4.「検索条件範囲」を選択し、条件が入力されたデータ範囲を入力
• 5.「抽出範囲」にデータを表示したい空白セルを選び、「OK」をクリック

FILTER関数を使う

FILTER関数は、フィルターオプションと同じように使える関数ですが、Microsoft 365 でしか使えないため、注意しましょう。データを表示したい空白セルを選び、「=FILTER(A5:D14,(C5:C14="男性")*(D5:D14<=30))」のように条件を入力します。それぞれの部分は、以下のような意味を表しますので、必要に応じて変更しましょう。

• ●「A5:D14」の部分…データを抽出したい範囲
• ●「C5:C14」の部分…1つ目の条件
• ●「D5:D14」の部分…2つ目の条件

なお、「*」は、両方の条件を満たすデータのみを抽出するときに使います。いずれかの条件を満たすデータのみを抽出したい場合は、「+」を入力します。

INDEX関数とMATCH関数を組み合わせ、参照するデータと照合する内容を設定して抽出する方法があります。ただし、この方法では条件に合うただ1つのデータしか抽出することができないため、注意しましょう。具体的には、データを表示したい空白セルに以下のような条件を入力します。

例「=INDEX(A2:C11,MATCH(F1&H1,A2:A11&B2:B11,0),3)」

それぞれの部分は、以下のような意味で使われています。自分が使用する範囲に応じて変更しましょう。

• ●「A2:C11」の部分…データを抽出したい全範囲
• ●「F1&H1」の部分…設定した条件のセル
• ●「A2:A11&B2:B11」の部分…データを抽出するために参照する範囲
• ●「0」の部分…完全一致させたい場合、「0」を入力する。「1」なら条件未満の最大の値、「-1」なら条件より大きい最小の値となる。
• ●「3」の部分…抽出したいデータの値がある列(左から順に数える)

DGET関数を使って、複数条件に一致した1つのセルを抽出する方法もあります。具体的には、空白セルに以下のような条件を入力します。

例「=DGET(C6:C12,E6,C15:D16)」

• ●「C6:C12」の部分…データを抽出するために参照する範囲
• ●「E6」の部分…データを抽出したい項目が入力されているセル
• ●「C15:D16」の部分…検索条件として設定したセル

データ抽出のために参照する範囲や、検索条件として設定したセルは、「F4」キーを使って絶対参照にしておくと、関数をドラッグしたりコピー・貼り付けしたりするときにも便利です。

その他、複数条件で抽出するための関数にはVLOOKUP関数やXLOOKUP関数があります。なお、COUNTIFS関数では複数条件に合致するデータの抽出はできますが、合致するデータの個数しかわからないため、データ自体が抽出できるわけではありません。抽出したい内容に合わせて使う関数を選びましょう。

VLOOKUP関数を使う方法

VLOOKUP関数は本来、1つの条件だけを設定する関数です。しかし、複数の条件をつなげた条件をあらかじめ作っておけば、複数の条件に合うデータを抽出することもできます。

• 1.空白セルに、データを抽出するために参照する範囲を設定
• 2.行の数だけ、オートフィルでセルの数を増やす
• 3.空白セルに、検索条件として設定したセルをつなぐ

以上の準備ができたら、VLOOKUP関数を設定します。

例「=VLOOKUP(J1,A2:D11,4,FALSE)」

• ●「J1」の部分…手順3. のセル
• ●「A2:D11」の部分…データを抽出したい全範囲
• ●「4」の部分…抽出したいデータの値がある列
• ●「FALSE」…完全一致させたい場合、「FALSE」または「0」を入力する。「TRUE」または「1」なら条件未満の最大の値となる

XLOOKUP関数を使う方法

XLOOKUP関数を使えば、行と列の両方でデータを検索できます。ただし、Excel 2021 から正式に追加された関数なため、それより前のバージョンを利用している場合は使えないので、注意しましょう。
例えば、以下のように設定します。

例「=XLOOKUP(E3&F3,A3:A12&B3:B12,C3:C12,”該当なし”)」

「E3&F3」の部分…設定したい検索条件
「A3:A12&B3:B12」の部分…データを抽出するために参照する範囲
「C3:C12」の部分…抽出したいデータの値がある範囲
「”該当なし”」の部分…検索した値がなかった場合に返したい文言

Excelで複数条件に合ったデータを抽出するには、もともとExcelに搭載されているフィルターオプションを使ったり、INDEX関数とMATCH関数を使ったりする方法、その他便利な関数があります。抽出したいデータや、使いやすい方法に合わせて使いましょう。