Der Funktionsumfang von Excel entwickelt sich ständig weiter. Wo zur Auswertung von Projektdaten bisher Pivot-Tabellen, komplexe Formeln oder der Spezialfilter notwendig waren, reichen jetzt wenige, kurze Formeln. Denn seit einiger Zeit gibt es die neuen dynamischen Arrayfunktionen. Sie stehen allen Anwendern von Excel 365 zur Verfügung, die das Update von Juli 2020 oder später heruntergeladen haben.

Dieser Tipp stellt drei der neuen Arrayfunktionen vor und zeigt anhand von Beispielen, wie diese das Auswerten von Listen mit Projektdaten erleichtern. Sie finden die Funktionen EINDEUTIG, SORTIEREN und FILTER über die Registerkarte Formeln unter Nachschlagen und Verweise (Bild 1).

Bild 2 zeigt die für das Beispiel im Artikel verwendeten Daten. Die ursprüngliche Liste wurde mit Strg + T in eine "intelligente" Tabelle umgewandelt. So bleiben die Auswertungen stets aktuell, auch wenn sich die Daten der Tabelle ändern oder die Datenbasis erweitert wird. Der Tabelle wurde der Name tbl_Projektliste zugewiesen.

Die Beispieldateien zu diesem Artikel können Sie gemeinsam mit dem Artikel herunterladen.

Aufgabe 1: Abzurechnende Stunden auswerten – getrennt nach Projekten und Leistungen

Als erstes sollen die abzurechnenden Stunden für jedes Projekt der Tabelle zusammengefasst werden. Das ließe sich zwar auch mit einer Pivot-Tabelle lösen, allerdings müsste diese jedes Mal manuell aktualisiert werden, wenn sich die Datenbasis ändert. Die folgende formelgestützte Variante zeigt dagegen stets den aktuellen Stand an.

Um die abzurechnenden Stunden auszuwerten, benötigen Sie zuerst eine Liste, die jedes Projekt nur einmal anzeigt. Dies erledigt die Funktion EINDEUTIG in Zelle H6 (Bild 3).

