スモールデータ集計の技

VBSを使ってExcelやCSVを操作し、ハイレベルなデータ集計技をお伝えします。

ディクショナリを使った集計データの保持

今回のテーマ

Excelデータを読み込む際に、解析データをスクリプト内で保持するパターンを紹介します。

お題

次ようなExcelファイルがフォルダ内に大量にあるものとします。
No.日時顧客名顧客属性都道府県利用金額
12020/10/4"住処商事"xxxxxx東京1300
22020/10/25"香料販売"xxxxxx神奈川14340
32020/10/25"Satoru co., Ltd"xxxxxx東京2340
.......................
このExcelファイルを読み取って、顧客別の利用金額の合計値と、東京に拠点のある顧客リストを作成してみましょう。

サンプルスクリプト

option Explicit
Dim fso, ExcelApp,  args, f, oFolder, ofile, TotalAmount, TokyoCustomer
const xlToLeft = -4159
const xlUp = -4162
const ExcelHeaderLine = 1   'ヘッダー行
const ExcelColHeader = 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
set TotalAmount = CreateObject("Scripting.Dictionary")  '結果を保存するデータ
set TokyoCustomer = CreateObject("Scripting.Dictionary")  '結果を保存するデータ
 
set args = WScript.Arguments
set oFolder = fso.GetFolder( args(0))
for each ofile in oFolder.Files
    ReadExcelFile ofile.path, TotalAmount, TokyoCustomer
next
WScript.echo "【顧客別合計金額】"
DisplayResult TotalAmount, "顧客名,合計金額"     'データの中身を表示する。
WScript.echo 
WScript.echo "【東京に拠点のある顧客リスト】"
DisplayResult TokyoCustomer, "顧客名,1"     'データの中身を表示する。
 
ExcelApp.Quit
WScript.Quit
 
'---------------------------------------------------
' ReadExcelFile
' Excelシートの中身を読み出す(1ファイル)
'  "名前"列をキーに、値は固定値(1)を代入 → 目的に応じて変更
'---------------------------------------------------
Sub ReadExcelFile( filepath, TotalAmount,TokyoCustomer  )
  dim obook, ofile, row, oSheet,maxrow, schema, oneLine, UserName, col
  dim prefecture, amount
  if fso.FileExists( filepath ) then
      set ofile = fso.getFile( filepath )
      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,ExcelColHeader).end(xlUp).row
      ParseSchemaExcel oSheet, ExcelHeaderLine, ExcelColHeader, schema   
      for row = ExcelHeaderLine + 1 to maxrow
        ParseLineExcel oSheet, row, schema, oneLine  
        'ロジックエリア 開始
         UserName= oneLine.item("顧客名")
         prefecture= oneLine.item("都道府県")
         amount= oneLine.item("利用金額")
        Dict_SumNumeric  TotalAmount,UserName, amount    
         if prefecture = "東京" then
           Dict_IfNotAdd  TokyoCustomer, UserName, 1    
        next
      oBook.close                                      'Excelファイルを閉じる
  else
     WScript.echo "Can't file " & filepath
  end if
end sub
 
'---------------------------------------------------
' Dict_IfNotAdd
' Dictionaryでもしキーがなければ追加する。
'---------------------------------------------------
Sub Dict_IfNotAdd( oData, key, avalue )
   if not oData.Exists( key ) then
     oData.Add key, avalue
   end if
end sub
 
'---------------------------------------------------
' Dict_SumNumeric
' Dictionaryでもしキーがなければ追加し、あれば加算する。
'---------------------------------------------------
Sub Dict_SumNumeric( oData, key, avalue )
  if oData.exists( key ) then
     oData.item( key ) = oData.item( key) + CDbl(avalue)
  else
    oData.Add key, CDbl(avalue)
  end if
end sub
 

'---------------------------------------------------
' ParseSchemaExcel
' ヘッダー列(列名)を読み取り、schemaディクショナリに格納する。
'  基本はいじらない。
'---------------------------------------------------
Sub ParseSchemaExcel(oSheet, row, colline, schema)
  dim col, maxcol
  schema.removeall
  maxcol = oSheet.Cells(row,oSheet.Columns.count).end(xlToLeft).column
  for col = colline to maxcol
    if oSheet.cells( row, col ).value <> "" then
      schema.add Trim(oSheet.cells( row, col ).value), col
    end if
  next
end sub
 
'---------------------------------------------------
' ParseLineExcel
' データ列を読み取り、列名と関連付けてDictionaryに格納する。
'  基本はいじらない。
'---------------------------------------------------
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
 

使い方は
 >cscript <スクリプトファイル名>
です。コンソールに保持データが出力されます。

解説

ExcelCSVのデータの”読み取り→出力”のパターンで圧倒的に多いのは
 ①特定のキーの値を合計する。(例:顧客別利用額合計)

 ②条件に合ったキーのリストを作る
です。これらをvbsのディクショナリーデータで保持する場合、
 ① キー(顧客名など):値(利用金額など)
および
 ②キー(顧客名):値(なんでもよい)
といった保持になります。処理のロジックは
 ①顧客名が既存のキーになければ、”キー:顧客名(username)、値:利用金額(value)”でレコードを追加する
  もし既存にあれば、利用金額を加算する。という処理になります。

if Data.exists( username) then
      Data.item( username) = Data.item(username) + value
else
      Data.Add username, value
end if

および
 ②条件にあった場合、顧客名が既存のキーになければ、”キー:顧客名(username)、値:なんでもよい”でレコードを追加する

if not Data.exists( username) then
      Data.Add username, ""
end if

となります。これらはそれぞれ典型的なパターンであり、上記を毎回(プログラムを作るたび)書くのは無駄なので、テンプレートでは関数化しています。
①のパターンは
   Dict_SumNumeric ディクショナリー, キー, 値(数値)
②のパターンは
   Dict_IfNotAdd ディクショナリー、キー、値(文字列でもなんでもよい)
として用意しています。これであればプログラム作成時の変更量が大幅に削減され、バグも出にくくなります。今回の場合は、データがふたつになった以外のロジックの記述は

 UserName= oneLine.item("顧客名")
 prefecture= oneLine.item("都道府県")
 amount= oneLine.item("利用金額")
 Dict_SumNumeric  TotalAmount,UserName, amount 
 if prefecture = "東京" then
    Dict_IfNotAdd  TokyoCustomer, UserName, 1    
 end if

この7行だけです。あとは、内部保持したデータを標準出力に表示する部分を追加してプログラムは終了です。
このパターンは結構多いので、テンプレートとして活用いただければ幸いです。

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