文字列から値に直して合計をSUＭで計算しようとしたがうまくできなかった

D列では、A列とC列を計算した式が入ってます。ですが、文字列？になってしまっているため、一番したの合計（SUM）が反応しなく０：００となってしまいます。

なので、F列の黄色の部分の方で、値に直してSUMを使おうかと考えていました。ですが、値に直してSUMを使っても0：00のままでうまくできませんでした。

どのようにすれば、D列の数字を合計で出せますか。
また、時間外の不足分は△表記で表していて、それぞれの合計を出せるようにしたいです。

文字列から値に直したらいけるのかなと思いやってみたのですが、できなかったので他にいい方法があれば教えていただきたいです。


D列に入ってる式
=IF(AND(G6>0,K6>G6),TEXT(ABS(K6-G6-"9:00"),IF(K6-G6<TIMEVALUE("9:00"),"△h:mm","h:mm")),"")

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

• うまく説明できなかったので、ご不明点等ございましたらお聞きしていただければ回答できますのでよろしくお願いいたします。

    補足日時：2021/08/20 13:58

• D列をコピーして、貼り付けのオプションで値を選んでF列に貼り付けました。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/08/20 14:37

• お教えいただいた式を入れたらできましたが、ー（マイナス）ではなく、△（さんかく）表記に直すことは可能ですか？
  普通の数字と△の数字を、それぞれ出せるのであれば出したいです。
  
  △表記で出せる方法がありましたら教えていただけると助かります。
  赤文字の数字が合計のところで出したい数字です。

  
  No.2の回答に寄せられた補足コメントです。 補足日時：2021/08/20 15:28

• ご回答いただきありがとうございます。
  
  各日のほうは、お教えいただいた式をコピーしてみたらうまくいきました。
  ですが、合計の方がどこか間違っているのか、F32では０となったり、F33では♯VALUE!となってしまったりで、うまくできません。。
  うまくできない理由としてどのような点が考えられるでしょうか。何か打ち間違えてるのか、シンプルに打ち間違えなのか、ご教授願います。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2021/08/22 12:02

• No.5様
  
  ♯VALUE!と出てしまいます、、どこがで間違ってしまっているということなんですかね、？

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2021/08/22 13:40

• No.7様
  返事が遅くなってしまい申し訳ございません。
  
  ①A列は、出勤時間です。00以外にあり得ます。0:00は大丈夫です。
  ②B列は、退勤時間です。　　　　　　　　〃
  
  ③9：00というのは、勤務時間８時間と休憩時間１時間という意味です。
  
  ④F33は、早く仕事が終わったものは△で表していて、その△は含まずに時間外の超過した部分だけの合計を出したいです。
  （F34に、時間外を含まない△の分だけの合計を出せれば理想です。）

  No.7の回答に寄せられた補足コメントです。 補足日時：2021/08/25 11:20

No.5 ベストアンサー 回答者： トモクンアヤチャン 回答日時： 2021/08/22 12:24

F33は合計欄です。

回答№3の
F32=SUM(IF(LEFT(F1:F32,1)="△",VALUE(RIGHT(F1:F32,LEN(F1:F32)-1))*-1,IF(ISERROR(VALUE(F1:F32)),0,VALUE(F1:F32))))
は
F33=SUM(IF(LEFT(F1:F32,1)="△",VALUE(RIGHT(F1:F32,LEN(F1:F32)-1))*-1,IF(ISERROR(VALUE(F1:F32)),0,VALUE(F1:F32))))
の間違いです。
F32のセルはF31の明細行（日）と同じです。もう一度上からコピーをお願いします。
すみませんでした。

この回答へのお礼

ありがとうございました！

お礼日時：2021/08/25 14:15

No.8 回答者： トモクンアヤチャン 回答日時： 2021/08/25 13:03

①②③は承知しました。

0:00から勤務を開始することも退勤することもありえないということですね。（通常はありえます。）
夜勤のような、22：00-05：00のような勤務はありえないのでしょうか。
ある場合は、AND(A1>0,C1>A1)のC列がA列より大きいの条件に一致しません。
また、24:00を超える残業9:00-1:00もダメになります。
これを解消するには
F1=IF(AND(A1="",C1=""),"",IF(C1<A1,C1+"24:00",C1)-A1-"9:00")
とします。A列Ｃ列ともに空欄のみ空欄とし、一方のみ空欄は0：00と見なして計算します。0:00をまたいだ場合は+24:00で調整します。
④ですが、これを実現するために少し修正が必要です。
F33=SUMIF(F1:F32,">0") 　ﾌﾟﾗｽのみ合計
F34=SUMIF(F1:F32,"<0") 　ﾏｲﾅｽのみ合計
とし、Ｆ列の書式編集ですが、ホーム→セルの書式設定→ユーザー定義→
「[hh]:mm;△[hh]:mm」と、ｈｈの前後に[]を入れます。このことにより、24時間を超えても25時26時と増えていきます。
いかがでしょうか。

