行数の多いExcelシート操作の高速化テクニック
最近仕事で20数万行の商品の仕入伝票一覧に、4万行くらいの価格一覧表を当てはめて、平均値・最大値を算出したり、10万行の商品マスタから単価・売価などの情報を呼び出すという業務がありました。
その際、VBAの中でWorkSheetFunctionのVlookupを使ったり、ワークシートにVlookupを代入して計算したのですが、とてつもなく時間がかかる（全部の処理で10時間くらいかかったと思います。）ので高速化の手段を探ってみました。
その中で連想配列を使う手法が最も効果が高く、全体の処理が5分もかからずに完了するように高速化ができたので、今回は連想配列を使用した高速化の方法を何度かに分けてご紹介します。
Excelのバージョンが2016からはワークシートのVlookup関数も早くなっていますが、VBAのWorkSheetFumctionで呼び出したVlookupは速度が速くなっていないですし、Sumifs関数やCountifs関数はそもそも対応していなかったりします。
一方、連想配列を使用する方法は様々な用途に利用できて処理速度も高速なため、まだまだこれからも利用価値は高いと思います。
今回は連想配列を使う方法の概要と事前準備についてお伝えします。
連想配列とは
連想配列とは「キー」と「要素」が一つのセットになった配列のことです。
通常の配列はインデックスが通常0から始まる数値で割り振られますが、連想配列においては「キー」をインデックスとして「要素」を呼び出すことが可能になります。
連想配列の記載方法は後ほど記載しますが、イメージとしては下記のような違いになります。
普通の配列の場合、配列に格納された値はインデックスの番号で呼び出すことが可能です。
食べ物=Array(ハンバーグ,チョコレート,コーヒー)
Debug.Print 食べ物(0)’ハンバーグ
Debug.Print 食べ物(1)’チョコレート
Debug.Print 食べ物(2)’コーヒー
連想配列はキーを使用してItemの値を取得することができます。
Set 食べ物 = CreateObject(“Scripting.Dictionary”)
食べ物.Add Key:=”メイン”, Item:=”ハンバーグ”
食べ物.Add Key:=”デザート”, Item:=”チョコレート”
食べ物.Add Key:=”飲み物”, Item:=”コーヒー”
Debug.Print 食べ物.Item(”メイン”)’ハンバーグ
Debug.Print 食べ物.Item(”デザート”)’チョコレート
Debug.Print 食べ物.Item(”飲み物”)’コーヒー
連想配列に格納するにはキーと要素を両方記述しないといけないため、通常は繰り返しの処理を用いてセルの値などを取得します。
格納した連想配列から値をキーで呼び出すという手順はVlookup関数の使い方と似ているため、Vlookup関数を使ったことがあってVBAを使える人なら比較的なじみやすい気がします。
スポンサーリンク
連想配列を使用する方法について
VBAで連想配列を作るためにはDictionaryオブジェクトというものを使用します。
Dictionaryオブジェクトを使用するためには参照設定をする方法としない方法があります。
参照設定をする方法
参照設定をする方法ではVBAのウインドウでメニューの中の「ツール」→「参照設定」を選択し、「Microsoft Scripting Runtime」にチェックをつけてOKをクリックします。
連想配列を宣言して使用開始するには下記のように宣言します。
Set 〇〇 = New Dictionary
配列名は何でもいいのですが、一般的にDictionaryオブジェクトをわかりやすいように「Dic〇〇」や「dic〇〇」とすることが多いようです。
参照設定をしない方法
参照設定をしない場合、CreateObjectで「Scripting.Dictionary」を指定します。
Set 〇〇 = CreateObject(“Scripting.Dictionary”)
参照設定せずに使用できますが、ヒントとか予測のプロパティが表示されないため、慣れていない人は参照設定をしておく方が無難かもしれません。
私は慣れていないですが、ヒントを参考にすることが少ないのでこちらの方法をよく使っています。
（Scripting.Dictionaryではないと思いますが、参照設定がバージョンで異なっていて動かないトラブルも避けられますし。）
下記のリンクから実際に使う手順についてご紹介します。
コメント