実践編1:複数ファイルを集計する

今回は簡単な実践編です。下記のような売り上げデータの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にご連絡ください。

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

Excelの行データを列名で参照できるようにする。

前回はExcelの列を列名（カラム名）で指定するようにしました。今回はさらに各行のデータも列名で指定できるようにしましょう。

option Explicit
Dim fso, ExcelApp,  args
const xlToLeft = -4159
const xlUp = -4162
Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
set args = WScript.Arguments
ReadExcelFile  args(0)
ExcelApp.Quit
WScript.Quit

Sub ReadExcelFile( filename )
  dim obook, ofile, row, oSheet,maxrow,telnum, schema, oneLine
  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
    telNum= oneLine.item("電話番号")
     if Left(telNum,3) = "03-" then
       WScript.echo oneLine.item("名前")
     end if
  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 ファイル名
と入力します。
プログラムを解説します。
前回と差分は
set oneLine = CreateObject("Scripting.Dictionary") '列名-データ
と
ParseLineExcel oSheet, row, schema, oneLine
が追加され、前回は
telNum= oSheet.Cells(row, schema.item("電話番号")).value
だったのが
telNum= oneLine.item("電話番号")
に変わっています。そして最後にParseLineExcel( oSheet, row, schema, oneline )の処理が追加されています。
この処理はrow行を読み取って、onlineというディクショナリにキー：列名、item:データ値を入れるというものです。通常中身を細かく理解する必要はありません。ライブラリーだと思ってそのまま追加してください。
これによって、ReadExcelFile処理の中では直接セルを指定する必要がなくなり、データ処理がわかりやすくなります。
このテンプレートは”Excelファイルを引数に、列を指定してデータを読み出し処理を行う”というパターンでは、ReadExcelFileの中のforループの中だけを変更するだけで、広く使えるものですので是非ご活用ください。
今回はここまで。

Excelの列を列名で指定する。

今回よりExcel/CSV操作のTIPSの紹介をしていきます。
ExcelのセルはB列10行目といった、アルファベットと行数で表現されています。ただ、アルファベットはプログラムで扱うのが非常に面倒なので、通常プログラムから参照するときは列も数字にして"Cells(行番号,列番号).value"といった形で参照します。
例えば、

名前	郵便番号	住所	電話番号
村松	200-0000	東京都港区	03-xxx-xxx
山田	210-0000	神奈川県川崎市	044-xxx-xx

