excelの計算が合わなくなった。
関数を使用して時間を算出しているシートで、○○時間○○分という形式で表示させている。
これで時給計算もしているが、前月に計算誤りがあり、減算することとなった。そのため、時間をマイナス入力したという。本当は以下のような感じで表示させたいのだが。
■INT関数とTRUNC関数
ところが時間の「分」の値をマイナスで入力したところおかしな状態に･･･
30分減算したかったので「-30」と入力したところ、「-0時間30分」と表示される予定だったのですが、なぜか「-1時間30分」と表示される。
時間のところの計算が間違っている。
この箇所の関数を確認したところ、以下の通り。
=INT(合計時間のセル/60)&"時間"&MOD(合計時間のセル,60)&"分"
最初は「分」で表示されている時間集計を、「○時間○○分」に変換している関数のようだ。
まず分数を60で割り、何時間なのか算出する。
これには 小数点以下を切り捨てるINT関数 を使用している。
30分の場合は、
３０ ÷ ６０ ＝ ０．５
ここで小数点以下をINT関数で切り捨てるのだが、マイナスの場合状況が変わる。
−３０ ÷ ６０ ＝ −０．５
マイナスの場合は、INT関数では「-１」になってしまう。
どうもマイナス方向へ切捨てをしている様子。
「その数字より小さな整数」という判定をしている。
確かに小数点分の量を捨てていくという発想でいくとそうなるが、今回の計算ではこれは困る。
これを TRUNC関数 に変更することで、マイナス方向であっても少数部を切捨てをしてくれるようになりました。
=TRUNC(合計時間のセル/60)&"時間"&MOD(合計時間のセル,60)&"分"
■MOD関数も
しかしまだ問題が残りました。実はMOD関数で出てきた「余り」の部分がおかしな結果になっています。
「−７０」分と入力した場合の結果が「−1時間50分」と出力されてしまうのです。
これはMOD関数もマイナスに対応していないことが原因です。
いろいろな方法があるでしょうが、今回は元の値のマイナス要素を排除した絶対値から計算した結果から、「余り」（分）の部分を導き出すことにしました。
絶対値を算出する関数は以下の通り。これでマイナスを消します。
=ABS(対象のセル）
「時間の合計時間の箇所に上記関数を埋め込んでみました。
=TRUNC(合計時間のセル/60)&"時間"&MOD(ABS(合計時間のセル）,60)&"分"
この結果「−７０」の結果が「-1時間10分」と表示されるようになりました。
