エクセル開発15年、セルネッツの竹本です。今回はグループ集計に欠かせないVBAの連想配列について、実演デモを交えてやさしく解説をしていきます。

では始めていきます。

連想配列とは？

まず連想配列とは？

できることはのちほど紹介しますが、理論ということで、解説はなかなか難しいものになります。Wikipediaで検索して出てきた結果を見るとますますわからなくなってしまうと思います。
なので今回は理論はさておき、なにができるかを実際に見ていきます。

セルネッツ流連想配列とは？

開発未経験者はとても混乱するようです。

ディクショナリー配列という言い方もするんですが、連想配列は辞書ではありません。理論も大事なんですが、使いこなせることがもっと大事です。

「グループの種類をゲットするやつ」みたいに覚えてしまって良いと思います。

実演

PhraseExpressによるテンプレート作成

今は動きませんが、ここにプログラムを書いていくのではなく、すでに登録してあるものをテンプレート的に使うと楽です。

ディクショナリー配列、私の場合は「ディック」と登録しています。

そのままコピーしてもらってきます。プロシージャーの区切りを線のところに引いてあげて、いったんコンパイルします。

種類を求める際のポイント

もう１回説明します。

コマンドボタンでやりますが、「種類を求める」ボタンをクリックしたときにどういう動作が行われるのか、のちほど詳しく解説しますが、データの最終行を求めて2列目の集計キーで種類を求めます。

求まった値は10列目に、ここで重要なのは何列目でグループの種類を集めたいのかということです。ここに尽きます。10列目に書きます。

ではやってみます。2列目ですね、店舗の種類がここに入ります。

一瞬だったんですけど、渋谷・新宿・吉祥寺・上野。4店舗あることがわかりました。フィルターをすれば一発でわかるようになります。

要領を見ていただきたいので次に列をずらします。3列目を11列目に書きます。3列目をやります。11列目に来ました。

続いて4列目は色の種類。今度は1列ずらして12列目に書くようにしましょう。

最後はサイズです。5列目でグループの集計をして13列目に出します。

ということで、それぞれ店舗だったり商品名・色・サイズ。それぞれの要素の種類がここに入ったわけなんですが、今の動作、処理の流れを連想配列によって求めました。

集計キーとは？

集計キーとは、どの単位でグルーピングをするのかということです。表現の仕方ですが、要素名・列名・フィールド名と言うこともあります。つまりどの列で集計したいんですか？というような言い方になります。

この時点でお気づきでしたか？エクセル標準機能のデータの重複の削除と同じなんです。得られる結果は同じです。でもこの方法は大量データだと処理が遅かったりします。

なぜ連想配列を使うのか？

なぜ連想配列を使うのか。メリットを5つ記述しました。

ひとつは、メモリの中でやってしまうのでシートを汚さない、シート操作をしないということです。なので配列という言い方になるんだと思います。

記述がシンプルで、先ほどスニペットツールで雛形をボンと持ってきましたが、修正を加えたのはどの列かというところだけです。

シートに書かないケースもありますので、どの列というところしか書き換えていません。

記述がシンプルなので使い回しがしやすい、可読性が高いということはメンテナンス性が高い、早いということです。

先ほどのエクセルの機能でやった場合、たとえば店舗というのを持ってきて、データから重複の削除をやります。やっていることはダブった値をただ消すので結果としては同じなんです。

現在選択している範囲を選択して、商品名。

なので連想配列、わざわざプログラムでやらなくても良いじゃんという話になったりするんですけど、先ほどのようにシートを汚さないということで、こういう必要がないわけです。

複数の集計キーを使う

そして今度は先ほどは単一キー、ひとつのフィールド、ひとつの要素に対して何種類あるの？というのを出したんですが、今度は2つの集計キーで出したいと思います。

これも連想配列なんですけれど、今度は空っぽです。いったんさっきの中身をもらってきます。

今度は12シートに移っているので、12シートに変えました。

ここにブックマークを設定して、行番号は同じになります。

店舗と商品名なので、2列目と3列目でグルーピングした場合はどうなるのかというと、先ほどはひとつのキーでしたが、もうひとつ必要になるので、そこだけ追加します。

キー1をコピーしてキー2を作りました。キーが2つになりました。2列目と3列目になるので、押します。キーが2つになったので＆でつなげます。

どこに書き出すのか、10列目と11列目になるので、10列目にキーの1。10列目と11列目、キーの1と2。

もう1回見てみましょう。

今私が直したところを見ます。キーをひとつ増やしました。

じゃあやってみます。種類を求める。今度は2つの集計キーです。先ほどは店舗の種類が4種類しかありませんでした。

商品名は6種類あり、その組み合わせのパターンが存在しているので、データの個数は14個と書いています。ということは、14行分作られたということです。店舗と商品名で2つの集計キーでその種類を確認してみたところ、14種類ありました。

というやり方になるだけなので、3つあれば同じようにここにキーの3を入れて、4列目だったらここです。ここにそのまま手で書けるぐらいに、10、11、12。ここが3、これでやってみましょう。

ここに入ります。今度は15種類ということです。店舗と商品名と色で見た場合に、先ほどより1行増えた形になります。というのがプログラムの記述です。

このあと解説をしていきますのでいったん終了します。

ということで、データ重複の削除をエクセルの機能でやったとしても同じ結果は得られるんですけど、大量データだったりすると速度が遅くなったりします。

ソースを解説

ソースを解説します。まず先ほどのをもう1回やってみましょう。45行出てきました。こういう定番のものをあらかじめ登録してしまっているわけです。

スニペットツールに関しては別の動画で紹介しています。ちなみに行とやると行、ついでにデバッグとやるとよく使うやつが出てきます。

ソースの解説です。これは変数宣言。使った変数は本当にこれだけです。

名前が「O」で始まっていますけど、セルネッツ流ではオブジェクトの変数を使うときはオブジェクトのOを入れています、「O_」。

ディクショナリー連想配列。この名前でなくても、もっと短くても良いと思います。連想配列、これはお約束なので赤くしておきましょう。

ここもお約束なところ、これが連想配列です。

ディクショナリー連想配列というオブジェクト型の変数があって、あとは配列キー、それからキーの1、2。読み込み行、書き込み行という、この変数をあらかじめ定義しておきます。

上から順番に行きます。

まず最終データ行を求める必要がありますよね。

続いてここから始まります。連想配列。書き込み行、どんどんアウトプットしていくときに、ここでカウントアップをしているだけです。

仕様によって、シートに書かない場合はいらないです。

このfor文が重要で、有効データ範囲をループします。

最終データ行の11フィン行、読み込み行は2行目からです。2行目から最終データ行までぐるぐる回りなさい。ポイントはここです。ここに尽きます。

集計したいキーを取得。2列目で集計を取りたいなら2列目だけで良いです。2列目をキーの1に入れて、3列目も取りたいということでこうなっているだけです。あとは一緒です。何列でも増やせます。これはワンセットです。その数ぶんやれば良いだけです。

ここの説明なんですけど、登場しました。先ほどの_ディクショナリー連想配列、エグジスト配列キー。存在していなかったものが登場した場合です。

先頭データ行から最終データ行まで、2列目と3列目の値を取って、今取った値でないものが出現したときという意味です。

Notとなっているので存在しなかったときに入りなさい。言い方を変えると、初登場の集計キーが登場したときに入る。新登場したらここに入るだけです。

ここに入った瞬間、いったんここは空白にしてあげて、あとは10列目、11列目に書くだけということです。

あとはそれぞれの行がなにをやっているかを実際に体感して覚えるために、ここのキーを変えればわかりやすいと思います。

なぜ連想配列が重要なのか、いつ使うのか。

なぜ連想配列が重要なのか、いつ使うのか。私たちがお客様に「集計をしてほしいんだけど」と言われたときに集計キーを尋ねるようなシーンですが、業務システムで集計処理の基本はグルーピングです。

グループ集計は要件としてとても頻度が高いです。

これは業務システムで、だいたい会計系が多いので、たとえば組み込み系の開発だったり、アプリ開発だったり、WEB系とはちょっと違うので、業務システムというくくりになってくると、集計のあたりが本当に頻度が高いです。

例としてはたくさんありますが、先ほどのデータで言うと、お客様はこういう感じでリクエストしてくることが多いです。

店舗単位の売上が知りたい。たとえば渋谷はいくら、新宿はいくら、吉祥寺はいくら、が知りたい。新宿でもいろんな商品が売れているので、新宿の中の帽子の売上だけを知りたい。

たとえば定番だったり、売れ筋、死に筋、特定の商品、特定のカテゴリーが今どのぐらいの売上なのかを知りたい。

さらにどんどん細分化をしていくだけです。

今度は色・サイズという要素も加わってきます。

さらに年月。お客様はできるだけ細かく見たいケースが非常に多いので、どこまで要素を細かく取るかは、要素が存在するぶん全部取ります。

ないものは取れませんが、この大中小関係は必ず受注データ、今回のデータに関しては、大中小という構造があるので、その要素は全部取れるということです。

連想配列の活用事例(デモ)

続いてもう1回デモです。連想配列の活用事例ということで、大中小分類データ、特にここですね。このあたりを説明したいと思うので、デモをしたいと思います。

大が特定されると配下の中が特定されて、小が特定される、親子・孫の関係ですね。では先ほどの別ファイルを開いて、このユーザーフォーム、データ連動のデモ、これを開きます。

連想配列の活用例ですが、今見ていただいているエクセルのマクロですが、この2枚目にリストシートがついています。
これはテストデータで、セミナー開催を想定しています。

