Excelでの時間表示が4:74とか23:87になってしまいます

質問

Excelで所要時間の平均をAVERAGE関数を使って算出すると、4:74とか23:87とか、分のところが60を超えた数値で表示されてしまいます。セル書式の表示形式はユーザー定義の[0":"00]です。（試しに表示形式を[時刻]に変えてみると先程の4:74は23:28に変わってしまいます。）もしかして引数がｈ:ｍｍで表示されているからうまく計算できないのかな、とも思うのですが、だとするとｈ:ｍｍを小数点表示に変える方法がわかりません。4:74を5:14と表示させる方法を教えてください。宜しくお願いします。

回答No.5

noname#204879 · ベストアンサー

[No.4回答に対するお礼]に対する回答、

》 全て12:34方式に入力し直すのはなかなか厳しく、また、入力相違も怖いです

手入力せずに時刻形式にする方法は次のとおり。Ａ列（セル A1から下方）に「1234 方式で入力されてい」るデータがあると仮定すると
１．別の空き列の１行目に式 =TIMEVALUE(TEXT(A1,"0"":""00")) を入力して、
　　“ユーザー定義”で h:mm または [h]:mm と書式設定し、このセルを下方
　　にズズーッと複写
２．ステップ１の列全体を選択して、[コピー]→[値の貼り付け]を実行した後、
　　Ctrl+X を「エイヤッ！」と叩き付け
３．Ａ列全体を選択して、マウスの右クリックから[切り取ったセルの挿入]を実
　　行
４．Ｂ列を削除

それでは現状（1234 方式で入力）のままで「時間の平均をAVERAGE関数を使って算出」する式を紹介します。ただし、4:56 等のつもりで 465 等 に入力ミスされたデータはないものとします。
範囲 A1:A10 に「元データ」があると仮定すると、配列数式 {=AVERAGE(TIMEVALUE(TEXT(A1:A10,"0"":""00")))} は平均時間を返して来ます。書式は [h]:mm としておく方がよろしいかと。
ネ？式は簡単でも難解でしょ？（そうでもないか？）
式の難解云々より、実は先ほど述べた「入力ミス」が「怖い」のです。なぜなら 4:65 とあり得な～い時刻表示されているのに気付きにくい！時刻形式で 4:65 と入力した場合は、5:05 となるので「あれ？」となるでしょ。

補足

mike_gさんから教えていただいた方法でエクセルと格闘しています。なかなかうまく行かず試行錯誤しています。

回答No.6

noname#204879

[No.5回答に対する補足]に対するコメント、

》 なかなかうまく行かず試行錯誤しています

「手入力せずに時刻形式にする方法」と「配列数式」のどちらで「試行錯誤」されていますか？
前者なら、ステップ番号の何処で躓いているかをお知らせください。
「配列数式」は初心者にはお奨めしません。理解できないと思うからです。

お礼

ありがとうございました。試行錯誤の末「手入力せずに時刻形式にする方法」で無事時刻が60進法表示になりました。ご指導ありがとうございました。
それから、初めて「値の貼り付け」という操作を知ることができました。感動感激しています。今までは、こんな方法があるといいなと思いつつ関数が返してきた数値を手で再入力したりしていたのです。仕事の能率が大幅に上がりそうです。ありがとうございました。

回答No.4

noname#204879

問題の原因はデータを時刻形式に入力されていないからです。例えば、1:23 や 12:34 を 123、1234 と入力しているでしょ？
計算を簡単にするには、面倒ですが 1:23、12:34 と入力することです。
123、1234 方式で入力すると、式が難解になりますが、どちらをご希望ですか？

お礼

ご回答ありがとうございます。No.3の方も元データを見ろとおっしゃっていました。元データを見たところ、確かにmike_ｇさんがおっしゃるとおり1234 方式で入力されていました。ただ、元データはボリュームがあって全て12:34方式に入力し直すのはなかなか厳しく、また、入力相違も怖いです。“難解”とのことですが、1234 方式を12:34方式に変換する算式等があるなら参考までに教えてください。

回答No.3

keirika

AVERAGE関数で出した計算結果が473.977777777777778になっているのではないでしょうか。

この数値であれば、表示形式が[0":"00]の場合は4：74
つまり四捨五入で474日と言うことになります。

またh:mmの場合は23:28は23時間28分になります。

一度、平均を出す元になっている数値を見直されてはいかがでしょうか。

お礼

そうですか。セルの書式だけでは解決しないかも知れないのですね…。ご回答ありがとうございました。

回答No.2

39otukare

セルの書式設定で　時刻→13：30→ＯＫで表示しませんか？

お礼

39otukareさんの方法でやってみても4:74が23:28になってしまいます。なんともうまくいきません。ご回答ありがとうございました。

回答No.1

neminemi

表示形式をユーザー定義のh:mmと設定したらどうでしょう？

お礼

h:mmでやってみると4:74が23:28になってしまいます。うまくいきません。残念です。ご回答ありがとうございました。