仕事柄、表形式のデータから特定の値を検索してそれに対応する値を抽出したいときにVLOOKUP関数をよく使います。以下のような感じですね。
ただVLOOKUP関数で検索できるのは1つの列だけで、複数の列を検索して対応する値を抽出することはできません。例えば以下のようなケースです。
こういう場面にはこれまでに何度も遭遇していましたが、調べてもやり方が分からなかったので、参照する元の表を加工して対応していました。上の例でいうと、
- ポジションと評価のマトリクスにしてINDEX関数とMATCH関数を組み合わせる
- ポジションと評価を"&"でつなげて1つの列に収めたうえでVLOOKUP関数を使う
といった具合です(1つ目の方法は参照する項目が3つ以上になると使えないですね)。
しかし今回いろいろ調べてみた結果、SUMPRODUCT関数を使うとそのままの形で抽出できることを発見しました。
数式を書き出すと、
=SUMPRODUCT((E2=A:A)*1,(F2=B:B)*1,C:C)
です。
※「=SUMPRODUCT((E2=A2:A11)*1,(F2=B2:B11)*1,C2:C11)」としても同じです。
SUMPRODUCT関数は、同じ行にある数値をすべて掛け合わせたうえでそれらを合計する関数ですが、それでなぜ抽出ができるのか、順を追って考えていきます。
まず、
=(E2=A2)
という数式を入力すると、「TRUE」という計算結果が表示されます。
TRUEは文字通り「真」という意味の論理値です。E2セルとA2セルに入力されているのはどちらも「M」で「E2=A2」という式は正しいので、TRUEという結果になります。これに対して、異なる値が入っているセルを=でつなげるとFALSE(偽)という結果になります。
そして、TRUEには1、FALSEには0という数値が割り当てられています。論理値に1を掛けることで数値に変換することができます。次に、
=(E2=A2:A11)*1
という数式を入れてみると、自動的に下のほうにまで数値が表示されます(エクセルのバージョンによっては違うかもしれません)。
これはどいうことかというと、
=(E2=A2)*1
=(E2=A3)*1
=(E2=A4)*1
…
=(E2=A11)*1
の計算結果が上から順に表示されています。
つまり、
(E2=A2:A11)*1
という数式により10行分の値が計算されているということですね。
(E2=A:A)*1
だとA列のすべての行に対して計算が行われ、1行目が「0」、2~6行目が「1」、7行目以降は「0」となります。
(ただしこの数式を入れると表示しきれないので「#SPILL!」のエラーになります)
同様に、
(F2=B:B)*1
という数式ではB列のすべて行に対して計算が行われ、3行目と8行目が「1」、それ以外の行は「0」となります(F2セルと同じ「A」が入力されているのはB3,B8セルだけなので)。
ということは、120という結果を抽出した
=SUMPRODUCT((E2=A:A)*1,(F2=B:B)*1,C:C)
という数式は、以下のような計算と同じだということです。
A~C列のいずれかに0が入っている行は、掛け合わせれば必ず0になります。したがってA列とB列がともに「1」、すなわちポジションと評価が両方とも一致する行の評点が計算結果として出てくるというわけです。
では、評点の列に数字以外が入っている場合はどうしたらいいでしょうか。掛け算の中に数字以外のものが入っていると0になってしまうので、そのままの数式だと0となってしまいます。
そこで、まずC:CのところをROW(C:C)に置き換えます。これによってC列の値が行数に置き換えられるので、ポジションと評価が一致する行数である「3」の結果を得ることができます。