Excelがすごく重くて……
これも良くお話として聞きます。
実際、訪問先のパソコンで使用しているExcelを開いてもらうと、開ききるまでに２分以上かかるファイルによく当たります。
それで、ちょっとエクスプローラーでファイルの場所を見せて頂くと、.xlsxファイルなのに、6ＭＢとか8ＭＢとか、とにかく大きなファイルになってしまっています。
動画ファイルなどが増えている昨今、8ＭＢは小さいように見えますが、EXCELファイルとしてはかなり巨大な部類になります。
これらの原因の多くが、便利なので使ってしまうセルや行のコピーにあります。
Excelには実に便利な関数が非常に多く用意されています。
そして、よくこの肥大するファイルで使用されている関数にVLOOKUPがあります。
|
1 2 |
=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を書かれたシートを読み込むと、その全ての計算を律儀に行います。その結果、あっという間にメモリやＣＰＵを使い切り、どんどん処理が遅くなっていってしまいます。
こうした場合、弊社では処理の一部をVBA化する事をオススメしています。
VBAは、「Visual Basic for Applications」の略で、MicrosoftOfficeの各製品に内蔵されているプログラミング言語です。
今ではあまり互換性が無くなってしまいましたが、Visual Basicと同じ文法で処理を記述する事が可能な、本格的なプログラミング言語になります。
プログラム言語なので、他のシートを開いたり、様々な処理を加えたりできます。
例えば、上記のVLOOKUPと同じような事が出来るVBAだと、次のような例になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
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化したりなど、様々な業務に対応できます。
弊社はVBAに詳しいので、御社の状況に合わせたVBAを開発いたします。
どのような事でお困りか、お問い合わせからお気軽にご相談ください。