22. Excelデータの端数処理

  2. リソース
  3. マニュアル
  4. 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. [数式]タブのボタンをクリックし、必要に応じてツール バーのドロップダウン ボックスを使用して端数処理する桁数を調整します。

22.1
think-cell 丸めを使用する方法
22.2
think-cell 丸めの限界
22.3
TCROUND数式の問題解決策

22.1 think-cell 丸めを使用する方法

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

Excel 2010 以降の場合のリボン [think-cell round] (think-cell 丸め).

22.1.1 端数処理パラメーター

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

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

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

x =
n =
 100 50 2 1 0.01
1.018 0 0 2 1 1.02
17 0 0 18 17 17.00
54.6 100 50 54 55 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 を使用すべきです。必要な場合にだけ、より制限的な機能 TCROUNDDOWN、TCROUNDUP、TCROUNDNEAR を使用してください。

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

22.1.2 計算のレイアウト

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

22.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!でその旨が通知されます。

22.2 think-cell 丸めの限界

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

22.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 丸めに解釈されます。また、同じ加数が複数回現れる総和は、定数の乗算と数学的に等価であり、ソリューションが存在するかどうかは保証されません。

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

すべての関連するセルに 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 に端数処理する必要がある一貫したソリューションがあります。結果はこのようになります。

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

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

22.3 TCROUND 数式の問題解決策

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

22.3.1 #VALUE!

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

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

22.3.2 #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 を使用してください。

まだユーザーではありませんか？

無料ではじめる すべての機能を表示

共有する