So erstellst du eine Pivot-Auswertung ohne den Befehl PivotTable
Stelle dir vor, du hast eine Liste mit Verkäufern, Materialien und Umsätzen. Nun möchtest du wissen, wie viel Umsatz jeder Verkäufer mit jedem Material gemacht hat. Da gibt es die neue Funktion PIVOTMIT(), die diese Berechnungen mit nur einer Formel ermöglicht. Du gibst an, welche Werte du ermitteln möchtest z. B. die Materialien und die Verkäufer und welche Berechnung z. B. summieren oder zählen du haben möchtest.
In diesem Artikel stelle ich dir die drei typischen Einsatzmöglichkeiten der Funktion PIVOTMIT vor: Zunächst wird der Umsatz je Material und Verkäufer ermittelt, anschließend wird das Vorkommen dieser Kombination gezählt und danach noch ein Filter eingesetzt.
Ergebnisse dieser Art waren bisher nur mit Pivot‑Tabellen oder Funktionen wie z. B. SUMMENWENNS() möglich.
Bonus: Im Bonusteil erfährst du, wie du prozentuale Anteile ermittelst.
Tipp: Im zweiten Teil findest du die Antwort, wie du die Felder zur Berechnung dynamisch hältst.
So bildest du die Summe der Materialverkäufe je Verkäufer
Im ersten Beispiel sollen die Umsätze aller Materialien pro Verkäufer ermittelt werden. Zusätzlich bildet die Funktion für dich auch die Gesamtsumme.
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.
Starte mit [Umschalt] + [F3] den Funktionsassistenten und öffne die Funktion PIVOTMIT().
Für das erste Argument Zeilen_felder markiere die Zellen, die du in der ersten Spalte deiner Ergebnistabelle sehen möchtest (C1:C135). In diesem Beispiel sollen die Zellen mit den Materialien zusammengefasst werden. Markiere auch die Überschrift Material in der Zelle C1 mit.
Für das zweite Argument Spalten_felder markiere die Zellen, die du in der ersten Zeile deiner Ergebnistabelle sehen möchtest (F1:F135). In diesem Beispiel sollen die Zellen mit den Verkäufern zusammengefasst werden. Markiere auch die Überschrift Verkäufer in der Zelle F1 mit.
Klicke ins dritte Argument Werte und wähle die Zellen, deren Inhalt du berechnen möchtest (J1:J135). In diesem Beispiel die Zellen mit den Umsätzen. Markiere auch die Überschrift Umsatz in der Zelle J1 mit.
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: Wenn du die Felder variabel halten möchtest, klicke hier.
So zählst du, wie oft die Kombination Material und Verkäufer in der Liste vorkommt
Im zweiten Beispiel soll gezählt werden. Auch hier bildet die Funktion zum Abschluss wieder die Gesamtanzahl.
Klick in die Zelle, in der die Formel steht, die du gerade erfasst hast. In diesem Beispiel M4. Drücke die Tastenkombination [Strg] + [F3], um das zweite Dialogfeld des Funktionsassistenten zu öffnen.
Gib im vierten Argument den Funktionsnamen ANZAHL ein und klicke auf OK.
Entferne ggf. das Währungsformat und stelle das Standardformat ein.
So setzt du den Filter ein
Wenn du nur die Umsätze einer bestimmten Region sehen möchtest, nutze das Argument Filter_matrix im Dialogfeld Funktionsargumente.
Klick in die Zelle, in der die Formel steht, die du gerade erfasst hast. In diesem Beispiel M4. Drücke die Tastenkombination [Strg] + [F3].
Gib im vierten Argument wieder den Funktionsnamen SUMME ein.
Blättere nun im Dialogfeld Funktionsargumente weiter nach unten und klicke ins Feld Filter_matrix. Wähle hier die Spalte aus, nach der du filtern möchtest. Gib dann ein = (Gleichheitszeichen) ein und erfasse den Filterwert in „ (Anführungszeichen). In diesem Beispiel H1:H135="Region 3"
Klicke auf OK.
Weise den Umsatzzellen das Währungsformat zu.
Du erkennst, dass nicht jeder Verkäufer in der Region 3 alle Materialien verkauft hat.
Bonus: So ermittelst du anstelle der Summe den prozentualen Anteil
In Pivot-Tabellen ermittelst du den prozentualen Anteil mit einem Rechtsklick. Hier änderst du nur zwei Argumente deiner Formel:
Klick in die Zelle, in der die Formel steht, die du gerade erfasst hast. In diesem Beispiel M4. Drücke die Tastenkombination [Strg] + [F3].
Gib im vierten Argument den Funktionsnamen PROZENTVON ein
Blättere nach unten und entferne den Inhalt im Argumentfeld Filter_matrix.
Klicke ins letzte Feld Verhältnis_zu und gib dort eine 2 ein. Klicke auf OK.
Hinweis: Der Wert 2 gibt den Anteil zur Gesamtsumme aus. Der Wert 0 gibt den Anteil zur Spaltensumme aus und 1 gibt den Anteil zur Zeilensumme aus.
Weise den Zahlenzellen das Prozentformat zu.
Trainings, die für dich interessant sein könnten: