Excel: Starke Konkurrenz für Pivot – wichtige Projekt-Kennzahlen auf einen Blick

Teil 2:
Das Cockpit erweitern mit SUMMEWENNS & Jokern
Diese Beitragsserie stellt eine Alternative zu Pivot-Tabellen vor, um Projektdaten in Microsoft Excel zu analysieren und das Ergebnis kompakt in einem "Cockpit" darzustellen. Hildegard Hügemann und Dieter Schiecke zeigen im zweiten Teil, wie Sie in Excel ab 2007 auch komplexe Abfragen gestalten, die mehrere Abfragekriterien miteinander kombinieren. Die fertige Beispieldatei können Sie zusammen mit dem Artikel herunterladen.

 

Excel: Starke Konkurrenz für Pivot – wichtige Projekt-Kennzahlen auf einen Blick

Teil 2:
Das Cockpit erweitern mit SUMMEWENNS & Jokern
Diese Beitragsserie stellt eine Alternative zu Pivot-Tabellen vor, um Projektdaten in Microsoft Excel zu analysieren und das Ergebnis kompakt in einem "Cockpit" darzustellen. Hildegard Hügemann und Dieter Schiecke zeigen im zweiten Teil, wie Sie in Excel ab 2007 auch komplexe Abfragen gestalten, die mehrere Abfragekriterien miteinander kombinieren. Die fertige Beispieldatei können Sie zusammen mit dem Artikel herunterladen.

 

Wie der erste Teil dieses Beitrags zeigt, gibt es außer Pivot-Tabellen auch andere leistungsfähige Techniken, um Projektdaten zu analysieren und das Ergebnis in kompakter Form darzustellen. Die in Teil 1 vorgestellten Techniken basieren auf den Funktionen ZÄHLENWENN und SUMMEWENN, mit denen sich bedingte Abfragen anhand eines bestimmten Kriteriums aufbauen lassen.

Nicht immer reicht jedoch ein einziges Kriterium aus, um aussagekräftige Abfragen zu erstellen. Lesen Sie in diesem zweiten Teil, wie Sie Auswertungen mit mehreren Abfragekriterien definieren können. Die dabei verwendeten Funktionen SUMMEWENNS und MITTELWERTWENNS sind ab Excel 2007 standardmäßig verfügbar. Eine weitere hilfreiche Funktion ist SUMMENPRODUKT, mit der Sie auch Abfragen aufbauen können, in denen die Kriterien nicht mit UND, sondern mit ODER verknüpft sind.

Dieser Beitrag knüpft unmittelbar an den ersten Teil an und stellt die Techniken für die Abfragen 3 bis 7 des dort beschriebenen Beispiels vor (Bild 1). Die Tabelle mit den Projektdaten des Beispiels (Bild 2) können Sie zusammen mit diesem Beitrag herunterladen.

Bild 1: Das Beispielcockpit – erstellt mit den Formeln SUMMEWENN, SUMMEWENNS, MITTELWERTWENNS und SUMMENPRODUKT.

Bild 2: Auszug aus der Datentabelle des Beispiels.
Bild vergrößern

Abfrage 3: Wie hoch sind die Istkosten in einem Quartal für eine bestimmte Abteilung?

Eine typische Fragestellung in unserem Beispiel könnte lauten: Wie viel Euro wurden im 4. Quartal für EDV-Projekte ausgegeben? In diesem Fall müssen also mehrere Kriterien geprüft werden, bevor summiert wird: Die Abteilung und der Zeitraum (hier das Quartal).

