VLOOKUP関数より便利！実務で使えるSUMPRODUCT関数

2022年9月3日2022年11月15日

当ページのリンクには広告が含まれています。

URLをコピーしました！

VLOOKUPの上位互換。SUMPRODUCT関数を超細かく説明。
今まで知らなかった人も簡単な算出なら今日から使えるようになります。
※注※上位互換は勝手に思っているだけです。

関数の使い方をサイトで調べたけど意味が分からない。専門用語や肝心な部分の説明がない…
難しくて結局理解できない。と悩んだ経験はあると思います。
今回は私と同じような調べても分からなかった方へとことん嚙み砕いて説明します！

こちらの関数は私が本業で予算管理などの際に使用している関数になります。
※より実務向けだと思いますので、本来のSUMPRODUCT関数の使い方とは若干違いますのでご了承ください。

いきなり実務で説明すると複雑なので、3つのステップに分けます。

ステップ①　SUMPRODUCT関数はどんな時に使う？やり方は？

まず下記の表に対しての①～③の出し方。計算方法をご覧ください。

うわっ。エグッ。無理。と思った方。ちょっと待ってください。
めちゃくちゃ分解して説明するので、今はこんな式になるんだ。ぐらいの感じで大丈夫です。

1個1個見ていけば理解できます。

まずは質問の内容です。実際に関数を使って質問通りに出そうと考えると、どうでしょうか？

VLOOKUP関数は使える！という方だとお気づきかもしれませんが、
①の場合、「村人」、「AM」、「3日目」と３つの条件を含みながら答えを出す。
VLOOKUPでは不可能ですよね？

ちなみにVLOOKUPの計算方法の場合で考えてみると…

基準となる値に対して、それと同じ値が選択した範囲内の何列目にあるか指定して…と。そんな細かく指定できない。と分かると思います。あと合計も出せないですよね。
又、VLOOKUP関数は検索してきた一番上の値を表示してしまうため。

※VLOOKUPを知らない方はスルーで大丈夫です。

ちなみにSUMPRODUCT関数は設定した条件であれば合計して計算が可能です。
※下記にて説明。

上の表は例なので、実際のビジネスの場合だと下記のような時に使用します。
※あくまで一例です。しっかりと理解すれば使いどころはたくさんあります。

上のデータから下の表に関数で入力しています。

各販売店名に対して、売上金額と返品金額、さらにエリアを合算して計算しています。
こんなことができることをなんとなく分かる程度で大丈夫です。

何ができるのか？結論。『タテ』と『ヨコ』から条件に合った値を探し出すことができる。

上記の表だと複雑に感じると思いますので冒頭に出てきた表を１つづつ読み解いていきましょう。

問①の村人のAMの3日目の合計数の求め方。

上記の問いに対し、関数は一度忘れて自分で数えてみると分かり易いです。

恐らく目視で確認した場所は

『村人』があるターゲットの行
『AM』がある時間の行
『3日目』がある日数の列

最後に数字が書いてある範囲を見たかと思います。

ニャーコラ

赤枠の3か所から絞り、青枠の数字から足し引きして計算したよね！

簡単に言えば、SUMPRODUCT関数も見た流れと同じようにして計算しています。

実際に関数式を色分けしてみました。

それぞれの（　　）の中身をみると指定した範囲から対象となるものを＝で絞り込んでますよね。

では1個づつ（　　）の中がどうなっているのか見てみましょう。

初めの（　　）の説明

まず（A2:A8=”村人”）とありますが、これはA2～A8までの行の範囲内で『村人』になるものを対象に絞ったという意味。下記の表の赤枠部分を指定してます。

2つ目の（　　）の説明

次に（C2:C8=”AM”）とありますが、これはC2～C8までの行の範囲内で『AM』になるものを対象に絞ったという意味。下記の表の赤枠部分を指定してます。

3つ目の（　　）の説明

次は（D1:H1=”3日目”）とありますが、これはD1～H1までの列の範囲内で『3日目』になるものを対象に絞ったという意味。下記の表の赤枠部分を指定してます。

最後の（　　）の説明

最後は単純です。（D2:H8）とありますが、これはD2～H8までの範囲から探してねという意味。下記の表の赤枠部分を指定してます。

これで全ての（　　）の意味が理解できたかと思います。
では最後にその他の（　　）の意味や＊の説明になります。

下記がこれまで説明した以外の（　　）と＊部分です。

①＝SUMPRODUCT　は数式の冒頭部分なので他の簡単な関数と同じです。
②続いて（　　）の意味は、まずSUMPRODUCT関数で探したいものを全て囲うためです。
③＊の意味は『〇〇』and『△△』のように『and』の意味です。＊で全てをつなげます

調べたい値がそれぞれの行や列から範囲を選び、指定する。最後にどこから探しだすのかを加える。
ただ、それだけのことです。

じっくり見ると意外と単純な作りになっています。
恐らく最初に見た時のややこしそう。からはマシになったとおもいます。

ステップ②　実務編の前に知ってた方がいいExcel知識

基礎編の説明も終わったし、実践編にいきたいところですが
これを知っておかなければ実践に活かしにくいので先に説明します。

又、知っていると仕事でExcelでデータ分析をされる方など役に立つと思います。
※ご存じの方は飛ばして大丈夫です。

①絶対参照と相対参照、＄マークで切り替え
②””←ダブルクォーテーションの使い方

以上の２つです。

①絶対参照・相対参照・複合参照とは？違いや設定方法。

Excelで計算する際にこんなことないでしょうか？

ちなみに、下記のようなものを相対参照と呼びます。
ネットで調べると難しいことが書いてたりしますが、簡単にいうと両方のセルが同じようにズレることです。

