Excelデータの端数処理

ビルトイン Excel 丸めツールは、計算結果が正しくないように見えることがあります。これは Excel が各セル値を個別に考慮するためです。think-cellExcel データの端数処理機能は計算を全体的に考慮し、正確な値からの逸脱を最小限に抑えつつ、丸めた値を使用して計算を適正に維持できるような方法で丸めを行います (数学的に可能な場合)。

think-cell 丸めの概要

レポートまたは PowerPoint のプレゼンテーションでデータをコンパイルすると、Excelで総和を端数処理する際、頻繁に問題が生じます。丸めた各数値の合計が、丸めた総計と正確に一致することが期待されますが、通常は困難です。例えば、以下の表を考えてみてください。

Excel のセル書式設定を使用して値を整数に丸めると、次の表のようになります。「計算が誤っている」と思われる合計は太字で表示されています。

同様に、Excel の標準端数処理機能を使用すると、丸め後の値の合計は正しく計算されますが、丸めエラーが累積され、結果は元の値を実際に合計した数値から大幅にずれてしまうことがよくあります。次の表は、上記の例の=ROUND(x,0)の結果を示しています。元の値から 1 以上ずれた合計は太字で表示しています。

think-cell 丸めを使用すると、毎回「誤差」を最小限に抑えて端数処理された合計値を出すことができます。ほとんどの値は最も近い整数に丸められますが、いくつかの値は反対方向に丸められるので、誤差を累積することなく正確な計算が維持されます。値を変更することによって適切な丸め合計を計算できる様々な方法があるため、このソフトウェアは、数値の変更を最小限にしながら、正確な値からの逸脱を最小にするソリューションを採用しています。例えば、10.5 を丸めて 10 にすることは、3.7 を 3 に丸めるよりも望ましい方法です。以下の表には、上記の例に対する最適なソリューションが示されています。「誤差が生じている」値は太字で示されています。

独自の計算でこの算出を行うには、関連する Excel セルの範囲を選択してください。次に、Formulasタブの ボタンをクリックし、必要に応じてツールバーのドロップダウンボックスを使用して端数処理する桁数を調整します。

think-cell 丸めを使用

think-cell 丸めは Microsoft Excel にシームレスに統合され、Excel の標準端数処理機能と類似した一連の関数を提供します。Formulas タブにある think-cell 丸め リボングループを使用して、これらの関数を自分のデータに簡単に適用できます。

丸めパラメータ

Excel 関数と同様に、think-cellの丸め関数は 2 つのパラメータをとります。

think-cell 丸めは整数値だけではなく任意の倍数にも端数処理できます。例えば、データを 5-10-15 -... というステップで表す場合は、単純に 5 の倍数に丸めます。think-cell 丸めツール バーのドロップ ダウンボックスを使用して、必要な丸め桁数を入力するか、選択します。 think-cell の丸めは、ユーザーに合った適切な関数とパラメーターを選択します。次の表には、ツール バーとその特定の n パラメーターを使用して行う値 x の端数処理の例が示されています。

値が期待どおりに表示されない場合は、Excel セルの書式設定がGeneralに設定されていることと、列に小数点以下の値をすべて表示できる幅があることを確認します。

基礎となる値からの逸脱が可能な限り少ない最適な値を得るには、できるだけ TCROUND を使用すべきです。必要な場合にだけ、より制限的な機能 TCROUNDDOWN、TCROUNDUP、TCROUNDNEAR を使用してください。

注意：RAND()の式の中では、 TCROUNDのような不明確な関数は使わないようご注意ください。関数が検証のたびに異なる値を返す場合、think-cell丸めは値を誤って計算します。

計算のレイアウト

上記の四角形のレイアウト例は、デモンストレーション用です。TCROUND 関数を使用して、Excel シート全体に分散する任意の合計の表示を決定できます。Excel の他のシートへの 3 次元参照や他のファイルへのリンクも機能します。

TCROUND 関数の配置

TCROUND関数はセルの出力を制御するものであり、最も外側の関数でなければなりません。

失敗例に似た入力をした場合は、think-cell 丸めにより、Excel のエラー値#VALUE!でその旨が通知されます。

think-cell 丸めの限界

think-cell丸めでは、小計と合計を使って任意の合計のソリューションを常に見つけます。think-cell丸めはまた、乗算および数値関数を含む他の計算に対する合理的なソリューションを提供します。しかし、数学的な理由から、+、 - 、および SUM 以外の演算子が使用された際には、一貫した方法で丸めるソリューションを保証することはできません。

定数を使った乗算

多くの場合、think-cell 丸めは、定数の乗算が関係するとき、つまり係数の多くとも 1 つが別の TCROUND 関数の結果から生じているときに良好な結果を生じる。次の例を参照してください。

C1 セルの正確な計算は、3×1.3+1.4=5.3 です。この結果は、1.4 から 2 に値を切り上げることで出すことができます。

