【Excel】CEILING・FLOOR関数で切り上げ・切り捨て!Excelの端数処理を単位指定で行う方法

Excelで数値の端数処理は、Rounding関数などで行うのが一般的です。しかし、特定の単位で切り上げ・切り捨てを行いたい場合、これらの関数では対応できません。例えば、100円単位で切り上げたい、500円単位で切り捨てたいといったケースです。このような場合に役立つのがCEILING関数とFLOOR関数です。本記事では、CEILING関数とFLOOR関数を使って、指定した単位で数値を切り上げる・切り捨てる方法を解説します。これらの関数を使いこなすことで、より柔軟な端数処理が可能になります。

CEILING関数とFLOOR関数は、Excelで数値を特定の単位の倍数に丸めるための関数です。CEILING関数は数値を指定した単位の倍数に切り上げ、FLOOR関数は数値を指定した単位の倍数に切り捨てます。これらの関数を理解することで、経費計算や在庫管理など、さまざまなビジネスシーンでの端数処理が効率化できます。

CEILING関数とFLOOR関数の基本

CEILING関数とFLOOR関数は、数値計算において、特定の基準値(倍数)に合わせて数値を丸める際に使用します。これらの関数は、単に小数点以下を処理するだけでなく、指定した「単位」に基づいて切り上げや切り捨てを行う点が特徴です。これにより、例えば金額の端数を特定の金額単位(100円、1000円など)で揃えたい場合に、柔軟な対応が可能となります。

CEILING関数は、数値を指定した「重要度」の倍数になるように切り上げます。一方、FLOOR関数は、数値を指定した「重要度」の倍数になるように切り捨てます。この「重要度」が、端数処理の単位となります。例えば、100円単位で切り上げたい場合は、重要度に「100」を指定します。

CEILING関数で単位指定の切り上げを行う方法

CEILING関数は、数値を指定した単位の倍数に切り上げるための関数です。この関数を使うことで、例えば「100円単位で切り上げたい」といった要望に応えることができます。CEILING関数の構文は以下の通りです。

CEILING(数値, 重要度)

ここで、「数値」は丸めたい対象の数値を指定します。そして、「重要度」には、どの単位で切り上げたいかを数値で指定します。例えば、100円単位で切り上げたい場合は、「100」と入力します。

具体的な例を見てみましょう。セルA1に「1234」という数値が入っているとします。この数値を100円単位で切り上げたい場合、別のセルに以下の数式を入力します。

=CEILING(A1, 100)

この数式の結果は「1300」となります。これは、1234を100の倍数になるように切り上げた結果です。もし、セルA1の値が「1200」だった場合、数式の結果は「1200」になります。なぜなら、1200は既に100の倍数であり、切り上げの必要がないからです。

CEILING関数は、正の数だけでなく負の数にも対応しています。しかし、負の数に対する挙動は、正の数とは異なります。負の数の場合、CEILING関数は「0に近い方」へ切り上げます。例えば、-1234を100単位で切り上げる場合、結果は「-1200」となります。

CEILING関数で500円単位の切り上げを行う手順

500円単位で数値を切り上げたい場合も、CEILING関数を使用します。「重要度」に「500」を指定するだけです。

1. 切り上げたい数値を準備する
   例えば、セルA1に「3780」という数値を入力します。
2. CEILING関数を入力する
   結果を表示したいセルに、以下の数式を入力します。
   =CEILING(A1, 500)
3. Enterキーを押す
   数式が適用され、結果が表示されます。

この例では、3780は500の倍数ではないため、次の500の倍数である4000に切り上げられます。もし、セルA1の値が「3500」だった場合、結果は「3500」となります。

CEILING関数で千円単位の切り上げを行う手順

千円単位で切り上げたい場合も同様に、「重要度」に「1000」を指定します。

1. 切り上げたい数値を準備する
   例えば、セルB1に「15600」という数値を入力します。
2. CEILING関数を入力する
   結果を表示したいセルに、以下の数式を入力します。
   =CEILING(B1, 1000)
3. Enterキーを押す
   数式が適用され、結果が表示されます。

この例では、15600は1000の倍数ではないため、次の1000の倍数である16000に切り上げられます。もし、セルB1の値が「15000」だった場合、結果は「15000」となります。

FLOOR関数で単位指定の切り捨てを行う方法

FLOOR関数は、数値を指定した単位の倍数に切り捨てるための関数です。CEILING関数とは逆に、端数を切り捨てて、指定した単位の倍数に合わせたい場合に使用します。構文はCEILING関数と似ています。

FLOOR(数値, 重要度)

「数値」には丸めたい数値を、「重要度」には切り捨てたい単位の数値を指定します。例えば、100円単位で切り捨てたい場合は、「100」と入力します。

具体的な例を見てみましょう。セルC1に「1234」という数値が入っているとします。この数値を100円単位で切り捨てたい場合、別のセルに以下の数式を入力します。

=FLOOR(C1, 100)

この数式の結果は「1200」となります。これは、1234を100の倍数になるように切り捨てた結果です。もし、セルC1の値が「1200」だった場合、数式の結果は「1200」になります。1200は既に100の倍数であり、切り捨ての必要がないからです。

FLOOR関数も、正の数と負の数に対応していますが、負の数に対する挙動はCEILING関数とは異なります。負の数の場合、FLOOR関数は「0から遠い方」へ切り捨てます。例えば、-1234を100単位で切り捨てる場合、結果は「-1300」となります。

FLOOR関数で500円単位の切り捨てを行う手順

500円単位で数値を切り捨てたい場合は、「重要度」に「500」を指定します。

1. 切り捨てたい数値を準備する
   例えば、セルD1に「3780」という数値を入力します。
2. FLOOR関数を入力する
   結果を表示したいセルに、以下の数式を入力します。
   =FLOOR(D1, 500)
3. Enterキーを押す
   数式が適用され、結果が表示されます。

この例では、3780は500の倍数ではないため、直前の500の倍数である3500に切り捨てられます。もし、セルD1の値が「3500」だった場合、結果は「3500」となります。

FLOOR関数で千円単位の切り捨てを行う手順

千円単位で切り捨てたい場合も同様に、「重要度」に「1000」を指定します。

1. 切り捨てたい数値を準備する
   例えば、セルE1に「15600」という数値を入力します。
2. FLOOR関数を入力する
   結果を表示したいセルに、以下の数式を入力します。
   =FLOOR(E1, 1000)
3. Enterキーを押す
   数式が適用され、結果が表示されます。

この例では、15600は1000の倍数ではないため、直前の1000の倍数である15000に切り捨てられます。もし、セルE1の値が「15000」だった場合、結果は「15000」となります。

CEILING.MATH関数とFLOOR.MATH関数

Excelの新しいバージョン(Excel 2010以降)では、CEILING関数とFLOOR関数に加えて、CEILING.MATH関数とFLOOR.MATH関数が提供されています。これらの関数は、特に負の数に対する挙動が、従来の関数とは異なります。どちらの関数も、引数「重要度」の正負によって、切り上げ・切り捨ての方向が決まります。

CEILING.MATH関数の構文は以下の通りです。

CEILING.MATH(数値, [重要度], [モード])

「重要度」は省略可能で、省略した場合は1になります。また、「モード」引数も省略可能ですが、これを指定することで、負の数に対する挙動を制御できます。

一方、FLOOR.MATH関数の構文は以下の通りです。

FLOOR.MATH(数値, [重要度], [モード])

こちらも「重要度」と「モード」は省略可能です。これらの関数は、より直感的で一貫した端数処理を求める場合に有用です。

CEILING.MATH関数とFLOOR.MATH関数の使い分け

CEILING.MATH関数とFLOOR.MATH関数は、主に負の数に対する処理で従来の関数と差が出ます。従来のCEILING関数やFLOOR関数は、負の数に対して「0に近い方」または「0から遠い方」という挙動を示しました。しかし、CEILING.MATH関数とFLOOR.MATH関数では、「重要度」引数の符号によって、絶対値が大きい方向へ丸めるか、絶対値が小さい方向へ丸めるかを明示的に指定できます。

例えば、-1234を100単位で処理する場合を考えます。

• 従来のCEILING(-1234, 100)は -1200 を返します(0に近い方へ切り上げ)。
• CEILING.MATH(-1234, 100) は -1200 を返します(重要度が正のため、0に近い方へ切り上げ)。
• CEILING.MATH(-1234, -100) は -1300 を返します(重要度が負のため、絶対値が大きい方向へ切り上げ)。

同様に、FLOOR関数でも挙動が変わります。

• 従来のFLOOR(-1234, 100) は -1300 を返します(0から遠い方へ切り捨て)。
• FLOOR.MATH(-1234, 100) は -1300 を返します(重要度が正のため、0から遠い方へ切り捨て)。
• FLOOR.MATH(-1234, -100) は -1200 を返します(重要度が負のため、絶対値が小さい方向へ切り捨て)。

このように、CEILING.MATH関数とFLOOR.MATH関数は、「重要度」引数の符号を調整することで、より細かく端数処理の方向を制御できます。特に、負の数を含むデータセットを扱う際には、これらの新しい関数を使用することを推奨します。

CEILING・FLOOR関数とROUND関数・ROUNDUP関数・ROUNDDOWN関数の違い

Excelには、端数処理を行うための関数が複数存在します。CEILING関数とFLOOR関数は、特定の「単位」に基づいて丸めるのに対し、ROUND関数、ROUNDUP関数、ROUNDDOWN関数は、指定した「桁数」に基づいて丸めます。この違いを理解することが、適切な関数を選択する上で重要です。

ROUND関数は、指定した桁数で数値を四捨五入します。例えば、ROUND(123.456, 2) は 123.46 となります。ROUNDUP関数は、指定した桁数で数値を切り上げます。ROUNDUP(123.451, 2) は 123.46 となります。ROUNDDOWN関数は、指定した桁数で数値を切り捨てます。ROUNDDOWN(123.456, 2) は 123.45 となります。

