22. Excel-Datenrundung

Werden in Excel Daten für einen Bericht oder eine PowerPoint-Präsentation zusammengestellt, ist das Runden von Summierungen ein häufiges Problem. Meistens ist es wünschenswert, dass die gerundete Summe mit der Summe der gerundeten Summanden übereinstimmt. In der Praxis ist das aber nicht immer leicht zu erreichen. Nehmen wir zum Beispiel die Ausgangsdaten in folgender Tabelle:

Wenn Sie die Zellenformatierungsfunktion von Excel verwenden, um die Daten als ganze Zahlen darzustellen, ergibt sich folgende Tabelle. Summen, die „falsch“ erscheinen, sind fett dargestellt:

Ähnlich werden bei Verwendung der Standard-Rundungsfunktionen von Excel zwar die Summen der gerundeten Werte korrekt berechnet, doch die Rundungsfehler summieren sich, sodass die Ergebnisse oft erheblich von der tatsächlichen Summe der Ursprungswerte abweichen. Die folgende Tabelle zeigt das Ergebnis der Funktion =ROUND(x,0), angewandt auf die oben abgebildeten Beispieldaten. Summen, die von ihrem Ausgangswert um 1 oder mehr abweichen, werden fett angezeigt:

Mithilfe von think-cell round können Sie mit minimalen Anpassungen konsistent gerundete Gesamtsummen erreichen. Während die meisten Werte zur nächstgelegenen ganzen Zahl gerundet werden, wird bei einigen eine Rundung in die andere Richtung vorgenommen. Auf diese Weise wird die Korrektheit der Berechnung sichergestellt, ohne dass sich Rundungsfehler summieren. Da es verschiedene Wege gibt, durch Ändern von Werten eine korrekt gerundete Summe zu erhalten, verwendet die Software eine Lösung, bei der möglichst wenige Werte geändert werden und sich eine möglichst geringe Abweichung von den tatsächlichen Werten ergibt. Zum Beispiel wird eher 10,5 auf 10 gerundet als 3,7 auf 3. In der folgenden Tabelle wird eine optimale Lösung für das obige Beispiel gezeigt. Werte, bei denen „geschummelt“ wurde, sind fett dargestellt:

Um diese Funktion auf Ihre eigenen Daten anzuwenden, wählen Sie den betreffenden Bereich in Ihrem Excel-Datenblatt aus. Dann klicken Sie auf der Registerkarte Formeln auf die Schaltfläche und passen Sie gegebenenfalls die Rundenpräzision mithilfe des Dropdownfelds der Symbolleiste an.

22.1

think-cell round verwenden

22.2

Beschränkungen von think-cell round

22.3

Problembehebung in TCROUND-Formeln

22.1 Verwendung von think-cell round

think-cell round integriert sich nahtlos in Microsoft Excel und bietet eine Reihe von Funktionen, die den Standard-Rundungsfunktionen von Excel ähneln. Sie können diese Funktionen ganz einfach mithilfe der think-cell round-Menübandgruppe auf der Registerkarte Formeln auf Ihre Daten anwenden.

22.1.1 Rundungsparameter

Genau wie die Excel-Funktionen benötigen auch die Funktionen von think-cell round zwei Parameter:

x

Der Wert, der gerundet werden soll. Sie können hier eine Zahl, eine Formel oder einen Verweis auf eine andere Zelle angeben.

n

Die Präzision, auf die gerundet werden soll. Die Bedeutung dieses Parameters hängt von der Funktion ab, die Sie verwenden. Die Parameter für die think-cell Funktionen entsprechen denen für die jeweiligen Excel-Funktionen. Beispiele finden Sie in der folgenden Tabelle.

think-cell round kann nicht nur auf Ganzzahlen, sondern auch auf Vielfache runden. Beispielsweise können Sie auf Vielfache von Fünf runden, um Ihre Daten in den Schritten 5-10-15-... darzustellen. Wählen Sie im Dropdown-Feld in der think-cell round-Symbolleiste die gewünschte Präzision aus, auf die gerundet werden soll, oder geben Sie die gewünschte Präzision einfach ein. Daraufhin wählt think-cell round automatisch die passende Funktion und geeignete Parameter für Sie aus. Die folgende Tabelle enthält einige Beispiele für die Rundung bestimmter X-Werte mit der Symbolleiste, oder gemeinsam mit dem spezifischen n-Parameter.

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

Wenn die Ergebnisse der Rundung nicht Ihren Erwartungen entsprechen, stellen Sie sicher, dass unter „Zellen formatieren...“ in Excel das Format Standard eingestellt ist und die Spalten breit genug sind, um alle Nachkommastellen anzuzeigen.

