実践編: データ転記ツールの作り方ステップバイステップ

前回は「マクロとは何か」「ChatGPTでどうやってマクロをつくるか」を説明しました。

今回は一歩すすんで、実際の業務で役立つ「データ転記ツール」をつくっていきます。

データ転記の業務フロー

多くの方が日常業務でおこなっているのが、あるExcelファイルから別のExcelファイルへのデータの転記です。

✔　転記作業の例
・　データベースからエクスポート
・　エクスポートしたExcelをひらく
・　必要な情報を探して選択
・　コピー
・　帳票用のExcelファイルを開く
・　適切な場所に貼り付け
・　整形や調整
・　保存

このような作業を週に何度も、あるいは毎日行っているという方も多いのではないでしょうか。

今回は転記のプロセスを自動化するマクロをつくっていきます。

転記作業の図解による整理

データベースから特定の情報をとりだし、特定のフォーマットに転記することを目的とするマクロを考えてみます。

上の図のように、作業イメージをかいてみましょう。

ソースデータと転記先の関係性を整理する

マクロをつくる前に、その内容を整理しておきます。

どのデータをどこに転記するか

マクロをつくる前に、どこの何を、どこに転記するのか確認します。

✔　どのデータをどこに転記するか
・ソースファイルの「顧客ID」→ 転記先の「ID:」欄
・ソースファイルの「名前」→ 転記先の「名前:」欄

条件や変換はあるか

特定の条件があって一部のデータのみをマクロで転記したいときは、条件について確認しておきます。

また、転記先での変換が必要なデータについても確認します。

✔　条件や変換について
・　特定の条件のデータだけを転記する
・　データの形式を変換する（例：日付のフォーマット変更）

複数行のデータを扱うか

ソースファイルの複数行のデータを転記したいときは、一番上の行のみ転記されてしまうなどの不具合が生じないように、処理についても確認しておきます。

1つの顧客について複数行のデータがある場合の処理

これらの諸条件をメモ帳やExcelに整理しておくと、後のコードづくりがスムーズになります。

VBAコードの基本構造

データ転記マクロのVBAコードは、一般的に以下のような構造になります。

Sub データ転記マクロ()
‘ 変数の宣言
Dim ソースブック As Workbook
Dim 転記先ブック As Workbook
Dim ソースシート As Worksheet
Dim 転記先シート As Worksheet

‘ ファイルを開く
Set ソースブック = Workbooks.Open(“C:\Path\To\ソースファイル.xlsx”)
Set 転記先ブック = ThisWorkbook

‘ シートを設定
Set ソースシート = ソースブック.Sheets(“Sheet1”)
Set 転記先シート = 転記先ブック.Sheets(“Sheet1”)

‘ データを転記
‘ （ここに転記のための具体的なコード）

‘ ソースファイルを閉じる
ソースブック.Close SaveChanges:=False

‘ 完了メッセージ
MsgBox “データの転記が完了しました。”, vbInformation
End Sub

これが、基本的なマクロのコードの骨格となります。

次に、この骨格を埋めていく具体的な指示について見ていきましょう。

ChatGPTへの効果的な指示の出し方

ChatGPTにマクロを作成してもらう際、明確な指示を出すことが重要です。

以下のポイントを意識しましょう。

まずは、作業の背景を説明する

例えば「A列からB列にデータをコピーするマクロ」と指示するよりも、以下のような背景を加えることで、より実用的で堅牢なコードが得られます。

✔　マクロをつくる背景
顧客リストから、特定の条件を満たす顧客だけを別シートに抽出して月次レポートを作成するため

背景を伝えることは、単なるプログラミングの指示ではなく、ビジネス課題の解決のためのコラボレーションとしてChatGPTを活用することにつながります。

✔　背景を説明する効果
・　予期せぬ状況を考慮
・　業務フローに沿った例外処理
・　複数の選択肢から最適な方法を選択
・　拡張性や保守性を考慮して設計
・　必要以上に複雑なコードを回避
・　実際のビジネスニーズに合致

背景を説明すると、つくられるコードが一時的な解決策ではなく、長期的に価値のあるものになります。

