連想配列でグループ集計を効率化

連想配列でグループ集計を効率化

投稿日: 2021年05月16日 カテゴリー:Excel Access,Excelシステム開発,ExcelマクロSample,業務効率化

本コラムでは、連想配列について解説して参ります。大量データ集計においては、この「連想配列」が欠かせないものであること。そして、その理由などをご紹介したいと思います。

連想配列って何?

連想配列って?
配列を連想すること?
連想することで配列を???
ますます分からん!?

連想配列、定義に向き合うと挫折するかも?

連想配列とは

出典: フリー百科事典『ウィキペディア(Wikipedia)』

連想配列(れんそうはいれつ、英語: associative array)とは、コンピュータプログラミングにおいて、添え字にスカラー数値以外のデータ型(文字列型等)も使用できる配列である。抽象データ型のひとつ。連想リスト、連想コンテナ、辞書(あるいはカタカナでディクショナリ 英語: dictionary)、ハッシュ(英語: hash)、マップ(英語: map)とも呼ばれる。


とのことなのですが、理解できましたでしょうか?


正直、さっぱり理解できません。それどころか、これが理解できないと「プログラマーとして活躍できないのでは?」、「上級SEになれないのでは?」と、焦ってしまう方も多いようですが、そんなことはありません。


定義を暗記できても、意味を理解できても、仕事の中で、「いつどんな場面で活用すればよいのか」。これが瞬時に脳裏にイメージできなければ、業務システム開発という場面で、最前線には立てません。
仕事は試験ではありませんから、定義を覚えること以上に、いかにスピーディーに問題解決を図れるか、が勝負の分かれ道です。


そのためには、できるだけ多くの引き出しをもっていて、それを適宜上手に使いこなせる人こそ、優秀なプログラマーとして一目を置かれるのです。

セルネッツ流では、ここぞというシーンで、連想配列を上手に活用しているエンジニアが高い成果を出しています。

グループ集計とは?

[年月別]
21年01月
21年02月
・・・
21年12月

[支店別]
東京本社
大阪支店
福岡支店

[年月別]+[支店別]
21年01月+東京本社
21年02月+東京本社
・・・
21年12月+東京本社

21年01月+大阪支店
21年02月+大阪支店
・・・
21年12月+大阪支店

このように特定項目でデータをひとまとめにし、そのグループ毎の合計金額や合計数量を求めること。

企業活動は、事業の収益アップを目指し、日々PDCAを繰り返すことが一般的ですが、どこに注力すべきか。

色んなアプローチで、様々な角度から<売れ筋・死に筋>製品の把握、推移や傾向分析を行っています。情報分析ツールの1つに過ぎないExcelシステムですが、データ集計〜データ分析は、恐らく業種・規模・形態を問わず行っている大事な作業の一つです。

お客様の要望を反映し、正常動作するプログラムを開発することが前提ですが、同時に、プログラムが何を目的に作成することとなったのか、その狙いなども、しっかり理解することが大切です。

活用メリットは何?

何よりも、コーディング [Step数] が激減することです。

それは、プログラミング記述において、メンテナンス性を高めることにも繋がり、第三者にとって、読みやすいプログラム記述となるだけでなく、解読しやすいため、バグが出にくく、出ても治しやすい、そして何より、どんな仕様追加の依頼が来ても、ゴチャゴチャになりにくい、というメリットがあります。

これは、なにも、ExcelVBA開発に限ったことではなく、開発言語を問わず、プログラミングの原則論となります。「可読性」、「メンテナンス性」、「拡張性」、「視認性」、「判読性」、「高速化」という面で、大きな差が生じてくるため、セルネッツでは、連想配列の習得は必須としています。
(すみません、この言い方が敷居を高くしてしまうようですが、1時間講習で理解いただけますので、ご安心ください。)


連想配列どんな場面で使う?

何ができるのか、という観点からみれば、一覧表データ群に対して、「特定キーの種類を調べる」と覚えてください。

業務システムでの連想配列の活用シーンは、まさに、この使い方が最も多いのです。データ件数が多ければ多いほど、処理高速化は図れますが、データ件数が少ない場合でも、とても有効な強力な機能となるのです。


