【VBA】実践!中小企業のExcelデータをVBAで前処理する【前処理】

中小企業がVBAを活用できるようになれば、効率的なデータ処理やタスク自動化が可能になり、業務効率が向上します。

また、繰り返し作業の削減やエラーの軽減により、生産性が向上し、人的コストの節約が期待できます。

この記事では、工事台帳データを例として、VBAを用いたデータの前処理方法を解説していきます。

【Power Query】ExcelデータをVBAで前処理する際の基本知識【Excel】

なぜVBAが必要なのか?

中小企業のデータではVBAが登場することはほとんどなく、手作業でグラフ化し、それをPDFもしくはPowerPointで共有しているはずです。

人員に余裕があれば良いのですが、この作業自体が面倒になり、共有作業も途中で頓挫してしまうことも多いのではないでしょうか?

面倒な作業であっても、VBAを味方にしてしまえばあっという間に解決してしまいます。

ノーコードだと詰まるところが必ず出てくる。VBAなら簡単に解決できる場合が多い

例えば、このような工事台帳を自社で管理しているとしましょう。

アイピア 工事台帳とは?【無料DLあり】エクセルテンプレートとおすすめ3選を紹介

日々の工事の経費を合計した実績と予算金額との時系列グラフを作成し、それを社員の方々に共有しようと考えたとします。

数十、数百ある工事毎の成績を1つ1つずつ作成していくのも手ですが、全ての時系列グラフを完成させるまでに膨大な時間が必要になります。

ノーコードでの処理に多忙な時間がかかりそうな時こそVBAで処理すべき合図になります。

Dictionaryオブジェクト

辞書型の基本もここで押さえておきましょう。

配列と違ってkey値を使ってデータを入れる方法です。

基本の形は以下のようになります。

【Power Query】VBAの変数・条件分岐・繰り返し処理を使いこなそう【Excel】

dictionaryオブジェクトと繰り返し処理を組み合わせる

大量のデータを前処理するには繰り返し処理との組み合わせが必須です。

データをVBAで前処理する準備


ここでは工事台帳の例を用いていきます。

最終的な目標は、登録された見積金額(請負金額)から、日々入力されていく工事原価の総計を算出し、そこから最終的な利益をグラフ化することです。

では見ていきましょう。

リストボックスで工事のリストボックスを作成

リボンの開発→挿入→リストボックスを選択し、適当な位置にリストボックスを作成します。

リストボックスの書式設定から、「入力範囲」と「リンクするセル」を設定

リストボックスを右クリックし、入力範囲とリンクするセルを指定していきます。

新規に「原価グラフ」のシートを作り、リストを選択したときの表示先を用意

「入力範囲」には工事名をテーブル化されているデータを指定します。

この場合だと工事1から工事60までの工事名です。

「リンクするセル」の部分は、新規シートでリストボックスを選択したときに出力される先を用意して指定します。

新規シートの工事名には、INDEX関数を用いて工事Noに対応した工事名が自動で出力されるようにしておきます。

VBAで処理するデータ「原価グラフデータ」のテーブルを作成

登録された見積金額(請負金額)から、日々入力されていく工事原価の総計を算出し、そこから最終的な利益をグラフ化することが目標なので、日付、予算、実績のテーブルを用意し、VBAで処理したデータの受け入れ先を用意しておきましょう。

【関数を使ったデータの前処理】VLOOKUPの使い方【Excel】

実践!工事台帳データをVBAで前処理してみよう


では、実際に工事台帳データをVBAで前処理してみます。

必要なテーブルのrangeや工事名を取得

【Power Query】ExcelデータをVBAで前処理する際の基本知識【Excel】

rangeを用いてテーブルデータを取得します。

日割の予算値を計算

【関数を使ったデータの前処理】VLOOKUPの使い方【Excel】

工事毎の経費の実績データの取得 ※繰り返し処理を使いこなす

【Power Query】プロシージャを理解しよう【Excel】

さあ、ここからは初学者が苦手な繰り返し処理の登場です。

ポイント

大量のデータを扱う際は、配列と繰り返し処理を組み合わせる

最初は慣れないですが、少しずつ習得していきましょう。

ここまで前処理ができたら、これらのデータをグラフ化していきます。

まとめ

この記事では、工事台帳データを例として、VBAを用いたデータの前処理方法を解説しました。

前処理に必要な基本知識は、セルからデータを取得する事とセルからデータを入力する事で、わかってしまえば意外にも簡単です。

あとは、具体例で慣れていけばどんどんデータを見える化→機械学習へ応用できます。

社内のデータをどんどん見える化し、独自のDXを加速させていきましょう。

【Power Query】VBAの変数・条件分岐・繰り返し処理を使いこなそう【Excel】