Bytt ut en pivottabell med 3 dynamiske matriseformler - Excel-tips

Innholdsfortegnelse

Se på video

Det har gått åtte dager siden dynamiske matriseformler ble kunngjort på Ignite 2018-konferansen i Orlando. Dette er hva jeg har lært:

Moderne arrays ble kunngjort på Ignite 24. september 2018 og ble offisielt kalt Dynamic Arrays.
Jeg har skrevet en 60-siders e-bok med 30 eksempler på hvordan du bruker dem, og jeg tilbyr den gratis til slutten av 2018.
Utrullingen kommer til å gå mye tregere enn noen vil, noe som er frustrerende. Hvorfor så sakte? Excel-teamet har gjort endringer i Calc Engine-koden som har vært stabil i 30 år. Av spesiell bekymring: med tillegg som injiserer formler i Excel som uforvarende brukte implisitt skjæringspunkt. Disse tilleggene går i stykker hvis Excel nå returnerer et spillområde.
Det er en ny måte å referere til området som returneres av en matrise: =E3#men det har ikke noe navn ennå. Den # kalles sølt Formula Operator . Hva synes du om et navn som Spill Ref (foreslått av Excel MVP Jon Acampora) eller The Spiller (foreslått av MVP Ingeborg Hawighorst)?

Som medforfatter av Pivot Table Data Crunching elsker jeg et godt pivottabell. Men hva om du trenger pivottabellene dine for å oppdatere og du ikke kan stole på lederen din for lederen for å klikke på Oppdater? Teknikken beskrevet i dag tilbyr en serie med tre formler for å erstatte en pivottabell.

For å få en sortert liste over unike kunder, bruk =SORT(UNIQUE(E2:E564))i I2.

Én dynamisk matriseformel for å opprette kunder på siden av rapporten

Bruk =TRANSPOSE(SORT(UNIQUE(B2:B564)))i J1 for å sette produktet over toppen .

Bruk TRANSPOSE for kolonneområdet

Her er et problem: du vet ikke hvor høy kundelisten vil være. Du vet ikke hvor bred produktlisten vil være. Hvis du refererer til I2 #, vil Spiller automatisk referere til den nåværende størrelsen på den returnerte matrisen.

Formelen for å returnere verdier område av dreiebordet er en enkelt rekke formel i J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

På engelsk sier dette at du vil legge til inntektene fra G2: G564 der Kunder i E samsvarer med den gjeldende radens kunde fra I2-arrayformelen og produktene i B samsvarer med den gjeldende kolonnen i array-formelen i J1.

Dette er en søt formel

Hva om de underliggende dataene endres? Jeg la til en ny kunde og et nytt produkt ved å endre disse to cellene i kilden.

Endre noen celler i de opprinnelige dataene

Rapporten oppdateres med nye rader og nye kolonner. Array-Range Reference of I2 # og J1 # håndterer den ekstra raden og kolonnen.

Tverrfanerapporten din utvides automatisk med de nye dataene

Hvorfor fungerer SUMIFS? Dette er et konsept i Excel som heter Broadcasting. Hvis du har en formel som refererer til to matriser:

Array one er (27 rader) x (1 kolonne)
Array to er (1 rad) x (3 kolonner)
Excel vil returnere en resulterende matrise som er like høy og bred som den høyeste og bredeste delen av de refererte matriser:
Resultatet blir (27 rader) x (3 kolonner).
Dette kalles kringkastingsarrayer.