VERGLEICH() verblüfft ...

Forumthread: VERGLEICH() verblüfft ...

03.09.2007 19:45:00

KlausD

Hallo!
Ich habe mit dem Befehl VERGLEICH verblüffende Erfahrungen gemacht, die würde ich hier gerne erzählen unter anderem deshalb, weil ich gerne wüsste, mit welcher Logik sowas zu verstehen ist:
Der Befehl VERGLEICH(Suchkriterium;Matrix;0) liefert mir ja den Index des ersten Feldes in einer eindimensionalen Matrix, dessen Inhalt mit dem Suchkriterium übereinstimmt.
Ich nehme z.B. als Matrix (in der gesucht werden soll) den Vektor C1:C5 mit den Inhalten (1;2;3;leer;=A5). leer heißt dabei, dass in dem Feld nichts drinsteht. =A5 heißt: in dem Feld steht der Bezug auf das Feld A5. Und das Feld A5 (das also im Feld C5 abgebildet wird) wiederum soll ebenfalls leer sein.
Im Feld A1 soll nun stehen: =VERGLEICH(B1;C1:C5;0). Ich suche also nach dem ersten Feld in C1:C5, das mit dem Inhalt von B1 übereinstimmt. Und B1 (also das Suchkriterium) soll ebenfalls leer sein. D.h., ich suche in dem Array C1:C5 nach dem ersten leeren Feld.
Was liefert mir das als Ergebnis? nicht 4, wie ich erwarten würde, sondern 5!
Ich hoffe, mein Problem wurde klar. Hat dafür jemand eine Erklärung?
Tät mich interessieren.
Klaus

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VERGLEICH() verblüfft ...

03.09.2007 20:21:20

HermannZ
AW: VERGLEICH() verblüfft ...

03.09.2007 22:50:00

KlausD
AW: @Klaus...

04.09.2007 11:46:00

HermannZ
AW: @Klaus...

07.09.2007 10:32:00

KlausD
AW: VERGLEICH() verblüfft ...

03.09.2007 20:21:00

Peter
AW: VERGLEICH() verblüfft ...

03.09.2007 20:36:12

HermannZ
AW: VERGLEICH() verblüfft ...

03.09.2007 22:43:00

KlausD

AW: VERGLEICH() verblüfft ...

03.09.2007 20:21:20

HermannZ

Hallo Klaus;
so wie du es schreibst ist es normal, der vergleich reagiert hier auf den Nullwert der Zelle C5, dieser Nullwert hervorgerufen durch die Zelle A5 die ja leer ist und in C5 mit 0 angezeigt wird.
wenn du einmal in A5 einen Wert einträgst dann siehst du wie der Vergleich darauf reagiert,er zeigt einen Fehlerwert in dem Fall #NV weil ja jetzt kein Nullwert mehr vorhanden ist das hat nicht mit der leeren Zelle B1 zu tun.Du kannst so die erste leere Zelle nicht feststellen.
Zum Beispiel du möchtest die Adresse der ersten leeren Zelle in Spalte C dann zum Beispiel so;
~f~
{=ADRESSE(MIN(WENN(C1:C5="";ZEILE(1:5)));3;4)} die Rücjgabe ist hier C4
oder du willst nur die Zeilennummer dann geht auch
=VERWEIS(1;--(C1:C5="");ZEILE(1:5)) hier wird nur 4 zurückgegeben.
vieleicht kannst du damit was anfangen
Gruss HermannZ

AW: VERGLEICH() verblüfft ...

03.09.2007 22:50:00

KlausD

Hallo, HerrmannZ,
also, etwas widersprüchlich ist es ja schon, dass der Vergleich auf einen Nullwert reagiert, obwohl doch die Zelle, mit der verglichen wird, keine Null enthält, sondern leer ist. Aber bei der leeren Zelle (in C4) wird keine Übereinstimmung gemeldet. Also so ganz kapiert hab ich das noch nicht.
Aber die beiden Befehle VERWEIS und ADRESSE kannnte ich noch gar nicht. Von daher schon mal schönen Dank!
Klaus

