Hvordan bruke EXPAND-funksjonen i Microsoft Excel

Microsoft Excels dynamiske matriser er en revolusjon for automatiske oppdateringer, men deres uforutsigbare størrelse kan ødelegge formateringen av dashbordet ditt. Ved å bruke FUNKSJONEN EXPAND kan du låse oppsettet ditt på plass og sørge for at regnearkene ser ryddige ut. Slik gjør du det.

Hvordan EXPAND-funksjonen fungerer

Tenk på EXPAND som en beholder for dataene dine. Den endrer ikke verdiene dine—den tvinger dem bare til å fylle et bestemt antall rader eller kolonner. For eksempel, hvis du har et lite 2x2 datasett, men trenger at det passer inn i et 5x5-oppsett for et dashbord, fungerer EXPAND som rammen som fyller ut hullene.

EXPAND-syntaksen

Denne funksjonen bruker fire argumenter for å definere grensene til databeholderen din:

=EXPAND(array,[rows],[columns],[pad_with])

• array er kilde-dataene eller formelresultatet (som en FILTER- eller UNIQUE-liste) som du ønsker å utvide.
• rader er det totale antallet rader du ønsker i det endelige resultatet. Hvis det utelates, settes det som standard til høyden på kildearrayet.
• kolonner er det totale antallet kolonner du ønsker i det endelige resultatet. Hvis det utelates, settes det som standard til bredden på kildematrise din.
• pad_with er verdien du ønsker å se i «ekstra»-cellene. Hvis du lar dette stå tomt, fyller Excel de nye cellene med #I/T. For å holde det ryddig kan du bruke "" for en tom celle eller "-" for en strek, men dette argumentet aksepterer alle datatyper (tekst, tall eller til og med en annen formel).

Du må oppgi minst et rows- eller columns-argument for å endre størrelsen på dataene dine. Ellers returnerer funksjonen den opprinnelige matrisen uendret. I likhet med alle dynamiske matrisefunksjoner kan heller ikke EXPAND brukes inne i en Excel-tabell - den må være utenfor en tabell. Dette er fordi disse moderne funksjonene sprer resultatene sine til et område med celler, mens Excel-tabeller forventer en enkelt, selvstendig formel eller verdi i hver enkelt celle.

Brukstilfelle 1: Opprettholde dashbordets symmetri

Når du lager side-ved-side sammenligningstabeller i Excel, sikrer EXPAND at oppsettet ditt forblir perfekt balansert selv når datamengden varierer mellom kategoriene.

Scenario: Du har en Excel-tabell kalt T_Sales og bygger et «versus»-dashbord for å sammenligne salgsytelsen til to regioner. Når du velger en region i cellene F1 og J1 fra rullegardinmenyene for datavalidering, vil FILTER-funksjonen i cellene E2 og I2 hente de relevante dataene fra tabellen.

Uten EXPAND-funksjonen vil du ende opp med et skjevt dashbord hvis regionen i F1 har fem salg, men regionen i J1 bare har fire. Dette er fordi dynamiske matriser automatisk krymper og vokser i henhold til antall resultater de gir.

Celle E2:

=FILTER(T_Sales,T_Sales[Region]=F1)

Celle I2:

=FILTER(T_Sales,T_Sales[Region]=J1)

Å pakke filtrene dine inn i EXPAND låser størrelsen på begge tabellene til en fast høyde, og holder dashbordelementene dine perfekt justert.

Celle E2:

=EXPAND(FILTER(T_Sales,T_Sales[Region]=F1),10,,"-")

Celle I2:

=EXPAND(FILTER(T_Sales,T_Sales[Region]=J1),10,,"-")

• FILTER henter alle rader der regionen samsvarer med verdiene i cellene F1 og J1, henholdsvis.
• 10 forteller Excel at de overflødige resultatene må være 10 rader høye.
• "-" fyller alle rader mellom det faktiske antallet data og 10-radersgrensen med en strek.

Siden du hoppet over columns-argumentet, antar Excel at du vil beholde den opprinnelige bredden på FILTER-resultatene (tre kolonner). Legg også merke til hvordan EXPAND-funksjonen som standard legger til nye rader nederst i arrayet (i stedet for øverst).

Brukstilfelle 2: Forberede uoverensstemmende tabeller for VSTACK

