Excel VBAで処理を定例化
目次
Excelファイルが開かない…
社内ＳＥとして働いている間、けっこうな割合で出会ったのがこの案件です。
「最近、ファイルが開くのにすごく時間がかかって…」とヘルプを求められ、対象のファイルを見ると、6ＭＢとか、8ＭＢとか、とにかく大きなファイルになってしまっています。
動画ファイルなどが増えている昨今、8ＭＢは小さいように見えますが、EXCELファイルとしてはかなり巨大な部類になります。
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を使うと、簡単にデータが保存されているシートから、必要なデータを拾い出す事ができます。
上記の例では、集計シートは、VLOOKUP関数で自身の右3枚のシートからデータを取り出してまとめて表示しています。
何等かの出力方法で、価格や原価などを別々に取り出すというのはよく聞く仕様ですので、それっぽくしてみました。原価と仕入数のデータをそれぞれ取り出し、どこから合計仕入金額幾らで入荷したかの表のようなものになります。
当然ですが、価格のシートの値を変えれば、VLOOKUPの再計算で最新の値を簡単に拾い出せます。
こうして、Excelシートは簡易データベースへと進化していく訳です。
ところが、便利なこの式が、簡単に行ごとコピー出来てしまうところに罠があります。
上の例は5行しかなく、20キロバイトのファイルですが、もしこれが100行、いえ、1000行もいったらどうなるでしょう？
実際、上記のような5行程度なら、データを手作業でコピー＆ペーストしてもそれほど苦ではありませんが、複数のファイルから、数百～千行のデータを、毎日人力で一つのファイルにコピー＆ペーストするというのは大変です。
そうした扱いの大変な大量のデータを扱おうとするから、VLOOKUPが必要になってくるので、ブック全体でVLOOKUPが4000個あるなど、それほど珍しくないのが実情です。
当然ですが、Excelは4000個のVLOOKUPを書かれたシートを読み込むと、その全ての計算を律儀に行います。その結果、あっという間にメモリやＣＰＵを使い切り、どんどん処理が遅くなっていってしまいます。
「最近、ファイルが開くのにすごく時間がかかって…」は大抵、こうした式を無造作にコピーしていった結果、式の総数が数千になってしまい、開く時に全部計算するため、時間がかかるというのが多いです。
式をやめて、VBAで処理しましょう。
こうした場合、処理の一部を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 ←データが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ファイルを自動で開きます。
開いたexcelからデータを取りだし、集計用のシートに複写していきます。
金額については、得られた数値データを計算します。
これを、ループによって5回繰り返します。
5行程度のデータですと、これを実装してもそう変わりませんが、数百、数千になってくると、一つ一つのセルで計算するvlookupに比べ、ブログラム中でデータ処理を計算をしてしまうVBAとでは差が歴然となります。
容量も小さくなり、開く速度も格段に速くなります。
もちろん、これらのデータ元がCSVファイルでも、少し内容を変えるだけで、対応可能。その他にも、逆に纏まったデータをCSV化したりなど、様々な業務に対応できます。
例えば、CSVの内容を自動的にセルに割り当てたり、入力フォームを表示したり、データベースと連携したりなど、様々な事に対応できますので、決まった作業なら、自動化する事も可能です。
Excelで業務をされているなら、きっとお役にたてると思います。
