ちゃんと理解して使うEXCEL関数 : 条件にあう２番目以降を抽出する方法を理解する

そもそもこの記事を書いていこうと思ったきっかけのページはこちら。

nyanto.jimdofree.com

EXCELで”条件に一致する何番目のものを取り出す”ことをやりたいだけでなら上のページに飛んですぐにコピペして使った方がよい。この記事はなんでこの式でできるのか、よくわからない人のための記事です。この記事を何故書くのか？というと、ググって過去と同じ記事に到達しており、”あー、ｘ年前の俺も理解してないし、今も理解していないのか。。”と悲しくなったから今回は理解しようと思ったから。

頭脳CPUクロックが486SX世代の私には理解が遅く手間取ってしまったが、自分で”なるほど”と思ったので記事化しておく。

前書きは以上で、本題はこちら。理解するためのステップを段階的に書いているので、長いのはご了承ください。

"INDEX＋MATCH関数で複数該当の２番目以降を抽出する方法" の式を理解する！

まずは元ページで解説してくれている元式。

元サイト様にも解説はあるし、それ以外にもQAサイトでは説明してくれている人がいたりするのが、前から理解していくものが多い気がしている。私は内側から理解する方がわかりやすかったのでその順番で。

まずは、式が多重構造になっているのでそれをばらす。この式で使われているEXCEL関数は以下の4つ。各式自体の説明は検索すればすぐ出ると思うので、割愛。 * IFERROR　 * INDEX　 * LARGE * ROW　

下層（内側）から読み解く

内側からとは、どういうことかというと、関数を前からばらしていって最下層になるところとも言える。じゃあ前から見てるじゃん！という突っ込みは置いておき、この式の階層の下になりそうなものを見る。（ここは最下層であることを確認できなくてもよい。上にたどるとズレが見つかるから）要は一番小さい単位になるところから読んでいこうということ。

ROW関数

