配賦・按分計算時に端数処理後の合計を一致させる

前回の続きです。

計算途中や計算結果で端数処理をした結果、
元の金額と一致すべきところが一致しなくなる場合があります。

端数処理をしたら合計が合わなくなる

例えば、
100万円の費用を、
按分基準に従って、5部門で按分して仕訳を起票したいという場合。

次のように、
round関数を使って、単純に按分して四捨五入しただけでは、合計金額が合わなくなる場合があります。

hasuu2_1

round関数を使って各部門で四捨五入をしているわけですが、その合計を取ると999,999になってしまい、元の数字1,000,000に1足りなくなってしまいます。

無理やり端数処理をするのは止めよう

こういうときにやりがちなのが、D列の計算式をむりやり変更して端数を調整してしまう方法です。

むりやり端数調整をしたらダメ!
hasuu2_1_2

D8セルに入れた、次の計算式で、むりやり端数調整をしています。

=round($C$9/$B$9*$B8,0)+1

でも、この方法は、望ましい方法とはいえません。

なぜかというと、
今回の金額の計算に特化したシートになってしまい、汎用性がなくなってしまう
からです。

例えば、
同じシートを使って、
元金額(C9セル)を1,300,000円に打ち変えてみましょう。

hasuu2_1_3

合計が1,300,001円となり、明らかに正しくない計算結果になってしまいます。
このように、式の中で無理やり変な調整をしてしまうと、使いまわしができなくなってしまうのです。

しかも、計算式の一部だけ変更していることに気づきにくいので、計算がおかしくなった原因を探るために時間を浪費してしまいがちで、全くいいことがありません。

端数がずれる調整は、別列で行う

では、どうすればいいのかというと、端数調整は別の列で行います。

もし、
端数調整をすべき部門が決まっているのであれば、
そこに、端数分を調整するような式を入れてしまいましょう。

hasuu2_1_4

この方法だと、

  • 端数調整をする部門が変わると、シート変更が必要
  • できれば、E4セル~E8セルには同じ式が入っていてほしいが、
    実際には、E7セルにしか式が入っていない

など、
まだ、完璧ではない点もありますが、
一応、実用上は、問題のないレベルなのではないかと思います。

完璧を期するなら調整対象部門も計算式で決める

もし、完璧を期するのであれば、

さらに、端数処理をどの部門で行うべきかも、
計算式で表現してしまいましょう。

例えば、

  • 按分基準最大の部門で端数処理を行う
  • 按分基準最大の部門が複数ある場合には、上の段のほうで端数処理を行う

というような方針の場合には、
次のような計算式を入れると、うまくいきます。

hasuu2_1_5
※作業用にC列を使っています。

さきほどの計算式から比べると、
随分、複雑になってしまいますが、

ここまで作りこんでおけば、
按分基準が変わっても、元金額が変わっても、
対応できるシートになります。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。