Vervang een draaitabel door 3 dynamische matrixformules - Excel-tips

Inhoudsopgave

Bekijk video

Het is acht dagen geleden dat dynamische matrixformules werden aangekondigd op de Ignite 2018-conferentie in Orlando. Dit is wat ik heb geleerd:

Moderne arrays werden aangekondigd op Ignite op 24 september 2018 en officieel Dynamic Arrays genoemd.
Ik heb een e-book van 60 pagina's geschreven met 30 voorbeelden van hoe je ze kunt gebruiken, en ik bied het gratis aan tot eind 2018.
De uitrol zal een stuk langzamer gaan dan iedereen wil, wat frustrerend is. Waarom zo langzaam? Het Excel-team heeft wijzigingen aangebracht in de Calc Engine-code die al 30 jaar stabiel is. Van bijzonder belang: met invoegtoepassingen die formules in Excel injecteren die onbedoeld een impliciete kruising gebruikten. Die invoegtoepassingen gaan kapot als Excel nu een overloopbereik retourneert.
Er is een nieuwe manier om te verwijzen naar het bereik dat wordt geretourneerd door een array: =E3#maar het heeft nog geen naam. De # wordt de gemorste formule-operator genoemd . Wat vind je van een naam als Spill Ref (voorgesteld door Excel MVP Jon Acampora) of The Spiller (voorgesteld door MVP Ingeborg Hawighorst)?

Als co-auteur van Pivot Table Data Crunching, ben ik dol op een goede draaitabel. Maar wat als u uw draaitabellen moet bijwerken en u niet kunt vertrouwen dat de manager van uw manager op Vernieuwen klikt? De vandaag beschreven techniek biedt een reeks van drie formules om een draaitabel te vervangen.

Gebruik =SORT(UNIQUE(E2:E564))in I2 om een gesorteerde lijst met unieke klanten te krijgen .

Eén dynamische matrixformule om klanten aan de zijkant van het rapport te creëren

Gebruik =TRANSPOSE(SORT(UNIQUE(B2:B564)))in J1 om het product bovenaan te plaatsen .

Gebruik TRANSPONEREN voor het kolommengebied

Hier is een probleem: u weet niet hoe lang de klantenlijst zal zijn. U weet niet hoe breed de productlijst zal zijn. Als u verwijst naar I2 #, verwijst de Spiller automatisch naar de huidige grootte van de geretourneerde array.

De formule voor de waarden van het gebied spillijst terugkeer een enkele array formule J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

In het Engels zegt dit dat u de inkomsten van G2: G564 wilt optellen waarbij de klanten in E overeenkomen met de klant van de huidige rij uit de I2-matrixformule en de producten in B overeenkomen met de huidige kolom van de matrixformule in J1.

Dit is een zoete formule

Wat als de onderliggende gegevens veranderen? Ik heb een nieuwe klant en een nieuw product toegevoegd door deze twee cellen in de bron te wijzigen.

Verander enkele cellen in de originele gegevens

Het rapport wordt bijgewerkt met nieuwe rijen en nieuwe kolommen. De Array-Range Reference van I2 # en J1 # behandelt de extra rij en kolom.

Uw kruistabelrapport wordt automatisch uitgebreid met de nieuwe gegevens

Waarom werken de SUMIFS? Dit is een concept in Excel genaamd Broadcasting. Als u een formule heeft die verwijst naar twee arrays:

Array één is (27 rijen) x (1 kolom)
Matrix twee is (1 rij) x (3 kolommen)
Excel retourneert een resulterende array die zo hoog en breed is als het hoogste en breedste deel van de arrays waarnaar wordt verwezen:
Het resultaat is (27 rijen) x (3 kolommen).
Dit wordt Broadcasting-arrays genoemd.