Excel – SVERWEIS, INDEX und Co.

Bild_001

Der krönende Höhepunkt beim kaufmännischen EDV-Unterricht oder im Excel-Aufbaukurs ist häufig der SVERWEIS.

Sie möchten Sie den SVERWEIS nicht verwenden!

Sie möchten auf der Basis Ihrer Suchvorgaben passende Werte aus einer Liste bzw. einer Tabelle zurück erhalten.

Sie möchten wie auf dem Titelbild den Auftragswert EUR 75 passend zur Vorgangsnummer 1008 erhalten. Vielleicht soll zusätzlich noch die Dauer des Verkaufsgesprächs und der Name des Mitarbeiters angezeigt werden.

Sie möchten Ergebnisse erzielen, die mit der Hilfe von Referenztabellen nachgeschlagen werden können. Im Prinzip könnte man mit ausgedruckten Listen arbeiten, sich ein Lineal, einen Stift und einen Textmarker zur Hand nehmen…

Die Möglichkeit, in Listen Werte aufzuspüren und zu diesen Werten passende weitere Daten ausgeben zu lassen, finden wir in der Tabellenkalkulation mit den Funktionen der Kategorie „Nachschlagen und Verweisen“. Neben recht einfachen Verweisen, können bei mehreren Suchbedingungen oder Listen mit mehreren möglichen Ergebnissen zum Teil recht komplexe Formeln resultieren.

Und nun habe ich ein Problem! Wann ist dieses Skript in einer Fassung, in der es veröffentlicht werden kann? Wann habe ich genug Material zusammengetragen? Ich glaube, dass Verweise ein so umfassendes Thema sind, dass man nie wirklich an ein Ende stößt. Von daher werde ich das Skript einfach nach Abschluss eines neuen Kapitels mit einer neuen Versionsnummer im Netz bereitstellen.

Ich freue mich auf Ihr Feedback im Netz.
Mit besten Grüßen
Andreas Thehos
01. Juni 2014

Das Skript, die zum Skript passenden Übungsdateien und Videos werden auf dieser Seite veröffentlicht.

Hier finden Sie ein paar ältere Videos, die zum Thema passen:
Excel – SVERWEIS – einfach und modern http://youtu.be/DjVVMcaufxk
Excel – VERWEIS – Matrixfunktionen http://youtu.be/MBnpDjRFMKQ
Excel – WVERWEIS und ISTLEER – Matrixfunktionen http://youtu.be/siFuFwJUP2s
Excel – INDIREKT – Werte aus anderen Tabellenblättern auflisten http://youtu.be/azg-rlcmVG4
Excel – INDEX und INDIREKT – Alternativ zu SVERWEIS http://youtu.be/IWgCex96LYQ
Excel – ADRESSE und INDIREKT – A1- und Z1S1-Lesart verstehen http://youtu.be/6BeNtlwErFY
Excel – Verwendung von Namen – Namen mit Formeln – dynamische Namen http://youtu.be/TvqvY_AAjTA
Excel 2013 – IFNA – Neue Funktion http://youtu.be/4LhNb_jndjs
Excel # 321 – Dynamischer Bereich mit intelligenten Tabellen http://youtu.be/2wWmCdkCY7g
Excel # 351 – Intelligente Tabellen – Tabellennamen in Formeln – Teil 01 http://youtu.be/UobweK5TDA4
Excel # 352 – Liste ohne Doppelte – Intelligente Tabellen und AGGREGAT – Teil 02 http://youtu.be/KPGAUrWz7Fs
Excel # 354 – Listen für ein- und mehrfach vorkommende Werte – Liste ohne Doppelte http://youtu.be/LWXxj6Bv2dM
Excel # 358 – Rangliste bei Sportereignis – AGGREGAT http://youtu.be/XJI_QwFEgbE
Excel # 359 – Listen dynamisch filtern – AGGREGAT und FINDEN http://youtu.be/L99bbA8W0sU
Excel # 360 – Mehrere Suchkriterien – mehrere Treffer – AGGREGAT statt SVERWEIS http://youtu.be/He3dblboncw
Excel # 384 – Bereich für Datenüberprüfung mit INDEX – Teil 1 http://youtu.be/x7vBi4qlQKw
Excel # 385 – Summe über dynamischen Bereich mit INDEX – Teil 2 http://youtu.be/npu_adz_mOI
Excel # 386 – Dynamisches Diagramm mit INDEX – Diagrammbereich auswählbar – Teil 3 http://youtu.be/FXdUjvQr0IU
Excel # 387 – Bedingte Bereiche mit INDEX in Matrixformeln und Namen nutzen – Teil 4 http://youtu.be/oaJ7qjtjZkk
Excel # 406 – Mit mehreren Suchkriterien Kreuztabelle auslesen – AGGREGAT und INDIREKT / INDEX http://youtu.be/VIYk9xP4UII
Word und Excel – SVERWEIS von Word nach Excel und wieder zurück – Verknüpfungen http://youtu.be/Fikd-PKuFZM
Word und Excel – Dynamische Berichte mit Verknüpfungen – Teil 1 – Übersicht http://youtu.be/2XIZ1ZQ6iwY
Word und Excel – Dynamische Berichte mit Verknüpfungen – Teil 2 – Excel Quelldatei http://youtu.be/YdjDpSHYkFI
Word und Excel – Dynamische Berichte mit Verknüpfungen – Teil 3 – Word-Bericht http://youtu.be/HkJOQVap2sE
Word und Excel – Dynamische Berichte mit Verknüpfungen – Teil 4 – Einzelberichte sichern
Excel # 434 – Wert über zwei Suchkriterien finden – SUMMENPRODUKT http://youtu.be/OJbPAtjC81Y
Excel # 441 – Elemente auflisten – WENN und INDIREKT http://youtu.be/5lOpwVA3P0c
Word und Excel – Dynamische Verknüpfung mit benannten Bereichen http://youtu.be/UXi-Df_bm2w
Excel # 501 – Platzierung in unterschiedlichen Listen – VERGLEICH und WAHL http://youtu.be/yk1ANDRKU3c
Excel # 502 – Platzierung in unterschiedlichen Listen ermitteln – VERGLEICH und INDIREKT http://youtu.be/vXfLAD6c-zE
Excel # 533 – High Speed SVERWEIS für sortierte Listen http://youtu.be/lhPizOTt6Fc

