Office-Blog

Wer mit dynamischen Arrayfunktionen arbeitet, wird früher oder später die Magie des #-Operators entdecken. Dieser ermöglicht es, mit nur einem Zeichen den kompletten Zellbereich zu adressieren, der mit einer dynamischen Arrayfunktion erzeugt wurde.

• Selbst alte Funktionen wie SUMMEWENN, ZÄHLENWENN, ANZAHL oder SUMME werden dynamisch, wenn der Überlauf-Operator # genutzt wird.
• Auch die Auswahl für Dropdowns kann mit Hilfe des #-Operators dynamisch gemacht werden.

Die folgenden drei Beispiele zeigen, WIE es funktioniert.

Beispiel 1: ZÄHLENWENN dynamisch machen

Die folgende Abbildung zeigt, worum es geht: In Spalte J soll zu jeder Person aus Spalte I ermittelt werden, wie oft sie im Dienstplan (Spalte B bis G) steht, wie viele Dienste sie also hat.

• Der Blogbeitrag vom 5.3.2025 zur Funktion EINDEUTIG erklärt, wie in Spalte I alle Personen aus dem Dienstplan mit Hilfe dynamischer Arrayfunktionen genau einmal aufgelistet werden.
• Mit der Funktion ZÄHLENWENN soll nun in Spalte J herausgefunden werden, wie oft jede der Personen im Dienstplan vorkommt.

In Spalte J soll die Anzahl der Dienste für jeden der Beschäftigten ermittelt werden; die Liste der Beschäftigten in Spalte I ist dynamisch, da sie mit dynamischen Arrayfunktionen generiert wird

Ich beginne mit der Eingabe der ZÄHLENWENN-Formel in Zelle J5.

• Sie lautet =ZÄHLENWENN(Dienste[[Montag]:[Freitag]];I5).
• Diese Formel kopiere ich durch das Ziehen am Ausfüllkästchen nach unten bis Zeile 9.

Das sieht erst einmal gut aus!

Die ZÄHLENWENN-Formel in Zelle J5 wie gewohnt nach unten kopieren

Das Problem: Die Inhalte in Spalte I sind dynamisch, in Spalte J jedoch nicht

• Ändere ich den Dienstplan so, dass eine neue Person hinzukommt, wird die Liste der Beschäftigen in Spalte I automatisch um einen Eintrag erweitert.
• Aber die ZÄHLENWENN-Formel erweist sich als statisch: Sie wird nicht automatisch erweitert.
• Bei jeder Änderung des Personenkreises im Dienstplan müsste der Bereich in Spalte J manuell korrigiert werden.

Das Problem: Kommen neue Daten hinzu, muss die ZÄHLENWENN-Formel per Hand korrigiert und nach unten erweitert werden

Die Lösung: Die ZÄHLENWENN-Formel dynamisch machen

Das Problem der statischen ZÄHLENWENN-Formel ist schnell behoben. So wird sie ebenfalls dynamisch:

• Ich lösche die nach unten kopierten Formeln in Spalte J und behalte nur die Ausgangsformel in J5.
• Dann markiere ich Zelle J5 und klicke oben in der Bearbeitungsleiste in die Formel.
• Dort setze den Cursor hinter den Zellbezug für das zweite Argument – hier also hinter I5.
• Ich ergänze direkt hinter I5 das Zeichen #.
• Sofort markiert Excel den Bereich aller Personen in Spalte I mit einem roten Rahmen.
• Mit Enter schließe ich die Korrektur der ZÄHLENWENN-Formel ab.

Das #-Zeichen hinter I5 hinzufügen und so die ZÄHLENWENN-Formel dynamisch machen

