Gefürchtet, geliebt und irgendwie unausweichlich. Die allseits bekannte SVERWEIS-Funktion könnte auch aus dem Buch „Der Herr der Ringe“ stammen:
„Eine Funktion, sie zu knechten, sie alle zu finden, ins Dunkel zu treiben und ewig zu binden“
So fühlt sich zumindest mancher Excel-Einsteiger, wenn er Bekanntschaft mit dem SVERWEIS macht. Wenn man ihn jedoch einmal gemeistert hat, kommt man nur schwer wieder davon los.
Nun, du kannst den SVERWEIS getrost in den Schicksalsberg werfen. Denn jetzt gibt es den
XVERWEIS
Was diese neue Excel-Mehrzweckwaffe kann, erfährst du in diesem Artikel.
Die Beschränkungen des SVERWEIS
Wer die SVERWEIS-Funktion häufig einsetzt, kennt auch den größten Schwachpunkt: Man kann in der Zieltabelle nur von links nach rechts suchen. Befindet sich die gesuchte Information links von der Suchspalte, kann man den SVERWEIS leider nicht nutzen. Hier kommt dann in der Regel das Dreamteam INDEX + VERGLEICH zum Einsatz.
Aber das ist nicht die einzige Einschränkung. Vergisst man beispielsweise, den dritten Parameter in SVERWEIS auf FALSCH bzw. 0 zu setzen (und das passiert nicht nur Excel-Anfängern!), liefert die Funktion in unsortierten Zieltabellen falsche Ergebnisse:
Ein vergessener Parameter im SVERWEIS ist gefährlich
Auf diesen Parameter kann man nur dann verzichten, wenn die zu durchsuchende Tabelle aufsteigend sortiert ist. In allen anderen Fällen muss der dritte Parameter auf FALSCH (= exakte Übereinstimmung) gesetzt werden:
FALSCH ist fast immer richtig!
Merke:
In 99,9% der Fälle ist beim SVERWEIS FALSCH richtig. Klingt komisch, ist aber so.
Dritte Einschränkung: Die Funktion kann immer nur einen Ergebniswert zurückliefern. Wenn man beispielsweise zu einer gesuchten Kundennummer sowohl den Vornamen als auch den Nachnamen ausgeben möchte, muss man zwei angepasste SVERWEIS-Funktionen verwenden:
Mehrere Werte erfordern mehrere Formeln
Bei zwei Feldern geht das ja noch, wenn jedoch neben den Namen auch noch die Adresse und drei weitere Felder benötigte werden, kann das schon etwas mühsam sein.
Vierte Einschränkung:
Wenn der SVERWEIS kein Ergebnis findet, liefert er den unschönen Fehlerwert #NV! zurück. Möchte man den vermeiden, muss die Formel mit der zusätzlichen WENNFEHLER-Funktion umschlossen werden.
Und zu guter Letzt durchsucht der SVERWEIS nur die erste Spalte und liefert dann einen Wert aus einer Zelle daneben. Hat man jedoch einen vertauschten Tabellenaufbau und muss die erste Zeile durchsuchen und möchte den Wert aus einer Zelle darunter, muss man auf eine andere Funktion zurückgreifen. Nämlich den selteneren WVERWEIS.
Aber jetzt kommt die gute Nachricht:
Der neue XVERWEIS hat keine dieser Einschränkungen! Und er kann noch einiges mehr.
Bevor ich dir zeige, wie genial diese neue Funktion ist, muss ich auf die einzige echte Einschränkung hinweisen. In den Genuss dieser Funktion kommen nur Anwender von Office 365. Wer den monatlichen Kanal von Office 365 nutzt, hat diese Funktion jetzt schon zur Verfügung, für Anwender des halbjährlichen Kanals wird sie ab Juli 2020 ausgerollt.
Version und Kanal prüfen
Wer jedoch überhaupt kein Office 365 einsetzt, muss auf den XVERWEIS leider ganz verzichten.
Darf ich vorstellen: Der XVERWEIS!
Nun aber zum XVERWEIS (die Datei mit den folgenden Beispiele kannst du dir hier herunterladen). Die allgemeine Syntax lautet so:
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht gefunden]; [Vergleichsmodus]; [Suchmodus])
Die erste Besonderheit ist, dass anders als beim SVERWEIS hier zwischen einer Suchmatrix (also der zu durchsuchenden Spalte) und einer Rückgabematrix (also der Spalte mit den gewünschten Ergebnissen) unterschieden wird. Und damit wird bereits der größte Schwachpunkt des SVERWEIS überwunden, denn man kann nun auch Ergebnisse aus Spalten zurückgeben, die links von der Suchspalte liegen:
XVERWEIS in seiner einfachsten Form
Wer sich das Beispiel im Bild oben genauer ansieht, dem wird auffallen, dass ich hier auf den im SVERWEIS fast immer notwendigen FALSCH-Parameter verzichtet habe. XVERWEIS geht nämlich schlauerweise davon aus, dass nach einer genauen Übereinstimmung gesucht werden soll, wenn der optionale Parameter „Vergleichsmodus“ nicht angegeben ist!
Integrierte Fehlerbehandlung
Sehr praktisch ist auch der optionale dritte Parameter. Damit kann man festlegen, was passieren soll, wenn der gesuchte Wert nicht gefunden wurde:
Die integrierte Fehlerbehandlung
Die beim SVERWEIS notwendige zusätzliche WENNFEHLER-Funktion ist hier also überflüssig!
Große Flexibilität beim Vergleichsmodus
Wie wir ja schon gesehen haben, wird beim Weglassen des Vergleichsmodus-Parameters eine exakte Übereinstimmung angenommen. Darüber hinaus sind aber noch weitere Möglichkeiten für den Vergleichsmodus gegeben.
- 0 = Exakte Übereinstimmung. Gilt auch, wenn der Parameter weggelassen wird
- -1 = Exakte Übereinstimmung oder nächst kleineres Element
- 1 = Exakte Übereinstimmung oder nächst größeres Element
- 2 = Platzhalterzeichenübereinstimmung
So lassen sich mit dem Wert 1 beispielsweise ganz problemlos Staffelpreislisten realisieren:
Verschiedene Vergleichsmethoden
Und mit dem Wert 2 ist sogar noch eine Joker-Suche möglich. Angenommen, wir wissen nicht die genau Schreibweise des Verkäufers Maier. Dann setzen wir einfach zwei Fragezeichen als Platzhalter ein und verwenden den Wert 2 im Parameter für den Suchmodus:
Suche mit Joker-Zeichen
Ein Fragezeichen steht dabei für genau ein beliebiges Zeichen. Für eine beliebige Anzahl von beliebigen Zeichen kann auch der Stern (*) eingesetzt werden.
Horizontale Suche
Bisher war, wie eingangs schon beschrieben, für eine horizontale Suche die WVERWEIS-Funktion notwendig. Darauf kann man beim XVERWEIS verzichten, denn der kann in alle Richtungen arbeiten:
XVERWEIS ersetzt auch WVERWEIS
Horizontale und vertikale Suche
Und wem das noch nicht reicht, kann durch zwei verschachtelte XVERWEIS-Funktionen seine Tabelle auch in beide Richtungen durchsuchen:
Suche in beide Richtungen
Eine einzige Formel liefert mehrere Werte
Anders als SVERWEIS, der immer nur einen Rückgabewert liefert, kann der XVERWEIS in einem Rutsch gleich mehrere Felder zurückgeben:
Eine Formel liefert mehrere Werte
Hier kommt eine spezielle Funktionalität zum Tragen, die auch in anderen neuen Excel-Funktionen Einzug hält: Die sogenannten dynamischen Arrays. Einen kleinen Einblick dazu hatte ich schon ein zwei anderen Artikeln gegeben:
Ich hoffe, die gezeigten Beispiele haben dir einen kleinen Vorgeschmack auf das gegeben, was mit dieser wunderbaren Funktion alles möglich ist. Natürlich muss man, wie generell bei jeder neuen Funktion die fehlende Abwärtskompatibilität beachten. Wenn du also in einer Firmenumgebung arbeitest, die mit unterschiedlichen Excel-Versionen arbeitet und Dateien mit Kollegen austauschen musst, solltest du sehr vorsichtig sein und besser noch auf den XVERWEIS verzichten.
In einer homogenen Office-365-Landschaft oder als Einzelkämpfer wirst du jedoch den SVERWEIS wahrscheinlich demnächst in die Tonne treten 🙂
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Herzlichen Dank für die bisherigen 250 Newsletter. Ich habe bisher alle von denen gelesen und auch als nicht Excedl-Anfänger kann man immer was rausnehmen… Klasse Tipp zum XVERWEIS. Ich bin begeistert….
Viele Grüße Peter
Hallo Peter,
dankeschön für das nette Feedback!
Schöne Grüße,
Martin
Super, herzlichen Dank! Der Xverweis allein ist ja schon klasse, aber mit Deiner Tabelle und den Beispielen konnte ich ohne Probleme nachvollziehen, wie der Xverweis abläuft, ein extra Beschrieb war bei so einem guten Beispiele-Aufbau nicht nötig …..
Herzliche Gratulation zum 250 x Newsletter!
Freue mich auf die nächsten 🙂
Herzliche Grüsse, Barbara
Hallo Barbara,
vielen Dank, ich werde hart an den nächsten 250 arbeiten 🙂
Schöne Grüße,
Martin
Herzlichen Glückwunsch zum 250ten Newsletter. Ganz lieben Dank für die viele Mühe, die Du Dir machst.
Habe schon die meisten meiner Programme durch XVERWEIS vereinfachen können. Einfach Super, vor Allem Deine Beispiele und Erläuterungen.
Bitte, bitte weiter so!!!!!!!
Micha
Hallo Micha,
das freut mich sehr, dass die Artikel gut ankommen. Und natürlich wird es weitergehen 🙂
Schöne Grüße,
Martin
Hallo Martin, Glückwunsch zum 250. Newsletter. Du hast damit meine Excel-Phobie geheilt und ich bin sogar Pivot-Fan geworden. Weiter so 🙂
Der neue XVERWEIS ist klasse. Vielen Dank dafür und auch für die Beispieldatei zum Üben. Eine echte Verbesserung!
Viele Grüße
Annette
Hallo Annette,
eine geheilte Excel-Phobie und ein Pivot-Fan: was will ich mehr!
Vielen Dank für dieses schöne Feedback 🙂
Martin
Guten Tag Herr Weiß,
der XVERWEIS wird sicherlich der Zukunft gehören.
Ob dieser bereits fehlerfrei funktioniert oder doch noch gewisse Kinderkrankheiten hat erscheint mir fraglich.
Somit wäre dies sicherlich ein Grund um vorerst einmal darauf zu verzichten.
Hinzu kommt sicherlich auch das nicht dieser nur unter Office 365 zur verfügung steht, allerdings abhängig vom jeweiligen Abonnentenmodel. Sofern dies ausschließlich zur Eigenbedarf eingesetzt wird könnte dies Sinn machen.
Sofern aber Daten und Excel-Dateien weiter gegeben werden sollen wäre der Wechsel zum XVERWEIS sicherlich verfrüht.
Hallo Herr Koulen,
für etwaige Kinderkrankheiten sehe ich jetzt das Risiko persönlich als nicht so hoch. Aber ich geben Ihnen beim zweiten Punkt absolut Recht: in dem Moment, wo man Daten weitergeben muss und beim Empfänger keine Office-365-Umgebung erwarten kann, muss man auf diese Funktion aus Kompatibilitätsgründen definitiv verzichten. Und das wird sicherlich noch eine ganze Weile der Fall sein.
Schöne Grüße,
Martin
Genial!
Vielen Dank für Bekanntschaft mit dem XVERWEIS.
Optimale Ausstattung für die Zukunft gesichert.
Hi Konstantin,
gern geschehen 🙂
Schöne Grüße,
Martin
Hallo Martin,
für alle, die kein Office365 haben: ich habe ich mir mal vor einiger Zeit eine Alternative gebastelt.
{=INDEX(wertematrix;
MAX((2*(suchmatrix=suchwert)-1)*(ZEILE(suchmatrix)-(ZEILE(suchmatrix_zelle1)-1)));
MAX((2*(suchmatrix=suchwert)-1)*(SPALTE(suchmatrix)-(SPALTE(suchmatrix_zelle1)-1))))}
Der Ausdruck sucht einen in und liefert den korrespondierenden Wert aus zurück. Falls nicht existiert, wird #WERT zurückgegeben. Nicht vergessen: die Formel muß wie angedeutet als Matrixformel mit SHIFT-CTRL-ENTER eingegeben werden. Das Argument in den MAX-Funktionen erzeugt eine Matrix, die überall -1 enthält, nur an der Position des steht dessen Zeilen# bzw. Spalten#. MAX liefert dann genau diese Zeilen#/Spalten# für INDEX zurück. ZEILE(suchmatrix_zelle1)-1 berechnet den Offset von im Blatt. 2*(suchmatrix=suchwert)-1) erzeugt die „-1“-Werte, die benötigt werden, wenn nicht vorhanden ist. Eine „0“ wäre einfacher, tut’s aber leider nicht (s. die Beschreibung von INDEX in der Online-Hilfe). Ich hoffe, die kurze Beschreibung machts verständlich. Deine tollen Animationen kann ich leider nicht erzeugen.
Ist zugegeben nicht so mächtig wie XVERWEIS, aber wenn man nichts besseres hat… Vielleicht hat Microsoft ja irgendwann ein Einsehen und stellt XVERWEIS allgemein zur Verfügung.
Da hat sich der Fehlerteufel eingeschlichen: jetzt ist es korrigiert
Hallo Martin,
für alle, die kein Office365 haben: ich habe ich mir mal vor einiger Zeit eine Alternative gebastelt.
{=INDEX(wertematrix;
MAX((2*(suchmatrix=suchwert)-1)*(ZEILE(suchmatrix)-(ZEILE(suchmatrix_zelle1)-1)));
MAX((2*(suchmatrix=suchwert)-1)*(SPALTE(suchmatrix)-(SPALTE(suchmatrix_zelle1)-1))))}
Der Ausdruck sucht einen suchwert in suchmatrix und liefert den korrespondierenden Wert aus wertematrix zurück. Falls suchwert nicht existiert, wird #WERT zurückgegeben. Nicht vergessen: die Formel muß wie angedeutet als Matrixformel mit SHIFT-CTRL-ENTER eingegeben werden. Das Argument in den MAX-Funktionen erzeugt eine Matrix, die überall -1 enthält, nur an der Position des steht dessen Zeilen# bzw. Spalten#. MAX liefert dann genau diese Zeilen#/Spalten# für INDEX zurück. ZEILE(suchmatrix_zelle1)-1 berechnet den Offset von im Blatt. 2*(suchmatrix=suchwert)-1) erzeugt die „-1“-Werte, die benötigt werden, wenn nicht vorhanden ist. Eine „0“ wäre einfacher, tut’s aber leider nicht (s. die Beschreibung von INDEX in der Online-Hilfe). Ich hoffe, die kurze Beschreibung machts verständlich. Deine tollen Animationen kann ich leider nicht erzeugen.
Ist zugegeben nicht so mächtig wie XVERWEIS, aber wenn man nichts besseres hat… Vielleicht hat Microsoft ja irgendwann ein Einsehen und stellt XVERWEIS allgemein zur Verfügung.
Hallo Reinhard,
vielen Dank für diese Alternative und die Erläuterungen dazu!
Schöne Grüße,
Martin
Vielen Dank für die sehr gut aufgebauten Beispiele, die eine weitere Beschreibung grundsätzlich nicht erfordern. Trotzdem ist es immer wieser interessant, etwas „drumherum“ zu kennen. XVERWEIS ist wirklich eine wahnsinnige Erleichterung!!
Vielen Dank für das tolle Feedback!
Schöne Grüße,
Martin
Hallo Martin,
auch von mir herzlichen Glückwunsch zu deinem Newsletter Jubiläum 😉 Dieser Newsletter verdeutlicht mal wieder, Excel schläft nie!!! Eine sehr gut gelungene Einführung in eine neue Funktion. Man lernt auf deiner Seite nie aus.
Viele Grüße, Harald.
P.S. Schade das die neue Funktion nur unter Office 365 funktioniert!
Hallo Harald,
vielen Dank für deine schönes Feedback! (und ja, ich lerne selbst immer viel Neues dazu…)
Schöne Grüße,
Martin
Hy
Ich habe ein Problem
Ich habe mittels Sverweis Kundennummern mit Geräten verbunden.
Und jetzt möchte ich die Kundennummer mit Geräten und den Gerätenamen anzeigen. In einer Funktion. Ein Kunde hat mehrere Geräte mit mehreren Gerätenamen.
=SVERWEIS(J24;gesamt!$A$8:$GX$1805;44;0) so sieht die Formel aus, jetzt komme ich aber nicht weiter.
LG Katrin
Hallo Katrin,
mit einem SVERWEIS kann immer nur der erste gefundene Treffer (also das erste Gerät) ausgegeben werden. Um mehrere/alle Geräte zu einer Kundennummer zu finden, musst du entweder auf die FILTER-Funktion zurückgreifen (nur in Microsoft 365/Office 365 verfügbar). Oder du gehst wie in diesem Artikel beschrieben vor:
https://www.tabellenexperte.de/besser-als-sverweis-alle-werte-finden/
Schöne Grüße,
Martin
Hallo Martin,
ich finde deine Newsletter verständlich und Tipps büroalltagstauglich 🙂
Ich finde allerdings keine Lösung für mein „Problemchen“ – Lösung aus Kombi Index und Sverweis funktioniert, jedoch habe ich 150 Suchkriterien. Ich kann doch nicht in einer Zelle 150 Index/Sverweis-Formeln pflegen.
Ich habe 2 Datenbanken:
in Nr. 1 Kosten je KST : Spalte A – Konten
Zeile B1:MV1 – Kostenstellen
in Matrix A1:MV600 sind die Beträge der Kostenstellen auf den jeweiligen Konten
in Nr. 2 Kostenstellenübersicht : Spalte A – Kostenstellen
Zeile B1:IB1 – Medien
in Matrix A1:IB300 Schlüssel (Verteilung) der Kostenstellen nach Medien
als Ergebnis hätte ich gern in Spalte A die Konten und Spalte B1:IB1 Medien
in der Matrix sollten die geschlüsselten Beträge der Kostenstellen auf Medium in dem jeweiligen Konto.
Wie gesagt, über sverweis würde ich den Schlüssel für die Kostenstelle finden und über die INDEX-Formel das Konto und den zu schlüsselnden Betrag – der Produkt wäre das Ergebnis, aber eben nur für 1 Kostenstelle.
hast du was in der Trickskiste?
Danke im voraus und liebe Grüße
M.C.
Hallo Monika,
ich kenne jetzt deine Tabelle nicht im Detail, aber mein Tipp wäre Power Query. Damit würde ich die beiden Kreuztabellen entpivotieren. Dann hättest du eine flache Tabelle mit allen Kombinationen Konten/Kostenstellen und eine zweite flache Tabelle mit Kostenstelle/Schlüssel. Beide Tabellen könnte man dann vermutlich über einen Join zusammenfassen und daraus eine Pivot-Tabelle erstellen.
Wie gesagt: Ich kenne die Details nicht, aber in diese Richtung würde ich es mal versuchen.
Schöne Grüße,
Martin
Auch von mir ein großes Lob!
Vielen Dank für die Mühe 🙂
Vielen Dank, sehr gern geschehen!
Schöne Grüße,
Martin
Hallo
Super Funktion, vielen Dank für die einfache Erklärung, es gibt aber ein Problem
Bei DropDown Listen als Kriterium funktioniert die korrekte Rückgabe des Wertes nicht.
Wenn ich also in Ihrem Beispiel aus Zeile A3 eine DropDown Liste der gesamten Kriterien mache (E3:E14), müsste sich je nach Auswahl des Kriterium der zugehöriger Wert verändern. (so ist zumindest meine Annahme) das passiert aber nicht.
Viele Grüße
Andrew
Hallo Andrew,
ich nehme an, Sie beziehen sich auf das erste Beispiel zum XVERWEIS oben im Artikel. Es spielt grundsätzlich keine Rolle, ob sie den Buchstaben in Zelle A3 direkt eingeben oder aus einer Dropdownliste auswählen. Die XVERWEIS-Funktion daneben in Zelle B3 greift sich den Buchstaben in A3 und liefert dann das entsprechende Ergebnis zurück. Wenn das bei Ihnen nicht der Fall ist, stimmt entweder etwas mit der Formel nicht. Oder Sie haben vielleicht die automatische Neuberechnung versehentlich deaktiviert (einfach mal nach der Eingabe die Funktionstaste F9 drücken, dann wird in jedem Fall neu berechnet).
Schöne Grüße,
Martin
Hallo,
wie immer ein super Newsletter, vielen Dank dafür.
Ich empfehle diese Seite auch jedem weiter! 🙂
Der XVERWEIS ist wirklich genial und macht es deutlich einfacher, als der SVERWEIS.
Ein gutes Beispiel dafür ist, dass man mit dem XVERWEIS beim Suchkriterium auf mehrere Felder Bezug nehmen kann (indem die Suchparameter mit „&“ verbunden werden).
Das war beim SVERWEIS zwar auch schon möglich, hier musste dann aber noch mit der WAHL-Funktion gearbeitet werden.
Da beim XVERWEIS die Such und Ausgabematrix getrennt eingegeben wird, entfällt die WAHL-Funktion.
So kann man mit der Formel
=XVERWEIS(I2&H2;A:A&B:B;E:E)
die Spalten A&B nach den Parametern, die in I2 und H2 eingetragen werden, durchsuchen und als Ergebnis den entsprechenden Eintrag aus der Spalte E ausgeben.
Das hat mich total begeistert, zumal es dank der getrennten Eingabe von Such- und Rückgabematrix auch völlig egal ist, in welcher Spalten-Reihenfolge die Suchkriterien in der Matrix stehen.
Viele Grüße
Toshi Abt
Hallo Toshi,
danke für dein schönes Feedback und für die Weiterempfehlung 🙂
Ja, der XVERWEIS ist wirklich eine feine Sache. Die einzige Schwierigkeit ist, dass man sich nach jahrzehntelangem SVERWEIS erst daran gewöhnen muss, ihn auch wirklich einzusetzen.
Schöne Grüße,
Martin
Gerne 🙂
Vielleicht an der Stelle noch eine Frage: Beim SVERWEIS war es möglich, mit Hilfe von VERGLEICH, den gewünschten Spaltenindex zu dynamisieren.
Im XVERWEIS beziehe ich mich bei der Ausgabematrix ja nicht mehr auf eine Spaltenzahl, sondern gebe die Spalten in Form von z.B. „A:A“ an.
Das kann ich ja dann nicht dynamisieren, oder kennst du da auch einen Trick? 🙂
Gruß
Toshi
Hallo Toshi,
ja, der Trick geht über die INDIREKT-Funktion und über formatierte Tabellen. Das heißt, die zu durchsuchende Tabelle wird als erst formatierte Tabelle angelegt.
Angenommen, der Name der gewünschten Ausgabespalte steht in Zelle A1:
=XVERWEIS(L8;Tabelle1[Auszahlungs-ID];INDIREKT(„Tabelle1[„&A1&“]“))
Schöne Grüße,
Martin
Hallo Peter,
danke für den super Beitrag. Du schreibst ja auch, dass sogar horizontale und vertikale Suche zeitgleich möglich ist und gibst dafür ein Beispiel. Weißt du ob es auch möglich wäre, wenn man bei deinem Beispiel in der Spalte neben dem Verkäufer auch noch ein Produkt stehen hätte z.B. Huber | Bananen | 500,00€ ob man dann bei deinem Ergebnisbereich zusätzlich neben Verkäufer, Monat und Umsatz auch noch das Produkt mit ausgeben kann? Also das wäre dann quasi ein drittes Suchkriterium.
Danke und ein schönes Wochenende
Hallo Xaver,
ich heiße zwar Martin und nicht Peter, aber das macht ja nichts 😉
Ja, auch ein drittes Kriterium wäre möglich. Wenn das Produkt in einer neuen Spalte steht, kann man die Suchkriterien und die Suchmatrix für den ersten XVERWEIS einfach mit einem &-Symbol kombinieren.
Zum Beispiel so:
=XVERWEIS(A2&C2;$F$2:$F$8&$G$2:$G$8;XVERWEIS(B2;$H$1:$M$1;$H$2:$M$8))
Schöne Grüße,
Martin
Hallo Martin,
ein wirklich klasse Beitrag! Schön kurzweilig mit kurzen und verständlichen Beispielen. Ich nutze den sverweis jeden Tag, und musste nun – fast beschämend feststellen, dass ich den xverweis in meiner Excelversion habe. Da wird doch gleich mal umgesattelt und fleißig probiert. Vielen Dank und weiter so, Gruß (ebenfalls) Martin
Hallo Namenskollege,
kein Grund zur Scham, da bist du sicherlich nicht der Einzige. Der XVERWEIS ist bei vielen Anwendern noch nicht so richtig bekannt. Aber der Umstieg lohnt sich auf jeden Fall!
Schöne Grüße,
Martin
Hallo,
ich bin Lehrerin und arbeite gerade meine Vorbereitung von Sverweis auf Xverweis um. Und gleich stoße ich auf ein Problem, das mir niemand erklären kann.
Ich mache eine Schularbeitenauswertung. Die Schüler erhalten die Note aufgrund der erreichten Punkte.
Suchmatrix: 0 bis 20 Nicht genügend, 21 bis ….
Nun gibt es aber auch Schüler, die nicht mitgeschrieben haben, bei ihnen steht daher anstatt der Punkte ein NB für nicht beurteilt.
Bei dieser Lösung erhält der Schüler ein Sehr gut anstelle eines Nicht beurteilt: =XVERWEIS(D19;$E$12:$E$16;$G$12:$G$16;“Nicht beurteilt“;-1)
Arbeite ich mit den Bis-Punkten, die Matrix beginnt also mit 20 klappt alles perfekt.
=XVERWEIS(D19;$F$12:$F$16;$G$12:$G$16;“Nicht beurteilt“;1)
Wie soll ich meinen Schülern das erklären? Wieso wird der Text NB bei der ersten Lösung nicht als falscher Eintrag erkannt?
Besten Dank für Tipps. Liebe Grüße vom Bodensee Eva
Hallo Eva,
ich habe mal versucht, dein Beispiel nachzubauen. Und ich gehe davon aus, wenn der Schüler nicht mitgeschrieben hat, dann bleibt die Eingabezelle D19 leer, richtig?
Die Matrix wird standardmäßig von oben nach unten durchsucht. Beim Suchmodus -1 wird also bis zur höchsten Punktezahl alles nach einem leeren Eintrag durchsucht. Da es keinen leeren Eintrag in der Matrix gibt, wird am Ende der nächst kleinere Wert genommen, was eben die 51 und damit ein „sehr gut“ ist.
Beim Suchmodus 1 hingegen wird am Ende der Matrix der nächst größere Wert vom letzten Eintrag (in meinem Bild 60) aus gesehen genommen. Einen größeren Wert gibt es aber nicht, da 60 bereits der letzte Wert in der Liste ist. Daher greift der Text „Nicht beurteilt“.
Du kannst auch die Gegenprobe machen:
Falls du in der Matrix den höchsten Wert in der bis-Spalte leer lässt, dann wird auch beim Suchmodus 1 unerwünschter Weise ein „sehr gut“ erscheinen. Denn dann gibt es ja einen leeren Eintrag.
Ich hoffe, damit wird es ein wenig klarer.
Schöne Grüße,
Martin
Vielen Dank für die umfassende Antwort. Ich seh immer mehr: Der Xverweis ist nicht nur ein ausgebauter Sverweis nach links. Die Funktion ist so vielfältig, dass ich meine gesamte Unterrichtsvorbereitung komplett umarbeiten muss. Das wird mich und meine Schüler noch eine Weile beschäftigen.
Herzliche Grüße Eva
Hallo,
ich beim Probieren mit Zeitschwellwerten auf folgenden Excel-Spezialeffekt gestoßen.
In meiner Tabelle wollte ich das die Messung mindestens 2 Stunden nach der Start erfolgt.(Endzeit minus Startzeit muss größ0er als 2:00 sein)
Hat nicht geklappt-weil Excel ja mit Bruchstücken vom Tag rechnet.
Dann habe ich die Rechenergebnisse in Kommazahlen umformatiert und sie da, wenn ich 10 Uhr 8 Uhr „abziehe“ kommt einen andere Zahl raus wie der Subtraktion 8:00 minus 06:00 Uhr..
Ich habe keine Erklärung wieso?
Können Sie mir helfen?
Excelversion 2013, Windows10
04:00 06:00 0,08333333333333330
06:00 08:00 0,08333333333333330
08:00 10:00 0,08333333333333340
10:00 12:00 0,08333333333333330
12:00 14:00 0,08333333333333300
14:00 16:00 0,08333333333333290
16:00 18:00 0,08333333333333300
18:00 20:00 0,08333333333333300
20:00 22:00 0,08333333333333290
22:00 00:00 0,08333333333333300
00:00 02:00 0,08333333333332990
02:00 04:00 0,08333333333334010
04:00 06:00 0,08333333333332990
06:00 08:00 0,08333333333333990
08:00 10:00 0,08333333333333020
10:00 12:00 0,08333333333332990
12:00 14:00 0,08333333333332990
14:00 16:00 0,08333333333333020
16:00 18:00 0,08333333333333990
18:00 20:00 0,08333333333332990
20:00 22:00 0,08333333333333020
22:00 00:00 0,08333333333331990
00:00 02:00 0,08333333333332990
Hallo Armin,
das Problem hier ist, dass Excel generell eine maximale Genauigkeit von 15 Stellen hat. Daher ist alles, was nach der 15 Stelle kommt als Datenmüll zu betrachten. Wie diese Werte allerdings genau zustande kommen, kann ich Ihnen leider auch nicht sagen.
Schöne Grüße,
Martin
man könnte den Hinweis ab welcher Version man die Funktionnützen kann auch irgendwie Userfreundlich am Anfang des Beitrages einbauen, oder man lässt in hier bewusst weg und versteckt ihn im Beitrag wie ein Osterei 🙂
Hallo Hugo,
ja, das könnte man, danke für den Hinweis.
Schöne Grüße,
Martin
Hallo Martin,
XVERWEIS Suuuuuuuuuuuuuuuuuuuuuuuuuuper
Ich liebe inzwischen die Funktion ABER ich habe noch einen Hänger 🙁
Ich suche einen bestimmten Eurowert z.B. 175,99 € in zwei Spalten die, die als Min und Max Wert 175,10 und 181,00 haben. Um dann den Wert aus der danebenliegenden Spalte zurückzubekommen.
Hab keine Idee dazu.
IST Min Max Output
175,99 € 178,10 € 181,00 € 12,00%
Hast Du mir eine Lösung ?
Herzlichen Dank und weiter so. In meinen jungen Jahren hieß es Excel = Hausfrauenprogramm. Inzwischen müssten die Damen und Herren in der Küche aber verdammt fit sein. 😉
Gruß Christoph
Das Problem saß mal wieder VOR dem Bildschirm! Problem gelöst!
Ist ja nichts anderes wie dein „Staffelpreis“ Tabelle.
Gruß Christoph
?
Noch etwas: SVERWEIS kann nur maximal 255 Zeichen vergleichen. XVERWEIS hat diese Einschränkung nicht.
Hallo Bernd,
danke für die Ergänzung, das war mir auch nicht bewusst.
Schön Grüße,
Martin
Uiii. Aber Achtung – Das scheint nach meinen ausgiebigen Tests nicht zu 100% sicher zu funktionieren. Seltsam. Manchmal wird der Treffer nicht gefunden, wenn der Vergleichstext mehr als 255 Zeichen hat. Blöd. Ich bin jetzt erst mal zurück zu VBA bei >255 Zeichen.