Praxisfall: Bedingte Formatierung im Entfernungsrechner 6

Die bedingte Formatierung erleichtert das Ablesen von Zahlen in langweiligen, großen Tabellen
 

Die “Bedingte Formatierung” in Excel bietet unzählige Möglichkeiten, Tabelleninhalte dynamisch und abhängig von bestimmten Kriterien oder Ereignissen ansprechend aufzubereiten.

Heute zeige ich Dir am Beispiel eines Entfernungsrechners eine praktische Anwendung: Hier hilft die bedingte Formatierung beim Ablesen der Distanz zwischen zwei Städten aus einer ziemlich großen Matrix.

Dabei setzen wir eine ganze Reihe von Excel-Funktionen ein:

  • INDEX
  • BEREICH.VERSCHIEBEN
  • VERGLEICH
  • SPALTEN
  • ZEILEN

Und so geht’s:

Der fertige Entfernungsrechner

Damit du gleich zu Beginn einen Eindruck davon bekommst, was dich erwartet, zeige ich dir schon mal den fertigen Entfernungsrechner (die Beispieldatei kannst du hier herunterladen):

Die fertige Entfernungstabelle

Die fertige Entfernungstabelle in Aktion

Wie du schon im Bild erkennen kannst, wird die jeweilige Distanz durch eine farbige horizontale und vertikale Markierung am Schnittpunkt der beiden Städte deutlich hervorgehoben. Diese Markierung verändert sich dynamisch, sobald wir Ausgangs- und/oder Zielstadt ändern. Sehen wir uns also an, wie das funktioniert.

Das Grundgerüst

Mein Entfernungsrechner besteht im Wesentlichen aus drei Bereichen:

  • B2:B3 = Eingabebereich für die Ausgangs- und Zielstadt
  • F3 = Ausgabeebereich für die Entfernungskilometer
  • A6:A46 = die eigentliche Entfernungstabelle

Um dem Anwender die Eingabe zu erleichtern, sind die Zellen B2 und B3 für die Städteauswahl als Dropdown-Felder angelegt.

Berechnung der Entfernung

In Zelle F3 werden die Entfernungskilometer zwischen den beiden gewählten Städten ausgegeben. Dazu verwenden folgende Formel, die ich gleich erklären werde:
=INDEX(BEREICH.VERSCHIEBEN(A7:A46;0;VERGLEICH(B3;B6:AO6;0));VERGLEICH(B2;A7:A46;0))

Auch wenn es auf den ersten Blick ein wenig kompliziert aussieht, handelt es sich doch im Grunde genommen nur um eine INDEX-Funktion:

Berechnung der Entfernung

Berechnung der Entfernung

Zur Erinnerung:
Die INDEX-Funktion gibt in seiner einfachsten Form den Wert aus, der an einer bestimmten (durch den Zeilen-Index definierten) Stelle in einem Tabellenbereich steht.
=INDEX(BEZUG;Zeile)

Den zweiten Parameter der INDEX-Funktion, also die Zeile, ermitteln wir mit Hilfe der VERGLEICH-Funktion:
=VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)

Nehmen wir also an, unser gewählter Ausgangsort in Zelle B2 wäre Bremen. Dann müssten wir im ersten Schritt den Bereich A7:A46 nach dem Wert in B2 (also “Bremen”) durchsuchen:
VERGLEICH(B2;A7:A46;0)
Da wir nach einer genauen Übereinstimmung suchen, ist der dritte Parameter der Wert 0 (Null). Das Ergebnis dieser Funktion ist der Wert 5, da Bremen an fünfter Stelle in der Liste steht. Somit haben wir schon einmal die Zeilennumer für unsere INDEX-Funktion ermittelt.

Kommen wir zum ersten Parameter der INDEX-Funktion, nämlich dem Bezug. Da wir ja eigentlich nicht die Spalte A durchsuchen wollen, sondern die jeweilige Spalte der ausgewählten Zielstadt, müssen wir den Bereich A7:A46 nach rechts bis in die Spalte mit der Zielstadt “verschieben”. Und genau das machen wird mit dieser Funktion:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten)

