Ersetzen Sie eine Pivot-Tabelle durch 3 dynamische Array-Formeln - Excel-Tipps

Inhaltsverzeichnis

Schau Video

Es ist acht Tage her, seit auf der Ignite 2018-Konferenz in Orlando dynamische Array-Formeln angekündigt wurden. Folgendes habe ich gelernt:

Moderne Arrays wurden am 24. September 2018 bei Ignite angekündigt und offiziell als Dynamic Arrays bezeichnet.
Ich habe ein 60-seitiges E-Book mit 30 Beispielen für deren Verwendung geschrieben und biete es bis Ende 2018 kostenlos an.
Der Roll-out wird viel langsamer sein als irgendjemand will, was frustrierend ist. Warum so langsam? Das Excel-Team hat Änderungen am Calc Engine-Code vorgenommen, der seit 30 Jahren stabil ist. Von besonderer Bedeutung: mit Add-Ins, die Formeln in Excel einfügen, die versehentlich implizite Schnittmengen verwendet haben. Diese Add-Ins werden unterbrochen, wenn Excel jetzt einen Spill-Bereich zurückgibt.
Es gibt eine neue Möglichkeit, auf den von einem Array zurückgegebenen Bereich zu verweisen: =E3#Es hat jedoch noch keinen Namen. Das # wird als Operator für verschüttete Formeln bezeichnet . Was halten Sie von einem Namen wie Spill Ref (vorgeschlagen von Excel MVP Jon Acampora) oder The Spiller (vorgeschlagen von MVP Ingeborg Hawighorst)?

Als Co-Autor von Pivot Table Data Crunching liebe ich eine gute Pivot-Tabelle. Was aber, wenn Sie Ihre Pivot-Tabellen aktualisieren müssen und dem Manager Ihres Managers nicht vertrauen können, dass er auf Aktualisieren klickt? Die heute beschriebene Technik bietet eine Reihe von drei Formeln zum Ersetzen einer Schwenktabelle.

Verwenden Sie =SORT(UNIQUE(E2:E564))in I2, um eine sortierte Liste eindeutiger Kunden zu erhalten .

Eine dynamische Array-Formel zum Erstellen von Kunden am unteren Rand des Berichts

Verwenden Sie =TRANSPOSE(SORT(UNIQUE(B2:B564)))in J1, um das Produkt nach oben zu bringen .

Verwenden Sie für den Spaltenbereich TRANSPOSE

Hier ist ein Problem: Sie wissen nicht, wie hoch die Kundenliste sein wird. Sie wissen nicht, wie breit die Produktliste sein wird. Wenn Sie sich auf I2 # beziehen, bezieht sich der Spiller automatisch auf die aktuelle Größe des zurückgegebenen Arrays.

Die Formel zum Zurückgeben des Wertebereichs der Pivot-Tabelle ist eine einzelne Array-Formel in J2 : =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Auf Englisch bedeutet dies, dass Sie die Einnahmen aus G2: G564 hinzufügen möchten, wobei die Kunden in E mit dem Kunden der aktuellen Zeile aus der I2-Array-Formel übereinstimmen und die Produkte in B mit der aktuellen Spalte der Array-Formel in J1 übereinstimmen.

Dies ist eine süße Formel

Was ist, wenn sich die zugrunde liegenden Daten ändern? Ich habe einen neuen Kunden und ein neues Produkt hinzugefügt, indem ich diese beiden Zellen in der Quelle geändert habe.

Ändern Sie einige Zellen in den Originaldaten

Der Bericht wird mit neuen Zeilen und Spalten aktualisiert. Die Array-Bereichsreferenz von I2 # und J1 # behandelt die zusätzliche Zeile und Spalte.

Ihr Kreuztabellenbericht wird automatisch mit den neuen Daten erweitert

Warum funktioniert der SUMIFS? Dies ist ein Konzept in Excel namens Broadcasting. Wenn Sie eine Formel haben, die sich auf zwei Arrays bezieht:

Array eins ist (27 Zeilen) x (1 Spalte)
Array zwei ist (1 Zeile) x (3 Spalten)
Excel gibt ein resultierendes Array zurück, das so groß und breit ist wie der höchste und breiteste Teil der referenzierten Arrays:
Das Ergebnis ist (27 Zeilen) x (3 Spalten).
Dies wird als Broadcasting-Arrays bezeichnet.