具体的な目的を説明する

もちろん、具体的な目的も書いておきます。

✔　マクロをつくる目的
ソースファイルのA列のデータを転記ファイルのC列に転記したい

使用するファイルの構造を説明する

✔　ファイル構造の説明
ソースファイルは顧客リストで、A列にID、B列に名前がある
転記先ファイルはB2セルにID、B3セルに名前を入れたい

特殊な条件があれば明記する

✔　特殊な条件の説明
A列の値が”ABC”で始まるデータだけを転記したい

エラー処理の要望を伝える

✔　エラー処理の要望
ファイルが見つからない場合のエラーメッセージを表示してほしい

例えば、以下のような指示が効果的です。

Excelマクロを作成したいです。
「C:\データ\顧客リスト.xlsx」ファイルを開き、そのSheet1のA列からJ列のデータを、
現在開いているブックのSheet1のA2セルから転記するVBAコードを書いてください。
ただし、転記するのはC列が「完了」となっている行だけにしてください。
ファイルが見つからない場合はエラーメッセージを表示してください。

このように具体的に指示することで、ChatGPTはより正確なコードがつくられます。

実習：シンプルな転記マクロの作成

それでは実際に、シンプルなデータ転記マクロをつくってみましょう。

✔　想定するシナリオ
データベースから抽出した顧客リストがある
顧客IDを検索して、その顧客の情報を帳票に転記したい

ステップ1：ChatGPTに指示を出す

ChatGPTには以下のように指示します。

顧客リストから、特定の条件を満たす顧客だけを別シートに抽出して月次レポートを作成するためExcelマクロを作成したいです。
ソースファイル「C:\データ\顧客リスト.xlsx」のSheet1には、
A列：顧客ID
B列：顧客名
C列：電話番号
D列：メールアドレス
E列：最終取引日
のデータがあります。
現在開いているブック（帳票テンプレート）のA2セルに入力されている顧客IDを
ソースファイルから検索し、以下のように転記したいです：
– 顧客名 → B5セル
– 電話番号 → B6セル
– メールアドレス → B7セル
– 最終取引日 → B8セル
該当するIDが見つからない場合は「該当する顧客情報が見つかりません」と
メッセージを表示してください。
VBAコードを作成していただけますか？

ステップ2：ChatGPTが生成したコードの例

ChatGPTからは、以下のようなコードが返ってくるでしょう。

Sub 顧客情報転記()
‘ 変数の宣言
Dim ソースブック As Workbook
Dim 転記先ブック As Workbook
Dim ソースシート As Worksheet
Dim 転記先シート As Worksheet
Dim 検索ID As String
Dim 検索範囲 As Range
Dim 検索結果 As Range

‘ エラーハンドリング
On Error Resume Next

‘ 現在のブックを転記先として設定
Set 転記先ブック = ThisWorkbook
Set 転記先シート = 転記先ブック.ActiveSheet

‘ 検索するIDを取得
検索ID = 転記先シート.Range(“A2”).Value

‘ IDが空白の場合はメッセージを表示して終了
If 検索ID = “” Then
MsgBox “検索するIDを入力してください。”, vbExclamation
Exit Sub
End If

‘ ソースファイルを開く
Set ソースブック = Workbooks.Open(“C:\データ\顧客リスト.xlsx”)

‘ エラーチェック
If Err.Number <> 0 Then
MsgBox “ソースファイルを開けませんでした。ファイルパスを確認してください。”, vbCritical
Exit Sub
End If

Set ソースシート = ソースブック.Sheets(“Sheet1”)

‘ A列を検索範囲として設定
Set 検索範囲 = ソースシート.Range(“A:A”)

‘ IDを検索
Set 検索結果 = 検索範囲.Find(What:=検索ID, LookIn:=xlValues, LookAt:=xlWhole)

