Excel VBA質問箱 IV

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

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


1003 / 13644 ツリー ←次へ | 前へ→

【76904】日時の集計 chou 15/4/9(木) 16:27 質問[未読]
【76906】Re:日時の集計 β 15/4/9(木) 18:10 発言[未読]
【76908】Re:日時の集計 chou 15/4/10(金) 11:03 発言[未読]
【76909】Re:日時の集計 β 15/4/10(金) 13:16 発言[未読]
【76910】Re:日時の集計 β 15/4/10(金) 13:37 発言[未読]
【76911】Re:日時の集計 β 15/4/10(金) 13:47 発言[未読]
【76912】Re:日時の集計 15/4/10(金) 20:34 発言[未読]
【76932】Re:日時の集計 chou 15/4/14(火) 15:02 お礼[未読]

【76904】日時の集計
質問  chou  - 15/4/9(木) 16:27 -

引用なし
パスワード
   下記のデータの経過時間の集計について質問です。
開始、更新、終了は、YYYY/MM/DD hh:mm:ssが入っています。
集計は何日何時間何分何秒で結果を出したいです。

  A   B    C   D   E    F      G
1 番号  カテ1 カテ2 状況    開始   更新    終了
2 A12345 あ   1     新規    2015/1/1 2015/1/1
3 A12345 あ   1   継続    2015/1/2 2015/1/2 2015/1/3
4 B12345 あ   1   新規    2015/1/4 2015/1/4
5 C12345 あ   2   新規    2015/1/4 2015/1/5
6 D12345 あ   2   新規    2015/1/7 2015/1/8
7 D12345 あ   2   継続    2015/1/8 2015/1/9
8 D12345 い   1   継続    2015/1/9 2015/1/9
9 D12345 い   1   継続    2015/1/12 2015/1/12 2015/1/14

1.各行の経過時間を算出
 1行目であれば、
 開始時間から更新時間、更新時間から終了時間までを出して、
 その合計と平均をだしたい。

2.開始時間から終了時間までの合計と平均。
 A列の番号が複数だった場合は、最初の開始と最後の行の終了時間をとって集計したい。
例えば、A12345の場合は、1行目の開始と2行目の終了、
D12345の場合は、6行目の開始から9行目の終了時間を取る。

その集計方法がわかれば、VBAはかけると思うので、方法だけでも教えていただきたいです。

よろしくお願いします。

【76906】Re:日時の集計
発言  β  - 15/4/9(木) 18:10 -

引用なし
パスワード
   ▼chou さん:

こんにちは

1.は簡単ですよね。数式でもいけると思います。
日時(1)-日時(2) で、その間の時間がシリアル値で取得できます。
(シリアル値:整数部分に日数、小数点以下で、24時間中のどれだけかをあらわした数値)
必要なら、そのシリアル値から Day関数、Hour関数、Minute関数、Second関数で、その値を取り出すこともできます。
合計は簡単ですよね。平均はAverage関数あたりが使えると思います。

2.は、ループ処理が必要になりますね。いろんな処理方法が考えられますが、たとえば

・2行目からデータ最終行の次の行までループで繰り返し処理
  ・2行目なら開始時間と終了時間をそれぞれの変数に格納
  ・3行目以降なら、
     ・番号が1つ上のセルと同じなら、終了時間を置換
     ・番号が異なれば、すでに格納してある開始時間と終了時間から、1.で説明した関数などでその間の時間を取得し、1つ上の行の答えとし
      さらに、この行の開始時間と終了時間で変数置換

・ループが終了すれば、あとは 合計、平均は 1.と同じ要領で。

【76908】Re:日時の集計
発言  chou  - 15/4/10(金) 11:03 -

引用なし
パスワード
   ありがとうございます。

やはりループ処理しかないですよね。。

件数(行数)が10000行位あるので、ループも悩んでいたところでした。

ループさせる以外で方法のアイデアがある方いらっしゃいましたら、

ぜひ、ご教示ください。

よろしくお願いいたします。

【76909】Re:日時の集計
発言  β  - 15/4/10(金) 13:16 -

引用なし
パスワード
   ▼chou さん:

1.はループなしで、各行に、一度のセットで数式を埋め込むことができます。
2.はループ処理必須だと思いますが、結果を毎回セルに書き出さず、配列やDictionaryといったものに
格納しておき、最後に一度、どさっと転記すれば、10万行程度ならそんなに時間はかからないと思いますが?

【76910】Re:日時の集計
発言  β  - 15/4/10(金) 13:37 -

引用なし
パスワード
   ▼chou さん:

↑ と、大見得をきりましたが、実際にどれぐらいかかるか?
以下でお試しください。
H列に所要時間を記載、データ最終行の下のH列に合計と平均を記載します。

Sub Test()
  Dim w As Variant
  Dim c As Range
  Dim st As Double
  Dim ed As Double
  Dim tot As Double
  Dim cnt As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 7)
    ReDim w(1 To .Rows.Count, 1 To 1)
    w(1, 1) = "所要時間"
    For Each c In .Columns(1).Offset(1).Resize(.Rows.Count + 1).Cells
      If c.Row > 2 Then
        If c.Value <> c.Offset(-1).Value Then
          w(c.Row - 1, 1) = ed - st
          tot = tot + ed - st
          cnt = cnt + 1
        End If
      End If
      If c.Row > .Rows.Count Then Exit For
      st = c.Offset(, 4).Value2
      ed = c.Offset(, 4).Value2
      If Not IsEmpty(c.Offset(, 6)) Then ed = c.Offset(, 6).Value2
    Next
    .Columns(8).Value = w
    .Range("G" & .Rows.Count).Offset(1).Value = "合計"
    .Range("H" & .Rows.Count).Offset(1).Value = tot
    .Range("G" & .Rows.Count).Offset(2).Value = "平均"
    .Range("H" & .Rows.Count).Offset(2).Value = tot / cnt
  End With
End Sub

【76911】Re:日時の集計
発言  β  - 15/4/10(金) 13:47 -

引用なし
パスワード
   ▼chou さん:

出先のPC(WIn7+xl2010 スペックは中ぐらい)で、10万行のテストデータを作成し
アップしたコードで実行しますと、2.8〜2.9秒でした。
10万件なので、まぁ、これぐらいなら許してもらえませんかね?

【76912】Re:日時の集計
発言    - 15/4/10(金) 20:34 -

引用なし
パスワード
   こんにちは。

ピボットテーブルはいかがですか。
下準備として、最初に一回 手作業でピボットテーブルを作成しておいて、
マクロでは、データを差し替えてピボットテーブルの更新ボタンを押し、
日数計算用の数式を入力します。

【76932】Re:日時の集計
お礼  chou  - 15/4/14(火) 15:02 -

引用なし
パスワード
   皆様

ありがとうございました。

ループで頑張ってみようと思います。

また、わからないことなどありましたら、ご質問させていただきたいと思います。

ありがとうございました。

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