【Excel・エクセル】SUMPRODUCTとINDEXを組み合わせて複数条件に合う値を取り出す
2つの条件に合う値を求められないかな？
SUMPRODUCT関数とINDEX関数を組み合わせてみよう。
- SUMPRODUCT関数とINDEX関数で複数条件に合う値を求められる
- =INDEX(取り出したい値がある列,SUMPRODUCT((1つ目の条件範囲=1つ目の条件)*(2つ目の条件範囲=2つ目の条件),ROW(求めたい値がある列)))
SUMPRODUCT関数×INDEX関数の練習ファイルダウンロード
一緒にやってみませんか？
【Excel・エクセル】SUMPRODUCT関数、INDEX関数、ROW関数のおさらい
とりあえずやり方を知りたい！という方は「SUMPRODUCT関数×INDEX関数の使い方」へお進みください！
例えば次の表で、担当者が「もりの」、価格が「150」円の商品名を知りたい場合。
いくつか方法はありますが、今回はSUMPRODUCT関数、INDEX関数、ROW関数を使って求めたいと思います。
それぞれ、どんな関数だっけ？
=SUMPRODUCT((配列1=条件)*(配列2))
| 配列1 | 条件範囲 |
| 条件 | 条件 |
| 配列2 | 合計したい範囲 |
=INDEX(配列,行番号,列番号)
| INDEX | この範囲の、この位置の値を教えて |
| 配列 | この範囲の |
| 行番号 | この位置（上から数えて何行目か） 配列が1列の時は、省略できる |
| 列番号 | この位置（左から数えて何列目か） 配列が1行の時は、省略できる |
=ROW(参照)
| ROW | このセルって、何行目？ |
| 参照 | 調べたいセル |
やばい、これは難しそうな予感……！
まぁ、範囲や値を入れ替えるだけでできるから。
【Excel・エクセル】SUMPRODUCT関数×INDEX関数の使い方
では、先ほどの表から担当者もりの、価格150円の商品名を取り出したいと思います。
=INDEX(取り出したい値がある列,SUMPRODUCT((1つ目の条件範囲=1つ目の条件)*(2つ目の条件範囲=2つ目の条件),ROW(求めたい値がある列)))
結構ややこしいので、上の式に当てはめていけばOKですよ！
=INDEX(B1:B6,SUMPRODUCT((A1:A6=B8)*(C1:C6=B9),ROW(B1:B6)))で、求められるよ。説明するね。
ステップ1
まずはINDEX関数から書いてきます。
セルB10に、「＝INDEX(」と入力してください。
ステップ2
次に、取り出したい値がある列です。
今回は商品名を知りたいので、商品名が書かれたB1～B6をドラッグします。
そして「,（カンマ）」。
ステップ3
ここでSUMPRODUCT関数の出番です。
「SUMPRODUCT((」まで入力します。
「（」は2つ……っと。
ステップ4
続いて1つ目の条件、「担当者がもりの」です。
条件範囲の担当者が書かれたA1～A6をドラッグして、「,（カンマ）」。
条件はもりのと書かれたセルB8をクリックします。
カッコを閉じて、「*(」で次の条件へつなぎます。
ここはカンマじゃなくてイコールっと。
ステップ5
続いて2つ目の条件、「価格が150」です。
条件範囲の価格が書かれたC1～C6をドラッグして、「=」。
条件は、もりのと書かれたセルB9をクリックします。
カッコを閉じて、「,」です。
さらに「*」で条件を繋げれば、3つ以上の複数条件を指定できるんよ。
ステップ6
ここでついに登場、ROW関数。
指定する範囲は、最初にINDEX関数で指定した範囲と同じです。
「ROW(」と入力して、取り出したい値がある範囲、B1～B6をドラッグします。
最後に「)))」と入力すればOKです！
それぞれROW関数の閉じカッコ、SUMPRODUCT関数の閉じカッコ、INDEX関数の閉じカッコだね。
完成！
長い道のりでしたが、無事複数条件に合う値が抜き出せました！
INDEX関数の列番号は？
今回は1だから、省略したよ。
これで、複数条件に合う値を抜き出せますね！
それでは、お疲れさまでした！
- SUMPRODUCT関数とINDEX関数で複数条件に合う値を求められる
- =INDEX(取り出したい値がある列,SUMPRODUCT((1つ目の条件範囲=1つ目の条件)*(2つ目の条件範囲=2つ目の条件),ROW(求めたい値がある列)))