Den Wert an einem Schnittpunkt finden (Variante 2) 3

Artikelbild-170
Eine weitere Möglichkeit, den Wert eines Schnittpunkts in einer Tabelle auszugeben.
 

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:

170_Schnittpunkt-1

INDEX-Funktion und Schnittmengenoperator

Damit das funktioniert, muss für jede Spalte und jede Zeile ein Name definiert werden:

Der Nachteil: Viele definierte Namen

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

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

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.

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 zu Luschi Antworten abbrechen

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

3 Gedanken zu “Den Wert an einem Schnittpunkt finden (Variante 2)