いまさら聞けないExcelの使い方講座

【Excel】同僚が作成した売上表と合計金額が合わない！エクセルでデータは同じはずなのに計算結果が合致しない時に確認すべきこと

トップスタジオ

2018年8月24日 06:55

同僚が作成した売上表と金額が異なるのはなぜ？

　同じデータを用いて同じような方法で作成したのに、自分が作成した売上表と同僚が作成した売上表の合計金額が異なっている……という経験をしたことはありませんか。どちらの売上表も計算式は合っているはずなのになぜ……？と、原因を追究するのに苦労したことのある読者もいるのではないでしょうか。

　次の2つの商品別売上表の例（①と②）を見てください。どちらも同じデータ（定価、割引率、数量）をもとに売上表を作成しているのですが、「小計」欄（③）の金額が異なっていますよね。なぜでしょうか。

　この2つの表では、「小計」を求めるための数式は同じで、間違っていません。しかしながら、小計を求める数式で参照している「5％割引後の定価」を算出する際に行った四捨五入のやり方が異なっているため、このような事象が起きてしまっているのです。

　今回は、このような相違に悩まされないために、Excelにおける四捨五入の方法の違いについて解説します。Excelで小数点の含まれる数値を四捨五入する場合、「表示形式による四捨五入」（先ほどの例では、Aさんの商品別売上表で適用したやり方）と「ROUND関数による四捨五入」（Bさんの商品別売上表で適用したやり方）の2つの方法があります。まずは、どのような手順でそれぞれの売上表を作成したのか解説したあと、2つの表を比較して、どうして「小計」が異なってしまったのかを考えてみます。

表示形式による四捨五入とは

　ではまず、「表示形式による四捨五入」の方法（「Aさんが作成した商品別売上表」（①）の作成方法）について見ていきましょう。実際に売上表を作成しながら、その方法について解説していきます。

　B列の「定価」欄（②）、D列の「数量」欄（③）には、既にデータが入力されているものとします。

　C列に、5％割引後の定価（計算式は「定価×95％」）を求めます。実際に、セルC3をクリックして「=B3*95%」（④）と入力し、算出してみましょう。

　［Enter］キーを押して確定すると、計算結果が「978.5」（⑤）と表示されます。

　ここで、セルC3の値は小数になっているので表示形式による四捨五入を行います。セルC3（⑥）を選択して、［ホーム］タブ（⑦）の［数値］にある［小数点以下の表示桁数を減らす］（⑧）を1回クリックします。

　すると小数第1位で四捨五入され、「978.5」が「979」（⑨）という整数で表示されます。

　ここで重要なポイントは、［小数点以下の表示桁数を減らす］ボタンを使った場合、見かけ上は四捨五入されていますが、実際の「978.5」という値はそのまま保持されているということです。試しに、先ほどクリックした［小数点以下の表示桁数を減らす］ボタンの隣にある［小数点以下の表示桁数を増やす］ボタン（⑩）をクリックしてみてください。再び「978.5」（⑪）と表示されますね。このとおり、小数点以下の数値はただ隠れているだけで、実際に端数処理が行われて数値が変わってしまったわけではないのです。

　これが表示形式による四捨五入の仕組みです。

　再び［小数点以下の表示桁数を減らす］（⑫）を押して、セルC3（⑬）の表示を整数に戻しておきます。操作を続けて、売上表を完成させましょう。

　オートフィル機能を使用して、セルC3の数式をC列のほかのセルにもコピーします（⑭）。

　C列にある5％割引後の定価がすべて計算されました（⑮）。

　次に、E列にある小計（計算式は「5％割引後の定価×数量」）を求めます。セルE3をクリックして「=C3*D3」（⑯）と入力します。

　［Enter］キーを押すと数式が確定され、小計が表示されます（⑰）。

　先ほどと同様に、オートフィル機能を使用してセルE3の数式をE列のほかのセルにもコピーします。E列の小計がすべて計算されました（⑱）。

　「合計」欄にもそれぞれ数式を入力して表を完成させます。セルD6（⑲）には「=SUM(D3:D5)」と入力して数量の合計を求め、セルE6（⑳）には「=SUM(E3:E5)」と入力して売上金額の合計を求めます（数式を直接入力しなくても［オートSUM］ボタンで簡単に集計できますが、ここではオートSUMについての詳しい解説は省略します）。

