Eine Liebeserklärung an MS Excel
Excelschulung. Wir üben XVERWEIS. Warum einige der Werte nicht berechnet werden, will eine Teilnehmerin wissen:
Der Fehler ist schnell gefunden: In der Zelle L8 steht nicht „Platinum“, sondern „Platiunum “ – gefolgt von einem Leerzeichen.
Martin (der tabellenexperte) wundert sich, dass bei der Verwendung von SVERWEIS & co gesamte Tabellen in die Datei geladen werden … Aber: kennt Abhilfe.
Danke für die wertvollen Informationen und die guten Hinweise.
Warum denn der XVERWEIS, den wir in der Excelschulung geübt haben, nicht funktioniere, will eine Teilnehmerin wissen:
Die Ursache ist schnell gefunden: sie hatte ein Komma statt eines Semikolons getippt.
Eine andere Teilnehmerin gibt ihr den Tipp den Funktionsassistenten zu verwenden, statt die Formel per Hand zu schreiben. Ich pflichte ihr bei.
Wo denn der Fehler liege, wollte die Teilnehmerin wissen – warum kann ihr XVERWEIS den nicht rechnen?
Nun: der Fehler ist schnell gefunden: XVERWEIS schreibt man mit einem „s“.
Eine Teilnehmerin einer Excelschulung stellt mir eine Frage. Ob ich ihr helfen könne.
Klar.
In einer Tabelle hat sie in einer Spalte Namen, in einer anderen einen Rechnungsbetrag:
Sie möchte auf einem zweiten Tabellenblatt zu dem Namen den entsprechenden Rechnungsbetrag sehen:
Nichts leichter als das: XVERWEIS muss her:
=XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)
Sieht doof aus, weil in beiden Tabellen Leerzeilen stehen und Überschriftsinformationen, welche nun mit 0 Euro quittiert werden.
Also wird die Formel angepasst:
=WENN(XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)=0;"";XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51))Sieht doof aus, weil Nina 0 Euro hat und nun eine leere Zelle:
Also: Formel ändern und 0 durch „“ ersetzen:
=WENN(XVERWEIS(A8;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)="";"";XVERWEIS(A8;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51))
Immer noch doof – weiter unten entdecken wir einen Namen, der noch nicht auf dem ersten Tabellenblatt auftauchte. Also ändern wir den Parameter wenn_nicht_gefunden in „“ – und schon verschwindet das #NV
Die Formel:
=WENN(XVERWEIS(A27;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51;"")="";"";XVERWEIS(A27;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51;""))Die Teilnehmerin ist glücklich!
Amüsant.
PowerQuery-Schulung. Wir greifen auf eine (Währungskurs-)Tabelle im Internet zu:
Wir müssen die Daten transformieren, das heißt: an mehreren Stellen trennen:
Das Ergebnis
wird nach Excel als Tabelle geladen und dort weiterverarbeitet:
IN diesem Beispiel mit einem XVERWEIS (oder SVERWEIS würde auch funktionieren).
Eine Teilnehmerin meldet sich und sagt, dass ihr XVERWEIS Fehler liefern würde:
Die Ursache war schnell gefunden: sie hatte nicht Leerzeichen:Bindestrich als Trennteichen verwendet, sondern nur den Bindestrich:
Somit war noch ein Leerzeichen im Text, Und deshalb lief der XVERWEIS ins Leere …
Volker Pagel ist sehr irritiert. Er hat lange gesucht, um die Ursache für folgende Inkonsequenz zu finden.
In einer Liste befinden sich Kostenstellennummer, beispielsweise 010, 0010 oder 000010.
Die Funktionen der Klasse ZÄHLENWENN, SUMMEWENN & co unterscheiden nicht zwischen 010, 0010 oder 00001, obwohl diese als Text vorliegen. Wahrscheinlich werden diese Texte intern als Zahlen behandelt.
Anders dagegen die Funktionen WENN, SVERWEIS, XVERWEIS, SUMMENPRODUKT & co. HIER wird klar zwischen 010 und 00010 unterschieden!
„Ein in der Formel verwendeter Wert ist vom falschen Datentyp“, behauptet Excel und liefert den Fehler #WERT beim XVERWEIS:
Nein, lieber XVERWEIS – DAS ist nicht die Ursache des Fehlers, sondern weil die beiden Bereiche unterschiedlich groß sind:
$A$1:$A$297872 und $F$1:$F$297782
In der Schulung fragt eine Teilnehmerin, warum der XVERWEIS bei ihr nicht funktioniere:
Der Fehler ist schnell gefunden: Die Formel
=XVERWEIS(A13;$A$1:$A$10;$D:$D$10)muss natürlich lauten (die „1“ fehlt):
=XVERWEIS(A13;$A$1:$A$10;$D$1:$D$10)Immerhin: Excel hilft und zeigt die fehlerhafte Position an:
Warum rechnet der XVERWEIS bei mir nicht, möchte ein Teilnehmer in meiner Excelschulung wissen:
Ich lasse die korrekt berechnete Zelle editieren:
Ich sehe den Fehler nicht. Er als ich den Teilnehmer bitte, eine andere, fehlerhafte Zelle mit einem Doppelklick zu editieren, sehe ich, dass von den acht Dollarzeichen eines fehlt. Und somit sind die beiden Spalten nicht gleich groß.
Ich wohne in der Albert-Roßhaupter-Straße. Eigentlich müsste sie – nach neuer deutscher Rechtschreibung – Albert-Rosshaupter-Straße heißen. Aber der Name des SPD-Politikers wurde nicht geändert. Sei’s drum. Was passiert allerdings bei der Suche in Excel von Wörtern mit „ß“ und „ss“?
Die Funktion SVERWEIS unterscheidet; XVERWEIS allerdings nicht:
Erstaunlich! Umso erstaunlicher ist es, dass im Spanischen weder zwischen „n“ und „ñ“ unterschieden wird:
Im spanischen Excel werden auch nicht die Vokale mit und ohne Akzent unterschieden:
nicht bei BUSCARV (SVERWEIS) und nicht bei BUSCARX (XVERWEIS). Sehr erstauntlich.
Danke an Mourad Louha für diesen wertvollen Tipp!
Sehr ärgerlich!
Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:
In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …
Mich beschleicht ein Gedanke …
Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*
Lieber Herr Martin,
Excel nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.
Ich müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer anderen Zelle
Entsprechen. So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob
Ich den Inhalt der Zellen auch verkettet ausgeben kann.
Vielen Dank
Bevor ich eine Antwort gebe, Frau L.,
zwei Gegenfragen: sind die Daten sortiert (nach den Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht einfach“)
Liebe Grüße
Rene Martin
So ist es mir recht,
leider kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…
Eindeutig? Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich nicht aus…
Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden
Die erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet die Informationen die ich gerne zusammen verkettet hätte, also
Für 20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…
Beim Trennzeichen wäre ich emotional flexibel
Die erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.
Vielen Dank
Hallo Frau L.,
ich hoffe, Sie haben die Funktion TEXTVERKETTEN. DAMIT klappt es.
Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.
Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.
Die erste Formel lautet:
=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($I$1;
VERGLEICH(L2;$A$2:$A$27;0);0;
VERGLEICH(L2;$A$2:$A$27;1)-
VERGLEICH(L2;$A$2:$A$27;0)+1))
Die zweite:
=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($M$31;
VERGLEICH(O32;$L$32:$L$51;0);0;
VERGLEICH(O32;$L$32:$L$51;1)-
VERGLEICH(O32;$L$32:$L$51;0)+1))
Klappt das? Ist es das, was Sie möchten?
Gestern war ich sehr verblüfft und erstaunt. Ich habe eine Excelmappe mit einer intelligenten (strukturierten, dynamischen, formatierten) Tabelle. In einer ANDEREN Datei greife ich darauf zu – per SVERWEIS, INDEX und VERGLEICH oder XVERWEIS:
Also beispielsweise über:
=SVERWEIS([@Name];Datenquelle.xlsx!tbl_Schlumpf[#Alle];2;FALSCH)
oder:
=INDEX(Datenquelle.xlsx!tbl_Schlumpf[Schlumpf];VERGLEICH([@Name];Datenquelle.xlsx!tbl_Schlumpf[Name];0))
oder:
=XVERWEIS([@Name];Datenquelle.xlsx!tbl_Schlumpf[Name];Datenquelle.xlsx!tbl_Schlumpf[Schlumpf])
Beide Dateien, die sich im gleichen Ordner befinden, werden geschlossen. Nur die Zieldatei wird geöffnet. Das Ergebnis: Fehlermeldungen bei allen drei Formeln:
Ich ersetze die Bezüge, also
Datenquelle.xlsx!tbl_Schlumpf[Name]
durch
[Datenquelle.xlsx]Quelle!$A:$A
Das ist nicht schön! Aber – es funktioniert! Hat Microsoft vergessen DAS bei intelligenten Tabellen zu implementieren? DAS ist ja ein Schritt zurück! DAS will ich eigentlich nicht (mehr). Immerhin: es funktioniert. So kann ich die Zieldatei öffnen, die Werte werden aktuell angezeigt, ohne dass die Quelldatei offen sein muss.
Danke an Christa für den Hinweis, dass man die Verknüpfung über PowerQuery organisieren kann.
Und: vielen Dank an den Hinweis von Mourad Louha: DAS IST GEWOLLT!
Doof! In einer intelligenten Tabelle habe ich drei Spalten: ID, Vorname und Nachname. XVERWEIS kann einen Vornamen finden:
=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname])
XVERWEIS kann ALLE Vornamen (als Matrixfunktion) zurückgeben:
=XVERWEIS(G2:G9;tbl_Kunden[Nr];tbl_Kunden[Vorname])
XVERWEIS kann Vor- und Nachnamen (hätten wir noch mehr Informationen – so auch diese) zurückgeben:
=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname]:tbl_Kunden[Nachname])
Aber leider: beides geht nicht: mehrere Spalten zurückgeben von mehreren IDs:
Schade!
Mit den drei Funktionen BEREICH.VERSCHIEBEN, INDIREKT und XVERWEIS kann man einen dynamischen bereich aufspannen. Diese drei Funktionen kann man als Namen speichern (ich habe sie mal Jahr1, Jahr2 und Jahr3 genannt).
Die Namen mit den Funktionen BEREICH.VERSCHIEBEN und XVERWEIS kann man wunderbar in einem Diagramm verwenden:
INDIREKT aber nicht!
