Die beste VERWEIS Funktion in Excel
► Kanal abonnieren: bit.ly/jakobnbr
In diesem Video geht es um die beste VERWEIS Funktion in Excel. Dabei besprechen wir die Vorteil der SVERWEIS Funktion, der XVERWEIS Funktion und der Kombination INDEX + VERGLEICH Funktion, um auf die beste Lösung zu kommen. Am Ende des Videos sage ich dir, was meine Lieblingsfunktion in Excel ist.
▬ Weitere Videos ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
► Professionelle Dashboards in Excel
• Interaktives Dashboard...
► Die wichtigsten Excel Funktionen
• 20 Excel Funktionen di...
►ALLE meine Excel Tipps & Tricks
• Excel Tipps & Tricks
▬ Mein Equipment ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Im Video:
► Software: amzn.to/3eUCBNb
► Maus: amzn.to/3ERJHwG
► Tastatur: amzn.to/3mPemof
► Bildschirm: amzn.to/3EMIxCJ
► Mikrofon: amzn.to/3t149cl
► Kamera: amzn.to/3eQZpgS
Vielen Dank für deine kostenlose Unterstützung durch diese Affiliate-Links!
▬ Über diesen Kanal ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Abonniere meinen Kanal: bit.ly/jakobnbr
LinkedIn: bit.ly/jnbr_linkedin
▬ Timestamps ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
00:00 Intro
00:55 Standard Anwendung
04:05 SVERWEIS im Nachteil
06:25 INDEX VERGLEICH erweitern
08:09 weiteres Beispiel
09:52 Die BESTE Funktion
11:48 XVERWEIS im Detail
Xverweis Von diesen Kanal gelernt, Funktioniert für meine Bedarfe SUPER
perfekt :D
Immer wieder gerne! Besonders die Empfehlung "über der Tellerrand hinaus schauen!"
Gerne ☺️
Für mich ist INDEX und VERGLEICH eher eine Möglichkeit eine Zelle herauszufinden, die einen gesuchten Wert enthält. An die Kompatibilität mit älteren Officeversionen habe ich nie gedacht. Daher Danke für diese tolle Erklärung.
Wie immer ein sehr gut erklärtes Video. Meine Empfehlung dazu wäre allerdings der XVERWEIS. Wie sich gezeigt hat und da stimme ich zu, sollte man den SVERWEIS nicht mehr einsetzen. Allerdings meine ich, dass INDEX und VERGLEICH und dann auch noch mit WENNFEHLER zu kompliziert ist, weil zu viele Verschachtelungen hat. Auch wenn es den XVERWEIS erst ab Excel (Office) 2021 bzw. 365 gibt, sollte man dem auf jeden Fall den Vorzug geben. Aber: Es ist meine persönliche Meinung dazu.
Geb ich dir recht - mache ich selbst auch. Leider gibt es einfach noch zu viel Unternehmen die in alten Versionen herum Gurken 😕
Hallo Lochhofener, da kann ich dir aus persönlicher Erfahrung sagen, dass der Sverweis oder Index/Vergleich noch lange nicht wegzudenken sind. Wie Jakob schon schrieb arbeiten so viele Firmen noch mit alten Versionen. Genau sieht es in Privathaushalten aus, wer hat da schon 365?! Ich persönlich finde, dass es keine schlechten oder guten Funktionen gibt. Excel bietet halt so viele unterschiedliche Möglichkeiten um seine Aufgabe zu lösen. Und solange einem die Funktion das Ergebnis liefert was man haben möchte, ist doch alles super ☺. Zu der Aussage das manche (gerade verschachtelte) Funktionen zu kompliziert sind, kann ich nur sagen, dass Excel ein ewiger Lernprozess ist. Man muss die Funktionsweise der Funktionen verstehen und auch immer wieder anwenden, dann geht es schnell in Fleisch und Blut über wie man so schön sagt oder Übung macht den Meister. Gruß Ronny
@@ronnywiking2521 Ich bin da voll bei Dir und wollte auch keine Excel Funktionen "verteufeln". Und ja, es gibt noch viele, die M365, bzw. Office 365 einsetzen. Ich hatte wegen der Verschachtelung mehr an Anwender >60 bzw. >70 gedacht, für diese Anwender (ich spreche da aus Erfahrung) sollte das einfach gehalten werden (Zitat einer Kundin: "Ich bin Generation Schreibmaschine und das muss einfach sein"). Daher die Anmerkung.
@@TribunMaximus1961 ☺ Der Satz deiner Kundin ist super, musste ich schmunzeln, hat Sie ja auch recht. Da bin ich auch völlig bei dir, dass funktioniert natürlich nicht, da sollte es schon einfach und verständlich sein.
Danke 👍
Gerne!
Danke, TOLL!
Gerne! :D
Ich selbst benutze und schreibe Tabellen eher für mich und gebe sie dann eventuell weiter. Daher nutze ich X Verwris eher nicht. Aber es hat eindeutig Vorteile. Mit Ibdex und Vergleich stehe ich ein wenig auf Kriegsfuß, da hier 2 Funktionen nötig sind, statt einer (SVERWEIS). Deshalb achte ich beim Erstellen der Tabellen darauf, dass das gesuchte Kriterium links von der Rückgabe steht. Wenn ich die Tabellen selbst erstelle, kann ich darauf achten. Etwas anderes ist es, wenn ich Eibe fertige Tabelle auswerten soll. Was aber eigentlich nicht vor kommt, da ich Rentner bin und eher für mich selbst damit arbeite.👍
Also ich nutze auch fast nur index Vergleich, wähle als matrix allerdings immer alle notwendigen spalten und lasse mir das Ergebnis über die spalrennr ausgeben. Die schreibe ich meist in eine hilfszelle auf die ich dann verweise, so kann ich recht unkompliziert die Formel in alle möglichen Zellen weiter kopieren.
Mir läuft der SVERWEIS aus alter Gewohnheit leichter von der Hand, muss nicht mehr nachdenken. Das ist der einzige Grund, warum ich mich mit der Index-Funktion noch nicht so sehr angefreundete habe, dass ich sie täglich verwende. Auf jeden Fall ist dieser Vergleich in deinem Video richtig gut, vielen Dank dafür. Ein kleines "Framing" gegen den SVERWEIS war allerdings dabei. Diese Funktion hat nicht zwei Aufgaben nicht erledigt, sondern nur eine. Der "konstruierte" zweite Fall war die gleiche Aufgabe, lediglich mit einer anderen Variablen. Also da wurde der SVERWEIS etwas schlecht geredet 😉 Tatsache ist natürlich, dass man nicht nach links suchen kann und damit hat der SVERWEIS bei dieser Gegenüberstellung in jedem Fall verloren.
Hallo Alltagsnutzen, diese Aussage stimmt leider nicht so ganz, man kann sehr wohl auch nach links suchen mit dem SVERWEIS, es bedarf halt nur einer kleinen Hilfe! Falls dich interessiert wie, schaue einfach mal meinen Kommentar dazu an. Gruß Ronny
Haha stimmt. Ich bin in die "Übung" bereits mit einem vorbestimmten Outcome hineingegangen. Wissenschaftlich also nicht korrekt. Und, wie Ronny mich aufgeklärt hat, nicht einmal gerechtfertigt.
Schon aus Gewohnheit nutze ich weiter den Sverweis. Dass sogar dieser noch dazulernt, zeigt sich daran, dass er nun auch bei 0 und nicht nur bei Falsch, eine unsortierte Tabelle akzeptiert. Der komplexe Index ist nur bei echten Tabellen interessant.
Hallo Jakob, ich mag deine Videos wirklich sehr und lerne darüber auch echt gut. Ich baue solche Funktionen gerne in Makros ein um sozusagen nicht eine sondern dutzende solcher Berechnungen und verweise dynamisch zu erstellen, beispielsweise wenn Zeilen eingefügt werden etc. Was mir auffiel über eine immer größer werdende Struktur, dass die Effektivität der rechenoperationen im einzelnen irrelevant ist, aber bei multiplen Operationen es doch zu gewissen lags kommt, gerade bei ausgelagert edv im Rechenzentrum. Die können schon nervig sein und ich habe das Gefühl das diese durch eben sverweis und co entstehen. Ich probiere das aber momentan auf xverweis umzustriken Kennst du zufällig eine einfache Möglichkeit um Daten aus Office 365 auf ein lokales Excel einfach zu synchronisieren? Gerade mit unterschiedlichen Nutzern um beispielsweise "stammdaten" die sich immer wieder ändern zu synchronisieren?
Xverweis ist angeblich noch intensiver als der SVerweis. Habe es aber noch nicht im großen Stil testen können. Wäre gespannt ob du einen unterschied merkst. Du kannst dir die Daten über einen PowerQuery Import rein holen. Dann sind die Daten verknüpft. Aber lokal im Dokument, und aktualisieren regelmäßig oder wenn du möchtest.
Der SVERWEIS ist in Situationen mit einer sehr großen zu durchsuchenden Matrix oftmals die einfachste und beste Wahl. Wichtig: die Matrix muss aufsteigend sortiert sind. Dann kann die Formel etwa so aussehen ... ich gehe hier von einer Matrix mit 2 Spalten aus ... in Spalte 1 befindet sich der Index und in Spalte 2 der auszugebende Wert. =WENN(SVERWEIS([Suchwert];[Matrix];1;WAHR)=[Suchwert];SVERWEIS([Suchwert];[Matrix];2;WAHR)) Die Formel hab ich auf 10.000 Zeilen angewendet und sie hat jeweils über 300.000 Zeilen gesucht. Dauerte bei mir etwa 0,02 Sekunden. INDEX+VERGLEICH brauchten dafür 1,7 Sekunden. Nur mal zur Vorstellung :D
Hallo Jakob, wenn ich mit dem XVERWEIS zwei Suchkriterien benötige, und diese mit einem "&" verknüpfe, kommt Excel bei mehreren Zeilen schwer ins Schwitzen. Ist dir eine andere Lösung bekannt? Danke und Grüße
Hallo Jakob, viele Dank für die wiedermal super Erklärung. Wir nutzten bislang nur xverweis im Betrieb. Gibt es Übungstabellen von dir, mit denen man das gezeigt selbst nachbauen kann? Oder hast du eine Empfehlung für Excelübungen? Vielen Dank und weiter so. 👍
Diverse Tabellen für Übungen kannst du auf meiner Webseite finden. :)
Ich habe bisher versucht mit dem sverweis Kollegen anhand eines Kriteriums Kunden zuzuordnen. Das war anfangs schwierig, da es aus zwei Dateien zusammengesucht werden musste. Jetzt habe ich eine Vorlage erstellt, in die ich die Abfrage einfüge, dann werden die Kollegen zugeordnet. Ich versuche dass jetzt mal mit xverweis. Das mit dem Spaltenindex war in der Tat nicht selbsterklärend 😩 und hat mich sehr viel Zeit gekostet
Der Spaltenindex ist mit Sicherheit das Unintuitive am SVERWEIS :/
Ich hätte hier statt "Wennfehler" "WennNV" verwendet. Da letztere nur bei #NV ersetzt, andere Fehler aber noch angezeigt werden. Vor allem wenn man eine Auswertung aufbaut und diverse Daten verschiedener Struktur abfragt, ist es hilfreich, noch eventuelle Fehler angezeigt zu bekommen, statt zu rätseln, warum da eine Leere Zelle ist. Man kann den SVerweis auch dazu überreden, nach links zu suchen (und zu finden), aber das ist tricky. Gibt hier bei YT auch mindestens ein Video dazu. Da sollte man schon überlegen, ob es nicht bessere Lösungen gibt.
SVERWEIS als Funktion kann allerdings auch Dinge, die so mit den anderen Funktionen nicht, oder nur unter Zuhilfenahme weiterer Funktionen möglich werden. Wenn ich z.B. aus einer Tabelle aus einer Zeile mehrere Spalten liefern will, kann ich einfach die Suchmatrix komplett über die gesamte Quelltabelle legen und jeweils eine identische Funktion zur Ausgabe verwenden, den Spaltenindex kann ich entweder direkt oder indirekt leicht in die Funktion einbauen (z.B. SVERWEIS($A$2;MeineMatrix;Zeile(A3)-1;FALSCH). Die Formel steht in Zelle A3. Die Zelle kann einfach nach unten kopiert werden, ohne auch nur ein Zeichen zu ändern (Excel zählt A3 automatisch hoch), in der ersten Zelle steht dann das Suchergebnis in der 2. Spalte, in der zweiten Zelle das Ergebnis der 3. Spalte usw. Ich denke, jede der Funktionen hat ihre Stärken und Schwächen und jede der Funktionen hat ihre Daseinsberechtigung. Dennoch wieder was gelernt, XVERWEIS kannte ich noch gar nicht, obwohl ich schon geraume Zeit mit 365 arbeite...
Danke für dein Video. Kannst du mal eine Plattform eröffnen, z. B. um Excel Probleme die ich/deine Zuschauer gerade lösen möchte/n, in live. Ich stelle dir mein Problem vor und du sucht nach einer Lösung. Auf KZhead mag ich den Namen nicht nennen, beginnt mit „T“ und hat sechs Buchstaben. Das wäre cool. Liebe Grüße.
Hey! Ich steh' gerade etwas am Schlauch. Was meinst du denn? (ich lösche deine Nachricht, sobald ich's gelesen habe einfach 😎)
Hallo Jakob, leider muss ich dir in diesem Video widersprechen. Die Aussage, diese Aufgabe nicht mit dem SVERWEIS zu lösen stimmt so nicht ganz. Für alle die es probieren wollen erst die Formel dann eine kurze Erklärung. =SVERWEIS(I7;WAHL({1.2};B:B;A:A);2;0) Wie wir wissen muss beim SVERWEIS das Suchkriterium immer vor dem zu suchenden Wert stehen und Standardmäßig sind in Excel die Spaltennummern klar definiert (Spalte A = 1, B = 2 usw. . Nun kommt die Funktion WAHL in Spiel. Mit dieser Funktion gebe ich vor welche Spalte die erste (B = Spalte 1 und welche die zweite (A = 2) ist. Zu sehen in der Formel WAHL am besten mit dem Funktionsassistenten. Wichtig hierbei ist nur für den Index, dass es in geschweiften Klammern stehen muss (lassen wir mal jetzt so stehen). Wenn ihr die Formel so eingebt (exakter Aufbau der Tabelle wie bei Jakob) erhaltet ihr auch die Materialnummer für den Klebstoff MN0077301. Beim Fineliner müsste genauso wieder mit der Funktion WENNFEHLER gearbeitet werden - =WENNFEHLER(SVERWEIS(I7;WAHL({1.2};B:B;A:A);2;0);"MV"). Ist die Formel kompliziert? - Ich sag mal etwas gewöhnungsbedürftig ☺ Man benötigt ja mehr als eine Funktion! - Stimmt auch! ABER: 2 oder 3 (mit WENNFEHLER) benutzt man auch bei Index/Vergleich. Zwei eigenständige Funktionen die einzeln die Aufgabe nicht lösen könnten sondern nur zusammen. Fazit: Diese Zeilen sollen keinesfalls dieses Video schlecht dastehen lassen (Jakob dein Video ist super ☺). Sie sollen einfach nur zeigen das Excel mehr kann als man manchmal denkt. Es gibt so viele Möglichkeiten in Excel um an sein Ziel zu kommen und das ist es auch was für mich ausmacht. Gruß Ronny
Hallo Ronny! Vielen Dank für den Input. Heute durfte ich wirklich noch etwas Neues über den SVERWEIS lernen. Ich dachte, ich wüsste alles ;) Das ist wirklich interessant, dass es so funktioniert. Die Syntax ist, wie du sagst, etwas gewöhnungsbedürftig, aber definitiv merkbar und umsetzbar. Genial.
Wow! Mit dem cleveren WAHL - Trick (=formelinterne Vertauschung der Spalten) kann der SVERWEIS wieder fast wieder aus der Mottenkiste geholt werden. Und wenn man den tumben Fixwert der Spaltenauswahl ("Spaltenindex") noch durch ein flexibles SPALTEN(A:B) ersetzt, ist eine weitere Macke von SVERWEIS kuriert. Gruß Andy
gutes Video, verstehe es aber irgendwie auch nicht
Das ist einfach - Index-Vergleich. Das ist der bessere SVerweis. Office 2021 haben wir auf Arbeit noch nicht...