Finde einen Schnittpunkt in einer Excel-Tabelle 12

Artikelbild-122
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.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

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

  • Avatar-Foto
    Lukas

    Hi Martin, cleverer Trick! Kannte ich noch nicht!
    Übrigens nette Website… hab heute Entdeckt dass Du auch schon bei mir warst :- )

    Gruss, Lukas

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Marcel Pfeifer

    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

  • Avatar-Foto
    Martin Weiß

    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

  • Avatar-Foto
    Mona

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Mona

        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

        • Avatar-Foto
          Martin Weiß

          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

          • Avatar-Foto
            Mona

            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

          • Avatar-Foto
            Martin Weiß

            Hallo Mona,

            hast du in deiner Datei auch Namen vergeben? Ansonsten funktioniert die Lösung mit INDIREKT nicht.
            LG,
            Martin