条件に合うデータを関数で全て順に抽出できる数式の作り方
１．数式の形を確認
始めに、「条件に合うデータを順に抽出」できる数式の形や使い方、引数の指定の仕方を確認します。
１－１．数式の基本形
数式の基本形は、次の通りです。
引数は、「元データの最左列」、「条件式」、「条件範囲」の３つです。
数式の形をコピーして、引数のところを直して使うと簡単だと思います。
１－２．数式は「左上のセル」に作る
数式は、基本的に「結果を表示させる範囲の『左上のセル』」に作ってください。
下の例の場合は、「セルA15」になります。
セルA15に入れた数式を右および下方向へコピーすることで、より簡単にFILTER関数っぽく使えるようになります。
「左上に数式を作る」 → 「右・下方向へコピー」 → 「出来上がり」、とイメージしてください。
２．「条件が一つの数式」を作ってみる
次に、数式を実際に作ってみます。
上は、ある企業の出張実績の一覧です。
出張名や出張先、出張日、社員名、部署名と役職が入力されています。
この表（【元データ】）から、「部署が『総務部』」のデータを抜き出し、下の表（【抜き出したデータ】）に表示させてみます。
以下手順です。
（１）以下の数式をコピーし、結果を表示したい範囲の左上のセルに貼り付ける
【コピーする数式】
※ 複数条件の場合には、この後紹介する数値をコピーし貼り付けます
（２）「抽出対象の列」の文字を消し、元データの一番左側の列をクリックする
※ 列番号（赤矢印部分）を押すと簡単です
（３）「条件式」の文字を消し、まずは「条件範囲」を指定する
※マウスの左ボタンを押しながら選択すると簡単です
（４）キーボードのF4キーを１回押し、「$」を全部で４つ付ける
※ F4キーはキーの最上段にあります
（５）比較演算子を入力する
※ 条件に応じて「=」か「<>」、「>」などの記号を入れます
（６）条件値を入力する
※ セル番号を入れる場合には、F4キーを1回押して絶対参照にします
（７）「条件範囲」の文字を消し、「条件範囲」を指定する
（８）キーボードのF4キーを１回押し、「$」を全部で４つ付ける
（９）Enterキーを押す
（１０）セルの右下にカーソルを当て、「+」マークになったら右方向に左ドラッグする
※ 左ドラッグとは、マウスで左クリックを押しながらカーソルを移動させることです
（１１）範囲の右下にカーソルを当て、「+」マークになったら下方向にドラッグすれば完了
結果、セルA15に入れた数式は次のようになります。
=IFERROR(INDEX(A:A,1/LARGE(INDEX(($F$4:$F$9="総務部")/ROW($A$4:$A$9),0),ROW(A1))),"")
ポイントは、次の通りです。
- 数式は「左上のセル」に作り、その他のセルにコピーする
- 「条件範囲」は絶対参照にする
- 「条件値」もセル番号の場合には「絶対参照」にする
３．各引数の入れ方をおさらい
次に、各引数の入れ方をおさらいします。
３－１．抽出対象の列
「抽出対象の列」には、「抽出したい値の入った列」を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
３－３．条件範囲
条件式で指定した「条件範囲」を、ここにも同じく「絶対参照」で指定します。
しかし本当は、列番号は条件範囲と違っても問題ありません。
ただ、全く同じものを入れると覚えたほうが分かりやすいと思います。
４．複数条件の数式の形は
条件を複数にしたい場合、「どれも満たす（AND）」形にするか、「どれかを満たす（OR）」形にするかで、数式が変わります。
４－１．AND条件の場合
複数条件をAND（どの条件も満たす）で指定したい場合、ポイントは次の通りです。
- 「(条件式)」の後に「*(条件式)」を追加する
- 条件式内の「条件範囲」はどれも絶対参照にする
例えば、条件が二つの場合は次のようになります。
【コピーする数式】
上の例は、条件に「役職が『部長』の人」を足したものです。
数式は、次のようになります。
=IFERROR(INDEX(A:A,1/LARGE(INDEX(($F$4:$F$9="総務部")*($G$4:$G$9="部長")/ROW($A$4:$A$9),0),ROW(A1))),"")
このように、複数のAND条件を増やしたい場合には、「*(条件式)」を増やすとできます。
４－２．OR条件の場合
複数条件をOR（どれかの条件を満たす）で指定したい場合には、状況によって数式の形がさらに変わります。
詳しくは８ページ目で解説します。
以上、参考になれば幸いです。
【口コミ・評判のいい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頁）