検索
連載

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.

ページトップに戻る