Andreas Unkelbach
Werbung


Dienstag, 8. Mai 2018
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.
 

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.

Tabelle mit Stammdaten als T_Kostenstellenstamm 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.

INDEX und VERGLEICH

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.
 

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

Keine Kommentare - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Keine Kommentare

Kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):



Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.

Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht und ist nicht erforderlich):

Homepage:

Kommentar:





* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste