Udskift en drejetabel med 3 dynamiske matrixformler - Excel-tip

Indholdsfortegnelse

Se video

Det har været otte dage siden, at dynamiske matrixformler blev annonceret på Ignite 2018-konferencen i Orlando. Her er hvad jeg har lært:

Moderne arrays blev annonceret på Ignite den 24. september 2018 og kaldes officielt Dynamic Arrays.
Jeg har skrevet en 60-siders e-bog med 30 eksempler på, hvordan man bruger dem, og jeg tilbyder den gratis indtil udgangen af 2018.
Udrulningen vil være meget langsommere, end nogen vil, hvilket er frustrerende. Hvorfor så langsom? Excel-teamet har foretaget ændringer i Calc Engine-kode, der har været stabil i 30 år. Af særlig bekymring: med tilføjelsesprogrammer, der injicerer formler i Excel, der uforvarende brugte implicit kryds. Disse tilføjelsesprogrammer går i stykker, hvis Excel nu returnerer et spildområde.
Der er en ny måde at henvise til det interval, der returneres af et array: =E3#men det har ikke noget navn endnu. Den # kaldes Spildt Formel Operator . Hvad synes du om et navn som Spill Ref (foreslået af Excel MVP Jon Acampora) eller The Spiller (foreslået af MVP Ingeborg Hawighorst)?

Som medforfatter af Pivot Table Data Crunching elsker jeg en god pivottabel. Men hvad nu hvis du har brug for dine pivottabeller for at opdatere, og du ikke kan stole på din managers manager til at klikke på Opdater? Den teknik, der er beskrevet i dag, tilbyder en række på tre formler til erstatning af en drejetabel.

Brug =SORT(UNIQUE(E2:E564))i I2 for at få en sorteret liste over unikke kunder .

Én dynamisk matrixformel til oprettelse af kunder nede i rapporten

Brug =TRANSPOSE(SORT(UNIQUE(B2:B564)))i J1 for at placere produktet på toppen .

Brug TRANSPOSE til kolonneområdet

Her er et problem: du ved ikke, hvor høj kundelisten vil være. Du ved ikke, hvor bred produktlisten vil være. Hvis du henviser til I2 #, henviser Spiller automatisk til den aktuelle størrelse på det returnerede array.

Formlen til at returnere værdier område af pivottabellens er et enkelt array formel i J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

På engelsk siger dette, at du vil tilføje indtægterne fra G2: G564, hvor kunderne i E matcher den aktuelle rækkes kunde fra I2-arrayformlen, og produkterne i B matcher den aktuelle kolonne i array-formlen i J1.

Dette er en sød formel

Hvad hvis de underliggende data ændres? Jeg tilføjede en ny kunde og et nyt produkt ved at ændre disse to celler i kilden.

Skift nogle celler i de originale data

Rapporten opdateres med nye rækker og nye kolonner. Array-Range Reference for I2 # og J1 # håndterer den ekstra række og kolonne.

Din rapport på tværs af faner udvides automatisk med de nye data

Hvorfor fungerer SUMIFS? Dette er et koncept i Excel kaldet Broadcasting. Hvis du har en formel, der refererer til to arrays:

Array one er (27 rækker) x (1 kolonne)
Array to er (1 række) x (3 kolonner)
Excel returnerer et resulterende array, der er så højt og bredt som den højeste og bredeste del af de refererede arrays:
Resultatet bliver (27 rækker) x (3 kolonner).
Dette kaldes Broadcasting arrays.