【Excel VBA】データ集計ツール作成時の配列Buffer処理について

Question

はじめまして。
超がつくほどのVBA初心者のものです^^

２枚のシート、[A 会員データ]シート、[B 集計結果]シート
があります。

[A 会員データ]シートのデータリストはこのような感じになっています。

|氏名漢字|氏名かな|性別|mail|TEL|住所|
|:--|:--:|--:|
|田中太郎|たなかたろう|１|tarou.bbb.orjp|011-266-**|東京都北区2-41-*
|鈴木恵子|すずきけいこ|２|suzuki.bbb.orjp|011-345-**|北海道札幌市2-41-*

上記のようなデータが約10万件あります。
ちなみに性別欄の１は「男性」で２は「女性」を表しています。

BのシートにAの会員データを下記のように集計した2つのリストの
作成を目指しています。

１．都道府県別、且つ、男女別の人数
2． 男女の内訳人数

出力結果は下記のようなリストをイメージしています。

1.都道府県別、且つ、男女別の人数

都道府県	性別	人数
鹿児島県	男性	45
鹿児島県	女性	12
〇〇県	男性	45
〇〇県	女性	12
××県	女性	12

2

内訳	人数
男性	46
女性	12

配列Buffer処理にて高速処理を目指しています。

配列での実装経験がなくVBAの知識も乏しいので
誠に恐縮なのですが熟練者の方ご教授頂ければ幸いです。

宜しくお願い致します。

Answer 1

配列で実装するということは、高速化のためだと思いますが、
配列にすればなんでも高速になるとは限らないです。
ピボットテーブルでできるならことなら、VBAでピボットテーブルで集計、出力を実装するのが高速だと思う（たぶん）（VBAはインタプリタなのでそもそも遅い）。

あるいは、ADOをつかってSQLで集計して、CopyFromRecordset で書き出すというのは、シンプルなコードで高速な処理が実現できそうです（SQLが分かるなら）。

エクセルのシートをデータベースのテーブルとして扱うとめちゃくちゃ簡単にデータを加工できる！｜エクセルVBA - ぼくLog

配列を使うなら、
ttyp03さんの回答のように Scripting.Dictionary を使って集計していくことになりそうですね。

いろいろ、やってみて一番高速なのを選択すればいいでしょう。

Answer 2

流れ的にはこんな感じかと思います（1の集計の場合）
Rangeで範囲を配列に取り込む。

Dim r As Variant
r = Range("範囲")

For文で行数分配列を回す。
各行から都道府県・性別を取得。
※都道府県は住所から都道府県名に変換するテーブルなどが必要です。
ハッシュテーブルで人数をカウント。
イメージ的にはこんな感じ。
Hash("都道府県名")("性別") = 人数
※ハッシュテーブルはScripting.Dictionaryなどでよいでしょう。

集計結果をシートに出力。

配列を使って速くなるのは、最初の取り込むところだけですね。
それもどれだけ速くなるかは実際にやってみないとわかりません。

2の集計も同じ要領でできます。

追記
ハッシュのサンプルを追記します。

Dim sexstr(2) As String
Dim all As Object
Dim place As String
Dim sex As Integer
Dim cnt As Object
Dim pkey As Variant
Dim skey As Variant

' 全体を管理する連想配列
Set all = CreateObject("Scripting.Dictionary")

' 集計対象
place = "鹿児島県"
sex = 1

' 地名のキーが作成されているか
If all.Exists(place) Then
    ' 作成されているので性別毎のカウントアップ
    Set cnt = all(place)
    cnt(sex) = cnt(sex) + 1
Else
    ' 作成されていないので地名毎・性別毎のカウント用連想配列を作成
    Set cnt = CreateObject("Scripting.Dictionary")
    cnt(sex) = 1
    Set all(place) = cnt
End If

' 出力
sexstr(1) = "男"
sexstr(2) = "女"
For Each pkey In all
    For Each skey In all(pkey)
        Debug.Print pkey & " " & sexstr(skey) & " " & all(pkey)(skey)
    Next
Next

Comments

[退会済みユーザー]

コメントありがとうございます。
ハッシュテーブルというのがいまいち良く分かっていません。
Scripting.Dictionaryを使って今回のような
ケースの場合はどのようにコードを書けば良いのでしょうか？

[ttyp03]

基本的な部分だけ書いてみました。
参考にしてください。

[退会済みユーザー]

ありがとうございます！
参考にさせて頂きます！

Answer 3

目的が二つの集計表を作ることであり、会員データシートに列を追加してもかまわなければ、私なら、集計表示用に性別数値を文字に置換えた列と都道府県名を切り出した列を加え、VBAを使わずにピボットテーブルで済ませます。

性別
=IF(C2=1,"男","女")
都道府県(3文字目が「都道府県」のどれかなら3文字、そうでないなら4文字切り出す)
=LEFT(F2,3+ISERROR(FIND(MID(F2,3,1),"都道府県")))

これを使い、ピボットテーブルを作成します。
データのある列を選択しておき、挿入リボンのピボットテーブルを選び、票を入れたいシートを選びOKします。

フィールドリストで表の集計項目と集計値にする列を選びます。
表1ならば行ラベルに都道府県と性別を、値に適当な(全行にデータのある)列を入れます。

Comments

[退会済みユーザー]

回答ありがとうございます！
私もVBAを使わなければピボットでやってしまうと思います^^
ただ急に配列で実装することになってしまいまして。トホホです(;´д｀)トホホ

[退会済みユーザー]

ピボットテーブルはご存じだったのですね。失礼しました。

Buffer処理での高速処理といわれているのは、Cellへのアクセスに配列を使うと早くなるという理解(指示？)によるものだとしたら、それは誤解がある気がします。
大量(多数)のセルへの出力は、ループ処理でちまちま出力せずに、範囲で一気にやった方が速いということであって、配列を使えば何でも速いってことではありません。読み取り目的で表全体を配列に代入しても大した効果は期待できないと思います。
Office TANAKA:配列を使う http://officetanaka.net/excel/vba/speed/s11.htm

出力したい要件が、今回の質問の内容だけならば、大きくても94行*3列程度の大きさの表と2行2列の表になるだけですから、出力処理をループ処理で行っても誤差程度の差しか出ないでしょう。

もしかして、10万行の行処理毎にセルに出力していて遅いので、処理中の値を配列で保持するようにしたいと言うことなんでしょうか…。

[退会済みユーザー]

コメントありがとうございます。

>もしかして、10万行の行処理毎にセ>ルに出力していて遅いので、処理中>の値を配列で保持するようにしたい>と言うことなんでしょうか…。

はい。出力が遅いので、配列で値を保持したいとの事です。

[退会済みユーザー]

Scripting.Dictionaryを使って今回のような
ケースの場合はどのようにコードを書けば良いのでしょうか？
連想配列というのがいまいち良く分かっていません。。。

[退会済みユーザー]

具体的なことはttyp03さんのサンプルにお任せするとして、連想配列の基本的なことだけ。

ふつうの配列は、各要素へのアクセスに番号を使いますが、連想配列では、番号の代わりにキーと呼ばれる任意の文字列を使用します。この文字列が同じなら同じ要素となるので、今回ならば都道府県名をキーにする事で、都道府県別の集計が簡単にできるという事になります。