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
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
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
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
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
Also wirklich mit einem Leerzeichen nach der ersten schließenden Klammer!
Und schon erhalten wir unser gewünschtes 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.
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.
Hi Martin, cleverer Trick! Kannte ich noch nicht!
Übrigens nette Website… hab heute Entdeckt dass Du auch schon bei mir warst :- )
Gruss, Lukas
Hallo Lukas,
danke, das Kompliment mit der Website gebe ich gerne zurück. Ja, ich konnte schon den einen oder anderen guten Tipp bei Dir entdecken 🙂
Schöne Grüße,
Martin
Warum verwendest du für die Matrix-Suche nicht einfach die INDEX-Formel? Ist schneller in der Ausführung und zudem nicht volatile wie INDIREKT.
Schöne Grüße
Marcel
Hallo Marcel,
danke für den Hinweis. Und Du hast Recht: INDIREKT ist eine volatile Funktion und damit in großen Tabellen immer ein wenig mit Vorsicht zu genießen.
Meine Lösung soll aber auch nur EINE Lösung sein und wie fast immer in Excel führen viele Wege zum Ziel.
Schöne Grüße,
Martin
Pingback: short + flush 09 / 2015 | clevercalcul
Pingback: Excel-Quickies im November | Der Tabellen-Experte
Hi Martin,
ich bin bei meiner Suche über Deinen Tipp gestolpert, den ich gleich versucht habe anzuwenden.
Von der Logik her könnte ich doch mit gegebenen Werten -nehmen wir mal Deine Ausgangstabelle- auch ein Ergebnis innerhalb B4:B13 ermitteln, richtig?
Viele Grüße
Mona
Hi Mona,
die Zellen B4:B13 enthalten ja die Regionen. Welches Ergebnis möchtest du ermitteln? Die passende Region bei gegebenem Umsatz und Produkt?
Schöne Grüße,
Martin
Hi Martin,
ja ganz genau, würde in dem Fall die passende Region zu gegebenem Umsatz und Produkt suchen.
Bisher war ich leider wenig erfolgreich, hoffe Du hast noch so einen genialen Tipp.
Viele Grüße
Mona
Hi Mona,
bezogen auf die Beispieltabelle oben aus dem Artikel würde das so aussehen:
C18: Produkt (also z.B. Produkt4)
C19: Umsatz (also z.B. 2004)
C17: =INDEX(B4:B13;VERGLEICH(C19;INDIREKT(C18);0))
Die INDIREKT-Funktion setzt voraus, dass für die jeweiligen Produktumsatzspalten Namen definiert sind. So wie halt im Artikel beschrieben.
Schöne Grüße,
Martin
Hi Martin,
danke für Deine Info. Die Formel funktioniert an Deiner Tabelle astrein, leider nicht an meiner.
Hier meckert er immer, dass ich zuwenig Argumente habe, obwohl das Handling das Selbe ist…
Muss wohl weiter probieren.
Für den Fall, dass Du mir noch einen Tipp hast – immer gerne… 😉
Viele Grüße
Mona
Hallo Mona,
hast du in deiner Datei auch Namen vergeben? Ansonsten funktioniert die Lösung mit INDIREKT nicht.
LG,
Martin
Servus Martin, bin auf der Suche nach einer Problemlösung auf deinen Vorschlag gestoßen. Meine Tabelle ist etwas umfangreicher (geht von A1:P99), sollte aber doch wohl das gleiche Prinzip sein. Allerdings kommt bei mir als Lösung, nach Eingabe der Indirekt-Formel, #Null raus. Was mache ich denn falsch?
Gruß
Torsten
Hallo Torsten,
die vorgestellt Lösung basiert ja auf den definierten Namen. Es gibt jedoch bestimmte Regeln, wie solche Namen aussehen müssen bzw. welche Zeichen sie nicht enthalten dürfen. Wie zum Beispiel Leerzeichen etc.
Daher kann es sein, dass die erstellten Namen nicht identisch mit den Spalten- oder Zeilenüberschriften sind und daher die INDIREKT-Formel keinen Treffer findet. Schau einfach mal in den Namensmanager rein und prüfe die Schreibweise der Namen.
Schöne Grüße,
Martin
Hallo Martin,
danke für den Hinweis. Mein Fehler war tatsächlich, dass ich in meinen Bezeichnungen statt Unterstrich einen Bindestrich hatte. Jetzt funktioniert die Tabelle so wie sie soll.
Gruß
Torsten