AW: @Klaus...

04.09.2007 11:46:00

HermannZ

Hallo Klaus;
Sorry aber meine Schriftstellerischen Fähigkeiten sind leider etwas begrenzt.
Aber vieleicht kannst du es so besser verstehen, erweitere einmal den Bezug C1:C5 auf C1:C9 oder so.
öffne den Assistenten und schau dir die Formel auswertung einmal an dort siehst du jetzt das er die Matrix so darstellt;
{1;2;3;0;0;0;0;0;0}
und im Ergebnis liefert er weiterhin die 5, warum gerade die zweite Null und nicht die erste?
Du siehst das er bei Suchkriterium eine Null darstellt, hier geschieht genau das, was auch in Zelle C5 geschieht.Die Zelle C5 ist das Ergebniss von Zelle A5 und die ist Leer, dadurch wird aber in Zelle C5 eine Null wiedergegeben und diese Null entspricht dem sogenannten Nullwert und ist gleich dem Suchwert im Vergleich.Da die anderen Zellen keinen Nullwert haben sondern hier nur rein darstellerrisch ein Null abgebildet wird, liefert er wieder die 5.
Der Wert einer Zelle mit Null ist ja mit dem Wert einer Leerzelle nicht identisch.
Der sogenannte Nullwert liegt ausserhalb der rechengrenze von Excel und ist >1E+307 und wird somit bei berechnungen nicht erfast,bei allen anderen Funktionen wird er sehr wohl erkannt.z.B bei Suchen oder so.
Dieses Vorgehen hat auch seine vorteile dadurch kann mann zum Beispiel auch Spalten oder zeilen summieren die Fehlerwerte enthalten,diese Werte z.B #NV , #DIV/0 liegen auch alle ausserhalb der Rechengrenze von Excel.Somit braucht man nur die Werte auf diese Grenze zu begrenzen und kann dann mit z.B. folgender Formel die Summe einer Spalte mit Fehlerwerten berechnen;
~f~
=SUMMEWENN(A1:A100;" Mir ist aber noch was eingefallen wenn man mit dem Vergleich ein Matrix durchläuft dann findet er auch die erste Leere Zelle.Die Formel müsste dann so aussehen;
{=VERGLEICH(1;--(C1:C5="");0)}
hier geschiet nun folgendes es werden die Zellen Überprüft ob "" in der Formel umgestellt auf den Wert 1 für WAHR und 0 für FALSCH
es wird dann folgende Matrix gebildet;
{0;0;0;1;0}
da ja der Suchwert 1 ist und die Positon der ersten Übereinstimmung zurückgegeben wird, findet er jetzt natürlich auch die erste Leere Zelle und gibt deren Positionsnummer zurück. Im Beispiel 4.
Ich hoffe das ist jetzt etwas besser.
Gruss HermannZ

AW: @Klaus...

07.09.2007 10:32:00

KlausD

Hallo, HerrmannZ,
schon jetzt mal vielen Dank für Deine ausführlichen Erläuterungen. Da werd ich heut abend mal drüber grübeln ...
Grüße,
Klaus

AW: VERGLEICH() verblüfft ...

03.09.2007 20:21:00

Peter

Hallo Klaus,
das liegt offenbar irgendwie an den leeren Zellen. Wenn Du in B1 ein Leerzeichen eingibst und auch in C4, dann zeigt VERGLEICH als Ergebnis 4 an. Aber keine Ahnung warum...
Gruß, Peter

AW: VERGLEICH() verblüfft ...

03.09.2007 20:36:12

HermannZ

Hallo Peter;
ja genau so ist es er sucht ja dann auch keinen Nullwert sonder ein Leerzeichen und das entspricht dann wieder der zelle 4.
Gruss HermannZ

AW: VERGLEICH() verblüfft ...

03.09.2007 22:43:00

KlausD

Hallo, Peter,
ja, es wird offenbar alles gefunden - außer einer leeren Zelle. Aber wenn die Zelle einen Bezug auf eine leere Zelle enthält, dann auf einmal gibt das eine Übereinstimmung. Schon komisch.
Klaus

Infobox / Tutorial

VERGLEICH-Funktion in Excel: Leere Zellen und Nullwerte verstehen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Öffne Excel und gehe zu einem neuen Arbeitsblatt.
Gib Werte in die Zellen ein:
- In C1 bis C3: 1, 2, 3
- In C4: (leer)
- In C5: =A5 (stellen Sie sicher, dass A5 leer ist)
Gib in Zelle B1 das Suchkriterium ein, z.B. (leer).
Verwende die VERGLEICH-Funktion in Zelle A1:
```
=VERGLEICH(B1;C1:C5;0)
```
Drücke Enter und beobachte das Ergebnis.

Beachte, dass die Funktion den Index einer leeren Zelle möglicherweise nicht zurückgibt, wenn andere Zellen im Bereich Werte besitzen.

Häufige Fehler und Lösungen

Problem: Die Formel gibt nicht den erwarteten Index zurück.
- Lösung: Stelle sicher, dass die Zelle, auf die verwiesen wird (z.B. A5), auch tatsächlich leer ist. Wenn sie einen Nullwert anzeigt, wird das als Leer interpretiert, was zu unerwarteten Ergebnissen führt.
Problem: VERGLEICH findet keine leere Zelle.
- Lösung: Verwende die Formel:
```
{=VERGLEICH(1;--(C1:C5="");0)}
```
  Diese Formel sucht explizit nach leeren Zellen.

Alternative Methoden

ADRESSE und MIN:
```
{=ADRESSE(MIN(WENN(C1:C5="";ZEILE(1:5)));3;4)}
```
Diese Formel gibt die Adresse der ersten leeren Zelle zurück.
VERWEIS:
```
=VERWEIS(1;--(C1:C5="");ZEILE(1:5))
```
Diese Formel gibt die Zeilennummer der ersten leeren Zelle zurück.

Praktische Beispiele

Beispiel für VERGLEICH und leere Zellen:
- Wenn du in B1 nichts eingibst und nach dem ersten leeren Feld in C1:C5 suchst, kann das Ergebnis von VERGLEICH 5 sein, wenn C5 eine Formel hat, die auf A5 verweist, und A5 leer ist.
Suchkriterien:
- Wenn du das Suchkriterium in B1 auf ein Leerzeichen änderst, wird das Verhalten von VERGLEICH anders. In diesem Fall könnte das Ergebnis 4 sein, wenn C4 ebenfalls ein Leerzeichen enthält.

Tipps für Profis

Verwende die Funktion WENN in Kombination mit VERGLEICH, um nur bestimmte Bedingungen zu prüfen:
```
=WENN(VERGLEICH(B1;C1:C5;0)=#NV; "Nicht gefunden"; "Gefunden!")
```
Überprüfe immer deine Daten: Achte darauf, dass deine Zellen wirklich leer sind und keine unsichtbaren Zeichen enthalten.

FAQ: Häufige Fragen

1. Warum gibt VERGLEICH nicht das erwartete Ergebnis zurück?
VERGLEICH kann manchmal unerwartete Ergebnisse liefern, wenn Zellen Nullwerte oder Formeln enthalten, die auf leere Zellen verweisen.

2. Wie kann ich die erste leere Zelle in einem Bereich finden?
Verwende die Formel =VERGLEICH(1;--(C1:C5="");0) um die Position der ersten leeren Zelle zu ermitteln.

3. Was passiert, wenn ich ein Leerzeichen in B1 eingebe?
Wenn du ein Leerzeichen in B1 eingibst, sucht VERGLEICH nach einem Leerzeichen in C1:C5, was das Ergebnis beeinflussen kann.

Beliebteste Forumthreads (12 Monate)

Forumthread: VERGLEICH() verblüfft ...

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

VERGLEICH-Funktion in Excel: Leere Zellen und Nullwerte verstehen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread