Excelで数値を丸めるための数式

多くの場合、顧客はクリーンで丸い数字を見たいと思っています。精度を上げるためにユーザーに10進値と不要な数字を氾濫させると、実際にはレポートが読みにくくなる可能性があります。このため、Excelの丸め関数の使用を検討することをお勧めします。

この図は、ROUND、ROUNDUP、およびROUNDDOWN関数の使用によって数値9.45がどのように影響を受けるかを示しています。

ExcelのROUND関数は、指定された数値を指定された桁数に丸めるために使用されます。ROUND関数は、元の値と丸める桁数の2つの引数を取ります。

2番目の引数として0を入力すると、Excelはすべての小数点以下の桁数を削除し、最初の小数点以下の桁数に基づいて数値の整数部分を丸めるように指示します。たとえば、この式は94に丸められます。

= ROUND（94.45,0）

2番目の引数として1を入力すると、Excelは小数点以下第2位の値に基づいて小数点以下1桁に四捨五入します。たとえば、次の式は94.5に丸められます。

= ROUND（94.45,1）

2番目の引数として負の数を入力して、小数点の左側の値に基づいて丸めるようにExcelに指示することもできます。たとえば、次の数式は90を返します。

= ROUND（94.45、-1）

ROUNDUPまたはROUNDDOWN関数を使用して、特定の方向に強制的に丸めることができます。

このROUNDDOWN式は、94.45を94に切り捨てます。

= ROUNDDOWN（94.45,0）

このROUNDUP式は、94.45を95に丸めます。

= ROUNDUP（94.45,0）

Excelで最も近いペニーに丸める

一部の業界では、1ドルの金額を最も近いペニーに四捨五入するのが一般的な方法です。この図は、ドルの金額を最も近いペニーに切り上げるか切り下げると、結果の数値にどのように影響するかを示しています。

CEILINGまたはFLOOR関数を使用して、最も近いペニーに丸めることができます。

CEILING関数は、渡された重要度の最も近い倍数に数値を切り上げます。このユーティリティは、標準の丸めプロトコルを独自のビジネスルールでオーバーライドする必要がある場合に便利です。たとえば、有意差1のCEILING関数を使用して、Excelで123.222を124に丸めることができます。

= CEILING（123.222,1）

したがって、重要度として.01を入力すると、CEILING関数は最も近いペニーに丸めるように指示されます。

最も近いニッケルに丸めたい場合は、.05を重要度として使用できます。たとえば、次の数式は123.15を返します。

= CEILING（123.11、.05）

FLOOR関数は、丸め強制することを除いて同じように動作しますダウン最寄りの重要性。次の関数例は、123.19を最も近いニッケルに切り捨て、結果として123.15を返します。

= FLOOR（123.19、.05）

Excelで有効数字に丸める

一部の財務報告では、数値は有効数字で表示されます。数百万の数値を扱う場合、数十、数百、数千の場所までの精度を示すために、レポートに余分な数値を氾濫させる必要がないという考え方です。

たとえば、883,788という数値を表示する代わりに、数値を有効数字1桁に丸めることを選択できます。これは、900,000と同じ数を表示することを意味します。883,788を有効数字2桁に四捨五入すると、数値は880,000と表示されます。

本質的に、あなたは特定の番号の場所が示すのに十分重要であるとみなしています。残りの数値はゼロに置き換えることができます。これを行うと問題が発生する可能性があるように感じるかもしれませんが、十分な数を処理している場合、特定の重要度を下回る数値は重要ではありません。

次の図は、数値を有効桁数に丸める数式を実装する方法を示しています。

ExcelのROUND関数を使用して、指定された数値を指定された桁数に丸めます。ROUND関数は、元の値と丸める桁数の2つの引数を取ります。

2番目の引数として負の数を入力すると、Excelは小数点の左側の有効数字に基づいて四捨五入します。たとえば、次の数式は9500を返します。

= ROUND（9489、-2）

有効数字の引数を-3に変更すると、9000の値が返されます。

= ROUND（B14、-3）

これはうまく機能しますが、異なるスケールの数値がある場合はどうなりますか？つまり、あなたの数のいくつかが数百万であり、他の数が数十万である場合はどうなりますか？それらすべてを有効数字1桁で表示する場合は、数値のタイプごとに必要となる有効数字の引数の違いを考慮して、数値ごとに異なるROUND関数を作成する必要があります。

この問題を解決するために、ハードコードされた有効数字の引数を、その数値を計算する数式に置き換えることができます。

あなたの番号が-2330.45であると想像してください。この式は、ROUND関数の有効数字引数として使用できます。

LEN（INT（ABS（-2330.45）））*-1 + 2

この数式は、最初に数値をABS関数でラップし、存在する可能性のある負の記号を効果的に削除します。次に、その結​​果をINT関数でラップし、存在する可能性のある小数をすべて取り除きます。次に、その結果をLEN関数でラップして、小数や否定記号なしで数値に含まれる文字数の測定値を取得します。

この例では、数式のこの部分の結果は数値4になります。数値–2330.45を取り、小数と負の記号を取り除くと、4文字が残ります。

次に、この数値に-1を掛けて負の数値にし、探している有効桁数に加算します。この例では、その計算は次のようになります：4 * –1 + 2 = –2。

この場合も、この式はROUND関数の2番目の引数として使用されます。この数式をExcelに入力し、数値を2300（有効数字2桁）に丸めます。

= ROUND（-2330.45、LEN（INT（ABS（-2330.45）））*-1 + 2）

次に、この数式を、ソース番号を指すセル参照と、必要な有効桁数を保持するセルに置き換えることができます。

= ROUND（B5、LEN（INT（ABS（B5）））*-1 + $ E $ 3）