しかし、think-cell 丸めは、切り上げまたは切り捨てによってのみ「誤差を処理」できます。元の数値からのこれ以上のかい離はサポートされていません。したがって、入力値の特定の組み合わせに対して、一貫した方法を使った丸めのソリューションはありません。この場合、TCROUND 関数は、Excel エラー値 #NUM! として評価されます。次の事例は解決できない問題を示しています。

C1 セルの正確な計算は、6×1.3+1.4=9.2 です。セル A1 および B1 を丸めると、6×1+2=8または 6×2+1=13 となります。実際の結果 9.2 は、8 または 13 に丸めることはできません。そして、think-cell 丸めの出力は次のようになります。

注記：Excel 関数AVERAGEは、総計と定数の乗算の組み合わせとして think-cell 丸めに解釈されます。また、同じ加数が複数回現れる総和は、定数の乗算と数学的に等価であり、ソリューションが存在するかどうかは保証されません。

一般乗算とその他の関数

すべての関連するセルに TCROUND 関数が使用され、中間結果が+、 - 、SUM および AVERAGE によってのみ接続されている限り、加数および (中間) 合計は、1 つの丸め問題として扱われます。こうしたケースでは、think-cell 丸めは、そのようなソリューションが存在する場合は、関連するすべてのセル全体にわたって一貫性を提供するソリューションを見つけます。

TCROUNDは通常の Excel 関数なので、任意の関数や演算子と組み合わせることができます。しかし、上述の関数以外の関数を使用してTCROUNDステートメントからの結果を接続すると、think-cell 丸めはこのコンポーネントを 1 つの相互に接続された問題へと統合することはできません。その代わりに、数式のコンポーネントは、個別に解決される別個の問題として扱われます。結果は、他の数式への入力値として使用されます。

多くの場合、think-cell 丸めの出力値が合理的であることに変わりはありません。ただし、+、 - 、SUM および AVERAGE 以外の演算子を使用すると、丸めなしの計算の結果とは大きく異なる計算結果が出てしまいます。次の例を参照してください。

この場合、C1 セルの正確な計算は、8.7×1.7=14.79 となります。セル A1 とセル B1 は乗算によって接続されているため、think-cell 丸めではこれらのセルの数式を共通の問題へと統合できません。その代わりに、セル A1 を有効な入力と検知した後、セル B1 は独立して検証され、出力は残りの問題内で定数として扱われます。それ以上の制約はないため、セル B1 からの値 1.7 は最も近い整数 2 に丸められます。

この時点で、C1 セルの「正確な」計算は、8.7×2=17.4 となります。これが、think-cell 丸めが解決しようとしている問題です。17.4 を 18 に端数処理する必要がある一貫したソリューションがあります。結果はこのようになります。

セル C1 の端数処理される値は 18 であり、元の値 14.79 と大きく異なる点にご注意ください。

TCROUND 数式のトラブルシューティング

think-cell丸めを使用すると、#VALUE! と #NUM! という、2 つのエラーが発生する可能性があります。

##VALUE!

#VALUE! エラーは、誤って入力された数式や誤ったパラメータなどの構文上の問題があることを示しています。また、正しい区切り記号を使用するように注意してください。例えば、英語版の Excel では数式は=TCROUND(1.7, 0)のようになりますが、ローカライズされたドイツ語版の Excel では=TCROUND(1,7; 0)として書き込む必要があります。

think-cell 丸めに特有の他の間違いには、TCROUND 関数呼び出しの配置があります。他の数式内で TCROUND 関数を使用することはできません。TCROUNDがセルの数式の最も外側の関数であることを確認してください（TCROUND 関数の配置を参照）。

##NUM!

#NUM!エラーは数値の問題から発生します。TCROUND関数の出力が#NUM!の場合、与えられた数式によって示される問題が数学的に解決できないことを意味しています（think-cell 丸めの限界を参照）。

TCROUND関数で囲まれた数式が+、 - 、SUMだけを含み、すべてのTCROUNDステートメントが同じ桁数 (第 2 パラメーター) を共有している限り、ソリューションの存在は確実であり、think-cell 丸めがそのソリューションを探し当てます。ただし、以下の場合、一貫した方法で丸められたソリューションが存在するという保証はありません。

• 数式には、乗算や数値関数などの演算が含まれます また、同じ加数が複数回現れる総和は乗算と数学的に等価です。
• TCROUND関数の 2 番目のパラメーターで異なる桁数を使用します。
• 特定の関数 TCROUNDDOWN、TCROUNDUP、TCROUNDNEAR を頻繁に使用します。

一貫したソリューションを得るため、問題を再現してみることができます。次を試してください。

• TCROUND ステートメントの一部または全部で、より高い精度を使用します。
• +、 - 、TCROUND 以外の乗算や数値関数で SUM を使用しないでください。
• すべての TCROUND ステートメントに同じ桁数 (2 番目のパラメーター) を使用します。
• 可能であれば、より具体性のある関数 TCROUND、TCROUNDDOWN、TCROUNDUP の代わりに TCROUNDNEAR を使用してください。