昨日の「INDEX関数で大文字・小文字を区別する方法」に引き続き、今日は、INDEX関数で複数列の条件を抽出させる方法です。
これは、以下の条件に当てはまるときに使えます。
詳細を見ていきましょう。
複数列の条件指定はSUMPRODUCTをINDEXに入れ込む
この例では、右側のテーブルに国ごとに代表的な都市名がリスト化されており、左側のテーブルで、各国の国名と首都名を抽出しようとしています。
この場合、E列のCountry IDで国を特定し、H列のTypeが「Capital」であるものを抽出したいというわけで、E列とH列の2列の条件指定が必要です。
詳細は後述しますが、この場合、SUMPRODUCTは使えないので、有効な式（セルC2に入力した式）は以下の通りになります（見やすくするため、実際の式中で用いている$は省いています）
=INDEX(G1:G12,SUMPRODUCT((E1:E12=A2)*(H1:H12=C1)*ROW(E1:E12)))
計算式の解説は以下の通りです。
全てのセルに適用すると、以下の通りに無事抽出できました。
SUMPRODICTは文字列の抽出には使えない
通常、複数列の条件指定と言われると、まず考えるのはSUMPRODUCTかと思います。
実際にC2に以下の式を入れてみると、
=SUMPRODUCT((G2:G12)*(E2:E12=A2)*(H2:H12=C1))
「#VALUE!」になります。
これは、SUMPRODUCTでは文字列の抽出が出来ないために起こる事象です。
実際、この表の例で、G列が数字の場合は、下記の通り、SUMPRODUCTでも指定が可能です。
条件抽出のために複数列の値を1列にCONCATENATEする方法
本来、このようなケースで一番簡単なのは、E列とH列の値をCONCATENATEした列を新規に作って、その列で条件指定する場合かと思います。
上記の表では、I列にE列&H列を追加し、I列を使ってシンプルにINDEX-MATCHで絞り込んでいます。
条件列を、I列ではなくD列に追加すれば、VLOOKUPでもできるやり方です。
やり方としては、これが一番簡単なのですが、一方で、ソースデータの制約上、条件列を追加するのが難しいこともありますので、今回は、ソースデータには触らずに条件を絞り込む方法を紹介しました。
意外と探すのにてこずったので、少しでもお役に立てば幸いです。
コメント