エクセル一覧表から、条件に合致するデータだけを抽出したいときがあります。
このような作業はフィルターを使えば、手作業でも可能です。
しかし抽出条件が複数だったり、データ件数や合計値を算出したりするとなると、マクロ化することで作業負担を軽減できます。
このページではエクセル一覧表から、複数条件に合致するデータのみを抽出して表示し、合計や件数を自動計算するマクロを作っていきます。
・条件に合致するデータの件数や合計値を算出
・VBAプログラム入りのエクセルをダウンロード可能
またページ下部でこのページで紹介しているVBAプログラムをダウンロードすることもできますので、ぜひお仕事にご活用ください。
目次
- 1 条件合致するデータを自動抽出するマクロの概要
- 2 VBA入りのエクセルファイルをダウンロード
- 3 VBA作成前の事前準備
- 4 VBAのプログラムソース解説
- 4.1 プログラム0｜変数宣言の指定
- 4.2 プログラム1｜プログラム開始
- 4.3 プログラム2｜シート設定
- 4.4 プログラム3｜各シートの最終行を取得
- 4.5 プログラム4｜データをリセット
- 4.6 プログラム5｜開始日と終了日を取得
- 4.7 プログラム6｜取引先を取得
- 4.8 プログラム7｜開始日、終了日、取引先が空欄か判定
- 4.9 プログラム8｜変数の初期化
- 4.10 プログラム9｜条件に合致した行を抽出
- 4.11 プログラム10｜条件に合致した行のデータのみを対象して分析
- 4.12 プログラム11｜プログラム9で条件に合致しなかった場合、ここへジャンプ
- 4.13 プログラム12｜合計値と件数を出力
- 4.14 プログラム13｜プログラム終了
- 5 類似のVBAプログラムの事例
- 6 Excel VBAについて詳しく理解したいなら
条件合致するデータを自動抽出するマクロの概要
このページでは、一覧表から条件に合致するデータ抽出し、計算する結果を出力するマクロを紹介します。
流れは以下のとおりです。
手順2. データ抽出用シートに必要情報を入力
手順3. VBAプログラム実行
以下で詳しく説明します。
手順1. エクセル一覧表を準備
B列：摘要
C列：納品日
D列：取引金額
E列：取引先
今回は上記のようなエクセル一覧表を想定して、データ抽出マクロを作成します。
エクセル一覧表は「Sheet1」としています。
手順2. データ抽出用シートに必要情報を入力
今回は上記のようなデータ抽出用シートを準備しました。このシートは「ExtractData」としています。
3つの部分について以下で説明します。
2. 表示用：条件に合致した合計値と件数を出力
3. 表示用：条件に合致したデータを表示
1. 事前入力：条件入力用セル
・セルB3：集計終了日
・セルB4：取引先
上記の3つの条件を事前に入力します。
ここでは、「セルB2：集計開始日=2021/7/1」、「セルB3：集計終了日=2021/7/31」、「セルB4：取引先=愛知販売」としています。
この条件に合致するデータを抽出します。
未入力の場合
この3つの条件が未入力の場合、未入力の条件は無視してマクロが動作します。
・集計開始日：未入力
・集計終了日：未入力
・取引先：愛知販売
●事例2：2021/7/1~2021/7/31に該当するデータを全て表示
・集計開始日：2021/7/1
・集計終了日：2021/7/31
・取引先：未入力
上記のように未入力の条件は無視して、入力された条件をもとにデータ抽出を行います。
2. 表示用：条件に合致した合計値と件数を出力
・セルB7：取引件数
手順1で示したデータ一覧表のなかで、条件に合致するデータを抽出し、取引金額合計値をB6に出力します。
また取引件数をB7に出力します。
3. 表示用：条件に合致したデータを表示
手順1で示したデータ一覧表のなかで、条件に合致する10行目からデータ表示します。
手順3. VBAプログラム実行
VBAを実行すると、以下のように条件に合致したデータを表示します。
ボタンでマクロを実行するため、フィルターなどの操作なしで必要な情報を確認できます。
条件を変更してVBAを再度実行
条件を変更して再度VBAプログラムを実行してみます。
そうすると、以下のように再度データ抽出した結果が表示されます。
このとき、データを手作業で削除する必要はありません。
なぜならVBAプログラムで表示情報は上書きされるようにしているからです。
これにより作業負担を軽減できます。
VBAプログラムの詳細は後半部分で解説しています。
ボタンにVBAプログラムを登録
このページではボタンを設定していませんが、VBAプログラムをボタンに登録することもできます。
ボタンにVBAプログラムを登録することで、ボタンを押下しプログラムを実行することができます。
ボタンをVBAプログラムを設定したい場合は、以下で動画も交えて設定方法を紹介しているので、そちらをご覧ください。
VBA入りのエクセルファイルをダウンロード
以下で紹介しているVBAプログラムをそのまま使いたい人は、以下のフォームからダウンロードできます。
登録したメールアドレスへ「VBAプログラムが含まれたエクセル」を送信します。
本プログラムの内容をそのまま使用可能です。ぜひお仕事にお役立てください。
それでは、以下でプログラムについて詳細を説明します。
VBA作成前の事前準備
ExcelでVBAを使うためにはいくつかの準備が必要です。
具体的には以下です。
上記の関して、以下で説明します。
1. ExcelでVBAを使うための事前準備
Excelで、以下の2つの準備をします。
2. 開発タブを追加
保存ファイルの拡張子変更、Excelの基本設定変更の2つです。
2つともで難しい作業はなく、それぞれ1分もあれば設定変更可能です。
上記に関しては、以下の記事で解説をしています。
もしVBAを使うための準備段階に不安がある人は上記をご覧ください。
この内容は以下の動画で紹介しています。
入門エクセルマクロの使い方｜マクロ作成から実行までを徹底解説
文字や画像だけで分かりづらい人は上記の動画をご覧ください。
VBAのプログラムソース解説
今回紹介するプログラムの概要は以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub ExtractData() 'プログラム2｜シート設定 Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("ExtractedData") 'プログラム3｜各シートの最終行を取得 Dim cmax1 As Long, cmax2 As Long cmax1 = ws1.Range("A65536").End(xlUp).Row cmax2 = ws2.Range("A65536").End(xlUp).Row 'プログラム4｜データをリセット ws2.Range("B6:B7").ClearContents If Not cmax2 = 9 Then: ws2.Range("A10:E" & cmax2).ClearContents 'プログラム5｜開始日と終了日を取得 Dim startdate As Date, enddate As Date startdate = ws2.Range("B2").Value enddate = ws2.Range("B3").Value 'プログラム6｜取引先を取得 Dim torihiki As String torihiki = ws2.Range("B4").Value 'プログラム7｜開始日、終了日、取引先が空欄か判定 Dim flag(2) As Boolean ' BooleanのDefault値はFalse If startdate = 0 Then: flag(0) = True If enddate = 0 Then: flag(1) = True If torihiki = "" Then: flag(2) = True 'プログラム8｜変数の初期化 Dim n As Long: n = 10 Dim goukei As Long: goukei = 0 Dim kensu As Long: kensu = 0 'プログラム9｜条件に合致した行を抽出 Dim i As Long For i = 2 To cmax1 If flag(0) = False Then If ws1.Range("C" & i).Value < startdate Then: GoTo Continue End If If flag(1) = False Then If ws1.Range("C" & i).Value >= enddate Then: GoTo Continue End If If flag(2) = False Then If ws1.Range("E" & i) <> torihiki Then: GoTo Continue End If 'プログラム10｜条件に合致した行のデータのみを対象して分析 ws2.Range("A" & n & ":E" & n).Value = ws1.Range("A" & i & ":E" & i).Value goukei = goukei + ws1.Range("D" & i).Value kensu = kensu + 1 n = n + 1 'プログラム11｜プログラム9で条件に合致しなかった場合、ここへジャンプ Continue: Next 'プログラム12｜合計値と件数を出力 ws2.Range("B6").Value = goukei ws2.Range("B7").Value = kensu 'プログラム13｜プログラム終了 End Sub |
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラム1｜プログラム開始
|
1 |
Sub ExtractData() |
VBAではプロシージャという単位でプログラムを呼び出します。
プロシージャの構文は下記となっています。
|
1 2 3 |
Sub プロシージャ名() '実行プログラム End Sub |
「Sub」で始まり「End Sub」で終わります。
プロシージャに関連するmoduleの話については以下で説明しています。
プログラム2｜シート設定
|
1 2 3 |
Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("ExtractedData") |
ws1とws2をワークシート型で変数設定します。
「Sheet1」シートをws1、「ExtractData」シートをws2として扱います。
プログラム3｜各シートの最終行を取得
|
1 2 3 |
Dim cmax1 As Long, cmax2 As Long cmax1 = ws1.Range("A65536").End(xlUp).Row cmax2 = ws2.Range("A65536").End(xlUp).Row |
ws1のA列の最終行をcmax1、ws2のA列の最終行をcmax2としてそれぞれ取得します。
Debug.Printの検証結果
|
1 2 3 4 5 6 7 8 |
Dim cmax1 As Long, cmax2 As Long cmax1 = ws1.Range("A65536").End(xlUp).Row cmax2 = ws2.Range("A65536").End(xlUp).Row Debug.Print "cmax1:" & cmax1 Debug.Print "cmax2:" & cmax2 >>>cmax1:75 >>>cmax2:9 |
プログラム4｜データをリセット
|
1 2 |
ws2.Range("B6:B7").ClearContents If Not cmax2 = 9 Then: ws2.Range("A10:E" & cmax2).ClearContents |
このプログラムでデータをリセットします。
データをリセットする目的
このプログラムは条件変更して、プログラムを複数回実行することを想定しています。
そのため、ws2にデータが出力された状態でプログラムを実行することが考えられます。
そこでデータをリセットし、ws2のB6とB7のデータ、ws2の9行目より下のデータを削除するようにしています。
これで空白にした状態で新しいデータを出力していきます。
プログラム5｜開始日と終了日を取得
|
1 2 3 |
Dim startdate As Date, enddate As Date startdate = ws2.Range("B2").Value enddate = ws2.Range("B3").Value |
ws2のB2の日付をstartdate、B3の日付をenddateとして取得します。
Debug.Printの検証結果
|
1 2 3 4 5 6 7 8 9 |
Dim startdate As Date, enddate As Date startdate = ws2.Range("B2").Value enddate = ws2.Range("B3").Value Debug.Print "startdate:" & startdate Debug.Print "enddate:" & enddate >>>startdate:2021/07/01 >>>enddate:2021/07/31 |
上記のように日付を取得します。
セルB2とB3が未記入の場合0となる
startdateとenddateをDate型で変数定義しています。
Date型で定義した変数に、未入力のセルを設定すると「0」となります。
|
1 2 3 4 5 6 7 8 9 10 11 |
Dim startdate As Date, enddate As Date 'セルB2が未記入 startdate = ws2.Range("B2").Value 'セルB3が未記入 enddate = ws2.Range("B3").Value Debug.Print "startdate:" & startdate Debug.Print "enddate:" & enddate >>>startdate:0:00:00 >>>enddate:0:00:00 |
上記のように対象セルが未記入の場合、Date型変数は「0:00:00」=0となります。
プログラム6｜取引先を取得
|
1 2 |
Dim torihiki As String torihiki = ws2.Range("B4").Value |
セルB4の値をtorihikiとして取得します。
Debug.Printの検証結果
|
1 2 3 4 5 6 |
Dim torihiki As String torihiki = ws2.Range("B4").Value Debug.Print "torihiki:" & torihiki >>>torihiki:愛知販売 |
プログラム7｜開始日、終了日、取引先が空欄か判定
|
1 2 3 4 |
Dim flag(2) As Boolean ' BooleanのDefault値はFalse If startdate = 0 Then: flag(0) = True If enddate = 0 Then: flag(1) = True If torihiki = "" Then: flag(2) = True |
flag(2)を3つのBoolean要素を含む静的配列として設定します。
ここでは以下の2つのポイントを把握しておくと、理解が進みます。
ポイント1. Boolean型の性質として、初期値(Default)はFalseである
ポイント2. Date型は未入力の場合、0となる
ポイント1のとおり、Booleanの初期値がFalseのため、空欄のときはTrueに切り替えます。
ポイント2のとおり、startdateとenddateは日付型のため、空欄=0として処理を行います。
空欄かどうかをTrue/Falseで判定
それぞれの変数に対して、入力値があるかどうかを以下のようにTrue/Falseで判定します。
– 入力あり：flag(0) = False
– 入力なし：flag(0) = True
●enddate(セルB3)
– 入力あり：flag(1) = False
– 入力なし：flag(1) = True
●torihiki(セルB4)
– 入力あり：flag(1) = False
– 入力なし：flag(1) = True
このページの事例では、全て入力されているため、以下のとおり、全てFalseとなります。
|
1 2 3 |
Debug.Print flag(0),flag(1),flag(2) >>>False, False, False |
配列については別ページで解説しています
ここでは静的配列を使いました。
以下のページで事例を使って配列について紹介していますので、興味がある人はご覧ください。
プログラム8｜変数の初期化
|
1 2 3 |
Dim n As Long: n = 10 Dim goukei As Long: goukei = 0 Dim kensu As Long: kensu = 0 |
n=10,goukei=0,kensu=0としてそれぞれの変数を初期化します。
それぞれの変数は以下のとおり使用します。
goukei = 0：ws2のB6に取引金額の合計値算出するための変数
kensu = 0：ws2のB6に取引件数を算出するための変数
上記とおり、変数を設定します。
プログラム9｜条件に合致した行を抽出
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Dim i As Long For i = 2 To cmax1 If flag(0) = False Then If ws1.Range("C" & i).Value < startdate Then: GoTo Continue End If If flag(1) = False Then If ws1.Range("C" & i).Value >= enddate Then: GoTo Continue End If If flag(2) = False Then If ws1.Range("E" & i) <> torihiki Then: GoTo Continue End If |
このプログラムでは、ws1の2行目~cmax1(75)行目まで各行を処理しながら、以下のような条件で対象行を除外しています。
1. 集計開始日:startdate
|
1 2 3 |
If flag(0) = False Then If ws1.Range("C" & i).Value < startdate Then: GoTo Continue End If |
集計開始日が入力されていて、ws1のC列が集計開始日(startdate)より前の日付であれば、Continue(プログラム11)へジャンプさせます。
ジャンプさせることで、プログラム10が実行されないため、条件合致した行を集計させないようにできます。
「集計開始日が未入力」もしくは「ws1のC列が集計開始日(startdate)を含めて後の日付」であれば、プログラム10で集計を行います。
2. 集計終了日:enddate
|
1 2 3 |
If flag(1) = False Then If ws1.Range("C" & i).Value >= enddate Then: GoTo Continue End If |
集計終了日が入力されていて、ws1のC列が集計終了日(enddate)を含めて後の日付であれば、Continue(プログラム11)へジャンプさせます。
ジャンプさせることで、プログラム10が実行されないため、条件合致した行を集計させないようにできます。
「集計開始日が未入力」もしくは「ws1のC列が集計終了日(enddate)より前の日付」であれば、プログラム10で集計を行います。
3. 取引先:torihiki
|
1 2 3 |
If flag(2) = False Then If ws1.Range("E" & i) <> torihiki Then: GoTo Continue End If |
取引先が入力されていて、ws1のE列が取引先と一致していない場合は、Continue(プログラム11)へジャンプさせます。
ジャンプさせることで、プログラム10が実行されないため、条件合致した行を集計させないようにできます。
「取引先が未入力」もしくは「ws1のE列が取引先(torihiki)と一致」であれば、プログラム10で集計を行います。
上記の3つの条件全てを満たしたデータだけが、プログラム10で集計対象となります。
事例で学ぶfornext構文
for next文については以下のページで事例を交えて説明しています。興味がある人はご覧ください。
事例で学ぶif文
if文については以下のページで事例を交えて説明しています。興味がある人はご覧ください。
プログラム10｜条件に合致した行のデータのみを対象して分析
|
1 2 3 4 |
ws2.Range("A" & n & ":E" & n).Value = ws1.Range("A" & i & ":E" & i).Value goukei = goukei + ws1.Range("D" & i).Value kensu = kensu + 1 n = n + 1 |
取引金額と取引件数を算出
|
1 |
ws2.Range("A" & n & ":E" & n).Value = ws1.Range("A" & i & ":E" & i).Value |
プログラム9で条件に合致したデータだけを表示します。
ws1で条件合致したA列からE列を、ws2の10行目から出力していきます。
取引金額と取引件数を算出
|
2 3 |
goukei = goukei + ws1.Range("D" & i).Value kensu = kensu + 1 |
goukeiでws1のD列(取引金額)を累算します。
kensuで取引件数を加算します。
行数をカウントアップ
|
4 |
n = n + 1 |
データを1行転記したら、n=n+1でデータを表示する行数をカウントアップします。
そうしないと、常にn=10のままになってしまい、10行目にしかデータが表示されなくなります。
カウントアップはよく忘れてしまうので、注意が必要です。
プログラム11｜プログラム9で条件に合致しなかった場合、ここへジャンプ
|
1 2 |
Continue: Next |
プログラム9で条件に合致しなかった場合のジャンプ先です。
プログラム9から、プログラム11にジャンプさせることで、プログラム10の算出対象から除外させることができます。
プログラム12｜合計値と件数を出力
|
1 2 |
ws2.Range("B6").Value = goukei ws2.Range("B7").Value = kensu |
セルB6にgoukeiを、セルB7をkensuを出力します。
Debug.Printの検証結果
|
1 2 3 4 5 6 7 |
ws2.Range("B6").Value = goukei ws2.Range("B7").Value = kensu Debug.Print "goukei:" & goukei Debug.Print "kensu:" & kensu >>>goukei:836800 >>>kensu:9 |
プログラム13｜プログラム終了
|
1 |
End Sub |
プログラム1と対になるプログラムで、プログラム終了させるための記述です。
「End Sub」を読み込むと、プログラムが終了します。
プログラムの解説はここまでです。
類似のVBAプログラムの事例
ここではデータ抽出マクロを紹介していますが、このサイトではデータを取り扱うVBAプログラムを紹介しています。
VBAのデータ集計
1. Excelマクロで月末処理のデータ集計
2. Excelマクロで月別データ集計
3. Excelマクロで週別データ集計
4. Excelマクロで期別(Q毎)にデータ集計
5. Excelマクロで日別にデータ集計
VBAのデータ転記・蓄積
エクセルマクロVBAのデータ転記の事例を紹介しています。
シート別、ブック別、蓄積などの実例を使ってプログラムを解説しています。
VBAの特定の文字列を含むセルや行の処理
エクセルマクロVBAで特定文字列を含むセル・行・列に対する処理を行うプログラムを紹介しています。
セルや行の色付け、選択、行削除から別シートへの抽出も解説しています。
参考にしてみてください。
Excel VBAについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。
上記のリンクでは、VBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。