【Excel】INDEX関数で複数列の条件を指定する方法

Excel

昨日の「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でもできるやり方です。

 

やり方としては、これが一番簡単なのですが、一方で、ソースデータの制約上、条件列を追加するのが難しいこともありますので、今回は、ソースデータには触らずに条件を絞り込む方法を紹介しました。

 

意外と探すのにてこずったので、少しでもお役に立てば幸いです。

 

コメント

タイトルとURLをコピーしました