条件に合うデータを関数ですべて抽出する方法｜複数条件も

　◎トップページ > エクセル関数 問題解決 > 条件に合うデータすべての抽出１

ここでは、「条件に合うデータを関数ですべて抽出する方法」を、複数のページにわたって解説します。

このページでは、基本編として「数式の形」や「引数の指定の仕方」、「複数条件にする方法」、「別シート/別ファイルを参照する方法」を説明します。

• このページの目次

１．どんなことができる数式か？

２．FILTER関数が使えない時の代わりとして

３．記事全体の構成と各ページの概要

４．数式の形と引数の入れ方【条件一つの場合】

５．数式の形と引数の入れ方【複数条件の場合】

６．別シート／別ファイルから抽出するには

７．次のページでは「数式の使い方」を解説

まずは、どんなことができる数式か？から、例も含めて確認してみます。

１－１．条件に合うものをすべて抽出できる

これから説明する数式の組み合わせでできるのは、「一覧表から条件に合うデータを、上から順にすべて抜き出す」ことです。

特にポイントは「上から順にすべて」で、一覧表に条件と一致するデータ（行）が３個あったらそれらを上から順に３個、１００個あったら１００個抜き出せます。

１－２．例を確認

では、一つ例を見てみましょう。

上の表は、出張の実績を一覧にしたものです。

この表から「部署名が『総務部』」のデータ（行）のみを抜き出してみます。

結果、上のように３つのデータが抽出できました。

このように、条件に該当するデータを複数抽出できるようになります。

１－３．数式のメリット｜FILTER関数の代用など

この数式のメリットは、次の通りです。

まず、「条件に合うデータすべて」を抽出できます。VLOOKUP関数などでは、基本的に1つだけです。

また、FILTER関数のほうが正直便利ですが、使えないバージョンでもこの数式は使えるので代用になります。

そして、「条件指定の幅が広い」のも特徴で、「複数条件」や「含む条件」、「以上・以下の範囲条件」、「どれかに当てはまれば～」の「OR条件」なども指定可能です。

なお、関数での抽出なので、フィルター機能と違って「元の表は変わらず残る」のも特徴です。

数式は、「FILTER関数の代用」としても使えます。

２－１．FILTER関数は便利

FILTER関数とは、「条件に合うデータのみに絞り込むことができる」関数です。

この関数は、紹介する数式よりも簡単で、とても使い勝手がいいです。

２－２．使えるバージョンと困る例

ただFILTER関数には、一つ弱点があります。それは「Excel2021かMicroSoft365以外では使えない」ことです。

FILTER関数で困る例は、次の通りです。

特にあるのが、「別のパソコンでファイルを開いたらエラーになった」です。

開くパソコンのエクセルが2016や2019の場合には、FILTER関数が使えないのでエラーになるのです。

対して、ここで説明する数式はこのような事態を防ぎ、FILTER関数の代用として使うことができます。

ちなみに、FILTER関数の使い方を知りたい場合には、次のページで解説しています。

⇒ FILTER関数の使い方の解説はこちら

次に、記事全体の構成とページごとの概要です。

全部で14ページありますが、全て見る必要はありません。重要なのは「このページ」と「数式の使い方・作り方を解説した２ページ目」です。

このページでは「数式の基本的な作り方」が分かりますが、細かいポイントは2ページ目で触れているため、見たほうがより分かりやすくなっています。

後は、やりたいことに応じて確認するとよいです。

★１ページ目：数式の形と引数の指定の仕方（このページ）

　数式の形と引数の指定の仕方を、例をあげて確認します

★２ページ目：数式の使い方・作り方

　数式の使い方や作り方を、条件一つの形で紹介します

３ページ目：数式の解説

　数式の意味を、2ページ目で紹介した形をもとに説明します

４ページ目：条件の基本的な指定の仕方

　「複数条件」、「条件値以外」、「条件値のセル参照」の方法を説明します

５ページ目：「空白」、「空白以外」の条件にする

　条件を「空白」、「空白以外」とする方法を説明します

６ページ目：「以上」、「以下」の条件にする

　条件を「以上」、「以下」等とする方法を説明します

７ページ目：「含む」、「含まない」の条件にする

　条件値を「含む」、「含まない」とする方法を説明します

８ページ目：「OR（または）条件」にする

　「OR条件」とする方法を説明します

９ページ目：「OR条件」をまとめて指定する

　「OR条件をリストでまとめて指定」する方法を説明します

10ページ目：「OR条件以外」と指定する

　「OR条件以外」とする方法を説明します

11ページ目：数式のQ&A

　数式のQ&Aです

　数式をコピーした時にうまくいかない、エクセルの動作が重くなったなどの対処法を解説しています

12ページ目：空白が０と表示される場合の対処法

　空白が０となってしまう場合の対処法を説明します

