連載

2014年7月23日

Excelで検索条件に一致するセルの個数と数値の合計を求める：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、検索条件に一致するセルの個数と数値の合計を求める方法について。オートフィルターを使うと元のデータが表示されず絞り込んだデータが表示されるため、データを見比べるときに不便な場合もあるので別の方法を紹介します。

[薬師寺国安，PROJECT KySS] PC用表示関連情報

LINE

Hatena

連載目次

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

　今回は「条件に一致するセルの個数の取得」と「条件に一致するセルの数値の合計」の2つのTipsを紹介する。このような処理は、どのような会社においても、日常的に行われているのではないだろうか。

　もちろんExcelのメニューからでも、必要とするデータの件数を求めたり、条件に一致する「数値」の合計を求めたりすることはできる。しかし、毎日行う作業をいちいちExcelのメニューから行っているのでは、非効率的ではなかろうか。Excelでデータを管理している以上、毎日行う作業はマクロ化しておき、ボタンクリック一つで処理を実行できるようにしておくのが、ベストな方法であるといえよう。

　また条件に一致するデータ件数の取得や、検索条件に一致する数値の合計は、「オートフィルター」のTips「Excelのオートフィルターで絞り込んだデータの件数と合計を関数で求める」でも紹介していたが、オートフィルターを使うと、元のデータが表示されず絞り込んだデータが表示されるため、データを見比べるときに不便な場合もあるだろう。

　そこで今回はオートフィルターは使わずに、セルの中のデータと指定したデータが一致する個数を求めたり、一致する数値の合計を求めたりする。このTipsも「オートフィルター」を使う場合と同じように、実用性に富んだものであると思う。

Valueプロパティで検索条件に一致するセルの個数を取得

　図1のように、「都道府県」と「氏名」と「金額」の入力されたデータがある。「都道府県」を入力するセルと「実行」ボタンを用意し、「都道府県」を入力した後「実行」ボタンをクリックすると、「条件に一致した件数」に「件数」が表示されるようにしてみよう。

図1　「都道府県」と「氏名」と「金額」の入力されたデータがある。「実行」ボタンを配置し、「条件に一致した件数」を表示するセルを用意している

　条件に一致する個数を求めるにはValueプロパティを使用する

Valueプロパティの書式

｛オブジェクト｝.Value=｛値｝

　｛オブジェクト｝にはRangeオブジェクトを指定する。｛値｝には件数などを指定する。

　図1で、「都道府県」を入力して「実行」ボタンをクリックして「条件に一致した件数」を表示するコードはリスト1になる。VBE（Visual Basic Editor）を起動して、Module1内にリスト1のコードを記述する。

Option Explicit
Sub 指定した都道府県の個数を取得()
  Dim i As Long
  Dim 件数 As Long
  If Range("C2").Value = "" Then
    MsgBox "都道府県を入力してください。"
    Exit Sub
  Else
    For i = 5 To 20
      If Cells(i, 2) = Range("C2").Value Then
        件数 = 件数 + 1
      End If
    Next
    Range("G4").Value = 件数 & "件"
  End If
End Sub

リスト1　「都道府県」の条件に一致するコード

　まず、Long型変数「i」と「件数」を宣言し、5行目で「都道府県」を入力するセルに何も入力されていなかった場合は、警告メッセージを発して処理を抜ける。それ以外の場合は9行目以下の処理を行う。

　9～13行目では変数「i」で5から20まで繰り返し処理を行う。この「5から20まで」はデータの入力されている行番号になる。

　10～12行目で「i行目のB列」を指定し、その値が「都道府県」入力のセルに入力した値と同じならば、変数「件数」を1ずつ加算していく。

　14行目で「条件に一致した件数」のセルに、変数「件数」に代入されている値と、文字列「件」を連結して表示している。

　このマクロを「実行」ボタンに関連付け、実行した結果が図2だ。

図2　入力した「都道府県」と一致したデータの件数が表示された

Valueプロパティで検索条件に一致するセルの数値を合計する

　図2の「都道府県」の条件で取得した件数は「7件」ある。この取得した「7件」の「金額」の合計を求めてみよう。こういった処理は現場で日常的に用いられる処理ではないだろうか。

　図2に「条件に一致した金額の合計」セルを追加すると図3のようになる。

図3　「条件に一致した金額の合計」セルを用意した

　条件に一致するセルの数値を合計するには、「Valueプロパティで検索条件に一致するセルの個数を取得」と同じ書式を使用する。

　「都道府県」にデータを入力し、「実行」ボタンをクリックして、「金額」の合計を求めるコードは、次のリスト2を、前掲のリスト1に追加したものである。

Option Explicit
Sub 指定した都道府県の個数を取得()
～コード略～
  Dim 合計 As Long
  合計 = 0
  If Range("C2").Value = "" Then
～コード略～
  Else
    For i = 5 To 20
      If Cells(i, 2) = Range("C2").Value Then
        件数 = 件数 + 1
        合計 = 合計 + Cells(i, 4)
      End If
    Next
～コード略～
    Range("G5").Value = Format(合計, "#,###")
  End If
End Sub

リスト2　条件に合致した「金額」の合計を求めるコード

　4・5行目でLong型の変数「合計」を宣言し、0で初期化しておく。「都道府県」で検索した「金額」を12行目のように記述することで、「i」行目の「金額（D）」列の値を加算して、変数「合計」に格納していく。「D列」は「4列目」に当たるため、ここでは、「Cells(i,4)」と指定している。

　最後に16行目で「条件に一致した金額の合計」のセルにFormat関数を使い、3桁区切りで「合計」の値を表示する。

Format関数の書式

Format(｛データ｝,｛書式｝)

　｛データ｝には｛書式｝を適用する値を指定する。｛書式｝には、書式指定文字列を指定する。「書式指定文字列」については「Strings.Format メソッド (Microsoft.VisualBasic) - MSDN」を参照してほしい。

　「実行」ボタンには既にマクロが関連付けられているので、関連付け直す必要はない。実行すると、図4のようになる。

図4　「都道府県」に一致する「件数」と、その一致した「金額」の合計が表示された

著者プロフィール

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

薬師寺国安事務所代表。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）。