【決定版】社会保険料、薬剤料の「五捨五超入」をエクセルで計算する

社会保険料の給与天引きの計算や薬剤料の計算では、通常の「四捨五入」ではなく「五捨五超入」(あるいは「五捨六入」)と言われる計算をするときがあります。

【結論】五捨五超入の一番簡単な数式

たとえば、A1セルに入っている数値を「五捨五超入」して整数にしたいときには、次の数式で計算ができます。

=ceiling(A1-0.5, 1)

注意点は2つあります。

注意点1:ceiling関数を使う

他のホームページ・ブログでは「round関数」を使った例が紹介されていることがほとんどですが、round関数を使ってしまうと、後で書くように、数式が若干複雑になってしまいます。

数式を簡単にするために、可能であれば「ceiling関数」を使いましょう。
(詳細は、ceiling関数を使う理由で説明します)

注意点2:2つ目の引数は「1」

2つ目の引数には「1」を指定してください(引数の意味は、「ceiling関数とは?」で解説します)。

round関数のイメージで「0」を入れてしまうと、正しく計算できませんので、ご注意ください。

「五捨五超入」とは?

「五捨五超入」というのは、次のような計算方法です。

  • 端数が「.5」以下のときは、切り捨て
  • 端数が「.5」を超えるときは、切り上げ

通常の「四捨五入」とどこが違う?

「四捨五入」と比較すると、ほとんどの場合は計算結果は一緒になるのですが、端数が「.5」ちょうどのときの扱いが違っています

四捨五入の場合

端数が「.5」ちょうどのときは切り上げ

「五捨五超入」の場合

端数が「.5」ちょうどのときは切り捨て

エクセルでは、このような端数処理を行う「専用の関数」はありませんので、工夫をして計算をしないといけません。

そこで、今回はceiling関数をうまく使って数式を組み立てました。

ceiling関数とは?

ceiling関数とは、[数値]を、指定した[基準値]の倍数に切り上げる関数です。

書式

=ceiling( [数値] , [基準値] )

数値 元の数値を指定します
基準値 切り上げる[基準]を指定します

下の2つの例を見て頂くと、ceiling関数のイメージはつかめるのではないかと思います。

例1
=ceiling(1.4, 1)
 → 2
例2
=ceiling(32, 15)
 → 45

ceiling関数で五捨五超入ができる理由

このceiling関数を使っただけでは、単なる「端数切り上げ」の処理しかできません。

そこで、実際の数式では、

=ceiling(A1-0.5, 1)

というように「A1セルから0.5を引いた数」をceiling関数で切り上げることで、「五捨五超入」の処理を実現しています。

なぜ、これでうまくいくのかは、下記の表のように

  • A1セルから0.5を引く
  • ceiling関数で切り上げ

の2つのステップを分解すると、わかりやすくなるのではないかと思います。

うまい具合に「13599.5<元の値≦13600.5」のときに、計算結果が「13600」になっていることがわかると思います。

ceiling関数を使う理由

ところで、今回の計算では、切り上げの計算をするときに、よく使われるroundup関数は使わず、あえてceiling関数を使っています。

なぜかというと、今回の計算では、roundup関数を使うと、一部計算結果に不具合が出てしまうのです。

元の数値が「0」の場合のroundup関数の計算結果が「-1」になってしまっています。
これは、明らかにおかしいですよね?

ceiling関数とroundup関数は「負の数値に対する」計算結果が違う

なぜ、こんなことになるのか、少し掘り下げて見ます。

まず、ceiling関数とroundup関数を比較してみましょう。

  • =ceiling( [数値] , 1)
  • =roundup( [数値] , 0)

この2つの関数では、2つめの引数の「意味」が違っていることにご注意ください。

  • ceiling関数の場合は「基準値」(=「1」の整数倍に切り上げ)
  • roundup関数の場合は「端数処理する桁数」(=小数第「0」位に切り上げ)

この2つの関数は、どちらも「切り上げ」計算をする関数です。
ですから、この2つの数式を計算してみると、とてもよく似た計算結果になります。

実際に計算結果を比較してみると、「元の数値」が正の数の場合には、まったく同じ計算結果となります。
ところが、「元の数値」が負の数の場合には、計算結果に違いが出てきます

