EXCLEでの時間計算・日数の返還

質問

EXCLEで統計用のシートを作成中です。

教えていただきたいことがあります。

・日数と時間の集計とアベレージの出し方です。
　1件あたり、数十日～数時間の業務があり、その業務時間の集計を行いたいのですが・・。

　Ex　　１）12日3時間10分
　　　　　２）2時間10分
　　　　　３）1日12時間20分
　　　　　４）4時間13分
　　　　　　　こんな感じのデータがあり、その項目の数十件程度の
　　　　　　　合計と平均値を出したいと考えています。

　　表現方法は、「○○時間△△分」という形式にしたいのですが
　　数式の作成方法を　教えていただけないでしょうか。

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

回答No.2

bunjii · ベストアンサー

>表現方法は、「○○時間△△分」という形式にしたいのですが数式の作成方法を　教えていただけないでしょうか。
元データの時間数が文字列と言うことなので日数（整数部）と時間数（小数部）に分けて数値化します。

A列に元データの文字列があるとしてB列へ日数+時間数に変換した値を算出します。

B2=IFERROR(LEFT(A2,FIND("日",A2)-1),0)*1+SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-IFERROR(FIND("日",A2),0)),"時間",":"),"分","")*1

此処で、 IFERROR(LEFT(A2,FIND("日",A2)-1),0)*1 は日数の整数部です。
次の、 SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-IFERROR(FIND("日",A2),0)),"時間",":"),"分","")*1 は"h時間m分"と言う文字列から"h:m"と言う文字列に変換して1倍すると時間のシリアル値に変わります。
このシリアル値は1日が整数の1で1時間は1/24です。
これを元に合計（SUM関数）と平均（AVERAGE関数）で値を算出します。

合計と平均値の値を文字列に変換するには次の式で良いと思います。

B10に合計のシリアル値があるとして
=INT(B10)*24+HOUR(B10)&"時間"&MINUTE(B10)&"分"

回答No.1

Cupper-2

普通にAVERAGE関数でOK。

ただし日付日時の形式にきちんとしておかないと計算はできません。
これは日付日時をExcelではシリアル値と言う数値で管理していることを利用するからです。
「2時間10分」
これは単なる文字列で、計算の対象にはなりません。

質問文にある値は4つとも文字列になっていますので、そのままでは計算できません。
そんなですので、
　yyyy/mm/dd hh:mm
の書式で記述しなおしましょう。
　例：1900/1/12 3:10
見栄えが悪いというのでしたら、セルの書式を
　d"日"hh"時間"mm"分"
とすればきれいに収まると思います。

あとは平均の値を表示するセルの書式を　[hh]"時間"mm"分"　にするだけです。

文字列を切り取って時間に直して計算することもできますが、そのアルゴリズムはご自身で考えてみてください。
1週間は7、1日は1、12時間は0.5、1時間は1/24
となるのがシリアル値です。
（計算することを念頭に置かず入力作業を疎かにしたツケが回ってきたと思ってください）

数十件程度なら入力し直しても10分もかからないでしょうから、そちらのほうが早いかもです。