【Excel・エクセル】SUMPRODUCTとINDEXを組み合わせて複数条件に合う値を取り出す

もりの

2つの条件に合う値を求められないかな?

やまの

SUMPRODUCT関数とINDEX関数を組み合わせてみよう。

今回のポイント
  • SUMPRODUCT関数とINDEX関数で複数条件に合う値を求められる
  • =INDEX(取り出したい値がある列,SUMPRODUCT((1つ目の条件範囲=1つ目の条件)*(2つ目の条件範囲=2つ目の条件),ROW(求めたい値がある列)))

スポンサーリンク

 SUMPRODUCT関数×INDEX関数の練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

【Excel・エクセル】SUMPRODUCT関数、INDEX関数、ROW関数のおさらい

とりあえずやり方を知りたい!という方は「」へお進みください!

例えば次の表で、担当者が「もりの」、価格が「150」円の商品名を知りたい場合。

いくつか方法はありますが、今回はSUMPRODUCT関数INDEX関数ROW関数を使って求めたいと思います。

SUMPRODUCT関数×INDEX関数の練習問題
複数条件に合う値を知りたい
もりの

それぞれ、どんな関数だっけ?

SUMPRODUCT関数の書き方

=SUMPRODUCT((配列1=条件)*(配列2))

配列1条件範囲
条件条件
配列2合計したい範囲
SUMPRODUCT関数でカウントするときの式
配列2を選択し、カッコ2つで閉じる
INDEX関数の書き方1

=INDEX(配列,行番号,列番号)

INDEXこの範囲の、この位置の値を教えて
配列この範囲の
行番号この位置(上から数えて何行目か)
配列が1列の時は、省略できる
列番号この位置(左から数えて何列目か)
配列が1行の時は、省略できる
行番号と列番号を指定した画像
4行3列の位置にいるポケモンは!?
ROW関数の書き方

=ROW(参照)

ROWこのセルって、何行目?
参照調べたいセル
ROW関数でセルの行番号を取得しようとする画像
このセル、何行目かな?
ROW関数で行番号が表示できた画像
あ、6行目なんだね!
もりの

やばい、これは難しそうな予感……!

やまのののんびりした顔
やまの

まぁ、範囲や値を入れ替えるだけでできるから。

【Excel・エクセル】SUMPRODUCT関数×INDEX関数の使い方

では、先ほどの表から担当者もりの、価格150円の商品名を取り出したいと思います。

SUMPRODUCT関数×INDEX関数の練習問題
複数条件に合う値を知りたい
先にやり方を確認

=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(」と入力してください。

「=INDEX(」まで入力した画像
まずは関数名

ステップ2

次に、取り出したい値がある列です。

今回は商品名を知りたいので、商品名が書かれたB1~B6をドラッグします。

そして「,(カンマ)」

取り出したい値がある範囲をドラッグした画像
取り出したい値がある範囲

ステップ3

ここでSUMPRODUCT関数の出番です。

「SUMPRODUCT((まで入力します。

「SUMPRODUCT((」と入力した画像
「SUMPRODUCT(」と入力
もりの

「(」は2つ……っと。

ステップ4

続いて1つ目の条件、「担当者がもりの」です。

条件範囲の担当者が書かれたA1~A6をドラッグして、「,(カンマ)」

条件はもりのと書かれたセルB8をクリックします。

カッコを閉じて、「*(」で次の条件へつなぎます。

1つ目の条件を指定した画像
1つ目の条件を指定
もりの

ここはカンマじゃなくてイコールっと。

ステップ5

続いて2つ目の条件、「価格が150」です。

条件範囲の価格が書かれたC1~C6をドラッグして、「=」

条件は、もりのと書かれたセルB9をクリックします。

カッコを閉じて、「,」です。

2つ目の条件を指定した画像
2つ目の条件を指定
やまの

さらに「*」で条件を繋げれば、3つ以上の複数条件を指定できるんよ。

ステップ6

ここでついに登場、ROW関数。

指定する範囲は、最初にINDEX関数で指定した範囲と同じです。

「ROW(」と入力して、取り出したい値がある範囲、B1~B6をドラッグします。

ROW関数を入力した画像
INDEX関数で選んだ範囲と同じ

最後に「)))」と入力すればOKです!

全関数を一気に閉じる
やまの

それぞれROW関数の閉じカッコ、SUMPRODUCT関数の閉じカッコ、INDEX関数の閉じカッコだね。

完成!

長い道のりでしたが、無事複数条件に合う値が抜き出せました!

複数条件に合う値が抜き出せた画像
複数条件に合う値が抜き出せた!
もりの

INDEX関数の列番号は?

やまの

今回は1だから、省略したよ。

これで、複数条件に合う値を抜き出せますね!
それでは、お疲れさまでした!

今回のおさらい
  • SUMPRODUCT関数とINDEX関数で複数条件に合う値を求められる
  • =INDEX(取り出したい値がある列,SUMPRODUCT((1つ目の条件範囲=1つ目の条件)*(2つ目の条件範囲=2つ目の条件),ROW(求めたい値がある列)))