Dienstag, 8. Mai 2018
07:32 Uhr
07:32 Uhr
Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt
Manchmal bedarf es einfach eines externen Stups in die richtige Vorgehensweise um ein Problem zu lösen. Mein bisheriger Finanzbericht tendierte dazu ewig zu laufen (durch viele Verweisfunktionen (siehe "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS") die nach Eingabe eines neuen Wertes eine sehr lange Berechnung von Werten nach sich gezogen haben.
Hier hat es dann für mich Klick gemacht und ich verfahre nun wie folgt:
Mein Problem war, dass zu einer Projektnummer entweder keine Daten, Daten aus der Drittmittelstatistik oder aus einen aktuellen Recherchebericht vorliegen.
Jetzt habe ich zwei Hilfsspalten "Drittmittelstatistik vorhanden? Dann 2, sonst 0" und "Rechercheberichtdaten vorhanden? Dann 1 sonst 0"). Ist die Summe beider Hilfsspalten kleiner als 1 wird keine Suche durchgeführt, bei größer 1 eine Suche in der Drittmittelstatistik und bei 1 im Recherchebericht. :)
Im Grunde entspricht dieses auch der Vorgehensweise im Klassiker "Fliegendes Klassenzimmer" von Erich Kästner wo es geschrieben steht: "Forscht, wo ihr was zum Forschen findet. Das Unerforschbare laßt unergründet." 📖
Dieses ist eigentlich eine ganz einfache Lösung, die aber durch oberen Artikel inspiriert wurde. Dieses wäre aber noch kein eigener Artikel wert (allenfalls ein Danke-Kommentar) wenn ich nicht gleichzeitig auch auf den Artikel "Ein echtes Dream-Team: INDEX und VERGLEICH" erneut gelandet wäre und mich endlich einmal intensiver mit INDEX und VERGLEICH auseinander gesetzt hätte. Bisher hat mich diese Formelkombination etwas abgeschreckt, aber am Beispiel eines aktuellen Finanzberichtes sehe ich hier tatsächlcih einen enormen Vorteil für mich.
Schon im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" merkte ich, dass die Tabellenfunktion einige Vorteile hat und für meinen Fall habe ich eine Tabelle mit Kostenstellenstammdaten als Tabelle formatiert und direkt als T_Kostenstellenstamm wie in folgender Abbildung benannt.
Hierdurch habe ich nun eine Tabelle die mir alle Kostenstellen, Bezeichnung und Verantwortlicher ausliefert.
Nun kommen die beiden Funktionen INDEX und VERGLEICH ins Spiel.
Durch die Formel INDEX kann ich aus einer Matrix eine bestimmte Zeile und eine bestimmte Spalte ausgeben.
Das Wunderbare ist nun, dass ich statt einer Matrix auch obigen Tabellennamen ausgeben kann.
Der Syntax lautet:
=INDEX( Matrix ; Zeile ; Spalte )
So ergibt die Formel:
=INDEX(T_Kostenstellenstamm;5;3)
das Ergebnis Lotse (3. Spalte, 5. Zeile der Tabelle).
Dieses wäre nun noch kein Mehrgewinn, denn woher ist mir bekannt, in welcher Zeile die relevante Kostenstelle ist? Zumal ich hier willkürlich nach der Kostenstelle 1040000 geschaut habe.
Entsprechend kommt hier noch die Formel VERGLEICH ins Spiel.
Ihr Syntax lautet:
= VERGLEICH( Suchkriterium ; Suchmatrix ; Vergleichstyp )
Wobei der Vergleichstyp 0 eine exakte Übereinstimmung erfordert (-1 soll einen kleineren Wert und +1 einen größeren Wert suchen)
Spannend ist, dass hier als Suchmatrix nun auch wieder der benannte Bereich in unserer Datentabelle T_Kostenstellenstamm genutzt werden kann.
Konkret bedeutet dieses, dass ich durch folgende Formel direkt die relevanten Daten erhalte.
Zur Kostenstelle in der Spalte B (hier Zelle B2) wird über die Formel:
=INDEX(T_Kostenstellenstamm;VERGLEICH(B2;T_Kostenstellenstamm[Kostenstelle];0);2)
genau die Bezeichnung aus der benannten Tabelle T_Kostenstellen heraus gesucht.
Übersetzt lautet die Formel
Natürlich könnte ich nun statt der Spalte auch wieder eine Vergleichsfunktion ausführen indem ich die Spaltenüberschriften nach der übereinstimmenden Spaltenbezeichnung durchsuche, aber das wäre dann doch zu viel des Gute.
An dieser Stelle einmal ein herzliches Danke an Excel-Blogs im Web die dann doch eine schöne Lösung finden und mir selbst immer wieder dabei helfen, etwas Neues zu lernen.
Nachtrag:
Dieses ist mittlerweile im Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" passiert.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionenzu finden. Mein Weiterbildungsangebot zu SAP Themen finden Sie auf unkelbach.expert.
Bedingtes Suchen nach Daten oder Suche erst dann, wenn es etwas zum Finden gibt
Der Artikel "SVERWEIS mit Raketenantrieb" auf tabellenexperte.de brachte dann aber eine elegante Lösung für dieses Problem. Die vorgestellte Lösung ist eine WENN Funktion, die erst einmal schaut, ob ein Suchwert ungefähr in den zu durchsuchenden Daten vorhanden ist und danach den Treffer mit der Suche vergleicht und erst in Folge dessen eine echte Suche durchführt.Hier hat es dann für mich Klick gemacht und ich verfahre nun wie folgt:
Mein Problem war, dass zu einer Projektnummer entweder keine Daten, Daten aus der Drittmittelstatistik oder aus einen aktuellen Recherchebericht vorliegen.
Jetzt habe ich zwei Hilfsspalten "Drittmittelstatistik vorhanden? Dann 2, sonst 0" und "Rechercheberichtdaten vorhanden? Dann 1 sonst 0"). Ist die Summe beider Hilfsspalten kleiner als 1 wird keine Suche durchgeführt, bei größer 1 eine Suche in der Drittmittelstatistik und bei 1 im Recherchebericht. :)
Im Grunde entspricht dieses auch der Vorgehensweise im Klassiker "Fliegendes Klassenzimmer" von Erich Kästner wo es geschrieben steht: "Forscht, wo ihr was zum Forschen findet. Das Unerforschbare laßt unergründet." 📖
INDEX und VERGLEICH als Alternative zum SVERWEIS mit Namen für die Matrix
Dieses ist eigentlich eine ganz einfache Lösung, die aber durch oberen Artikel inspiriert wurde. Dieses wäre aber noch kein eigener Artikel wert (allenfalls ein Danke-Kommentar) wenn ich nicht gleichzeitig auch auf den Artikel "Ein echtes Dream-Team: INDEX und VERGLEICH" erneut gelandet wäre und mich endlich einmal intensiver mit INDEX und VERGLEICH auseinander gesetzt hätte. Bisher hat mich diese Formelkombination etwas abgeschreckt, aber am Beispiel eines aktuellen Finanzberichtes sehe ich hier tatsächlcih einen enormen Vorteil für mich.
Schon im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" merkte ich, dass die Tabellenfunktion einige Vorteile hat und für meinen Fall habe ich eine Tabelle mit Kostenstellenstammdaten als Tabelle formatiert und direkt als T_Kostenstellenstamm wie in folgender Abbildung benannt.
Hierdurch habe ich nun eine Tabelle die mir alle Kostenstellen, Bezeichnung und Verantwortlicher ausliefert.
Nun kommen die beiden Funktionen INDEX und VERGLEICH ins Spiel.
Durch die Formel INDEX kann ich aus einer Matrix eine bestimmte Zeile und eine bestimmte Spalte ausgeben.
Das Wunderbare ist nun, dass ich statt einer Matrix auch obigen Tabellennamen ausgeben kann.
Der Syntax lautet:
=INDEX( Matrix ; Zeile ; Spalte )
So ergibt die Formel:
=INDEX(T_Kostenstellenstamm;5;3)
das Ergebnis Lotse (3. Spalte, 5. Zeile der Tabelle).
Dieses wäre nun noch kein Mehrgewinn, denn woher ist mir bekannt, in welcher Zeile die relevante Kostenstelle ist? Zumal ich hier willkürlich nach der Kostenstelle 1040000 geschaut habe.
Entsprechend kommt hier noch die Formel VERGLEICH ins Spiel.
Ihr Syntax lautet:
= VERGLEICH( Suchkriterium ; Suchmatrix ; Vergleichstyp )
Wobei der Vergleichstyp 0 eine exakte Übereinstimmung erfordert (-1 soll einen kleineren Wert und +1 einen größeren Wert suchen)
Spannend ist, dass hier als Suchmatrix nun auch wieder der benannte Bereich in unserer Datentabelle T_Kostenstellenstamm genutzt werden kann.
Konkret bedeutet dieses, dass ich durch folgende Formel direkt die relevanten Daten erhalte.
Zur Kostenstelle in der Spalte B (hier Zelle B2) wird über die Formel:
=INDEX(T_Kostenstellenstamm;VERGLEICH(B2;T_Kostenstellenstamm[Kostenstelle];0);2)
genau die Bezeichnung aus der benannten Tabelle T_Kostenstellen heraus gesucht.
Übersetzt lautet die Formel
- =INDEX(T_Kostenstellenstamm;
Schaue in den Index der Tabelle T_Kostenstellenstamm und gebe mir als Zeile das Ergebnis von - VERGLEICH(B2; T_Kostenstellenstamm[Kostenstelle]; 0)
der Suche nach B2 in der Spalte Kostenstelle der Tabelle T_Kostenstellenstamm mit Vergleichstyp 0 also einer exakten Übereinstimmung aus - ;2)
danach möchte ich aus dieser Tabelle T_Kostenstellenstamm die 2. Spalte haben, welche der Bezeichnung entspricht.
Natürlich könnte ich nun statt der Spalte auch wieder eine Vergleichsfunktion ausführen indem ich die Spaltenüberschriften nach der übereinstimmenden Spaltenbezeichnung durchsuche, aber das wäre dann doch zu viel des Gute.
An dieser Stelle einmal ein herzliches Danke an Excel-Blogs im Web die dann doch eine schöne Lösung finden und mir selbst immer wieder dabei helfen, etwas Neues zu lernen.
Nachteil: Mehrere Werte bzw. Treffer möglich
Grundsätzlich ist diese Methode bei Einzelwerten hilfreich wenn aber bestimmte Werte mehrfach vorkommen wird nur der erste Treffer sowohl bei SVERWEIS als auch INDEX,VERGLEICH genommen. Damit hier auch mehrere Treffer genommen werden können bietet sich entweder die Formel SUMMEWENN oder eine Matrixformel an. Hier werde ich aber noch einen anderen Artikel zu schreiben.Nachtrag:
Dieses ist mittlerweile im Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" passiert.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 8.5.2018, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=930 (Abgerufen am 3.10.2024)
Keine Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn