Kumulative Zählungen zu einer Pivot-Tabelle in Excel hinzufügen

Question

I have data in excel that looks like this:

Shop   | Division    | Launch Year
-------+-------------+-------------
Shop 1 | Division 1  | 2005
Shop 2 | Division 1  | 2006
Shop A | Division 2  | 2005
Shop B | Division 2  | 2007
...

Und so weiter. Es gibt insgesamt etwa 100 Geschäfte in vier Abteilungen. Ich brauche eine Tabelle, die zeigt, wie viele Geschäfte am Ende jedes Jahres pro Abteilung geöffnet sind. Mit einer Pivot-Tabelle kann ich leicht zeigen, wie viele neue Geschäfte in diesem Jahr eröffnet wurden, aber nicht, wie viele es insgesamt sind.

Ich weiß, wie man einer Pivot-Tabelle laufende Summen hinzufügt, aber in meinem Fall funktioniert das nicht, da ich keine Zahlen zum Hinzufügen habe – ich muss stattdessen laufende Zählungen hinzufügen. Wie kann ich das machen?

Ich suche nach diesem Endergebnis:

Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1          | 1          |
2006 | 2          | 1          |
2007 | 2          | 2          |
...

Answer 1

Eine laufende Zählung ist eine laufende Summe von 1 Sekunden.
Wenn die Daten es zulassen, fügen Sie einfach eine zusätzliche Spalte voller Einsen hinzu und verwenden Sie diese in Ihrer Pivot-Tabelle für eine laufende Summe.

Answer 2

Wenn Ihre Tabelle relativ statisch ist (Spalten und Zeilen ändern sich kaum), verwenden Sie diese Spalten- und Zeilenüberschriften. Verwenden Sie im Textkörper Formeln wie (in Pseudocode):

=countifs('Launch Year',"<="&'row header', 'Division', 'column header')

Wenn Sie die richtige Verankerung verwenden, können Sie die Formel einmal erstellen und sie dann im gesamten Text kopieren und einfügen.

Answer 3

Sie benötigen hierfür keine Pivot-Tabelle.

Erstellen Sie Ihre Tabelle wie in der gewünschten Ausgabe gezeigt. Füllen Sie den Datenbereich mit der folgenden Array-Formel, die Sie mit eingegeben Ctrl Shift Enterund dann quer und nach unten kopiert haben:

=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))

Aktualisieren Sie die Zellbezüge nach Bedarf und achten Sie dabei auf die Verankerung.

Sumproduct-FunktionDokumentationvon MS.

Sie verwenden IFAnweisungen, um zwei gleich große Arrays mit übereinstimmenden Werten (die --Wendungen TRUEzu 1und FALSEzu 0) zu generieren. Sie multiplizieren diese miteinander, um Datensätze zu finden, die beiden Kriterien entsprechen, und summieren dann die resultierende Liste der 1's und 0's, um den Gesamtwert zu erhalten.

EDIT: Um auf Ihren Kommentar/Ihre Frage zu antworten

Das hier bisher gezeigte Setup ist eine kleine Daten- und Ergebnistabelle als Proof of Concept. Um dies erweiterbarer zu machen, werden Sie die Eingabetabelle wahrscheinlich in eine benannte Tabelle umwandeln. Wählen Sie dazu eine beliebige Zelle im entsprechenden Bereich aus (Beispiel: B4im gezeigten Setup) und drücken Sie Ctrl+ T. Bestätigen Sie anschließend im resultierenden modalen Fenster. Dadurch wird das Schreiben von Zellreferenzen vereinfacht und alle Zellreferenzen auf diese Datenbereiche werden automatisch aktualisiert, wenn Daten hinzugefügt werden.

Mit derselben Methode können Sie auch die Ergebnistabelle in eine Tabelle umwandeln. Wenn Sie dann ein neues Jahr hinzufügen, müssen Sie nur das Jahr in die nächste Zeile unter dieser Tabelle eingeben und alle Formeln werden automatisch eingefügt:

Beachten Sie, dass diese automatische Aktualisierung der Ausgabetabelle nicht funktioniert, wenn Sie eine neue Division hinzufügen. Die Tabelle wird entsprechend erweitert, Sie müssen die SUMPRODUCTFormel jedoch manuell in das neue Feld kopieren.

Viel Glück und viel Spaß!