中小企業情報化塾

Facebook Youtube

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の米国およびその他の国における登録商標または商標です。

中小企業の情報化を支援します。