プロが教えるわが家の防犯対策術!

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

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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

#2の補足に関して


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

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

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

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

#3です。

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

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

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

こんばんは。



例:

  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 個のデータでしたら、別シートに数式で出して、検査してもよいかもしれません。
    • good
    • 0
この回答へのお礼

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

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

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

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


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

この回答への補足

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

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

補足日時:2005/11/06 19:32
    • good
    • 0

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


数値の扱いとして、
「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
    • good
    • 0
この回答へのお礼

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで平均退社時間を出したいと思っています。

エクセルで平均退社時間を出したいと思っています。
時間を18:30や19:45と入力していき、平均の時間を出す方法を教えてください。よろしくお願いします。

Aベストアンサー

=AVERAGE(時間が入っているセル範囲)

で、出ませんか?
※表示形式を「時刻」や「h:mm」にしてますか?

Qエクセルで平均時間の表示の仕方

色々と他の質問を見たのですが、自分と同じ状態なのかも解らず…
既出でしたらすみません。

エクセルで時間の表を作成しています。
例えば(1)さんの合計145:00
   (2)さんの合計180:00
   (3)さんの合計166:20
の平均時間を出したいのですが、どうしたら良いのでしょうか?
よろしくお願いします。

Aベストアンサー

#DIV/0!が出るということは、もしかしてデータは文字列に
なっているのではないでしょうか?
SUM()で囲んでも 0になるのでは・・。

セルの書式設定が文字列になっていたり、頭に'が入ってないか
余分なスペースが無いか確認してみて下さい。
または、セルの参照先が間違っているとか・・。

A1に 148:00があるとして、=VALUE(A1) で下までコピーし
最後にAVERAGE()を入れて、セルの書式設定を[h]:mm:ss
としてみてはいかがでしょう。

Q時間の平均を計算する方法

初めまして。

Excelで
「○○時間▲▲分」となっている平均時間の算出方法を
ご教示していただきたく投稿させていただきます。

=「○○時間▲▲分」/(ex.)3
としても、やはり文字が含まれている為か、#value! となります。

セルの書式設定⇒ユーザー定義で「G/標準"時間"」に変換してから
平均をだし、ユーザー定義の「数値」で小数点の位置変更で
することも上司から教わりできたのですが今日してみるとユーザー設定で
変更しても「G/標準"時間"」の形に変化せず…

知識が乏しく検索方法にも困っており、皆様方の知識や常識を
お教えいただきたく、どうぞよろしくお願い致します。

Aベストアンサー

この質問文から幾つかの要素に分けて回答したいと思います.
先ずセルの書式設定について,質問者さんが言っているのは"表示形式"の部分ですね.ここはセルに入力した値(数値)がどの様に表示するのかを設定する部分です.

例えばセルの値に0.25と入力されているとしましょう.このセルの表示形式を時刻(h:mm)の形式で表示させれば6:00になります.
これは1日という時間を1という数値にしているという事で,1時間であれば1/24≒0.041666・・・という数値になるという意味になります.

では質問文のG/標準"時間"というのはG/標準の形式に時間という言葉を末尾に入れるという意味を示しています.この場合セルの入力値は数値ですから,四則演算,関数を入れて計算する事ができる事になります.
(セルの表示では1時間でも,数式バーでは1になっていますよね)

では質問文のG/標準"時間"を設定するためにはどうすれば良いかについて回答します.
これはセルの書式設定⇒表示形式⇒ユーザーの定義までを選んで,種類(T)の下の入力部にG/標準"時間"を書けばよいという事です.

では主題である,時間の平均を求める方法についてです.
質問文から予想するに,例えばA列に時間情報が入力されているとします.
A1:1時間30分
A2:2時間15分
A3:3時間00分

本来なら以下の様な関数で求まります.
=Average(A1:A3)

但し,A1~A3の入力値が数値ではなく,文字であるなら,一旦数値に直す必要があります.
例えばA1~A3を選択した状態で置換すると,検索する文字列は時間,置換後の文字列は:,再置換で検索する文字列は分,置換後の文字列は空白(何も入力しない)という一連の作業をすれば

