20. Excel データの端数処理

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

Excel での正確な数値の例

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

Excel のセル書式設定を使用した丸め

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

Excel 関数 ROUND の使用例

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

think-cell 丸めの例

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

20.1
think-cell 丸めを使う
20.2
think-cell 丸めの限界
20.3
TCROUND 数式の問題解決

20.1 think-cell 丸めを使う

think-cell 丸めは Microsoft Excel にシームレスに統合され、Excel の標準丸め関数に似た一連の関数を提供します。think-cell 丸めツール バー ボタンを使用して、これらの関数を自分のデータに簡単に適用できます。

Excel の think-cell 丸めツール バー

Excel 2007 以降では、ツール バーに代わってリボンが提供されています。think-cell 丸めリボンは、数式タブにあります。

Excel 2007 以降での think-cell 丸めリボン

20.1.1 丸めパラメーター

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

x
丸められる値。これは、定数、数式、または別のセルへの参照です。
n
丸め桁数。このパラメーターの意味は、使用する関数によって異なります。think-cell 関数のパラメーターは、相当する Excel 関数のパラメーターと同じです。次の表の例を参照してください。

Excel と同じように、think-cell 丸めは整数値だけではなく任意の倍数に丸めることができます。例えば、データを 5-10-15 -... というステップで表す場合は、単純に 5 の倍数に丸めます。

Excel では、必要な丸め桁数に応じて、異なる関数を使用する必要があります。この例はROUNDMROUNDの関数で、パラメーターnを違う方法で解釈します。think-cell 丸めツール バーのドロップ ダウンボックスを使用して、必要な丸め桁数を入力するか、選択します。 think-cell の丸めは、ユーザーに合った適切な関数とパラメーターを選択します。

次の表は、ツール バーROUNDまたはMROUNDとその特定のnパラメーターを使用して行う丸めの例を示しています。

ツール バー 100 50 2 1 0.01
ROUND -2 - - 0 2
MROUND 100 50 2 1 -
1.018 0 0 2 1 1.02
17 0 0 18 17 17.00
54.6 100 50 55 54 54.60
1234.1234 1200 1250 1234 1234 1234.12
8776.54321 8800 8800 8776 8777 8776.54

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

ボタン 数式 記述
image TCROUND(x, n) think-cell 丸めでは、丸め誤差を最小限に抑えるために、2 つの最も近い倍数のどちらに丸めるかを決定します。
image TCROUNDUP(x, n) x を 0 から離れる方向に丸めます。
image TCROUNDDOWN(x, n) x を 0 に近づく方向に丸めます。
image TCROUNDNEAR(x, n) x を必要な桁数の最も近い倍数に丸めます。
image 選択したセルから think-cell 丸め関数をすべて削除します。
image
必要な丸め倍数を選択または入力します。
image think-cellが、2 つの最も近い倍数の近い方ではなく、遠い方に丸めることを決定したすべてのセルをハイライトします。
image ホイールの回転は、think-cell 丸めがビジーであることを示しています。

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

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

20.1.2 計算のレイアウト

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

20.1.3 TCROUND 関数の配置

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

失敗: =TCROUND(A1, 1)+TCROUND( SUM(B1:E1), 1 )
成功: =TCROUND( A1+SUM(B1:E1), 1 )
 
失敗: =3*TCROUNDDOWN(A1, 1)
成功: =TCROUNDDOWN(3*A1, 1)

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

20.2 think-cell 丸めの限界

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

20.2.1 定数を使った乗算

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

think-cell 丸めでの定数の乗算

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

think-cell 丸めを使った丸め事例 (TCROUND)

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

think-cell 丸めでの一貫性のない丸め

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

#NUM! think-cell 丸めエラー

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

20.2.2 一般乗算とその他の関数

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

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

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

誤った数式の使用による丸めの影響

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

この時点で、C1 セルの ”正確な” 計算は、8.6 × 2 = 17.2 です。これが、think-cell 丸めが解決しようとしている問題です。一貫したソリューションがありますが、17.2 から 18 への切り上げが必要です。結果はこのようになります。

think-cell 丸めを使用した丸めと乗算

セル C1 の丸められた値は 18 であり、元の値 14.62 と大きく異なることにご注意ください。

20.3 TCROUND 数式の問題解決

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

20.3.1 #VALUE!

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

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

20.3.2 #NUM!

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

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

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

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

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