前回の記事で丸め処理 (四捨五入、切り上げ、切り捨て) を説明しました。
Excel で作成した表で「途中計算は合っているのに合計が合わない」とか「小計と総合計で金額が少しずつずれている」という現象でした。
ただこれ、丸め処理だけの問題だけじゃなく、Excel が自動調整してくれる見た目だけの丸め処理も問題で、知っていれば便利、知らないと大問題な仕組みのひとつです。
この記事では、計算結果が合わない現象の原因と、正しく表示させるための方法をわかりやすく解説します。
仕事などで Excel を使うときはミスの無いようにしましょう
前回の記事はこちら
計算結果が合わない現象
前記事の冒頭に表示した源泉徴収簿データです。(本物の数値とは異なります)
金額の部分には「単価 × 時間」の数式が入力されており、合計には「各月の積算」する数式が入力されています。
この表の何が合っていないかというと、月の合計金額 (23,438 + 14,063) と 合計金額 (37,500) が合っていません。
簡単に現象を再現してみたい方は、以下の数値や数式をセルに入力してください。
999.99999
=2/3表示結果はこちら
※ 左側に入力した内容を表示してます
セルの列サイズを拡大、縮小すると面白い現象が確認できます。
同じ値が入っているのに表示される数値が異なります。
原因
Excel のセル幅を変えると数値が変わる現象、これは Excel が自動で表示を最適化しているために発生します。
33.333… などの値を表示上 33.33 と表示する現象ですが、Excel 的には「表示幅の制限」、「表示桁数の桁数不足」を判断し、正しく自動調整している結果となります。
Excel の内部では「正しい値」と「表示上の値」を別々で管理してるため、正しい値を表示するには正しい書式設定を設定する必要があります。
実際の値を確認
実際に入力した値は Excel 上部の数式バーに表示されます。
確認したいセルにカーソルを合わせると確認できます。
補足 ： 数式の検証
セルに数式が入力されている場合、「数式の検証」で計算結果を確認できます。
「数式の検証」ツールの表示制限で、10 桁で丸められますので、本当の正しい値は見れない可能性はあります。
※ ちなみに補足の補足ですが、Excel の精度では「整数桁+小数桁」合わせて 15桁程度が限界のようです。
数式の検証はこちらの記事で。
対策
書式を設定する前に、前提として以下の内容が決定されている必要があります。
- 表示したい桁数が決まっていること
- 表示したい桁数の正しい値が格納されていること
金額であれば整数値みたいな感じです。
なお、正しい値の算出は丸め処理が必要なので、前回の記事をご覧ください。
書式設定
では書式設定です。
設定対象のセルにカーソルを合わせ、キーボードで「Ctrl + 1」ボタンを押すか、右クリックして「セルの書式設定」をクリックすると以下の画面が表示されます。
↓
あとはセルの書式設定で「表示形式」の「分類」を数値に変更して、「小数点以下の桁数」を表示したい桁数を決めて OK ボタンをクリックするだけです。
セルの幅が大きくても指定した桁数で表示されるようになりました。
注意点
セルの書式設定を変更した場合、Excel の自動調整が無効になります。
設定した書式で表示できない場合、代わりに ###（シャープ記号） が表示されます。
★ 下図は隣 (幅が狭い) のセルに同じ書式を設定したもの
↓ セル幅を広くすると正しく表示されます。
書式設定が変わる要因
セルの書式設定が予想しない設定に変わるよくある発生原因は以下となります。
基本的には Excel が自動調整 (自動設定) してくれる便利機能ですが、操作するときは注意しましょう。
コピー＆貼り付け (コピペ)
他のセルやシートからコピペしたときなどに、コピー元の書式を反映してしまい想定と異なる結果になることがあります。
書式を変えずに貼り付ける場合は、「値のみ貼り付け」や「数式のみ貼り付け」など、形式を選択して貼り付けすることで書式変更を回避できます。
入力時の自動判定
日付や金額記号など、Excel が自動判定する文字を入力すると Excel が自動的にセルの書式設定を変更してしまう場合があります。
一度自動判定されるとセルの値を変更しても既に設定されている書式で表示されてしまいます。
- 1/2 → 日付
- 50% → パーセンテージ
- \100 → 通貨
など。
事前に書式を設定しておくか、文字列として表示したい場合は ‘ (シングルクォーテーションマーク) を先頭に入れるという方法もあります。
まとめ
Excel の計算結果が合わない原因と対策でした。
Excel には「正しい値」と「表示上の値」を別々で管理しているため、正しく表示するには正しい「セルの書式設定」が必要となります。
コピペや自動判定で間違った表示になってしまうことがありますので、操作後の確認は忘れずに。