では、条件が一つの場合の数式の形と引数の入れ方を確認します。

４－１．数式の形は

条件が一つの場合、数式の形は次の通りです。

必要なのは、「抽出対象の列（＝抽出したい値が入った1列すべて）」、「条件式」、「条件範囲（＝条件の検索対象の範囲）」の３つです。

４－２．各引数の入れ方

次に、各引数の入れ方を見ていきます。

４－２－１．抽出対象の列

「抽出対象の列」には、「抽出したい値の入った列」を1列指定します。

例えば、A列の値を抜き出したい場合には「A:A」、D列の場合には「D:D」となります。

４－２－２．条件式

「条件式」は、次の順番で書きます。

指定の仕方はそれぞれ次の通りです。

例えば、条件式を「B5からB10で『50以上』」としたい場合、それぞれ次のようになります。

つなげると、

$B$5:$B$10>=50

で、これが条件式です。

そして特に条件値は、ケースによって書き方が違うため注意が必要です。

（１）条件値が「文字列」、「日付」、「時刻」の場合

条件値を文字列にする場合には値を「"」で囲みます。そして、日付の場合にはDATEVALUE関数を、時刻にする場合には、TIMEVALUE関数を使います。

数式の例は、それぞれ次の通りです。

（２）条件値が「セル番号」、「数値」の場合

条件値を数値やセル番号にする場合には、そのまま入力すればOKです。

数式の例は、それぞれ次の通りです。

４－２－３．条件範囲

条件式で指定した「条件範囲」を、ここにも同じく絶対参照で指定します。

しかし本当は、列番号は条件範囲と違っても問題ありません。

ただ、全く同じものを入れると覚えたほうが分かりやすいと思います。

４－２．「数式の使い方・作り方のポイント」は次のページで解説

数式は、FILTER関数と違って「一つのセルに数式を入れる」だけでは完成しません。

「フィルター機能」や「FILTER関数」のように使いたい時には、この他手順があり、長くなるため次のページで解説しています。

例えば、「数式作成からコピーの手順」や「絶対参照にする箇所」などの説明です。

下のリンクをクリックすると次のページに行けます。

　⇒ 「数式の使い方・作り方」のページへはこちら

次に、複数条件にしたい場合の数式の形と引数の入れ方です。

５－１．数式の形は

複数条件の場合、数式の形は次の通りです。

※ 条件が二つの場合

下の例は、先ほどの表から「部署名が『総務部』で役職が『部長』」の条件と一致するデータを抜き出したものです。

必要な引数の種類は条件が一つの場合と変わりません。違うのは、「カッコで囲んだ条件式同士を『*』で掛け合わせる」ことです。

この点を押さえると、条件を３つにも４つにもできます。

なお、条件同士の関係は「AND」です。「条件のどれにも当てはまる」時に一致と判定されます。

５－２．各引数の入れ方

各引数の入れ方は、「条件が一つの場合」と同じです。

条件の数が増えるだけで、各引数の入れ方は変わりません。

続いて、条件に合うデータ全てを「別シート」や「別ファイル」から抜き出すときのポイントを見てみます。

６－１．別シートから抽出する場合

データを別シートから抽出する場合、工夫の必要な引数は「抽出対象の列」と「条件範囲」です。

この２つに、「『抽出元の表』があるシート」の列や範囲を指定します。

別シートの列や範囲を指定するには、「『対象のシート』をクリック」→「『列』または『範囲』を選択」の順に操作します。

すると、引数の前に「シート名!」、または「'シート名'!」が自動で付き、別シート参照が簡単にできます。

６－２．別ファイルから抽出する場合

データを別ファイルから抽出する場合も、工夫の必要な引数は「抽出対象の列」と「条件範囲」です。

別ファイルの列や範囲を指定するには、「『対象のファイル』をクリック」→「『対象のシート』をクリック」→「『列』または『範囲』を選択」の順に操作します。

すると、引数の前に「保存場所\[ブック名]シート名!」、または「'保存場所\[ブック名]シート名'!」が自動で付き、別シート参照が簡単にできます。

次のページでは、数式の使い方を解説します。

７－１．FILTER関数のように使うためには「コツ」がある

紹介した数式は、FILTER関数のように使うための「コツ」があります。

反対に言うと、FILTER関数のように「一つのセルに入れただけで表形式で抜き出せるわけではない」のです。

そのコツに触れながら、数式作りの手順を一つ一つ確認します。

７－２．次のページへのリンク

次のページへは、以下のリンクかボタンをクリックしてください。

⇒ 次ページ「数式の使い方・作り方」へはこちら

以上、参考になれば幸いです。

なお、一連の記事の好きなページに行けるリンクはこちらです。

• 条件に合うデータ抽出　各ページへのリンクはこちら

• ◎具体例の確認と数式の概要（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頁）

関数の問題解決記事一覧へはこちら