Excelでの複数条件による行の抽出方法を記載します。
条件が一つのときであればVLOOKUPまたはMATCHとINDEXの組み合わせでできますが、
複数条件の時はこれらではなく、SUMPRODUCTを使用します。
SUMPRODUCTによる複数条件抽出
数式
下図のE3とF3に入力をすると、条件にあった情報がH3とI3に出力されます。
※注意点：一致する行が１つでないときは誤った答えが返されます。
１つでない可能性があるなら下部に書いているような対策が必要です。
H3の数式=SUMPRODUCT((A3:A6=E3)*1, (B3:B6=F3)*1, ROW(C3:C6))
I3の数式=INDEX(C:C, H3)
簡単な解説
前提として、Excelは{}で配列を記述します。
・SUMPRODUCTの説明
SUMPRODUCTはベクトルでいうところの内積を計算する関数です。=SUMPRODUCT({0,1,2}, {3,4,5}, {6,7,8}, {9,10,11})
という数式を入力すると0*3*6*9 + 1*4*7*10 + 2*5*8*11
が計算されて1160
という答えが返されます。
・引数の説明
上で第一引数に入れているA3:A6=E3
はTrue:False配列を返します。
具体的には{True, True, False, False}
です。
ここに1を掛けることで{1, 1, 0, 0}
という数値の配列に型を変えます。
第二引数も同様です。
・ROW関数ROW(C3:C6)
は行番号を返す関数なので{3,4,5,6}
となります。
よってH3の数式は=SUMPRODUCT({1,1,0,0}, {0,1,0,1}, {3,4,5,6})
となるので答えは4となります。
最後に、G3の数式はC列から4番目を取得するので鈴木
を返します。
今回はわかりやすさのため2段階に分けましたが、当然1つの数式にまとめても動作します。
条件が3つ以上あるときも第一引数、第二引数のような形で増やしていけばOKです。
一致する行が１つとは限らない場合の対策
SUMPRODUCTで行番号を掛けない値が1になっていればいいわけです。
こんなかんじでIF文を使うことで、一致する行が複数あるときは該当行番号を0にしてしまうことができます。=IF(SUMPRODUCT((A3:A6=E3)*1,(B3:B6=F3)*1)=1, SUMPRODUCT((A3:A6=E3)*1, (B3:B6=F3)*1,ROW(C3:C6)),0)
赤い部分が1つであることを確認する条件式、青い部分が元々の数式です。
おまけ：条件が一つのとき
INDEXとMATCHを使う方法
図のF3に=MATCH(D3, A3:A6)、
G3に=INDEX(B3:B6, F3)
を入れることで実現します。
MATCHで該当する要素が配列の何番目にあるかを取得し、
INDEXで指定します。
VLOOKUPを使う方法
G3に=VLOOKUP(D3, A3:B6, 2)
を入れることで実現します。
条件、表、答えの列番号の3つを指定します。
さいごに
Excelを使う人はいくらでもいますが、Excelに配列の概念があることを知っている人は少ないんじゃないでしょうか。「Excel 配列」でググると配列数式のことばかりヒットしますが、実際にはいろいろな関数で配列は使われています。そのためノンプログラマよりプログラマのほうが使いこなせる数式が圧倒的に多いはずです。
配列の概念を知っていないとINDEXの挙動なんかはわかりにくいかもしれません。VLOOKUPを知っていてもINDEXやMATCHやSUMPRODUCTをあまり使わないという人がいるのはそのせいなんじゃないかなあと思います。まあそんな人がいるんじゃないかっていうのは想像なんですが。
なお、私は個人的にはVLOOKUPを使いたくありません。なぜなら行の挿入や入れ替えに対して無力だから。使用するメリットは、簡潔に書ける、可読性が高いという2点だとは思いますが、デメリットの方が強いのでMATCHとINDEXの組み合わせを使うことをお勧めします。
最後にSUMPRODUCTに関して。SUMPRODUCTでぐぐると「りんご3個、みかん5個、合計で～円」みたいなサンプルがヒットしてしまいますが、その使い方ではこの関数をつかう必要性は全くないでしょう。複数条件マッチでこそ、この関数を使う意味があると思います。
コメント