Excel-TippZellen in Abhängigkeit der Hintergrundfarbe zählen oder summieren

Oft werden wichtige Zellen, Spalten oder Zeilen in Excel mit Farben markiert, um die Übersichtlichkeit zu verbessern. Können diese Farben auch in Formeln und Funktionen genutzt werden?

Standardmäßig können Sie die Hintergrundfarben in Excel beim Filtern oder Sortieren von Daten verwenden. Wollen Sie die Zellen aufgrund Ihrer Hintergrundfarbe in Formel oder Funktionen verarbeiten, dann ist dies zurzeit im Excel-Standard nicht möglich.

Mit einer alten Excel4-Makrofunktion können Sie Hintergrundfarben in Formeln und Funktionen einsetzen. Der Name dieser Excel4-Makrofunktion lautet ZELLE.ZUORDNEN().

Was sind Excel4-Makrofunktionen?

Excel4-Makrofunktionen, auch als XLM-Makrofunktionen bekannt, stammen aus der Zeit vor der Einführung von VBA (Visual Basic for Applications). Diese Funktionen wurden in Excel 4.0 eingeführt und ermöglichen die Automatisierung von Aufgaben und die Erstellung komplexer Makros in Excel.

Obwohl Excel4-Makros veraltet sind, unterstützt Microsoft Excel sie weiterhin für Rückwärtskompatibilität. Benutzer, die alte Arbeitsmappen mit XLM-Makros öffnen, können diese weiterhin ausführen, obwohl neue Entwicklungen und Support hauptsächlich auf VBA fokussiert sind.

Sicherheitseinstellungen zu Excel4-Makrofunktionen

Aufgrund ihrer Fähigkeit, tief in das System einzugreifen und Änderungen vorzunehmen, werden Excel4-Makros oft als Sicherheitsrisiko betrachtet. Bösartige Akteure können diese Makros ausnutzen, um Schadcode auszuführen. Daher sind viele Sicherheitseinstellungen und Schutzmaßnahmen in modernen Excel-Versionen implementiert, um die Ausführung von XLM-Makros zu überwachen und zu kontrollieren.

Damit Sie die Excel4-Makrofunktionen nutzen können, müssen Sie zunächst die Excel-Sicherheitseinstellungen überprüfen und gegebenenfalls anpassen.

Öffnen Sie die Excel-Optionen, indem Sie im Menüband die Befehlsfolge Registerkarte Datei > Befehl Optionen ausführen.

Daraufhin öffnet sich das Dialogfeld Excel-Optionen.

Wählen Sie hier am linken Rand das Register Trust Center und anschließend Einstellungen für das Trust Center aus.

Es öffnet sich das Dialogfenster Trust Center. Wählen Sie hier am linken Rand das Register Makroeinstellungen aus. Überprüfen Sie hier, ob das Kontrollkästchen Excel 4,0-Makros aktivieren, wenn VBA-Makros aktiviert sind aktiviert ist.

Sollte dies nicht der Fall sein, dann aktivieren Sie das Kontrollkästchen. Bestätigen Sie diese Einstellung, indem Sie alle offenen Dialogfelder durch Klick auf OK schließen.

Namen definieren und verwenden

Sie können die Excel4-Makrofunktion ZELLE.ZUORDNEN() nicht direkt in einem Tabellenblatt einsetzen. Der erste Schritt, um Farben in Formeln zu nutzen, besteht darin, einen Namen zu definieren, in welchem die Excel4-Makrofunktion ZELLE.ZUORDNEN() hinterlegt wird.

Aktivieren Sie hierzu im Menüband die Befehlsfolge Registerkarte Formeln > Befehlsgruppe Definierte Namen > Befehl Namen definieren.

Es öffnet sich das Dialogfeld Neuer Name. Vergeben Sie hier bei Name: einen aussagekräftigen Namen. In unserem Beispiel verwenden wir den Namen Hintergrundfarbe.

Bei Bereich lassen Sie die Auswahl Arbeitsmappe stehen. Damit stellen Sie sicher, dass der definierte Name in der komplette Arbeitsmappe und nicht nur in einem bestimmten Tabellenblatt verwendet werden kann.

Unter Bezieht sich auf: erfassen Sie bitte die folgende Formel:

=ZELLE.ZUORDNEN(63,INDIREKT("ZS(-1)";))

Bestätigen Sie Ihre Einstellung, indem Sie das Dialogfeld durch Klick auf OK schließen.

Mit der Excel4-Makrofunktion ZELLE.ZUORDNEN() kann man Eigenschaften einer Zelle abfragen. Das erste Argument 63 besagt, dass Sie die Hintergrundfarbe (Farbcode) einer Zelle auslesen können.

Mit dem zweiten Argument INDIREKT("ZS(-1)";) geben Sie die Position der Zelle an, aus welcher Sie die Hintergrundfarbe auslesen wollen.

In der INDIREKT-Funktion geben Sie die Zelladresse in der Z1S1-Schreibweise an. In dieser Schreibweise werden nicht Koordinaten verwendet, sondern Zeilen- und Spaltennummern. So wird aus der Zelle A1 eben Z1S1 und aus Zelle B5 wird Z5S2. Die oben verwendete Angabe „ZS(-1)“ verweist damit auf die Zelle links neben sich.

Verwendung der definierten Namen in Excel-Formeln und Berechnungen

Nach dem Definieren des Namens können Sie diesen in einer Zelle verwenden, um die entsprechende Farbe zu extrahieren. In der folgenden Abbildung sehen Sie eine Tabelle, in der Umsätze mit einer Hintergrundfarbe hervorgehoben worden sind. Sie wollen diese Umsätze anhand der Hintergrundfarben nun summieren oder zählen.

Schreiben Sie in eine Zelle rechts neben ersten Umsatzzahl die Formel =Hintergrundfarbe. Excel zeigt dann die entsprechende Zahl (Farbcode) für die Hintergrundfarbe an, die in der Zelle links daneben hinterlegt ist.

Ziehen oder kopieren Sie die Formel nach unten, um die Farben (Farbcodes) der anderen Zellen auszulesen.

Mit diesen Zahlen (Farbcodes) in der Hilfsspalte können Sie nun weitere Berechnungen durchführen, zum Beispiel die Anzahl der Zellen mit einer bestimmten Farbe zählen.

Beispiel: Zählen aller gelben Zellen

Um die Anzahl der Zellen mit einer bestimmten Hintergrundfarbe zu zählen, verwenden Sie die ZÄHLENWENN-Funktion. Zählen Sie einfach, wie oft der Farbcode 6 (gelber Hintergrund) in der Hilfsspalte C vorkommt.

Beispiel: Summieren von allen gelben Zellen

Um die gelben Zellen zu summieren, verwenden Sie die SUMMEWENN-Funktion.

Die SUMMEWENN-Funktion in der obigen Abbildung durchsucht die Hilfsspalte C nach dem Farbcode 6 und summiert die entsprechenden Zellen in der Spalte B.

Aktualisieren bei Änderung der Hintergrundfarbe

Beachten Sie, dass Änderungen der Hintergrundfarbe nicht automatisch aktualisiert werden. Sie müssen hierzu die F9-Taste drücken, um die Berechnungen zu aktualisieren.

Dateiformat

Da es sich bei der Funktion ZELLE.ZUORDNEN() um eine Makrofunktion handelt, müssen Sie die Datei im Format xlsm speichern.