一般社団法人 全国個人事業主支援協会

COLUMN コラム

こんにちは。
田中です。

※あくまで考え方の一例なので、これが正解不正解というものではありません。
※もっといいプログラムの組み方があるかもしれませんので、参考程度に。

事象

基本的には元データ1つにつき、集計結果ファイルは1つ。
けれども時には、Excelにはシートがあり、シートごとに結果を記載する場合もある。

例を挙げると、1つの商品に対して、年齢層ごとにデータをまとめるとして。
・結果ファイルは「商品A_年齢層.xlsx」
・1つの地域につき1シート
みたいな感じする場合というところ。

考え方

○基本的な1つのデータにつき、結果ファイルが1つの場合

Sub Sample1 For 元データ resultFile = 元データをもとに結果ファイル名を作成 resultFileを作成 resultFileを開く   resultFileに元データの内容を記載   resultFileを閉じる Next End Sub

こういうロジックになるでしょうか?
これをもとに複数バージョンにしたのが下。

○結果ファイルを全ファイル開いて、元データを一件ずつ見ては該当するシートに記載していくパターン

Sub Sample2
For 元データ
 resultFile = 元データをもとに結果ファイル名を作成

 If 結果ファイルがない then
  resultFileを作成
  resultFileを開く
 End If
 resultFileをアクティブ
 該当するシートをアクティブ
 resultFileに元データの内容を記載
Next

開いているresultFileをすべて閉じる

End Sub

結果ファイルがあればそれをアクティブにして、なければを作っては開いたままにして該当したファイルごとに記載していく。

欠点:メモリをとにかく喰う!!
resultFileを開きっぱなしにするわけだから、当たり前といえば当たり前。
開いているファイルの数とか、開くファイルが大きかったりすると致命的。
「メモリが足りません」エラーが出て、元データのファイルが開かない、ファイルが保存できないなど障害が起きる。
例でいうところの地域が都道府県別だったりしたら48シート分…

ということで、次っ!!

Sub Sample3
For 元データ
 resultFile = 元データをもとに結果ファイル名を作成 If 結果ファイルがない then
  resultFileを作成
 End If

 resultFileを開く

 resultFileをアクティブ
 該当するシートをアクティブ resultFileに元データの内容を記載

 resultFileを閉じる
Next
End Sub

今度は、元データごとに該当するresultFileを開いては閉じてというする方法。
これでメモリの問題は解決する。
もちろん、完璧なプログラムなど存在しない。
欠点:処理時間がかかる
ただ、数が少なければ無難な方法。

Sub Sample4
For 元データ
 resultFileObject作成
 Key:resultFile value:該当する元データ
 ※元データが複数の場合、私は"*"とかで区切って一つの文字列にしてしまったりする
Next

For resultFileObjectのKey
 resultFileを作成
 ※ここで"*"で区切ったものをばらす

 For 元データ(resultFilesObjectのValue)
  該当するシートをアクティブ
  元データの内容を記載
 Next

 resultFileを閉じる
Next
End Sub

resultFileを作る前に、一度該当するファイルをまとめて見ました!
これなら、結果ファイルを開くは一度だけ。

結果的にこのやり方が処理時間が短く、なんとかメモリも範囲内に収まった模様。

ちなみに、Sample4で処理が止まったときの原因の一因について記載する。
・エラーもなく無言で処理が終わる
→ワークブックの数十回以上の開閉
→クリップボードのデータ量が多い

結果的に処理速度、メモリのことを考えると、ひとつひとつファイルを閉じて開いてを繰り返したほうがよさそう。

The following two tabs change content below.

この記事をシェアする

  • Twitterでシェア
  • Facebookでシェア
  • LINEでシェア