利益額を求めるために、売上金額×利益率の式が入ってます。相対参照の場合下記のようにズレますよね？

C5のセルを計算するには
C5=C1×B5が正しい式だが…
相対参照で計算した682セルを基準で
下にコピペするとズレていく。

C7のセルを計算するには
C7=C1×B7が正しい式だが…
同じくズレていき、ここまでいくと全然違う数値に…

正しく数式を入れると下記のようになります。

上記の正しい式をそれぞれ見ると赤字部分のC1は全て一緒。つまりC1は固定すればいいのです。
固定の方法は『行』『列』の前に＄を付けること。

＄C＄1とすることで固定されます。やり方は自分で＄を入力してもいいですが
手入力はめんどうなので、固定したい部分を選択しF4のキーを押すと表示が変わります。
※パソコンのにより、F4キー＋fn（ファンクションキー）の同時押しの場合もあります。

この＄を行と列の前に付け、固定することを絶対参照といいます。

相対参照だとC1ではなく下にズレてC2になっていましたが、しっかりと固定されています。

では複合参照とはどういったものか？（２パターンあります）

先程の例に利益率を追加しました。
先程の計算式をコピペすると下記のような数式になります。

利益率30％を計算するためにコピペすると
C1セルは絶対参照で固定さてれおり、
B4が正しいのに横にズレてます。

『D4のセル』の利益率30％を求める正しい数式は

=D$1*$B4　となります。

絶対参照と違うところは、『行』か『列』のどちらかにしか＄が入っていません。

例えばA1のセルがあります。

相対参照の場合は『A1』でそのまま。
絶対参照の場合は『＄A＄1』になりますよね。

複合参照の場合になると『＄A1』又は『A$1』の2パターンになります。
それぞれどういった意味なのか？

簡単にいうと『行』か『列』どちらかの前に＄が入ると、＄の後のものが固定されます。

つまり

＄A1の場合→Aの前に＄が入っているので、『列』が固定される

A$1の場合→1の前に＄が入っているので、『行』が固定される

先程の表で考えてみると

=D$1*$B4になりますので『D＄１→1(行)は固定』『＄B4→B(列)は固定』となります。

C$1とはA.B.C…と横にはズレるけど
1,2,3行目と縦にはズレないということ。
$B4とはA.B.C…と横にはズレないけど
1,2,3行目と縦にはズレるということ。

1つ前のC＄1は横にズレる→D$1と
C→Dにズレている。
SB4は横にはズレないからそのまま。

1つ目、2つ目にあった$B4は
横にはズレないけど縦にはズレる。
なので下にズレた場合$B5となる。

慣れるまでは少しややこしいが、行・列のどちらかを固定したい場合は複合参照を使用することで解消

・固定したい『行』か『列』の前に＄が付くと前に付いた方が固定される。
・固定したい部分を選択してF4キーを押していくと切り替わっていく
（A1 → $A$1 → A$1 → $A1 → A1）とループする。

②””←ダブルクォーテーションとは何か？

条件を設定する際に文字列を使用する場合は””ダブルクォーテーションが必要になる。

例えば下記の数式はA2～A8の中から”村人”となるものだけを選択したい。と条件設定しています。
””を使用しなくてもいい条件設定の場合は数値の時だけです。

例：A2:A8=2　→A2～A8の中から2だけを選択したいという意味。この場合2は数値なので””は不要

ニャーコラ

今回は関係ないけど、IF関数やIFERROR関数とかの条件設定にも使用するよね！

ステップ③実務で使う場合の例

下記のシートがあるとします。そのシート内容から右のまとめの表に関数で入れるにはどうしますか？（分かりやすくするために数行しか入れていませんが、実務の際は数千・数万行とあると思います）

2021年のエリアや売上金額・返品金額、月毎の実績などを右の表に
条件を設定して入力するにはどうするか？

入力するだけじゃなく、横や下に飛んでもズレが生じないか？

ステップ①・②での内容が全て詰まってます。

まとめのシート（Ｄ４のセル）に581と入れるにはどこを参照しないといけないか？

めちゃくちゃ簡単に説明すると
答えは『福岡』・『売上金額』・『2021年5月』この3つの項目を掛け合わせていき計算をします。

ステップ①で説明したように

逆に考えるとわかりやすいかもしれません。
Ｄ４のセルはどんな実績？と聞くと福岡の2021年5月の売上金額ということは分かると思います。

この時、どこを見て考えたか想像してみてください。

『福岡』はエリアを見た。
『売上金額』は項目を見た。
『年月』は区分や5月のところを見たと思います。

簡単に説明するとこの見たところ同士を掛け合わせていき計算する。感じです。

実際の計算方法を1ヵ所づつ説明

まず初めにD4のセルの計算式を出してみます。これが答えです。

SUMPRODUCT関数に、絶対参照と複合参照を用いて計算しています。
ぱっと見、意味不明かもしれませんがステップ①・②を理解できていると1つ1つ見れば分かります。

関数の式に対して、何がどれかを分けると下記のような感じです。

※””で気が付いた方へ

ステップ②では””は条件を設定する際に文字列だと使用しないと記載しましたが、下記の例では『福岡』『売上金額』『2021年5月』など文字列なのに””を使用していないじゃないか。と思われた方もいるかもしれません。
下記例では『文字列』では条件設定しておらず、セルの場所（A4、B4など）で設定しており、
どちらのシートにも同じ名称で入っている為不要としています。
例えば②まとめ表の『福岡』が博多になっていた場合は範囲を選択した後に”博多”とする必要があります。
その場合数式はこのようになります。
(‘2021年’!$B$3:$B$14=$A4)これが×　　(‘2021年’!$B$3:$B$14=”博多”)が○　になります。

※何のことかさっぱりわからない方はとりあえずスルーしてください。