中小企業情報化塾

今回は簡単な実践編です。下記のような売り上げデータの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ファイルが保存されている想定)
プログラムを解説します。
変数宣言以外で変更したのは上記の”変更1”と”変更2”の部分(併せて25行)だけです。トータルでも72行です。
テンプレートやロジックに慣れてくれば、お題が出されてから、実装デバッグして完成まで1時間未満で完成するでしょう。
簡単に全体のロジックを解説します。ポイントはSalesというハッシュテーブル(Dicrionary)です。
ここには“キー:顧客名、データ:出現回数,合計金額”というデータを保存しています。
変更1はフォルダ名を引数で指定して、その中のファイルのパスと、SalesデータをReadExcelFileにわたし、読み込んだデータをSalesに蓄積しています。
すべてのファイルのデータを読み終わったら、顧客名、来店回数、平均金額(総額を来店回数で除したもの)を出力しています。
変更2は1行ごとに顧客名と利用金額を取得して、salesに保存された顧客名を調べて、登録されていれば来店回数と総額を加算し、なければ新規に登録しています。
これだけのロジック追加でプログラムが完成です。
似たような集計ニーズは様々な場面であると思います。是非テンプレートとして活用してみてください。
上記サンプルは解説用に細かい実践向けのコード(エラー処理等)を省略しています。実践用のテンプレートを希望される方はsupport@info-advisor.comにご連絡ください。

*Excelは、米国Microsoft Corporationの米国およびその他の国における登録商標または商標です。