Einfacher und intuitiver als Pivot & Co. Excel 365 – Projektauswertung leicht gemacht mit den neuen Arrayfunktionen

Excel 365 – das bieten die neuen Arrayfunktionen

Pivot-Tabellen, komplexe Formeln oder der Spezialfilter: Um Projektdaten auszuwerten, kam man bisher um umfangreiche Werkzeuge nicht herum. Mit den neuen Arrayfunktionen gelingt das jetzt einfacher und intuitiver. Dieter Schiecke zeigt anhand von drei Beispielen, was die neuen Funktionen leisten und wie sie Projektauswertungen erleichtern.

Management Summary

  • Neue, dynamische Arrayfunktionen in Excel 365 eröffnen leistungsfähige Möglichkeiten zum Erstellen aktueller Auswertungen.
  • Drei verschiedene Beispiele zeigen, wie sich die neuen Arrayfunktionen einsetzen lassen, um typische Fragestellungen bei der Auswertung von Projektlisten zu beantworten.
  • Die beiden Arrayfunktionen EINDEUTIG und SORTIEREN eignen sich z.B. als Basis für eine stets aktuelle Stundenabrechnung.
  • Die Funktion FILTER erlaubt, spezifisch nur solche Datensätze aufzulisten, die zu einem bestimmten Projekt gehören.
  • Durch Kombination der Funktionen EINDEUTIG und FILTER lässt sich schnell eine Stundenabrechnung mit Detaildaten aufbauen. Der neue Überlaufoperator "#" sorgt dabei trotz dynamischer Wertebereiche für kurze Formeln.

Einfacher und intuitiver als Pivot & Co. Excel 365 – Projektauswertung leicht gemacht mit den neuen Arrayfunktionen

Excel 365 – das bieten die neuen Arrayfunktionen

Pivot-Tabellen, komplexe Formeln oder der Spezialfilter: Um Projektdaten auszuwerten, kam man bisher um umfangreiche Werkzeuge nicht herum. Mit den neuen Arrayfunktionen gelingt das jetzt einfacher und intuitiver. Dieter Schiecke zeigt anhand von drei Beispielen, was die neuen Funktionen leisten und wie sie Projektauswertungen erleichtern.

Management Summary

  • Neue, dynamische Arrayfunktionen in Excel 365 eröffnen leistungsfähige Möglichkeiten zum Erstellen aktueller Auswertungen.
  • Drei verschiedene Beispiele zeigen, wie sich die neuen Arrayfunktionen einsetzen lassen, um typische Fragestellungen bei der Auswertung von Projektlisten zu beantworten.
  • Die beiden Arrayfunktionen EINDEUTIG und SORTIEREN eignen sich z.B. als Basis für eine stets aktuelle Stundenabrechnung.
  • Die Funktion FILTER erlaubt, spezifisch nur solche Datensätze aufzulisten, die zu einem bestimmten Projekt gehören.
  • Durch Kombination der Funktionen EINDEUTIG und FILTER lässt sich schnell eine Stundenabrechnung mit Detaildaten aufbauen. Der neue Überlaufoperator "#" sorgt dabei trotz dynamischer Wertebereiche für kurze Formeln.

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).

Die hellgrün unterlegten Einträge sind drei der sechs neuen Arrayfunktionen, die Excel 365 zur Verfügung stellt
Bild 1: Die hellgrün unterlegten Einträge sind drei der sechs neuen Arrayfunktionen, die Excel 365 zur Verfügung stellt

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.

 Aus den Daten der Beispieltabelle sollen die abzurechnenden Stunden – nach Projekten und nach Leistungen – ermittelt werden
Bild 2: Aus den Daten der Beispieltabelle sollen die abzurechnenden Stunden – nach Projekten und nach Leistungen – ermittelt werden

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).
Die neue Funktion EINDEUTIG listet alle in der Datentabelle enthaltenen Projektnamen genau einmal auf.
Bild 3: Die neue Funktion EINDEUTIG listet alle in der Datentabelle enthaltenen Projektnamen genau einmal auf.

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.
Die Array-Funktion wird nur in der obersten Zelle eingegeben oder geändert (links), sie läuft dann nach unten über und erscheint in den anderen Zellen ausgegraut (rechts).
Bild 4: Die Array-Funktion wird nur in der obersten Zelle eingegeben oder geändert (links), sie läuft dann nach unten über und erscheint in den anderen Zellen ausgegraut (rechts).

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.

Die neue Funktion SORTIEREN ordnet die Liste der Projekte zusätzlich alphabetisch.
Bild 5: Die neue Funktion SORTIEREN ordnet die Liste der Projekte zusätzlich alphabetisch.

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

Hier liefern SORTIEREN und EINDEUTIG eine alphabetisch sortierte Liste der Leistungen.
Bild 6: Hier liefern SORTIEREN und EINDEUTIG eine alphabetisch sortierte Liste der Leistungen.

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.

Die abzurechnenden Stunden für jedes Projekt und jede Leistung werden mit Hilfe von SUMMEWENN addiert.
Bild 7: Die abzurechnenden Stunden für jedes Projekt und jede Leistung werden mit Hilfe von SUMMEWENN addiert.

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).

Diese Formel fasst pro Projekt die anzurechnenden Stunden zusammen …
Bild 8: Diese Formel fasst pro Projekt die anzurechnenden Stunden zusammen …
 … und diese Formel erledigt das analog für jede Leistungsart
Bild 9: … und diese Formel erledigt das analog für jede Leistungsart

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.

Mit Hilfe der neuen FILTER-Funktion werden nur die Datensätze zu dem in H6 ausgewählten Projekt aufgelistet.
Bild 10: Mit Hilfe der neuen FILTER-Funktion werden nur die Datensätze zu dem in H6 ausgewählten Projekt aufgelistet.

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).

Auch das geht jetzt per Formel: Daten aus einer Liste filtern und nach einem bestimmten Kriterium sortieren lassen
Bild 11: Auch das geht jetzt per Formel: Daten aus einer Liste filtern und nach einem bestimmten Kriterium sortieren lassen

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.

Auswertung, die wie ein Kontoauszug zu jedem Projekt die erbrachten Stunden auflistet und zusammenfasst.
Bild 12: Auswertung, die wie ein Kontoauszug zu jedem Projekt die erbrachten Stunden auflistet und zusammenfasst.

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).

Mit MTRANS werden die Projektnamen, die EINDEUTIG liefert, in einer Zeile statt als Spalte aufgelistet
Bild 13: Mit MTRANS werden die Projektnamen, die EINDEUTIG liefert, in einer Zeile statt als Spalte aufgelistet
  • 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.

Auswertung

Die beiden gelb markierten Formeln nutzen den neuen Überlauf-Operator #, der dafür sorgt, dass sich ändernde Bereiche jederzeit korrekt berechnet werden.
Bild 14: Die beiden gelb markierten Formeln nutzen den neuen Überlauf-Operator #, der dafür sorgt, dass sich ändernde Bereiche jederzeit korrekt berechnet werden.


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

Download ZIPDownload ZIP

Bewertungen und Kommentare

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung.
0 Kommentare anzeigen & selbst mitreden!
Gesamt
Bewertungen 4
Kommentare 0

Das könnte Sie auch interessieren