Excel関数で1000円単位の数字にする方法をお探しですね。
広告
Excelで1000円未満を切り捨てたり、5の倍数に丸めたりする方法
Excelで価格表や見積書を作っていると、「1000円未満は切り捨てたいな」とか「5円単位、500円単位にきれいにそろえたい」って思うこと、ありますよね。
普通のROUND関数でも丸めはできるんですが、「○○の倍数に丸める」ならMROUND・CEILING・FLOOR関数を使った方が断然わかりやすいんです。
この記事では、実際の仕事でも使いやすい例を交えながら、これらの関数の使い方を解説していきます。
まずは3つの関数の違いを理解しよう
MROUND・CEILING・FLOOR関数は、どれも「基準値の倍数」に数値をそろえるための関数です。
たとえば基準値を「5」にすれば5の倍数、「1000」にすれば1000円単位、「500」にすれば500円単位で丸められます。
普通のROUND関数は「小数第何位」「十の位」「千の位」みたいに桁数で指定しますよね。
でも、MROUND・CEILING・FLOORは「何の倍数にするか」を直接指定できるんです。
これが大きな違い。
だから、価格を5円刻みにしたり、発注数を12個単位にそろえたり、時間を15分単位に丸めたりするのがすごく簡単になります。
3つの関数の役割ははっきり分かれています。
– **MROUND関数**：一番近い倍数に丸める
– **CEILING関数**：倍数に切り上げる
– **FLOOR関数**：倍数に切り捨てる
たとえば「12」を5の倍数に丸める場合で考えてみましょう。
MROUNDなら10になります。
「13」なら15です。
一方、CEILINGなら12も13も両方とも15に切り上げ。
FLOORならどちらも10に切り捨てます。
つまり、どの関数を選ぶかは「近い倍数がいいのか」「必ず上げたいのか」「必ず下げたいのか」で決めればOKです。
基本の書き方はどれも似ています。
– MROUND関数：`=MROUND(数値,基準値)`
– CEILING関数：`=CEILING(数値,基準値)`
– FLOOR関数：`=FLOOR(数値,基準値)`
たとえばA2セルの値を5の倍数に丸めるなら`=MROUND(A2,5)`、5の倍数に切り上げるなら`=CEILING(A2,5)`、5の倍数に切り捨てるなら`=FLOOR(A2,5)`です。
数式の形が揃っているので、1つ覚えれば残りも簡単に理解できますよ。
1000円未満を切り捨てるならFLOOR関数が一番わかりやすい
「1000円未満切り捨て」っていうのは、12,345円を12,000円に、98,765円を98,000円にするような処理のことです。
これをやりたいときは、FLOOR関数で基準値に1000を指定するのが一番わかりやすいです。
A2セルに金額が入っているなら、数式は`=FLOOR(A2,1000)`。
これだけです。
この式は、A2の金額を超えない範囲で一番近い1000の倍数へ切り捨ててくれます。
請求金額、予算表、概算見積もりなんかで「千円単位にそろえたい」ってときに便利です。
同じことはROUNDDOWN関数でもできます。
`=ROUNDDOWN(A2,-3)`って入力すれば、千円未満を切り捨てられます。
でも、ROUNDDOWN関数は桁数に「-3」を指定するっていう考え方なので、初心者にはちょっとわかりにくいんですよね。
FLOOR関数なら「1000の倍数に切り捨てる」って読めるので、あとから数式を見返したときに「あ、これは千円単位にしてるんだな」ってすぐわかります。
特に500円単位、2000円単位、250円単位みたいに、桁数だけじゃ表しにくい単位で処理したいときは、FLOOR関数の方が断然自然です。
具体例を見てみましょう。
– A2に「12,345」→ `=FLOOR(A2,1000)` の結果は「12,000」
– A3に「12,999」→ 結果は「12,000」
– A4に「13,000」→ もう1000の倍数なので「13,000」のまま
ここで注意したいのは、セルの表示形式で小数点や桁を省略しているだけでは、実際の値は変わっていないということ。
計算結果をちゃんと千円単位に変えたいなら、表示形式じゃなくてFLOOR関数やROUNDDOWN関数で値そのものを丸める必要があります。
ちなみに、Excelのバージョンによっては、CEILING.MATH関数やFLOOR.MATH関数っていう選択肢もあります。
普通の正の金額を扱うだけなら`=FLOOR(A2,1000)`で問題ない場面がほとんどですが、マイナスの数を含むデータを扱う場合はちょっと注意が必要です。
古いCEILING関数・FLOOR関数では、数値と基準値の符号の組み合わせによってエラーになることがあります。
売上や価格みたいに正の数だけを扱う表なら大きな問題にはなりにくいんですが、返品額や差額なんかでマイナス値を扱う場合は、あらかじめテストしてから使うと安心です。
5の倍数単位に丸めるなら目的に合わせて関数を選ぼう
5の倍数単位に丸めたい場合は、まず「近い5の倍数にしたいのか」「必ず5の倍数へ切り上げたいのか」「必ず5の倍数へ切り捨てたいのか」を決めます。
A2セルの数値を5の倍数に丸めるなら、
– 一番近い倍数にしたいとき：`=MROUND(A2,5)`
– 切り上げたいとき：`=CEILING(A2,5)`
– 切り捨てたいとき：`=FLOOR(A2,5)`
たとえばA2が「12」の場合、MROUNDは10、CEILINGは15、FLOORは10になります。
A2が「13」の場合、MROUNDは15、CEILINGも15、FLOORは10です。
**MROUND関数**は「四捨五入に近い感覚」で倍数に丸めたいときに便利です。
5の倍数で考えると、10と15の中間は12.5ですよね。
12は10に近いから10になり、13は15に近いから15になります。
じゃあ12.5みたいにちょうど中間の値はどうなるの?って思いますよね。
ExcelのMROUND関数では0から離れる方向へ丸められます。
正の数なら12.5は15になるんです。
この特徴を知っておくと、価格調整や数量調整で「なんでこの値になったの?」って聞かれたときに説明しやすくなります。
一方、販売価格や手数料の計算では、MROUNDよりCEILINGやFLOORを選ぶ方が適切な場合もあります。
たとえば「送料は5円単位で切り上げる」っていうルールならCEILING、「割引後価格は5円未満を切り捨てる」っていうルールならFLOORを使います。
MROUNDは一番近い5の倍数にしてくれる便利な関数ですが、常に切り上げ・切り捨てのルールを守る関数じゃないんです。
仕事での端数処理では、税務とか社内規程、取引先との取り決めで丸め方が決まっていることがあるので、単に結果がきれいに見える関数を選ぶんじゃなくて、ルールに合う関数を選ぶことが大事です。
5の倍数だけじゃなく、10、50、100、500、1000なんかにも同じ考え方が使えます。
たとえばA2の金額を500円単位で処理するなら、
– 近い値に丸める：`=MROUND(A2,500)`
– 切り上げる：`=CEILING(A2,500)`
– 切り捨てる：`=FLOOR(A2,500)`
基準値を変えるだけで処理単位を柔軟に変更できるので、価格表や見積書の条件が変わってもすぐ対応できます。
複数の商品や明細に同じ処理をしたいときは、先頭行に数式を入力して下方向へコピーすれば一括で丸め処理を適用できますよ。
実務で間違えやすいポイントと使い分けのコツ
MROUND・CEILING・FLOOR関数を使うときに一番多いミスは、丸めたい目的と関数が合ってないことです。
「1000円未満切り捨て」って書いてあるのにMROUNDを使うと、値によっては切り上がっちゃいます。
たとえば12,600円を1000円単位でMROUNDすると13,000円になりますが、1000円未満切り捨てなら12,000円じゃないとダメですよね。
MROUNDは「近い倍数へ丸める」関数であって、「切り捨て専用」じゃないんです。
端数処理のルールがはっきりしている場面では、まず切り上げ・切り捨て・一番近い値のどれかを確認してから数式を作ることが大切です。
もう1つの注意点は、表示形式による丸めと関数による丸めを混同しないこと。
Excelでは、セルの表示形式や小数点の桁数ボタンを使うと、見た目だけを丸めて表示できます。
でも、セルの中に保存されている実際の値は変わってないんです。
だから、見た目では1,000円に見えていても、内部的には999.6円のまま計算されて、合計や平均が想定とずれることがあります。
請求書、経費精算、売上集計みたいに計算結果の正確性が必要な表では、表示形式だけに頼らず、FLOORやCEILINGなんかの関数で値そのものを処理しましょう。
実務では、次のように目的別に覚えると迷わなくなります。
– **1000円未満を切り捨てる**：`=FLOOR(A2,1000)`
– **1000円単位に切り上げる**：`=CEILING(A2,1000)`
– **一番近い1000円単位に丸める**：`=MROUND(A2,1000)`
– **5の倍数に切り捨てる**：`=FLOOR(A2,5)`
– **一番近い5の倍数に丸める**：`=MROUND(A2,5)`
あと、基準値をセルに分けておくと、あとから単位を変更しやすくなります。
たとえばB1セルに「1000」って入力して、A2の金額をB1の単位で切り捨てるなら`=FLOOR(A2,$B$1)`とします。
絶対参照の「$B$1」にしておけば、数式を下へコピーしても基準値の参照先がずれません。
500円単位に変えたくなったらB1を「500」に変更するだけで、表全体の丸め単位を切り替えられます。
条件が変わりやすい見積書やシミュレーション表では、この作り方にしておくとメンテナンスが楽になりますよ。
まとめ
Excelで1000円未満切り捨てをするならFLOOR関数、5の倍数など指定した単位に近い値で丸めるならMROUND関数、指定単位へ必ず切り上げるならCEILING関数を使うのが基本です。
ROUND・ROUNDUP・ROUNDDOWN関数は桁数で処理するのに対して、MROUND・CEILING・FLOOR関数は倍数で処理できるので、価格や数量の単位調整にぴったりなんです。
端数処理は小さな違いでも合計金額や請求額に影響するので、見た目じゃなくて実際の値がどう変わるかを意識して、目的に合った関数を選びましょう。
慣れてくると「あ、これはFLOORだな」「これはMROUNDが便利だな」って自然に判断できるようになりますよ。
広告
