条件に合うデータを関数で全て順に抽出できる数式の作り方

このページは「条件に合うデータを全て順に抽出する方法」の2ページ目です(⇒ 1ページ目へはこちら

 

ここでは、数式の作り方を解説します。

1.数式の形を確認

始めに、「条件に合うデータを順に抽出」できる数式の形や使い方、引数の指定の仕方を確認します。

 

1-1.数式の基本形

数式の基本形は、次の通りです。 

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

引数は、「元データの最左列」、「条件式」、「条件範囲」の3つです。

 

数式の形をコピーして、引数のところを直して使うと簡単だと思います。

 

1-2.数式は「左上のセル」に作る

数式は、基本的に「結果を表示させる範囲の『左上のセル』」に作ってください。

 

下の例の場合は、「セルA15」になります。

セルA15に入れた数式を右および下方向へコピーすることで、より簡単にFILTER関数っぽく使えるようになります。

 

「左上に数式を作る」 → 「右・下方向へコピー」 → 「出来上がり」、とイメージしてください。

2.「条件が一つの数式」を作ってみる

次に、数式を実際に作ってみます。

上は、ある企業の出張実績の一覧です。

 

出張名や出張先、出張日、社員名、部署名と役職が入力されています。

 

この表(【元データ】)から、「部署が『総務部』」のデータを抜き出し、下の表(【抜き出したデータ】)に表示させてみます。

 

以下手順です。

 

(1)以下の数式をコピーし、結果を表示したい範囲の左上のセルに貼り付ける

【コピーする数式】

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

※ 複数条件の場合には、この後紹介する数値をコピーし貼り付けます

 

(2)「抽出対象の列」の文字を消し、元データの一番左側の列をクリックする

※ 列番号(赤矢印部分)を押すと簡単です

 

(3)「条件式」の文字を消し、まずは「条件範囲」を指定する

※マウスの左ボタンを押しながら選択すると簡単です

 

(4)キーボードのF4キーを1回押し、「$」を全部で4つ付ける

※ F4キーはキーの最上段にあります

 

(5)比較演算子を入力する

※ 条件に応じて「=」か「<>」、「>」などの記号を入れます

 

(6)条件値を入力する

※ セル番号を入れる場合には、F4キーを1回押して絶対参照にします

 

(7)「条件範囲」の文字を消し、「条件範囲」を指定する

 

(8)キーボードのF4キーを1回押し、「$」を全部で4つ付ける

 

(9)Enterキーを押す

 

(10)セルの右下にカーソルを当て、「+」マークになったら右方向に左ドラッグする

※ 左ドラッグとは、マウスで左クリックを押しながらカーソルを移動させることです

 

(11)範囲の右下にカーソルを当て、「+」マークになったら下方向にドラッグすれば完了

結果、セルA15に入れた数式は次のようになります。

=IFERROR(INDEX(A:A,1/LARGE(INDEX(($F$4:$F$9="総務部")/ROW($A$4:$A$9),0),ROW(A1))),"")

 

ポイントは、次の通りです。

数式作りのポイント
  • 数式は「左上のセル」に作り、その他のセルにコピーする
  • 「条件範囲」は絶対参照にする
  • 「条件値」もセル番号の場合には「絶対参照」にする

3.各引数の入れ方をおさらい

次に、各引数の入れ方をおさらいします。

 

3-1.抽出対象の列

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

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

 

3-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

3-3.条件範囲

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

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

 

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

4.複数条件の数式の形は

条件を複数にしたい場合、「どれも満たす(AND)」形にするか、「どれかを満たす(OR)」形にするかで、数式が変わります。

 

4-1.AND条件の場合

複数条件をAND(どの条件も満たす)で指定したい場合、ポイントは次の通りです。

AND条件の場合
  • 「(条件式)」の後に「*(条件式)」を追加する
  • 条件式内の「条件範囲」はどれも絶対参照にする

例えば、条件が二つの場合は次のようになります。

【コピーする数式】

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

上の例は、条件に「役職が『部長』の人」を足したものです。

 

数式は、次のようになります。

=IFERROR(INDEX(A:A,1/LARGE(INDEX(($F$4:$F$9="総務部")*($G$4:$G$9="部長")/ROW($A$4:$A$9),0),ROW(A1))),"")

 

このように、複数のAND条件を増やしたい場合には、「*(条件式)」を増やすとできます。

 

4-2.OR条件の場合

複数条件をOR(どれかの条件を満たす)で指定したい場合には、状況によって数式の形がさらに変わります。

 

詳しくは8ページ目で解説します。

⇒「OR(または)条件の指定方法」はこちら

 

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

⇒ 次ページ「計算の流れの解説」へはこちら

2
3
4
5
6

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


Top