条件に合うデータを関数で全て順に抽出できる数式の意味は

「条件に合うデータをすべて順に抜き出す数式」を解説する３ページ目です。

ここでは、条件に合うデータを順に抽出できる数式の構成を解説をします。

関数ごとにどのような値を返し、結果なぜ条件に合うデータを抜き出せるのかの説明です。

なお、解説の１ページ目を見るにはこちらです。

⇒１ページ目へ

まずは、条件に合うデータを順に抜き出すことができる数式の基本形と、用いる例の表や数式を確認します。

１－１．数式の基本形は

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

１－２．例に用いる表と数式

例に用いる表は、前ページでも使った次の表です。

出張実績の一覧表です。

この表から、抽出条件を「部署が『総務部』の人」とし、条件に合うものをセルA15以降に抜き出します。

数式の解説は、セルA15に入れた次の数式を元に行います。

そして、数式は内側から計算されるため、その順番で計算の流れを見ていきます。

それでは、内側から順番に動きと返す値を確認します。

２－１．INDEX((条件式)/ROW(条件範囲),0)の部分

始めに計算されるのは、「INDEX(条件式)/ROW(条件範囲),0)」の部分です。

２－１－１．ここにINDEX関数を使う意味

この部分にINDEX関数を使うのは、「(条件式)/ROW(条件範囲)」の行ごとの結果を、配列に入れたいからです。

条件式で指定する、条件に合うかを判定したい範囲は複数行になります。

そこで、条件に合っているかの判定結果とその後の計算結果を行ごとに格納するため、INDEX関数を使います。

２－１－２．条件式の部分

条件式の部分では、行単位で「条件式と合うかを判定」します。

例の表の場合には、次のようになります。

合えば「TURE」、合わなければ「FALSE」です。

２－１－３．ROW(条件範囲)の部分

ROW(条件範囲)の部分では、行単位で「行番号」を返します。

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

F4は「4」、F5は「5」...となります。

２－１－４．(条件式)/ROW(条件範囲)の結果

(条件式)/ROW(条件範囲)の結果は、条件に合う行は『1』を、合わない行は『0』を、その行番号で割る」ことになります。

エクセルでは、TUREは「1」、FALSEは「0」です。

ですから、条件に合う行は「1÷行番号（0.いくつ）」、合わない行は「0÷行番号（0）」となります。

例の場合のイメージは、次の通りです。

ここで注目したいのが、「条件に合う行の数値」です。

条件に合う場合には「1÷行番号」なので、上にある行ほど「数値が大きい」のが分かります。

この結果が、後のLARGE関数に絡んできます。

２－１－５．INDEX( ,0)で配列に格納

この結果が、INDEX関数で配列に格納されます。

例だと、「{0.25,0,0.167,0,0.125,0}」となります。

２－２．LARGE( ,ROW(A1))の部分

２－１の結果、「LARGE（　,ROW(A1))」は、次のようになります。

LARGE({0.2,0,0.167,0,0.125,0},ROW(A1))

まず、ROW(A1)は、A1の行番号が返ってくるので「1」です。

ですから、

LARGE({0.25,0,0.167,0,0.125,0},1)

となります。

LARGE関数は、「大きい方から指定した順位の値を返す」関数なので、この数式は「1番目に大きい値」の「0.25」が返ります。

２－３．1/LARGE( )の部分

２－２の結果、「1/LARGE( )」は、次のようになります。

1/0.25

計算すると、「4」になります。

つまり、「条件に合う1番上の行番号」になるのです。

4行目だけで見てみると、1/LARGE( )の部分は、

1/1/ROW(4)

ですから、結局行番号に戻るのです。

ただ、全体で見ると「条件に合う行」の「1番上の行番号」を取り出せていることになります。

２－４．INDEX( ,1/LARGE( ))の部分

２－３の結果、「INDEX（　,1/LARGE(　))」は、次のようになります。

INDEX(A:A,4)

つまり、「A列の4行目」が返ってきます。

結果、返ってくるのは、「1」となります。

２－５．IFERROR( 　,"")の部分

IFERROR関数の部分は、エラー処理です。

もし、条件に合うものが一つもなかった場合、IFERROR関数がないと#VALUE!エラーになります。

それを避けるため、IFERROR関数を使って「エラーの場合には空白」としています。

２－６．数式を下にコピーすると

数式を下にコピーすると、条件に合う２番目以降の値が抜き出せます。

数式の中で、下にコピーした時に変わるのは、「ROW(A1)」のA1だけです。

ROW(A1)は、LARGE関数の「なん番目に大きい値を取り出すか？」を指定しています。

そして、数式を入れたセルの一つ下にコピーすると、この部分が「A2」に変わり、結果「条件に合う２番目の値」を抽出できます。

２－７．数式を右にコピーすると

数式を右にコピーすると、「条件に合う右隣の値」が抜き出せます。

数式の中で、右にコピーした時に変わるのは、「A:A」だけです。

そして、この部分はINDEX関数の引数「配列」です。

数式を入れたセルの一つ右にコピーすると、この部分が「B:B」に変わり、結果「条件に合うB列の値」を抽出できます。

複数条件（AND）の場合、数式が変わるのは条件式の部分でした。

抜き出すと、

(条件式)

が、条件二つの場合、

(条件式)*(条件式)

になります。

条件式の部分は、「条件に合うかを判定」します。

そして、TUREは1、FALSEは0なので、二つの条件のどちらも当てはまる場合には1、そうでない場合には0となり、結果どちらにも当てはまるものだけが残るのです。

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

⇒ 次ページ「条件の指定方法」へはこちら

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

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

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

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