10 Kommentare

  1. Andrea Hoppmann

    Hallo Herr Theos,
    Ihre Anleitungen sind traumhaft!
    „Wurschtel“ mich seit vielen Jahren durch Excel-Tabellen und habe vor kurzem Ihren Blog entdeckt.
    Bin seit dem Lichtjahre weiter!
    Vielen Dank.

  2. Wolfgang Külzer

    Hallo Andreas,
    Deine Blog und die Videos sind einfach super, Herzlichen Dank für die super Arbeit.
    Habe leider noch nicht gefunden wo man die Dateien runter laden kann, kannst du
    mir da weiter helfen.

    Besten Dank

    • thehosblog

      Hallo,
      viele Dateien sind aktuell nicht verfügbar.
      a) Ich habe mein Konto bei Dropbox gelöscht.
      b) Die OneDrive Struktur wurde bei mir geändert. Deshalb funktionieren zum Teil einige Links nicht mehr.

      Ich wollte die Dateien binnen der nächsten Monate wieder hochladen. Ist immerhin kostenlos hier und ich stecke eine ganze Menge Zeit und Geld hier in den Kanal.

      An dieser Stelle auch noch einmal ein großes Dankeschön an alle, die sich mit einer Überweisung an den laufenden Kosten beteiligen!

      Beste Grüße
      Andreas

  3. Helmut Andresen

    Hallo,
    Ihre Anleitungen sind super.
    Ich suche noch eine Möglichkeit die Datenmatrix in der Formel index über ein Datum abzufragen. Dabei habe ich Datensätze die aus den Jahren 2010 bis 2016 stammen und entsprechend getrennt benannt sind. Eine Matrix Daten10, Daten11, . . .Abhängig von einem vorgegebenen Datum soll die entsprechende Matrix in die Formel index eingesetzt werden und dann die Daten übergeben. Und es soll nicht ganz so lang werden, wie mit der wenn-Abfrage, da ich die ja für jedes Jahr erweitern müsste und bei weiterer Datensammlung dann immer länger würde. Ich trage also in meinem Rechnungsblatt 01.01.2016 ein und bei der Formel soll dann erscheinen index(daten16;…;…). Keine Ahnung, wie das auf kurzem Weg geht.
    Beste Grüße
    Helmut Andresen

  4. Andreas Mark

    Guten Abend, ich hätte eine Frage zur Funktion „Aggregate“ – funktioniert diese Formel nur, wenn in einer Tabelle alle Felder befüllt sind?

    • thehosblog

      Man sollte auf die Fälle eingehen. Ansonsten funktioniert AGGREGAT super seit Excel 2010

  5. Matthias Herrmanny

    Hallo Herr Thohos,

    ich hätte mal eine Frage – Die Aggregat Funktion ist super – nur habe ich das Problem, das meine Daten die in TAB _OCUM[Volume Name] (Blatt A) stehen sich voneinander unterscheiden, so das einen festen Vergleich nicht möglich ist. Der einzige gleichbleiben in diesem Bereich sind die Ersten 2-3 Stellen, davon gibt es etwa 10 Unterschiedlich, wobei 1-3 Variablen zu einer Gruppe gehört. Wenn ich nun aber in das Aggregat ein Such String einbaue, funktioniert das Auflösen, nur
    bleiben bei mir auch die Negativen Zellen stehen, genau diese möchte ich ja extra raus filtern. Diese könnte ich nun über den Auto-Filter entfernen, aber dann müsste ich auch nicht so ein Aufwand betreiben, dann könnte ich direkt alle Daten in das andere Blatt (C) übernehmen und die noch fehlen Informationaus Blatt (B) Matchen.

    Ich habe in den letzten Tag eine Menge hier über Excel gelernt über ihren Kanal, leider habe ich auch bei meiner Suche bei Youtube und auf den Bekannten Office Forum im Internet kein vergleichbares Situation gefunden, dort wurde immer mit Festen Werten in einer Zeile gesucht.

    Vielleicht können Sie sich meine Formel anschauen und hätten ein Hinweis für mich wie ich die Funktion des Aggregat so nutzen kann, dass ungewollte Zellen, dann nicht mehr Erscheinen.
    Anbei meine Formel:
    =INDEX(tab_OCUM[Volume Name];AGGREGAT(15;6;ZEILE(tab_OCUM[Volume Name])/(((FINDEN(tab_OCUM[Volume Name];tab_OCUM[@[Volume Name]])>=1))*(ODER(ISTZAHL(FINDEN(„cc“;INDEX(tab_OCUM[Volume Name];ZEILE(tab_OCUM[@[Volume Name]])-5;0)));ISTZAHL(FINDEN(„sdc“;INDEX(tab_OCUM[Volume Name];ZEILE(tab_OCUM[@[Volume Name]])-5;0))))*1));1)-5;0)

    Anbei der String der mir die Information liefert ob die Such Variable im TAB_OCUM[Volume Name] erfolgreich war oder nicht.
    ODER(ISTZAHL(FINDEN(„cc“;INDEX(tab_OCUM[Volume Name];ZEILE(tab_OCUM[@[Volume Name]])-5;0)));ISTZAHL(FINDEN(„sdc“;INDEX(tab_OCUM[Volume Name];ZEILE(tab_OCUM[@[Volume Name]])-5;0))))*1)

    MFG
    Matthias

  6. Panning Fred

    Ich verstehe nur Bahnhof…..das ist mir zu hoch 😦 Bekomme nichtmal eine Einfache Liste ähnlich eines Onlineshops hin. Artikel auf Mappe 1 Anklicken => „zu Mappe 2 hinzufügen“ anklicken Artikel wird in Mappe 2 Übernommen (Ähnlich wie beim Onlineshop „Waren in den Warenkorb legen“) Sitze da schon seit Tagen dran. :((

  7. André Bemm

    Hallo Andeas,
    ersteinmal vielen Lieben Dank für deine Viseos und Blogs, für mich eine Sehr große Hilfe, insbesondere bei den Verweisen und Aggregaten.
    Nun habe ich folgendes Problem, zu welchem ich noch kein Video oder Blog gefunden habe und villeicht kannst du mir helfen:
    Ich möchte mir mit hilfe von Aggregat Werte auslesen lassen ähnlich wie du es in Excel#360 auch schliderst.
    Ich habe Werte wie:

    Datum (>=HEUTE()), Status, Kunde, Projekt, und Subunternehmer.

    Mit Aggregat MÜSSEN (bisher) alle diese Werte ausgefüllt werden um auf Ergebnisse zu kommen.
    Mit Hilfe von WENN;DANN;SONST konnte ich zumindest soweit kommen, dass der unterster Wert, bei Definition 0 entfallen kann, Also:
    AGGREGAT (Datum (>=HEUTE()), Status, Kunde, Projekt, und Subunternehmer);
    WENN Subunternehmer = 0 DANN AGGREGAT (Datum (>=HEUTE()), Status, Kunde, Projekt)
    WENN Projekt = 0 DANN AGGREGAT (Datum (>=HEUTE()), Status, Kunde; )
    usw.
    Das habe ich soweit getrieben, dass letzlich nur noch das Projekt angezigt wird.
    Hier ist aber der Fehler, dass die Reihenfolge ebenfalls Definiert ist.
    Ich möchte aber ereichen, dass EGAL WELCHER Wert UND EGAL WIEVIELE Werte vorgeben sind immer durch Aggregat und Index die Ergebnisse aufgelistet werden.

    Aktuell, da ich mir nicht anders zu Helfen weiß, entsteht eine schier endlos lange Formel aus Aggragt- und Wenn-Funktionen, was sehr nevernaufreibend und unübersichjtlich ist und durch Tippfehler auch immer wieder zu Fehlermeldungen führt.

    Kannst du mir da vielleicht Tips geben.
    Vielen Dank schon mal und immer weiter so mit deinen Blogs.

    Viele Grüße

    André

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s