たとえばビジネスというカテゴリーには4種類のセミナーがあります。プログラミングのセミナーでは開発言語によってセミナーが分かれています。

人事労務・会計経理・その他ということで、データは全部で16件あるんですが、カテゴリーの種類、セミナーの種類としてはカテゴリーは5種類です。セミナーの種類はたくさんあります。これをユーザーフォームでどんどんデータを投入していくときに連想配列を活用します。

今のリストを画像にしたものがこちらです。ちょっとリアリティを持たせるためにテストをします。

このカテゴリーとセミナーは大中という大分類があって中分類です。中分類、受講料、これは直結しているので、こことここが決まればここが決まります。

たとえばCの01、ビジネス一般を選択します。すると、ビジネス一般を選んだ場合はセミナーは4種類のどれかを選べるようになっています。

スキル研修からITスキル、全部独占ですが、一番下のその他を選んだ場合は2種類しかないわけですよね。マーケティング・プレゼンテーション、2種類しかありません。ということで、どちらを選ぶかによって受講料が連動してここが変わっていきます。

こういうケースで、今はユーザーフォームで説明をしましたが、ユーザーフォームでなかったとしても同じです。カテゴリーを選ぶとその配下、それに属するものだけが表示される。

さらにというのもありますので、プレゼンテーションにも実は子供がいて、先ほどの親がいて、子供がいて、孫がいてという階層は3階層、少ないほうです。

もっと4階層、5階層。薬品の原材料だったりに関しては、輸入国・原産国・仕入れの国から、薬品の成分からなにからなにまでということで、化粧品などは相当奥の深いところまで、薬品のところまで行ったりするので、かなり細かい単位で再分類することになっていきます。

なので、連想配列を使いこなせるようにならないとユーザーフォームは作れないかもしれません。やっていることは難しいものではないと思います。種類を特定していくやり方が先ほどの連想配列でできるので、今のユーザーフォームでこれを実現しようとなると、まず必要になってきます。

終わりに近づいていますけれども、種類を教えてくれるものなんです。こういうものであればダブり、重複を消してくれるので、こういうことになります。

いろいろデータがありますが、何種類あったんですか？ということで、件数が膨大だったりすると目視では拾えないので、よく固定でやるケースがあります。

たとえば年月、月に関しては1月から12月、これは世界中、1月から12月しかないので、固定でやっても良いですが、たとえば日にちに関しては閏年があるので、年によって2月が28だったり29だったりします。

都道府県は47都道府県ありますが、実際にエントリーされている県は必ずしもすべてが揃っているわけではないので、存在しているデータの中で選ばせたいケースでは、必ずこのグループ集計が必要になってきます。

まとめ

最後まとめになります。

グループ集計では種類数を知りたいときに必ず使います。

例を2つ挙げておくと、成績の一覧表とか、学習塾とか学校だったり学年があって、その中にクラスがあって、生徒名があったりします。

また地域があって、関東だったり関西だったりを選ぶと、その配下の都道府県だけが選べるようになって、その都道府県を決定することによって、そこに存在する店舗名だけができて、その店舗名の中からいろいろ出てきたりします。こういうことは本当によく使うので一番最初に書きました。

そして覚えてほしい理由ですが、重要度5つ星なのに難易度は1、2ぐらいなんです。

覚えていただくのは理論というよりも、先ほどのソースを使えば、一覧表のテーブルのデータであれば種類がすぐにわかってしまうことを体感して覚えていただけると開発の仕事に大いに生きてきます。

3番目、構文を暗記する必要はありません。テンプレートと呼んでいますが、エクセルの中に入れて管理しても良いですし、私はスニペットツールを使っていますが、あとはマイクロソフトの無料のOneNoteがあります。グループで共有したりもできるのでとても便利です。

ということで、よく使うプログラムソース、定番のものは毎回調べたりするのではなく、すぐ出てくるようにしておくことが、仕事を早く、生産性を上げて開発をしていくポイントになります。

ということで、理論や仕組みよりなにができるのか、いつ使うのかというところで連想配列を解説をさせていただきました。

「グループの種類をゲットする」、かなり乱暴な短い言い方になってしまいましたが、プログラマーだったりエンジニアを目指す人たちにとっては、この連想配列をガッツリ勉強していかないといけないかもしれません。

VBAの業務改善という領域の中で、こういうようなグルーピングをしていく場合においては使いこなせることが重要になってきますので、ぜひ覚えていただければと思います。

今回は「グループ集計に欠かせないVBA連想配列」について解説をさせていただきました。

少しでも参考になったかたはぜひチャンネル登録・高評価をお願いします。ご視聴ありがとうございました。

タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
　　　　　　　　　　　　　　　　　　　　　　　　　タケモ塾運営：株式会社セルネッツ