Rätselhaftes Excel oder numerisch Rechnen will gekonnt sein!

am

Auch, wenn mit Excel (fast) alles machbar ist, so gibt es von Zeit zu Zeit Ergebnisse, die unverständlich erscheinen.


Strategiebetrachtung bei Umlagerungen eines Filialisten
mit statistischer Hit-Nieten-Analyse aller Artikel
 
Der Geist in der Maschine?  
Oder etwa Zahlenmagie?
Oder sind wir nur zu blöd?

Im Sheet Just4 Fun finden sich zwei Tabellen; Eine mit Stammdaten und die andere berechnet Werte des Lagerbestands.  Die Datei hast Du im 5. Teil bei der dynamischen Bestandsübersicht erarbeitet.

In Zelle C22 muss "K" oder "L" stehen. In Zelle K19 steht die Gesamtsumme als Summe der Werte am Jahresende (Zeilensumme). Die Summe der Inventurwerte zuzüglich der Summe aller Zugänge minus der Summe der Abgänge (Spaltensumme) wird in K20 berechnet. 

Zeilensumme-Spaltensumme muss Null sein. Das wird in Zelle K21 mit "=(K19-K20)=0" geprüft. Wird "K" gewählt, dann ist das auch WAHR. Bei "L" ist es FALSCH. 

Warum, es stehen doch in beiden Fällen die gleichen Zahlen in Zeilen- und Spaltensumme?

Ändere nun den Check in Zelle K21 in "=(K19=K20)".
Und ein Wunder ist geschehen; jetzt ist der Check WAHR.
Was steckt also dahinter?

Rechengenauigkeit

Der Grund ist die Zahlendarstellung auf einem Rechner und damit natürlich auch in Excel. Jede Rechnung mit Gleitkommazahlen kann ungenaue Ergebnisse erzeugen. Häufiges Umformung von Formeln, das für die Rechnung notwendig ist, beeinträchtigt die numerische Stabilität. Oder x-y ist für x ≈ y ungenau.

Umformen behebt das Problem: Statt (K19-K20)=0 besser K19=K20.

Festlegen der Rechengenauigkeit behebt das Problem, endgültig und nicht reversibel!

https://xkcd.com/2585/

Hinweis: Die Verwendung der Option Genauigkeit wie angezeigt kann kumulierte Berechnungseffekte haben, die dazu führen können, dass Ihre Daten im Laufe der Zeit immer ungenauer werden. Verwenden Sie diese Option nur, wenn Sie sicher sind, dass die Genauigkeit Ihrer Daten mit der angezeigten Genauigkeit beibehalten wird. [1]

daraus lernen wir: Jede numerische Rechnung rundet. Das liegt einfach an der kleinsten Maschinenzahl [2,3], je nach Quelle mit Grichisch-Epsilon oder kurz eps bezeichnet. Das bedeutet, dass bei Rechnungen Gleitkommazahlen, zu einer der beiden benachbarten Gleitkommazahlen gerundet werden (Abrunden, Aufrunden oder Rundung durch Abschneiden), die in dem System in dem Du rechnest darstellbar sind. Je nach Programmiersprache, Norm oder Programm, wie z.B. Excel ist die kleinsten Maschinenzahl unterschiedlich.

Was ist Null?

t.b.c.


Quellen

  1. Festlegen der Rundungsgenauigkeit Support Microsoft, (Abgerufen: 10. März 2022, 12:00 UTC)
  2. Seite „Maschinengenauigkeit“. In: Wikipedia – Die freie Enzyklopädie. Bearbeitungsstand: 24. Juli 2021, 16:21 UTC. URL: https://de.wikipedia.org/w/index.php?title=Maschinengenauigkeit&oldid=214166955 (Abgerufen: 10. März 2022, 12:01 UTC)
  3. Wübbeling F and Keil T (2018), "Zahlendarstellung und Maschinengenauigkeit eps", Vorlesungsunterlagen. Münster, October, 2018. [BibTeX] [URL]

Update vom 10.03.2022

kleinsten Maschinenzahl, Quellen 2, 3, xkcd ergänzt. 




Kommentare

Kommentar veröffentlichen