Excel VBA

Excelファイルが開かない…

社内SEとして働いている間、けっこうな割合で出会ったのがこの案件です。

「最近、ファイルが開くのにすごく時間がかかって…」とヘルプを求められ、対象のファイルを見ると、6MBとか、8MBとか、とにかく大きなファイルになってしまっています。

動画ファイルなどが増えている昨今、8MBは小さいように見えますが、EXCELファイルとしてはかなり巨大な部類になります。

これらの原因の多くが、便利なので使ってしまうセルや行のコピーにあります。

Excelには実に便利な関数が非常に多く用意されています。
そして、よくこの肥大するファイルで使用されている関数にVLOOKUPがあります。

 =VLOOKUP([参照セル],[検索範囲],[表示する列番号], [検索の型]) =VLOOKUP(B3,'[kakaku.xlsx]Sheet1'!$A$1:$B$5,2,FALSE)  

2行目の例は、自身のB3セルの値で、kakaku.xlsxにあるSheet1のA1:B5(絶対領域指定)を検索して、見つかった場合は、B列の値を表示しなさいという意味になります。

非常に簡単なVLOOKUPの例ですが、3つのシートからデータを持ってきて、集計表を作成する例になります。

こうすると、簡単に元のシートから必要なデータを拾い出す事ができます。上記の例では、重ね表示してある一番手前のシートは、VLOOKUP関数で自身の右3枚のシートからデータを取り出してまとめて表示しています。

何等かの出力方法で、価格や原価などを別々に取り出すというのはよく聞く仕様ですので、それっぽくしてみました。原価と仕入数のデータをそれぞれ取り出し、どこから合計仕入金額幾らで入荷したかの表のようなものになります。

当然ですが、価格のシートの値を変えれば、VLOOKUPの再計算で最新の値を簡単に拾い出せます。

こうして、Excelシートは簡易データベースへと進化していく訳です。

ところが、便利なこの式が、簡単に行ごとコピー出来てしまうところに罠があります。

上の例は5行しかなく、20キロバイトのファイルですが、もしこれが100行、いえ、1000行もいったらどうなるでしょう?
実際、上記のような5行程度なら、データを手作業でコピー&ペーストしてもそれほど苦ではありませんが、複数のファイルから、数百~千行のデータを、毎日人力で一つのファイルにコピー&ペーストするというのは大変です。
そうした扱いの大変な大量のデータを扱おうとするから、VLOOKUPが必要になってくるので、ブック全体でVLOOKUPが4000個あるなど、それほど珍しくないのが実情です。

当然ですが、Excelは4000個のVLOOKUPを書かれたシートを読み込むと、その全ての計算を律儀に行います。その結果、あっという間にメモリやCPUを使い切り、どんどん処理が遅くなっていってしまいます。

こうした場合、処理の一部をVBA化する事で解決が可能です。
VBAは、「Visual Basic for Applications」の略で、MicrosoftOfficeの各製品に内蔵されているプログラミング言語です。
今ではあまり互換性が無くなってしまいましたが、Visual Basicと同じ文法で処理を記述する事が可能な、本格的なプログラミング言語になります。

プログラム言語なので、他のシートを開いたり、様々な処理を加えたりできます。

例えば、上記のVLOOKUPと同じような事が出来るVBAだと、次のような例になります。

Dim wkb00, wkb01, wkb02, wkb03 As Workbook
Dim wsh00, wsh01, wsh02, wsh03 As Worksheet
Dim Rag00, Rag01, Rag02, Rag03 As Range

Dim dbfm As String
 Dim n As Integer
Set wkb00 = ThisWorkbook
dbfm = ThisWorkbook.Path
dbfm = dbfm & "\"

Set wsh00 = wkb00.Worksheets(1)

Set wkb01 = Workbooks.Open(dbfm & "kakaku.xlsx")
Set wsh01 = wkb01.Worksheets(1)
Set wkb02 = Workbooks.Open(dbfm & "nyuuka.xlsx")
Set wsh02 = wkb02.Worksheets(1)
Set wkb03 = Workbooks.Open(dbfm & "orosi.xlsx")
Set wsh03 = wkb03.Worksheets(1)
For n = 1 To 5

  Set Rag00 = wsh00.Cells(n + 2, 2) '品名
  Set Rag01 = wsh01.Cells(n, 1)
  Rag00.Value = Rag01.Value


 Set Rag00 = wsh00.Cells(n + 2, 3)
'単価
  Set Rag01 = wsh01.Cells(n, 2)
  Rag00.Value = Rag01.Value

  Set Rag00 = wsh00.Cells(n + 2, 4)
'仕入
  Set Rag02 = wsh02.Cells(n, 2)
  Rag00.Value = Rag02.Value

  Set Rag00 = wsh00.Cells(n + 2, 5)
'金額
  Rag00.Value = Rag01.Value * Rag02.Value


  Set Rag00 = wsh00.Cells(n + 2, 6)
'取引先
  Set Rag03 = wsh03.Cells(n, 2)
  Rag00.Value = Rag03.Value

Next 

kakaku.xlsx、nyuuka.xlsx、orosi.xlsx のEXCELファイルを自動で開き、データを取りだして表示していきます。
ついでに、金額の部分もこのVBA中で計算してしまっています。

5行程度のデータですと、これを実装してもそう変わりませんが、数百、数千になってくると、一つ一つのセルで計算するvlookupに比べ、ブログラム中でデータ処理を計算をしてしまうVBAとでは差が歴然となります。
容量も小さくなり、開く速度も格段に速くなります。

もちろん、これらのデータ元がCSVファイルでも、少し内容を変えるだけで、対応可能。その他にも、逆に纏まったデータをCSV化したりなど、様々な業務に対応できます。

例えば、CSVの内容を自動的にセルに割り当てたり、入力フォームを表示したり、データベースと連携したりなど、様々な事に対応できますので、決まった作業なら、自動化する事も可能です。

Excelで業務をされているなら、きっとお役にたてると思います。