日次、周次、月次で定型のExcelフォーマットを使い、データ集計をするといった事務作業はどこの会社でも一般的な業務です。
色々なシステムから出力したデータを切り貼りしながら手作業で「集計表」を作られているケースも多いかと思います。
そんな作業がボタンひとつで「自動化」できたらステキですよね。
こういったExcelの集計表の作成処理を自動化するのには一種のセオリー的なものがあります。
今回は集計表の作成処理を自動化させる際のポイントや実装例を紹介していきます。
自動化させるために必要な知識や技術
まずは処理を自動化させる際に必要となる知識や技術を紹介していきます。
Excel VBAの入門レベルの知識
Excel上の処理を自動化させるにあたって、やはりVBAの知識は必要になります。
ただ、高度な知識は必要ではなく、以下の基本的な処理が実装できれば大丈夫です。
- WorkSheetの初期化やシート名を基にした処理対象のシートの指定
- セルへの値の書き込みやセルの値の読み取り
- IF文やFor文などの基本的な制御構文
SQL及びADOの入門レベルの知識
集計表作成処理を自動化するにあたって、集計の行うための元となるデータを取得する処理を実装する際には、ADOのレコードセット操作に関する知識、及び、SQLの知識が必要になります。
ただ、こちらも高度な知識は不要です。
例えば高度なSQLの知識があれば一回のSQLの実行で、出力する表のフォーマットに合わせてデータを加工して取得することもできますが、簡単なSELECT文を複数回実行し、幾つかのデータ群に分けてデータを取得し、それを後からExcel上で成型すれば良いので、簡単なSQLが作れれば結構です。
※もしSQLが得意であれば、SQLのSELECT文側で最終的に出力したい集計表のレイアウトで成型までできると、Excel側でデータを加工する処理を大幅に減らせる為、実行速度の大幅な改善に繋がります。
Excelシートの表作成に関する中級レベルの知識
表作成に関する知識というのは、Excelシートのセルに対してSUM等の関数を設定し、計算式を適切に当てはめていくことを指します。
絶対参照と相対参照の使い分けができて、VLOOKUP関数やISERROR関数、SUM関数やIF関数などの集計表を作るうえでよく使用する一般的な関数が理解できていれば良いです。
Excelブックのシート構成例
集計表の作成処理を自動化するにあたって、自分の中で決まったシート構成パターンを作っておくと、どのように作成するか悩まなくてすみますし、実装手順も過去の制作物から流用することができるようになる為おすすめです。
個人的には以下のようなシート構成で自動処理を作成することが多いです。
シート１：条件設定及び処理実行用シート
当シートでは、データ集計を行うための条件を指定するシートとし、集計対象期間等のパラメーター設定用のセルを配置したり、集計処理実行ボタンを設置します。
シート２：データ用シート
上記のシート１で指定した条件を元に、データベースから大量のデータを取得して書き出すためのシートです。
データ取得時には毎回このシートを初期化してデータを書き込みます。
このシートは集計表から参照される用途なので、必ずしも集計表のフォーマットと合わせておく必要はありません。
ただ、集計表のフォーマットに近い形のデータだと、集計表内のセルに設定する計算式をシンプルなものにできる為、処理速度の向上につながる場合もあります。
後、必要によって、複数のデータ用シートを用意する場合もあります。
シート３：集計表シート
集計表を配置するシートです。
集計表の各セルに設定する計算式は自シートやデータシートを参照させます。
データシートを参照する計算式では、データシートの列数は固定のケースが多いですが、行数は可変なのが大半なので、そこを考慮した計算式を設定します。
セルに設定する計算式を工夫し、人が手でメンテナンスしなくても済む作りにしておきます。
集計表作成処理の流れ
上記のシート１に配置する処理実行ボタン押下時の処理実装例を紹介していきます。
- 入力された条件値の値チェック
- データベースへ接続しデータ取得
- データシートの初期化
- レコードセットのデータをデータシートに書込み
- 集計表シートに埋め込んだ計算式が自動的に値をセット
それでは、ここの項目を以下で説明していきます。
入力された条件値の値チェック
まずはシート１で条件を指定するセルに値がちゃんとあるか、不適切な値が入力されていないかなどのチェック処理をVBAで実装します。
対象のセルに値が無かったり、値が不正な場合はメッセージボックスでエラーを出して、適切な値を入力してもらえるようにユーザーに入力や訂正を促しつつ処理を終了します。
Dim CurrentSheet As Worksheet
Set CurrentSheet = Worksheets("メイン")
If CurrentSheet.Range("B3").Value = "" Then
MsgBox "集計条件を指定してください。"
Exit Sub
End If
If Not IsNumeric(CurrentSheet.Range("B3").Value) Then
MsgBox "集計条件は数値を指定してください。"
Exit Sub
End Ifデータベースへ接続しデータ取得
ADOでデータベースに接続し、SQLを発行、実行し、取得結果のデータをレコードセットに格納します。
もし、データが取れなければ、条件設定シートで指定した条件を見直す必要があるので、メッセージボックスを表示して、対象のデータが存在しないことを通知し処理を終了します。
この処理は当ブログで過去にADOの接続クラスを紹介しているので、それを使用すると実装が楽チンです。
是非活用してください。
データシートの初期化
レコードセットのデータを書き込む先のデータシートを初期化します。
尚、初期化処理はデータが取れて、データシートに書き込む直前が望ましいと思います。
シートの初期化処理は汎用的な処理なので、引数にシート名を設定して、呼び出されたら引数のシートを初期化するようなSubプロシージャとして用意しましょう。
'引数で指定されたシート名のシートを初期化します。
Sub TargetSheetInit(TargetSheetName As String)
Dim TargetSheet As Worksheet
Set TargetSheet = Worksheets(TargetSheetName)
TargetSheet.Cells.Clear
Set TargetSheet = Nothing
End Subレコードセットのデータをデータシートに書込み
レコードセットのデータをデータシートに書き込みます。
レコードセットの行数分ループして、必要によりレコードセットの値によって処理を分岐しつつ、1行ずつ処理をしていきます。
なるべく複雑処理をさせずに、当処理ではレコードセットのデータをただ書き出すだけにしておいたほうが実装が楽です。
集計表シートに埋め込んだ計算式が自動的に値をセット
集計用シートに、データシートを参照する適切な計算式が埋め込まれていれば、上記のレコードセットのデータをデータシートの書き込んだ時点で集計表シートにも値が入り、一連の処理が完了します。
よって、上記のデータシートへの書き込み処理が完了した時点で、完了通知をメッセージボックスで表示させましょう。
後、Excelの組み込み関数だけを使用してセルに計算式を設定する場合、記述が複雑になったり、やりたいことができない場合もあります。そういった場合は、VBAでFunctionプロシージャを自作し、自作関数を使用した計算式をセルに埋め込んてあげると、計算式の記述は簡素化される為おすすめです。
より良くするためのポイント・テクニック
当項では、自動化した処理をより良くするためのポイントやテクニックを紹介していきます。
Excelが「応答なし」状態を回避する
Excelで処理を自動化する場合、大量のデータをループ処理で扱うケースは多々あります。
その場合、Excelは操作を受け付けなくなり、Excelのウィンドウ上部に「応答なし」と表示されてアプリケーションがハングアップしたようになります。
「応答なし」状態が短時間であれば気にしなくても良いでしょうが、元データが大量だったり、集計表が巨大だったり、複数のシートに跨っていたりすると、処理時間が5分や10分など長時間に及ぶものも出てきます。
そんな時に「応答なし」と出ていると、ユーザーは処理が進んでいるのか不安になり、シートをクリックしたり、Excelを終了させようとするなどの余計な操作をしてしまいます。
よって、処理時間が長めになる場合は、この「応答なし」状態を回避してあげることが望ましいです。
ではどのように回避するのかというと、例えば大量のデータに対するループ処理中などで「応答なし」状態になっている場合に、「DoEvents関数」を呼び出し、Excelの制御を一瞬OSに返してあげるという方法があります。
このDoEvents関数をループ処理内で実行することで画面が応答なし状態になることを防いでくれますが、このDoEvents関数には欠点があり、ループ処理自体の処理速度が遅くなります。
ExcelがOSからの入力も無視して一生懸命処理している最中に、DoEventsが実行されると、ExcelがOSから声を掛けられていちいちそれに返答をしているようなものなので、確かに遅くなります。
できれば応答なしを無くしつつ、処理速度の低下も減らしたいという場合は、以下の処理を実装して、このSubプロシージャをCallしてください。
Option Explicit
Declare Function GetInputState Lib "USER32" () As Long
Private m_Time As Variant
Public Sub CheckEvents()
If GetInputState() Or (DateDiff("s", m_Time, Time) > 1) Then
DoEvents
m_Time = Time
End If
End Subステータスバーで進捗状況を画面に表示する
大量のデータを扱う集計処理では、処理に時間が掛かり、処理が止まっていないか不安になったり、現在どこまで処理が進んでいるのか気になる場合も多いです。
そういうケースではExcelの「ステータスバー」と呼ばれる、Excel画面右下の表示領域に処理状況を逐一表示する方法があります。
因みに、応答なし状態になっている場合は、この「ステータスバー」に表示させようとしても、画面描写が止まってしまい、ステータスバーも切り替わってくれないため、前項の「DoEvents」などの画面描写処理の実装も不可欠です。
Dim MaxCount As Long Dim i As Long '～なんやかんやの処理があって 'レコードセットの全行数を変数に代入します。 MaxCount = Rs.RecordCount 'カウンタの初期値を設定します。 i = 1 'レコードセットの行数分ループします。 Do Until Rs.EOF DoEvents Application.StatusBar = i & " 行目 / " & MaxCount & " 行" '～なんやかんやの処理 i = i + 1 Rs.MoveNext Loop 'ステータスバーの使用を終了します。 Application.StatusBar = False
最後に
今回の記事では、Excelの集計表作成処理を自動化する場合のポイントや実装例を紹介しました。
VBAを一切に触っていない人や、プログラミング経験が乏しい場合は、今回の記事は難易度が高いかも知れませんが、ある程度VBAが触れる場合は、集計表の作成作業を一度自動化してしまえば、それ以降は同じ処理やロジックなどを使いまわしながら別の集計表でも応用できるようになる為、是非積極的に取り入れてみてください。
それでは今回も読んでいただきましてありがとうございました。