Office-Blog

Seit März 2022 gibt es 14 neue Funktionen in Excel 365, allerdings bisher nur im Betakanal. Nun sind sie auch im Updatekanal Aktueller Kanal (Vorschau) angekommen. Eine der neuen Funktionen ist ZUSPALTE. Mit ihr lassen sich Daten, die auf mehrere Spalten verteilt sind, in einer zusammenfassen. Damit kann ich für die unten links gezeigte Tabelle ermitteln, wer als Ersthelfer eingesetzt wurde und wie oft. Ich nutze dafür neben der Funktion ZUSPALTE noch EINDEUTIG, SORTIEREN und ZÄHLENWENN.

1) So funktioniert ZUSPALTE standardmäßig

Beim Betrachten der unten abgebildeten Ausgangstabelle werden manche denken, dass dies mit ZÄHLENWENN schnell erledigt ist. Das würde zutreffen, wenn es einen festen Personenkreis gäbe, der als Ersthelfer eingesetzt wird. Doch hier ist die Situation so, dass Woche für Woche andere Personen als Ersthelfer zum Einsatz kommen.

So sieht die Ausgangstabelle aus

Da es keine feste Liste der eingesetzten Personen gibt, muss sie aus den jeweils vorliegenden Daten dynamisch zusammengesetzt werden. Hier kommt ZUSPALTE ins Spiel. So gehe ich vor.

• Zuerst wandele ich die Übersicht mit den eingesetzten Ersthelfern (Zellbereich B3:G6) mit Strg + T in eine Tabelle um. Diese erhält den Namen tbl_Ersthelfer.
• In Zelle I3 erzeuge ich eine einspaltige Liste aller Einträge von Montag bis Freitag, die bisher auf 5 Spalten und 3 Zeilen verteilt sind. Dazu gebe ich ein =ZUSPALTE(
• Nun markiere ich den Datenbereich von Montag bis Freitag und schließe mit Enter ab.
  Die Formel lautet =ZUSPALTE(tbl_Ersthelfer[[Mo]:[Fr]]).
• Die Formel trägt nicht nur in Zelle I3 ein Ergebnis ein, sondern listet ab Zelle I3 alle Namen untereinander auf. Sie erzeugt einen sog. SPILL-Bereich, denn ZUSPALTE gehört zur Gruppe der dynamischen Arrayfunktionen.
• Einen kleinen Schönheitsfehler hat die eben erzeugte Liste noch: sie enthält zwei Nullen, da zwei Zellen in der Übersicht leer sind.

Dieses Ergebnis liefert die Standardvariante der Funktion ZUSPALTE

2) Die Nullen mit einem zusätzlichen Parameter vermeiden

Zur Syntax der Funktion ZUSPALTE gehören neben de Argument für die auszuwertende Matrix noch die beiden Argumente Ignore und Scan_by_column.
Der Parameter 1 für das Argument Ignore bewirkt, dass leere Zellen ignoriert werden. Demzufolge ergänze ich die ZUSPALTE-Formel um die 1.

Mit dem Parameter 1 werden leere Zellen nicht berücksichtigt

Hinweis: Auf dieser Seite liefert Microsoft eine komplette Beschreibung der Syntax von ZUSPALTE.

3) Die Liste auf eindeutige Werte reduzieren und diese sortieren

In der von ZUSPALTE generierten Liste in Spalte I tauchen momentan die Namen der Ersthelfer mehrfach auf. Mit EINDEUTIG sorge ich in Spalte J dafür, dass jeder Name nur einmal aufgelistet wird. Dazu nutze ich in Zelle J3 folgende Formel: =EINDEUTIG(I3#).

Das #-Zeichen hinter dem Zellbezug auf I3 bewirkt, dass alle Zellen ab I3 berücksichtigt werden, egal, wie viele es sind. Kommt der Name eines weiteren Ersthelfers links in der Übersicht hinzu, erweitert sich die Liste in Spalte I automatisch um eine neue Zeile. Diese Dynamik berücksichtige ich in der EINDEUTIG-Funktion, indem ich keinen festen Zellbezug wie I3:I15 verwende, sondern mit I3# nur den Startpunkt der Liste angebe.

In Spalte J die Duplikate dynamisch entfernen mit EINDEUTIG

Um eine sortierte Liste der Namen zu haben, umschließe ich die EINDEUTIG-Funktion mit der Funktion SORTIEREN.

In Spalte J eindeutige Werte auflisten und automatisch sortieren lassen

4) Alle Funktionen in einer Formel kombinieren

Die bisher beschriebenen Schritte zeigen, mit welchen Funktionen die Auswertung aufgebaut wird. Diese Schritte kann ich in einer Formel zusammenfassen.

• Zuvor trage ich in I3 und J3 Überschriften für die Auswertung ein.
• Dann stelle ich in Zelle I4 die folgende Formel auf: =SORTIEREN(EINDEUTIG(ZUSPALTE(tbl_Ersthelfer[[Mo]:[Fr]];1))).

Alles in einer Formel: ZUSPALTE, EINDEUTIG und SORTIEREN kombinieren

5) Zählen, wie oft jeder Name vorkommt

In Spalte J ermittle ich nun, wie oft jeder der Ersthelfer in KW 29 zum Einsatz kam. Das erledige ich mit ZÄHLENWENN.

• In Zelle J4 gebe ich folgende Formel ein: =ZÄHLENWENN(tbl_Ersthelfer[[Mo]:[Fr]];I4#).
• Je nach Anzahl der eingesetzten Ersthelfer kann die in Spalte I erzeugte Liste mal kürzer und mal länger sein. Daher mache ich den Zellbezug auf das Argument Suchkriterium in der ZÄHLENWENN-Funktion wieder dynamisch, indem ich I4# eingebe.

Die Anzahl mit ZÄHLENWENN ermitteln, aber dynamisch dank des #-Zeichens hinter dem Bezug auf Zelle I4

So funktioniert ZUSPALTE auch bei einer Monatsauswertung

Was in einer kleine Liste für nur eine Woche funktioniert, lässt sich natürlich auch für größere Tabelle nutzen, wie die folgende Abbildung zeigt.

Eine Monatsübersicht lässt sich mit ZUSPALTE ebenfalls im Nu auswerten

Die Formel in Zelle G4 lautet:

Als Formel in Zelle H4 gebe ich ein:

Verfügbarkeit der 14 neuen Funktionen in Excel 365

Wie eingangs erwähnt, sind ZUSPALTE und die weiteren neuen Funktionen ab Ende Juli im Updatekanal Aktueller Kanal (Vorschau) verfügbar.

Das lässt hoffen, dass alle, die den vielerorts verwendeten Updatekanal Aktueller Kanal haben, in den nächsten Wochen in den Genuss der neuen Funktionen kommen.

Über die Befehlsfolge Datei > Konto lässt sich überprüfen, welche Version und welcher Kanal eingestellt ist.

Ab Ende Juli sind die 14 neuen Funktionen auch in Aktueller Kanal (Vorschau) verfügbar

Interesse an weiteren dynamischen Arrayfunktionen?

Hier im Blog wird es ganz sicher weitere Beiträge zu den 14 neuen Funktionen vom März 2022 geben. Wer sich mit allen Neuerungen vertraut machen möchte, kann sich schon mal den Termin der nächsten Excel-Kompetenztage vormerken: 22. bis 24. Mai 2023.