Excelで時刻の平均を出すには？

初めて質問します。
Excel2002を使って40名くらいの一週間の就寝時刻のデータの整理をしているのですが、
時刻の平均がうまく出せません。

たとえば就寝時刻が21:00と1:00だったら平均は23:00だと思うのですが、AVERAGEの関数（というのでしょうか？）で計算すると11:00になってしまいます。

1:00を25:00にすれば計算できるのですが、セルの書式を時刻の13:30という設定にして、
もうすでに膨大な量を入力してしまっています。

24時間表記のまま、時刻の平均を出す良い方法はありますでしょうか。
もしくは、
24時間以上の表記に簡単に直せる方法があれば教えてください。よろしくお願いします！

No.3 ベストアンサー 回答者： imogasi 回答日時： 2005/11/06 20:49

＃２の補足に関して

＞例として示してくださった　=IF(A2<4,A2+24,A2)）
（すみません、この式はイメージで書いてしまっていて、不適当でした。無視して、下記をご参考に）
例データ
A列A２,A３に
１：００
２３：００
D1に
４：００
とあるとします。
B2に=IF(A2-$D$1>0,A2,A2+1)
と入れてB3に式を複写します。
またB2,B3の書式をユーザー定義[h]mm:ssとします。２４時間を越えた時刻表示をするようにします。
結果B2,B3は
25:00:00
23:00:00
となります。
加えると48:00:00
となって２で割ると２４時になります。
ーーーー
しかし、時刻を２３：００のように「：」を使って入力しましたか。

この回答へのお礼

ありがとうございます！！！
無事に25:00:00といった表記になりました。
これで先に勧めます（涙）

データで時刻を扱う場合、「：」はよくないのですね？見やすさ重視で入力しておりました。
勉強不足でした。反省します。

お礼日時：2005/11/06 21:39

No.5 回答者： imogasi 回答日時： 2005/11/06 22:22

＃３です。

お礼の部分に関して。
＞データで時刻を扱う場合、「：」はよくないのですね？
いえいえ、時刻は「：」を使って入力してください。何かと便利ですよ。計算や表示形式の利用などで。文字列で入力されるのが、困る場合が多いと思います。

この回答へのお礼

アドバイスをありがとうございます！
Excelは奥が深いのですね…
これからもっと勉強します。
ありがとうございました！！

お礼日時：2005/11/06 22:48

No.4 回答者： Wendy02 回答日時： 2005/11/06 21:30

こんばんは。

例:

　 A列　　　　B列
　表示　　　実際の計算（検査用）
　13:30　　 25:30
　 0:00　　 24:00
　 1:00　　 25:00
　 2:00　　 26:00
　11:00　　 23:00
--------　　------

A6:
=SUM(IF(HOUR(A1:A5)<7,A1:A5+1,IF(HOUR(A1:A5)<19,A1:A5+0.5,A1:A5)))/COUNT(A1:A5)

配列数式ですから、一旦式を入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定してください。

実際に、昼夜逆の人がいなければ、この計算で何とかなるかと思います。未だ、十分に精査されていませんのが、今のところ、問題は見当たりません。

7時というものを、境目にしていますが、これは、6時(18時）でも5時でも構いません。

なお、HOUR関数で括らないと、万が一、通常の24H 方式で入力された時に、間違った計算が出てきます。

今回、検査のために使ったB列の検査数式
=IF(HOUR($A$1:$A$5)<7,$A$1:$A$5+1,IF(HOUR($A$1:$A$5)<19,$A$1:$A$5+0.5,$A$1:$A$5))

もし、正しいようでしたら、検査数式は必要ありません。元の式と同じ行でないと、出てきません。同じ行でない場合は、5行の範囲を選択して、上記の配列の確定(Shift+ Ctrl+ Enter)で確定しなければなりません。また、外すときは、範囲を選択して、F2を押し、Ctrl+Enterでしか外れません。

40名×7 =280 個のデータでしたら、別シートに数式で出して、検査してもよいかもしれません。

この回答へのお礼

回答ありがとうございます。
上の配列数式というので簡単に出ました！
下の方の方法で出した時間と一致しています。

検査数式というのがまだよく理解できておりませんが、今後の参考にさせていただきます。
本当に救われました！感謝であります！！

お礼日時：2005/11/06 22:39

No.2 回答者： imogasi 回答日時： 2005/11/06 19:00

浅学非才の身ですので、断定はできませんが、

この問題はエクセルの問題ではないと思います。エクセルは計算に使うだけで、その前の設計段階の問題でしょう。時間なら平均が考えられるが、時刻では、幅を区切らないと平均が意味がないのではないでしょうか。永久に流れる時刻の中の平均は無意味でしょう。
ですからこういうことです。
就寝時間のスタートを何時、終わりは夜１２時ではなく2時３時４時ぐらいを、サンプル・グループの実情に合わせて、合理的に決める。
その場合は夜１時は２５時、夜２時は２６時として、関数（たとえば=IF(A2<4,A2+24,A2)）で計算して
出し（ただし１４時を、２時就寝と記した者はないものとする）、そして平均を出せば
どうでしょう。平均は数直線上にプロットできるものでないと、意味がなく、その最初と最後の線の重心に当たるものと、学校で勉強しました。ですから、夜１２時以後が、折り返すような数値になってはならず、延長線上にないといけないと思います。
これで都合が悪い場合は、補足して、OKWave読者に知らせてください。

この回答への補足

回答ありがとうございます。
がむしゃらに、ただ時間をエクセルに入力していたのでそこまで考えておりませんでした。
やはり24時間表示ではない方が良いかもしれませんね。

関数に関しても全く知識がございません。
例として示してくださった　=IF(A2<4,A2+24,A2)）
はどのように使えばいいのでしょうか・・・

補足日時：2005/11/06 19:32

No.1 回答者： thrush76 回答日時： 2005/11/06 16:41

日にちを設定してはどうでしょう。

数値の扱いとして、
「1900/1/0 21:00」と「1900/1/0 1:00」
の平均を出しているため11:00になってしまいます。
日を一日ずらして、
「1900/1/0 21:00」と「1900/1/1 1:00」
の平均を出せば、23:00になるでしょう。

この回答への補足

回答ありがとうございます。
既に入力されている時間に日にちを設定するには、やはり一つ一つ直していかなければならないのでしょうか？

補足日時：2005/11/06 19:32

この回答へのお礼

日にちの設定は今後の参考にさせていただきます！
ありがとうございました。

お礼日時：2005/11/06 23:06