売上明細データに商品台帳データから分類、商品名、原単価をつけ込むサンプルです。
VLOOKUP関数のと速度比較をして見ました。
「マッチング」とは？
「マッチング」というとデータベースソフトがない時代に複数のINPUTファイルから１つのOUTPUTファイルを並び順を崩さずに作成する方法として利用していましたが、データベースソフトでは単にデータを追加していくだけで後から必要な順に取り出せるので「死語」になってしまったかも知れません。
しかし、Excelで集計等を行なう場合にはシート上で必要な順に並んでいる必要があり「マッチング」は不可欠な技術です。
なお、「VLOOKUP関数＝マッチング」ではありません。ここではマスタ情報を集計時に取り出すにあたって効率良く処理を行なうためにマッチングを用いています。
ここでは、1万件の売上明細シートに対して、100種類の商品を商品台帳シートから商品コードをキーにして分類、商品名、原単価を取り出し、売上集計のシートに店舗、分類順に転記することにします。しかし、Excelで集計等を行なう場合にはシート上で必要な順に並んでいる必要があり「マッチング」は不可欠な技術です。
なお、「VLOOKUP関数＝マッチング」ではありません。ここではマスタ情報を集計時に取り出すにあたって効率良く処理を行なうためにマッチングを用いています。
(画像をクリックすると、このサンプルがダウンロードできます)
これが「売上明細」シートです。1万件のデータは乱数を使って架空に作成します。
これが「商品台帳」シートです。商品コード「101」から「200」まで100件の商品が登録されています。
これが「売上集計」シートです。「売上明細」から1万件をそのまま「商品台帳」の情報を付加して転記し、金額の計算を行ないます。2シートあるのは計算式の貼り込みだけで行なうシートと「マッチング」のマクロで「商品台帳」の情報を付加するのとの2種類の結果を検証するためです。
まず、「売上明細」シートに1万件の売上明細データを作成します。
これは「RND関数」を利用して作成するマクロが準備されているので、これを起動させます。
古いデータですが2008年導入のPC(Core 2 Duo 2.4GHz + Win7-32Bit + Excel2010)でこの位の処理時間です。
その前のPC(HT-Pentium4 2.8GHz + WinXP + Excel2003)では、9.2秒掛かっていました。
ちなみに2019年導入のPC(Corei7-8700 3.2GHz + Win10-64Bit + Excel2013)では、他のアプリを動かしたままでも0.3秒程度ですが、 Excelの起動初回は1秒程度掛かるようでバラつきがありますが近年のPCは確実に速くなっています。
'***************************************************************************************************
' マッチング処理のサンプル Module1(Module)
'
' 作成者:井上治 URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev 変更履歴内容------------------------------------------------------------------------>
'03/11/29(1.00)新規作成
'12/03/25(1.01)初回修正
'20/03/02(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'===================================================================================================
Private Const g_cnsSH1 As String = "売上明細"
Private Const g_cnsSH2 As String = "商品台帳"
Private Const g_cnsSH3 As String = "売上集計①"
Private Const g_cnsSH4 As String = "売上集計②"
Private Const g_cnsMaxRow As Long = 10000 ' テストの件数は10000件固定
'---------------------------------------------------------------------------------------------------
' GetSystemTimeで使用する構造体
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
'---------------------------------------------------------------------------------------------------
#If VBA7 Then
' システム時刻取得(ミリ秒単位)
Private Declare PtrSafe Sub GetSystemTime Lib "KERNEL32.dll" _
(lpSystemTime As SYSTEMTIME)
#Else
' システム時刻取得(ミリ秒単位)
Private Declare Sub GetSystemTime Lib "KERNEL32.dll" _
(lpSystemTime As SYSTEMTIME)
#End If
'***************************************************************************************************
' ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名 ：①疑似売上データ作成
'* 機能 ：疑似売上データ作成
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年11月29日
'* 作成者 ：井上 治
'* 更新日 ：2020年03月02日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Sub ①疑似売上データ作成()
'-----------------------------------------------------------------------------------------------
Dim objSh1 As Worksheet ' 売上明細
Dim objStrTime As SYSTEMTIME ' 処理開始時間
Dim lngRow As Long ' 行INDEX
Dim lngRowMax As Long ' 行INDEX上限
' 画面描画停止
Call GP_StopScUpd(objStrTime)
Set objSh1 = ThisWorkbook.Worksheets(g_cnsSH1)
lngRowMax = g_cnsMaxRow + 1
objSh1.Activate
objSh1.Rows("2:" & objSh1.Rows.Count).ClearContents
'---------------------------------------------------------------------------
' 各行をループ
For lngRow = 2 To lngRowMax
' Rnd関数でコードを作成
objSh1.Cells(lngRow, 1).Value = Int((110 - 101 + 1) * Rnd + 101)
objSh1.Cells(lngRow, 2).Value = Int((200 - 101 + 1) * Rnd + 101)
objSh1.Cells(lngRow, 3).Value = Int((10 - 1 + 1) * Rnd + 1)
Next lngRow
'---------------------------------------------------------------------------
' 売単価列以降は計算式を一旦貼り付ける
objSh1.Range("$D$2:$D$" & lngRowMax).FormulaR1C1 = "=INT(RC5*RC6)"
objSh1.Range("$E$2:$E$" & lngRowMax).FormulaR1C1 = "=INT(VLOOKUP(RC2,商品台帳!R2C1:R101C4,4,FALSE))"
' 一旦、商品台帳の原単価を参照し、それらしい粗利になるように売価を設定
objSh1.Range("$F$2:$F$" & lngRowMax).FormulaR1C1 = "=RAND()*0.5+0.9"
' 再計算
objSh1.Calculate
' 売単価は値に貼り替え
With objSh1.Range("$D$2:$D$" & lngRowMax)
.Value = .Value
End With
'---------------------------------------------------------------------------
Application.Goto Cells(2, 1), True
objSh1.Calculate
' 欄外は消去
objSh1.Columns("E:F").ClearContents
' 画面描画再開
Call GP_StartScUpd(objStrTime)
End Sub
次に計算式による「売上集計」データの作成を行ないます。2種類の集計を行なうため、「売上明細」シート上は何も更新しないこととし、「売上集計」シートに「売上明細」の情報は貼り替えて集計します。
'***************************************************************************************************
'* 処理名 ：②売上集計_計算式
'* 機能 ：計算式利用による集計(簡単ですがマッチングではない)
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年11月29日
'* 作成者 ：井上 治
'* 更新日 ：2020年03月02日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Sub ②売上集計_計算式()
'-----------------------------------------------------------------------------------------------
Dim objSh1 As Worksheet ' 売上明細
Dim objSh3 As Worksheet ' 売上集計①
Dim objStrTime As SYSTEMTIME ' 処理開始時間
Dim lngRowMax As Long ' 行INDEX上限
'---------------------------------------------------------------------------
Set objSh1 = ThisWorkbook.Worksheets(g_cnsSH1)
Set objSh3 = ThisWorkbook.Worksheets(g_cnsSH3)
lngRowMax = g_cnsMaxRow + 1
'---------------------------------------------------------------------------
' 画面描画停止
Call GP_StopScUpd(objStrTime)
'---------------------------------------------------------------------------
' 売上集計①
With objSh3
.Activate
' 元の値をクリア
On Error Resume Next
If .FilterMode Then .ShowAllData
.Cells.Ungroup
On Error GoTo 0
.Rows("2:" & .Rows.Count).ClearContents
'-----------------------------------------------------------------------
' 売上明細から売上集計①へ値を移送 ' ①
.Range("$A$2:$A$" & lngRowMax).Value = _
objSh1.Range("$A$2:$A$" & lngRowMax).Value ' 店舗
.Range("$C$2:$C$" & lngRowMax).Value = _
objSh1.Range("$B$2:$B$" & lngRowMax).Value ' 商品コード
.Range("$E$2:$F$" & lngRowMax).Value = _
objSh1.Range("$C$2:$D$" & lngRowMax).Value ' 数量,単価
' 商品台帳参照の計算式をセット ' ②
.Range("$B$2:$B$" & lngRowMax).FormulaR1C1 = _
"=VLOOKUP(RC3,商品台帳!R2C1:R101C4,2,FALSE)" ' 分類
.Range("$D$2:$D$" & lngRowMax).FormulaR1C1 = _
"=VLOOKUP(RC3,商品台帳!R2C1:R101C4,3,FALSE)" ' 商品名
.Range("$G$2:$G$" & lngRowMax).FormulaR1C1 = _
"=VLOOKUP(RC3,商品台帳!R2C1:R101C4,4,FALSE)" ' 原単価
' 横合計の計算式をセット ' ③
.Range("$H$2:$H$" & lngRowMax).FormulaR1C1 = "=RC5*RC6" ' 売上額
.Range("$I$2:$I$" & lngRowMax).FormulaR1C1 = "=RC5*RC7" ' 原価額
.Range("$J$2:$J$" & lngRowMax).FormulaR1C1 = "=RC8-RC9" ' 粗利
'-----------------------------------------------------------------------
' 再計算
.Calculate
' 分類,商品名,原単価は値に貼替え
With .Range("$B$2:$B$" & lngRowMax)
.Value = .Value
End With
With .Range("$D$2:$D$" & lngRowMax)
.Value = .Value
End With
With .Range("$G$2:$G$" & lngRowMax)
.Value = .Value
End With
Application.Goto .Cells(2, 1), True
End With
'---------------------------------------------------------------------------
' 画面描画再開
Call GP_StartScUpd(objStrTime)
End Sub
| № | 概略説明 |
|---|---|
| ① |
「売上明細」シートから「売上集計①」シートに1万件の列単位にデータを転記しています。コピー、貼り付けではなく「転記先範囲の値」＝「転記元範囲の値」の1行で転記できます。3回に分けているのは列の位置関係が一致していないからです。 |
| ② |
VLOOKUP関数の計算式を貼り込んでいます。R1C1参照形式での計算式なので行によっての変位がなく、1万行分を1行の記述で貼り込めます。 |
| ③ |
数量×単価、及び売上額－原価額の計算式です。これも同様にR1C1参照形式での計算式なので行によっての変位しません。 |
その後は②のVLOOKUP関数の計算結果のみ値に貼り替えてしまいます。
VLOOKUP関数で100行の商品台帳情報を検索するものが1万行×3列もあるので、さぞかし重い処理になるものと思われましたが、処理時間はなんと1秒もかかりませんでした。
これでは、「マッチング」は危うし！？
では「マッチング」による「売上集計」データの作成を行ないます。
'***************************************************************************************************
'* 処理名 ：③売上集計_マッチング
'* 機能 ：マクロでマッチングによる集計
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年11月29日
'* 作成者 ：井上 治
'* 更新日 ：2020年03月02日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Sub ③売上集計_マッチング()
'-----------------------------------------------------------------------------------------------
Dim objSh1 As Worksheet ' 売上明細
Dim objSh2 As Worksheet ' 商品台帳
Dim objSh4 As Worksheet ' 売上集計②
Dim objStrTime As SYSTEMTIME ' 処理開始時間
Dim lngRow1 As Long ' 行INDEX
Dim lngRow2 As Long ' 行INDEX
Dim lngRow5 As Long ' 行INDEX
Dim lngRowMax As Long ' 行INDEX上限
'---------------------------------------------------------------------------
Set objSh1 = ThisWorkbook.Worksheets(g_cnsSH1)
Set objSh2 = ThisWorkbook.Worksheets(g_cnsSH2)
Set objSh4 = ThisWorkbook.Worksheets(g_cnsSH4)
lngRowMax = g_cnsMaxRow + 1
'---------------------------------------------------------------------------
' 画面描画停止
Call GP_StopScUpd(objStrTime)
'---------------------------------------------------------------------------
' 売上集計②
With objSh4
.Activate
' 元の値をクリア
On Error Resume Next
If .FilterMode Then .ShowAllData
.Cells.Ungroup
On Error GoTo 0
.Rows("2:" & .Rows.Count).ClearContents
'-----------------------------------------------------------------------
' 売上明細を並替え(商品コード順)
objSh1.Range(objSh1.Cells(2, 1), objSh1.Cells(lngRowMax, 4)).Sort _
Key1:=objSh1.Cells(2, 2), _
Order1:=xlAscending, _
Orientation:=xlTopToBottom
'-----------------------------------------------------------------------
' 売上明細から売上集計②へ値を移送
.Range("$A$2:$A$" & lngRowMax).Value = _
objSh1.Range("$A$2:$A$" & lngRowMax).Value ' 店舗
.Range("$C$2:$C$" & lngRowMax).Value = _
objSh1.Range("$B$2:$B$" & lngRowMax).Value ' 商品
.Range("$E$2:$F$" & lngRowMax).Value = _
objSh1.Range("$C$2:$D$" & lngRowMax).Value ' 数量,単価
' 商品台帳の最終行判定(存在する最大値より大きいコードのセット)
objSh2.Cells(102, 1).Value = "9999"
'-----------------------------------------------------------------------
lngRow1 = 2
lngRow2 = 2
' マッチングで転記
Do While lngRow1 <= lngRowMax ' ①
lngRow5 = lngRow1 + 1
' 売上明細上で同一商品コードの最終行を探す
Do While objSh1.Cells(lngRow5, 2).Value = objSh1.Cells(lngRow1, 2).Value ' ②
lngRow5 = lngRow5 + 1
Loop
lngRow5 = lngRow5 - 1
' 対象の商品コードを商品台帳より探す
Do While objSh2.Cells(lngRow2, 1).Value < objSh1.Cells(lngRow1, 2).Value ' ③
lngRow2 = lngRow2 + 1
Loop
' 商品コードが見つかったら(商品台帳に必ずあるならこのIfの行は削除可)
If objSh2.Cells(lngRow2, 1).Value = objSh1.Cells(lngRow1, 2).Value Then ' ④
' 商品台帳より分類,商品名,単価を転記(同一商品全て)
' ※このケースでは1商品当たり平均100行なので1行ずつセットしては遅くなる
.Range(.Cells(lngRow1, 2), .Cells(lngRow5, 2)).Value = _
objSh2.Cells(lngRow2, 2).Value ' 分類 ' ⑤
.Range(.Cells(lngRow1, 4), .Cells(lngRow5, 4)).Value = _
objSh2.Cells(lngRow2, 3).Value ' 商品名
.Range(.Cells(lngRow1, 7), .Cells(lngRow5, 7)).Value = _
objSh2.Cells(lngRow2, 4).Value ' 原単価
End If
lngRow1 = lngRow5 + 1 ' ⑥
Loop
'-----------------------------------------------------------------------
' 横合計の計算式をセット
.Range("$H$2:$H$" & lngRowMax).FormulaR1C1 = "=RC5*RC6" ' 売上額
.Range("$I$2:$I$" & lngRowMax).FormulaR1C1 = "=RC5*RC7" ' 原価額
.Range("$J$2:$J$" & lngRowMax).FormulaR1C1 = "=RC8-RC9" ' 粗利
' 再計算
.Calculate
Application.Goto .Cells(2, 1), True
End With
'---------------------------------------------------------------------------
' 画面描画再開
Call GP_StartScUpd(objStrTime)
End Sub
| № | 概略説明 |
|---|---|
| ① |
マッチングのループの開始です。この時点では売上集計、商品台帳ともに先頭行(2行目)が参照された状態で開始します。 |
| ② |
このサンプルのケースでは、1万行の明細に対して商品は100アイテムしかないため1商品あたり平均100行分の転記が発生することになります。1行ずつ100回転記するのと、100行に1回転記するのでは明らかに処理時間が異なるため、予め同一商品コードの最終行を判定しておきます。 |
| ③ |
ここがマッチングの「要」の部分です。マッチングでは予め比較するキー(ここでは商品コード)の昇順に並んでいるのが前提なので、1件ごとに「商品台帳」の先頭から探すことはしません。直前に参照されていた位置(行)から後ろに向かって参照したいコード値より小さいものを読み飛ばすだけです。 このサンプルでは、「売上明細」が1万件もあるので、読み飛ばされる「商品台帳」はほとんどないでしょう。 |
| ④ |
ここでは、③で見つかった「商品台帳」が同一キーなのかを確認しています。「商品台帳」に存在しない商品コードが使われることがないと保証できるシステムであれば、このIf判断の行は省略しても構いません。 |
| ⑤ |
ここでは同一商品コードの行範囲に対して、1回で分類、商品名、原単価を転記します。このサンプルでは100アイテムですから、転記回数は最大100回です。転記されるのは最初から値なので、後から値に貼り替える必要がありません。 |
| ⑥ |
比較参照する「売上集計」の行を先ほど判定した同一商品コードの最終行の次行に変更します。 |
処理結果はごらんのように、計算式の場合より若干速い結果となりました。
僅差ですが、何とか面目を保った結果です。
※マッチングのコード組み立ての考え方を「仕様要件からコードの組み立てを考える。」で説明しています。
では、「集計」機能を使って2つの処理結果を照合して見ます。店舗、分類単位で集計を行ないます。
'***************************************************************************************************
'* 処理名 ：④処理結果の検証
'* 機能 ：処理結果の検証(①、②の結果が一致しているか→集計で確認)
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年11月29日
'* 作成者 ：井上 治
'* 更新日 ：2020年03月02日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Sub ④処理結果の検証()
'-----------------------------------------------------------------------------------------------
Dim objSh3 As Worksheet ' 売上集計①
Dim objSh4 As Worksheet ' 売上集計②
Dim objStrTime As SYSTEMTIME ' 処理開始時間
Dim lngRowMax As Long ' 行INDEX上限
'---------------------------------------------------------------------------
Set objSh3 = Worksheets(g_cnsSH3)
Set objSh4 = Worksheets(g_cnsSH4)
lngRowMax = g_cnsMaxRow + 1
'---------------------------------------------------------------------------
' 画面描画停止
Call GP_StopScUpd(objStrTime)
'---------------------------------------------------------------------------
' 売上集計①
With objSh3 ' ①
' 並替え(店舗､分類､商品ｺｰﾄﾞ順)
.Range(.Cells(2, 1), .Cells(lngRowMax, 10)).Sort _
Key1:=.Cells(2, 1), Order1:=xlAscending, _
Key2:=.Cells(2, 2), Order2:=xlAscending, _
Key3:=.Cells(2, 3), Order3:=xlAscending, _
Orientation:=xlTopToBottom
' 集計処理(店舗､分類別)
.Range(.Cells(1, 1), .Cells(lngRowMax, 10)).Subtotal _
GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9, 10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
lngRowMax = .Range("$A$" & .Rows.Count).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(lngRowMax, 10)).Subtotal _
GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 9, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End With
'---------------------------------------------------------------------------
' 売上集計②
With objSh4 ' ②
' 並替え(店舗､分類､商品ｺｰﾄﾞ順)
.Range(.Cells(2, 1), .Cells(lngRowMax, 10)).Sort _
Key1:=.Cells(2, 1), Order1:=xlAscending, _
Key2:=.Cells(2, 2), Order2:=xlAscending, _
Key3:=.Cells(2, 3), Order3:=xlAscending, _
Orientation:=xlTopToBottom
' 集計処理(店舗､分類別)
.Range(.Cells(1, 1), .Cells(lngRowMax, 10)).Subtotal _
GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9, 10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
lngRowMax = .Range("$A$" & .Rows.Count).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(lngRowMax, 10)).Subtotal _
GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 9, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End With
'---------------------------------------------------------------------------
' 画面描画再開
Call GP_StartScUpd(objStrTime)
End Sub
どちらも店舗・分類・商品コードで並び替えて、集計を行なう方法で手作業でもできることですが、統一性を計るためにマクロ化したものです。
一番最後の当たりだけを表示していますが、行数も一致していますし、集計結果も一致しています。
この処理結果から判断できることは、VLOOKUP関数を行列含めて3万カ所に貼った割にはかなりの処理速度が得られたということが挙げられます。ここではマクロを使っていますが、この結果はVLOOKUP関数の計算式が解っていれば手作業で出来うる範囲のことです。
但し、商品台帳側の件数が多くなる場合は、「マッチング」でのマクロの処理とここまで僅差ではいられないであろうと思われます。1回処理して結果を出せば終わりという場合であれば、マクロを作ることが無駄に見えてしまう結果とも言えます。
あと、計算式を値に貼り替えず放置するとかなりパフォーマンスとファイルサイズに影響が出るので注意が必要です。VLOOKUP関数の計算式をそのまま残すと、最終の集計処理の処理時間は100秒と4倍近くの時間を要したことと、ファイルサイズも1.5倍になりました。
※本項の説明をご覧になった人から指摘があった点について説明を追加しておきます。
[指摘事項１]
計算式では商品台帳の参照はエラー処理がないが、マクロでは発見できたかの判断記述がある。
これは、異論があるかも知れませんが、計算式は照合の型が「FALSE」としており、参照ができない場合は「#N/A」となります。一方、マクロの方でIfの判断を外してしまうと、参照ができない場合に間違った結果が転記されてしまうので、公平でないと考えています。
[指摘事項2]
商品台帳にコードが存在する前提であれば、各項目でVLOOKUP関数で参照するのは不効率であり、作業列でMATCH関数で参照を完了させ、各列ではINDEX関数で行を確定した状態で参照する方が速い。
3項目程度であれば、各項目でVLOOKUP関数で参照する方が速いだろうと判断していましたが、その検証は怠っていましたので、ここで検証してみることにします。
VLOOKUP関数だけの場合と、MATCH・INDEX関数の組み合わせとの速度比較。
サンプルをここからダウンロードして、上記同様に比較してみて下さい。「売上集計②」の方が2割ほど速い結果となりました。
さらに、商品台帳のヒットを前提とするMATCH関数での速度比較。
上記より、さらに2割ほど速い結果となりましたが、商品台帳にそのコードが必ず存在することを前提としており、存在しない場合は間違った結果を返します。(#N/Aにならない)