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

  > > 条件に合うデータすべての抽出1

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

 

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




1.どんなことができる数式か?

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

 

1-1.条件に合うものをすべて抽出できる

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

 

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

 

1-2.例を確認

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

条件に合うデータ抽出-例の表

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

 

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

条件に合うデータ抽出-数式を使った例

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

 

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

 

1-3.数式のメリット|FILTER関数の代用など

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

  • 「条件に合うデータを全て」抽出できる
  • FILTER関数が使えないバージョンでも使える
  • 条件の指定の幅が広い(複数条件/含む/以上・以下/OR)
  • 作業列が必要ない
  • 「抽出元の表」は変わらず残る

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

 

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

 

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

 

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



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

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

 

2-1.FILTER関数は便利

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

条件に合うデータ抽出-FILTER関数の代用

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

 

2-2.使えるバージョンと困る例

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

 

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

  • Excelのバージョンが古くてFILTER関数が使えない
  • 職場で作ったファイルを自宅で開いたら、FILTER関数がエラーになった

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

 

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

 

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

 

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

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



3.記事全体の構成と各ページの概要

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

 

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

 

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

 

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

 

3-1.基本知識

★1ページ目:数式の形と引数の指定の仕方(このページ)

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

 

★2ページ目:数式の使い方・作り方

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

 

3ページ目:数式の解説

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

 

3-2.様々な条件指定方法

4ページ目:条件の基本的な指定の仕方

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

 

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

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

 

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

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

 

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

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

 

8ページ目:「OR(または)条件」にする

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

 

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

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

 

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

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

 

3-3.エラー等発生時の対応策

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

 数式のQ&Aです

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

 

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

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



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

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

 

4-1.数式の形は

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

=IFERROR(INDEX(抽出対象の列,1/LARGE(INDEX((条件式)/ROW(条件範囲),0),ROW(A1))),"")

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

 

4-2.各引数の入れ方

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

 

4-2-1.抽出対象の列

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

条件に合うデータ抽出-抽出対象の列の入れ方

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

 

4-2-2.条件式

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

条件範囲 → 比較演算子 → 条件値

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

  • 条件範囲:条件を指定する範囲を1列(絶対参照)
  • 比較演算子:「=」「<>」「<」「<=」「>」「>=」のどれか
  • 条件値:条件値を「値」又は「セル番号(絶対参照)」で

 

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

  • 条件範囲:$B$5:$B$10
  • 比較演算子:>=
  • 条件値:50

つなげると、

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

で、これが条件式です。

 

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

 

(1)条件値が「文字列」、「日付」、「時刻」の場合

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

 

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

  • 文字列:A1="テスト"
  • 日付:A1=DATEVALUE("4/1")
  • 時刻:A1=TIMEVALUE("9:00")

 

(2)条件値が「セル番号」、「数値」の場合

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

 

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

  • セル番号:A1=B1
  • 数値:A1=50

4-2-3.条件範囲

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

条件に合うデータ抽出-条件範囲の入れ方

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

 

 

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

 

4-2.「数式の使い方・作り方のポイント」は次のページで解説

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

 

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

条件に合うデータ全て抽出-数式の作り方
2ページ目-数式の使い方・作り方

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

 

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

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



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

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

 

5-1.数式の形は

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

=IFERROR(INDEX(抽出対象の列,1/LARGE(INDEX((条件式1)*(条件式2)/ROW(条件範囲),0),ROW(A1))),"")

※ 条件が二つの場合

 

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

条件に合うデータ抽出-複数条件の場合

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

 

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

=IFERROR(INDEX(抽出対象の列,1/LARGE(INDEX((条件式1)*(条件式2)*(条件式3)/ROW(条件範囲),0),ROW(A1))),"")

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

 

5-2.各引数の入れ方

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

 

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



6.別シート/別ファイルから抽出するには

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

 

6-1.別シートから抽出する場合

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

 

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

条件に合うデータ抽出-別シートから抽出する場合

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

 

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

 

6-2.別ファイルから抽出する場合

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

 

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

 

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


口コミ・評判のいいExcel関数本の紹介】

 イラストで学ぶ 入社1年目からのExcel関数

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

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

 

7-1.FILTER関数のように使うためには「コツ」がある

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

 

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

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

 

7-2.次のページへのリンク

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

 

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

 

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

 

1
2
3
4
5