横浜国立大学理工学部建築都市環境系学科卒
一級鉄筋技能士
中小企業がVBAを活用できるようになれば、効率的なデータ処理やタスク自動化が可能になり、業務効率が向上します。
また、繰り返し作業の削減やエラーの軽減により、生産性が向上し、人的コストの節約が期待できます。
この記事では、工事台帳データを例として、VBAを用いたデータの前処理方法を解説していきます。
目次
なぜVBAが必要なのか？
中小企業のデータではVBAが登場することはほとんどなく、手作業でグラフ化し、それをPDFもしくはPowerPointで共有しているはずです。
人員に余裕があれば良いのですが、この作業自体が面倒になり、共有作業も途中で頓挫してしまうことも多いのではないでしょうか？
面倒な作業であっても、VBAを味方にしてしまえばあっという間に解決してしまいます。
ノーコードだと詰まるところが必ず出てくる。VBAなら簡単に解決できる場合が多い
例えば、このような工事台帳を自社で管理しているとしましょう。
アイピア 工事台帳とは？【無料DLあり】エクセルテンプレートとおすすめ3選を紹介
日々の工事の経費を合計した実績と予算金額との時系列グラフを作成し、それを社員の方々に共有しようと考えたとします。
数十、数百ある工事毎の成績を1つ1つずつ作成していくのも手ですが、全ての時系列グラフを完成させるまでに膨大な時間が必要になります。
ノーコードでの処理に多忙な時間がかかりそうな時こそVBAで処理すべき合図になります。
Dictionaryオブジェクト
辞書型の基本もここで押さえておきましょう。
配列と違ってkey値を使ってデータを入れる方法です。
基本の形は以下のようになります。
|
1 2 3 4 5 |
Dim 辞書名 As Object Set 辞書名 = CreateObject("scripting.dictionary") もしくは Dim 辞書名 As New scripting.dictionary |
dictionaryオブジェクトと繰り返し処理を組み合わせる
大量のデータを前処理するには繰り返し処理との組み合わせが必須です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub dict_test() Dim dictionary As Object Set datadict = CreateObject("scripting.dictionary") datadict("202310") = 100 datadict("202311") = 120 datadict("202312") = 110 Dim key As Variant'どのような型のデータでも格納OK For Each key In datadict'for each で繰り返し処理を行う Debug.Print (datadict(key)) Next key End Sub |
データをVBAで前処理する準備
ここでは工事台帳の例を用いていきます。
最終的な目標は、登録された見積金額(請負金額)から、日々入力されていく工事原価の総計を算出し、そこから最終的な利益をグラフ化することです。
では見ていきましょう。
リストボックスで工事のリストボックスを作成
リボンの開発→挿入→リストボックスを選択し、適当な位置にリストボックスを作成します。
リストボックスの書式設定から、「入力範囲」と「リンクするセル」を設定
リストボックスを右クリックし、入力範囲とリンクするセルを指定していきます。
新規に「原価グラフ」のシートを作り、リストを選択したときの表示先を用意
「入力範囲」には工事名をテーブル化されているデータを指定します。
この場合だと工事1から工事60までの工事名です。
「リンクするセル」の部分は、新規シートでリストボックスを選択したときに出力される先を用意して指定します。
新規シートの工事名には、INDEX関数を用いて工事Noに対応した工事名が自動で出力されるようにしておきます。
|
1 2 |
=INDEX(テーブル2[工事名],原価グラフ!F1) ※テーブル2とはデータセットの工事マスタのテーブル |
VBAで処理するデータ「原価グラフデータ」のテーブルを作成
登録された見積金額(請負金額)から、日々入力されていく工事原価の総計を算出し、そこから最終的な利益をグラフ化することが目標なので、日付、予算、実績のテーブルを用意し、VBAで処理したデータの受け入れ先を用意しておきましょう。
実践！工事台帳データをVBAで前処理してみよう
では、実際に工事台帳データをVBAで前処理してみます。
必要なテーブルのrangeや工事名を取得
rangeを用いてテーブルデータを取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
'原価グラフデータの生成 Sub createcostdata() ' step1【初期設定】 ' 1-1.テーブルの領域取得 Dim constructiontablerange As Range Set constructiontablerange = ThisWorkbook.Worksheets("データセット").Range("テーブル2") Dim costtablerange As Range Set costtablerange = ThisWorkbook.Worksheets("データセット").Range("テーブル3") Dim costgraphtablerange As Range Set costgraphtablerange = ThisWorkbook.Worksheets("原価グラフ").Range("原価グラフデータ") ' 1-2.対象の工事名取得 Dim koujiname As String koujiname = ThisWorkbook.Worksheets("原価グラフ").Range("F2").Value ' 1-3.以前のデータを消去 If costgraphtablerange.Rows.Count > 1 Then ThisWorkbook.Worksheets("原価グラフ").ListObjects("原価グラフデータ").DataBodyRange.Clear ThisWorkbook.Worksheets("原価グラフ").ListObjects("原価グラフデータ").Resize Range("A1:C2") End If End Sub |
日割の予算値を計算
|
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 |
'step2.【開始日、終了日の処理】 '2-1.日付データ取得 Dim d1 As Date Dim d2 As Date Dim diffdays As Integer On Error Resume Next d1 = WorksheetFunction.VLookup(koujiname, constructiontablerange, 5, False) '開始日 d2 = WorksheetFunction.VLookup(koujiname, constructiontablerange, 6, False) '終了日 On Error GoTo 0 If d1 = 0 Then MsgBox "開始日取得エラー" Exit Sub End If If d2 = 0 Then MsgBox "開始日取得エラー" Exit Sub End If '日付の差分を取得 diffdays = DateDiff("d", d1, d2) '2-2.工事日数から1日単位の予算値を計算 Dim diffvalue As Double Dim budgetcost As Double bugetcost = WorksheetFunction.VLookup(koujiname, constructiontablerange, 3, False) '予算値 diffvalue = budgetcost / diffdays |
工事毎の経費の実績データの取得 ※繰り返し処理を使いこなす
さあ、ここからは初学者が苦手な繰り返し処理の登場です。
大量のデータを扱う際は、配列と繰り返し処理を組み合わせる
最初は慣れないですが、少しずつ習得していきましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
'step3.【実績データの取得】 '配列を繰り返し処理と組み合わせる Dim i As Long Dim actualdic As Object '辞書オブジェクトの作成 Set actualdic = CreateObject("scripting.dictionary") '繰り返し処理 For i = 1 To costtablerange.Rows.Count ' 工事名が一致するデータのみを参照 If koujiname = costtablerange.Cells(i, 1).Value Then ' 日付：キー 金額：値 辞書型の "Scripting.Dictionary" オブジェクトに新規のキーと値を追加している actualdic(Str(costtablerange.Cells(i, 2).Value)) = costtablerange.Cells(i, 3).Value End If Next |
ここまで前処理ができたら、これらのデータをグラフ化していきます。
まとめ
この記事では、工事台帳データを例として、VBAを用いたデータの前処理方法を解説しました。
前処理に必要な基本知識は、セルからデータを取得する事とセルからデータを入力する事で、わかってしまえば意外にも簡単です。
あとは、具体例で慣れていけばどんどんデータを見える化→機械学習へ応用できます。
社内のデータをどんどん見える化し、独自のDXを加速させていきましょう。