Bezug
Der Ausgangsbezug ist klar, nämlich A7:A46.

Zeilen
Da wir den Bezug nicht nach unten, sondern nur nach rechts verschieben wollen, geben wir hier den Wert 0 (Null) an.

Spalten
Um wieviele Spalten müssen wir den Bezug nach rechts verschieben? Das berechnen wir wieder mit der VERGLEICH-Funktion. Diesmal suchen wir nach der Zielstadt, die wir in Zelle B3 ausgewählt haben. Und wir durch suchen den horizontalen Bereich mit den Städten, also B6:AO6, nach einer genauen Übereinstimmung:
VERGLEICH(B3;B6:AO6;0)
Wenn z.B. die Zielstadt “Berlin” ist, lautet das Ergebnis dieser Funkion 3 (d.h. die dritte Spalte).

Hier nochmal die ganze Formel:
=BEREICH.VERSCHIEBEN(A7:A46;0;VERGLEICH(B3;B6:AO6;0))

Damit liefert unsere ursprüngliche INDEX-Funktion den Wert in der 5. Zeile (Ausgangsort war Bremen) und in der 3. Spalte (Zielort war Berlin) und damit 375 km!

Bedingte Formatierung

Wenn du bis jetzt noch nicht ausgestiegen bist, habe ich eine gute Nachricht: Das Schlimmste hast du hinter dir! Jetzt benötigen wir nur noch die Regeln für die bedingte Formatierung. Insgesamt habe ich dafür 5 verschiedene Regeln definiert:

5 Formatierungsregeln

5 Formatierungsregeln

Regel 1: Kilometerangabe
Markiere zunächst den Bereich, für den diese Regel gelten soll, also B7:AO46 und stelle sicher, dass sich zu Beginn der Definition deiner Regeln die aktive Zelle auch tatsächlich links oben in B7 befindet. Dann legen wir eine neue Formatierungsregel an.

Regel 1

Regel 1

Hier prüfen wir, ob die gewählte Ausgangsstadt (B2) der Stadt in der aktuellen Zeile in Spalte A entspricht. Und gleichzeitg muss die Zielstadt (B3) der Stadt in der aktuellen Spalte in Zeile 3 entsprechen. Wichtig ist hier die korrekte Adressierung, also der exakte Einsatz der $-Zeichen!

Regel 2: Ausgangsstadt
Markiere den Bereich A7:A46 und stelle sicher, dass sich die aktive Zelle dann wieder in A7 befindet. Dann legen wir folgende einfache Regel an:

Regel 2

Regel 2

Hier prüfen wir lediglich, ob die Stadt in der aktuellen Zeile in Spalte A der Ausgangsstadt in B2 entspricht. Auch hier wieder auf die $-Zeichen achten!

Regel 3: Zielstadt
Nun markieren wir den Bereich B6:AO6 und erstellen eine analoge Regel für die Zielstadt:

Regel 3

Regel 3

Regel 4: Horizontale Einfärbung
Kommen wir nun zur blauen Einfärbung der Zeile mit der Ausgangsstadt bis hin zur Kilometerangabe. Dafür markieren wir wieder die eigentliche Entfernungstabelle von B7:AO46 und legen diese Regel an:

Regel 4

Regel 4

Hier ist folgende Formel einzutragen:
=UND($A7=$B$2;SPALTEN($B$7:B7)<=VERGLEICH($B$3;$B$6:$AO$6;0))

Zur Erklärung:
In der ersten UND-Bedingung prüfen wir wieder, ob es sich um die korrekte Ausgangsstadt handelt. Die zweite Bedingung zählt die Anzahl der Spalten beginnend ab der Spalte B bis zur jeweils aktuellen Spalte. Dann wird geprüft, ob diese Anzahl kleiner oder gleich der Spaltennummer der Zielstadt ist. Diese wird über die VERGLEICH-Funktion ermittelt, die wir oben schon besprochen haben.

Regel 5: Vertikale Einfärbung
Und zu guter Letzt noch die Einfärbung der Spalte mit der Zielstadt. Auch hier muss der ganze Bereich von B7:AO46 markiert sein. Die Regel sieht so aus:

Regel 5

Regel 5

Und hier wieder die komplette Formel dazu:
=UND(B$6=$B$3;ZEILEN($B$7:B7)<=VERGLEICH($B$2;$A$7:$A$46;0))

Analog zur Regel 4 prüfen wir in der ersten Bedingung auf die korrekte Zielstadt. Und die zweite Bedingung zählt die Anzahl der Zeilen beginnend bei Zeile 7 und vergleicht diese Anzahl kleiner oder gleich der Zeilennummer der Ausgangsstadt ist.

Somit ergibt sich folgendes Regelwerk für unsere Tabelle:

Alle Regeln im Überblick

Alle Regeln im Überblick

Setze die Regel für die Kilometer an die erste Stelle und sorge mit dem Haken "Anhalten" dafür, dass die folgenden Regeln nicht mehr ausgeführt werden, wenn die erste bereits greift. Ansonsten würde die rote Einfärbung durch die blauen Markierungen erstetzt werden.

Das war's!

Ich gebe zu, ziemlich viel Stoff für ein paar bunte Markierungen. Aber wenn du das Prinzip verstanden hast, wird es dir beim nächsten Mal deutlich leichter fallen.

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 “Praxisfall: Bedingte Formatierung im Entfernungsrechner

  • Carsten

    Hallo Martin,

    die “komplizierte” Formel zu Beginn ist aus meiner Sicht mit einer klassischen Kombination aus SVERWEIS und VERGLEICH einfacher zu haben: =SVERWEIS(B2;A7:AO46;VERGLEICH(B3;A6:AO6;0);0) [Wichtig ist dabei, dass der VERGLEICH der Spalte bei der ersten Spalte der Matrix anfängt].
    Gibt es andere Gründe, auf INDEX zurückzugreifen, die sich vielleicht erst bei größeren Datentabellen durch den SVERWEIS entsprechend negativ (Berechnungszeit etc.) auswirken?

    Gruß, Carsten

    • Martin Weiß Beitragsautor

      Hallo Carsten,

      manchmal sieht man den Wald vor lauter Bäumen nicht 🙂
      Du hast natürlich Recht: ein einfacher SVERWEIS hätte es hier auch getan (aber warum einfach, wenn’s auch kompliziert geht…)

      Vielen Dank für Deinen sehr guten Hinweis!

      Schöne Grüße,
      Martin

  • Gerhard Pundt

    Hallo Martin,
    ein schöner Anwendungsfall für die INDEX-Funktion, die ich persoenlich sehr mag und gar nicht kompliziert finde.
    Zeilen und Spalten mit der Funktion VERGLEICH zu benennen, bietet so viele Moeglichkeiten in EXCEL. Danke für den Beitrag.
    Viele Grüße
    Gerhard

    • Martin Weiß Beitragsautor

      Hallo Gerhard,

      ja, die INDEX-Funktion führt hinter dem SVERWEIS völlig zu Unrecht ein kleines Schattendasein. Dabei ist sie so vielseitig und gerade in Kombination mit VERGLEICH dem SVERWEIS sogar weit überlegen.

      Schöne Grüße,
      Martin

  • Joe

    Hallo Martin,
    zuerst mal ein Hurra, ich habe es geschafft. Als Anfänger habe ich mich recht schwer getan, aber nach einigen Schweißperlen und Grübeln habe ich es doch geschafft. Es war genau das, was ich gerade im Büro brauchte. Vielen Dank für die Idee. Gruß Joe
    P.S. Ich lerne weiter

    • Martin Weiß Beitragsautor

      Hallo Joe,

      vielen Dank für das Feedback. Ja, manchmal ist es nicht ganz einfach, dafür ist das Erfolgserlebnis umso größer, wenn es schließlich doch klappt.

      Schöne Grüße,
      Martin