【はじめに】
ExcelのVlookup関数を使用してデータを集計する業務をしている方向けに、セルに直接式を入力するのではなくVBAでマクロ化させる方法を紹介します。
非エンジニアの方でも、プログラミング志向を学べてデータ集計以外にも応用できるスキルなのでぜひ参考にしてください。
【どんなケースで使えるのか】
実際にどんなケースでマクロ化できるのか、例を挙げて解説していきます。
ケーススタディ
商品コードを入力して、数量を入力すると「商品名」、「単価」、「金額」、「合計」が自動で入力されるシートを「対象シート」とします。
商品一覧のマスタデータを「参照シート」とします。
普段であれば、Vlookup関数で対象のセルに一つずつ式を入力していきますが、行が追加されればその都度式も追加で入力していく必要があります。
マクロ化させれば、行が100行、1000行に増えても式を追加していく必要なく表を使用することが可能です。
【実際にVBAでマクロ化してみる】
次の手順で、マクロ化していきます。
手順1:関数を定義する
手順2:対象とするシートと参照するシートをマクロ内で名前を定義する
手順3:次の変数を用意する
手順4:繰り返しデータを入力していく最後の行数と繰り返しデータ参照する最後の行数を取得する
手順5:繰り返し処理するために、次の変数を用意
手順6:対象シートの商品番号と一致する参照シートの「商品名」、「単価」を順番に入力していく
手順1:関数を定義する
'手順1:関数を定義する
Sub vlookup()
'この中にコードを記述していきます。
End Sub
ポイント
- Subの後の関数名は何でもいいですが、どのような処理をしているのか分かるような名前にするのが良いでしょう。
手順2:対象とするシートと参照するシートをマクロ内で名前を定義する
'手順2:対象とするシートと参照するシートをマクロ内で名前を定義する
Dim Ws1 As Worksheet, Ws2 As Worksheet '変数の定義
Set Ws1 = ThisWorkbook.Sheets("対象シート") '変数にシート名を代入
'参照するシートが同ブックにある場合
Set Ws2 = ThisWorkbook.Sheets("参照シート")
'参照するシートが別ブックにある場合
'Set Ws2 = Workbooks("ブック名").Sheets("参照シート")
ポイント
- 変数を定義する場合は、Dimを変数の先頭に記述します。
- また、変数のデータ型をAs ‘データ型’ で定義します。
- オブジェクト型の変数に値を代入する場合は、Setを変数先頭に記述します。
一般的なデータ型は次の通りです。
データ型名 | 変数 | 格納できるデータ | データ内容 |
---|---|---|---|
ブール型 | Boolean | True または False | 真偽 |
整数型 | Integer | -32,768~32,767の整数 | 数値 |
長整数型 | Long | -2,147,483,648~2,147,483,647の整数 | 数値 |
日付型 | Date | 日付:西暦100年1月1日~西暦9999年12月31日, 時刻:0:00:00~23:59:59 | 日付 |
文字列型 | String | 任意の長さの文字列(最大約20億文字) | 文字列 |
オブジェクト型 | Object | オブジェクト参照するデータ型 | オブジェクト(WorkBook, WorkSheet, Rangeなど) |
バリアント型 | Variant | すべてのデータ | 全てのデータ |
手順3:各項目を代入する変数を用意する
'手順3:各項目を代入する変数を用意する
'①「商品番号」と抽出した「商品名」、「単価」、「数量」を代入する変数を用意する
Dim ItemNo As String, ItemName As String, price As Long, Amount As Long
'②参照するデータの「商品番号」を代入する変数を用意する
Dim MasterItemNo As String
手順4:繰り返しデータを入力していく最後の行数と繰り返しデータ参照する最後の行数を取得する
'手順4:繰り返しデータを入力していく最後の行数と繰り返しデータ参照する最後の行数を取得する
Dim Ws1LastRow As Long, Ws2LastRow As Long
Ws1LastRow = Ws1.Range("A65536").End(xlUp).Row 'Ws1のA列にある値の最終行を取得
Ws2LastRow = Ws2.Range("A65536").End(xlUp).Row 'Ws2のA列にある値の最終行を取得
ポイント
- 入力されているデータ数に応じて、最終行は変動します。
- 最終行列数を取得するには次のように記述します。
.Range("セル").End(xlUp).Row '対象のセルを起点に上に向かって値が入っているセルの行数を取得(最終行)
.Range("セル").End(xlDown).Row '対象のセルを起点に下に向かって値が入っているセルの行数を取得(先頭行)
.Range("セル").End(xlToRight).Column '対象のセルを起点に右に向かって値が入っているセルの列数を取得(一番右の列)
.Range("セル").End(xlToLeft).Column '対象のセルを起点に左に向かって値が入っているセルの行数を取得(一番左の列)
手順5:繰り返し処理するため、行数を代入する変数を用意
'手順5:繰り返し処理するため、行数を代入する変数を用意
'①対象シート最初の行数〜最後の行数を代入する変数
Dim i As Long
'②参照シートのデータの最初の行数〜最後の行数を代入する変数を用意
Dim j As Long
手順6:対象シートの商品番号と一致する参照シートの「商品名」、「単価」を順番に入力していく
'手順6:対象シートの商品番号と一致する参照シートの「商品名」、「単価」を順番に入力していく
For i = 2 To Ws1LastRow '対象シートの2行目から最終行まで入力を繰り返す
ItemNo = Ws1.Range("B" & i).Value '対象シートに入力された「商品番号」
Amount = Ws1.Range("E" & i).Value '対象シートに入力された「数量」
For j = 2 To Ws2LastRow '参照シートの2行目から最終行のデータを繰り返し参照していく
MasterItemNo = Ws2.Range("A" & j).Value '参照シートの「商品番号」
If ItemNo = MasterItemNo Then 'もし対象シートの「商品番号」と参照シートの「商品番号」が一致したらデータをItemNameとPriceに代入する
ItemName = Ws2.Range("B" & j).Value
price = Ws2.Range("C" & j).Value
Exit For '代入したら参照シートの参照を止める
End If
Next
Ws1.Range("C" & i).Value = ItemName '対象シートのセルに参照シートと一致した「商品番号」の値を入力する
Ws1.Range("D" & i).Value = price '対象シートのセルに参照シートと一致した「単価」の値を入力する
Ws1.Range("F" & i).Value = price * Amount '「単価」×「数量」=「金額」を入力する
Next
ポイント
- セルを指定する場合は、Range.(“セル”)/Cells(行番号, 列番号)で指定します。
- セルの値を指定する場合は、.Valueをセル指定の後に付けます。
- 同じ処理を繰り返し実行していくには、For~Next文を使用します。
- 入れ子構造にすることで、ExcelのVlookup関数と同じ処理を行うことができます。
- 手順6を言葉に落とし込んだものが次の構造になります。
For 変数1 = 開始値 to 終了値
'対象シートの2行目から最終行まで入力を繰り返す
'対象シートに入力された「商品番号」を変数ItemNoに代入する
'対象シートに入力された「数量」を変数Amountに代入する
For 変数2 = 開始値 to 終了値
'参照シートの2行目から最終行のデータを繰り返し参照していく
'参照シートの「商品番号」を変数MasterItemNoに代入する
'もし対象シートの「商品番号ItemNo」と参照シートの「商品番号MasterItemNo」が一致したらデータをItemNameとPriceに代入する
'代入したら参照シートの参照を止める
Next
Next
手順7:各商品の金額をを計算する
'手順7:各商品の金額を計算する
Ws1.Range("F" & Ws1LastRow).Offset(1).Value = WorksheetFunction.Sum(Ws1.Range("F2", "F" & Ws1LastRow)) '金額が入力されている最終行の1行下に合計金額を入力
ポイント
- Offset(1)で指定したセルの1行下を参照しています。
- 関数の中で、通常のエクセル関数を組み込むことができます。
- ExcelのSum関数を組み込むときは、次のように記述します。
WorksheetFunction.Sum(Ws1.Range("セル1", "セル2" ))
【サンプルコード公開】
サンプルコード全体を公開します。
'手順1:関数を定義する
Sub vlookup()
'手順2:対象とするシートと参照するシートをマクロ内で名前を定義する
Dim Ws1 As Worksheet, Ws2 As Worksheet '変数の定義
Set Ws1 = ThisWorkbook.Sheets("対象シート") '変数にシート名を代入
'参照するシートが同ブックにある場合
Set Ws2 = ThisWorkbook.Sheets("参照シート")
'参照するシートが別ブックにある場合
'Set Ws2 = Workbooks("ブック名").Sheets("参照シート")
'手順3:次の変数を用意する
'①「商品番号」と抽出した「商品名」、「単価」、「数量」を代入する変数を用意する
Dim ItemNo As String, ItemName As String, price As Long, Amount As Long
'②参照するデータの「商品番号」を代入する変数を用意します。
Dim MasterItemNo As String
'手順4:繰り返しデータを入力していく最後の行数と繰り返しデータ参照する最後の行数を取得する
Dim Ws1LastRow As Long, Ws2LastRow As Long
Ws1LastRow = Ws1.Range("A65536").End(xlUp).Row 'Ws1のA列にある値の最終行を取得
Ws2LastRow = Ws2.Range("A65536").End(xlUp).Row 'Ws2のA列にある値の最終行を取得
'手順5:繰り返し処理するために、次の変数を用意
'①対象シート最初の行数〜最後の行数を代入する変数
Dim i As Long
'②参照シートのデータの最初の行数〜最後の行数を代入する変数を用意
Dim j As Long
'手順6:対象シートの商品番号と一致する参照シートの「商品名」、「単価」を順番に入力していく
For i = 2 To Ws1LastRow '対象シートの2行目から最終行まで入力を繰り返す
ItemNo = Ws1.Range("B" & i).Value '対象シートに入力された「商品番号」
Amount = Ws1.Range("E" & i).Value '対象シートに入力された「数量」
For j = 2 To Ws2LastRow '参照シートの2行目から最終行のデータを繰り返し参照していく
MasterItemNo = Ws2.Range("A" & j).Value '参照シートの「商品番号」
If ItemNo = MasterItemNo Then 'もし対象シートの「商品番号」と参照シートの「商品番号」が一致したらデータをItemNameとPriceに代入する
ItemName = Ws2.Range("B" & j).Value
price = Ws2.Range("C" & j).Value
Exit For '代入したら参照シートの参照を止める
End If
Next
Ws1.Range("C" & i).Value = ItemName '対象シートのセルに参照シートと一致した「商品番号」の値を入力する
Ws1.Range("D" & i).Value = price '対象シートのセルに参照シートと一致した「単価」の値を入力する
Ws1.Range("F" & i).Value = price * Amount '「単価」×「数量」=「金額」を入力する
Next
'手順7:各商品の金額を計算する
Ws1.Range("F" & Ws1LastRow).Offset(1).Value = WorksheetFunction.Sum(Ws1.Range("F2", "F" & Ws1LastRow)) '金額が入力されている最終行の1行下に合計金額を入力
End Sub
【さいごに】
今回は、ExcelのVlookup関数をマクロ化する方法を紹介しました。
私自身、始めは文法から学び始めましたが目的が明確になく、一度挫折しています。
- まずは身近にある簡単な業務から実際にマクロ化を経験してみること
- 文法は後から身に付いてくる
- あとはテンプレを使い回す
この三点を意識すれば、業務レベルでのExcelスキルがグッと上がりますのでぜひ参考にしてください。
コメント