Excel – Bedingte Formatierung mit SUMMENPRODUKT

Mit Hilfe der Bedingten Formatierung können Zellen hervorgehoben werden, deren eigene Werte oder deren benachbarte Werte bestimmte Eigenschaften erfüllen.

Meist prüft man jedoch nur ab, ob ein Wert lediglich in einem Wertebereich liegt oder einen bestimmten Wert hat. Mit Hilfe der Funktion SUMMENPRODUKT und einem kleinen um 90 Grad versetzten Bereich / Array können jedoch auch mehrere Werte abgeprüft werden.

Insofern wirklich ein Array in der Form {1.4.11.14} genutzt wird, verweigert sich allerdings die Bedingte Formatierung mit dem Hinweis, dass Arraykonstanten nicht genutzt werden dürfen.

Mein Tipp: Die Berechnung einfach in einen Namen auslagern und dann in der Bedingten Formatierung einfach das Ergebnis des Namens überprüfen.

Insofern einer der rot umrandeten Zahlen in Spalte B auftritt und der Name „Bea“ in Spalte A, soll die ganze Zeile markiert werden.

Excel474a

Hat man die Hilfszellen also im Blatt vorliegen, könnte man die folgende Formel in der Bedingten Formatierung nutzen.

=SUMMENPRODUKT(($A2=$H$2)*($B2=$H$1:$K$1))=1

Wichtig sind die $-Zeichen vor A2 und vor B2, damit die Formel sich in jeder Zelle des Bereichs A2:Cxxx jeweils auf die eigenen Werte der Spalten A und B beziehen kann.

Excel474b

Doch hier müssen die Werte zum Vergleich noch in den Zellen H2 und H1:K1 vorgehalten werden.
Klickt man die Zelle A2 an und wechselt auf Formeln – Definierte Namen – Namens-Manager, kann ein Name mit einer Berechnung (z.B. Prüfung) angelegt werden. In diesem Namen lassen sich statt Bezüge auch Konstanten anlegen.

Excel474c

=SUMMENPRODUKT(('SUMMENPRODUKT ARRAY'!$A2="Bea")*('SUMMENPRODUKT ARRAY'!$B2={7011.7100.7400.7700}))

Anschließend muss die Formel in der Bedingten Prüfung auf
=Prüfung=1
geändert werden… Fertig!

Hier ist die Übungsdatei:
Excel 474 Bedingte Formatierung Summenprodukt

Hier geht es zum Video:

Videolink: http://youtu.be/EgcWIyE54TI

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s