例えば、ある保育園のクラスに属する先生の登録マスタがあるとします・・・。
【データ内訳】先生が件、クラスが件です。


UserFormから園児を担当する先生を登録してゆく場合、クラス選択肢は、[択]となり、それぞれに属する配下データ(先生)が選択肢と表示される必要があります。

存在するクラス(組)を知っているからと言って、プログラム内で固定記述するようでは、プログラマー失格です。組は将来、増減する可能性があり、可変設計が必要なためです。

01.ひよこ組-山田先生
02.あひる組-斉藤先生
03.うさぎ組-田中先生
04.きりん組-佐藤先生
05.きりん組-鈴木先生
06.ひよこ組-近藤先生

つまり、件数が多い・少ないにかかわらず、この連想配列の活躍シーンは、存在数の取得という局面で広く活用できるのです。


では、なぜ、重要なのか?というと、業務システムでは、「特定キーの種類を調べる」ケースが実に多いのです。例えば、〇年〇月度>支店別>部門別>担当者別の売上合計を求める場合、基幹システムから出力された数千件の明細データの中から、まずは、その「種類数」という概念に向き合うこととなります。


ここで、連想配列の登場です。それ以外でも、企業の業務システムが売上や利益、教育機関では合否人数、出欠集計などあらゆるシーンで「グループに属する配下情報を集計する」ということが非常に多いため、この連想配列は、知識として必要不可欠なのです。

連想配列どんなメリットがある?

前述のとおり、連想配列を活用できるようになると、コーディング記述するステートメント数が激減します。

それに伴い、シンプルにコーディングができるので、必然的にメンテナンス性として必須条件である「可読性アップ」につながります。連想配列を使った場合と使わなかった場合で、プログラムソースを見比べてみればその結果は一目瞭然。是非とも、覚えていただきたいテクニックの一つですね。

連想配列どれだけ速いの?速度の差

処理対象のデータ件数や、その処理内容にもよりますが、100万件の大量データをセル操作&連想配列なしで行った場合と比べれば、100倍以上の差があるので、実際に試していただくと納得いただけると思います。

遅い人だと50秒。その差は49秒だから許容できるか?弊社では不合格となります。
理由は、速度の遅さよりも、普通に考えて、100,000件からわずか数十件データを抽出する処理に、5秒以上かかる結果を受け入れるという意識に問題があります。

このような意識では、メモリ不足への対処や考慮、配慮など期待できません。「教わればできる」レベルは、プロではありません。Excelシステムで大量データを処理する場合、「応答なし」で止まってしまう原因は、Excelシステムの問題ではなく、作り方の問題が圧倒的に多いのです。

未経験者については講座を通じて学んでいただきますが、ベテランさんなら暗黙の了解と理解ください。

(活用事例)
UserForm入力支援フォームから、データを投入する場面で活躍する「コンボボックス」。このコントロールの選択肢は、実は、連想配列で存在する種類数を取得し、瞬時にその選択肢として設定しています。

UserForm連想配列の連携

例えば、UserFormなどで、セミナー申し込みフォームを作る際に、この例では、カテゴリCDを選ぶと属する配下が表示される仕組みですが、

「大分類>中分類>小分類>明細」

のようなデータ構造で、絞り込みによる特定データの選択などでは必要不可欠です。

コンボボックスの元となるデータ。それがこのセミナー管理マスターです。「カテゴリCD」をキーに連想配列すれば、おのずとその種類が順に取得できます。

そして、選択肢を変更した瞬間に。属する配下データ(セミナーID)が、その選択しとなるように設計するのです。

難しそうですが、やってみれば簡単です。

そう断言できるのは、記述Step数が証明してくれますので、ご安心ください。

連想配列サンプルで検証

実際に試してみるのが一番ですね。サンプルではデータ件数こそ少ないですが、[LIST]シート内のデータの並び順を変えたり、増やしたり、減らしたり、名前を変更することで、あらゆる検証ができるので試してみましょう。

※[カテゴリCD] 種類数(全5種:C01~C99)を取得します。Lesson1_連想配列_実験してみよう1

'【連想配列Dictionaryオブジェクト宣言】
Set O_Dictionary連想配列 = CreateObject("Scripting.Dictionary")

'81番シートFin行Get
S_Fin行 = O_81.Cells(O_81.Rows.Count, 1).End(xlUp).Row

S_書込行 = 1

'【連想配列Loop】2列目
For i = 2 To S_Fin行

    S_Key01 = Cells(i, 2)    '★[カテゴリCD]

    S_配列Key = S_Key01

    '★Break発生=データを挿入
    If Not O_Dictionary連想配列.Exists(S_配列Key) Then '[非存在]なら新登場!

        S_書込行 = S_書込行 + 1

        Cells(S_書込行, 10) = Cells(i, 2)
        Cells(S_書込行, 11) = Cells(i, 3)

        O_Dictionary連想配列.Add S_配列Key, ""

    End If

Next i

Set O_Dictionary連想配列 = Nothing

連想配列ソース解説

連想配列わかった!

まず、すでに綺麗にSORTされたデータ状態で連想配列を試してみます。ちょうど太字罫線のあたりが境界線なので、正しく取得できることを確認してみましょう。

次に、連想配列の良いところは、SORTされていなくても種類数を取得してくれるんです。これは、実際にデータをバラバラにしてテストしてみてください。

そして、ここまで体感し連想配列で何ができるか?がわかってきたら、そのキーの個数を増やしてみましょう。

カテゴリCDの種類数が簡単に把握できたら、キーを増やしてみたり、データ件数も大量に増やしてみれば、とても重宝なことが理解いただけるのではないでしょうか。

連想配列~課題sampleに挑戦!

弊社では、エンジニア採用基準として、まず第一に、Excel基礎知識、応用力を重視しています。そして、プログラミング技術という面から、何度も登場してきた言葉「連想配列」。

この連想配列を評価基準としていますが、一度、試してみると、少しづつ理解は深まると思いますので、ぜひ、チャレンジしてみてください。

ページ中央あたりの「課題2/3 :二次元配列の理解~活用」という課題サンプルが、連想配列の理解度を評価するためのサンプルです。(目標は1秒未満です)


例えば、弊社の課題サンプルをもとに紹介すると・・・。
10万人の会員データがあります。それぞれお名前、性別、都道府県という情報がある場合、[都道府県]+[性別]ごとの人数算出する。
といったチャレンジ課題なのですが、速い方で1秒未満、遅い方で50秒・・・。それだけ差異が生じます。

ポイントは、データとして実在する都道府県の種類数がわかれば、あとは性別ごとの人数(=レコード件数)を調べるだけなのですが、実に大きな処理速度の差が生じしてしまうことになります。


それは何故でしょう。この連想配列を知っているかどうか、まずポイントの一つがコレです。次に、配列操作をしているかどうか。いくら連想配列で高速に種類数を求めたとしても、セルの値のダイレクトに精査していれば遅くなるのも無理はありません。

連想配列はSORT不要で種類をGet

連想配列の良いところ。存在するデータの種類数を順次検出してくれるので大変便利なのですが、一覧データをあらかじめSORT(並び替え)しておく必要がありません。異なるキーを検出した順番で教えてくれるので、とっても優秀ですよね。

もし、コンボボックスのような活用で、データの表示順が必要ならば、あらかじめ序列を設定しておく必要があります。たとえば、「都道府県」データ選択の場合、先頭が”北海道”なのか”東京都”なのか。
これは利用する方の意見を反映するだけですが、意見をうかがって、対応ができるようにするためには、コード設計が必要です。ここでは割愛させていただきますが、連想配列は便利ですね。

おわりに

いかがでしたでしょうか。連想配列について、紹介させていただきましたが、この5月から、セミナ開催をはじめましたが、今回も質問をいただくことになりましたので、できるだけ分かりやすく解説したつもりですが、今後は、動画配信もふくめ、「ExcelVBA活用講座」を紹介してまいりますので、ご期待ください。




業務効率化ご案内

便利ツールダウンロード

セミナー情報はこちら

お客様の声ご紹介

Array ( [0] => 12 [1] => 8 [2] => 29 [3] => 7 )

株式会社セルネッツ