なぜExcelでデータベースなのか
顧客管理、商品管理、注文管理を別々のExcelファイルで管理している会社は多いでしょう。しかし、これらのデータを手作業で結合するのは時間がかかります。
実際に、注文データから「どの顧客が何の商品をいくつ注文したか」を一覧にしたい場面があります。3つのファイルを開いて、IDを見比べながらコピー&ペーストしていませんか。
この作業をVBAで自動化すると、ボタン一つで完璧な結合データが作成できます。
データベースの基本的な考え方
リレーショナルデータベースとは、複数の表を関連づけて管理するシステムです。身近な例で説明しましょう。
図書館を想像してください。本の情報、利用者の情報、貸出記録を別々の台帳で管理しています。貸出記録には本のIDと利用者のIDだけを記録し、必要な時に詳細情報を他の台帳から探します。
これと同じ仕組みを、ExcelのVBAで実現できます。
今回作成するシステムの構成
作成するシステムは3つのマスタデータを結合します。
顧客マスタには顧客ID、顧客名、電話番号、住所を格納します。商品マスタには商品ID、商品名、価格、カテゴリを格納します。注文データには注文ID、注文日、顧客ID、商品ID、数量を格納します。
最終的に、注文データをベースとして、顧客名や商品名を含む詳細な一覧表を自動生成します。
ファイル選択機能の実装
まず、ユーザーが簡単にファイルを選択できる機能を作ります。
Function SelectFile(dialogTitle As String) As String
Dim fd As FileDialog
Dim selectedFile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = dialogTitle
.Filters.Clear
.Filters.Add "Excelファイル", "*.xlsx;*.xls"
.AllowMultiSelect = False
End With
If fd.Show = -1 Then
selectedFile = fd.SelectedItems(1)
Else
selectedFile = ""
End If
SelectFile = selectedFile
End Function
Code language: JavaScript (javascript)この関数は、Windowsの標準的なファイル選択画面を表示します。FileDialogオブジェクトを使用して、Excelファイルのみを表示するフィルタを設定しています。
With文を使うことで、同じオブジェクトに対する操作を簡潔に書けます。まるで「fdについて、以下の設定を行う」と宣言しているようなものです。
入力値の検証機能
シート番号の入力では、適切な値が入力されているかを確認します。
Function InputSheetNumber(dataType As String, defaultValue As Integer) As Integer
Dim userInput As String
Dim sheetNumber As Integer
userInput = InputBox(dataType & "のシート番号を入力してください", _
"シート番号入力", defaultValue)
If userInput = "" Then
InputSheetNumber = 0
Exit Function
End If
If IsNumeric(userInput) Then
sheetNumber = CInt(userInput)
If sheetNumber >= 1 Then
InputSheetNumber = sheetNumber
Else
InputSheetNumber = 0
End If
Else
InputSheetNumber = 0
End If
End Function
Code language: JavaScript (javascript)IsNumeric関数で数値かどうかを判定し、CInt関数で整数に変換します。不正な値が入力された場合は0を返し、呼び出し元で処理を中断します。
データ結合処理の核心部分
最も重要な部分が、3つのファイルのデータを結合する処理です。
For i = 2 To lastRowOrder
orderID = wsOrder.Cells(i, 1).Value
customerID = wsOrder.Cells(i, 3).Value
productID = wsOrder.Cells(i, 4).Value
' 顧客情報を検索
For j = 2 To lastRowCustomer
If wsCustomer.Cells(j, 1).Value = customerID Then
customerName = wsCustomer.Cells(j, 2).Value
Exit For
End If
Next j
' 商品情報を検索
For k = 2 To lastRowProduct
If wsProduct.Cells(k, 1).Value = productID Then
productName = wsProduct.Cells(k, 2).Value
price = wsProduct.Cells(k, 3).Value
Exit For
End If
Next k
Next i
Code language: PHP (php)この処理は、注文データの各行について、対応する顧客情報と商品情報を探し出します。
まず注文データから顧客IDと商品IDを取得します。次に、その顧客IDで顧客マスタを全件検索し、一致する行が見つかったら顧客名を取得してループを抜けます。商品情報も同様に検索します。
Exit For文により、該当データが見つかった時点で検索を停止します。これにより処理時間を短縮できます。
エラー処理と安全な実行
プログラムが途中で止まらないよう、エラー処理を組み込みます。
On Error GoTo ErrorHandler
' メイン処理
Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "エラーが発生しました: " & Err.Description, vbCritical
Code language: PHP (php)On Error GoTo文により、エラーが発生した場合の処理を指定します。ErrorHandlerラベルで、画面更新やアラート表示を元に戻し、エラー内容をユーザーに表示します。
ScreenUpdatingをFalseにすると画面更新が停止し、処理速度が向上します。しかし、エラーが発生した場合は必ずTrueに戻す必要があります。
オブジェクトとメモリ管理
外部ファイルを開いた後は、適切にファイルを閉じます。
Set wbCustomer = Workbooks.Open(customerFile)
Set wsCustomer = wbCustomer.Worksheets(customerSheet)
' 処理
wbCustomer.Close False
Setキーワードでオブジェクト変数にワークブックを割り当てます。処理終了後は、Close メソッドでファイルを閉じます。引数のFalseは「保存しないで閉じる」という意味です。
実用的な最適化テクニック
処理速度を向上させるため、いくつかの最適化を行います。
最終行の自動取得により、データ量に関係なく動作します。Cells(Rows.Count, 1).End(xlUp).Rowは、A列の最下行からデータがある最初のセルまで遡って行番号を取得します。
画面更新の停止により、大量データ処理時の画面のちらつきを防ぎます。Application.ScreenUpdating = Falseで更新を停止し、処理完了後にTrueで再開します。
AutoFitメソッドにより、列幅をデータに合わせて自動調整します。見やすい表が簡単に作成できます。
完成したシステムの使い方
完成したシステムは以下の手順で動作します。
CreateJoinedSheetを実行すると、まず顧客情報ファイルの選択画面が表示されます。次に商品情報ファイル、注文情報ファイルを順番に選択します。
各ファイルのシート番号を入力します。通常は1を入力すれば問題ありません。
すべての入力が完了すると、自動的にデータ結合処理が開始されます。新しいシートに結合結果が出力され、フィルタ機能も自動で設定されます。
VBAプログラミングの基本パターン
このシステムには、VBAプログラミングの基本的なパターンが含まれています。
変数宣言でデータを格納する箱を用意し、ユーザーインターフェースで入力を受け取ります。外部データの読み込み、ループ処理によるデータ加工、結果の出力という流れは、多くのVBAプログラムで共通しています。
関数の作成により、同じ処理を再利用できます。エラー処理により、予期しない状況でもプログラムが安全に動作します。
まとめ
ExcelのVBAを使って、3つの外部ファイルを自動結合するシステムを作成しました。ファイル選択ダイアログ、入力値検証、データ結合処理、エラー処理といった実用的な機能を組み合わせることで、手作業では時間のかかるデータ統合作業を自動化できます。このシステムは、顧客管理、商品管理、注文管理といったビジネスシーンで直接活用できる実践的なソリューションです。