のようなExcelファイルがあったとします。このうち電話番号が03で始まるユーザーの名前のリスト(テキストファイル）を作りたいとします。
Excelで開いてフィルタ機能を使えば簡単に絞り込めますが、そのうちの名前だけのテキストファイルを作るとすると、ひと手間かかりますし、ファイルがたくさんあったらExcelだけでやるのは結構な手間ですよね。
これを普通のプログラムで行う場合は、前回のプログラム中、ReadExcelFileの中だけを変更した場合

Sub ReadExcelFile( filename )
  set ofile = fso.getFile( filename )
  set oBook = ExcelApp.Workbooks.Open(ofile.path) 
  set oSheet = oBook.WorkSheets(1)
  maxrow =  oSheet.Cells(oSheet.Rows.count,1).end(xlUp).row
  for row = 1 to maxrow
     telNum = oSheet.Cells(row,4).value
     if Left(telNum,3) = "03-" then
         WScript.echo oSheet.Cells(row,1).value
     end if
  next
  oBook.close
end sub

のようになります。結果はコンソールの標準出力に出ますので、これを”＞userlist.txt"のようにリダイレクトでテキストに出力すれば、目的のテキストファイルが作れます。
しかし、このプログラムの場合、条件として
　①１列目が名前であることを事前に知っている
　②４列目が電話番号であることを事前に知っている
　③列が変更されない
が条件になります。もし、何らかの事情で列が変更されると、その度にプログラムを変更しなければなりません。今回は対象の列が二つだけですが、処理が複雑になれば変更量が膨大になってしまいます。プログラムの仕様変更の多くは、データの事情が変わったことによることが多いのです。
さらに、同じ処理(電話番号を条件に名前を出力する）を複数のExcelファイルで連続して行いたいが、ファイルによって列の並びが違うものが混じっていたら、そのプログラムでは対応できません。
そこで今回は列の指定を、より柔軟にできる方法を紹介したいと思います。

option Explicit
Dim fso, ExcelApp,  args
const xlToLeft = -4159
const xlUp = -4162
Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
set args = WScript.Arguments
ReadExcelFile  args(0)
ExcelApp.Quit
WScript.Quit

Sub ReadExcelFile( filename )
  dim obook, ofile, row, oSheet,maxrow,telnum, schema
  set ofile = fso.getFile( filename )
  set schema = 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
    telNum= oSheet.Cells(row, schema.item("電話番号")).value
     if Left(telNum,3) = "03-" then
       WScript.echo oSheet.Cells(row, schema.item("名前")).value
     end if
  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

使い方は前回同様
>cscript プログラム名 Excelファイル名 > userlist.txt
です。
今回は変数の宣言を入れたので、ややプログラムが長くなっています。10行目までのメインルーチンは何も変わっていません。
このプログラムの特徴は、ReadExcelFile手続きの中で列番号を直接指定していないことです。列番号の代わりに”schema.item("電話番号")のように列の名前で指定しています。このschemaは
set schema = CreateObject("Scripting.Dictionary") '列名-列番号
でDictionary(ハッシュテーブル)の変数です。これはキーと、値のセットを蓄えられるデータベースのテーブルのような構造を持っています。
このschemaに
　キー：列名、値：列番号
のようなデータを格納しておき、参照するときには列名を指定すると、列番号が返されるようにしたのです。
どこでそれをやっているかというと
ParseSchemaExcel oSheet, 1, schema 'ヘッダー行の解析
です。このParseSchemaExcelという手続きは、Excelシートの1行目を読んで、列名をよみとり、その列番号をitemに保存するという処理をしています。
それがSub ParseSchemaExcel(oSheet, row , schema)ブロックです。中身は１０行しかない簡単な処理ですが、中身を理解する必要はありません。
標準ライブラリーのように、そういうことをしてくれる関数ぐらいに覚えておけば十分です。通常、実際に利用するスクリプトを作るときに、この処理の中をいじることはありません。
このプログラムの特徴は、実際のExcelの列名から列番号を１ファイルごとに動的に読み取って処理をしますから、あらかじめ電話番号が何列目かも、名前が何列目かも知る必要がありません。１列名だろうが、87列目だろうがプログラムを作る負荷は同じです。（列を個別で指定する場合か”DA”列は何列目かなどを数えたりしました）
また、ファイルごとに列の配置が異なっていたとしても同一のプログラムで一括処理が可能です。
Excelの表を操作するプログラムでは、この一工夫だけでも相当にプログラミングの手間を減らし、バグの削減に効果を発揮すると思います。
今回はここまで

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

まずはExcel読み出しの基本から

今回からコードの説明に入ります。申し訳ありませんが、本ブログはVBS/VBAの文法解説ではないので、そこまでは検索サイトで”VBScript 入門”といったキーワードで検索ください。
まずはVBSからMicrosoft Excel(r)(以後Excelと呼びます）を呼び出す基本パターンを紹介します。下記のプログラムをテキストファイルにコピーして、拡張子を.vbsにしてください。
ファイル名はなんでもかまいません。

Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
const xlUp = -4162
set args = WScript.Arguments

ReadExcelFile  args(0)
ExcelApp.Quit
WScript.Quit

Sub ReadExcelFile( filename )
  set ofile = fso.getFile( filename )
  set oBook = ExcelApp.Workbooks.Open(ofile.path) 
  set oSheet = oBook.WorkSheets(1)
  maxrow =  oSheet.Cells(oSheet.Rows.count,1).end(xlUp).row
  for row = 1 to maxrow
    WScript.echo oSheet.Cells(row,1).value
  next
  oBook.close
end sub

次にコマンドプロンプトを開き、.vbsを保存したフォルダに移動(cd <パス>)します。
そして
>cscript プログラム名.vbs Excelファイル名
と実行すると、Excelファイルの1列名（A列）の情報が書き出されるはずです。
もちろんこれだけではただ”あーそうか”というだけで何の価値もありませんが、まだ基礎ですので我慢願います。回を重ねるごとにだんだん価値が出てきますので
では解説です。
１行目：Set fso = CreateObject("Scripting.FileSystemObject")
ファイル操作を行う標準オブジェクト（Scripting.FileSystemObject)を作成しています。
2行目：Set ExcelApp = CreateObject("Excel.Application")
Excelを呼びだすためのオブジェクトを作成しています。
３行目：const xlUp = -4162
定数を定義しています。Excelの最終行を意味する値です。
４行目：set args = WScript.Arguments
プログラムを実行する際の、引数を操作するオブジェクトを作成しています。
６行目：ReadExcelFile args(0)
Excelを呼び出す手続きである”ReadExcelFile”を呼び出しています。引数にExcelのファイル名をわたしています。
７行目：ExcelApp.Quit
Excelを終了しています。
８行目：WScript.Quit
プログラムを終了しています。

次にExcelを読み出す本体の手続きを解説します。
１０行目：Sub ReadExcelFile( filename )
手続きの始まりの宣言です。引数がFilenameという変数に入ります。
１１行目：set ofile = fso.getFile( filename )
Excelファイルを探して、ファイルオブジェクトに保存しています。
１２行目：set oBook = ExcelApp.Workbooks.Open(ofile.path)
Excelファイルを開いて、Bookオブジェクトを作成しています。ofile.pathはExcelファイルのフルパスです。
１３行目： set oSheet = oBook.WorkSheets(1)
Excelの一番左（最初）のシートを示すオブジェクトを作成しています。
１４行目： maxrow = oSheet.Cells(oSheet.Rows.count,1).end(xlUp).row
最終行の行番号をとりだしてmaxrowに保存しています。
１５行目： for row = 1 to maxrow
行を一行づつループで回します。
１６行目：WScript.echo oSheet.Cells(row,1).value
Row行、１列の値をコンソール（標準出力）に出力しています。１を２にすれば２列目になります。
今回は直接出力していますが、当然変数に一時的に保存して加工することも可能です。
１７行目： next
ループの終わりです。
１７行目： oBook.close
Excelを閉じます。（これをしないと画面で見えなくともExcelファイルが開きっぱなしになります。）
１８行目：end sub
手続きの終わりです。

ここまではExcelファイルをVBSから読み出す標準手続きであり、何も工夫はありません。ただ、VBAしか触ったことのない方はVBSからExcelファイルを読み出す方法がわからないかと思い、前段としてこの回を設けました。
今後の紹介は、すべて今回のスクリプトがベースになりますので、理解しておいてください。なお、今回はわかりやすさを重視して変数宣言等を省略しています。

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

Microsoft Excelと日々格闘しているオフィスワーカーに向けて

中小企業診断士の村松です。

普段はシステムインテグレーターとして、お客様の情報化支援を行っています。そうしたなかで、システムにかかわる様々な情報を集計したり、分析したり、報告書にまとめたりといったExcelにからむ業務が日々大量にあります。しかし、多くのデータは最初から定型のExcelのデータとして存在しているわけではなく、システムから吐き出されたCSVファイルだったり、お客様が管理しやすいように加工されたExcelだったり、毎月生成される大量のデータだけのExcelファイルだったりします。

普段Excelを使い慣れているかたはご存じでしょうが、一度定型の一つのExcelにデータとしてまとめられれば、それ以降の加工や集計はExcelの機能の独壇場です。マクロはあるし、関数はあるし、ちょっと凝った処理ならVBAでプログラムもありです。しかし、形式や形態のバラバラなデータを、一つ一つExcelに取り込んで加工して、連携させて、また加工してというのは、非常に手間のかかる作業です。もちろん毎回同じプロセスを繰り返すのであれば、Excelを駆使して自動化は可能かもしれません。しかし、Excelのプログラム(VBA)や関数はExcelの中の話なので、仕組みを埋め込んだExcelを開いてそこから始めなければなりません。

一度自動化の仕組みをVBAで組み込んだとしても、列が増えたり、移動したりすると、その書き換えはプログラムが高度であればあるほど難しくなるというジレンマを抱えます。

わたしは、この精神衛生上、非常によろしくない環境から脱すべく、VBSを使った簡易集計テンプレートを作りました。VBS(Visual Basic Script)は、VBAと言語体系は同じですがExcelの中に埋め込むのではなく、独立したスクリプトプログラム（テキストに書かれたプログラムを直接実行するかたち）として実行するものです。このスクリプトプログラムからExcelをリモートコントロールする方法で、VBAと同じ処理を自由に呼び出してExcelを読み出したり、生成したりすることができます。また、テキストファイルの処理も自在にできますので、CSVも同時に扱うことができます。

とはいえ、これだけではVBAを外だししただけで、Excelのフォームの変更でメンテナンスの手間が複雑なのは変わりません。そこで、VBS/VBAでよくつかわれるDictionaryを駆使した、Excelの簡易操作テンプレートにまとめ、だれでも数十分程度で高度な加工ができるようにしたのです。

本ブログでは、そこで蓄積したExcel加工やCSV操作を容易にするTIPS群を紹介していきたいと考えています。

ちなみに、そんなまどろっこしいことは抜きにして、最終形のテンプレートが欲しいという方は<Support@Info-Advisor.com>にメールをください。