Mit Hilfe der Funktion SUMMEWENNS können Sie diese Frage beantworten. So geht’s:

  1. Wählen Sie zunächst im Auswertungsbereich in Zelle B10 in der Dropdown-Liste den Eintrag "EDV". Geben Sie rechts daneben in C10 die Zahl für das gesuchte Quartal ein – hier also eine "4".
  2. Markieren Sie D10. Hier soll die Summe der Kosten berechnet werden, für die die Kriterien in B10 und C10 zutreffen.
  3. Tippen Sie "=SU", wählen Sie aus der angezeigten Funktionsliste den Eintrag SUMMEWENNS aus und übernehmen Sie die Funktion per Tab-Taste in die Bearbeitungsleiste.
  4. Zur Eingabe des ersten Arguments ("Summe_Bereich") positionieren Sie in der Datentabelle den Mauszeiger über der Spalte "Istkosten" und klicken Sie, sobald dieser sich in einen kleinen schwarzen Pfeil verwandelt. Die angezeigte Formel lautet jetzt =SUMMEWENNS(Projekte[Istkosten].
  5. Setzen Sie ein Semikolon und markieren Sie auf die gleiche Weise die Spalte "Abteilung", um das zweite Argument festzulegen ("Kriterien_Bereich1"). In der Bearbeitungsleiste steht jetzt als vorläufige Formel: =SUMMEWENNS(Projekte[Istkosten];Projekte[Abteilung].
  6. Nach einem weiteren Semikolon klicken Sie auf Zelle B10. Dort steht das erste Kriterium – hier "EDV" für die zu berücksichtigende Abteilung. Die Formel lautet nun: =SUMMEWENNS(Projekte[Istkosten];Projekte[Abteilung];B10.
  7. Nach einem erneuten Semikolon lassen Sie das Argument für Kriterien_Bereich2 folgen. Markieren Sie dazu wieder mit dem kleinen schwarzen Pfeil die Spalte "Quartal". Die Formel lautet jetzt: =SUMMEWENNS(Projekte[Istkosten];Projekte[Abteilung];B10;Projekte[Quartal].
  8. Fügen Sie nach einem weiteren Semikolon das Argument Kriterien2 hinzu, indem Sie auf C10 klicken, also die Zelle mit der Ziffer des gewünschten Quartals. Schließen Sie mit Enter ab. Die komplette Formel ist:
    =SUMMEWENNS(Projekte[Istkosten];Projekte[Abteilung];B10;Projekte[Quartal];C10).

Bild 3: Mit dieser SUMMEWENNS-Funktion werden zwei Auswahlkriterien beim Addieren von Werten berücksichtigt.

Abfrage 4: Wie viele Projekte wurden in den ersten vier Monaten von 2012 abgeschlossen?

Umfasst der abzufragende Zeitraum ein komplettes Quartal, kann in der Formel auf die Informationen in der Spalte "Quartal" zurückgegriffen werden, um den Zeitraum anzugeben. Doch was, wenn der zu betrachtende Zeitraum nicht genau einem Quartal entspricht, sondern kürzer oder länger ist? So kann beispielsweise im Mai die Aufgabe anstehen, die Anzahl der Projekte zu ermitteln, die seit Jahresbeginn bis zum Ende des Monats April abgeschlossen wurden.

Hier muss die Formel drei Bedingungen berücksichtigen: Den Projektstatus (in dem Fall D für "Fertig"), den Beginn sowie das Ende des gewünschten Zeitraums. Bild 4 zeigt die Ausgangssituation. Zur Lösung der Aufgabe verwenden Sie diesmal die Funktion ZÄHLENWENNS.

Bild 4: Beim Berechnen der Anzahl werden drei Kriterien berücksichtigt.

  1. Geben Sie zunächst in den Zellen B13, C13 und D13 die Kriterien ein (Bild 4).
  2. Markieren Sie Zelle E13. Hier soll die Anzahl der Projekte berechnet werden, die alle drei Kriterien erfüllen.
  3. Tippen Sie "=Z" ein. Wählen Sie mit der Pfeiltaste nach unten den Eintrag ZÄHLENWENNS und holen Sie diesen per Tab-Taste in die Bearbeitungsleiste.
  4. Klicken Sie zum Festlegen des ersten Arguments (Kriterienbereich1) über der Spalte "Status" mit dem kleinen schwarzen Pfeil. Setzen Sie ein Semikolon und klicken Sie anschließend auf B13, in der das Kriterium steht. Die angezeigte Formel lautet: =ZÄHLENWENNS(Projekte[Status];B13.
  5. Tippen Sie ein weiteres Semikolon ein. Für das Argument Kriterienbereich2 markieren Sie mit dem kleinen schwarzen Pfeil die Spalte "Beginn".
  6. Nach einem Semikolon geben Sie für das Argument Kriterien2 die Zeichenfolge ">="& ein und klicken auf Zelle C13. Die Formel lautet nun: =ZÄHLENWENNS(Projekte[Status];B13;Projekte[Beginn];">="&C13.
  7. Ergänzen Sie nach einem weiteren Semikolon die Bezüge für die Argumente Kriterienbereich3 und Kriterien3. Markieren Sie dafür mit dem schwarzen Pfeil die Spalte "Ende". Tippen Sie nach einem Semikolon die Zeichenfolge "<="& ein. Klicken Sie dann auf Zelle D13.…

Bewertungen und Kommentare

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
2 Kommentare anzeigen & selbst mitreden!
Gesamt
Bewertungen 2
Kommentare 2

Fortsetzungen des Fachartikels

Teil 1:
Ein Auswertungs-Cockpit mit flexiblen Abfragen aufbauen
Viele denken zuerst an Pivot-Tabellen, wenn es um die Auswertung von Projektdaten in Microsoft Excel geht. Doch Excel bietet auch andere Techniken, um eine große Menge an Daten zu analysieren und die Ergebnisse kompakt darzustellen.
Teil 3:
Ein Cockpit mit Datenbank-Funktionen aufbauen
In den ersten beiden Teilen dieser Artikelreihe haben Sie erfahren, wie Sie Projektdaten auch ohne Pivot-Tabellen analysieren und die Ergebnisse kompakt und übersichtlich darstellen können.

Alle Kommentare (2)

Christa
Gegendorfer

sehr gut und nachvollziehbar beschrieben!

 

Oliver
Dattner

immer wieder sehr hilfreich !