条件に合うデータを関数ですべて抽出する方法｜複数条件も
ここでは、「条件に合うデータを関数ですべて抽出する方法」を、複数のページにわたって解説します。
このページでは、基本編として「数式の形」や「引数の指定の仕方」、「複数条件にする方法」、「別シート/別ファイルを参照する方法」を説明します。
１．どんなことができる数式か？
まずは、どんなことができる数式か？から、例も含めて確認してみます。
１－１．条件に合うものをすべて抽出できる
これから説明する数式の組み合わせでできるのは、「一覧表から条件に合うデータを、上から順にすべて抜き出す」ことです。
特にポイントは「上から順にすべて」で、一覧表に条件と一致するデータ（行）が３個あったらそれらを上から順に３個、１００個あったら１００個抜き出せます。
１－２．例を確認
では、一つ例を見てみましょう。
上の表は、出張の実績を一覧にしたものです。
この表から「部署名が『総務部』」のデータ（行）のみを抜き出してみます。
結果、上のように３つのデータが抽出できました。
このように、条件に該当するデータを複数抽出できるようになります。
１－３．数式のメリット｜FILTER関数の代用など
この数式のメリットは、次の通りです。
- 条件に合うデータを「全て」抽出できる
- FILTER関数が使えないバージョンでも使える
- 条件の指定の幅が広い（複数条件/含む/以上・以下/OR）
- 作業列が必要ない
- 抽出元の表は変わらず残る
まず、「条件に合うデータすべて」を抽出できます。VLOOKUP関数などでは、基本的に1つだけです。
また、FILTER関数のほうが正直便利ですが、使えないバージョンでもこの数式は使えるので代用になります。
そして、「条件指定の幅が広い」のも特徴で、「複数条件」や「含む条件」、「以上・以下の範囲条件」、「どれかに当てはまれば～」の「OR条件」なども指定可能です。
なお、関数での抽出なので、フィルター機能と違って「元の表は変わらず残る」のも特徴です。
２．FILTER関数が使えない時の代わりとして
数式は、「FILTER関数の代用」としても使えます。
２－１．FILTER関数は便利
FILTER関数とは、「条件に合うデータのみに絞り込むことができる」関数です。
この関数はここで紹介する数式よりも簡単で、とても使い勝手がいいです。
２－２．使えるバージョンと困る例
ただFILTER関数には、一つ弱点があります。それは「Excel2021かMicroSoft365以外では使えない」ことです。
FILTER関数で困る例は、次の通りです。
- Excelのバージョンが古くてFILTER関数が使えない
- 別のパソコンで開いたらエラーになった
特にあるのが、「別のパソコンで開いたらエラーになった」です。
開くパソコンのエクセルが2016や2019の場合には、FILTER関数が使えないのでエラーになるのです。
対して、ここで説明する数式はこのような事態を防ぎ、FILTER関数の代用として使うことができます。
ちなみに、FILTER関数の使い方を知りたい場合には、次のページで解説しています。
３．記事全体の構成と各ページの概要
次に、記事全体の構成とページごとの概要です。
全部で14ページありますが、全て見る必要はありません。重要なのは「このページ」と「数式の使い方・作り方を解説した２ページ目」です。
なお、このページでは「数式の基本的な作り方」が分かりますが、細かいポイントは2ページ目の方が分かりやすくなっています。
３－１．基本知識
数式の形と引数の指定の仕方を、例をあげて確認します
数式の使い方や作り方を、条件一つの形で紹介します
数式の意味を、2ページ目で紹介した形をもとに説明します
３－２．様々な条件指定方法
「複数条件」、「条件値以外」、「条件値のセル参照」の方法を説明します
条件を「空白」、「空白以外」とする方法を説明します
条件を「以上」、「以下」等とする方法を説明します
条件値を「含む」、「含まない」とする方法を説明します
「OR条件」とする方法を説明します
「OR条件をリストでまとめて指定」する方法を説明します
「OR条件以外」とする方法を説明します
３－３．エラー等発生時の対応策
数式のQ&Aです
数式をコピーした時にうまくいかない、エクセルの動作が重くなったなどの対処法を解説しています
空白が０となってしまう場合の対処法を説明します
４．【条件が一つ】数式の形と引数の入れ方
では、条件が一つの場合の数式の形と引数の入れ方を確認します。
なお、もし途中で説明の意味が分からなくなった場合には、次のページの「数式の作り方」も参考にしてください。
４－１．数式の形は
条件が一つの場合、数式の形は次の通りです。
必要なのは、「抽出対象の列（＝抽出したい値が入った1列すべて）」、「条件式」、「条件範囲（＝条件の検索対象の範囲）」の３つです。
４－２．各引数の入れ方
次に、各引数の入れ方を見ていきます。
４－２－１．抽出対象の列
「抽出対象の列」には、「抽出したい値の入った列」を1列指定します。
例えば、A列の値を抜き出したい場合には「A:A」、D列の場合には「D:D」となります。
４－２－２．条件式
「条件式」は、次の順番で書きます。
指定の仕方はそれぞれ次の通りです。
- 条件範囲：条件値と比較する範囲（１列を絶対参照で）
- 比較演算子：「=」「<>」「<」「<=」「>」「>=」どれか
- 条件値：条件にしたい値
例えば、条件式を「B5からB10で『50以上』」としたい場合、それぞれ次のようになります。
- 条件範囲：$B$5:$B$10
- 比較演算子：>=
- 条件値：50
つなげると、
$B$5:$B$10>=50
で、これが条件式です。
そして特に条件値は、「文字列」や「数値」などの「データの型」によって書き方が違うため注意が必要です。
（１）条件値が「文字列」、「日付」、「時刻」の場合
条件値を文字列にする場合には値を「"」で囲みます。そして日付の場合にはDATEVALUE関数も、時刻にする場合にはTIMEVALUE関数も使います。
数式の例は、それぞれ次の通りです。
- 文字列：A1="テスト"
- 日付：A1=DATEVALUE("4/1")
- 時刻：A1=TIMEVALUE("9:00")
（２）条件値が「セル番号」、「数値」の場合
条件値を数値やセル番号にする場合には、そのまま入力すればOKです。
数式の例は、それぞれ次の通りです。
- セル番号：A1=B1
- 数値：A1=50
ちなみに、セル番号の場合には「絶対参照」にします。
４－２－３．条件範囲
条件式で指定した「条件範囲」を、ここにも同じく絶対参照で指定します。
しかし本当は、列番号は条件範囲と違っても問題ありません。
ただ、全く同じものを入れると覚えたほうが分かりやすいと思います。
４－２．「数式の使い方・作り方のポイント」は次のページで解説
数式は、FILTER関数と違って「一つのセルに数式を入れる」だけでは完成しません。
「フィルター機能」や「FILTER関数」のように使いたい時には、この他手順があり、長くなるため次のページで解説しています。
５．【複数条件】数式の形と引数の入れ方
次に、複数条件にしたい場合の数式の形と引数の入れ方です。
５－１．数式の形は
複数条件の場合、数式の形は次の通りです。
※ 条件が二つの場合
下の例は、先ほどの表から「部署名が『総務部』で役職が『部長』」の条件と一致するデータを抜き出したものです。
必要な引数の種類は条件が一つの場合と変わりません。違うのは、「カッコで囲んだ条件式同士を『*』で掛け合わせる」ことです。
この点を押さえると、条件を３つにも４つにもできます。
なお、条件同士の関係は「AND」です。「条件のどれにも当てはまる」時に一致と判定されます。
５－２．各引数の入れ方
各引数の入れ方は、「条件が一つの場合」と同じです。
条件の数が増えるだけで、各引数の入れ方は変わりません。
６．別シート／別ファイルから抽出するには
続いて、条件に合うデータ全てを「別シート」や「別ファイル」から抜き出すときのポイントを見てみます。
６－１．別シートから抽出する場合
データを別シートから抽出する場合、工夫の必要な引数は「抽出対象の列」と「条件範囲」です。
この２つに、「『抽出元の表』があるシート」の列や範囲を指定します。
別シートの列や範囲を指定するには、「『対象のシート』をクリック」→「『列』または『範囲』を選択」の順に操作します。
すると、引数の前に「シート名!」、または「'シート名'!」が自動で付き、別シート参照が簡単にできます。
６－２．別ファイルから抽出する場合
データを別ファイルから抽出する場合も、工夫の必要な引数は「抽出対象の列」と「条件範囲」です。
別ファイルの列や範囲を指定するには、「『対象のファイル』をクリック」→「『対象のシート』をクリック」→「『列』または『範囲』を選択」の順に操作します。
すると、引数の前に「保存場所\[ブック名]シート名!」、または「'保存場所\[ブック名]シート名'!」が自動で付き、別シート参照が簡単にできます。
７．「数式の作り方・使い方」は次頁で解説
次のページでは、数式の作り方・使い方を解説します。
７－１．FILTER関数のように使うためには「コツ」がある
紹介した数式は、FILTER関数のように使うための「コツ」があります。
反対に言うと、FILTER関数のように「一つのセルに入れただけで表形式で抜き出せるわけではない」のです。
そのコツに触れながら、数式作りの手順を一つ一つ確認します。
７－２．次のページへのリンク
【口コミ・評判のいいExcel本の紹介】
なお、一連の記事の好きなページに行けるリンクはこちらです。
- 条件に合うデータ抽出 各ページへのリンクはこちら
- ◎具体例の確認と数式の概要（1/12頁）
- ◎数式の作り方（2/12頁）
- ◎数式構成の解説（3/12頁）
- ◎条件値の基本的な指定方法（4/12頁）
- ◎空白、空白以外の条件指定方法（5/12頁）
- ◎以上、以下等の指定方法（6/12頁）
- ◎含む条件、含まない条件の指定方法（7/12頁）
- ◎OR（または）条件の指定方法（8/12頁）
- ◎OR条件値を範囲指定する方法（9/12頁）
- ◎OR条件以外と指定する方法（10/12頁）
- ◎数式のQ&A（11/12頁）
- ◎空白が０になる対処法（12/12頁）