　最後に、「小計」欄に桁区切りのスタイルを適用（㉑）して完成です。以上の手順で作成した表がAさんの売上表です。

ROUND関数による四捨五入とは

　では次に、「ROUND関数による四捨五入」の方法（「Bさんが作成した商品別売上表」の作成方法）について見ていきましょう。比較しやすいように、先ほど作成した表のすぐ下に作成します（①）。B列の「定価」欄（②）、D列の「数量」欄（③）には、既にデータが入力されている状態から始めます。

　C列にある5％割引後の定価（計算式は「定価×95％」）を求めましょう。この時にROUND関数を使って四捨五入した値を入力します。

　ROUND関数は、数値を四捨五入する時に使用する関数で、「=ROUND(数値,桁数)」という書式で記述します。1つ目の引数には、四捨五入したい数値を指定し、2つ目の引数には、四捨五入する桁数を指定します。例えば、「978.5」という数値を四捨五入して整数にしたい場合は、「=ROUND(978.5,0)」のように記述します。ROUND関数の詳細や桁数の指定の方法については、以前の記事で詳しく解説しているので参照してください。ここでは、小数が含まれる数値を四捨五入して整数にするには、桁数に「0」を指定すると理解しておいてください。

　では、セルC10に入力してみましょう。「=ROUND(B10*95%,0)」（④）と入力します。1つ目の引数には、5％割引後の定価を求める計算式「B10*95%」を指定し（⑤）、2つ目の引数には「0」（⑥）を指定します。

　［Enter］キーを押すと数式が確定され、計算結果が表示されます。「1,030×95％」の計算結果「978.5」が四捨五入されて「979」になりましたね（⑦）。このような四捨五入では、端数処理がされて、数値そのものが変わっているので、前述の例のように隠れている小数値はありません。

　これがROUND関数による四捨五入の仕組みです。

　セルC10の数式をC列のほかのセルにもコピーして、C列の5％割引後の定価をすべて算出します（⑧）。

　先ほどの手順と同様の方法で、「小計」欄や「合計」欄にデータを入力して、仕上げに桁区切りのスタイルを適用し、売上表を完成させます（⑨）。

　以上の手順で作成した表がBさんの売上表です。

　改めて「小計」欄や「合計」欄を見てみると、2つの売上表で値が異なっていますよね。次項では、両者を見比べながら、このような結果になってしまった理由を考えてみましょう。

2つの売上表の小計や合計が異なる理由

　前項で作成した2つの売上表は、なぜ「小計」欄や「合計」欄の値が異なってしまったのでしょうか。わかりやすくするために、前述の［小数点以下の表示桁数を増やす］ボタンをクリックして、小数第1位まで表示されるようにしましょう。

　セル範囲C3:C5とセル範囲C10:C12を選択した状態（①）で、［ホーム］タブ（②）の［数値］にある［小数点以下の表示桁数を増やす］（③）をクリックします（離れているセル範囲を選択するには、［Ctrl］キーを押しながらセル範囲をドラッグすると選択できます）。

　「スニーカー用ソックス 3足組」の5％割引後の定価に注目してみましょう。Aさんの売上表には「978.5」（セルC3）（④）と表示されていますが、Bさんの売上表には「979.0」（セルC10）（⑤）と表示されていますね！

　「5％割引後の定価」欄には、同じ値が入っているように見えて、実際は異なる値が入力されているのです。したがって、Aさんの小計は「978.5×20=19,570」、Bさんの小計は「979×20=19,580」と算出され、結果として「小計」欄の値が異なってしまったというわけです。

　このような四捨五入の違いについて、戸惑う読者もいるかもしれません。［小数点以下の表示桁数を減らす］ボタンは、あくまでも数値の見た目を整えるものです。例えば、小数点以下の桁数が多すぎてセルに収まりきらないようなシーンで使用すると便利です。数値自体を変更することなく表示桁数を減らせるので、すっきりと見やすくなります。

　対してROUND関数は、消費税や割引を計算するときなど、正確に数値を管理したいときに使用します。