マクロで時間の合計・平均計算

質問

いつもお世話になっています。
Excel2000でセルに表示された時間の平均を計算するマクロを作っています。
セルB4・B5・B6にはそれぞれ"00:00:01"・"00:32:54"・"02:33:12"が表示されています。
セルの書式設定はユーザ定義型で"hh:mm:ss"です。
B4からB6の合計時間を求めて、B7(書式設定は標準)に表示させるまでは
　　　Worksheets("Sheet1").Select
　　　　　　Range("B7").Select
　　　　　　ActiveCell.FormulaR1C1 = "=sum(R[-3]C:R[-1]C)"

で"03:06:07"と表示できたのですが、"=average(R[-3]C:R[-1]C)"とすると
"09:02:02"と合計でも平均でもない値が表示されてしまいました。
"=sum(R[-3]C:R[-1]C)/3"とすると"0.3764"と小数で表示されてしまいます。

それならばと、上記述の下に"MyTime=ActiveCell.Value"や"MyTime=Range("B7").Value"と記述してみたのですが、小数で値が入ってきてしまいます。
"03:06:07"さえ取れれば、あとは文字列を数値に変換して無理やり計算しようと
思っているのですが、それすらできず、困っています。

割り切れない秒数は切り上げにするとして、
B4からB6の時間の平均"01:02:03"を出すにはどうしたらよいのでしょう？

どなたかご存知の方、教えてください！！

回答No.5

osamuy · ベストアンサー

nishi6さんの回答をみて、ちょっと改良：

Public Function avg_time(cells_ref As Range) As Date
Dim c As Range
avg_time = CDate(0)
For Each c In cells_ref
avg_time = avg_time + CDate(c.Text)
Next
With cells_ref
avg_time = avg_time / (.Rows.Count * .Columns.Count)
End With
End Function

やっぱりiterateできるのか。

お礼

回答ありがとうございます。
お礼が遅くなってすみません。
No.3にも回答頂いていますが、こちらにまとめてお礼いたします。

『(経過)時間』とわけのわからん書き方をしていた、セルに表示される値ですが、

　　例）　赤坂さん(仮)：8時00分00秒～8時00分01秒まで勤務　⇒　B4には"00:00:01"
　　　　　井上さん(仮)：8時20分00秒～8時52分54秒まで勤務　⇒　B5には"00:32:54"
　　　　　植村さん(仮)：8時00分10秒～10時33分22秒まで勤務　⇒　B6には"02:33:12"

　　　とそれぞれ表示されます。B7には3人の平均勤務時間"01:02:02"が表示されます。
のでB4～B6の値は24時間を超えることはないです。
その日一日の勤務時間累積といった意味合いで使っていました。
『(経過)時間』と略さずにちゃんと書かないとだめですよね、すみません。

改良していただいたソース、コピペで動きました。
ありがとうございます。
ソースの内容ですが、『For Each ○○ In △△』を見たのは初めてなので
勉強しながら理解していこうと思います。
（今はまだ何が起こっているかわからない状態なので…）

色々と時間を割いて頂き、本当にありがとうございました。
また機会がありましたらよろしくお願いします（ペコリ）。

回答No.4

nishi6

＞09:02:02と合計でも平均でもない値が表示されてしまいました。
『0:00:01』は本来、『24:00:01』が実際の値で、計算上は09:02:02が正しいのではないでしょうか。

書式定義で表示された、見た目の値での平均を出すには小細工が必要でしょう。
質問に提示のマクロでは、B7に算式をセットするようにしてありますが、B7にセットすれば平均を計算するユーザー定義関数を作ってみました。

ただ、27:00:01が実際の値の場合、03:00:01と表示されます。下のユーザー定義関数ではこれも見た目のまま計算しています。この辺が質問からは読み取れない箇所です・・・

マクロの中に絶対番地をセットするのもおかしいので、ユーザー定義関数は、指定したセル範囲について、質問の意味の平均を計算します。

