Vor ziemlich genau einem Jahr habe ich eine Methode vorgestellt, um mit Hilfe der INDIREKT-Funktion und dem sogenannten Schnittmengenoperator den Wert auszugeben, der sich am Schnittpunkt einer bestimmten Spalte und einer bestimmten Zeile befindet.
Die damals gezeigte Lösung ist zwar sehr elegant, hat aber ein paar kleine Nachteile, so dass sie nicht für alle Szenarien immer auch praktikabel ist.
Heute zeige ich eine weitere Möglichkeit, das Problem zu lösen.
Und so geht’s:
Die alte Lösung
Nur kurz zur Erinnerung: So sah die ursprüngliche Lösung aus:
INDEX-Funktion und Schnittmengenoperator
Damit das funktioniert, muss für jede Spalte und jede Zeile ein Name definiert werden:
Der Nachteil: Viele definierte Namen
Über das Menü „Formeln – Aus Auswahl erstellen“ lässt sich das zwar schnell erledigen. Wenn in deinen Spalten- oder Zeilenüberschriften jedoch Leerzeichen enthalten sind, werden diese im Namensmanager durch einen Unterstrich ersetzt. Damit sind die tatsächlichen Überschriften und die definierten Namen nicht mehr identisch. Funktioniert zwar grundsätzlich auch, aber in den Dropdown-Feldern müssen die Einträge angepasst werden.
Die Variante, die ich gleich vorstelle, verzichtet auf die ganzen Namens-Schnickschnack und verwendet stattdessen die Funktion BEREICH.VERSCHIEBEN
Die alternative Lösung
Die Beispiel-Tabelle ist ähnlich aufgebaut. Sie zeigt die Umsätze nach Vertriebsregion und Monat (bei Bedarf kannst du die Tabelle hier herunterladen):
Die Lösung ohne definierte Namen
Als variable Eingabefelder dienen mir die Zellen C16 und C17, die ich als einfache, aber anwenderfreundliche Dropdown-Felder angelegt habe. Abhängig von den gewählten Einträgen bestimmt Excel in Zelle C18 den Wert, der sich am Schnittpunkt in der Tabelle befindet.
Dazu verwende ich die BEREICH.VERSCHIEBEN-Funktion, die grundsätzlich so aufgebaut ist:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite)
Die Funktion gibt einen Bezug zurück, der zu dem angegebenen Bezug um eine bestimmte Anzahl an Zeilen und/oder Spalten verschoben ist. Die beiden letzten Parameter für Höhe und Breite geben die Größe des neuen Bezugs an und sind optional. Wenn man sie weglässt, wird automatisch die gleiche Größe verwendet, der Ausgangsbezug hat.
In meinem konkreten Fall sieht das also so aus:
Die Funktion BEREICH.VERSCHIEBEN
Der Ausgangsbezug ist die Zelle B2, also die linke obere Ecke meiner Umsatztabelle.
Die Anzahl an Zeilen und Spalten, um die dieser Bezug jetzt verschoben werden soll, berechne ich jeweils über eine VERGLEICH-Funktion.
=VERGLEICH(Suchkriterium; zu durchsuchender Bereich; Vergleichstyp)
In der ersten Funktion suche ich nach dem Wert aus Zelle C17, also der Region. Durchsucht wird der Bereich B3:B12. Da als dritter Parameter der Vergleichstyp 0 (Null) angegeben ist, wird nur nach einer exakten Übereinstimmung gesucht. Denke daran, wenn du mit der VERGLEICH-Funktion arbeitest, ansonsten kann es zu unerwünschten Ergebnissen kommen.
Sobald die gesuchte Region gefunden wurde, liefert die Funktion die Position innerhalb des Suchbereichs zurück. Für das Produkt 7 wäre das die Position 7, also die 7. Zeile innerhalb der Umsatztabelle.
Genauso verfahren wir für die Ermittlung der Spalte. Hier übergebe ich an die zweite VERGLEICH-Funktion den Monat, der in Zelle C16 steht und durchsuche den Bereich C2:I2. Damit wird für den April 2016 die Position 4 zurückgeliefert – sprich die 4. Spalte.
Damit hat die BEREICH.VERSCHIEBEN-Funktion alles, was sie braucht:
=BEREICH.VERSCHIEBEN(B2;7;4)
… und liefert das gewünschte Ergebnis zurück.
Und wir haben dazu keinen einzigen Namen definieren müssen. Wie du siehst, führen in Excel meist mehrere Wege zum Ziel!
Kleiner Bonus:
Wenn du dir die Beispieldatei herunterlädst, dann wird der gefundene Wert in der Tabelle mit Hilfe einer Formatierungsregel auch noch farbig hervorgehoben.
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.
Hallo Martin,
warum verwendest du nicht einfach die Index-Formel? Diese ist genau für so etwas gemacht und ist zudem keine volatile Funktion wie Bereich.verschieben.
SG
Marcel
Mallo Martin,
mit der Index-Funktion sieht das so aus:
=INDEX($B$2:$I$12;VERGLEICH($C$17;$B$3:$B$12;0)+1;VERGLEICH($C$16;$C$2:$I$2;0)+1)
Gruß von Luschi
Pingback: Excel-Quickies im Oktober | Der Tabellen-Experte