はじめに
次のような請求書があります。
サンプルファイルはコチラ
C列の時間単価に15分単位で記録されているD列の作業時間がかけられ、E列に計が求められています。
16行目には、それぞれの縦の合計がSUM関数で計算されています。
全ての金額はカンマ区切りで表示されています。
掛け算や割り算をする計算書で注意しなければならないこと
実はこの請求書には矛盾があります。
それぞれの計が4,804円、16,663円、9,608円です。
電卓で計算するとその合計は31,075円になります。
しかし、セルD16の合計の計算結果が31,074円となっており1円の差異が出ています。
これはなぜなのでしょうか。
D列の消費税額の通知を全て小数点以下2桁の表示にしてみるとわかります。
それぞれの消費税額が4,803.75円、16,662.75円、9,607.50円と表示されています。これが本当のそのセルの値です。
このように表示されている値よりも本当のセルの値の方が細かくなっていることがあります。
Excelの合計は、本当のセルの値を使って計算しますので、 答えは31,074.00円、それぞれの数値が四捨五入で表示されるので、先ほどのような表示になります。
それでは請求書として金額の誤差がある書類になってしまうので、そのままにしておくことはできません。
Excelで請求書を作成する場合、特に掛け算や割り算をした時には小数点以下の結果になる場合があります。
その上でカンマ区切りなどの小数点以下を表示しない設定にすると、小数点以下は四捨五入で表示されます。
しかし、その値を使った計算は、表示された値ではなくて、そのセルに本当に入っている値を使って計算しますので今回のような差異が生まれてしまいます。
きちんと四捨五入などの端数処理を行う計算にしなければなりません。
端数処理を行うExcel関数
端数処理を行う方法は大きく分けて三つです。
- 四捨五入
- 切り上げ
- 切り捨て
それぞれ目的によって使う関数が違います。
「ROUND関数」四捨五入
ROUND関数の書式は次の通りです。
=ROUND(四捨五入する数値またはセル,桁数)
セルE10に入る、セルC10の値とセルD10の値をかけ合わせた、4803.75という結果を四捨五入し、小数点以下のない、4084という結果にする場合は次の計算式です。
=ROUND(C10*D10,0)
この書式は後から紹介するROUNDDOWN関数、ROUNRUP関数も同様です。
桁数の考え方は、最終的な表示が小数点以下の表示を全くなくすのであれば0です。
小数点第1位までの表示にする場合は1です。
1の桁を四捨五入し10の桁までの表示にする場合は-1です。
桁が大きくなる方がマイナスになるのでちょっと勘違いしやすいです。
「ROUNDUP関数」切り上げ
=ROUNDUP(四捨五入する数値またはセル,桁数)
「ROUNDDOWN関数」切り捨て
=ROUNDDOWN(四捨五入する数値またはセル,桁数)
「INT関数」
INT関数はその数よりも小さい整数にする関数で書式は次の通りで、桁数の指定はありません。
=INT(数値やセル)
100.93の小数点以下を無視し、100とするINT関数の数式は次の通りです。
=INT(100.93)
マイナスの値の-100.93を-100にINT関数で次のように数式を作ってみます。
=INT(-100.93)
すると答えは、-100にならず、-101になります。
INT関数は、その数よりも小さい整数にします。
マイナスの方向だと、数値はよりマイナス側になりますので、-101となるので、注意が必要です。
これは四捨五入などを扱う関数の中でINT関数のみ注意する点です。マイナスの値がなければINT関数はよく端数処理で使う関数です。
「TRUNC関数」
INT関数はマイナスの値の場合、想定とは違う動きになります。
そこで、INT関数の代わりにTRUNC関数を使えば、小数点以下を完全に無視した値とすることができます。使い方はINT関数と同じで次の通りです。
=TRUNC(数値やセル)
次の計算式は答えが-100になります。
=TRUNC(-100.93)
表示と計算結果を必ず合わせるオプション設定
Excelには上記のような関数で処理しなくても、表示上の数字を使って計算させることによって矛盾のない計算書を作成するオプションがあります。
ファイルタブの中のオプションをクリックし、詳細設定を選び、次のブックを計算するときの中の、表示桁数で計算するのチェックを入れます。
そうするとその設定したブックのみ表示桁数で計算することができます。
ただし、計算結果を無理に合わせ込むので、正確な計算結果を得ることができない場合もあるので注意しましょう。
まとめ
今回は請求書作成などに使える端数処理の方法を、関数で行う方法と、オプションで行う方法の2通りを紹介しました。
関数はROUND関数とその仲間、INT関数とTRUNC関数に分けられます。
ROUND関数は桁数の指定があります。表示桁数が小数点までのところを0として、小数点以下の方向がプラス、10の桁100の桁の方向がプラスになります。
切り捨てだけで良くて桁数の指定がいらない場合は、INT関数が便利です。消費税の計算などはINT関数を使うケースが多いです。
サンプルファイルのセルE10からE12に、今回のROUND関数の仲間やINT関数、ROUND関数を設定してみましょう。
サンプルファイルはコチラ
サトウヨシヒロ
ExcelをはじめとしたOfficeソフトのプロフェッショナル。
テクニカルライターであり、インストラクターであり、業務改善のエキスパートで、セミナーやパソコンレッスン、雑誌、ブログ記事の監修・執筆をしています。
『Officeの魔法使い』
『Twitter・佐藤嘉浩(Excelの魔法使い・スピルのひと)＠仙台』