Schaltfläche	Formel	Beschreibung
	TCROUND(x, n)	think-cell round entscheidet, auf welches der beiden nächstgelegenen Vielfachen gerundet wird, um den Rundungsfehler zu minimieren.
	TCROUNDUP(x, n)	Erzwungenes Aufrunden von x (von 0 weg).
	TCROUNDDOWN(x, n)	Erzwungenes Abrunden von x (in Richtung 0).
	TCROUNDNEAR(x, n)	Erzwungenes Runden von x zum nächsten Vielfachen der gewünschten Präzision.
		Entfernt alle think-cell round-Funktionen aus den ausgewählten Zellen.
		Geben Sie die gewünschte Rundungspräzision an.
		Markiert alle Zellen, in denen think-cell nicht zum nächsten, sondern zum weiter entfernten der beiden nächstgelegenen Vielfachen gerundet hat.
		Das drehende Rad zeigt an, dass think-cell round beschäftigt ist.

Um optimale Ergebnisse mit geringstmöglichen Abweichungen von den zugrunde liegenden Werten zu erreichen, sollten Sie TCROUND verwenden, wo immer dies möglich ist. Die restriktiveren Funktionen TCROUNDDOWN, TCROUNDUP und TCROUNDNEAR sollten nur eingesetzt werden, wenn dies unbedingt erforderlich ist.

Achtung: Innerhalb der RAND()-Formeln dürfen Sie keine nicht-deterministischen Funktionen wie TCROUND verwenden. Wenn die verwendete Funktion bei jedem Aufruf einen anderen Wert zurückgibt, macht think-cell round bei der Berechnung der gerundeten Werte Fehler.

22.1.2 Darstellung der Berechnung

Die rechteckige Anordnung in diesem Beispiel dient lediglich dem besseren Verständnis. Mithilfe der TCROUND-Funktionen können Sie die Anzeige beliebiger Summen in Ihrer Excel-Tabelle festlegen. Auch die 3D-Referenzen auf andere Datenblätter und Verknüpfungen auf andere Dateien von Excel funktionieren.

22.1.3 Platzieren von TCROUND-Funktionen

Da TCROUND-Funktionen das Ergebnis einer Zelle festlegen sollen, müssen diese Funktion ganz außen stehen:

Schlecht:	=TCROUND(A1, 1)+TCROUND(SUM(B1:E1), 1)
Gut:	=TCROUND(A1+SUM(B1:E1), 1)
Schlecht:	=3*TCROUNDDOWN(A1, 1)
Gut:	=TCROUNDDOWN(3*A1, 1)

Wenn Sie eine Formel so eingeben, wie sie in den falschen Beispielen dargestellt ist, zeigt think-cell round den Excel-Fehlerwert an #VALUE!.

22.2 Beschränkungen von think-cell round

think-cell round findet stets eine Lösung für beliebige Summen mit Teilsummen und Gesamtsummen. Zusätzlich bietet think-cell round auch sinnvolle Lösungen für einige andere Berechnungen mit Multiplikationen und numerischen Funktionen. Allerdings kann aus mathematischen Gründen die Existenz einer konsistent gerundeten Lösung nicht garantiert werden, wenn außer +, - und SUM noch weitere Operatoren benutzt werden.

22.2.1 Multiplikation mit einer Konstante

In vielen Fällen erzeugt think-cell round gute Ergebnisse, wenn mit einer Konstante multipliziert wird, d. h., wenn mindestens einer der Faktoren aus dem Ergebnis einer anderen TCROUND-Funktion abgeleitet ist. Betrachten wir das folgende Beispiel:

Die genaue Berechnung für die Zelle C1 ist 3x1,3+1,4=5,3. Dieses Ergebnis kann erreicht werden, indem der Wert 1,4 auf 2 gerundet wird.

Allerdings kann think-cell round nur durch Auf- oder Abrunden „schummeln“. Eine weitere Abweichung von den ursprünglichen Werten wird nicht unterstützt. So kann für gewisse Kombinationen von Eingabewerten keine vollständig konsistente Lösung gefunden werden. In diesem Fall erzeugt die TCROUND-Funktion den Excel-Fehlerwert #NUM!. Das folgende Beispiel illustriert ein unlösbares Problem:

Die genaue Berechnung für die Zelle C1 ist 6x1,3+1,4=9,2. Wenn die Zellen A1 und B1 gerundet werden, ergibt sich 6x1+2=8 oder 6x2+1=13. Das genaue Ergebnis 9,2 kann weder auf 8 noch auf 13 gerundet werden kann. think-cell round gibt folgendes Ergebnis aus:

Hinweis: Die Excel-Funktion AVERAGE wird von think-cell round als eine Kombination von Summenbildung und Multiplikation mit Konstanten interpretiert. Außerdem entspricht eine Summe, bei der derselbe Summand mehrmals auftritt, einer Multiplikation mit einer Konstante, und die Existenz einer Lösung ist nicht garantiert.

22.2.2 Allgemeine Multiplikation und andere Funktionen

Solange die TCROUND-Funktion für alle relevanten Zellen verwendet wird und Zwischenergebnisse ausschließlich durch +, -, SUM und AVERAGE verbunden sind, werden alle Summanden und (Zwischen-)Summen als ein gemeinsames Rundungsproblem betrachtet. Existiert in diesen Fällen eine konsistente Lösung, wird think-cell round diese finden und alle Zellen optimal runden.

