Excelでの時間集計について（7:30→730と表現されてた場合）

Excelでの時間集計です。

日々の集計時間合計が、
例えば、7時間50分、30分、10時間15分、8時間30分を
750、30、1015、830のような整数で表されています。
これを1か月分集計したいのです。

750+750＝1500になってしまいますが、
実際は、7時間50分+7時間50分＝15時間40分となるため、
変換してから計算しないといけません。

そして最後の結果は
「15:40」のように表示したいです。
どのような計算をしたら良いでしょうか？

No.7 回答者： sorun2 回答日時： 2020/07/15 11:07

既に回答出ていますが、まだ締め切られていないので

直接の回答ではないですが外野から、失礼します。

>750、30、1015、830のような整数で表されています。
時間の集計をわざわざ整数に変換されていますが、その必要性を理解できません。

参考に添付図をご覧ください
書式設定をユーザー定義すれば、整数に変換しなくても、
D列のように見かけは、整数に表示されても、正しく合計されます。

この回答へのお礼

>>750、30、1015、830のような整数で表されています。
>時間の集計をわざわざ整数に変換されていますが、その必要性を理解できません。

わざわざ変換等するわけがありません。
そのように出力されてしまっているテキストデータの加工についての質問です。

お礼日時：2020/07/15 13:37

No.6 回答者： mike_g 回答日時： 2020/07/14 15:29

斯樣なる手順は如何で御座るや？

添附圖參照
式 =TIME(A1/100,MOD(A1,100),0) を、[h]:mm に書式設定したセル C1 に入力→當該セルを下方に３行オートフィル⇒範圍 C1:C5 を選擇⇒Σアイコン(注1)を「エイヤッ!」とダブルクリック(注2)⇒式 =TEXT(C5,"[h]mm")*1 をセル A5 に入力して Enterキーをパシーッ

注記:
１．此のアイコン、アチコチに點在、さァ探しませう
￣￣或ひは Alt+Shift+ほ を「エイヤッ!」とダブルクリックしても構は無い
２．式 =SUM(C1:C4) がセル C5 に入力さる

No.5 回答者： fujillin 回答日時： 2020/07/14 12:41

No1です。

回答で述べておいた別案（ユーザ定義関数）を試しに作成してみました。
引数で渡された範囲の値を、ご質問のような数値と解釈して時間換算して合計するというものです。

使い方としては、
　=sumTime(A1:A5, B3, 130)
といったように、合計したい範囲に対してセル範囲（またはセル、あるいは直接の数値）を列挙して引数として渡します。
結果はシリアル値で返されます。


事前に、ユーザ定義関数として、以下を標準モジュールにコピペしておきます。
（引数の整合性のチェックは、少々いい加減ですが）

Function sumTime(ParamArray Rs() As Variant) As Date
　Dim c As Range, i As Integer, t As Date
　t = 0
　For i = 0 To UBound(Rs())
　　If TypeName(Rs(i)) = "Range" Then
　　　For Each c In Rs(i)
　　　　t = t + num2time(c.Value)
　　　Next c
　　Else
　　　t = t + num2time(Rs(i))
　　End If
　Next i
　sumTime = t
End Function

Function num2time(v) As Date
　num2time = 0
　If IsNumeric(v) Then
　　num2time = TimeSerial(Int(v / 100), v Mod 100, 0)
　End If
End Function

No.4 ベストアンサー 回答者： chonami 回答日時： 2020/07/14 12:23

別セルで時刻に変換するならデータがA1にあるとして

=TEXT(A1,"0!:00")*1

と入れて必要数コピーし集計。

そのままのセルで時刻に変換したいなら、まずセルの書式設定の表示形式をユーザー定義で
「0!:00」に変更。(見た目は「7:50」になるが中身は750）
範囲を選択してコピーし、クリップボードを起動しそのまま「すべて貼り付け」にした後、表示形式を時刻に変更

でどうでしょうか？
実際のセルの配置がわからないので、列単位などのかたまりでデータがある前提ですが。

この回答へのお礼

そんな簡単な式で変換できるんですね！ありがとうございます

お礼日時：2020/07/15 13:38

No.3 回答者： tom04 回答日時： 2020/07/14 11:12

No.2です。

投稿後気づきました。
SUMPRODUCT関数の範囲が間違っていましたね。
とりあえずA2～A100セルの範囲としていましたので

=SUMPRODUCT(INT(A2:A100/100))/24+SUMPRODUCT(MOD(A2:A100,100))/24/60

でした。

どうも失礼しました。m(_ _)m

No.2 回答者： tom04 回答日時： 2020/07/14 11:10

こんにちは！

本来であればシリアル値で入力した方がよいのですが、すでに整数で入力済みだとしての一例です。

具体的は配置が判らないのですが、
仮にA2セル以降にデータがあるとします。

表示したいセル（セルの表示形式はユーザー定義から　[h]:mm　としておく）に
=SUMPRODUCT(INT(A2:A1100/100))/24+SUMPRODUCT(MOD(A2:A100,100))/24/60

としてみてはどうでしょうか？

※　文字列セルが範囲内にあるとエラーになりますので、
必ず数値入力セルだけを範囲指定してください。（空白セルはいくつあっても問題ありません）m(_ _)m

No.1 回答者： fujillin 回答日時： 2020/07/14 10:53

こんにちは

＞どのような計算をしたら良いでしょうか？
そのまま計算するのには無理があるので、ご指摘の通りに

＞変換してから計算しなければいません
をそのまま実行するしかないでしょう。

例えばA1セルに元となる値があるなら、これを時間のシリアル値に変換する一例として、
　=TIME(INT(A1/100),MOD(A1,100),0)
のような計算を行うことで可能になります。

変換対象となるセルの数が多いようなので、一旦、作業用のセル（範囲）にそれぞれをまとめて変換（多分、関数式のフィルコピーで対応可能）しておいてから、SUM関数等でそちらの値を加算するのが簡単ではないかと思います。

作業用のセル（範囲）を用いたくない場合に、上記の変換をそれぞれのセルに対して記していると、とても長い式になってしまいそうに思われます。
別案として、『指定範囲をまとめて変換して合計する』ような機能のユーザ関数を作成しておいて、それを利用するといった方法も考えられますね。


＞そして最後の結果は
＞「15:40」のように表示したいです。
シリアル値で合算（集計）したものを値として、セルの表示書式を設定しておけば、ご要望のようになるはずです。