[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『統計表を作りたい』（midnn）
データベースから統計表を作成する方法を教えてください。
今、ws1にはメンバーのデータベースがあり、
C列に1～6の班番号（数字ではなく文字列）
DG列に住所（○○町から始まり、番地やマンション名まで）が入っています。
ws2には、cells(4,1）～cells(9,1)に上から順に、「1班」～「6班」という班番号を示す文字列が、
cells(3,2）～cells(3,5）に左から順に、「A町」～「D町」という町名を示す文字列が入り、cells(3,6)には、「その他」という文字列が入っています。
すなわち、見出しを含めて縦７行×横６列の表です。
班員は必ず「1班」～「6班」のいずれかに属し、大半が「A町」～「D町」に在住しています。その他の町に住んでいる場合は、「その他」に割り振られます。
この表内の各セルに該当人数を入力するマクロを組みたいのですが、方法がわかりません。
一番の問題は、班番号も町名も可変であり、年度が変われば班や町名が増えたり減ったりすることです。
そこでws3というデータベースシートを作成し、ws3.cells(11,3)にその年度の最大班数（今年度なら6）を、ws3.cells(3,4)から下に向かってその年度存在する町名を手入力するようにはしてみたのですが、そこでお手上げになっています。
よろしくお願いします。
＜ 使用 Excel：Excel2021、使用 OS：Windows11 ＞
文字だけだとわからないんで、差し支えなければレイアウトを提示してもらえますか？
『[談]シートレイアウトの投稿どうしてますか？』（ｍｏｍｏ） https://www.excel.studio-kazu.jp/kw/20110209184943.html
Before,Afterどちらも教えてください。
（稲葉） 2023/01/24(火) 17:03:10
|[A] |[B]|[C]|[D]|[E]|[F]
[3]| |A町|B町|C町|D町|その他
[4]|１班| | | | |
[5]|２班| | | | |
[6]|３班| | | | |
[7]|４班| | | | |
[8]|５班| | | | |
[9]|６班| | | | |
<ws2> After
|[A] |[B]|[C]|[D]|[E]|[F]
[3]| |A町|B町|C町|D町|その他
[4]|１班| 10| 15| 10| 8| 0
[5]|２班| 5| 6| 24| 3| 0
[6]|３班| 12| 10| 9| 11| 1
[7]|４班| 8| 9| 7| 14| 3
[8]|５班| 14| 2| 14| 6| 0
[9]|６班| 21| 2| 2| 8| 4
<ws3>
|[D]
[2]|住所
[3]|A町
[4]|B町
[5]|C町
[6]|D町
[7]|
[8]|
[9]|
すみません、こういうことでしょうか？
（midnn） 2023/01/24(火) 20:58:25
もちろんマクロでも集計可能ですが、 多分ですけど、ピボットテーブルでも出来るのではと思います。
＞ws1にはメンバーのデータベースがあり 想像ですけど、以下の様な感じですか？（１行目に見出し行あり）
|[A]|[B] |[C] |[D] |[E] |[F] |[G]
[1] |No.|名前 |班番号|住所１|住所２|住所３ |住所４
[2] | 1|古野 |1班 |A町 |〇○ |１番地 |
[3] | 2|杉本 |2班 |B町 |〇○ |２番地 |△△マンション
[4] | 3|高島 |3班 |E町 |〇○ |３番地 |
[5] | 4|川口 |4班 |C町 |〇○ |４番地 |
[6] | 5|佐々木|5班 |D町 |〇○ |５番地 |コーポ□□
[7] | 6|染谷 |6班 |F町 |〇○ |６番地 |
[8] | 7|安田 |1班 |B町 |〇○ |７番地 |
[9] | 8|中野 |2班 |A町 |〇○ |８番地 |
[10]| 9|川口 |3班 |C町 |〇○ |９番地 |
[11]| 10|尾崎 |4班 |E町 |〇○ |１０番地|
[12]| 11|鈴木 |5班 |D町 |〇○ |１１番地|
[13]| 12|松木 |6班 |F町 |〇○ |１２番地|
上記の表を基にピボットテーブルを作成します。
表範囲内のどこでも良いのでセルを選択し、（範囲をテーブルにしておくと良い）
1) 挿入→ピボットテーブル→新規ワークシートにチェックが入った状態でOKボタン
2) ピボットテーブルのフィールドがExcelウインドウの右側に表示されたら、
□班番号をピボットテーブルのフィールド下部にある「行」ボックスへドラッグ
□住所１を「列：ボックスへドラッグ
もう一度、□住所１を掴んで「値」ボックスへドラッグ
|[A] |[B] |[C]|[D]|[E]|[F]|[G]|[H]
[3] |個数 / 住所１|列ラベル| | | | | |
[4] |行ラベル |A町 |B町|C町|D町|E町|F町|総計
[5] |1班 | 1| 1| | | | | 2
[6] |2班 | 1| 1| | | | | 2
[7] |3班 | | | 1| | 1| | 2
[8] |4班 | | | 1| | 1| | 2
[9] |5班 | | | | 2| | | 2
[10]|6班 | | | | | | 2| 2
[11]|総計 | 2| 2| 2| 2| 2| 2| 12
上記の様になります。
3)「A町」「B町」の順番を入れ替えるには、「A町」「B町」と書いてあるセルを選択して、
左右にドラッグして入れ替えます。
表の右側にA～D町以外の町が固まったら、（上記表ならE町、F町）
その範囲を選択して右クリック→グループ化→そのまま右クリック→展開/折りたたみ→フィールド全体の降りたたみ
ピボットテーブルツールのデザイン→総計→行と列の集計を行わない
4) グループ1と表示されているセルを「その他」と書き換えたものが以下
|[A] |[B] |[C]|[D]|[E]|[F]
[3] |個数 / 住所１|列ラベル| | | |
[4] | |A町 |B町|C町|D町|その他
[5] |行ラベル | | | | |
[6] |1班 | 1| 1| | |
[7] |2班 | 1| 1| | |
[8] |3班 | | | 1| | 1
[9] |4班 | | | 1| | 1
[10]|5班 | | | | 2|
[11]|6班 | | | | | 2
町名の追加があった場合は、3)の作業を再度行う必要がありますが、 それほど難しい操作ではないと思います。 また、ピボットテーブルツール→デザインを色々いじれば、見やすいレイアウトに変更することも可能です。
（黒豆） 2023/01/24(火) 22:03:09
どうもありがとうございます。
ピボットテーブルは盲点でした。
確かにピボットテーブルならマクロより簡単に集計できそうです。
ただ今回の場合、元データの住所が↓こんな感じの入り方をしているのです。
|[B] |[C] |[D]
[2]|名前|班番号|住所
[3]|古野|１班 |A町1番地
[4]|杉本|２班 |B町2番地△△マンション
[5]|高島|３班 |E町3番地
[6]|川口|４班 |C町4番地
そしてこの元データは専用のシステムから落ちてきます。（頻度は班員が移動する度に、ですのでそれほど多くはありません。）
これだと、システムから落ちてくる度に、いったん元データを成形する必要がありますでしょうか。
（midnn） 2023/01/25(水) 10:18:52
レイアウトの想像は外れていましたが、最初に弁解しておきます。 ＞DG列に住所（○○町から始まり、番地やマンション名まで）が入っています。 ~~~~ 100列先のDG列かとも思いましたが、D～G列と考えた方が自然かなと思いました。
ご自身でお気付きになられていますが、 ピボットテーブルを作成した時点で住所はソートされているはずなので、 目視・手動で出来る規模なら、おっしゃる通りの対応で問題無さそうです。
また、今回は前方一致を見れば良いので、元データに以下のコードを用いて データを整えても良いかもしれません。
Sub Macro1()
Dim r As Range
Dim tmp$, str$
Range("E1") = "グループ"
For Each r In Range("D2", Cells(Rows.Count, "D").End(xlUp))
tmp = r.Value
Select Case True
Case tmp Like "A町*"
str = "A町"
Case tmp Like "B町*"
str = "B町"
Case tmp Like "C町*"
str = "C町"
Case tmp Like "D町*"
str = "D町"
Case Else
str = "その他"
End Select
r.Offset(, 1) = str
Next
End Sub
（黒豆） 2023/01/25(水) 18:57:45
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.