条件に合うデータを関数で全て順に抽出できる数式の作り方
◎トップページ > エクセル関数 問題解決 > 条件に合うデータすべての抽出 > 2
このページは「条件に合うデータを全て順に抽出する方法」の２ページ目です（⇒ １ページ目へはこちら）。
ここでは、数式の作り方を解説します。
【お知らせ】
2021年10月31日に、数式を変更しました。結果、もっと分かりやすく使いやすくなったと思います。Chiquilin様、ありがとうございました。
以前の数式の解説は、期限付きで以下のリンクから見られます。
⇒ （旧）条件に合うデータをすべて一覧から抽出できる数式の作り方
１．数式の形を確認
始めに、「条件に合うデータを順に抽出」できる数式の形や使い方、引数の指定の仕方を確認します。
１－１．数式の基本形
数式の基本形は、次の通りです。
=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((条件式)/ROW(条件範囲),0),ROW(A1))),"")
※ 条件が一つの場合
引数は、「元データの最左列」、「条件式」、「条件範囲」の３つです。
数式の形をコピーして、引数のところを直して使うと簡単だと思います。
１－２．数式は「左上のセル」に作る
数式は、「結果を表示させる範囲の『左上のセル』」に作ってください。
下の例の場合は、「セルA15」になります。
そして、セルA15に入れた数式を右および下方向へコピーします。
「左上に数式を作る」 → 「右・下方向へコピー」 → 「出来上がり」、とイメージしてください。
１－３．引数の指定の仕方
続いて、各引数の指定の仕方を見てみます。
ちなみに、ここは「こんなもんなんだ」レベルで読み流してください。次の「２．数式を作ってみる」を見ると、もっと分かると思います。
【基本形】
=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((条件式)/ROW(条件範囲),0),ROW(A1))),"")
１－３－１．「元データの最左列」
「元データの最左列」には、「元データの一番左の列全体」を１列指定します。
例えば、元データの一番左がA列の場合には「A:A」、D列の場合には「D:D」となります。
１－３－２．「条件式」
「条件式」は、
条件範囲 → 比較演算子 → 条件値
の順で指定します。
注意点は次の通りです。
・条件範囲：条件を指定する範囲を１列指定（←必ず絶対参照で）
・比較演算子：「=」や「<>」などの記号を指定
・条件値：条件値を値またはセル番号で指定（←セル番号は絶対参照で）
例えば、条件を「B5からB10で『50以上』」としたい場合、それぞれ次のようになります。
・条件範囲：$B$5:$B$10（『$』を付けて絶対参照化）
・比較演算子：>=
・条件値：50
つなげると、
$B$5:$B$10>=50
となります。これが条件式です。
なお、条件値をセル番号で指定する場合には、絶対参照にします。
例えば、先ほどの例で条件値「50」がセルA1に入っている場合、条件式は次の通りです。
$B$5:$B$10>=$A$1
このように、条件値にも「$」を付けます。
なお、適切に絶対参照にしないと、数式をコピーした時に正しい結果が返ってこないので、注意しましょう。
１－３－３．「条件範囲」
条件式で指定した「条件範囲」を、ここにも入れます。
同じく絶対参照にします。
なお、本当は列番号は条件範囲と違っても問題ありません。
ただ、全く同じものを入れると覚えたほうが分かりやすいと思います。
２．数式を作ってみる
次に、数式を実際に作ってみます。
上は、ある企業の出張実績の一覧です。
出張名や出張先、出張日、社員名、部署名と役職が入力されています。
この表（【元データ】）から、「部署が『総務部』」のデータを抜き出し、下の表（【抜き出したデータ】）に表示させてみます。
以下手順です。
（１）以下の数式をコピーし、結果を表示したい範囲の左上のセルに貼り付ける
【数式】
=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((条件式)/ROW(条件範囲),0),ROW(A1))),"")
※ 複数条件の場合には、この後紹介する数値をコピーし貼り付けます
（２）「元データの最左列」の文字を消し、元データの一番左側の列をクリックする
※ 列番号（赤矢印部分）を押すと簡単です
（３）「条件式」の文字を消し、まずは「条件範囲」を指定する
※マウスの左ボタンを押しながら選択すると簡単です
（４）キーボードのF4キーを１回押し、「$」を全部で４つ付ける
※ F4キーはキーの最上段にあります
（５）比較演算子を入力する
※ 条件に応じて「=」か「<>」、「>」などの記号を入れます
（６）条件値を入力する
※ 条件値の入れ方は、この後詳しく解説します
（７）「条件範囲」の文字を消し、「条件範囲」を指定する
（８）キーボードのF4キーを１回押し、「$」を全部で４つ付ける
（９）Enterキーを押す
（１０）セルの右下にカーソルを当て、「+」マークになったら右方向に左ドラッグする
※ 左ドラッグとは、マウスで左クリックを押しながらカーソルを移動させることです
（１１）範囲の右下にカーソルを当て、「+」マークになったら下方向にドラッグすれば完了
結果、セルA15に入れた数式は次のようになります。
=IFERROR(INDEX(A:A,1/LARGE(INDEX(($F$4:$F$9="総務部")/ROW($A$4:$A$9),0),ROW(A1))),"")
今回は、配列数式にする必要はありません。
簡単ですね。
３．条件値の指定の仕方
続いて、条件値の指定の仕方をもう少し掘り下げます。
各パターンでの条件値の指定の仕方を、確認してみましょう。
３－１．数式内に入力する場合
条件値を数式内に入力する場合、条件値の種類によって書き方が変わります。
（１）文字列
文字列の場合には、条件値を「"」で囲みます。
例）条件値が「総務部」→”総務部"
（２）数値
数値の場合には、そのまま入力します。
例）条件値が「50」→50
（３）日付
日付の場合には、DATEVALUE関数を使い「"」で囲みます。
例）条件値が2021年1月1日→DATEVALUE("2021/1/1”)
（４）条件値が時間
時間の場合には、TIMEVALUE関数を使い「"」で囲みます。
例）条件値が8:00→TIMEVALUE("8:00")
３－２．セル番号を指定する場合
セルに入力された値を条件値にする場合には、条件値の種類に関わらず、セル番号をそのまま入力します。
ただし、絶対参照にして数式コピーで参照先が動かないようにします（$を2つ付ける）。
条件値にカーソルを当て、F4キーを１回押しましょう。
例）セルA1に条件値が入っている→$A$1
４．複数条件の数式の形は
条件を複数にしたい場合、「どれも満たす（AND）」形にするか、「どれかを満たす（OR）」形にするかで、数式が変わります。
４－１．AND条件の場合
複数条件をAND（どの条件も満たす）で指定したい場合には、条件式の後に「*（条件式）を追加します。
例えば、条件が二つの場合は次のようになります。
=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((条件式1)*(条件式2)/ROW(条件範囲),0),ROW(A1))),"")
三つの場合はこうなります。
=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((条件式1)*(条件式2)*(条件式3)/ROW(条件範囲),0),ROW(A1))),"")
四つの場合はこうです。
=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((条件式1)*(条件式2)*(条件式3)*(条件式4)/ROW(条件範囲),0),ROW(A1))),"")
このように、「*（条件式）」を追加すれば、条件を増やせます。
４－２．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頁）