A1:1:30
A2:2:15
A3:3:00

という表示になります.
この状態であれば,文字ではなく数値(表示形式がh:mm)になるので,上述の
=Average(A1:A3)
で平均が求まります.

あとは表示形式の問題だと思いますので,平均を求めたセルを選んで書式設定を変更すれば良いと思います.

最終的なアウトプットは下記の様なパターンと思いますが,如何でしょうか?
表示⇒関数⇒表示形式
2時間15分⇒=Average(A1:A3)⇒h"時間"mm分
2.25時間⇒=Average(A1:A3)×24⇒G/標準"時間"

この質問文から幾つかの要素に分けて回答したいと思います.
先ずセルの書式設定について,質問者さんが言っているのは"表示形式"の部分ですね.ここはセルに入力した値(数値)がどの様に表示するのかを設定する部分です.

例えばセルの値に0.25と入力されているとしましょう.このセルの表示形式を時刻(h:mm)の形式で表示させれば6:00になります.
これは1日という時間を1という数値にしているという事で,1時間であれば1/24≒0.041666・・・という数値になるという意味になります.

では質問文のG/標準"時間...続きを読む

Qエクセルでの時間平均の出し方

過去の質問を観覧したのですが
解決しなかったので質問させていただきます。

エクセルで時間の平均を出したいのですが
表示は【h:mm:ss】になっているのですが
実際には年月日時間で表示されています。

表示内容:0:03:57
実際の内容:2008/1/11 0:03:57

になっています。

この状態で日付が1/11から1/16まであるので
普通に関数【AVERAGE】で出すと数値は出るのですが
時間だけの平均がわかりません。


結果として時間だけの平均を求めたいのですが
何かいい方法はありませんでしょうか??
よろしくお願いしますm(_ _)m

Aベストアンサー

Excelの日付/時刻データの実体は、シリアル値と呼ばれる数値データで、
整数部分で日付、小数部分で時刻を表しています。

例えば、
[2008/1/19 18:38:00]の実体は、[39466.7763888889]という数値で、
[39466]が[2008/1/19]を、[0.7763888889]が[18:38:00]を表します。

というわけで、時刻部分のみについて平均を出すには、
データの小数部分(=1で割った余り)について平均を求めればOKです。

ex.[ A1:A99の日時データについて、時刻部分のみの平均をとる ]

 =AVERAGE(INDEX(MOD(A1:A99,1),))

あるいは、
 =AVERAGE(MOD(A1:A99,1))
をCtrl+Shit+Enterで配列数式として確定してもOK。

※表示形式は自動で変わらないので、シリアル値が直接表示されます。
 手動で表示形式を【h:mm:ss】に変更してください。

Qエクセルで時間の差の出し方は?

エクセルで、睡眠時間を一覧表でまとめています。たとえば、23:00就寝 朝6:30起床
エクセルの公式は、どのような式ですか?

Aベストアンサー

  B2に就寝時間
  C2に起床時間
のとき D2に睡眠時間の計算式
 =IF(B2<C2,C2-B2,(1-B2)+C2)
とする、これは日にちをまたぐ時間計算の処理です

B2に時間を記入するまでD2を無表示にするには,下記のようにします。
 =if(B2="","",IF(B2<C2,C2-B2,(1-B2)+C2))

Qエクセルで残業時間(平均)の求め方について

こんにちは。
先日、「エクセルの時間計算」についていろいろご教示いただいた
者です。その節はありがとうございました。

また新たな疑問が出てきたので何卒お教えください。

【A1】月間稼働時間
【A2】実働稼働時間
【A3】超過時間(残業)
・・・と去年4月から12月までの時間を入れ表を作成しました。
上司から4月から12月までの【平均超過時間(残業)】を出して
ほしいと言われ、=average(範囲指定)をすればいいのかな?と
思ったのですが、出来ません。
ちなみに、早退や遅刻が多かった人は稼働時間が足りず【A3】
(超過時間)に「-」が入っています。
うまく平均時間が出ないのは「-」があるからでしょうか?
時間の平均値を求めるには何か特別な操作や設定が必要なのでしょうか?
それとも根本的に考え方が間違っているのでしょうか?

