時間単位の合計について こんにちは、よろしくお願いいたします。 時間単位で、合計をしたいのですが Ａ

時間単位の合計について

こんにちは、よろしくお願いいたします。
時間単位で、合計をしたいのですが
Ａ列に下記のように入っているとします。

　A列(時間）
2　07：00
3　07：00
4　07：00
5　07：00
6　04：00
　 32：00　　　　←合計欄

合計欄に下記のように計算式を入れました。

=IFERROR(TIMEVALUE(A2),0)+IFERROR(TIMEVALUE(A3),0)+IFERROR(TIMEVALUE(A4),0)+IFERROR(TIMEVALUE(A5),0)+IFERROR(TIMEVALUE(A6),0)

※IFERRORでくくっているのは実際には空欄があるためです。

今現在は計算があっているのですが、例えばA2の07：00を07：30とか8：00など別の時間に修正すると、勝手に表示が7：30：00とされ
計算が狂います。

セル書式はすべて同じ時刻の"00:00"を選んでいるのにもかかわらずです。


原因を教えてください。
よろしくお願いいたします。

質問者からの補足コメント

• あ、すみません、
  お礼に追加します。
  7:00と入ってるのをコピーして貼り付けて、00を30に直しても、同じ事が起こります。
  元データはちゃんと07:00と入っています。

    補足日時：2020/01/27 16:34

No.11 ベストアンサー 回答者： chonami 回答日時： 2020/01/28 15:28

>合計が130が正解なのに、10となります

合計のセルの表示形式を[h]:mmにするとどうなりますか？

この回答へのお礼

出来ました！
ありがとうございました。
空白セルの書式が標準のままでも出来ました！
質問の仕方なども色々ご教示頂きありがとうございました。

お礼日時：2020/01/28 15:56

No.12 回答者： GooUserラック 回答日時： 2020/01/28 15:42

一括変換のマクロです。

※ 該当箇所の書式を事前に「hh:mm」や「[hh]:mm」に変更しておくと確実です。

Sub Sample()

Dim MaxRow As Long
Dim MaxCol As Long
Dim UsedR As Long
Dim UsedC As Long
　With ActiveSheet.UsedRange
　　MaxRow = .Rows(.Rows.Count).Row
　　MaxCol = .Columns(.Columns.Count).Column
　End With
　For UsedR = 1 To MaxRow
　　For UsedC = 1 To MaxCol
　　　If InStr(Cells(UsedR, UsedC).Value, ":") > 0 Then
　　　　If IsDate(Cells(UsedR, UsedC).Value) Then
　　　　　Cells(UsedR, UsedC).Value = Cells(UsedR, UsedC).Value
　　　　End If
　　　End If
　　Next
　Next

End Sub

この回答へのお礼

出来ましたが、今後の勉強のために作ってみたいと思います。
しばらくお待ち下さい。
色々教えていただきありがとうございます。

お礼日時：2020/01/28 15:57

No.10 回答者： chonami 回答日時： 2020/01/28 12:26

まずですね。

この質問内容だと貼り付けた直後のデータは「文字列」です。TIMEVALUEで変換されるということでそれが証明されています。
（本当は№4の回答に返答が頂けてればもっと早く確証があったのですが。質問するのは回答に必要だからなので聞いたことには全部答えていただけるとその後の展開がスムーズになります。）

文字列のデータ型を貼りつけた後に表示形式で「時刻（h:mmとか）」に変換してもそれは時刻データにはなってません。ここはよく勘違いされる方が多いのでご注意ください。
貼り付けた後に、その列を選択して

データタブ→区切り位置→完了

とすればそれは時刻データになります。（面倒なコピペとかマクロはいりません。）
時刻データになれば普通にSUMで合計できるかと思います。

また、貼り付けて文字列のままの状態でも

=A2+A3+A4…

とSUMではなく足し算で式を書けば計算できますよ。
どちらを選ぶかはご本人の都合のいいようにしてください。

この回答へのお礼

ご教示ありがとうございます。
やってみたら、ちゃんと時刻データになりました。
ですが、合計が130が正解なのに、10となります。空白が標準のままだったのでそこに値の入ったデータを書式コピーしてもダメでした。ちなみに、空白セルまでをsumすると正しい答えが出ました。

お礼日時：2020/01/28 15:13

No.9 回答者： GooUserラック 回答日時： 2020/01/28 12:14

No.8 がもしダメならば、マクロ（ＶＢＡ）を使って一括変換する事が出来ます。

マクロを使えるならば対象の列をご提示ください。

No.8 回答者： GooUserラック 回答日時： 2020/01/28 11:57

空いている列にとりあえずコピーしておき、その列の書式設定を標準にしたらどうなりますか？

①「0.??????」のように小数点で表示、または「07:00」のように時間で表示された場合
　元の列の書式設定を「hh:mm」にして、コピーした列を元の列に「値貼り付け」してください。
② その他（どのように表示されたかご提示ください）

この回答へのお礼

やってみました。
まず、空いている列にコピーし標準にしたら、07コロン00と表示され、元の列に値貼り付けしたら
やはり07コロン00と表示されます。合計は0コロン00です。
値貼り付けした元の列は
07コロン00と表示され、合計は0です

お礼日時：2020/01/28 15:07

No.7 回答者： GooUserラック 回答日時： 2020/01/27 21:27

A2セルを選択した状態で数式バーにはなんと表示されていますか？

「07：00」または「'07：00」のように表示されているならば文字列になっているのでシリアル値にして下さい。
合計欄以外のＡ列の式設定は「hh:mm」にして、入力は「7:00」のようにしてみて下さい。

この回答へのお礼

ありがとうございます。
やってみました。
ソフトから出力された時点で、
書式をhh:mmに変更して合計したら0になっていました。
数式バーには07：00と出ていました。
それをいったん消して07：00と手打ちしたところ
数式バーには7:00:00と出てちゃんと計算されました。

これはどういう現象なのでしょう？

例では5個しかデータないんですけど、実際はもっと多いので
全部手で打ち直すのは難しいです。

お礼日時：2020/01/28 11:38

No.6 回答者： GooUserラック 回答日時： 2020/01/27 19:02

普通に合計欄の計算式を「=SUM(A2:A6)」にして書式を「[hh]:mm」にしてはいけないのでしょうか？

この回答へのお礼

ご回答ありがとうございます。
一番最初にやりましたが、合計が0になってしまい、書式を変えても駄目でした。

お礼日時：2020/01/27 19:16

No.5 回答者： chonami 回答日時： 2020/01/27 16:43

この後ちょっと長文が書きにくくなるので、現状での推論を書いておきます。

貼り付けたデータは文字列になっているためTIMEVALUE関数で時刻に変換することで合計ができています。
ですが、分などを入力しなおすことでそのセルは実際の時刻のデータ型（シリアル値）になります。
本来はTIMEVALUE関数の引数に実際の時刻データを入れるとエラーになるのですが、下手にIFERRORをつけているのでそのセルは0ということになり合計がずれているのだと思います。
貼り付ける際は貼り付けてから時刻データに変換してしまえばSUMで合計できますしSUMを使えば空欄セルがあっても普通に合計できます。

この回答へのお礼

なるほど
ありがとうございます。
やってみます。

お礼日時：2020/01/27 18:37

No.4 回答者： chonami 回答日時： 2020/01/27 16:38

>7:00と入ってるのを

「元は」何に入っていたのですか？最初に貼りつけた状態で表示形式を「標準」にするとセルの表示はどうなりますか？

No.3 回答者： chonami 回答日時： 2020/01/27 16:33

>普通に7:(コロン)30と入れました

では、7:30:00と表示されても計算上は7:30ですよね？
計算が狂うとはどのようになるのですか？そもそも「元データ」は文字列なんですよね？
そのデータがどこからきたか聞いているのですが。

この回答へのお礼

元データは、他のソフトから出力されたもので文字列ではなくユーザー定義のｈmmでした。
すみません。
（コロンが入ります。上手く変換出来ないのですみません）

お礼日時：2020/01/27 16:40