• Nach der Eingabe von =EINDEUTIG( markieren Sie die gesamte Projektspalte mit einem Mausklick genau über der Spalte "Projekt" (der Mauszeiger verwandelt sich dabei in einen senkrechten schwarzen Pfeil).
• Mit Enter schließen Sie die Formel ab. Sie lautet jetzt: =EINDEUTIG(tbl_Projektliste[Projekt]) (Bild 3).

So funktioniert die Formel

• Die in H6 eingegebene Formel erzeugt einen Überlaufbereich, der in diesem Fall bis H11 reicht.
• Dieser Überlaufbereich wird automatisch mit einem Rahmen gekennzeichnet. Er verschwindet, wenn eine Zelle außerhalb des Überlaufbereichs angeklickt wird.
• Die Formel selbst kann nur in Zelle H6 verändert werden. In den darunter liegenden Zellen ist die Formel ausgegraut (Bild 4).
• Wird in der Datentabelle in Spalte C entweder ein Projekt hinzugefügt, entfernt oder umbenannt, passt die Formel EINDEUTIG die Projektliste automatisch an.

Mittels einer Formel die Daten gleich noch sortieren lassen

Die Projektliste mit den eindeutigen Werten ist der erste Teilschritt. Meist jedoch sollen Listen sortiert erscheinen. Das erledigt die Funktion SORTIEREN. Wie in Bild 5 zu sehen ist, wird dazu die EINDEUTIG-Funktion einfach in die SORTIEREN-Funktion eingebaut.

Analog lässt sich das auch für die Liste der Leistungen in Zelle O6 umsetzen. Bild 6 zeigt das Ergebnis und die Formel.

Die erbrachten Stunden nach Projekt und nach Leistung zusammenfassen

Nachdem die Projekte und die Leistungen dynamisch aufgelistet werden, sorgt SUMMEWENN dafür, dass die abzurechnenden Stunden für jedes Projekt und jede Leistung addiert werden.

Flexibel für weitere Zeilen: Die SUMMEWENN-Formel anpassen

Damit SUMMEWENN auch dann funktioniert, wenn in der benachbarten linken Spalte weitere Projekte oder Leistungen hinzukommen, wird die Berechnung in eine WENN-Formel eingebaut. Sie sorgt dafür, dass SUMMEWENN nur dann verwendet wird, wenn in Spalte L bzw. O ein Text steht (ISTTEXT).

Aufgabe 2: Nur die Daten eines bestimmten Projekts anzeigen lassen (Funktion FILTER)

Aus der in Bild 2 gezeigten Datentabelle interessieren nur die Daten zu einem bestimmten Projekt? Dies ließe sich über das Einschalten eines Filters mit Strg + Umschalt + L, einen Datenschnitt, einen Spezialfilter oder eine Pivot-Tabelle lösen. Schneller und flexibler erledigen Sie das jedoch mit der neuen Arrayfunktion FILTER. Bild 10 zeigt, dass eine einzige Formel alle gefilterten Datensätze zu einem Projekt auflistet, das zuvor komfortabel per Dropdown in Zelle H6 ausgewählt wurde.

In der FILTER-Funktion werden zuerst die Spalten festgelegt, aus denen gefilterte Daten gewünscht werden, hier von Leistung bis Zeit. Das zweite Argument in der unten gezeigten Formel gibt an, wonach gefiltert werden soll, hier nach dem Projekt, das zuvor per Dropdown in Zelle H6 ausgewählt wurde.

In der Beispieldatei zu Aufgabe 2 sehen Sie, dass sich die gefilterten Datensätze auch sortieren lassen, beispielsweise nach Anzahl der abzurechnenden Stunden, und zwar absteigend (Bild 11).

Aufgabe 3: Zu jedem Projekt die Gesamtstunden auflisten

Noch detaillierter wird die Auswertung, wenn nicht nur die abzurechnenden Stunden für jedes Projekt addiert werden, sondern zusätzlich auch die einzelnen Stundeneinträge sichtbar sind, wie in Bild 12 gezeigt. Die Lösung lässt sich mit Hilfe von EINDEUTIG und FILTER in weniger als fünf Minuten aufbauen und ist besser als jede Pivot-Tabelle, da sie jederzeit automatisch den aktuellen Stand anzeigt.

Projektnamen und Stundenwerte auflisten

Das Auflisten der Projektnamen als Spaltenüberschriften in Zeile 5 erledigt wieder EINDEUTIG. Allerdings ordnet diese Funktion die Ergebnisse standardmäßig untereinander an. Damit sie nebeneinander erscheinen, kombinieren Sie EINDEUTIG mit MTRANS. Diese Funktion dreht die Anzeige der Ergebnisse um 90° (Bild 13).

• Nachdem die einzelnen Projekte ab Zelle I5 aufgelistet sind, lassen Sie mit folgender Formel die zugehörenden Stundenwerte anzeigen: =FILTER(tbl_Projektliste[Zeit];tbl_Projektliste[Projekt]=I5).
• Kopieren Sie diese Formel am Ausfüllkästchen nach rechts, um pro Spalte die Stundenauflistungen zu jedem Projekt zu erzeugen – und zwar je nach Datenlage unterschiedlich lang.

Eine kleine Statistik mit zwei kurzen Formeln

Zusätzlich zu der Stundenauflistung soll die Auswertung noch folgende Fragen beantworten: Wie viele Einträge pro Projekt gibt es? Und wie viele Stunden pro Projekt können abgerechnet werden? Die Ergebnisse liefern die in Bild 14 gelb hervorgehoben kurzen Formeln. Beiden nutzen den neuen Überlauf-Operator #. Das Zeichen # hinter der I6 bedeutet, dass der zu berechnende Bereich in I6 beginnt, aber je nach Datenlage automatisch in der erforderlichen Größe nach unten ausgedehnt wird.

Die fertige Lösung finden Sie in der Beispieldatei zu Aufgabe 3.