検索
連載

Excelで数値の合計に役立つ3つのワークシート関数SUMIF、SUMIFS、SUMPRODUCTの使い方：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、引数で指定したセル範囲の数値を、条件付きで合計するSUMIF、同じく複数条件を使えるSUMIFS、配列内の積を計算し、それを合計するSUMPRODUCTについて、VBAでの使い方を解説。

Share
Tweet
LINE
Hatena
「VBA／マクロ便利Tips」のインデックス

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013


　今回もTips「VBAにおけるワークシート関数――INDEX、MATCH、VLOOKUPの使い方」に引き続きワークシート関数をVBAで使う方法を解説する。今回紹介するワークシート関数は「SUMIF」「SUMIFS」「SUMPRODUCT」の3つだ。

引数で指定したセル範囲の数値を、条件付きで合計するSUMIF関数

　「SUMIF」関数をVBAで使う書式は下記のようになる。

SUMIF関数をVBAで使う（WorksheetFunction.SumIfメソッドの）書式

WorksheetFunction.SumIf(範囲,条件)


　「範囲」には、データの入力されているセルの範囲を指定する。「条件」には、合計する際の条件を指定する。

　図1のように「指定金額」を入力するセルと、「売上月」「売上」を表示したセルと、「指定金額以上の金額の合計」ボタンを配置しておく。


図1

　各セルにはExcelのメニューから書式を設定している。各自が好きなデザインにしても構わない。

　「指定金額以上の金額の合計」を計算するコードはリスト1になる。

Sub 指定金額金額の合計()
  Dim 金額 As Double
  Dim 指定金額 As String
  指定金額 = Range("C1").Value
  If 指定金額 = "" Or IsNumeric(指定金額) = False Then
    MsgBox "指定金額が不正です。"
    Exit Sub
  End If
  金額 = WorksheetFunction.SumIf(Range("C3:C14"), ">" & 指定金額)
  MsgBox "売上が" & 指定金額 & "以上の月の合計金額は" & Format(金額, "#,###") & "です。"
End Sub
リスト1　「指定金額以上の金額の合計」を計算するコード

　Double型の変数「金額」、文字列型の変数「指定金額」を宣言する（2〜3行目）。変数「指定金額」には、「C1」のセルの値を格納する（4行目）。

　変数「指定金額（セルC1）」の値が未入力であったり、数値ではなかったりする場合は、警告メッセージを表示して処理を抜ける（5〜8行目）。

　変数「金額」にWorksheetFunction.SumIfメソッドの書式にのっとって、「範囲」に「売上」の入力されているセルの「C3:C14」を指定し、「条件」に、変数「指定金額」より大きいという条件を指定する（9行目）。

　条件に合致する金額の合計を、Format関数で、3桁区切りでメッセージボックスに表示する（10行目）。

　リスト1を「指定金額以上の金額の合計」ボタンに関連付け実行すると、図2のように表示される。


図2　「指定金額」に「4500000」を指定し、この金額以上の金額の合計を求めて表示した

引数で指定したセル範囲の数値を、複数の条件付きで合計するSUMIFS関数

　「SUMIFS」関数をVBAで使う書式は下記のようになる。

SUMIFS関数をVBAで使う（WorksheetFunction.SumIfsメソッドの）書式

WorksheetFunction.SumIfs(合計範囲,条件範囲1,条件1,[条件範囲2,条件2……])


　「範囲」には、データの入力されているセルの範囲を指定。「条件範囲1」には、対応する条件による評価の対象となる範囲を指定。「条件1」には、対象となる「条件範囲1」のセルを定義する条件を指定する。

　SumIfとSumIfs関数の違いは、「条件範囲」や「条件」がSumIfs関数では複数指定できる点にある。

　図3のように「支店番号」、「1月」「2月」の売上の入力されたセルと、「実行」ボタンを配置しておく。


図3

「SumIfs」関数を用いて、複数の条件付きで合計を求めるコードはリスト2になる。

Sub SumIfs関数の使い方()
  Dim 合計 As Double
  合計 = WorksheetFunction.SumIfs(Range("D3:D11"), Range("B3:B11"), "A001*", Range("C3:C11"), ">1800000")
  MsgBox ("支店番号がA001を含む1月の売上が180万以上の2月の売上の合計は" & Format(合計, "#,###") & "です。")
