請求書・見積書・受領書などの書類を作成するときに、単価×数量などで金額を計算することがあります。単純に掛け算すれば問題ことが多いですが、私は今回、エクセル(EXCEL)関数を使って自動表示・自動入力して入力ミスの削減や工数削減をしようと思っています。金額を計算したときにエラー表示「#VALUE!」が表示され小計や合計を計算できない状態になってしまいました。
そこで今回は、計算結果のエラー表示を解決する方法とROUND関数を使った四捨五入のやり方についてまとめました。
請求金額の明細と合計を計算する関数式
請求書の定形書類を作っていて、金額欄に単価×数量の計算式を入力します。セルG10に「=E10*F10」と入力してG10~G14に貼り付けます。そうしたら、商品コードが入力されていない行にはエラーの「#VALUE!」が表示され、小計や合計が計算できない状態になります。これでは、意味がないので「IFERROR関数」を使って解決したいと思います。
VLOOKUP関数で商品コードを入力すると、商品名と単価が自動表示されるように設定しましたが、商品コードが入力されていないとエラー表示されてしまいますので、「IFERROR関数」を使って解決しました。
ここでも、「IFERROR関数」を使って対策したいと思います。セルに入力した「=E10*F10」を「IFERROR関数」を使って下記のようにします。
=IFERROR(E10*F10,””)
金額を計算する範囲に貼り付けることで、「#VALUE!」を表示しないようにでき、小計や合計を計算することができます。
エクセル(EXCEL)関数のROUND関数の使い方
ROUND(ラウンド)関数は、指定した桁(けた)数で四捨五入することができます。どの位(くらい)で四捨五入するかを指定することができますので、小数点以下とか1の位とか10の位と任意に指定することができますが、指定の仕方にルールがあります。
桁数の指定 | 対象の位 | 1234.567で例 |
---|---|---|
-3 | 100の位 | 1000 |
-2 | 10の位 | 1200 |
-1 | 1の位 | 1230 |
0 | 小数点以下1位 | 1235 |
1 | 小数点以下2位 | 1234.6 |
2 | 小数点以下3位 | 1234.57 |
=ROUND(数値,桁数)
数値・・・四捨五入する数値を指定します。
桁数・・・四捨五入する桁を指定します。
切り上げ・切り捨てる関数
ROUNDは四捨五入をする関数ですが、切り上げたり、切り捨てたりする関数があります。
指定した桁で切り上げる関数
=ROUNDUP(数値,桁数)
指定した桁で切り下げる関数
=ROUNDDOWN(数値,桁数)
数値を四捨五入するROUND関数を使って計算する
請求書の小計・消費税・合計・請求金額を計算したいと思います。小計と合計はSUM関数を使って計算します。小計は注文一覧の金額の合計を計算するようにしていし、合計は小計と消費税を計算した結果を表示するようにしました。請求金額に関しては、合計で計算されて値を表示するようにします。
消費税の金額を四捨五入するROUND関数を使って計算すると、
=ROUND(G16*D17,0)
となり、「小計×税率8%」を計算して、小数点以下1位を四捨五入するように桁数を「0」にしました。
数値・・・消費税を計算する式を指定します。
桁数・・・小数点以下1位を四捨五入して整数で表示したいので「0」を指定します。
表示形式による四捨五入との違いは?
エクセル(EXCEL)には、ROUND関数以外にも数値の表示形式を設定することができる「桁区切りスタイル」や「通貨表示形式」があります。この「桁区切りスタイル」や「通貨表示形式」を利用すると小数点以下を自動的に四捨五入し、小数点以下の値が表示されないようになりますが、セルに入力されている数値は変わりません。それに対してROUND関数はセルに含まれる数値そのものを四捨五入するという違いがあります。
表示形式と関数の違いは、
・表示形式は数値そのものに変更はありません。
・ROUND関数は四捨五入した結果が数値として残ります。
消費税の端数処理の仕方について
消費税の小数点以下の端数処理については、切り捨てることが多いと思いますが、社内によっては四捨五入したりと処理の仕方が違うことがあります。契約書などに消費税の端数処理の方法についてなどの記載があるときには、それに従い、間違わないように正しく処理していくようにしましょう。
小数点以下を切り捨てる関数
小数点以下を切り捨てて「整数」を表示するのに、ROUNDDOWN関数を使うのもいいですが、小数点第1位以下を切り捨てて整数を表示するなら「INT(インテジャー)関数がおすすめです。この「INT関数」は数値を指定するだけで小数点以下を切り捨てた数値を表示できます。
=INT(数値)
編集後記
計算した結果の数値が小数点が表示されてしまうことがあります。請求書・見積書・受領書・日計表などの金額を表示するときには小数点は必要ありません。でも、小数点を残しておくと合算するときなどに計算が合わないという結果になります。
そのため、「桁区切りスタイル」や「通貨表示形式」の表示形式で調整してしまうと電卓などで計算したときと、エクセルの合計を確認したときに1円とか一致しないことが発生しますので、セルに含まれる数値自体を整数にできるROUND関数やINT関数を使うことをおすすめします。
コメントを残す