一方、CEILING関数とFLOOR関数は、数値を特定の「倍数」に丸めます。例えば、CEILING(1234, 100) は 1300 となり、FLOOR(1234, 100) は 1200 となります。これは、100という単位で切り上げ・切り捨てを行っているためです。

端数処理の使い分け例

どのような場合にどの関数を使うべきか、具体的な例で考えてみましょう。

• 例1:経費の計算で、100円未満を切り捨てたい場合
  この場合は、FLOOR関数を使用します。例えば、経費が5430円の場合、FLOOR(5430, 100) とすると 5400円 となります。
• 例2:商品の価格設定で、10円単位で切り上げたい場合
  この場合は、CEILING関数を使用します。例えば、原価が198円の商品を10円単位で切り上げたい場合、CEILING(198, 10) とすると 200円 となります。
• 例3:レポートの点数で、小数点第1位を四捨五入したい場合
  この場合は、ROUND関数を使用します。例えば、点数が 85.75点 の場合、ROUND(85.75, 1) とすると 85.8点 となります。
• 例4:数値の丸め方で、常に切り上げて表示したい場合
  この場合は、ROUNDUP関数を使用します。例えば、在庫数 12.3 個 を表示する際に、常に切り上げて 13 個 と表示したい場合、ROUNDUP(12.3, 0) とします。
• 例5:数値の丸め方で、常に切り捨てて表示したい場合
  この場合は、ROUNDDOWN関数を使用します。例えば、割引率 0.789 を表示する際に、常に切り捨てて 0.78 と表示したい場合、ROUNDDOWN(0.789, 2) とします。

このように、処理したい内容に応じて、最適な関数を選択することが重要です。CEILING関数とFLOOR関数は、特定の「単位」での丸めに特化していると理解しておくと良いでしょう。

CEILING・FLOOR関数でよくある失敗とその対処法

CEILING関数とFLOOR関数は非常に便利ですが、いくつか注意すべき点や、よくある失敗例があります。これらの点を理解しておくことで、意図しない結果を避けることができます。

失敗例1:結果が意図した単位にならない

最もよくある失敗は、「重要度」の指定が間違っている場合です。例えば、1000円単位で切り上げたいのに、誤って「100」と指定してしまうと、100円単位での切り上げになってしまいます。

対処法: 数式を確認し、「重要度」の引数が目的とする単位(100円なら100、1000円なら1000)になっているかを再度確認してください。また、ExcelのバージョンによってはCEILING.MATH関数やFLOOR.MATH関数の方が、より直感的な挙動を示す場合があります。必要に応じてこれらの関数への切り替えも検討してください。

失敗例2:負の数で挙動がおかしくなる

前述の通り、CEILING関数とFLOOR関数は、負の数に対する挙動が直感的でない場合があります。特に、従来の関数では、負の数に対する切り上げ・切り捨ての方向が「0に近い方」または「0から遠い方」という解釈になるため、期待する結果と異なることがあります。

対処法: 負の数を扱う場合は、Excel 2010以降で利用可能なCEILING.MATH関数やFLOOR.MATH関数を使用することを強く推奨します。これらの関数は、「重要度」引数の符号を調整することで、より明確に切り上げ・切り下げの方向を指定できます。例えば、負の数を絶対値が大きい方向に丸めたい場合は、「重要度」に負の値を指定します。

失敗例3:小数点の単位で処理したい場合

CEILING関数やFLOOR関数は、整数単位での丸めに適していますが、小数点以下の単位で丸めたい場合にも応用できます。しかし、その場合も「重要度」の指定方法に注意が必要です。

例えば、1.234という数値を小数点第2位で切り上げたい場合、CEILING関数で「重要度」に0.01を指定します。数式は `=CEILING(1.234, 0.01)` となります。結果は1.24です。同様に、小数点第2位で切り捨てたい場合は、FLOOR関数で `=FLOOR(1.234, 0.01)` と入力し、結果は1.23となります。

対処法: 小数点以下の単位で丸めたい場合は、「重要度」にその単位を表す小数(例: 0.1, 0.01, 0.001)を指定してください。また、これらの場合もCEILING.MATH関数やFLOOR.MATH関数の方が、より正確に意図した結果を得られることがあります。

まとめ

本記事では、ExcelのCEILING関数とFLOOR関数を用いて、指定した単位で数値を切り上げる・切り捨てる方法を解説しました。CEILING関数は数値を指定した単位の倍数に切り上げ、FLOOR関数は指定した単位の倍数に切り捨てます。これらの関数を使いこなすことで、経費計算や価格設定など、ビジネスシーンでの端数処理の幅が広がります。特に、負の数を扱う場合は、Excel 2010以降で利用可能なCEILING.MATH関数やFLOOR.MATH関数が、より直感的で制御しやすい挙動を示します。これらの関数を理解し、実際の業務で活用することで、データ処理の効率と精度を高めることができます。ROUND関数などの他の端数処理関数との違いも理解し、目的に応じて使い分けることが重要です。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】