A1番地には、このような小数点以下3桁の数字が入力されています。
この小数点以下を、何桁まで見せるのか、というのを調整するのが表示形式。
A1番地を選択し、
［ホーム］タブ、
［数値］グループにある、［小数点以下の表示桁数を減らす］ボタンをクリックすると、
表示される小数点以下の桁数が1桁減り、小数点以下2桁で表示されるようになりました。
この時Excelさんは、隠れてしまう小数点以下3桁目を四捨五入するので、
このように表示されるわけです。
もう一度［小数点以下の表示桁数を減らす］ボタンをクリックすると、
小数点以下の桁数を更に1桁減らし、小数点以下1桁で表示されるようにするのと同時に、Excelさんは、その隠れてしまう小数点以下2桁目を四捨五入するので、
このように表示されます。
更に［小数点以下の表示桁数を減らす］ボタンをクリックすれば、小数点以下の桁数をまた更に1桁減らし、小数点以下が表示されないようにするのと同時に、Excelさんは、隠れてしまう小数点以下1桁目を四捨五入するので、
このように表示されるというわけです。
表示形式で小数点以下の桁数をどこまで見せるかを調整している時には、それと同時に、隠れ始める桁数のところで四捨五入した結果の数字が表示される、ということになります。
そしてもう1つ忘れてはいけないことは、表示形式というのは、あくまでも表面上の見かけの設定でしかないということ。
その証拠に、A1番地を選択した状態で数式バーを見ると、そのセルの中に入っているデータを確認できるわけですが、A1番地に入力されている数字は、A1番地に見えている「27」ではなく、「26.517」であることが分かります。
小数点以下で四捨五入して、小数点以下の桁数を見えないように表示しているから「27」と見えているだけのことであって、実際のこのセルのデータはあくまでも「26.517」ということです。
スポンサーリンク
表示形式とROUND関数を使う場合の決定的な違い
それでは前の章のポイントを踏まえたうえで、練習用データを操作しながらご覧いただいている方は、画面左下のシート見出しをクリックして、Sheet2に切り替えておいてください。
今度は、このような表で、「ご利用金額」と「ポイント付与率」から、獲得ポイントを求めてみたいと思います。
先ほど、小数点以下の桁数を表示形式で操ると、四捨五入したものが表示されることを見ていただきましたが、四捨五入といえばROUND関数も思い付きます。
実は小数点以下の桁数を、表示形式で調整した場合と、ROUND関数で処理した場合とでは、ひとくくりに「四捨五入」と言っても、決定的な違いが出てくるんです。
まずは、小数点以下の桁数を表示形式で調整する場合を見ていきたいのですが、その前に獲得ポイントを求めていきましょう。
C5番地の獲得ポイントは、B5番地に入力されている「ご利用金額」と、D1番地に入力されている「ポイント付与率」を掛けることで求めます。
ちなみに、「なんで数式のD1番地のところにドルマークが付いてるんだ！」という方は、この先をご覧いただくことよりも大切なことがあります！
まずは「計算式がずれないように！絶対参照で数式を固定」をご覧ください。
C5番地に獲得ポイントを求めることができました！
あとはこの掛け算の数式を、残りのセルにコピーすべく、オートフィルします。
残りのセルでも、獲得ポイントを求めることができました！
現在は、計算結果がそのまま表示されています。
今回は小数点以下を見せたくないので、獲得ポイントのセルが範囲選択されている状態で、
［ホーム］タブ、［数値］グループにある、［小数点以下の表示桁数を減らす］ボタンをクリックすると、
小数点以下を隠して、見せなくするように設定できました。
先ほどは獲得ポイントを求めてから、その小数点以下の見え方を表示形式で調整しましたが、今度はROUND関数を使って、獲得ポイントの小数点以下を直接四捨五入し、その結果を、ダイレクトにセルに表示するようにしてみましょう。
D5番地を選択し、ROUND関数を挿入します。
ROUND関数の引数の設定はこのようになります。
「B5番地とD1番地を掛け合わせたものを、桁数が「0」になるように四捨五入してね」という設定です。
「この意味がさっぱり分からん、特に桁数「0」って何だよ？」という方は、この先をご覧いただくことよりも大切なことがあります！
まずは「切り上げ・切り捨て・四捨五入｜ROUNDUP・ROUNDDOWN・ROUND関数の使い方」をご覧ください。
D5番地に獲得ポイントを求めることができました！
あとはこの数式を、残りのセルにコピーすべく、オートフィルします。
残りのセルにも、ROUND関数を使って、獲得ポイントの小数点以下を直接四捨五入して、ダイレクトに表示するよう、設定することができました！
スポンサーリンク
違いが出てしまう仕組みを解明！
さてここで、獲得ポイントの合計を求めたC8番地とD8番地に注目すると、合計の結果が違うではありませんか！
でも、おかしくありませんか？
月別の獲得ポイント欄に表示されている数字は、表示形式を使った方も、ROUND関数を使った方も同じ。
なのに、合計が違ってる！
それではその仕組みを解明していきましょう！
この謎は小数点以下の部分が握っているので、現在表示されていない小数点以下が見えるように、設定を変更していきましょう。
月別の獲得ポイントのセルであるC5番地からD7番地を範囲選択し、
［ホーム］タブ、［数値］グループにある、［小数点以下の表示桁数を増やす］ボタンをクリックすると、
小数点以下1桁目を見せることができました。
「表示形式」で操った方は、小数点以下の数字が見えていますが、ROUND関数の方は、獲得ポイントを求めつつ、その小数点以下をダイレクトに端数処理したので、小数点以下が「0」になっています。
もう一度［小数点以下の表示桁数を増やす］ボタンをクリックすると、小数点以下2桁目まで表示させることができますが、どちらの計算結果も小数点以下2桁目は「0」なので、
小数点以下2桁目は見えてなくてOKです。
［小数点以下の表示桁数を減らす］ボタンを押して、
小数点以下1桁目までの表示にしておきます。
振り返ってみると、表示形式で見え方を調整していた時には、表面上は「347」「172」「346」と見えていても、
実際は「346.8」「171.6」「345.6」なので、表示される合計は、この小数点付きの実際の数字を足した「864」になります。
ところが、ROUND関数で処理した方は、獲得ポイントを求めた時点で、獲得ポイント自体の小数点以下をダイレクトに四捨五入で端数処理しているので、「347.0」「172.0」「346.0」という、それぞれを四捨五入した結果を足しているため、「865」という合計になっているわけです。
今回は「獲得ポイント」というものを例にしましたが、これが金額計算になってくると、たかが1円でも、その違いが問題になったりすることもあります。
私の経験では、金額の計算の場合は、きっちりROUND関数で端数処理して計算した方が好ましいと思うのですが、ザックリと計算するだけでいい時は、そこまでしなくても・・・といったことがあると思うので、ルールを決めて処理していくことが大切だと思います。
Officeの使いこなしテクニックをメールでお届け！