週刊/税理士ジョーの銀行融資マガジン 購読受付中

イケてるSUM系関数「SUMIF・SUMIFS・SUMPRODUCT」を覚えよう

SUM系関数

Excelでよく使う関数と言えば…?

そうです、SUM関数。そんなSUM関数の応用といえる、SUM系関数「SUMIF・SUMIFS・SUMPRODUCT」の使い方をお話します。

目次

「SUMIF・SUMIFS・SUMPRODUCT」にできること

Excelの関数ではメジャーの地位を得ている「SUM」関数。SUMの役割は、範囲内のセルについて合計値を計算することですよね。

では、そんなSUMの応用系関数として、「SUMIF」「SUMIFS」「SUMPRODUCT」についてはどうでしょう。役割、使い方はわかりますか?

カンタンにまとめると、次のとおりです ↓

SUM系応用関数
  1. SUMIF
    役割……範囲内のセルのうち「単一」の条件に一致するセルの合計値を計算する
    使い方…=SUMIF(範囲,条件,合計範囲)
  2. SUMIFS
    役割……範囲内のセルのうち「複数」の条件に一致するセルの合計値を計算する
    使い方…=SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2,…)
  3. SUMPRODUCT
    役割……範囲(配列)内の対応する要素間の積を合計する
    使い方…=SUMPRODUCT(配列1,配列2,・・・)

ということで。このあと、上記3つの関数について詳しく見ていくことにします。

 

おさらい的に、まずはSUMから

SUM系の「応用」に行く前に、まずはサクッと「SUM」そのものをおさらいしておきましょう。サクッと一瞬で終わります。

下記のサンプルExcelデータについて、F列の「金額」合計を出したい。このようなときには、「SUM」関数です ↓

image1

上図のとおり、合計値を挿入したいセル「F8」に「=SUM(F2:F7)」と入力します。

実際には、入力するのはメンドーですからショートカットキーを使いましょう。

「F8」を選択した状態で、「Shiftキー」+「Altキー」+「=」。これで、上図のように、算式が自動入力されますので、「Enterキー」で確定して終了です。

これでF列の合計値 66,500円が表示されます。カンタンでしたね。

SUMのまとめ

役割……範囲内のセルについて合計値を計算する
使い方…=SUM(範囲)

 

SUMIF

それではいよいよ、SUMの応用編です。まずひとつめは、「SUMIF」から。

さきほどの「SUM」の説明と同じExcelデータを使います。

データ内の「勘定科目」ごとの金額合計が知りたいな、そんなときは「SUMIF」関数です。たとえば、「旅費交通費」についての合計金額を知りたいとすると… ↓

image3

上図の赤枠部分「4,500」を集計するための手順は次のとおりです ↓

image2

  1. B10セルに、金額を集計したい勘定科目「旅費交通費」を入力
  2. F10セルを選択して、「=su」と入力。関数の候補がドロップダウンで表示される
  3. 「↓」で「SUMIF」まで移動。「Tabキー」を押す。

続いて、下図の手順で「SUMIF」関数を入力していきます ↓

image5

  1. B2セルを選択。「Ctrlキー」+「Shiftキー」+「↓」で、B7セルまで選択(マウス操作で選択してもOK)。「,(カンマ)」を入力
  2. B10セルを選択。「,」を入力
  3. F2セルを選択。「Ctrlキー」+「Shiftキー」+「↓」で、F7セルまで選択。「)」を入力
  4. F10セルに「=SUMIF(B2:B7,B10,F2:F7)」が入力されていることを確認。「Enterキー」を押す

以上で、F10セルに旅費交通費の金額が集計されます。つまり、「SUMIF」関数の構造は次のとおりです ↓

image4

上記のとおり、「勘定科目」列の中から、条件である「旅費交通費」を探し出し、それに対応する金額を「金額」列から抜き出して集計した、ということになります。

データ数が少ない場合には、該当するセルを手作業で足し算する方法もありますが。データ数が多くなった場合には手間もかかるし、間違いも起きやすくなります。

そこは「SUMIF」関数を活用しましょう。

SUMIFのまとめ

役割……範囲内のセルのうち「単一」の条件に一致するセルの合計値を計算する
使い方…=SUMIF(範囲,条件,合計範囲)

 

SUMIFS

続いて「SUMIFS」について。この「SUMIFS」は、さきほどの「SUMIF」の拡張版です。

どういうことかというと、「SUMIF」で指定できる条件が一つだったのに対し、複数の条件を指定できるのが「SUMIFS」です。

サンプルのExcelデータを使って見ていきましょう。データ内の「旅費交通費」のうち、さらに「タクシー」だけの金額合計が知りたいな。そんなときは「SUMIFS」関数です ↓

image6

上図の赤枠部分「4,000」を集計するための手順は次のとおりです ↓

image7

  1. B10セルに、金額を集計したい勘定科目「旅費交通費」を入力。C10セルに、金額を集計したい摘要1「タクシー」を入力
  2. F10セルを選択して、「=su」と入力。関数の候補がドロップダウンで表示される
  3. 「↓」で「SUMIFS」まで移動。「Tabキー」を押す。

続いて、下図の手順で「SUMIFS」関数を入力していきます ↓

image8

  1. F2セルを選択。「Ctrlキー」+「Shiftキー」+「↓」で、F7セルまで選択(マウス操作で選択してもOK)。「,(カンマ)」を入力
  2. B2セルを選択。「Ctrlキー」+「Shiftキー」+「↓」で、B7セルまで選択(マウス操作で選択してもOK)。「,(カンマ)」を入力
  3. B10セルを選択。「,」を入力
  4. C2セルを選択。「Ctrlキー」+「Shiftキー」+「↓」で、C7セルまで選択(マウス操作で選択してもOK)。「,(カンマ)」を入力
  5. C10セルを選択。「)」を入力
  6. F10セルに「=SUMIFS(F2:F7,B2:B7,B10,C2:C7,C10)」が入力されていることを確認。「Enterキー」を押す

以上で、F10セルに旅費交通費のうち、タクシーの金額が集計されます。つまり、「SUMIFS」関数の構造は次のとおりです ↓

image9

上記のとおり、「勘定科目」列の中から、1つめの条件である「旅費交通費」を探し出し。

さらに「摘要1」列の中から、2つめの条件である「タクシー」も満たす金額を「金額」列から抜き出して集計した、ということになります。

SUMIFSのまとめ

役割……範囲内のセルのうち「複数」の条件に一致するセルの合計値を計算する
使い方…=SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2,…)

 

SUMPRODUCT

さいごに「SUMPRODUCT」について。こちらは、さきほどまでの「SUMIF」「SUMIFS」とは趣きが異なります。

サンプルのExcelデータを使って見ていきましょう。金種ごと(1円玉が〇枚、5円玉が〇枚…)の金額の合計を求めるような場合に役立つのが、「SUMPRODUCT」関数です ↓

image10

上図から、11月16日に手元にある現金の合計である赤枠部分「141,911」を集計するための手順は次のとおりです ↓

image11

  1. L2セルを選択して、「=su」と入力。関数の候補がドロップダウンで表示される
  2. 「↓」で「SUMPRODUCT」まで移動。「Tabキー」を押す。

続いて、下図の手順で「SUMPRODUCT」関数を入力していきます ↓image12

  1. B1セルを選択。「Ctrlキー」+「Shiftキー」+「→」で、L1セルまで選択。「Shiftキー」+「←」でB1からK1を選択した状態に(マウス操作で選択してもOK)「,(カンマ)」を入力
  2. B2セルを選択。「Ctrlキー」+「Shiftキー」+「→」で、L2セルまで選択。「Shiftキー」+「←」でB2からK2を選択した状態に(マウス操作で選択してもOK)。「)」を入力
  3. L2セルに「=SUMPRODUCT(B1:K1,B2:K2)」が入力されていることを確認。「Enterキー」を押す

以上で、L2セルに金種ごと(1円玉が〇枚、5円玉が〇枚…)の金額の合計が計算されます。つまり、「SUMPRODUCT」関数の構造は次のとおりです ↓

image13

上記のとおり、金種(1円、5円、10円…)と金種ごとの数量(1円玉が〇枚、5円玉が〇枚、10円玉が〇枚…)の積(掛け算)の合計を計算した、ということになります。

SUMPRODUCTのまとめ

役割……範囲(配列)内の対応する要素間の積を合計する
使い方…=SUMPRODUCT(配列1,配列2,・・・)

 

まとめ

SUMの応用系関数として、「SUMIF」「SUMIFS」「SUMPRODUCT」についてお話をしてきました。

知っていると、手間をかけずに、ミスが少なく、集計や計算ができるのが関数のメリットです。

手間がかかるなぁ、ミスが多いなぁ、というときには、対応する関数がないかを調べてみましょう。

関数を調べる1度目は時間がかかりますが、関数を利用する2度目以降の作業は効率化されます。

 

************
  きょうの執筆後記
************

ブログには書けない・書きにくいことその他。きょうの「執筆後記」は毎日メルマガでお届け中です。

よろしければメルマガ(無料)をご登録ください! → 登録はこちらから

SUM系関数

この記事が気に入ったら
いいね または フォローしてね!

良い記事があればシェア
  • URLをコピーしました!
目次