End Sub
リスト2　「SumIfs」関数を用いて、複数の条件付きで合計を求めるコード

　Double型の変数「合計」を宣言する（2行目）。

　3行目では、WorksheetFunction.SumIfsメソッドの書式にのっとって処理を記述する。まず、「合計範囲」に「2月」の「D3:D11」のセルを指定する。次に、「条件範囲1」に「支店番号」の「B3:B11」のセルを指定し、「条件1」に「支店番号」が「A001」で始まる「支店番号」を指定している。

　「A001*」と「アスタリスク（*）」を使って指定することで、「支店番号」が「A001」で始まる全ての「支店番号」を「条件1」に指定できる。次の「条件範囲2」には、「1月」の「C3:C11」のセルの範囲を指定し、「条件2」に「1800000」より大きい値を指定している。

　これで、「支店番号」に「A001」が含まれ、「1月」の売上が「1800000」以上ある、「2月」の売上の合計を求めて、Format関数で、3桁区切りでメッセージボックスに表示している。

　リスト2のコードを「実行」ボタンと関連付け実行すると、図4のように表示される。


図4　「支店番号」に「A001」が含まれ、「1月」の売上が「1800000」以上ある、「2月」の売上の合計が表示された

配列内の積を計算し、それを合計するSUMPRODUCT関数

　「SUMPRODUCT」関数をVBAで使う書式は下記のようになる。

SUMPRODUCT関数をVBAで使う（WorksheetFunction.SumProductメソッドの）書式

WorksheetFunction.SumProduct(配列1,配列2,配列3,……)


　引数の配列は2個から30個まで指定が可能。数値以外の配列要素は0とみなされる。

　図5のような、「商品名」「単価」「売れ数」を表示したセルと、「合計金額」を表示するセル、「実行」ボタンを配置しておく。セルの書式は各自が自由に決めてもらっていい。


図5　「商品名」と、「単価」、「売れ数」を表示したセルと、「合計金額」を表示するセル、「実行」ボタンを配置する

　配列内の積を計算し、その合計を求めるコードはリスト3になる。

Sub SumProduct関数の使い方()
  Dim 合計 As Double
  合計 = WorksheetFunction.SumProduct(Range("C3:C9").Value, Range("D3:D9").Value)
  Range("C11").Value = 合計
End Sub
リスト3　配列内の積を計算し、その合計を求めるコード

　Double型の変数、「合計」を宣言する（2行目）。

　3行目では、WorksheetFunction.SumProductの書式にのっとって処理を記述する。まず、「配列1」にセル「C3:C9」の範囲の「単価」セルの値を指定する。次に「配列2」に「D3:D9」の範囲の「売れ数」の値を指定して、これらの要素の積を計算して、合計を求める。合計はセル「C11」に表示する（4行目）。

　通常こういった場合の「合計金額」の求め方は、下記のように、「単価×売れ数」で「売上金額」を求め、最終的に「売上金額」を全て加算して「合計金額」を求める。

148980×20＝2979600
112830×21＝2369430
12800×58＝742400
11280×25＝282000
45600×33＝1504800
32800×18＝590400
212500×10＝2125000

　しかし、「SUMPRODUCT」関数を使用すると、この計算を一気にやってくれるのだ。この場合の積の計算と、合計は以下のような計算計算を一気に行い、「C11」には「10,593,630」と表示される（図6）。

148980×20+112830×21+12800×58+11280×25+45600×33+32800×18+212500×10

　このリスト3を「実行」ボタンと関連付け実行すると、図6のようになる。


図6　指定した配列の積を計算し、その合計が表示された

まとめ

　今回は、Worksheet関数の「SUMIF」「SUMIFS」「SUMPRODUCT」の3つの関数を紹介した。

　「SUMIF」「SUMIFS」関数は、条件を指定して、その条件に合致する数値の合計を求めることができるので、実際の現場では大いに力を発揮する関数ではないだろうか。このサンプルを参考に、ぜひ現場でも使ってもらいたい。

　最後の「SUMPRODUCT」関数の使い道は、あまりないかもしれないが、一気にセル内の「単価」と「売れ数」の「合計金額」を算出してくれる機能は、使い方によっては、非常に便利な関数ではないだろうか。

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms - Windows Platform Development (Oct 2014-Sep 2015）。


Copyright © ITmedia, Inc. All Rights Reserved.

ページトップに戻る