つたない文章で申し訳ありませんが、何卒お教えください。
よろしくお願いいたします。

Aベストアンサー

補足要求です。うまくできなかったというのはAVERAGE関数がエラーになったという意味ですか?
どのような書式で、どのように個人の残業時間を求めていますか? 
特に早退・遅刻が多くて稼働時間がマイナスとなっている人はA3にはどのような値が入っているか補足をお願いします。

ちなみに以下のように時間が全てゼロより大きい場合は
A1 8:00 、 A2 8:30 、 A3 9:00
と入力して、任意のセルに =AVERAGE(A1:A3) と入力すると、8:30という平均値が得られます。A2に「-」を入力しても同様に8:30の結果を得ることができます。従って質問文にあるように「-」を入れたのが原因では無いと思えるのです。

Qエクセルで分や秒の合計や平均の出し方

よろしくお願いします。
職場で行うマラソン大会のための集計表をエクセルで作っています。
各人の記録をストップウォッチで計測し、それをエクセルのセルに入力(○分△秒)し、チームの合計タイムや平均タイムを表示させるにはどのようにしたらよろしいでしょうか?

Aベストアンサー

時間は、35分24秒だったら、0:35:24 と入力すれば良いです。
あとは、書式の表示形式で、好きな表示形式にしましょう。

時間は、数値扱いなので、普通に、合計はSUM関数、平均はAVERAGE関数で求めることが出来ます。

QExcel 分を時間で表示したい

Excel2002

A列 3050(分)→ B列 ○時間○分

↑このようにするにはどのような数式を立てたら良いでしょうか?

おわかりの方教えてくださーい!

Aベストアンサー

A1に時間(分)が入っていたら、別のセルに下記の式を入れます。
=A1/(60*24)

そのセルの表示形式を、ユーザー定義で
[hh]"時""間"mm"分"
とします。

Qエクセルで分から時間に変換したいのですが

いつもお世話になっています。よろしくお願いいたします。
エクセルで「分」の合計を「時分」に変換して表示したいのですがどうもうまくいきません。
どなたかご教授願えないでしょうか?
例:200(分)=3時間20分もしくは3:20
お願いいたします。

Aベストアンサー

A1に「分」が入っていて、別のセルに時分に変換して表示する場合とします。

24時間(1440分)未満であれば、
=A1/1440
とし、セルの書式設定で以下のいづれかを設定すればOK。
(1)[時刻]→[13:30]を選択
(2)[ユーザー定義]→h:m
(3)[ユーザー定義]→h時間m分
※分単位は常に2桁(2時間05分など)で表示したいなら
 (1)の設定か、またはmをmmに変更してください。


上記方法では23時間59分までしか正しく表示できないので、
もし24時間(1440分)以上になる場合は別の方法が必要です。
=INT(A1/60)&":"&MOD(A1,60)
または
=INT(A1/60)&"時間"&MOD(A1,60)&"分"
※分表示を常に2桁にするのであれば、
 MOD(A1,60)をRIGHT("0"&MOD(A3,60),2)に変更してください。

QEXCELで文字列を時刻表示(時:分:秒)にしたいのですが?

セル内の7桁時分秒表示(00時00分00秒0)の文字列から00:00:00(時:分:秒)と表示させる。
又は00:00:00:0(時:分:秒:0)と表示させる。
よろしくお願いします。


2121170 を 21:21:17 又は 21:21:17:0
1710500 を 17:10:50 又は 17:10:50:0
0001070 を 00:01:07 又は 00:01:07:0

Aベストアンサー

◆表示だけなら
★表示形式をユーザー定義で、 00!:00!:00!:0 とします

★シリアル値にするなら
別のセルに
=TIME(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))


このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング