Finde einen Schnittpunkt in einer Excel-Tabelle 6

Eine verblüffende Methode, um den Wert an einem beliebigen Schnittpunkt einer Tabelle zu ermitteln!
 

Nehmen wir folgendes Szenario an:
Du bist Analyst, Controller oder sonst jemand, der häufig mit Excel zu tun hat und arbeitest an einer umfangreichen Übersicht, in der Umsätze nach Verkaufsregion und Produkt aufgeschlüsselt sind.

Nun willst deinem Chef die Bedienung besonders leicht machen (Chefs wollen ja immer alles schön einfach) und überlegst dir dazu, dass er über zwei einfache Auswahlfelder für die Region und das Produkt ganz bequem den dazugehörigen Wert aus der riesigen Tabelle angezeigt bekommen soll.

Vereinfacht ausgedrückt also den entsprechenden Schnittpunkt einer bestimmten Zeile und einer bestimmten Spalte.

Nur: Mit welcher Excel-Formel ermittelt man diesen Schnittpunkt?

Die Lösung, die ich dir heute vorstelle, wird dich mit Sicherheit verblüffen!

Die Ausgangslage

Ich habe wieder eine kleine Beispieltabelle vorbereitet, die du hier herunterladen kannst. Sie enthält die Umsätze für verschiedene Verkaufsregionen, gruppiert nach Produkt:

Die Beispieltabelle

Die Beispieltabelle

Wie eingangs beschrieben, möchte ich mir nun unterhalb der Tabelle den Umsatz abhängig von der ausgewählten Region und dem Produkt anzeigen lassen.

Zur Vereinfachung und um Fehleingaben zu verhindern, habe ich in den Zellen C17 und C18 zwei Dropdown-Listen für die Auswahl der Region und des Produktes eingerichtet:

Dropdown-Felder

Dropdown-Felder


Wie muss nun die Formel in C19 lauten, damit der gewünschte Umsatz angezeigt wird?

Viele Wege führen nach Rom…

Als erfahrener Excel-Anwender weißt du, dass es meistens mehrere Lösungen gibt. Denkbar sind z.B. verschiedene Konstrukte über INDEX und SVERWEIS-Funktionen, die mehr oder weniger verschachtelt sind. Mein heutiger Vorschlag mit der INDIREKT-Funktion wird dich jedoch in seiner Schlichtheit garantiert überraschen!

Zunächst ist aber noch eine kleine Vorarbeit nötig:
Für jede Zeile und Spalte der Umsatztabelle müssen wir einen eigenen Namen vergeben. Das hört sich aufwändig an, ist aber ganz einfach.

Markieren wir dazu den kompletten Tabellenbereich B3:I13 und rufen dann im Register “Formeln” die Schaltfläche “Aus Auswahl erstellen” auf. Excel schlägt dabei vor, die Namen aus den Zeilen- und Spaltenbeschriftungen zu erstellen:

Die Namen erstellen

Die Namen erstellen

In Blick in den Namensmanager zeigt, dass jede Zeile und jede Spalte der Tabelle unter dem Namen der Region bzw. des Produktes angesprochen werden kann:

Kontrolle im Namensmanager

Kontrolle im Namensmanager

Und damit sind unsere Vorarbeiten auch schon abgeschlossen!

Noch ein wichtiger Hinweis:
Die automatische Namensvergabe funktioniert nur dann zuverlässig, wenn dadurch auch nach Excel-Regeln gültige Namen entstehen. So muss der Name z.B. mit einem Buchstaben oder einem Unterstrich beginnen und es dürfen keine Leerzeichen enthalten sein. Berücksichtige das, wenn du mit eigenen Daten arbeitest!

Die INDIREKT-Funktion

Jetzt wird’s spannend! In Zelle C19 geben wir nun die folgende Formel ein:
=INDIREKT(C17) INDIREKT(C18)

Die INDIREKT-Spezial-Formel

Die INDIREKT-Spezial-Formel

Also wirklich mit einem Leerzeichen nach der ersten schließenden Klammer!

Und schon erhalten wir unser gewünschtes Ergebnis:

Das verblüffende Ergebnis

Das verblüffende Ergebnis

Was steckt dahinter?

Das unscheinbare Leerzeichen zwischen den beiden INDIREKT-Funktionen ist der sogenannte Schnittmengenoperator. Kaum bekannt und daher wenig genutzt wird damit die Schnittmenge aus den angegebenen Bezügen erstellt.

Im Beispiel oben also die Schnittmenge aus “Region4” (= C7:I7) und “Produkt5” (= G4:G13), die über die jeweilige INDIREKT-Funktion angesprochen werden.

Zugegeben, diese Lösung wird sich nicht in jedem konkreten Anwendungsfall einsetzen lassen. Denn nicht immer gestaltet sich die Vergabe der Bereichsnamen ganz so einfach. Wenn du die Namensregeln aber bereits beim Aufbau deiner Tabellen im Hinterkopf behältst, dann kannst du dir mit der vorgestellten Funktion viel Arbeit und Hirnschmalz sparen.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

6 Gedanken zu “Finde einen Schnittpunkt in einer Excel-Tabelle