今回は簡単な実践編です。下記のような売り上げデータのExcelファイルが月ごとにフォルダの中に保存されています。
| No. | 日時 | 顧客名 | 顧客属性 | 来店種別 | 利用金額 |
|---|---|---|---|---|---|
| 1 | 2020/9/4 | 村松 | xxxxxx | xxxx | 1500 |
| 2 | 2020/9/25 | 山田 | xxxxxx | xxxx | 5340 |
| … | …. | …. | …. | …. | …. |
フォルダ内のすべてのファイルを読んで、すべての顧客ごとの来店回数と、平均金額を表(CSV)にしてください。
ファイルは月ごとに作られています。集計する列名（顧客名、利用金額）以外の列名は不明です。何列目かもわかりません。同じ顧客がひと月に何度も来店します。
月ごとに微妙に列が変わっていることもあります。
さてこのプログラムを作るのに、一般的にはどのくらいの時間がかかるでしょうか？
前回までのテンプレートをもとに作成したプログラムを紹介します。
option Explicit Dim fso, ExcelApp, args, oFolder, oFile, customer, arr dim sales const xlToLeft = -4159 const xlUp = -4162 Set fso = CreateObject("Scripting.FileSystemObject") Set ExcelApp = CreateObject("Excel.Application") set args = WScript.Arguments '----------------変更1ここから---------------------------- set Sales = CreateObject("Scripting.Dictionary") set oFolder = fso.GetFolder( args(0) ) for each ofile in oFolder.Files if right(ofile.name, 5) = ".xlsx" then ReadExcelFile oFile.path, sales end if next ExcelApp.Quit WScript.echo "顧客名,来店回数,平均金額" for each customer in sales.keys arr = split( sales.item( customer), "," ) WScript.echo customer & "," & arr(0) & "," & (arr(1) / arr(0)) next '-----------------変更1ここまで------------------------------- WScript.Quit Sub ReadExcelFile( filename, sales) dim obook, ofile, row, oSheet,maxrow, customer, schema, oneLine, arr, amount set ofile = fso.getFile( filename ) set schema = CreateObject("Scripting.Dictionary") '列名-列番号 set oneLine = CreateObject("Scripting.Dictionary") '列名-データ set oBook = ExcelApp.Workbooks.Open(ofile.path) set oSheet = oBook.WorkSheets(1) maxrow = oSheet.Cells(oSheet.Rows.count,1).end(xlUp).row ParseSchemaExcel oSheet, 1, schema 'ヘッダー行の解析 for row = 2 to maxrow ParseLineExcel oSheet, row, schema, oneLine '----------------変更2ここから---------------- customer= oneLine.item("顧客名") amount = oneLine.item("利用金額") if amount <> "" and customer <> "" then if sales.exists( customer ) then arr = split(sales.item( customer ),",") arr(0) = arr(0) + 1 arr(1) = CDbl(arr(1)) + CDbl(amount) sales.item( customer ) = join( arr, "," ) else sales.add customer, "1," & amount end if end if '---------------変更2ここまで------------------- next oBook.close end sub Sub ParseSchemaExcel(oSheet, row , schema) dim col, maxcol schema.removeall maxcol = oSheet.Cells(1,oSheet.Columns.count).end(xlToLeft).column for col = 1 to maxcol if oSheet.cells( row, col ).value <> "" then schema.add Trim(oSheet.cells( row, col ).value), col end if next end sub Sub ParseLineExcel( oSheet, row, schema, oneline ) dim key oneline.removeall for each key in schema.keys oneLine.add key, oSheet.Cells( row, schema.item(key) ).value next end Sub
実行方法は、上記をテキストファイルに転記して、ReadExcel.vbsといった名前のファイルに保存し、コマンドプロンプトから
cscript ReadExcel.vbs フォルダ名
と入力します。(フォルダの下に、Excelファイルが保存されている想定）
プログラムを解説します。
変数宣言以外で変更したのは上記の”変更１”と”変更２”の部分(併せて25行)だけです。トータルでも72行です。
テンプレートやロジックに慣れてくれば、お題が出されてから、実装デバッグして完成まで１時間未満で完成するでしょう。
簡単に全体のロジックを解説します。ポイントはSalesというハッシュテーブル（Dicrionary)です。
ここには“キー：顧客名、データ：出現回数,合計金額”というデータを保存しています。
変更１はフォルダ名を引数で指定して、その中のファイルのパスと、SalesデータをReadExcelFileにわたし、読み込んだデータをSalesに蓄積しています。
すべてのファイルのデータを読み終わったら、顧客名、来店回数、平均金額（総額を来店回数で除したもの）を出力しています。
変更２は１行ごとに顧客名と利用金額を取得して、salesに保存された顧客名を調べて、登録されていれば来店回数と総額を加算し、なければ新規に登録しています。
これだけのロジック追加でプログラムが完成です。
似たような集計ニーズは様々な場面であると思います。是非テンプレートとして活用してみてください。
上記サンプルは解説用に細かい実践向けのコード(エラー処理等)を省略しています。実践用のテンプレートを希望される方はsupport@info-advisor.comにご連絡ください。