ceiling関数とroundup関数では「切り上げ」の意味が違う

なぜ、2つとも「切り上げ」をする関数なのに、計算結果が変わるのでしょうか?

実は、この2つの関数では「切り上げ」の考え方が違うのです。

ceiling関数の切り上げは「数が大きくなる方向」
  • 0.5の端数を切り上げる場合には、数が大きくなる「1」に切り上げ
  • -0.5の端数を切り上げる場合には、数が大きくなる「0」に切り上げ
roundup関数の切り上げは「符号を除いた数字が大きくなる方向」
  • 0.5の端数を切り上げる場合には、符号を除いた数字(0.5)が大きくなる「1」に切り上げ
  • -0.5の端数を切り上げる場合には、符号を除いた数字(0.5)が大きくなる「1」にマイナス符号を付けた「-1」に切り上げ

ceiling関数と比べると、元の数が「負の数」の場合、切り上げる向きが「逆方向になっている」のがわかると思います。

このように、「切り上げ」の意味が違うため、負の数の端数処理をしたときに計算結果が狂ってしまうのです。

roundup関数を使うと、元数値が0から0.5の場合に不具合が出る

さて、今回の計算では、

=ceiling(A1-0.5, 1)

というように、元数値(A1セル)の値-0.5を端数処理するという計算をしています。

そして、元数値が「0~0.5」までの場合、0.5を引くと負の数になり、ceiling関数を使うかroundup関数を使うかで計算結果が異なってしまうのです。

今回は、roundup関数を使うとこの範囲の数値が正しく計算されないため、ceiling関数を使う必要があるのです。

どうしてもroundup関数を使いたいときには?

どうしても、なじみがあるround系関数で処理をしたいときには、次のようにif関数と組み合わせましょう。

=if(A1<0.5, 0, roundup(A1-0.5, 0) )

A1セルが0.5未満のときは、roundup関数を使わずに、常に0を返すようにif関数を追加しました。

その結果、A1セルの値が0~0.5であっても、正しく計算結果を返してくれます。

(参考)round関数を使って、むりやり「五捨五超入」をする

ceiling関数、roundup関数などを使う書き方は、厳密な書き方ではあるのですが、数式を見たときに意味がわかりにくいと感じられるかもしれません。

やはり、「五捨五超入」を「元データを加工して切り上げるもの」ではなく、「四捨五入にちょっと修正を加えたもの」という体裁で数式を表現できたほうが、イメージしやすいのではないかと思います。

そこで、最後に、round関数に微調整を加えた形で「五捨五超入」を実現する方法もご紹介します。

ここで紹介するround関数を使う方法は、若干厳密性に欠ける方法ですので、あらゆる元データに対して正しい結果を返すものではありません。
元データの性質を十分に吟味してお使いください。

数式

たとえば、元データが「0.01刻み」のデータしかない場合、つまり「0.001」、「1.234」など、0.001の桁の数字が出てこない場合には、次の数式で、「五捨五超入」を計算できます。

=round(A2-0.001,0)

ポイントは、元データが「0.01刻み」なら、その刻み幅より細かい「0.001」という数字を引いて四捨五入をするというところです。

端数が「.5」ぴったりのデータの場合には、この操作をすることで端数が「.5」よりちょっと小さい数になるので、四捨五入の結果端数が切り捨てられるのです。

個人的には、この数式は、数式を見た瞬間に「五捨五超入」感が伝わってくるので、結構気に入っています。

ただし、繰り返しになりますが、この方法は、元データに「1.234」など、刻みが想定より細かい数字が入るとうまく動作しません
その点はご注意ください。

まとめ

「五捨五超入」をしたいのであれば、特別な理由がない限りは、冒頭で紹介したceiling関数を使いましょう。

なお、ceiling関数の2つめの引数は、roundup関数の2つめの引数とは仕様が違います。
整数単位に端数処理をしたい場合、roundup関数は「0」を指定しますが、ceiling関数では「1」を指定する必要があります。

勘違いしやすいところですので、十分ご注意ください。

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

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

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

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

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

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