profaim  >  関連技術  >  言語・環境  >  目的別  >  Excel関数

掲載日 : 2010/05/25

マイナス値を0と扱いたいケース

例えば、1日につき8時間労働超えの時間外労働時間を計算するために「((退社時間−出社時間)−休憩時間)−8時間」という計算式を作ったとします。この計算式、10時に出社して21時に退社、休憩時間が1時間だったとすると「((21:00-10:00)-1h)-8h=2h」と求まります。ところが10時に出社して15時に退社、休憩なしだった場合は「((15:00-10:00)-0h)-8h= -3h」となってしまいます。この様に、労働時間が8時間に満たなかった場合に残業時間をマイナスではなく0時間と扱いたいというのが今回のケースです。

出社時間が「A1セル」、退社時間が「B1セル」、休憩時間が「C1セル」に入力されている場合、上記ケースでは 「((B1-A1)-C1)-8」 となります。Microsoft Excel でセルを選択して「=B1-A1-C1-8」と入力すればその計算結果が表示されます。

マイナス値を0とするためのアプローチ

1.【条件分岐】IF を使ったアプローチ

「残業時間がマイナスだった場合は0を表示」と考えて最初に思いつくのは IF関数 でしょうか。IF関数 を使うと次の様に書けます。

=IF ( B1-A1-C1-8 < 0, 0, B1-A1-C1-8 )

Microsoft Excel の IF関数 は 「IF(条件式, 条件成立時の値, 条件不成立時の値)」 という構文になっているため、例の様に時間外労働時間の計算式を2ヶ所に書かなくてはならないため見づらく、式を変更する場合も2ヶ所直さなくてはいけないため、計算式が長い場合はお勧めできません。

2.【最大値】 MAX を使ったアプローチ

計算式と固定値の"0"とから大きい方を選択することで、マイナス値ではなく0を採用するという方法もあります。

= MAX(B1-A1-C1-8, 0)

Microsoft Excel の Max関数 は 「MAX(候補値1, 候補値2, …)」という構文になっており、候補値の中から一番大きな値を選択します。時間外労働時間の計算式と数値の "0" の2つを候補値とすることで、 計算結果がマイナスだった場合は必ず "0" が最大値となる仕組みです。計算式が1ヶ所で済むこと、MIN や 違う数値を使うことで簡単に応用も効くためお勧めです。

3.セルの書式で整形するアプローチ

表示だけなら関数ではなく書式で調整することもできます。この場合、セルの書式設定で 「0;"0"」 を設定します。

Microsoft Excel の数値書式設定は 「正の数の書式;負の数の書式;ゼロの書式;文字列の書式」と、セミコロン区切りで複数指定することができます。この例では、計算結果が正の数の場合は単純な数値形式で計算結果を表示、負の数の場合は 計算結果のかわりに文字列の "0" を表示することで実現しています。

※書式で整形する例は少々特殊で、見せかけ上 "0" と表示しているだけで、実際の値は計算で得られたマイナスの値になります。セルの値を使って別の計算をしようとしている場合は、マイナス値が計算に使われるということを忘れないようにしましょう。






  Excel関数  
目的別
マイナスを0として扱う

PR

カスタム検索