Da TCROUND eine normale Excel-Funktion ist, kann sie mit beliebigen anderen Funktionen und Operatoren kombiniert werden. Wenn Sie allerdings andere als die oben erwähnten Funktionen verwenden, um Ergebnisse aus der TCROUND-Funktion miteinander zu verknüpfen, kann think-cell round nicht mehr alle Komponenten in ein gemeinsames Problem integrieren. Vielmehr werden in diesem Fall die Bestandteile der Formel als einzelne Probleme betrachtet und unabhängig voneinander gelöst. Die Ergebnisse werden dann als Eingabe für andere Formeln verwendet.

In vielen Fällen führt die Verwendung von think-cell round trotzdem zu brauchbaren Ergebnissen. Es gibt jedoch Fälle, in denen die Anwendung von anderen Operatoren als +, -, SUM und AVERAGE zu gerundeten Ergebnissen führt, die vom Ergebnis der nicht gerundeten Berechnung erheblich abweichen. Betrachten wir das folgende Beispiel:

In diesem Fall wäre die genaue Berechnung für die Zelle C1 8,7×1,7=14,79. Da die Zellen A1 und B1 durch eine Multiplikation miteinander verbunden sind, kann think-cell round die Formeln von diesen Zellen nicht in ein gemeinsames Problem integrieren. Stattdessen wird die Zelle B unabhängig evaluiert, nachdem die Zelle A1 als gültige Eingabe erkannt wurde, und das Ergebnis wird als Konstante mit dem verbleibenden Problem verwendet. Da keine weiteren Einschränkungen vorliegen, wird der Wert 1,7 in Zelle B1 zur nächsten Ganzzahl gerundet, also auf 2.

Die „exakte“ Berechnung für die Zelle C1 wäre demnach 8,7×2=17,4. Dies ist das Problem, welches think-cell round nun zu lösen versucht. Es existiert eine konsistente Lösung, welche das Aufrunden von 17,4 auf 18 erfordert. Das Ergebnis sieht wie folgt aus:

Wie Sie sehen, weicht der gerundete Wert 18 in Zelle C1 erheblich vom ursprünglichen Wert 14,79 ab.

22.3 Problembehebung in TCROUND-Formeln

Wenn Sie think-cell round verwenden, können Ihnen zwei mögliche Fehlerwerte begegnen: #VALUE! und #NUM!.

22.3.1 #WERT!

Der #VALUE!-Fehler weist auf syntaktische Probleme wie Tippfehler in Formeln oder falsche Parameter hin. Achten Sie außerdem auch auf die Verwendung der richtigen Trennzeichen. Betrachten wir zum Beispiel eine Formel, die in der internationalen Excel-Version folgendermaßen aussieht: =TCROUND(1.7, 0). In der deutschen Version von Excel muss diese Formel so notiert werden =TCROUND(1,7; 0).

Ein weiterer typischer think-cell round-Fehler besteht in der falschen Platzierung des TCROUND-Funktionsaufrufs: Die TCROUND-Funktion kann nicht innerhalb einer anderen Formel verwendet werden. Achten Sie darauf, dass TCROUND immer die äußerste Funktion einer Formel in einer Zelle ist (siehe Platzieren von TCROUND-Funktionen).

22.3.2 #NUM!

Der #NUM!-Fehler ist eine Folge numerischer Probleme. Wenn TCROUND das Ergebnis einer #NUM!-Funktion ist, ist die mathematische Problemstellung mit den verwendeten Funktionen nicht lösbar (siehe Beschränkungen von think-cell round).

Solange die von TCROUND zu rundenden Formeln nur +, - und SUM enthalten und alle TCROUND-Funktionen auf dieselbe Genauigkeit (zweiter Parameter) runden, ist die Existenz einer Lösung garantiert und wird von think-cell round gefunden. In den folgenden Fällen ist hingegen nicht garantiert, dass eine konsistent gerundete Lösung existiert:

• Formeln verwenden andere Operationen wie Multiplikation oder numerische Funktionen. Zudem sind Summen, bei denen derselbe Summand mehr als einmal auftritt, einer Multiplikation mathematisch gleichwertig sind.
• Sie verwenden unterschiedliche Genauigkeiten im zweiten Parameter der TCROUND-Funktion.
• Sie machen häufig Gebrauch von den spezifischen Funktionen TCROUNDDOWN, TCROUNDUP und TCROUNDNEAR.

Sie können versuchen, das Problem anders zu formulieren, um eine konsistente Lösung zu finden. Versuchen Sie Folgendes:

• Verwenden Sie eine höhere Genauigkeit für einige oder alle TCROUND-Funktionen.
• Verwenden Sie die TCROUND-Funktionen nicht in Kombination mit Multiplikationen oder anderen numerischen Funktionen als +, - und SUM.
• Verwenden Sie dieselbe Genauigkeit (zweiter Parameter) für alle TCROUND-Aufrufe.
• Verwenden Sie möglichst immer TCROUND anstelle der einschränkenden Funktionen TCROUNDDOWN, TCROUNDUP und TCROUNDNEAR.

Noch kein Nutzer?