‘ 検索結果に基づいて処理
If 検索結果 Is Nothing Then
‘ 見つからなかった場合
MsgBox “該当する顧客情報が見つかりません。”, vbExclamation
Else
‘ 見つかった場合、データを転記
転記先シート.Range(“B5”).Value = ソースシート.Cells(検索結果.Row, 2).Value ‘ 顧客名
転記先シート.Range(“B6”).Value = ソースシート.Cells(検索結果.Row, 3).Value ‘ 電話番号
転記先シート.Range(“B7”).Value = ソースシート.Cells(検索結果.Row, 4).Value ‘ メールアドレス
転記先シート.Range(“B8”).Value = ソースシート.Cells(検索結果.Row, 5).Value ‘ 最終取引日

MsgBox “顧客情報の転記が完了しました。”, vbInformation
End If

‘ ソースファイルを閉じる
ソースブック.Close SaveChanges:=False

‘ エラーハンドリングをリセット
On Error GoTo 0
End Sub

インデント

マクロのコードを書くときは、Tabキー１回分やスペースキー４回分をつかって、インデントすることで、見やすいように工夫します。

ChatGPTでコードをつくってもらうと、適当にインデックスしてくれます。

シンタックスハイライト

また、コードの色分けのことを、シンタックスハイライトといい、ChatGPTのコードも色付けされて表示されます。

✔　今回の着色の例
・　予約語と呼ばれるキーワード（紫）
・　(‘)で始まるコードの説明や注釈（灰）
・　(“)で囲まれた文字列リテラル（緑）
・　固定された値を持つ特別な識別子（橙）
・　演算子（青）

予約語

予約語にはSub, Function, End, If, Then, Else, For, Next, Dim, Set, Private Sub, End Sub, As, On Error, GoTo, Resumeなどがあります。

定数・リテラル値

特別な識別子である定数やリテラル値には、以下のようなものがあります。

✔　定数やリテラル値の例
Nothing: オブジェクト変数が何も参照していない状態を表す特殊な値
False: 論理値の「偽」を表す組み込み定数
True: 論理値の「真」を表す組み込み定数
0: 数値リテラル（この場合は整数の0）

演算子

演算子には、イコール（=）、比較演算子（<, >, <=, >=など）、算術演算子（+, –, *, /）などがあります。

ステップ3：コードの確認

ChatGPTがつくったコードを可能な範囲で確認していきます。

このコードの重要なポイントを解説します。

変数の宣言

Dim で各種変数を宣言しています。
ブック、シート、検索用の変数などを用意しています。

エラーハンドリング

On Error Resume Next でエラーが発生しても処理を続行します。
エラーがあった場合は Err.Number でチェックします。

検索処理

Range.Find メソッドを使って顧客IDを検索しています。
検索結果が Nothing かどうかで、見つかったかどうかを判断しています。

データの転記

見つかった行から各列のデータを取得し、転記先の指定セルに入力しています。
Cells(行, 列) の形式でセルを指定しています。

後処理

ソースファイルを保存せずに閉じています。
エラーハンドリングをリセットしています。

マクロコードの基本要素

コードに含まれる重要な要素をもっと詳しく見ていきましょう。

1. 変数宣言とオブジェクト参照

Dim ブック As Workbook
Dim シート As Worksheet
Set ブック = Workbooks.Open(“ファイルパス”)
Set シート = ブック.Sheets(“シート名”)

Dim で変数を宣言します。
Set でオブジェクト変数に値を設定します。
Workbooks.Open でファイルを開きます。
Sheets(“シート名”) でシートを参照します。

2. セル値の取得と設定

‘ 値を取得
値 = Range(“A1”).Value
‘ 値を設定
Range(“B1”).Value = “テキスト”

Range(“セル参照”).Value でセルの値を取得・設定します。
Cells(行, 列).Value も同様に使えます（行と列を数値で指定）。

3. 検索機能

Set 検索結果 = 範囲.Find(What:=“検索文字列”, LookIn:=xlValues, LookAt:=xlWhole)

Find メソッドで範囲内を検索します
What に検索する値を指定します
LookIn で検索対象（値、数式など）を指定します
LookAt で完全一致か部分一致かを指定します

4. 条件分岐

If 条件 Then
‘ 条件が真の場合の処理
ElseIf 別の条件 Then
‘ 別の条件が真の場合の処理
Else
‘ どの条件も満たさない場合の処理
End If

