VLOOKUP関数で合計｜SUMと組み合わせて変換→集計
ここではエクセルのVLOOKUP関数で変換した後の数値を集計したい、といった場合の対応方法を解説します。
結論から言うと、作業列を使わないとVLOOKUP関数で変換した後の数値を合計することはできません。
しかしVLOOKUP以外の関数を使えばできるので、その方法を説明します。
１．まずはやりたいことを確認
始めに、やりたいことを例を含めて確認します。
１－１．VLOOKUP関数で変換後の数値を合計したい
やりたいことは、「VLOOKUP関数で変換後の数値を合計したい」です。
一つの数式でVLOOKUP関数で数値に変換しつつ、さらにそれらの合計を求めることができれば、作業用のセルが要らずにスッキリとしたものになります。
例えば、勤務コードから勤務時間の合計を求める場合などに使えます。
１－２．例を確認
一つ例をみてみましょう。
上の例は「勤務予定表」です。あらかじめ決められた「コード」を入力し、「勤務時間計」に勤務時間の合計を入れようとしています。
こんな時に思いつくのが「VLOOKUP関数でコードを各勤務時間に変換し合計する」ことです。
しかし実際には、VLOOKUP関数で変換したものを集計することは、1つの数式ではできません。変換した数値をいったん任意の作業列に表示させ、それを合計することは可能ですが…。
ではどんな数式、関数を使えばそのようなことができるのでしょうか？
２．SUMPRODUCT関数を使う
「変換して合計」を一つの数式で行うには「SUMPRODUCT関数」を使います。
では数式をどのように作ればいいのかを、SUMPRODUCT関数の引数と引数に入れる内容を見ることで確認しましょう。
２－１．SUMPRODUCT関数の引数
まず、SUMPRODUCT関数の引数は次の通りです。
=SUMPRODUCT(配列1,配列2…)
このように、SUMPRODUCT関数の引数は「配列」のみです。
上の図がSUMPRODUCT関数の「関数の引数ウィンドウ」です。数式バーに「=SUMPRODUCT(」と入力してfxボタンを押すと出てきます。
２－２．引数に入れる内容（元の表が横の場合）
では次に引数に入れる内容ですが、「数式は元の表が横型か縦型かで変わります」。
まずは、横型の場合から見てみましょう。
数式の形は、次の通りです。
=SUMPRODUCT((コードの範囲=コード表の範囲)*(変換後の数値の範囲))
「コードの範囲」には、「変換したいコードが入った範囲」を指定します。
「コード表の範囲」には、「コード表のコードの範囲」を指定します。
「変換後の数値の範囲」には、「コード表に入った変換後の数値が入った範囲」を指定します。
では、例の表を使いながら数式を作ってみます。
２－２－１．変換したいコードが入った範囲を指定する
使う引数は「配列1」のみです。まず配列1に「(」を入力後、変換したいコードが入った範囲を指定します。
例でいうとその範囲は「B4からG4」なので、配列1内は「(B4:G4」となります。
２－２－２．変換元のコードが入った範囲を指定する
次に「=」を入れ、変換元のコードが入った範囲を指定後、「)」を入れます。
例でいうとその範囲は「A9からA13」なので、配列1内は「(B4:G4=$A$9:$A$13)」となります。
２－２－３．変換したい数値が入った範囲を指定する
最後に「*(」を入れ、変換したい数値が入った範囲を指定後、「)」を入れれば完了です。
例でいうとその範囲は「E9からE13」なので、配列1内は「(B4:G4=$A$9:$A$13)*(E9:E13)」となります。
これで「元の表が横の場合の変換後の数値を合計」することができます。
２－３．引数に入れる内容（元の表が縦の場合）
では次に、縦型の場合を見てみましょう。
縦型の場合の例は、上の通りです。横型と同じ内容で、元の表が縦になっています。
数式の形は、次の通りです。
{=SUMPRODUCT((TRANSPOSE(コードの範囲)=コード表の範囲)*(変換後の数値の範囲))}
「コードの範囲」には、「変換したいコードが入った範囲」を指定します。
「コード表の範囲」には、「コード表のコードの範囲」を指定します。
「変換後の数値の範囲」には、「コード表に入った変換後の数値が入った範囲」を指定します。
なお、「{}」は手入力ではなく、一定のキー操作で付きます。
では、例の表を使いながら数式を作ってみます。
２－３－１．変換したいコードが入った範囲を指定する
数式は、やはりすべて引数「配列1」に入れますが、違うのは「TRANSPOSE関数を使う」ことと「配列数式にする」ことです。
まず配列1に「(TRANSPOSE(」と入力後、変換したいコードが入った範囲を指定します。
例でいうとその範囲は「B4からB9」なので、配列1内は「(TRANSPOSE(B4:B9」となります。
２－３－２．変換元のコードが入った範囲を指定する
次に「)=」を入れ、変換元のコードが入った範囲を指定後、「)」を入れます。
例でいうとその範囲は「F4からF8」なので、配列1内は「(TRANSPOSE(B4:B9)=$F$4:$F$8))」となります。
２－３－３．変換したい数値が入った範囲を指定する
続いて「*(」を入れ、変換したい数値が入った範囲を指定後、「)」を入れます。
例でいうとその範囲は「J4からJ8」なので、配列1内は「(TRANSPOSE(B4:B9)=$F$4:$F$8)*(J4:J8)」となります。
２－３－４．配列数式にする
最後にShiftキーとCtrlキーを押しながらEnterキーを押すと配列数式になります。
配列数式になると数式の両サイドに「｛｝」が付きます。「元の表が縦の場合の変換後の数値を合計」することができます。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】