Hier noch mal der Vorher-nachher-Vergleich für die ZÄHLENWENN-Formel:
=ZÄHLENWENN(Dienste[[Montag]:[Freitag]];I5)
=ZÄHLENWENN(Dienste[[Montag]:[Freitag]];I5#)

Mit Hilfe des #-Operators wird also das zweite Argument von ZÄHLENWENN – das Suchkriterium – dynamisch gemacht.

Beispiel 2: SUMMEWENN dynamisch machen

Was bei ZÄHLENWENN gilt, funktioniert natürlich auch bei SUMMEWENN. Hier ein Beispiel dafür. Die folgende Abbildung zeigt, worum es geht:

• Der Blogbeitrag vom 5.3.2025 zur Funktion EINDEUTIG erklärt, wie in Spalte F alle Kunden aus der Bestelltabelle mit Hilfe dynamischer Arrayfunktionen genau einmal aufgelistet werden.
• Mit der Funktion SUMMEWENN soll nun in Spalte G herausgefunden werden, wie hoch der Gesamtbestellwert pro Kunde ist.

In Spalte G soll der Gesamtbestellwert je Kunde ermittelt werde; die Kundenliste in Spalte F ist dynamisch, denn sie wurde mit dynamischen Arrayfunktionen generiert

Um die Gesamtbestellwerte zu berechnen, gehe ich wie folgt vor:

• Ich markiere Zelle G5.
• Dort gebe ich folgende Formel ein =SUMMEWENN(Daten[Kunde];F5;Daten[Bestellwert]).
• Diese Formel kopiere ich durch das Ziehen am Ausfüllkästchen nach unten bis Zeile 9.

Die SUMMEWENN-Funktion liefert den Gesamtbestellwert je Kunde

Das Problem: Die Inhalte in Spalte F sind dynamisch, in Spalte G jedoch nicht

Wird nun links in der Tabelle mit den Bestellungen (Spalte B bis D) ein neuer Kunde hinzugefügt, erweist sich, dass die SUMMEWENN-Formel noch nicht optimal ist.

•  In Spalte F wird die Liste der Kunden automatisch um eine Zeile erweitert.
• In Spalte G hingegen fehlt ein Ergebnis und die SUMMEWENN-Formel muss per Hand nach unten kopiert werden.

Das Problem: Kommt bei den Bestellungen ein Kunde hinzu, wird in Spalte F die Kundenliste dynamisch erweitert, in Spalte G jedoch fehlt ein Ergebnis

Die Lösung: Die SUMMEWENN-Formel dynamisch machen

Auch in diesem Fall genügt beim Suchkriterium (hier Zelle F5) das Hinzufügen des #-Operators.

Die erweiterte Formel für Spalte G lautet =SUMMEWENN(Daten[Kunde];F5#;Daten[Bestellwert]).

Die Lösung: Auch hier macht der #-Operator die Formel dynamisch

Beispiel 3: Für Dropdowns die Auswahlliste dynamisch machen

Wie lässt sich im Beispiel mit dem Dienstplan sofort sehen, WER WANN Dienst hat? Das gelingt mit einem Dropdown und der Bedingten Formatierung.

• Über das Dropdown soll der Name einer Person ausgewählt werden können.
• Daraufhin sollen alle Dienstplan-Zellen zur ausgewählten Person farblich hervorgehoben werden.

Vorschau auf die Lösung: Per Dropdown automatisch alle Dienstplan-Zellen zu einer Person einfärben

Das Dropdown einrichten

• Über die Registerkarte Daten und den Befehl Datenüberprüfung öffne ich das unten gezeigte Dialogfeld.
• Unter Zulassen wähle ich den Eintrag Liste.
• Dann klicke ich in das Eingabefeld unter Quelle und markiere im Arbeitsblatt die Zelle I5.
• Den Zellbezug $I$5, den Excel mir liefert, erweitere ich um das #-Zeichen.
• Mit OK schließe ich die Einrichtung des Dropdowns ab.

Beim Einrichten des Dropdowns mit dem #-Operator die Auswahlliste dynamisch machen

Zur Erläuterung:
Das Zeichen # hinter dem Zellbezug bedeutet, dass der zu berechnende Bereich in der Zelle I5 beginnt, sich aber je nach Datenlage in seiner Größe automatisch nach unten oder rechts verändern kann.

Die Bedingte Formatierung einrichten

• Ich markiere alle Zellen mit Namen im Dienstplan – hier im Beispiel C5 bis G7.
• Auf der Registerkarte Start klicke ich auf Bedingte Formatierung > Regeln zum Hervorheben von Zellen > Gleich.
• Im folgenden Dialogfeld klicke ich für das Befüllen des linken Eingabefeldes auf die Zelle mit dem Dropdown – hier Zelle I2.
• Rechts lege ich über den Eintrag mit benutzerdefiniertem Format eine passende Füllfarbe fest.

Das Bedingte Format einrichten, das auf die Auswahl im Dropdown in I2 reagiert