この回答へのお礼

理想のものができました！
本当に感謝してもしきれないくらい助かりました。
意味あるかはわかりませんが、全部の会話にお礼させていただきたいと思います。
本当にありがとうございました！

お礼日時：2021/08/25 14:15

No.7 回答者： トモクンアヤチャン 回答日時： 2021/08/22 17:26

場当たり的な回答で済みません。

明細行をF1=IF(AND(A1>0,C1>A1),C1-A1-"9:00","")のようにしないと、
合計のF33=SUM(F1:F32)が計算できません。
ついでに、
①A列の意味とデータの範囲、分が00以外にあり得るかなど、
Ａ列は0:00をゆるさないのか。
②C列の意味とデータの範囲、分が00以外にあり得るかなど、
③C1-A1-"9:00"の意味
④F33の合計の意味
を教えていただけないでしょうか。
出勤時刻・退勤時刻のようなものだろうと勝手に解釈していましたが、9:00は？です。勘違いでミスがまだありそうです。

この回答へのお礼

ありがとうございました！

お礼日時：2021/08/25 14:15

No.6 回答者： トモクンアヤチャン 回答日時： 2021/08/22 16:59

№5です。

№3でも書きましたが、時刻はマイナスを許さない仕様のようですので、
計算結果がマイナスになっていませんか。
それを直すのはちょっと大変です。マイナスは明細行と同様に“△”がいいですよね。
他の回答者のご意見にもありますが、次の通り設定を直します。
ファイル→その他→オプション→詳細設定→「次のブックを計算するとき」の「1904年から計算する」にレを入れる。
これにより、時刻のマイナス表示ができます。
ただし、ブック間のデータの参照などに問題が出ることがありそうで、やや危険です。
次に編集の定義です。
ホーム→数値→「セルの書式設定」のユーザー定義で、種類欄に「h:mm;△h:mm」といれて「OK」とします。これで、時刻のマイナス値が△表示されます。
これにより、明細行の長い数式はあまり意味がなくなってしまいますが、今はそのままにします。直しても結構です。直す場合は
F1=IF(AND(A1>0,C1>A1),C1-A1-"9:00","")のように。
合計は
F33=SUM(F1:F32)です。
少し気になったのですが、合計時刻は、23:00+1:00=0:00に戻りますが、それでいいのでしょうか。－23:59~23:59に収まるということで。

この回答へのお礼

ありがとうございました！

お礼日時：2021/08/25 14:15

No.4 回答者： enunokokoro 回答日時： 2021/08/21 15:06

作業列を用意すれば、難しい処理は不要です。

時間の表示形式のままだと、計算処理などでマイナスになる場合に
########と表示がなるだけで、数値としては問題ないはず。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/ …

　=K6-G6-"9:00"
の計算だけの作業列を用意して、表示形式を標準などにしてみて。
小数点の数値にマイナスが付くだけで、数値として認識されている
はずです。
この作業列を合計すれば済むことなので単純に計算できます。
合計がマイナスになる場合があるのなら、Text関数を使って処理を
するIF関数を使った計算式にするだけですよね。

No.3 回答者： トモクンアヤチャン 回答日時： 2021/08/21 00:15

sum関数は文字列は計算しません。

text関数は文字列編集ですので表示結果は文字列です。そこで、value関数で文字列を数値に再変換して集計します。
また、時刻はﾏｲﾅｽを許さない仕様のようですので、書式設定ではうまくいきません。よって｛"△h:mm"｝はこのままにします。
例
各日F2=IF(AND(A2>0,C2>A2),TEXT(ABS(C2-A2-TIMEVALUE("9:00")),IF(C2-A2<TIMEVALUE("9:00"),"△h:mm","h:mm")),"")
以下、Ｆ３２までコピー。

合計F32=SUM(IF(LEFT(F1:F32,1)="△",VALUE(RIGHT(F1:F32,LEN(F1:F32)-1))*-1,IF(ISERROR(VALUE(F1:F32)),0,VALUE(F1:F32))))
これは、△がついていたらそれを取って*-1するという手法をとってみました。
いかがでしょうか。
また、sumは文字（""など）を0とみなすはずですが当方のマシンでは関数の計算結果が文字の場合エラーになるようですので、""も0に置換しています。

この回答へのお礼

ありがとうございました！

お礼日時：2021/08/25 14:15

No.2 回答者： noname820 回答日時： 2021/08/20 15:00

オプションの設定を変更すると計算できるようです。

https://www.jpita.or.jp/simin/index.php?excelzit …

D列の式は下記でSUM出来ました。
=IF(AND(A2>0,C2>A2),C2-A2-"9:00",C2-A2)

No.1 回答者： chonami 回答日時： 2021/08/20 14:33

>値に直してSUMを使っても

どのような方法で値にしたのですか？