If…Then…Else で条件分岐を行います。
複数の条件は ElseIf で追加できます。
End If で条件分岐を終了します。

5. エラーハンドリング

On Error Resume Next ‘ エラーを無視して続行
On Error GoTo エラー処理ラベル ‘ エラー時に特定の処理へジャンプ
Err.Number ‘ エラーコード
Err.Description ‘ エラーの説明

On Error Resume Next でエラーを無視して処理を続行します
Err.Number でエラーコードを確認できます
On Error GoTo 0 でエラーハンドリングをリセットします

応用例：複数顧客の転記

先ほどの例では1件の顧客情報を転記しましたが、複数の顧客情報を一度に転記したい場合もあるでしょう。以下にその例を示します。

‘ ソースシートの全顧客情報を転記先シートに一覧表示する例
Sub 複数顧客情報転記()
‘ 変数の宣言
Dim ソースブック As Workbook
Dim 転記先ブック As Workbook
Dim ソースシート As Worksheet
Dim 転記先シート As Worksheet
Dim 最終行 As Long
Dim i As Long

‘ 現在のブックを転記先として設定
Set 転記先ブック = ThisWorkbook
Set 転記先シート = 転記先ブック.ActiveSheet

‘ ソースファイルを開く
Set ソースブック = Workbooks.Open(“C:\データ\顧客リスト.xlsx”)
Set ソースシート = ソースブック.Sheets(“Sheet1”)

‘ ソースの最終行を取得
最終行 = ソースシート.Cells(ソースシート.Rows.Count, 1).End(xlUp).Row

‘ ヘッダー行を転記
転記先シート.Range(“A1”).Value = “顧客ID”
転記先シート.Range(“B1”).Value = “顧客名”
転記先シート.Range(“C1”).Value = “電話番号”
転記先シート.Range(“D1”).Value = “メールアドレス”
転記先シート.Range(“E1”).Value = “最終取引日”

‘ データ行を転記
For i = 2 To 最終行
転記先シート.Cells(i, 1).Value = ソースシート.Cells(i, 1).Value ‘ 顧客ID
転記先シート.Cells(i, 2).Value = ソースシート.Cells(i, 2).Value ‘ 顧客名
転記先シート.Cells(i, 3).Value = ソースシート.Cells(i, 3).Value ‘ 電話番号
転記先シート.Cells(i, 4).Value = ソースシート.Cells(i, 4).Value ‘ メールアドレス
転記先シート.Cells(i, 5).Value = ソースシート.Cells(i, 5).Value ‘ 最終取引日
Next i

‘ ソースファイルを閉じる
ソースブック.Close SaveChanges:=False

‘ 完了メッセージ
MsgBox “顧客情報の転記が完了しました。全” & (最終行 – 1) & “件の顧客情報を転記しました。”, vbInformation
End Sub

この例では、For…Next ループを使って複数行のデータを処理しています。

まとめ

今回は、データ転記マクロの作成方法について学びました。

✔　マクロ作成の指示のポイント
・　転記作業の流れを理解する
・　ソースと転記先の関係性を整理
・　VBAコードの基本構造を理解する
・　ChatGPTへの効果的な指示を出す
・　実際にコードを作成し内容を確認

これらの知識を組み合わせることで、日常業務で使えるデータ転記ツールを作成できるようになります。

コツをつかめば、単純な転記だけでなく、条件に基づいたフィルタリングや、データの整形なども組み込めるようになります。

次回は「応用編：マクロのカスタマイズと日付を含むファイル名での保存」として、今回作成したマクロをさらに発展させ、転記後に日付を含むファイル名で保存する機能を追加します。

また、マクロの実行をボタンから行えるようにするなど、より使いやすくするテクニックも紹介します。

（おまけ）練習問題

今回の内容を復習するために、以下の練習問題にチャレンジしてみてください。

先ほどのコードを参考に、特定の条件（例：最終取引日が今年のもの）のデータだけを転記するマクロを作成してみましょう。
転記先のデータを整形する処理（例：電話番号のハイフン追加）を追加してみましょう。
ChatGPTに自分の業務で使えそうな転記マクロの指示を出してみましょう。