この式だと深そうなROWから見る。元の式を見ると、　”ROW(条件範囲）”となっている。ここでの”条件範囲”とは、大元の”複数該当の2番目以降を抽出する”でいうと複数該当を示すところ、つまり、$A$5:$A$14　である。

ROW関数は指定された範囲の行数を返すものなので、 ROW（($A$5:$A$14)　の意味は、A5からA14までが何行目なのかを返すことになる。ここで、指定された範囲がセル1つではなくて範囲なので、配列が返ってくることになる。適当なセルに　=ROW($A$5:$A$14)と入力すると、5としか出てこないが、数式入力欄でＦ９キーを押すと、その式の値を確認できる。式の値を確認する（Ｆ９）

結果、ROW($A$5:$A$14)は　{5;6;7;8;9;10;11;12;13;14}　という行数のリストが出てくることがわかる。

内側のINDEX関数～ROW関数のおまけ～

次に内側のINDEX関数を見ていく。INDEX関数に入る前に元の式を見ると、　INDEX(　(条件式)/ROW(条件範囲)　,0)　 ROWの前に”（条件式）/　”　がくっついている。ここが一番わかりにくくしている原因な気がするので確認していく。まず、”条件式”となっている部分は、　”複数該当の2番目以降を抽出する”　の、複数該当の部分。いったい何が該当するのか？という条件になる。元ページの例で言えば、正社員の列に〇がついている物というのが条件になる。これを式で書くと　( $A$5:$A$14="〇" ) と表せる。この部分だけだと何が起きているのか？　適当なセルに”＝( $A$5:$A$14="〇" ) " と入れて確認しようとすると "#VALUE"とエラー表示になってしまう。だが、先ほどと同様に数式のところでＦ９キーを押して確認すると、 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE} というリストが取れていることがわかる。つまり、指定された範囲の順番で、成り立つかどうかを返していることになる。では、　( $A$5:$A$14="〇" )/ROW($A$5:$A$14) は結局何をしているの？だが、範囲を一つのセルで考えると分かりやすい。 A5だけで考えてみる。適当なセルに "= ( $A$5="〇" )/ROW($A$5) "と入れてみると、0.2という結果が出てくる。 ( $A$5="〇" )はTRUE,ROW($A$5)＝5なので　TRUE/5　なのに0.2って何やねんだが、TRUE=1、FALSE=0だからこの計算が成り立っている。

これを図示すると以下のようになる。

実際には図に書いたように１セルずつではなくて、リストになっているわけだが。前述同様にＦ９キーで確認すると以下のように見える。 {0.2;0;0.142857142857143;0.125;0;0;0.0909090909090909;0.0833333333333333;0;0.0714285714285714}

なんでこんなことしているの？最初よくわからなかったが、当然意味があるので、続けて読んでほしい。ここでのポイントは、１を行数で割っている　行数の値の逆数になっている、というところだ。つまり、行数が進めば進むほど、値が小さくなっているということ。

さて、ようやくINDEXの話に入ろう。 INDEX関数は式の形が複数あるが、今回の場合、カンマとカッコに注意してみると、INDEX(配列,行番号）の形になっていることがわかる。 INDEX*1 1/LARGE・・・部分は前述の通りなので、 INDEX（抽出列, 条件にあう指定した番目の行数）　となる。ここでの”抽出列”は、データを抜き出したい列を指定する。そこの何行目か、ということだ。元ページだと、No部分を抜き出すので、1番目だったら以下のように記載することになる。 =IFERROR(INDEX(B:B,1/LARGE(INDEX*2,"") 取り出す行番号の指定がxlsの行番号だから、抽出列の指定の仕方も、一部の範囲ではなく列全体を指すB:Bとなる。

INDEX(抽出列,1/LARGE(INDEX*3 これで、条件が合う（〇がついている）何番目の行にある、抽出列で指定した列の値を取ってくることができる。

最初のIFERROR

長かった式もようやく最初のIFERRORに来た。 IFERROR(値，エラーの時の値）なので、 =IFERROR(INDEX(抽出列,1/LARGE(INDEX*4,"") この意味は、エラーの時は何も入れない。（空白）という条件が付いただけである。

よって、トータルとしては、 〇がついている（条件式）、その中で”番目”で指定した順番の行の抽出列で指定した列の値を取ってくる、取ってこれなかったら空白にする という式だと理解できる。

長かった・・・・

感想

（条件式）/ROW（条件範囲）で条件に合致する行番号に相当する値が作り出せた。　IFとか何もつけずに条件式をいきなり入れているから戸惑う。　別な書き方もできそう。
参照と配列の部分のモヤモヤが残ってしまった。ちゃんと見返したい。

*1:$A$5:$A$14="〇")/ROW($A$5:$A$14),0)　これが何かというと、Ｆ９で見ると、 {0.2;0;0.142857142857143;0.125;0;0;0.0909090909090909;0.0833333333333333;0;0.0714285714285714} になる。 INDEX関数に出てくる配列と参照の話はこちらの記事がわかりやすかったので貼っておく

INDEX関数の使い方をわかりやすく解説｜行番号と列番号で指定したセルを参照する【Excel】 | わたがしワーク ―Excel tips―

内側のINDEX（配列,行番号）の式なわけだが、行番号の部分が０になっているので、セルの範囲”参照”が取れていることがわかる。

ここまでの、INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0)で、条件に合致する（”〇”がついている）部分は行番号の逆数が入った範囲の参照が取れることになる。

※今回このメモを作っていて、配列と参照の記述がよく理解できていないことが分かった。EXCELで言う参照と配列の違いと、その確認方法がわからない。　①　=($A$5:$A$14="〇")/ROW($A$5:$A$14) 　②　=INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0) 　　違いはINDEX関数で行数0なので、参照を返すことなると思うのだが、Ｆ９でみるとどちらも同じように見える。　セルに代入すると①は#VALUE、②は0.2と結果が異なるので、違ってはいるのだろうが。　このあたりはもう少し調べないと分からなそうだ。　今回はここは一旦このままで先に進める。

LARGE

さて、ようやく次の外側のLARGE関数部分だ 1/LARGE(INDEX((条件式)/ROW(条件範囲),0),番目) 頭の”1/”部分は一旦おいておき、LARGE(INDEX((条件式)/ROW(条件範囲),0),番目)　だけ考える。 LARGE関数はLARGE（配列,順位）で渡したものの順位の値を返す。

ここまでの理解で、　LARGE（”条件にあったものは行数の逆数が入った値が入った配列”,大きい順に何番目かの指定）と理解できる。何番目のところを１とすれば、 =LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),1) 1番目に大きな値であった0.2が戻ってくるし、 =LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),2) とすれば2番目の0.1428...が戻ってくることになる。 =LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),2) 〇の数以上の８番目を指定すれば =LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),8) 0になるわけだ。さて、LARGEの頭についていた”１／”に戻ろう。 LARGE関数の結果として、大きい順に何番目かの行数の逆数が取れる、ということは、”１／”するということは戻すことになり、行数が取れるわけだ。つまり、 1/LARGE(INDEX((条件式)/ROW(条件範囲),0),番目)　は条件に合致した何番目のものの行番号が取得できるもの　となっているわけだ。

=1/LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),1) とすれば、５（行目）、 =1/LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),1)とすれば、７（行目）となるわけである。ここでの注意は、取得できる値は行数なので、指定した範囲の中での行番号ではなく、xlsのシート内の行番号だということだ。

外側のINDEX

やっと外側のINDEXだ。 INDEX(抽出列,1/LARGE(INDEX((条件式)/ROW(条件範囲),0),番目

*2:$A$5:$A$14="〇")/ROW($A$5:$A$14),0),1

*3:条件式)/ROW(条件範囲),0),番目

*4:条件式)/ROW(条件範囲),0),番目