Eine meiner Lieblingsfunktionen in Excel ist zweifellos der SVERWEIS. Wer dieses universelle Werkzeug einmal verstanden hat, kann sich nicht mehr vorstellen, jemals ohne diese Funktion ausgekommen zu sein:
Suchkriterium eingeben, zu durchsuchenden Tabellenbereich eingeben, gewünschte Ergebnisspalte festlegen, fertig!
Was macht man aber, wenn man statt einem Suchkriterien eigentlich zwei in seiner SVERWEIS-Funktion bräuchte? Oder drei?
Geht nicht, glaubst du?
Geht doch! Und zwar mit dem „Super“-SVERWEIS.
Der „normale“ SVERWEIS
Für diejenigen unter meinen Lesern, die mit dem SVERWEIS nicht ganz so vertraut sind, kommt hier ein kleines Anwendungsbeispiel (die Datei kannst du dir hier herunterladen).
Ich habe eine kleine Tabelle mit den Einwohnerzahlen Deutschlands, aufgegliedert nach Altersgruppen:
Die Ausgangstabelle
Nun möchte ich in Zelle F2 eine beliebige Altersgruppe eingeben und Excel soll mir dazu die passende Einwohnerzahl liefern. Ein Paradebeispiel für den SVERWEIS!
=SVERWEIS(Suchkriterium; Matrix; Spalte; Übereinstimmung)
Das Suchkriterium ist die Altersgruppe, die ich in Zelle F2 eintrage (um Fehleingaben auszuschließen, habe ich die gültigen Werte in einer Dropdownliste hinterlegt).
Die Matrix, also der Tabellenbereich, den ich nach meinem Suchkriterium durchsuchen möchte, ist der Bereich A2:B21. Wichtig beim SVERWEIS ist, dass das Suchkriterium immer nur in der ersten Spalte gesucht werden kann.
Die Einwohnerzahl steht in der zweiten Spalte meiner Matrix, daher lautet mein dritter Parameter „2“.
Und da ich nur an einer exakten Übereinstimmung mit meinem Suchkriterium interessiert bin, lautet der letzte Parameter FALSCH (alternativ kann die Zahl 0 eingeben werden). Hier also die fertige Formel:
Der einfache SVERWEIS
Damit erhalte ich folgendes Ergebnis:
Das gewünschte Ergebnis
Wenn ich die Altersgruppe in F2 ändere, liefert mir Excel sofort die dazu passende Einwohnerzahl:
Automatische Neuberechnung
Soweit, so gut.
Etwas kniffeliger wird es, wenn man zwei Suchkriterien hat, wie z.B. Altersgruppe und Geschlecht. Meine leicht abgewandelte Tabelle sieht nun so aus:
Die neue Ausgangstabelle
Wie lässt sich diese Aufgabe lösen?
Option 1: Die Hilfsspalte
Da man im SVERWEIS nur ein Suchkriterium angeben kann, wäre die naheliegendste Option, mit einer Hilfsspalte zu arbeiten. So könnte ich die beiden Spalten mit der Altersgruppe und dem Geschlecht in einer Spalte zusammenfassen:
Option 1: Die Hilfsspalte
Folglich muss ich auch meine beiden Suchkriterien in einer Hilfszelle zusammenfassen, bevor ich den SVERWEIS anwenden kann:
Vorbereitung des Suchkriteriums
Und erhalte auf diesem Weg wieder mein gewünschtes Ergebnis:
Der SVERWEIS mit Hilfsspalte
Ergebnis
Aber Hilfsspalten sind eben nicht sehr elegant und blähen die Ursprungstabelle unnötig auf. Mit Hilfe eines cleveren Tricks können wir auf dieses unschöne Hilfskonstrukt verzichten!
Option 2: Der Super-SVERWEIS
Auf diese Anwendungsmöglichkeit bin ich übrigens erstmals bei meinem großen Excel-Vorbild Chandoo aufmerksam geworden.
Dazu kombinieren wir den SVERWEIS mit der WAHL-Funktion, geben das ganze als Matrix-Formel ein (d.h. wir schließen die Eingabe mit STRG+Umschalt+ENTER ab) und erhalten (Trommelwirbel!) folgendes Ergebnis:
Der „Super“-SVERWEIS
Wie funktioniert nun diese „magische“ Formel?
Zerlegen wir das schöne Stück in seine Bestandteile. Die beiden Suchkriterien werden – ähnlich, wie oben in Option 1 beschrieben – zu einem einzigen zusammengefasst. Somit lautet der erste Parameter F2&F3.
Für den zweiten Parameter, der ja die zu durchsuchende Tabelle definiert, nutzen wird die WAHL-Funktion. Allgemein gesprochen wählt man bei der WAHL-Funktion aus einer Liste von Werten mit Hilfe eines Index einen Wert aus:
=WAHL(Index; Wert1; Wert2; ...)
Bezogen auf unser Beispiel sieht das also so aus:
Die WAHL-Funktion
Für den ersten Parameter – den Index – geben wir in geschweiften Klammern die Werte 1 und 2 an, getrennt durch einen Punkt. Was bedeutet das?
In Excel-Funktionen lassen sich Wertelisten entweder als Bezug in der Form A1:A5 eingeben, oder durch die direkte Eingabe der konstanten Werte, getrennt durch ein Semikolon: Somit sind =SUMME(A1:A5) und =SUMME(10;4;5;3) gültige Eingaben.
Wenn es sich dabei um eine Matrix handelt, werden die Konstanten in geschweifte Klammern gesetzt. Spalten werden dabei durch einen Punkt getrennt, Zeilen durch ein Semikolon. So lässt sich die Tabelle auf der linken Seite wie im folgenden Bild auch in einer Kurzschreibweise darstellen:
Matrix-Konstanten
Das bringt uns zurück zu unserer WAHL-Funktion: Die Angabe von {1.2} im ersten Parameter bedeutet also, dass wir an zwei Spalten interessiert sind.
In den folgenden Parametern werden die eigentlichen Werte übergeben, wobei für den ersten Wert die Spalten A und B zusammengefasst werden (A2:A42&B2:B42) und danach kommt die Wertespalte mit den Einwohnerzahlen (C2:C42).
Ich weiß, das ist ein sehr abstraktes Konstrukt. Etwas deutlicher wird es vielleicht, wenn man sich das Ganze mit Hilfe der Formelauswertung anzeigen lässt:
Mehr Klarheit mit „Formel auswerten“
Hier sieht man die Wertepaare, die in den geschweiften Klammern ermittelt wurden:
- „0 – 5männlich“.1693000;
- „0 – 5weiblich“.1604000;
- „5 – 10männlich“.1747000;
und so weiter. Das ist also die Tabelle, die an den SVERWEIS zum Durchsuchen übergeben wird.
Der Rest der SVERWEIS-Funktion ist dann wieder bekannter Stoff: Es wird die zweite Spalte durchsucht, und es wird wieder nach genauer Übereinstimmung gesucht („FALSCH“)
Wichtig ist, dass die SVERWEIS-Funktion als Matrix-Funktion eingeben wird, also die Eingabe mit STRG+Umschalt+ENTER abgeschlossen wird.
So, damit habe ich dich für heute genug strapaziert. Nur noch soviel zum Schluß: Mit diesem „Super“-SVERWEIS lassen sich auch mehr als zwei Kritieren verwenden. Du musst nur alle Suchkriterien mit der &-Verknüpfung zusammenfassen.
Viel Spaß beim Ausprobieren!
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.
Super der SuperSverweis 🙂 Hat mir sehr geholfen! Besten Dank für die ausführliche Erklärung. Stefanie
Hallo Stefanie,
gerne geschehen und freut mich, wenn ich helfen konnte!
Schöne Grüße,
Martin
Hallo Martin,
bin gestern auf deine Seite gestoßen und schon jetzt ein großer Fan Deiner Darstellungsweise/Didaktik. Obwohl ich schon paar Jahre mit Excel zu tun hatte, habe ich in den zwei Tagen viel neues gelernt. Die Alternativdarstellung von Tabellen/Matrizen und die kluge Verwendung wie in diesem Artikel war mir vollkommen neu.
Vielen Dank!
Matthias
Hallo Matthias,
vielen Dank, freut mich sehr! Geht mir übrigens auch noch oft so: Man lernt mit Excel nie aus 🙂
Schöne Grüße,
Martin
Bei dem Thema kam mir die Frage: Gibt es eigentlich eine Funktion um die Inhalte eines Bereiches (mehrere Zeilen und Spalten) in der erwähnten Matrizen-Kurzschreibweise in eine einzelne Zelle zu schreiben/konvertieren/kodieren?
Z.B., wenn jemand Excel nutzen würde, um Rechnungen zu verfassen, und die in der Vergangenheit erstellen Rechnungen in einem Erfassungsbogen erfasst werden sollen inklusive einer Kurzform aller in der jeweiligen Rechnung auftauchenden Rechnungsposten?
Hallo Matthias,
eine interessante Frage, auf die ich momentan leider auch keine Antwort habe. Ansonsten wäre eine mögliche Lösung, über die VERKETTEN-Funktion bzw. & die entsprechenden Zellen zusammenzuführen.
Grüße,
Martin
Ich bekomme es nicht hin ;-/
Meine Tabelle besteht mittlerweile aus nur noch 3 Spalten und 2 Zeilen
In Zeile 2 steht in den Zellen A2, A3 und A4 der Text: Monat, Mitarbeiter, Urlaub genommen
In Zeile 4 steht: 1,00, Name1, 2
In Zeile 5 steht: 1,00, Name3, 5
In F2 steht 1,00
In F3 steht Name1
Die Funktion ist: {=SVERWEIS(F2&F3;WAHL({1.2};A4:A10&B4:B10;C4:C10);3;0)}
Erwartet hätte ich als Ausgabe „2“, also die Angabe, wieviel Urlaub „Name1“ im Monat „1“ genommen hat.
Bekommen tue ich aber #BEZUG!
Die Fehlerverfolgung warnt m.E. bei dem Schritt C4:C10 (wobei mir dieser auch nicht klar ist).
Was ist falsch, warum ist es falsch und was kann ich dagegen tun?
Danke!
Michael
Ohhh man. Da Ja Spalte A und B zu einem Ausdruck zusammengefasst werden, ist für sverweis die Spalte C ja die 2. Spalte und nicht die dritte.
Erledigt, sorry fürs Posten und Danke fürs lesen.
Oder eher so: sverweis zählt ab der 2. (ggf. auch 3., 4., ….) durch WAHL übergebenen Option, also ab Spalte B.
hallo,
ich bin noch Anfängerin in Excel und habe das mit dem SVERWEIS hinbekomen, dank der guten Erklärung. Nur weiß ich nicht, wie das mit dem Dropdownfeld funktioniert. Für ein Beschreibung wäre ich dankbar.
Grüße
Christine
Hallo Christine,
wie man eine einfache Dropdown-Liste erstellt, habe ich in diesem Artikel beschrieben:
Eingabemöglichkeiten einschränken
Damit solltest Du eigentlich klarkommen.
Schöne Grüße,
Martin
vielen dank, habe es hinbekommen 🙂
grüße, christine
und wieder was dazugelernt 🙂
Hallo Martin!
Wie sieht es denn aus, wenn ich deine Tabelle erweitere. In deinem Bsp. hast du die Daten von einem Land. Wie sieht es aus, wenn ich die Tabelle (Alter,Geschlecht,Einwohner) nicht nur von einem Land besitze sondern von drei oder vier Ländern. Ein weiteres Suchkriterium wäre dann „Land“. Die Tabellen würden nebeneinander stehen.
Excel müsste dann erst die richtige Tabelle(Matrix) identifizieren und dann deinen sverweis anwenden.
Gruß
Sebastian
Hallo Sebastian,
in diesem Fall wäre der relevante Tabellenbereich nicht fix, sondern könnte mit der Funktion BEREICH.VERSCHIEBEN um die jeweilige Anzahl von Spalten nach rechts verschoben werden. Zum Beispiel so:
Schöne Grüße,
Martin
hallo, ich komme via google auf Ihre Seite, weil ich mit einem Excel-Problem nicht weiter komme, da ich ganz selten damit arbeite.
In zwei Tabellenblättern stehen Mitgliedernamen; die erste TAB hat die Gesamtmitglieder, die zweite TAB nur Jubilare; diese aber ohne das Eintrittsdatum.
Jetzt will ich das Eintrittsdatum in die zweite TAB übertragen mit SVERWEIS. Ich habe aber Namen mehrmals und muß auch über den Vornamen suchen. Wie mache ich das bei EXCEL. Es muß trivial sein, damit ich es behalte 🙂 Danke für eine rasche Rückmeldung. mfG J. Aurahs
Hallo Herr Aurahs,
dieser Artikel beschreibt eigentlich ziemlich genau, wie so etwas geht. Also entweder ganz trivial mit einer Hilfsspalte, in der Sie Name und Vorname zusammenfassen. Oder – nicht ganz so trivial – mit einer Kombination von SVERWEIS und WAHL. Beide Optionen sind oben Schritt für Schritt erläutert.
Schöne Grüße,
Martin
Hallo Martin, Mit S-Verweis und Super S-Verweis bekomme ich mein Problem nicht in den Griff. Kurz: Ich suche für 30 verschiedene Kunden aus verschiedenen Pivot Listen mit Darstellung von Wochenumsätzen diese Umsätze heraus und stelle Sie auf einem Tabellenblatt zum Report zusammen. Die 30 dazu notwendigen Formeln enthalten dann die Angaben zum Jahr, Quartal und die Woche {Bsp: =GETPIVOTDATA(„EUR Extended Book Price“;Bookings!$A$8;“Week Id“;“2016 Q1 W01″;“Customer Name“;“GEOTEAM1″)}. Hier möchte ich die Wunschwoche in einer Zelle quasi als Variable vorgeben und die 30 Formeln sollen auf diese Woche W02… umgestellt werden. Also ein Teil der Formel soll als Variable auf die „Wochenzelle“ zugreifen…. Idee? Wäre super, wenn Du eine Antwort hast.
Hallo Matthias,
du kannst in der Funktion PIVOTDATENZUORDNEN (bzw. GETPIVOTDATA) ja auch mit Zellbezügen arbeiten. Zumindest bei mir mit Excel 2013 klappt das ohne Probleme. Bezogen auf dein Beispiel wäre das also z.B.
=GETPIVOTDATA(„EUR Extended Book Price“;Bookings!$A$8;“Week Id“;A1;“Customer Name“;“GEOTEAM1″).
In Zelle A1 müsste dann also „2016 Q1 W02“ stehen, oder wie auch immer die genauen Spaltenbezeichnungen bei dir lauten. Es kommt lediglich auf die exakte Bezeichnung an.
Grüße,
Martin
Danke Martin für den Tipp. Also bisher war ich damit noch nicht erfolgreich. Noch mal etwas genauer beschrieben: Die Pivots (alle in einer Datei) ändern sich jeden Tag nach Refresh. Damit ergeben sich jede Woche weitere Spalten in verschiedenen Pivot Datenblättern mit Wochenumsätzen, also aus „2016 Q1 W01“ werden in der Spalten rechts daneben neue erzeugt die dann „2016 Q1 W02“ heisst usw. In einem separaten Datenblatt (Report), jedoch in der gleichen Datei wo auch die Pivots als Datenblätter abgelegt sind, suche ich mit der beschriebenen Formel Umsätze aus verschiedenen Pivots heraus (hier stelle ich also Zahlen zusammen, aus denen dann Weiteres berechnet wird). Das Problem ist, dass ich nun in jeder neuen Woche die Formeln (und das sind locker mal 30) manuell auf die folgende Woche umstellen muss, damit mein Report die neuen Wochenumsätze erkennt. Wenn ich jetzt also irgendwo diesen immer zu ändernden Formel Bestandteil „2016 Q1 W01“ auf meine Report-Seite als einen Zellbezug schreibe wo sich alle 30 Formeln automatisch aktualisieren, wäre das die Lösung. Das klappt aber bei mir so bisher nicht. Gerne kann ich auch mal eine Datei schicken und die Felder markieren. Danke für Deine Mühe. Matthias.
Hallo Martin, hast Du noch einen Tipp für mich? Wäre Dir sehr dankbar, denn ich bin noch immer nicht weiter mit meinem Thema von oben. Gruß, Matthias.
Hallo Matthias,
tut mir leid, wenn es mit dem Tipp nicht geklappt hat. Es scheint sich hier in der Tat um eine sehr individuelle Angelegenheit zu handeln und da müsste ich wirklich tiefer einsteigen. Spontan kann ich dir also keine bessere Lösung bieten. Aber vielleicht hat ein anderer Leser hier noch eine gute Idee.
Schöne Grüße,
Martin
Hallo Matthias,
ist zwar schon etwas spät, aber hast du soetwas gesucht?
https://www.file-upload.net/download-12318239/Beispiel.xlsx.html
Ist zwar nicht mit Pivottabellen und noch nicht so hübsch, aber geht mir eher darum zu sehen, ob es das ist was du suchst. Wenn ja könnte man das optimieren 🙂
Tolle Ideen dabei! Danke! Schade nur, dass die Screenshots nicht ausreichend aufgelöst sind, nicht lesbar…
..zumindest der Screenschot mit dem Super-SVERWEIS ist nicht lesbar, die anderen schon. Ich habe ihn mir aber zusammenbasteln können, wie wohl die meisten…
Hallo Gerd,
danke erstmal für den Hinweis. Ich habe mir gerade nochmal alle Screenshots angesehen und eigentlich sind alle gut lesbar. Ich traue es mich fast nicht zu fragen, aber hast Du denn die kleinen Vorschaubilder angeklickt? Dann gibt es nämlich eine (hoffentlich) gut lesbare Vergrößerung.
Schöne Grüße,
Martin
Hallo Martin,
ich hatte gar nichts angeclickt, sondern nach dem diagonalen Durchlesen auf die gute Idee der Kombination mit WAHL gleich meine Anwendung programmiert, ohne dein Beispiel nachzuvollziehen. Ich muss auch noch dateiübergreifend arbeiten, und es kommt noch INDIREKT mit ins Spiel, d.h. es baut sich noch einiges mehr drumherum. Das ging auch fast auf Anhieb. Ich war dann aber nochmal auf der Site. Irritiert hatte mich im nachhinein dabei dann, dass einige Vorschaubilder scharf, andere unscharf dargestellt wurden. Das liegt aber wohl nur an den kruden Auflösungsverhältnissen mit der Grafikkarte. Vorschaubilder im Fließtext? Naja, schön ist das nicht gerade für die Lesbarkeit. Sparen lässt sich damit eh nichts. Fakt bleibt, dass deine Idee voll bei mir ins Konzept passte, bingo!!
Hallo Gerd,
ok, verstanden. Über Vorschaubilder kann man sicherlich streiten. Der Grund ist für mich einfach ein etwas schnelleres Laden der Seite, was insbesondere bei mobilen Geräten einen Unterschied macht. Aber wie auch immer: Es freut mich, wenn Dir der Artikel ein wenig weiterhelfen konnte.
Schöne Grüße,
Martin
Hallo Martin
Habe es jetzt genauso gemacht wie beschrieben, bekomme aber immer den Fehler #Bezug! Weiss langsam nicht mehr, was ich falsch mache.
Voran kann es liegen? Habe exakt die gleiche Formel eingegebe wie Du, aber es funktioniert nicht. Kann es an der Excelversion liegen?
Danke und Gruss
Thomas
Hallo Thomas,
ich denke nicht, dass es an der Excel-Version liegt (welche hast Du denn?).
Und Du bist ganz sicher, dass Du die Formel korrekt als Array-Formel eingegeben hast: Also ohne die geschweiften Klammern am Anfang und Ende, sondern stattdessen die Formel mit Strg+Umschalt+Enter abgeschlossen hast?
Falls Du Dir die Beispieldatei noch nicht heruntergeladen hast, dann solltest Du das nachholen. Dort ist nochmal alles enthalten.
Schöne Grüße,
Martin
Kann es sein dass das für Massendaten ungeignet ist (Performance)?
Hallo Alexander,
letztendlich stößt auch jede normale SVERWEIS-Funktion irgendwann an Performance-Grenzen. Wenn zwei Tabellen mit je 100.000 oder mehr Datensätzen gegeneinander abgeglichen werden sollen, kann eine Zwangs-Kaffepause notwendig werden.
Und ja, bei der Array-Variante wird das bedeutend früher der Fall sein.
Grüße,
Martin
Super-Sache, vielen Dank für die gute Anleitung und Erläuterung.
Gern geschehen 🙂
Schöne Grüße,
Martin
Bekommt noch jemand einen #WERT-Fehler ausgegeben? Laut EXEL-Hilfe kommt der Fehler wenn Zahlen und andere Zelleninhalte in einer Formel vermischt werden.
Aber das ist ja bei Martins Beispiel auch der Fall.
Gibt es hier ein Workaround?
Meine EXEL-Version ist 2013.
Danke im Voraus
Hallo Sven,
bist Du sicher, dass Du die Formel über STRG+Umschalt+Eingabe abgeschlossen hast? Ansonsten funktioniert es definitiv nicht.
Schöne Grüße,
Martin
Hallo Martin
Ich find deine Anleitungen super, und probier einiges aus. Diesen Super-SVERWEIS bekomme ich allerdings nicht hin. Irgendwo harkts bei mir. Ich bekomme immer #bezug ausgegeben. Wenn ich beim Spaltenindex eine 1 eingebe, bekomme ich 10 – 15männlich.
Aber irgendwie will er nicht auf Spalte C zugreifen. Bei den Berechnungsschritten auch nicht. Ich habe glaub alles so gemacht wie gesagt, mit STRG Shift Enter abgeschlossen, aber es will nicht. Die Formel sieht genau so aus wie in der Vorgabe.
Besten Dank im Voraus.
Übrigens benutze ich Excel Version 2016…
Und aussehen tut sie so:
{=SVERWEIS(F2&F3;WAHL({1.2};A2:A41&B2:B41;C2:C45);2;FALSCH)} (nach STRG-C STRG-V geschweifte Klammern am Anfang und Ende natürlich von Hand eingegeben)
Ok, habs rausgefunden…
Bei mir zeigts die Formel original so an:
{=SVERWEIS(F2&F3;WAHL({1|2};A2:A41&B2:B41;C2:C41);2;FALSCH)}
Also mit einem Backslash {1|2} anstelle von einem Punkt. Da ichs nun beim Nachbauen immer mit dem Punkt versucht habe, gings nicht. Ob das wohl an der Schweizer Tastatur liegt?
Hallo Stephen,
der Punkt wäre auf jeden Fall wichtig. Ob es da bei der Schweizer Tastatur an dieser Stelle Unterschiede gibt, kann ich nicht sagen. Mir ist aber aufgefallen, dass in deinen ersten Nachrichten die Bezüge unterschiedlich groß waren:
A2:A41
B2:B41
C2:C45
Das dürfte ebenfalls nicht sein.
Grüße,
Martin
Ja, das war mir ebenfalls aufgefallen, ist beim rumprobieren passiert. Funktioniert allerdings auch so:-)
Aber der Punkt im Index war der Punkt… Wenn ich den mit Backslash ersetze, funktionierts.
Danke
Moin Martin,
großartig erklärt, vielen Dank dafür!
Bei mir taucht nun gerade folgendes Problem auf:
In der Zelle mit der Formel wird mir die Fehlermeldung #NV angezeigt.
Wenn ich aber die Funktionsargumente-Prüfung öffne wird mir dort das richtige Ergebnis wiedergespiegelt.
In einer weiteren betroffenen Zelle wird mir ein falscher Wert angezeigt – auch hier in der Fuktionsargumente-Prüfung der richtige Wert.
Woher kann dieser Fehler kommen und wie kann ich den beheben?
Danke Dir!
Wer lesen kann…
Fehler gefunden. Es haperte am Abschluss der Formel ohne STR-UMSCHALT-ENTER
Danke nochmal für die großartige Erklärung.
Ja, ohne die magische Tastenkombination geht’s nicht 🙂
Schöne Grüße,
Martin
Vorsicht, dieser Super SVerweis kann falsche Ergebnisse liefern, wie folgendes Beispiel zeigt:
Wertematrix: (1;112), Suchwerte: 11;12. Es ist offensichtlich dass die gesuchten Werte in der Matrix nicht vorkommen. Durch die Kombination mit & entsteht aber eine neue „Hilfsspalte“ mit dem Inhalt 1112 und ein Suchwert mit demselben Inhalt 1112. Der Super SVerweis wird also einen Treffer liefern, obwohl er das nicht sollte. Dies wäre auch mit einer zusätzlichen Spalte nicht lösbar. Ein Workaraound könnte die Trennung der Werte mit einem zusätzlichen Zeichen sein, das sicher nicht als Inhalt vorkommt (z.B.“| „), falls es das gibt..
Hallo Bernd,
erwischt 🙂
Aber Du hast vollkommen Recht: Die Kombination von zwei (oder mehreren) Feldern birgt ein gewisses Risiko auf falsche Treffer. Der vorgeschlagene Workaround mit einem Sonderzeichen minimiert dieses Risiko nochmal deutlich. Vielen Dank für den Hinweis.
Schöne Grüße,
Martin
Hallo Martin,
warum löst Du das Problem nicht mit einer SUMMEWENN-Funktion?
Gruß
Rainer
Hallo Rainer,
SUMMEWENN (bzw. SUMMEWENNS) würde zwar in diesem konkreten Beispiel funktionieren, da es sich bei der gesuchten Spalte um eine Wertespalte handelt. Wenn sich dort stattdessen jedoch Text befindet, klappt es mit diesen Funktionen nicht mehr.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die super Erklärung.
Leider klappt es bei mir bisher noch nicht. Allerdings handelt es sich bei meinen Werten des ersten Parameters um Werte, die in einer Dropdown-Liste stehen. Des Weiteren sind die Werte des zweiten Parameters auf einem zweiten Tabellenblatt angegeben.
Könnte es evtl. daran liegen? Oder muss ich den Fehler woanders suchen?
Besten Dank schon mal!
Viele Grüße
Sandra
Hallo Sandra,
das sollte grundsätzlich keinen Unterschied machen. Wichtig ist nur, dass die Anzahl der Zeilen in allen Bereichen immer gleich groß ist. Vielleicht liegt hier das Problem.
Grüße,
Martin
Ist die Garantie schon abgelaufen? Kannte den SVerweis schon. lg
Hallo Dave,
tut mir, aber da bist Du tatsächlich zu spät dran. Die Garantie ist schon am 03.11.2015 abgelaufen 🙂
Schöne Grüße,
Martin
Ich habe mehrere Tabelle
„Profil“ mit einem Feld Level (Mögliche Eingträge hier A, B, C, oder D aus einem Drop-Down Feld
„Fragen_All“ mit geclusterten Fragen (Überschrift dann eine Anzahl Fragen) Jede Frage kann mit einer Drop-Down Auswahl beantwortet werden! Jede Frage hat einen oder mehrere Einträge zum Feld Level in Tabelle „Profil“ A, B, C, oder D manche auch mehrere manche auch nur A oder B. Je nach Beantwortung der Frage ergeben sich Punkte die kummuliert werden.
Ziel ist es jetzt ein dynamisches Tabellenblatt zu bauen „Dyn-Blatt“ welches je nach Selektion in Blatt Profil z.B. „A“ alle Fragen inkl. der Drop-Down-Felder und Punkte Analyse die Dyn-Blatt Tabelle kopiert.
Wenn ich die gleiche Funktion mit einem Ausblenden von Zeilen in Tabelle „Fragen_All“ lösen kann würde ich auch in einem Blatt arbeiten.
Hauptziel ist ein dynamischer Fragebogen mit der dazugehörigen Auswertung.
z.b. Auswahl „A“ dann 75 Fragen oder Auswahl „B“ dann 100 Fragen.
Hallo Stephan,
aufgrund Deiner Beschreibung scheint es sich um eine etwas komplexere Angelegenheit zu handeln, die sich hier in den Kommentaren nicht so einfach beantworten lässt. Ich schicke Dir direkt noch eine E-Mail.
Schöne Grüße,
Martin
Prima Danke!
Gruß
Stephan
Schon gelegenheit gehabt eine Mail zu schreiben?
Hallo Martin,
super Anleitung, vielen Dank. Auch die anderen Tipps hier sind übersichtlich und verständlich erklärt. Weiter so.
Allerdings hat das oben gezeigte Beispiel für meine Datei nicht funktioniert (Ergebnis #NV), obwohl es sich bis auf die Zelladressen um genau das gleiche Beispiel handelt. Sprich, es werden zwei Werte verglichen und der Dritte Wert angegeben, nur das meine Quelltabelle weiter hinten im Blatt liegt. Das sollte jedoch nicht stören.
Meine Formel schaut so aus, sie wurde auch als Array-Formel abgeschlossen (geschweifte Klammern sind da):
=SVERWEIS(B1&B2;WAHL({8.9};H2:H10&I2:I10;J2:J10);2;FALSCH)
Bei mir kommt allerdings hinzu, dass B1 und B2 (also die Quellzellen) nicht fix sind, sondern es sich um Dropdown Listen handelt. B1 ist dabei eine statische Dropdown Liste, B2 ist eine (wie von Dir hier auch beschriebene) dynamische DropDownliste (via INDIREKT) mit Bezug auf die Erste. Ich vermute, dass der Super-SVerweis deshalb nicht funktioniert.
Hast Du dafür eine Lösung?
Vielen Dank im Voraus,
Gruß Marcel
Hallo Marcel,
wie Du weiter unten geschrieben hast, bist Du ja doch noch auf eine Lösung gekommen. Warum die im Beitrag vorgestellte Variante nicht funktioniert, kann ich nicht ganz nachvollziehen. Denn auch im Beispiel arbeite ich mit Dropdown-Feldern. Aber vielleicht liegt es ja wirklich an den dynamischen Dropdown-Listen. Aber Hauptsache, Du hast eine funktionierende Lösung.
Schöne Grüße,
Martin
Hallo Martin,
ich habe die Herausforderung nun auf einem anderen Weg gelöst und zwar damit:
=INDEX(J:J;VERGLEICH(B1&B2;H1:H10&I1:I10;FALSCH)) … natürlich mit dem obligatorischen CSE-Kommando.
Dabei muss man die zu vergleichenden Matrizen jedoch immer mit der ersten Zeile (wie oben H1) beginnen lassen, sonst verschiebt sich das Ergebnis um eins nach unten.
Über eine Antwort zu meinem vorherigen Kommentar freue ich mich dennoch.
Gruß Marcel
Hallo, ich habe heute versucht ebenso aus einer Tabelle2 etwas mit Parameter in eine Tabelle1 einzulesen. Dieses habe ich mit dem Sverweis (normal) versucht und wollte angaben zu dem Excel – File und den unterschiedlichen Arbeitsblättern Parametrisieren. Habe es nicht geschafft. Gibt (und ich bin mir sicher das es sie gibt) eine Lösung. Manuell funktioniert es natürlich, da ich ja nur in das zweite Excelfile gehen muss und den Zellenbezug mit sverweis herstellen kann, aber automatisch eben nicht.
Hallo Unger,
ohne zu wissen, wie die Tabellen aussehen und was genau von wo geholt werden soll, wird eine Antwort schwierig. Wenn Du willst, kannst Du mir die Tabelle gerne per E-Mail (info@tabellenexperte.de) zusenden und ich werden einen kurzen Blick darauf werfen.
Schöne Grüße,
Martin
Hallo Martin,
ich habe eine Liste mit 90.000 Einträgen. Kann das sein, dass WAHL so viele Zeilen nicht unterstützt? Wenn ich jetzt zum Beispiel nur die ersten 200 Zeilen in meine Matrizen der Formel aufnehme, dann funktioniert es und ich bekomme ich Werte ausgegeben.
Hallo PeterK,
das kann ich so nicht bestätigen. Ich habe gerade eine Liste mit 90.000 Einträgen getestet und die Formel funktioniert bei mir einwandfrei. Vielleicht hast Du irgendwo einen kleinen Wurm mit den Bezügen reingebracht.
Schöne Grüße,
Martin
Hallo Herr Weiß,
ich bin über Google auf Ihre Seite gestoßen.
Eine Frage hätte ich noch.
Wie könnte ich die Einwohnerzahl summieren?
Angenommen ich habe zwei Zeilen
5 -10 100000
5- 10 200000
Ich mache einen SVERWEIS auf 5 -10 und möchte den Wert 300000 rausbekommen.
Mein aktuelles Statement zeigt mir nur die 100000 an.
=SVERWEIS(A1;WAHL({1.2};Sheet1!A$2:A$3170;Sheet1!V$2:V$3170);2;FALSCH)
In Sheet1 A… stehen z.B. die Werte 0-15 mehrere male. In A1 steht auch die 5-10. In Sheet 1 V… stehen die Werte 100000, 200000 etc.
Beste Grüße
Hallo Benjamin,
SVERWEIS liefert immer nur den ersten gefundenen Wert zurück. In Ihrem Fall wäre die SUMMEWENN-Funktion das geeignete Mittel.
Bezogen auf Ihr Beispiel würde das so z.B. aussehen:
=SUMMEWENN(A1:A100;“5-10″;V1:V100)
Damit werden alle Werte in Spalte V summiert, bei denen in Spalte A der Eintrag „5-10“ steht.
Wichtig ist lediglich, dass die Schreibweise des Suchkriteriums (also „5-10“) exakt so ist, wie sie auch in Spalte A vorkommen (also auf Leerzeichen etc. achten)
Schöne Grüße,
Martin
Hallo Martin,
ich bin sehr begeistert von Super-SVERWEIS. Ich habe die Funktion überall eingesetzt. Mittlerweile habe ich aber festgestellt, dass wenn der Rückgabewert größer als 255 Zeichen ist, wird nur #WERT angezeigt. Gibt es da ein work around?
Hallo Gernot,
diese Beschränkung war mir bisher noch nicht bekannt, daher kann ich mit einem Workaround leider auch nicht dienen…
Tut mir leid.
Schöne Grüße,
Martin
Hallo Martin,
ich versuche gerade deinen Super-SVERWEIS hinzubekommen, aber etwas scheint nicht zu funktionieren. Ich habe den Datensatz nicht wie du auf demselben Tabellenblatt. Ich habe extra ein Tabellenblatt „Datensatz“ für die nötigen Daten angelegt. Diese Formel benutze ich derzeit: =SVERWEIS(F2&F3;WAHL({1.2};Tabelle1!A2:A41&Tabelle1!B2:B41;Tabelle1!C2:C41);2;FALSCH)
Bei mir kommt jedoch kein Wert sondern #NV 🙁
Woran liegt das und wie kann ich das Problem beheben?
Vielen Dank im voraus!
Ali Nada
Tut mir leid, das war die falsche Formel. Ich benutze folgende Formel:
=SVERWEIS(B1&B2;WAHL({1.2};Datensatz!A2:A65&Datensatz!B2:B65;Datensatz!C2:C65);2;FALSCH)
Hallo Ali,
die Formel funktioniert grundsätzlich auch, wenn sich die Daten in einem anderen Arbeitsblatt befinden. Ich sehe also zwei Fehlermöglichkeiten:
1. Die gesuchten Daten sind tatsächliche nicht in der Referenztabelle (Tippfehler, Leerzeichen etc.)
2. Du hast die Formel nicht mit STRG+Umschalt+Eingabe abgeschlossen
Ich tippe eher mal auf Punkt 2 🙂
Schöne Grüße,
Martin
Guten Tag!
Zunächst mal, danke für die tolle Seite und die klasse Erklärungen. Das hat mri schon an mancher Stelle weiter geholfen. Auch der Super-SVERWEIS würde sich als extrem hilfreich erweisen, allerdings klemmt es noch an einer ganz bestimmten Stelle.
Meine Formel lautet: {=SVERWEIS(C7&E12;WAHL({1.2};’Drop Down Data‘!A26:A33&’Drop Down Data‘!B26:B33;’Drop Down Data‘!C26:C33);2;FALSCH)}
C7 und E12 sind DropDown-Felder, und C26:C33 auf dem Tab Drop Down Data sind Zellen mit mehrzeiligem Text darin. In dieser Konstellation erhalte ich als Ausgabe #WERT!. Ersetze ich den Text durch einfache Zahlen, funktioniert es. Es scheint also mit meinem Text zusammen zu hängen – kann ich auf diese Weise eine Zelle nicht mit Text füllen? Oder liegt das Problem in der Formatierung? Was könnte ich sonst noch falsch gemacht haben?
Vielen Dank im Voraus für jedweden Tipp. 🙂
Hallo Sascha,
das Problem liegt ziemlich sicher in der Textlänge der Einträge in Spalte C. Die Formel kann mit maximal 255 Zeichen umgehen, alles was länger ist, führt zu dem #WERT!-Fehler.
Schöne Grüße,
Martin
Hallo Martin,
ja, danke für die schnelle Antwort, das war das Problem. Nachdem ich die Texte entsprechend gekürzt habe, funktioniert es auch damit.
Leider habe ich jetzt ein neues Problem: ich habe mir selber eine Zellformatierungsfalle gestellt. Damit die Zelle, in die die Texte kopiert werden müssen, überhaupt groß genug ist, muss ich sie mit anderen verbinden. Leider streikt Excel beim Versuch, dort den Super-SVERWEIS anzuwenden mit der Meldung „Sie können keine Arrayformel über verbundene Zellen eingeben“.
Jetzt gibt es zwei Möglichkeite: entweder gibt es noch einen Trick, mit dem ich das trotzdem da unterbringen kann, oder ich muss mein Formular aufwändig neu gestalten; ich hoffe ganz feste auf einen Trick?
Nochmal danke, auch im Voraus.
Beste Grüße,
Sascha
Und noch ne dumme Frage: wenn ich beim Kopieren der Formel möchte, dass die selben Zellen (A26:A33, etc.) benutzt werden, wie müsste die Formatierung dann aussehen? $A26:A33 funktioniert irgendwie nicht, der Bereich wird beim Kopieren trotzdem dynamisch verschoben, weshalb ich dann #NV als Ergebnis kriege.
Das Problem mit der Arrayformel in verbundenen Zellen habe ich jetzt erstmal so gelöst, dass ich erst die Formel eingeb und dann die Zellen verbinde – geht das vielleicht irgendwie eleganter?
Danke!
Sascha
Hallo Sascha,
Du hast mit den Zellbezügen schon richtig begonnen, aber nicht bis zum Ende umgesetzt. Das Dollarzeichen nur am Anfang reicht nicht, es muss heißen $A$26:$A$33.
Damit sind sowohl Spalten- als auch Zeilenbezug fixiert, und zwar von Anfang bis Ende.
Schöne Grüße,
Martin
Hallo Sascha,
ich fürchte, da sieht es schlecht aus. Einen Trick hierfür kenne ich leider auch nicht.
Schöne Grüße,
Martin
Hallo Martin.
Okay, alles klar, danke nochmal für die tollen Tipps, das hat mir wirklich sehr weitergeholfen!
Schönes Wochenende,
Sascha
Hallo Sascha,
Diese Formel bringt das gleiche Ergebnis, kann aber auch in verbundenen Zellen genutzt werden:
=INDEX(‚Drop Down Data‘!$C$1:$C$33;SUMMENPRODUKT((‚Drop Down Data‘!$A$26:$A$33=E3)*(‚Drop Down Data‘!$B$26:$B$33=F6)*ZEILE(‚Drop Down Data‘!$C$26:$C$33)))
Tut mr leid, mir ist ein kleiner Fehler unterlaufen…hier die Formel, die das gleiche tut
=INDEX(‚Drop Down Data‘!$C$1:$C$33;SUMMENPRODUKT((‚Drop Down Data‘!$A$26:$A$33=C7)*(‚Drop Down Data‘!$B$26:$B$33=E12)*ZEILE(‚Drop Down Data‘!$C$26:$C$33)))
Im übrigen lässt sich so die 255 Zeichen Beschränkung umgehen
Oh, super, danke für den Tipp, das werde ich mal ausprobieren!
Hallo Martin,
Klasse Arbeit!
Dein Beispiel habe ich umgesetzt und es funktioniert auch einwandfrei.
In meinem Fall habe ich aber Daten auf 2 Reitern in Excel.
Basierend auf Werten aus dem ersten Reiter (2 Suchkriterien) und einem Wert auf dem zweiten Reiter (immer ein ‚x‘), also insgesamt 3 Suchkriterien. möchte ich ein Feld aus dem 2. Reiter auf dem ersten Reiter darstellen.
Meine Formel sieht wie folgt aus:
{=SVERWEIS(A16&B16&“x“;WAHL({1.2.3};Rohdaten!A2:A500&Rohdaten!B2:B499&Rohdaten!H2:H499; Rohdaten!A2:H499);5;FALSCH)}
Ale Ergebnis erhalte ich: #NV
Ich setze also die Werte aus den Spalten A+B (aus dem ersten Reiter) +“x“ zusammen und nehme das als Suchbegriff.
Die jeweiligen Suchwerte stehen dann auf dem Reiter 2 (Name:Rohdaten):
Spalte A, B und Spalte H (H steht das gesuchte „x“).
Ich setze als Suchbereich die spalten A bis H auf dem 2. Reiter.
Das gesuchte feld mit dem gesuchten Inhalt steht in Spalte D (=4) auf dem 2. Reiter.
Beispieldaten:
Reiter 1:
Stichtag Meilenstein Plan Start
21.12. ABCD (hier soll das Ergebnis meiner Suche rein)
Reiter 2:
Stichtag Vorgangsname Status Anfang Text13 Ende Text12 Relevant
21.12. ABCD 3 19.12.2016 16:30:00 (Feld ist leer) 20.12.2016 12:30:00 (Feld ist leer) x
Kurzer Nachtrag:
In derie Formel muss natürlich …;4;FALSCH) anstatt …;5;FALSCH) heißen, da ich den Wert aus der 4. Spalte benötige.
Gruß
Thomas
Hallo Thomas,
so wie ich es sehe, sind die Datenbereiche nicht gleich groß: Der erste Bereich geht von Zeile 2 bis Zeile 500, alle anderen von Zeile 2 bis Zeile 499. Die Bereiche müssen alle gleich groß sein, damit es funktioniert.
Schöne Grüße,
Martin
Hallo Martin,
ich habe das noch einmal geprüft. Die bereiche sind identscih von zeile 2 bsi zeile 499.
Ich habe daraufhin die Formel neu aufgebaut, zuerst ohne dem 3. Suchkriterium „x“.
Dann gibt mir die Formel auch ein Ergebnis aus.
Da die Zeile aber mehrfach vorkommen kann, benötige ich aber das Suchkriterium „x“.
Wenn ich die Formel dann umbaue:
Beispiel:
=SVERWEIS(A56&B56&“x“;WAHL({1.2.3};Übersicht!A2:A499&Übersicht!B2:B499&Übersicht!F2:F499;Übersicht!D2:D499);3;0) und diese mit STRG+SHIFT+ENTER bestätige, erhalte ich als Ergebnis:
#NV
Ich habe daraufhin einmal alle 3 Spalten, nach denen ich in dem 2. Register ‚Rohdaten‘ suche, direkt hintereinander angeordnet (Also Spalte A+B+C –> war vorher Spalte A+B+F).
Dann bekomme ich auch das gewünschte Ergebnis.
Damit passt das einwandfrei, denn die Spaltenreihenfolge kann ich über das Exportscheme aus MS Projekt selbst bestimmen.
Im Endergebnis: Diese verrückte Formel haut astrein hin (freu, freu freu).
Vielen Dank
Gruß
Thomas
Freut mich, wenn’s doch noch geklappt hat.
Schöne Grüße,
Martin
Hallo!
Vielen Dank für die schöne Erklärung der Funktion. Aber wäre für solche Operationen nicht diese Formel einfacher zu verstehen?
=SUMMENPRODUKT((F3=B:B)*(F2=A:A)*(C:C))
Ist nur so ein Gedankengang. Ich bevorzuge Summenprodukt an vielen stellen, so benutzt ist es für mich halt nur eine Anreihung von Bedingungen
Viele Grüße
Ruben
Hallo Ruben,
die Variante mit SUMMENPRODUKT ist hierfür eine ausgezeichnete Lösung. Die Funktion ist wirklich unfassbar vielseitig und führt leider oft ein Schattendasein.
Vielen Dank für den guten Tipp!
Schöne Grüße,
Martin
Hallo Martin,
das ist ein super Tipp.
Ich wollte nur nochmal anmerken, dass auch bei mir mit deiner exakten Tabelle ein #Bezug Fehler beim Super-Sverweis auftaucht.
Ein anderes Kommentar hatte erwähnt, dass der sverweis mit einem Backslash funktioniert und das ist tatsächlich so.
{=SVERWEIS(BM6&BM7;WAHL({1.2};BI6:BI13&BJ6:BJ13;BK6:BK13);2;FALSCH)} ergibt „#BEZUG!“
{=SVERWEIS(BM6&BM7;WAHL({1\2};BI6:BI13&BJ6:BJ13;BK6:BK13);2;FALSCH)} ergibt das gewollte Ergebnis.
Excel Version: 2016.
Vielleicht hat sich da ja was geändert in der neuen Version?!
Grüße
Oder aber, das liegt daran, dass der Punkt bei mir als Komma eingestellt ist.. wer weiß.
Hallo Guido,
Punkt statt Komma klingt für mich schon mal sehr gut.
Schöne Grüße,
Martin
Vielen Dank für die tolle Formel.
Allerdings liefert die Formel ein falsches Ergebnis wenn die Einwohnerzahl „LEER“ ist. Dann ist das Ergebnis 0 und nicht „LEER“.
Gibt es eine Möglichkeit, dass in diesem Fall „LEER“ zurückgegeben wird?
Hallo Benjamin,
ja, das geht. Dazu musst Du die Formel einfach noch um eine WENN-Funktion erweitern, in der Du auf das Ergebnis 0 prüfst und für diesen Fall „Leer“ oder eine Leerzeichen ausgibst:
{=WENN(SVERWEIS(F2&F3;WAHL({1.2};A2:A41&B2:B41;C2:C41);2;FALSCH)=0;“Leer“;SVERWEIS(F2&F3;WAHL({1.2};A2:A41&B2:B41;C2:C41);2;FALSCH))}
Schöne Grüße,
Martin
Vielen Dank für die schön geschriebene Lektüre. Ich stehe vor dem Problem, dass ich gern eine Zahl eingeben möchte also Alter 0-5; statt dem dropdown, tippe ich die Zahl 3 in das Feld und es sollte mir 1.693.000 Einwohner anzeigen. Ich benötige dies für Mengenangaben und zeige dann den Endpreis des Artikels an. Was kann man hier tun?
Hallo Mosterrider,
damit das funktioniert, müsstest Du die Altersspalte auf zwei Spalten aufteilen (Alter von / Alter bis) und der „Super-SVERWEIS“ muss durch eine andere Variante ersetzt werden. In der Kurzform:
Spalte A: Alter von (numerischer Wert)
Spalte B: Alter bis (numerischer Wert)
Spalte C: Geschlecht
Spalte D: Einwohner
G25: gewünschtes Alter
G25: gewünschtes Geschlecht
G27: gesuchte Einwohner: =SVERWEIS(G3;INDIREKT(„C“&VERGLEICH(G2;A2:A42;1)&“:D“&“41″);2;FALSCH)
Nähere Erklärungen würden den Rahmen hier sprengen, ich werde vielleicht mal einen Blogartikel dazu schreiben.
Schöne Grüße,
Martin