EXPAND fungerer som en strukturell forbehandler som lar deg slå sammen datasett med ulikt antall kolonner uten å utløse feil.

Scenario: Du må kombinere to tabeller, T_Primary og T_Secondary, til en hovedliste ved å bruke VSTACK-funksjonen. T_Primary har imidlertid tre kolonner, mens T_Secondary kun har to.

Hvis du fullfører sammenslåingen ved kun å bruke VSTACK, vil formelen returnere en matrise med #I/A der tabellene ikke stemmer overens.

=VSTACK(T_Primary,T_Secondary)

Men ved å bruke EXPAND kan du legge til en tredje virtuell kolonne med tomme felter eller streker i den smalere T_Secondary-tabellen, noe som gjør resultatet mye ryddigere.

=VSTACK(T_Primary,EXPAND(T_Secondary,,3,"-"))

• EXPAND fokuserer bare på T_Secondary, og strekker den horisontalt til tre kolonner.
• "-" fyller den tomme kolonnen for notater med en strek slik at den ikke er tom eller viser en feil.
• VSTACK slår sammen den opprinnelige T_Primary-tabellen med den nylig utfylte T_Secondary-tabellen til en kontinuerlig liste.

Du spesifiserte ikke en verdi for rows-argumentet fordi du ikke ønsker å endre høyden på tabellen—du vil bare endre bredden. Merk også hvordan EXPAND-funksjonen legger til kolonner på høyre side av arrayet (i stedet for venstre side) som standard.

Brukstilfelle 3: Opprette stabile UI-beholdere

Noen ganger er utseendet på et Excel-regneark like viktig som hvordan det fungerer, og EXPAND-funksjonen hjelper til med begge deler. Ved å bruke en bindestrek som fylltegn kan du lage et resultatkort som forblir visuelt forankret selv når søkeresultatene dine er korte.

Scenario: Du har en kildetabell kalt T_Invoice i kolonnene A:C. Når du skriver inn en verdi i celle I2, vil FILTER-formelen i celle E2 fylle ut resultatet nedover og til høyre. Imidlertid ønsker du å bygge et resultatkort i kolonnene E:G som alltid matcher høyden på T_Invoice-tabellen, selv om den utvider seg eller krymper.

Denne oppsettet vil holde regnearkets layout balansert og profesjonell. Men enda viktigere, ved å bruke argumentet pad_with for å sette inn bindestreker vil det tjene et viktig funksjonelt formål: det vil advare alle som får tilgang til regnearket ditt om at området er aktivt, så de ikke bør skrive noe i disse cellene. Dette forhindrer at den beryktede #SPILL!-feilen oppstår når filterresultatene prøver å fylle området.

Her er formelen du skal skrive inn i celle E2:

=EXPAND(FILTER(T_Invoice,T_Invoice[Amount]>I2,""),ROWS(T_Invoice),,"-")

• FILTER henter dataene som samsvarer med kriteriene dine i celle I2.
• ROWS(T_Invoice) fungerer som rader-argumentet i EXPAND-syntaksen ved å dynamisk beregne høyden på kildetabellen din slik at resultatkortet alltid samsvarer.
• "-" på slutten fyller hver tom rad med en strek, og gir et synlig "reservert" rom uten behov for spesiell formatering.
• "" i midten hindrer at FILTER selv kaster en #CALC!-feil før EXPAND i det hele tatt får sjansen til å kjøre.

Du trenger ikke å angi columns-argumentet fordi formelen automatisk tilpasser seg kildetabellens tre-kolonnebredde.

Når kildetabellen din vokser, justeres antallet streker automatisk for å holde resultatkortet perfekt justert og beskyttet.

Feilsøking av EXPAND-funksjonen

For å avslutte, her er en hurtigreferansetabell for de vanligste feilene du vil støte på når du bruker Excels EXPAND-funksjon:

Å bruke EXPAND-funksjonen for å låse oppsettet ditt forhindrer akkordeoneffekten som ofte plager dynamiske rapporter. Ved å kombinere denne strukturelle stabiliteten med tydelige fylltegn, sikrer du at resultatene dine forblir organiserte og beskyttet mot overløpsfeil. Til syvende og sist er det den perfekte måten å gjøre Microsoft Excel-dashbordet ditt polert, profesjonelt og forutsigbart.