![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Hallo *,
ich bin gerade auf ein kleines Calc-Problem gestoßen, aus dem ich nicht
recht schlau werde:
A B
5 0,27359781121751
1 4,46428571428571
3 1,76354401805869
2 2,04918032786885
3 1,07118644067797
In Spalte A, 1. Zeile, steht die Funktion
=ZÄHLENWENN(B$1:B$5;">="&B1)
Die habe ich runter gezogen, um eine "Platzierung" zu erreichen.
1,76 ... ist doch eindeutig größer als 1,07 ... Kann es sein, dass die
Funktion Nachkommastellen ignoriert?
Gruß
Robert
Hi Robert,
ich bekomme eine "4" in A5 – wie es sein sollte …
Ciao
André
Hallo André ,
ich bekomme eine "4" in A5 – wie es sein sollte …
stelle ich mit Verblüffung auch fest, wenn ich die Inhalte der Mail
einfach kopiere und in Calc einfüge.
Ich muss wohl etwas mehr ausholen, da die Werte in der Spalte B
berechnet wurden:
Strecke[m] Zeit[s] Platz Geschwindigkeit[m/s]
2 7,31 5 0,27359781121751
10 2,24 1 4,46428571428571
1000 567,4 3 1,76242509693338
10 4,88 2 2,04918032786885
9,48 8,85 3 1,07118644067797
... ist jetzt etwas blöde mit der Formatierung. Beim Import einfach die
Leerzeichen zusammen fassen lassen.
Die Geschwindigkeit in der 4. Spalte wird über die Division der Inhalte
der ersten mit der 2. Spalte berechnet.
Formel in der 2. Zeile für den Platz:
=ZÄHLENWENN(D$2:D$64;">="&D2)
Formel in der 2. Zeile für die Geschwindigkeit
=A1/B1
Da kommen bei mir die merkwürdigen Platzierungen raus, bei den Zahlen
ohne Formel aus der vorhergehenden Mail nicht.
Gruß
Robert
Hi Robert,
das ist ja man seltsam …
Mit =ZÄHLENWENN(D$2:D$6; ">"&D2)+1 funktioniert das Ganze wieder …
Ciao
André
Hallo André,
ohne "plus 1" funktioniert das Ganze auch ( =ZÄHLENWENN(D$2:D$6; ">"&D2) ):
Strecke[m] Zeit[s] Platz Geschwindigkeit[m/s]
2 7,31 4 0,27359781121751
10 2,24 0 4,46428571428571
1000 567,4 2 1,76242509693338
10 4,88 1 2,04918032786885
9,48 8,85 3 1,07118644067797
Wenn Die Platzierung mit "1" beginnen soll, dann:
=ZÄHLENWENN(D$2:D$6; ">="&D2)
Gruß
Hans-Werner ;-))
------ Originalnachricht ------
Hallo André ,
ich bekomme eine "4" in A5 – wie es sein sollte …
stelle ich mit Verblüffung auch fest, wenn ich die Inhalte der Mail
einfach kopiere und in Calc einfüge.Ich muss wohl etwas mehr ausholen, da die Werte in der Spalte B
berechnet wurden:
Strecke[m] Zeit[s] Platz Geschwindigkeit[m/s]
2 7,31 5 0,27359781121751
10 2,24 1 4,46428571428571
1000 567,4 3 1,76242509693338
10 4,88 2 2,04918032786885
9,48 8,85 3 1,07118644067797... ist jetzt etwas blöde mit der Formatierung. Beim Import einfach die
Leerzeichen zusammen fassen lassen.
Die Geschwindigkeit in der 4. Spalte wird über die Division der Inhalte
der ersten mit der 2. Spalte berechnet.
Formel in der 2. Zeile für den Platz:
=ZÄHLENWENN(D$2:D$64;">="&D2)
Formel in der 2. Zeile für die Geschwindigkeit
=A1/B1Da kommen bei mir die merkwürdigen Platzierungen raus, bei den Zahlen
ohne Formel aus der vorhergehenden Mail nicht.
Scheint sich wohl um einen Rundungsfehler zu handeln. Ich hab mir das
Ergebnis der letzten Zeile (das 1,07118644067797) mal etwas genauer
angesehen (abgelegt in den Zellen ab A10), und stufenweise um jeweils
eine Stelle mehr gerundet:
Wert Stellen Gleichheit
1,07118644067797000 0
1,07118644067797000 18 0
1,07118644067797000 17 0
1,07118644067797000 16 0
1,07118644067797000 15 0
1,07118644067797000 14 1
1,07118644067800000 13 1
1,07118644067800000 12 1
1,07118644068000000 11 1
:
Die Formeln in Spalte A lauten dabei:
für A10 (sprich den Originalwert, wie er sich aus der Division ergibt):
=D5
für A11 etc. (jeweils eine Stelle mehr gerundet; sicherheitshalber
angefangen noch jenseits der Darstellungsgenauigkeit):
=RUNDEN(A10;B11)
Die Formel in Spalte C lautet:
=ZÄHLENWENN(A10;">="&A10)
Auffällig ist dabei, dass ab einer Rundung auf 14 oder weniger Stellen
der Vergleich passt, bei mehr Stellen (die es jedoch eigentlich aufgrund
der internen Zahlendarstellung gar nicht geben dürfte) jedoch nicht.
Ich kann mir eigentlich nur einen einzigen Grund dafür vorstellen,
nämlich dass die betreffende binäre Zahl sich nicht exakt als
Dezimalzahl darstellen lässt (also genau der umgekehrte Effekt wie im
Thread "calc simple Berechnung gibt Fehler" diskutiert).
Übrigens löst sich das "...9700" mit ein bisschen Tricksen tatsächlich
auf nach "9662". Irgendwie scheint sich dieser Rundungsfehler wohl durch
zu drücken.
Warum sich das allerdings bei ZÄHLENWENN überhaupt auswirkt, verstehe
ich auch nicht. Eigentlich sollte man meinen, da wird einfach die
Binärdarstellung der Zelle mit sich selbst (also der selben
Binärdarstellung) verglichen.
Einmal wieder beweist sich der Leitsatz, dass man beim Hantieren mit
Gleitkommazahlen auch immer sehr großzügig [tm] mit der Funktion RUNDEN
umgehen sollte. :-/
Wolfgang
<Verdacht> Die Operation im zweiten Parameter (">="&A10) liefert ja erst
mal einen *Text* zurück; und dazu muss natürlich die Referenz auf die
Zelle aufgelöst und durch den *dezimalen* Inhalt der Zeile ersetzt
werden. Und schon könnten wir die Abweichung zwischen dezimaler und
binärer Darstellung erklärt haben. </Verdacht>
<Kopfkratz> Trotzdem fällt es mir schwer, mir vor zu stellen bzw. zu
akzeptieren, wie bzw. dass ein Rundungsfehler jenseits der internen
Darstellungsgenauigkeit tatsächlich existieren und v. a. sich bis nach
oben auswirken kann. </Kopfkratz>
Wolfgang
Hallo Hans-Werner,
Wenn Die Platzierung mit "1" beginnen soll, dann:
=ZÄHLENWENN(D$2:D$6; ">="&D2)
Hast Du das auch so durchprobiert wie beschrieben? Die Geschwindigkeiten
in der Spalte D werden aus den Werten des Spalte a durch die Werte der
Spalte B berechnet. Nur wenn ich diese Rechnung durchführe kommt es zu
dem merkwürdigen Verhalten von "Zählenwenn". Wenn ich schlicht die Werte
kopiere passiert das nicht.
Gruß
Robert
Hallo,
warum das mit Zählenwenn passiert, ist mir auch unklar.
Warum machst du das nicht mit der Funktion RANG, die das ganz elegant berechnet?
=RANG(C2;$C$2:$C$6; )
ergibt
Strecke[m] Zeit[s] Geschwindigkeit[m/s] Rang
2 7,31 0,27359781121751 5
9,48 8,85 1,07118644067797 4
1000 567,4 1,76242509693338 3
10 4,88 2,04918032786885 2
10 2,24 4,46428571428571 1
MfG Alois
Hallo Alois,
warum das mit Zählenwenn passiert, ist mir auch unklar.
Warum machst du das nicht mit der Funktion RANG, die das ganz elegant
berechnet?
=RANG(C2;$C$2:$C$6; )
ZÄHLENWENN kannte ich. Da gibt es ähnliche Funktionen in Base/SQL. Und
aus der Ecke komme ich nun mal. Ist aber interessant zu wissen, dass es
eben unterschiedliche Zugriffsmöglichkeiten für den gleichen Zweck gibt.
Gruß
Robert
Hallo Robert,
deine Formel mit zählenwenn setzt voraus, dass hier die Prüfung auf Gleichheit von zwei Werten funktioniert.
Eine Prüfung Gleichheit bei Fließkommazahlen macht man in der Programmierung eher immer so, dass man entweder beide Werte vorher rundet (z.B. auf die 12. Stelle hinter dem Komma) und dann erst auf Gleichheit prüft oder dass man eine Gleichheit annimmt, wenn das Ergebnis der Subtraktion beider Werte einen Mindestwert unterschreitet.
Kurz gesagt: eine direkte Prüfung auf Gleichheit funktioniert in der Praxis nur mit Ganzzahlen (Integer,...).
Ich würde also die Formel so ändern, dass eine Prüfung auf Gleichheit nicht erforderlich ist und daher das "=" aus der Formel weglassen. Zum Ausgleich muss man dann den Wert 1 addieren:
=ZÄHLENWENN(C$2:C$6;">"&C2)+1
MfG Alois
Hallo Alois,
Kurz gesagt: eine direkte Prüfung auf Gleichheit funktioniert in der
Praxis nur mit Ganzzahlen (Integer,...).
Na ja, dann würde ich aber doch in der Hilfestellung auf so etwas
explizit hinweisen.
Die Prüfung auf Gleichheit funktioniert ja schließlich auch dann, wenn
ich die berechneten Zahlen (die gerundet sein mögen) nehme und
vergleiche - siehe die erste Mail in diesem Thread. Die Prüfung
funktioniert nur dann nicht, wenn ich die Rechnung als Grundlage nehme.
Für mich sieht das eher so aus, als ob mit zweierlei Maß gemessen wird:
Nehme ich die Elemente eines Bereichs, so wird nicht auf die Bezüge zu
anderen Zellen geachtet. Nehme ich hingegen nur eine Zelle, so werden
die Bezüge zu anderen Zellen berücksichtigt.
Wenn ich statt
=ZÄHLENWENN(D$10:D$14;">="&D11)
z.B.
=ZÄHLENWENN(D$10:D$14;">="&D11:D11)
nehme, dann passt das Ganze.
Ich darf also schlicht nicht Zellen mit Inhalten von Bereichen
vergleichen, sondern muss aus Zellen Bereiche machen - und das liegt
dann an den internen Berechnungsarten von Calc, die nirgendwo in der
Hilfe auftauchen.
Gruß
Robert
Hallo Robert, *,
wenn das mit dem Bereich bei dir funktioniert, muss das ein Zufall sein.
Ich habe das Problem mal auf einen ziemlich einfachen Fall reduziert: alles spielt sich Zeile 2 ab, die Spalten beinhalten:
B: 17
C: =A2/B2 Anzeige ist 0,058823529411765
D: 0,058823529411765 (ich habe das zum Vergleichen extra eingerückt)
E: =WENN(C1=D1;"gleich";"ungleich") Anzeige: gleich
F: =ZÄHLENWENN(D2:D2;"="&C2) Anzeige:0
G: =ZÄHLENWENN(D2:D2;"="&C2:C2)
Dass in E2 "gleich" steht, verblüfft, wenn man dann F2 und G2 anschaut, ich habe keine Erklärung dafür, es ist aber insofern eine glückliche Fügung, dass ich sicher sein kann, dass ich nicht falsch abgetippt habe und nur deshalb F2 und G2 immer 0 liefern.
F2 und G2 liefern nun beide den gleichen Wert 0, anders als in deinem Fall.
Abgesehen davon, dass die Hilfe auch von Leuten wie dir und mir beschrieben wird, man also keine Wunderdinge erwarten kann, die ganz besondere Situationen detailliert beschreiben, ist das eben gar kein Punkt, der in der Hilfe beshrieben werden kann, weil er nicht allgemeingültig ist, wie mein Beispiel zeigt.
Und das liegt, wie in den früheren Mails schon immer wieder angeklungen ist, an den Schwierigkeiten, die die Darstellung von gebrochenen Dezimalzahlen zumindest in den gebräuchlichen Computern macht, weil dazu in Binärzahlen umgerechnet werden muss und der Speicherplatz endlich ist (geregelt in der IEEE 754 seit 1985). Ein Dezimalbruch, der endlich ist, kann im Binärsystem unendlich sein (ich würde sogar gefühlsmäßig sagen, dass das die meisten sind, aber für eine präzisere Aussage müsste ich mich viel länger damit beschäftigen). Dadurch ergeben sich zwangsläufig Rundungsdifferenzen (den Begriff "Rundungs_fehler_" vermeide ich), da nur endlich viele Stellen zur Verfügung stehen. Es dürfte aber auch bei manchen auch im Binärsystem endlichen Brüchen zu Rundungsdifferenzen kommen, weil die Anzahl der zur Verfügung stehenden Stellen nicht ausreicht.
In meinem Beispiel ist sogar schon der Dezimalbruch periodisch und damit unendlich; im Binärsystem ist er es natürlich auch, weil 17 eine Primzahl ist.
Daher muss man _aus prinzipiellen Gründen_ beim Vergleich von Gleitkommawerten _immer _spezielle Maßregeln treffen (sobald einer davon berechnet ist, denn dann muss der interne Binärwert nicht unbedingt der gleiche sein wie der aus der Dezimaldarstellung errechnete), damit ein richtiges Ergebnis herauskommt: runden (wobei ich nicht die Hand ins Feuer lege, dass in seltenen Fällen auch das noch zu verschiedenen Werten führt; ich habe kein Beispiel, aber ich halte es für theoretisch möglich) oder noch besser die Abfrage, ob die Differenz der zu vergleichenden Werte größer ist als ein sehr kleiner Schwellwert, wie es ja auch schon in den früheren Mails beschrieben wurde.
Wenn die Funktion RANG tatsächlich immer das richtige Ergebnis liefert, dann hat deren Autor diese Maßnahmen offensichtlich in seinem Code getroffen, und du kannst froh sein, dass er deine Aufgabe bereits gelöst hat.
In der Hilfe kann meiner Meinung nach zu diesem Thema nichts stehen, da es sich um ein grundlegendes und der Computertechnologie immanentes Problem handelt, dessen man sich bewusst sein muss. Das wäre auch nicht anders, wenn man nicht die Basis 2 für die Speicherung von Zahlen verwenden würde, es wären dann nur andere Zahlen, die das gleiche Problem aufweisen würden. Und ich denke, mein Beispiel würde wahrscheinlich bei allen Systemen zum gleichen Problem führen, da 17 eine Primzahl ist, und wenn nicht, dann wäre es Zufall und man fände schnell ein anderes.
Viel zum Thema findest du Im Wikipedia-Artikel "Gleitkommazahl".
Gruß
Gerhard
Hallo Robert,
Deine Aussage, dass das Ganze funktioniert, wenn man bei der Bedingung einen Bereich angibt, kann ich nicht bestätigen. Die Ergebnisse in den Spalten "C" und "D" sind identisch:
"A1": 9,48
"B1": 8,85
"C1": =ZÄHLENWENN(E1:E1; ">="&E1)
"D1": =ZÄHLENWENN(E1:E1; ">="&E1:E1)
"E1": =A1/B1
"A2": 9,48
"B2": 8,86
"C2": =ZÄHLENWENN(E2:E2; ">="&E2)
"D2": =ZÄHLENWENN(E2:E2; ">="&E2:E2)
"E2": =A2/B2
"A3": 9,48
"B3": 8,87
"C3": =ZÄHLENWENN(E3:E3; ">="&E3)
"D3": =ZÄHLENWENN(E3:E3; ">="&E3:E3)
"E3": =A3/B3
und so weiter ...
"A"___"B"___"C"___"D"___"E"
9,48 8,85 0 0 1,07118644067797 <== FEHLER
9,48 8,86 1 1 1,06997742663657
9,48 8,87 1 1 1,06877113866967
9,48 8,88 1 1 1,06756756756757
9,48 8,89 1 1 1,06636670416198
9,48 8,90 1 1 1,06516853932584
9,48 8,91 1 1 1,06397306397306
9,48 8,92 0 0 1,06278026905830 <== FEHLER
9,48 8,93 1 1 1,06159014557671
9,48 8,94 1 1 1,06040268456376
9,48 8,95 1 1 1,05921787709497
9,48 8,96 1 1 1,05803571428571
9,48 8,97 1 1 1,05685618729097
9,48 8,98 1 1 1,05567928730512
9,48 8,99 0 0 1,05450500556174 <== FEHLER
9,48 9,00 1 1 1,05333333333333
9,48 9,01 1 1 1,05216426193119
9,48 9,02 1 1 1,0509977827051
9,48 9,03 1 1 1,04983388704319
9,48 9,04 1 1 1,04867256637168
9,48 9,05 0 0 1,04751381215470 <== FEHLER
9,48 9,06 1 1 1,04635761589404
9,48 9,07 1 1 1,04520396912900
9,48 9,08 1 1 1,04405286343612
9,48 9,09 1 1 1,04290429042904
9,48 9,10 1 1 1,04175824175824
9,48 9,11 1 1 1,04061470911087
9,48 9,12 1 1 1,03947368421053
9,48 9,13 1 1 1,03833515881709
9,48 9,14 1 1 1,03719912472648
9,48 9,15 1 1 1,03606557377049
9,48 9,16 1 1 1,03493449781659
9,48 9,17 1 1 1,03380588876772
9,48 9,18 1 1 1,03267973856209
9,48 9,19 1 1 1,03155603917301
9,48 9,20 0 0 1,03043478260870 <== FEHLER
9,48 9,21 1 1 1,02931596091205
9,48 9,22 1 1 1,02819956616052
9,48 9,23 1 1 1,02708559046587
9,48 9,24 0 0 1,02597402597403 <== FEHLER
Gruß
Hans-Werner
------ Originalnachricht ------
Hallo Hans-Werner,
Deine Aussage, dass das Ganze funktioniert, wenn man bei der Bedingung
einen Bereich angibt, kann ich nicht bestätigen. Die Ergebnisse in den
Spalten "C" und "D" sind identisch:
Hatte ich auch nur beim ersten Mal, als ich die Formel runter gezogen
habe. Beim nächsten Öffnen der Datei war's dann wie bei Dir beschrieben.
Gruß
Robert
Kurz gesagt: eine direkte Prüfung auf Gleichheit funktioniert in der
Praxis nur mit Ganzzahlen (Integer,...).Na ja, dann würde ich aber doch in der Hilfestellung auf so etwas
explizit hinweisen.
Hint: Auch die Hilfe wird nur von fehlbaren Menschen geschrieben, und
nicht von unfehlbaren Göttern.
Die Prüfung auf Gleichheit funktioniert ja schließlich auch dann, wenn
ich die berechneten Zahlen (die gerundet sein mögen) nehme und
vergleiche - siehe die erste Mail in diesem Thread.
Sie funktioniert offensichtlich unter bestimmten Umständen, aber unter
anderen Umständen funktioniert sie wohl offensichtlich nicht. Siehe die
Erläuterungen in meiner anderen Email von vor ein paar Tagen.
Die Prüfung
funktioniert nur dann nicht, wenn ich die Rechnung als Grundlage nehme.
Das ist definitiv falsch; auch basierend auf dem Rechnungsergebnis
funktioniert sie in 4 der 5 Fälle deines Beispiels.
Für mich sieht das eher so aus, als ob mit zweierlei Maß gemessen wird:
Nehme ich die Elemente eines Bereichs, so wird nicht auf die Bezüge zu
anderen Zellen geachtet. Nehme ich hingegen nur eine Zelle, so werden
die Bezüge zu anderen Zellen berücksichtigt.
Wenn ich statt
=ZÄHLENWENN(D$10:D$14;">="&D11)
z.B.
=ZÄHLENWENN(D$10:D$14;">="&D11:D11)
nehme, dann passt das Ganze.
Das ist Unsinn; das Problem liegt IMHO in Rundungsfehlern zwischen der
dezimalen und der binären Darstellung der betreffenden Zahl; siehe meine
anderen Emails.
Ich darf also schlicht nicht Zellen mit Inhalten von Bereichen
vergleichen, sondern muss aus Zellen Bereiche machen - und das liegt
dann an den internen Berechnungsarten von Calc, die nirgendwo in der
Hilfe auftauchen.
Sinnvoller, als sich auf solche esotherischen Abwege zu begeben, wäre
IMHO, einfach das Ergebnis der Division auf eine sinnvolle Genauigkeit
zu runden, also statt "=A1/B1" in Spalte D (Geschwindigkeit)
"=Runden(A1/B1;10)" o. ä. zu schreiben.
Wolf 'oder meintewegen auch auf 14 Stellen, sofern du mir glaubhaft
darlegen kannst, dass du eine solch hohe hohe Genauigkeit wirklich
benötigst' gang
Hallo Wolfgang,
Die Prüfung
funktioniert nur dann nicht, wenn ich die Rechnung als Grundlage nehme.Das ist definitiv falsch; auch basierend auf dem Rechnungsergebnis
funktioniert sie in 4 der 5 Fälle deines Beispiels.
Und was ist daran falsch? "Die Prüfung funktioniert nur dann nicht, wenn
ich die Rechnung als Grundlage nehme." Da steht nicht, dass sie
grundsätzlich dann nicht funktioniert.
Für mich sieht das eher so aus, als ob mit zweierlei Maß gemessen wird:
Nehme ich die Elemente eines Bereichs, so wird nicht auf die Bezüge zu
anderen Zellen geachtet. Nehme ich hingegen nur eine Zelle, so werden
die Bezüge zu anderen Zellen berücksichtigt.
Wenn ich statt
=ZÄHLENWENN(D$10:D$14;">="&D11)
z.B.
=ZÄHLENWENN(D$10:D$14;">="&D11:D11)
nehme, dann passt das Ganze.Das ist Unsinn; das Problem liegt IMHO in Rundungsfehlern zwischen der
dezimalen und der binären Darstellung der betreffenden Zahl; siehe meine
anderen Emails.
Ich lasse mir hier nicht "Unsinn" unterstellen. Der Ton gefällt mir
nicht. Dann lies bitte erst die weiteren Kommentare, bevor Du so etwas
schreibst.
Gruß
Robert
Hallo Robert,
Hallo Alois,
Kurz gesagt: eine direkte Prüfung auf Gleichheit funktioniert in der
Praxis nur mit Ganzzahlen (Integer,...).Na ja, dann würde ich aber doch in der Hilfestellung auf so etwas
explizit hinweisen.
Die Prüfung auf Gleichheit funktioniert ja schließlich auch dann, wenn
ich die berechneten Zahlen (die gerundet sein mögen) nehme und
vergleiche - siehe die erste Mail in diesem Thread. Die Prüfung
funktioniert nur dann nicht, wenn ich die Rechnung als Grundlage nehme.
Ich bin ja der Überzeugung, dass bei deinem Beispiel sichtbar wird, wie problematisch der Vergleich von 2 Fließkommazahlen auf Gleichheit ist. Die Funktion ZÄHLENWENN nimmt den Wert aus der Spalte und klappert die Matrix ab und vergleicht. Wir wissen nicht genau, wie das intern implementiert ist - so, wie es aussieht, werden die Vergleichswerte verschieden gespeichert bzw. behandelt und so sind dann beide Werte nicht mehr ganz gleich und darum funktioniert der Vergleich nicht so wie erwartet.
Am sichersten fährt man auf jeden Fall, wenn man bei der Berechnung der Geschwindigkeit eine Rundung verwendet - das hat ja schon ein anderer vorgeschlagen...
MfG Alois