Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


22094 / 76734 ←次へ | 前へ→

【60025】Re:年間月別合計の出し方
発言  kanabun  - 09/1/24(土) 10:35 -

引用なし
パスワード
   ▼ひかる さん:
>▼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行×12列の各要素に
下のように行列番号をつけ、そこに、元データ表より該当する数値を集計
して いきましたよね?

>       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) にありますか?
という2つのことが分かれば、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 のなかに格納されていますから
      j▼
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()に対して行ったような解析
(コードのトレース)を日本語で行ってみて、コードの各行が何をして
いるものなのか、何をするためにこの一行があるのか、コメントをつけて
見てください。

5 hits

【59849】年間月別合計の出し方 ひかる 09/1/13(火) 20:39 質問
【59850】Re:年間月別合計の出し方 kanabun 09/1/13(火) 21:18 発言
【59855】Re:年間月別合計の出し方 ひかる 09/1/13(火) 22:47 質問
【59856】Re:年間月別合計の出し方 kanabun 09/1/14(水) 0:09 発言
【59857】Re:年間月別合計の出し方 kanabun 09/1/14(水) 0:15 発言
【59858】Re:年間月別合計の出し方 ひかる 09/1/14(水) 7:07 お礼
【59859】Re:年間月別合計の出し方 kanabun 09/1/14(水) 9:33 発言
【59886】Re:年間月別合計の出し方 ひかる 09/1/15(木) 16:07 質問
【59887】Re:年間月別合計の出し方 ひかる 09/1/15(木) 16:21 お礼
【59892】Re:年間月別合計の出し方 kanabun 09/1/15(木) 16:51 発言
【59949】Re:年間月別合計の出し方 ひかる 09/1/20(火) 18:29 質問
【59950】Re:年間月別合計の出し方 kanabun 09/1/20(火) 20:25 発言
【59954】Re:年間月別合計の出し方 ひかる 09/1/20(火) 21:35 質問
【59955】Re:年間月別合計の出し方 kanabun 09/1/20(火) 21:49 発言
【59956】Re:年間月別合計の出し方 ひかる 09/1/20(火) 22:09 質問
【59957】Re:年間月別合計の出し方 kanabun 09/1/20(火) 22:50 発言
【59958】Re:年間月別合計の出し方 kanabun 09/1/20(火) 23:04 発言
【59959】Re:年間月別合計の出し方 kanabun 09/1/20(火) 23:31 発言
【59960】Re:年間月別合計の出し方 ひかる 09/1/20(火) 23:42 お礼
【59981】Re:年間月別合計の出し方 ひかる 09/1/22(木) 12:02 お礼
【59983】Re:年間月別合計の出し方 ひかる 09/1/22(木) 12:52 質問
【59988】Re:年間月別合計の出し方 ひかる 09/1/22(木) 18:07 質問
【60025】Re:年間月別合計の出し方 kanabun 09/1/24(土) 10:35 発言
【60026】Re:年間月別合計の出し方 kanabun 09/1/24(土) 10:40 発言
【60036】Re:年間月別合計の出し方 ひかる 09/1/24(土) 19:18 お礼
【60054】Re:年間月別合計の出し方 ひかる 09/1/26(月) 11:50 質問
【60061】Re:年間月別合計の出し方 kanabun 09/1/26(月) 14:39 発言
【60097】Re:年間月別合計の出し方 ひかる 09/1/27(火) 12:42 お礼
【60105】Re:年間月別合計の出し方 kanabun 09/1/27(火) 15:56 発言
【60106】Re:年間月別合計の出し方 ひかる 09/1/27(火) 16:27 お礼
【59889】Re:年間月別合計の出し方 kanabun 09/1/15(木) 16:48 発言

22094 / 76734 ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free