B7セルに　=TimeAverage(R[-3]C:R[-1]C)　とセットすれば、B4:B6の平均を計算します。
ご自分のマクロに応用して下さい。

Public Function TimeAverage(rgArea As Range)
　　Dim rg As Range 'Averageを計算するセル
　　Dim countTime As Double 'セルの個数
　　Dim sumTime As Double '経過時間計(24時間未満を計算する)

　　For Each rg In rgArea
　　　　countTime = countTime + 1
　　　　'24時間未満を集計する
　　　　sumTime = sumTime + rg.Value - Int(rg.Value)
　　Next
　　'平均を計算
　　TimeAverage = Format(Application.Ceiling(sumTime / countTime, "0:0:1"), "hh:mm:ss")
End Function

お礼

いつも丁寧な回答・解説・ソースをありがとうございます。
コピペで動きました。
『For Each ○○ In △△』を見たのは初めてなので、
どういう具合に動いているのかはまだわかっていないのですが
これを機会に調べて身に付けます！

ありがとうございました。
また機会がありましたらよろしくお願いします(ペコリ)。

回答No.3

osamuy

『(経過)時間』の定義が、よく分かりませんが、
セルの表示どおりの値による、時間の平均って事で、こんな感じでしょうか：

Public Function avg_time(cells_ref As Range) As Date
Dim d As Date, n As Integer
Dim r1 As Integer, r2 As Integer, r As Integer
Dim c1 As Integer, c2 As Integer, c As Integer
d = #12:00:00 AM#
With cells_ref
n = .Rows.Count * .Columns.Count
r1 = .Row
c1 = .Column
r2 = r1 - 1 + .Rows.Count
c2 = c1 - 1 + .Columns.Count
End With
For r = r1 To r2: For c = c1 To c2
d = d + CDate(cells_ref(r, c).Text)
Next: Next
avg_time = d / n
End Function

回答No.2

comv

こんにちは！

ご質問はマクロ上で使うシート関数式での処理で
よろしいのでしょうか
であれば

>アドバイス通り、"[h]:mm:ss"にするとB4が
>"24:00:01"となり
最初の時間のみ１日（２４時間）以上経過した時間の
ようですがそれを無視してあくまでも表示時間の
平均で
=CEILING(AVERAGE(MOD(R[-3]C,1),R[-2]C:R[-1]C),"0:0:1")

３つのセルの合計が２４時間を超えない前提であれば
=CEILING(MOD(SUM(R[-3]C:R[-1]C),1)/3,"0:0:1")
　（２４時間÷３＝９時間　２４時間以内であれば
　　３セルの平均は必ず9:0:0未満とする処理です）
２番目の式ではシリアル値での表示になるので
時刻表示にするには
=TEXT(CEILING(MOD(SUM(R[-3]C:R[-1]C),1)/3,"0:0:1"),"hh:mm:ss")

　としては如何でしょうか

お礼

comvさん、こんにちは。
お礼が遅くなってすみません。

>ご質問はマクロ上で使うシート関数式での処理で
>よろしいのでしょうか
はい。シート上にコマンドボタンを一つ置いて、それをクリックすると
平均値を算出、という風に使っています。

アドバイス頂いたCEILING～を貼り付けて実行するとエラーが出てしまいました(涙)。
が、今回CEILING関数を初めて知ったので調べてからもう一度挑戦してみます。

ありがとうございました。
また機会がありましたらよろしくお願いします(ペコリ)。

回答No.1

osamuy

B4:B6に対する表示形式を“[h]:mm:ss”とどうなりますか?

なんか、1日分足すと、計算が合いそうなんですけど。

補足

早速の回答ありがとうございます。
アドバイス通り、"[h]:mm:ss"にするとB4が"24:00:01"となり、
計算は合っているのがわかりました。

ですが、B4からB6に表示しているのは『時刻』ではなくて『(経過)時間』なので、
欲しい結果とは異なってしまうのです・・・。