| |
▼ひかる さん：
>▼kanabun さん：
>最後最後といいながら、何回も質問してすみません。
>前々回にこれで項目が増えても対応できますと書いたのですが、やはりうまくいきませんでした。
>大人/小人、男/女以外に、元表に仮に、会員/非会員など項目が増えた場合VBAコードのどこを修正すれば、できますか。
それには、これまでのコードをおさらいしてみるといいです。
まず、最初の Sub Try1()の考え方から。
> はじめに、データが次のようにあります。
> 会議室 研修室 和室 調理室
> 04月15日 560 800 1000 500
> 04月19日 1500 600 5200 1500
> 05月21日 800 2000 800 2100
> 05月31日 5000 480 2500 600
> 06月13日 4500 3600 4800 1600
> 06月18日 100 3600 8400 2650
> 06月21日 7000 600 8000 7900
>
> このデータを使って、次のように集計月別の年間集計するには、どうしたら
> 4月 5月 6月 7月 8月 9月 10月 ・・・1月 2月 3月
> 会議室 2060 5800 11600
> 研修室 1400 2480 7800
> 和室 6200 3300 21200
> 調理室 2000 2700 12150
というときに、どうしましたか？
集計先の書き込むデータ範囲を変数tblにして、その４行×１２列の各要素に
下のように行列番号をつけ、そこに、元データ表より該当する数値を集計
して いきましたよね？
> 4月 5月 6月 7月 ・・・ 3月
> 会議室 (1, 1) (1, 2) (1,3) (1,4) ・・・ (1,12)
> 研修室 (2, 1) (2, 2) (2,3) (2,4) ・・・ (2,12)
> 和室 (3, 1) (3, 2) (3,3) (3,4) ・・・ (3,12)
> 調理室 (4, 1) (4, 2) (4,3) (4,4) ・・・ (4,12)
このtblという配列内の位置が行列番号で分かれば、その位置にデータを
加算していけばいいわけです。
たとえば、元データが
> 会議室 研修室 和室 調理室
> 04月15日 560 800 1000 500
のようだったとき、
04月15日を →「4月」になおして、
「会議室」「4月」の位置に 560 を加算し、
「研修室」「4月」の位置に 800 を加算し、
・・・
「調理室」「4月」の位置に 500 を加算する、
ということをするために、
Q1. 書きこみ先の「会議室」は何行目(n) にありますか？
Q2. 書きこみ先の「4月」は何列目(m) にありますか？
という２つのことが分かれば、tbl(n, m) の位置にデータ(数値)を
足してやればいいのだから、
Sub Try1()では、
あらかじめ
「会議室」 = 1
「研修室」 = 2
「和室」 = 3
「調理室」 = 4
という行番号と、
「4月」 = 1
「5月」 = 2
・・・
「2月」 = 11
「3月」 = 12
という列番号を Dictionary オブジェクト (ここで 変数dicとして
いるものがそれ) に、番号を記憶させています。
Sub Try1()で
↓の部分がそれをしているところであることはお分かりですか？
> '行列見出し位置を辞書に格納
> For Each c In r.Resize(, 1).Offset(, -1) 'A列見出し項目（室）
> i = i + 1
> dic(c.Text) = i
> Next
> i = 0
> For Each c In r.Resize(1).Offset(-1) '1行目見出し項目（月）
> i = i + 1
> dic(c.Text) = i
> Next
Sub Try1()の
そのあとの部分は、
> '---- 元表のA列×1行目の見出しに対応する 書きこみ先の _
> 配列tblの 行列番号を Dictionaryから得る -----
> Dim rr As Range
> Dim MonData, RoomData, dat, ss As String
> Dim j As Long, n As Long, m As Long
>
> Set rr = Worksheets("Sheet1").Range("A1").CurrentRegion.Resize(, 5)
> Set rr = Intersect(rr, rr.Offset(1, 1))
> MonData = rr.Resize(, 1).Offset(, -1).Value
> RoomData = rr.Resize(1).Offset(-1).Value
> For i = 1 To UBound(MonData, 1)
> dat = MonData(i, 1)
> If IsDate(dat) Then
> ss = Month(dat) & "月"
> If dic.Exists(ss) Then
> m = dic(ss) '何列目か
> For j = 1 To UBound(RoomData, 2)
> ss = RoomData(1, j)
> If dic.Exists(ss) Then
> n = dic(ss) '何行目か
> '------tblのn行,m列目の要素に 数量を累加-
> tbl(n, m) = tbl(n, m) + rr(i, j).Value
> End If
> Next
> End If
> End If
> Next
元表を 行順、列順で2重ループして↓
> For i = 1 To UBound(MonData, 1)
> For j = 1 To UBound(RoomData, 2)
各セルのデータを tbl配列の(n行, m列)目に集計していっている
ところです。
まず、
> For i = 1 To UBound(MonData, 1)
で行順に読んでいって、その行が何月のデータであるかを取得し、
月の名前に対応する書きこみ先tblの列番号を
> m = dic(ss) '何列目か
のように、dicから取得しています。これは
dic("4月") = 1
のように「4月」に対応する列番号がdictionaryに記憶されているからです。
何列目に書き込むかは、元表のA列の日付をみれば分かりましたから、
2番目の（内側の）
> For j = 1 To UBound(RoomData, 2)
で、今度は 列見出し ▼
> 会議室 研修室 和室 調理室
> 04月15日 560 800 1000 500
が 配列変数 RoomData のなかに格納されていますから
ｊ▼
RoomData(1, 1) = "会議室" → dic("会議室") = 1 → n = 1
RoomData(1, 2) = "研修室" → dic("研修室") = 2 → n = 2
RoomData(1, 3) = "和室" → dic("和室") = 3 → n = 3
RoomData(1, 4) = "調理室" → dic("調理室") = 4 → n = 4
のように、書き込み先の n （行番号）を取得して、
tbl配列の(n行,m列)目に 数値データを加えているわけです。
> '------tblのn行,m列目の要素に 数量を累加-
> tbl(n, m) = tbl(n, m) + rr(i, j).Value
ですから、Try1()でやっていることが分かれば、
Sub Try2() や Sub Try3()や Sub Try4() も、Try1()の応用で
できることなのです。
集計先の列見出しと行見出し番号を dicに格納して、
元表のほうを順に 指定された行番号の指定された列位置に加算して
いけばいいわけです。
ただし、集計表の行見出しが複数項目になるばあい
> 集計表
> A B C D E
> 1 4月 5月 6月
> 2 会議室 大人
> 3 小人
> 4 研修室 大人
> 5 小人
> 6 和室 大人
> 7 小人
> 8 調理室 大人
> 9 小人
は、dicに格納する tbl配列の行番号は A列とB列を結合したものに
して行を特定する、とか、
dic("会議室大人") = 1
dic("会議室小人") = 2
dic("研修室大人") = 3
dic("研修室小人") = 4
・・・ "会議室"だけでは行が特定できない
一つの元表から、
>>> A B C D E F G
>>>----------------------------------------------------------------------
>>>1 日付 利用者 性別 会議室 研修室 和室 調理室
>>>2 4月15日 大人 男 560 800 1000 500
>>>3 4月19日 小人 女 1500 600 5200 1500
>>>4 5月21日 大人 女 800 2000 800 2100
>>>5 5月31日 小人 女 5000 480 2500 600
>>>6 6月13日 小人 男 4500 3600 4800 1600
>>>7 6月18日 大人 女 100 3600 8400 2650
>>>8 6月21日 大人 男 7000 600 8000 7900
複数形式の集計をしたい
▼Case 1
> 4月 5月 6月 7月
> 1 会議室 大人
> 2 会議室 小人
> 3 研修室 大人
> 4 研修室 小人
> 5 和室 大人
> 6 和室 小人
> 7 調理室 大人
> 8 調理室 小人
>
▼Case 2
> 4月 5月 6月 7月
> 1 会議室 男
> 2 会議室 女
> 3 研修室 男
> 4 研修室 女
> 5 和室 男
> 6 和室 女
> 7 調理室 男
> 8 調理室 女
という場合のように、
集計先のテーブルに合わせて、元表の結合する列を特定する処理が追加される、
とか
そういう応用処理が加わっているだけです。
いちど、ひかるご自身で、ぼくがTry1()に対して行ったような解析
（コードのトレース）を日本語で行ってみて、コードの各行が何をして
いるものなのか、何をするためにこの一行があるのか、コメントをつけて
見てください。
|
|
kanabun
- 09/1/24(土) 10:35 -
