Eine Formel viele Auswertungen: PIVOTMIT mit variablen Feldern

Die beiden Argumente Zeilen_felder und Spalten_felder kannst du variabel gestalten. Statt feste Zeilen- und Spaltenangaben zu verwenden, reicht es, die gewünschten Feldnamen in Zellen einzutippen. Das Ergebnis passt sich automatisch an.

Bonus: Im Bonusteil erfährst du, wie du deine Auswertung flexibel erweiterst, zum Beispiel mit einem Filter nach Regionen.

Tipp: Im ersten Teil zur Funktion PIVOTMIT wird diese Funktion erklärt.

So erstellst du die Berechnung mit variablen Zeilen- und Spaltenangaben

In diesem Beispiel sollen die Umsätze aller Verkäufer pro Gruppe ermittelt werden. In den Zellen H5 und J3 stehen die beiden Felder, die berechnet werden sollen.

• Klicke in die Zelle, ab der du das Ergebnis sehen möchtest. Diese Zelle kann auch auf einem anderen Tabellenblatt oder einer anderen Arbeitsmappe sein. In diesem Beispiel ist es die Zelle I4.

• Starte mit [Umschalt] + [F3] den Funktionsassistenten und öffne die Funktion PIVOTMIT().

• Für das erste Argument Zeilen_felder gib folgenden Ausdruck ein:
  SPALTENWAHL(A1:D135;XVERGLEICH(H5; A1:D1)).

• Hinweis: Mit SPALTENWAHL wird genau die Spalte aus der Tabelle geholt, deren Überschrift in Zelle H5 steht. XVERGLEICH ermittelt dafür automatisch die richtige Spaltennummer.

• Für das zweite Argument Spalten_felder  gib folgenden Ausdruck ein:
  SPALTENWAHL(A1:D135;XVERGLEICH(J3;A1:D1)).

• Klicke ins dritte Argument Werte und wähle die Zellen, deren Inhalt du berechnen möchtest (F1:F135). In diesem Beispiel die Zellen mit den Umsätzen.

• Klicke ins Feld Funktion und gib dort den Begriff SUMME ein. Wenn du aus dem Feld klickst, kann rechts daneben der Hinweis #WERT! erscheinen. Den kannst du zunächst ignorieren.

• Klicke auf OK.

Die Formel setzt eine der neuen Matrix-Funktionen ein. Hierbei ist es wichtig, dass alle Zellen neben und unter der Zelle mit der Formel leer sind. Ansonsten erscheint die Fehlermeldung #ÜBERLAUF!.

• Markiere die Zellen mit den Umsätzen und weise ihnen das Währungsformat zu.

Hinweis: Die Formel steht nur in der ersten Zelle. Nur hier kannst du Änderungen vornehmen.

Tipp: Im Beitrag So bringst du Listen automatisch zusammen, deren Überschriften nicht in der gleichen Reihenfolge sind findest du Informationen zur Funktion SPALTENWAHL

Bonus: Auswertung per Filter einschränken

Möchtest du die Auswertung auf eine bestimmte Region beschränken, kannst du zusätzlich das Argument Filter_matrix verwenden.

• Erweitere die Tabelle: Gib in die Zelle H2 den Text Region ein und die Zelle I2 die Region, nach der du filtern möchtest, z.B. Region 2.

• Klick in die Zelle, in der die Formel steht, die du gerade erfasst hast. In diesem Beispiel I4. Drücke die Tastenkombination [Strg] + [F3], um das Dialogfeld Funktionsargumente zu öffnen.

• Blättere nun nach unten und klicke ins Feld Filter_matrix. Gib folgenden Ausdruck ein:
  SPALTENWAHL(A1:F135;XVERGLEICH(H3;A1:F1))=I3

• Klicke auf OK.

Mit dieser Technik schreibst du eine PIVOTMIT‑Formel nur einmal und steuerst die Auswertung vollständig über Zellinhalte.