Byt ut en pivottabell med 3 dynamiska matrisformler - Excel-tips

Innehållsförteckning

Kolla på video

Det har gått åtta dagar sedan dynamiska matrisformler tillkännagavs vid konferensen Ignite 2018 i Orlando. Här är vad jag har lärt mig:

Modern Arrays tillkännagavs vid Ignite den 24 september 2018 och kallades officiellt Dynamic Arrays.
Jag har skrivit en 60-sidig e-bok med 30 exempel på hur man använder dem, och jag erbjuder den gratis till slutet av 2018.
Utbyggnaden kommer att gå mycket långsammare än någon vill, vilket är frustrerande. Varför så långsam? Excel-teamet har gjort ändringar i Calc Engine-koden som har varit stabil i 30 år. Av särskilt intresse: med tillägg som injicerar formler i Excel som oavsiktligt använde implicit skärningspunkt. Dessa tillägg bryts om Excel nu returnerar ett spillintervall.
Det finns ett nytt sätt att hänvisa till intervallet som returneras av en matris: =E3#men det har inget namn ännu. Den # kallas spillda Formel Operator . Vad tycker du om ett namn som Spill Ref (föreslagen av Excel MVP Jon Acampora) eller The Spiller (föreslagen av MVP Ingeborg Hawighorst)?

Som medförfattare till Pivot Table Data Crunching älskar jag ett bra pivottabell. Men vad händer om du behöver uppdatera dina pivottabeller och du inte kan lita på din chefs chef att klicka på Uppdatera? Den teknik som beskrivs idag erbjuder en serie med tre formler för att ersätta en pivottabell.

För att få en sorterad lista över unika kunder, använd =SORT(UNIQUE(E2:E564))i I2.

En dynamisk matrisformel för att skapa kunder på sidan av rapporten

För att placera produkten överst, använd =TRANSPOSE(SORT(UNIQUE(B2:B564)))i J1.

Använd TRANSPOSE för kolumnområdet

Här är ett problem: du vet inte hur lång kundlistan kommer att vara. Du vet inte hur bred produktlistan kommer att vara. Om du hänvisar till I2 # hänvisar Spiller automatiskt till den aktuella storleken på den returnerade matrisen.

Formeln för att returnera värden området av svängbordet är en enda matrisformel i J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

På engelska säger detta att du vill lägga till intäkterna från G2: G564 där kunderna i E matchar den aktuella radens kund från I2-arrayformeln och produkterna i B matchar den aktuella kolumnen i array-formeln i J1.

Detta är en söt formel

Vad händer om underliggande data ändras? Jag lade till en ny kund och en ny produkt genom att ändra dessa två celler i källan.

Ändra vissa celler i originaldata

Rapporten uppdateras med nya rader och nya kolumner. Array-Range Reference för I2 # och J1 # hanterar den extra raden och kolumnen.

Din rapport över korsflikar expanderas automatiskt med de nya uppgifterna

Varför fungerar SUMIFS? Detta är ett koncept i Excel som heter Broadcasting. Om du har en formel som refererar till två matriser:

Array one är (27 rader) x (1 kolumn)
Array två är (1 rad) x (3 kolumner)
Excel returnerar en resulterande matris som är lika lång och bred som den högsta och bredaste delen av de refererade matriserna:
Resultatet blir (27 rader) x (3 kolumner).
Detta kallas Broadcasting arrays.