投稿2020/03/30 09:49
編集2020/03/30 14:21利用記録というシートの各列に、
・利用開始時間
・利用終了時間
・利用者種類（3種
・利用物種類（2種
・利用日
・他
というデータが3万行ほどあります。
例：2行目
09:55:23| 11:29:51| 一般| ボール| 2019/4/5| 他項目| 他項目|・・・・
|:--|:--:|--:|
このデータから
日別時間帯別の集計をしなければなりません。
例：2019/4/5時間帯別利用者別種類別集計
|||9:0012:00|9:0012:00|12:0017:00|12:0017:00|17:0021:00|17:0021:00|
|:--|:--:|--:|
|||ボール|バット|ボール|バット|ボール|バット|||
|5|幼児・小|3|2|2|0|1|4
||中高生|4|4|3|5|1|4
||一般|2|4|2|0|5|6|
※ずれているかもです。すみません。
これを各月30日程度×12ヶ月分です。
一度データを配列に入れて、if分で
1．日付で
2.利用者で
3．利用種類で
4.時間で
と絞って記載しようとしましたが、非常に非効率的な気がします。
大まかな考え方、スマートな集計方法を教えていただけないでしょうか。
下記はとりあえず1日分のさらに9時~12時で抽出しようとしたものです。正常に動きますがこれを利用者分・時間帯別分、30日分、12か月分と思うと気が遠くなりそうです。
ちなみに・・・。表の様式は変えられません。
あとでループで廻そうとして変な変数を使っています。
VBA
1Sub 日別集計() 2 Dim i As Long, n As Long, m As Long 3 Dim y As String, z As String 4 Dim cnt(1 To 20) As Long 5 Dim buf As Variant 6 Dim x(1 To 3) As String 7 Dim dt As Date 8 9 x(1) = "幼児・小学生" 10 x(2) = "中・高校生" 11 x(3) = "一般" 12 13 Sheets("利用記録").Select 14 i = Sheets("利用記録").Cells(2, 1).End(xlDown).Row 15 buf = Sheets("利用記録").Range(Cells(2, 1), Cells(i, 12)).Value 16 Sheets("1").Select 17 cnt(20) = 1'--------------取り合えず1日分 18 19 For i = 6 To 13 Step 2 20 For n = 1 To UBound(buf) 21 dt = Range("a1") & "/" & Range("a7") & "/" & cnt(20) 22 If buf(n, 10) = dt Then 23 If Format(buf(n, 6), "Short Time") > "09:00" And Format(buf(n, 6), "Short Time") < "12:00" Then 24 If buf(n, 5) = x(1) And buf(n, 8) <> "ICT" Then 25 cnt(1) = cnt(1) + buf(n, 7) 26 Else 27 cnt(2) = cnt(2) + buf(n, 7) 28 End If 29 End If 30 End If 31 Next n 32 Cells(9, i) = cnt(1) 33 Cells(9, i + 1) = cnt(2) 34 cnt(1) = 0 35 cnt(2) = 0 36 Next i 37 38 39 40 41End Sub
ピポットテーブルは手付かずで、ご助言いただいたことを参考に勉強します。とりあえず回答順に参考にさせていただきました。記述も少なくて済み、処理時間もそれほど長くなかったのでシンプルに考えられました。
効率的といえばピポットテーブルだということも理解できました。
vba
1Sub 集計テスト() 2 Dim i As Long, n As Long, m As Long, l As Long 3 Dim x As String, y As String, z As String 4 Dim cnt(1 To 20) As Long 5 Dim buf As Variant 6 Dim ws As Worksheet 7 Dim rng As Range 8 9Application.ScreenUpdating = False 10 For i = 8 To 19 11 Sheets(i).Range("f9:m101").ClearContents 12 Next i 13 14 Set ws = Sheets("利用記録") 15 i = ws.Cells(1, 1).End(xlDown).Row 16 buf = ws.Cells(2, 1).Resize(i, 12).Value 17 18 For i = 1 To UBound(buf) 19 Sheets(Month(buf(i, 10)) & "月").Select 20 n = Day(buf(i, 10)) 21 Set rng = Range("b8:b101").Find(what:=n, lookat:=xlWhole).Offset(0, 3) 22 23 24 If buf(i, 5) = "幼児・小学生" Then 25 m = rng.Offset(0, 1).Row 26 ElseIf buf(i, 5) = "中・高校生" Then 27 m = rng.Offset(1, 1).Row 28 ElseIf buf(i, 5) = "一般" Then 29 m = rng.Offset(2, 1).Row 30 End If 31 32 If Format(buf(i, 6), "Short Time") > "09:00" And Format(buf(i, 6), "Short Time") <= "12:00" Then 33 l = 6 34 ElseIf Format(buf(i, 6), "Short Time") > "12:00" And Format(buf(i, 6), "Short Time") <= "17:00" Then 35 l = 8 36 ElseIf Format(buf(i, 6), "Short Time") > "17:00" And Format(buf(i, 6), "Short Time") <= "19:00" Then 37 l = 10 38 ElseIf Format(buf(i, 6), "Short Time") > "19:00" And Format(buf(i, 6), "Short Time") <= "21:00" Then 39 l = 12 40 End If 41 42 If buf(i, 8) = "バット" Then 43 l = l + 1 44 End If 45 46 Cells(m, l).Value = Cells(m, l).Value + buf(i, 7) '利用人数の項目があったためこれを追記 47 Next i 48 49Application.ScreenUpdating = True 50End Sub
皆さんありがとうございました。
回答4件
0
的外れな回答でしたらごめんなさい。
VBAの記述ではないので・・
レイアウトを作成しておいて、COUNTIFS関数を集計セルに入れてはどうですか？
３万件もあると重いでしょうか？
投稿2020/03/30 14:22
総合スコア119
0
ピボットテーブル推奨です
データ範囲を「名前」機能で可変対応できますので基本的にはVBA要らずです
とりあえずサンプル
見出し行があるかどうか、と項目の位置関係も不明なのでうまくいかなかったらごめんなさい
VBA
1Sub sample() 2 Dim r As Range 3 Dim ri As Range 4 5 'とりあえず新規Bookにコピーしてテスト 6 Sheets("利用記録").Copy 7 'CurrentRegionでデータ範囲が取得できれば、の場合 8 Set r = ActiveSheet.Range("A1").CurrentRegion 9 '1行目が見出し行だとして、空白セルがあればエラーになるので仮埋め。本来必要なし 10 For Each ri In r.Rows(1).Cells 11 If ri.Value = "" Then ri.Value = ri.Address(0, 0) 12 Next 13 '列追加して時間帯判定数式セット 14 With r.Offset(, r.Columns.Count) 15 .Columns(1).Formula = "=IF(A1<TIMEVALUE(""12:00""),""-12"",IF(A1<TIMEVALUE(""17:00""),""-17"",""-21""))" 16 .Item(1).Value = "時間帯" 17 End With 18 'データ範囲再セット 19 Set r = r.CurrentRegion 20 'ピボットテーブル作成 21 With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 22 SourceData:=r).CreatePivotTable("") 23 .ColumnGrand = False 24 .RowGrand = False 25 .RowAxisLayout xlTabularRow 26 .PivotFields(r(5).Value).Orientation = xlRowField 27 .PivotFields(r(3).Value).Orientation = xlRowField 28 With .PivotFields("時間帯") 29 .Orientation = xlColumnField 30 .Subtotals(1) = False 31 End With 32 .PivotFields(r(4).Value).Orientation = xlColumnField 33 .AddDataField .PivotFields(r(6).Value), "Count", xlCount 34 End With 35End Sub
投稿2020/03/30 14:17
編集2020/03/31 03:49
総合スコア52
0
「日別時間帯別の集計をしなければなりません。」
とのことですが、
サンプルの出力レイアウトの見出し部分がよくわからず、
要求が不明確なように思います。
3時間ごとの時間帯別に管理するのですかね？
私だったら・・・以下のようにします。
１）オリジナルのデータを分解
時間帯をまたぐ場合はまたいだ数だけ行を増やす。当然開始日時と終了日時も変わるが、後続処理には使わないと思われる。この段階で 時間帯 という項目が増える。
オリジナルのシートとは別のシートに展開する。Input２とする。
VBAでInputを全件処理します。2行目からwhile loopでセルの中身が空になるまで順次下に展開、
Cellに値をセットするときは、.value= xxxx のように .value を明示してください。暗黙にすると遅いです。
それと・・・すみません単語がでませんが、画面描画を止めるメソッドを呼び出さないと遅くなります。
２）案１）ゴリゴリ集計
私は古い人間なので、VBAだったとしてもキーブレイク処理を使います。
詳細はネットで検索したほうがわかりやすいです。
Inputのシートを日付＋時刻分類＋etcでソートした状態であることが前提です。
Outputは同じBookの別シートとします。
３）案２）ピボット集計
Excelのピボット機能を使ったほうが欲しいレイアウトがすぐに得られると思います。
ピボットは多次元DB的な使い方ができるのでいろんな軸に切り替えることができるのでVBAで一辺倒なロジックを書くよりよいかも。
ピボットで使いたい項目がない場合は、上流の処理の中で作り出すとよいです。
グラフにも連動するのでこっちがおすすめです。
3万件ぐらいならOKですよ。
投稿2020/03/30 13:58
総合スコア40
0
ベストアンサー
以下のようにされてはいかがでしょうか。
１．各月の集計シートの集計カウントは予め０クリア（もしくは空白）になっているものとする。
（何回もマクロを実行するなら、このクリアする処理を最初に行うようにする）
２．利用記録シートを順に1行ずつ、以下のように処理する。
１）日付の月から、集計シートを決定する。
２）日付の日及び利用者から、集計シートの集計対象行を決定する。
３）利用物及び利用開始時間帯から、集計シートの集計対象列を決定する。
４）上記で決定された集計シートの該当セルへ１加算する。
３．上記の２の処理を利用記録シート最後の行まで繰り返す。
投稿2020/03/30 13:27
総合スコア5493
あなたの回答
tips
プレビュー
Q&A
