Dynamiske arrays er den største ændring af Excel -formler i år. Måske den største ændring nogensinde. Dette skyldes, at dynamiske arrays lader dig nemt arbejde med flere værdier på samme tid i en formel. For mange brugere vil det være første gang, de forstår og bruger matrixformler.
Dette er en stor opgradering og velkommen ændring. Dynamic Arrays løser nogle virkelig hårde problemer i Excel og ændrer grundlæggende den måde, regneark er designet og konstrueret på.
Tilgængelighed
Dynamiske arrays og de nye funktioner nedenfor er kun tilgængelige Excel 365 . Excel 2016 og Excel 2019 tilbyder ikke understøttelse af dynamisk matrixformel. For nemheds skyld vil jeg bruge 'Dynamic Excel' (Excel 365) og 'Traditional Excel' (2019 eller tidligere) til at differentiere versioner herunder.
Ny: Videotræning i Dynamic Array Formula
Nye funktioner
Som en del af den dynamiske array -opdatering indeholder Excel nu 8 nye funktioner, der direkte udnytter dynamiske arrays til at løse problemer, der traditionelt er svære at løse med konventionelle formler. Klik på nedenstående links for detaljer og eksempler for hver funktion:
Fungere | Formål |
---|---|
FILTER | Filtrer data og returner matchende poster |
RANDARRAY | Generer række af tilfældige tal |
SEKVENS | Generer række af fortløbende tal |
SORTERE | Sorter område efter kolonne |
SORTER EFTER | Sorter område efter et andet område eller array |
ENESTÅENDE | Udtræk unikke værdier fra en liste eller et område |
XLOOKUP | Moderne erstatning for VLOOKUP |
XMATCH | Moderne erstatning for MATCH -funktionen |
Video: Nye dynamiske array -funktioner i Excel (ca. 3 minutter).
hvis og derefter formel i excel
Bemærk: XLOOKUP og XMATCH ikke var i den oprindelige gruppe af nye dynamiske array -funktioner, men de kører godt på den nye dynamiske array -motor. XLOOKUP erstatter VLOOKUP og tilbyder en moderne, fleksibel tilgang, der drager fordel af arrays. XMATCH er en opgradering til MATCH -funktionen, der giver nye muligheder til INDEX og MATCH formler.
Eksempel
Inden vi går ind i detaljerne, lad os se på et enkelt eksempel. Nedenfor bruger vi det nye UNIK funktion at udtrække unikke værdier fra området B5: B15, med en enkelt formel indtastet i E5:
= UNIQUE (B5:B15) // return unique values in B5:B15
Resultatet er en liste over de fem unikke bynavne, der vises i E5: E9.
Som alle formler opdateres UNIKT automatisk, når data ændres. Nedenfor har Vancouver erstattet Portland på række 11. Resultatet fra UNIK inkluderer nu Vancouver:
Spild - en formel, mange værdier
I Dynamisk Excel vil formler, der returnerer flere værdier, ' spil 'disse værdier direkte på regnearket. Dette vil straks være mere logisk for formelbrugere. Det er også en fuldt dynamisk adfærd - når kildedata ændres, opdateres spildte resultater straks.
Det rektangel, der omslutter værdierne, kaldes ' spil rækkevidde '. Du vil bemærke, at spildområdet har særlig fremhævning. I det UNIKE eksempel ovenfor er spildområdet E5: E10.
Når data ændres, vil spildområdet udvides eller trække sig sammen efter behov. Du kan muligvis se nye værdier tilføjet, eller eksisterende værdier forsvinder. På denne måde er et spildområde en ny slags dynamisk område.
Bemærk: Når spild er blokeret af andre data, ser du en #SPILL -fejl. Når du får plads til spildområdet, spildes formlen automatisk.
Video: Spild og spildområde
Spildområde reference
For at henvise til et spildområde skal du bruge et hash -symbol (#) efter den første celle i området. For eksempel at referere resultaterne fra funktionen UNIK ovenfor:
=E5# // reference UNIQUE results
Dette er det samme som at referere til hele spildområdet, og du vil se denne syntaks, når du skriver en formel, der refererer til et komplet spildområde.
Du kan direkte henvise en spildområdereference til andre formler. For eksempel at tælle antallet af byer, der returneres af UNIK, kan du bruge:
= COUNTA (E5#) // count unique cities
Når spildområdet ændres, afspejler formlen de nyeste data.
Massiv forenkling
Tilføjelsen af nye dynamiske matrixformler betyder, at visse formler kan forenkles drastisk. Her er et par eksempler:
- Uddrag og anfør unikke værdier ( Før | efter )
- Tæl unikke værdier ( Før | efter )
- Filtrer og udpak poster ( Før | efter )
- Uddrag delvise kampe ( Før | efter )
Enes magt
En af de mest kraftfulde fordele ved metoden 'en formel, mange værdier' er mindre afhængig af absolut eller blandet referencer. Da en dynamisk matrixformel spilder resultater på regnearket, forbliver referencer uændrede, men formlen genererer korrekte resultater.
For eksempel bruger vi FILTER -funktionen nedenfor til at udtrække poster i gruppe 'A'. I celle F5 indtastes en enkelt formel:
= FILTER (B5:D11,B5:B11='a') // references are relative
Bemærk, at begge områder er ulåste relative referencer, men formlen fungerer perfekt.
Dette er en enorm fordel for mange brugere, fordi det gør processen med at skrive formler så meget enklere. For et andet godt eksempel, se multiplikationstabellen herunder.
Kædefunktioner
Ting bliver virkelig interessante, når du kæder mere end en dynamisk array -funktion sammen. Måske vil du sortere resultaterne returneret af UNIK? Let. Bare pakk den ind SORT funktion omkring UNIK funktionen sådan:
Som før, når kildedata ændres, vises der automatisk nye unikke resultater, pænt sorteret.
Indfødt adfærd
Det er vigtigt at forstå, at dynamisk array -adfærd er en indfødt og dybt integreret . Hvornår nogen formel returnerer flere resultater, spildes disse resultater i flere celler på regnearket. Dette inkluderer ældre funktioner, der ikke oprindeligt var designet til at arbejde med dynamiske arrays.
For eksempel i traditionel Excel, hvis vi giver LEN funktion til rækkevidde tekstværdier, ser vi a enkelt resultat. I Dynamisk Excel, hvis vi giver LEN -funktionen en række værdier, ser vi mange resultater. Denne skærm herunder viser den gamle adfærd til venstre og den nye adfærd til højre:
Dette er en enorm ændring, der kan påvirke alle former for formler. For eksempel VLOOKUP -funktion er designet til at hente en enkelt værdi fra en tabel ved hjælp af et kolonneindeks. Men i Dynamisk Excel, hvis vi giver VLOOKUP mere end et kolonneindeks ved hjælp af en array konstant sådan her:
= VLOOKUP ('jose',F7:H10,{1,2,3},0)
VLOOKUP returnerer flere kolonner:
Med andre ord, selvom VLOOKUP aldrig var designet til at returnere flere værdier, kan den nu gøre det takket være ny formelmotor i Dynamic Excel.
Alle formler
Bemærk endelig, at dynamiske arrays arbejder med alle formler ikke kun funktioner . I eksemplet herunder indeholder celle C5 en enkelt formel:
indsæt dato og tid i excel
=B5:B14*C4:L4
Resultatet spildes til et 10 x 10 område, der omfatter 100 celler:
Bemærk: I traditionel Excel kan du se flere resultater returneret med matrixformel, hvis du brug F9 til at inspicere formlen . Men medmindre du indtaster formlen som en multi-cell array formel , vises kun en værdi på regnearket.
Arrays går mainstream
Med udrulning af dynamiske arrays bliver ordet ' array 'kommer til at dukke op meget oftere. Faktisk kan du se 'array' og 'rækkevidde' bruges næsten ombytteligt. Du vil se arrays i Excel omsluttet af krøllede seler som dette:
{1,2,3} // horizontal array {123} // vertical array
Array er et programmeringsudtryk, der refererer til en liste over emner, der vises i en bestemt rækkefølge. Grunden til, at arrays kommer så ofte op i Excel -formler, er, at arrays kan udtrykker perfekt værdierne i en række celler .
Video: Hvad er en array?
Array -operationer bliver vigtige
Fordi dynamiske Excel -formler let kan fungere med flere værdier, bliver arrayoperationer vigtigere. Udtrykket 'matrixoperation' refererer til et udtryk, der kører en logisk test eller matematikoperation på et array. Eksempelvis testes udtrykket herunder, om værdier i B5: B9 er lig med 'ca'
=B5:B9='ca' // state = 'ca'
fordi der er 5 celler i B5: B9, er resultatet 5 SANDE/FALSKE værdier i en matrix:
{FALSETRUEFALSETRUETRUE}
Arrayoperationen nedenfor kontrollerer beløb større end 100:
=C5:C9>100 // amounts > 100
Den endelige array -operation kombinerer test A og test B i et enkelt udtryk:
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100
Bemærk: Excel tvinger automatisk de SANDE og FALSKE værdier til 1 og 0 under matematikoperationen.
For at bringe dette tilbage til dynamiske matrixformler i Excel demonstrerer eksemplet herunder, hvordan vi kan bruge nøjagtig den samme matrixoperation inde i FILTER -funktionen som omfatte argument:
FILTER returnerer de to poster, hvor tilstand = 'ca' og beløb> 100.
For en demonstration, se: Sådan filtreres med to kriterier (video).
Nye og gamle matrixformler
I Dynamic Excel er det ikke nødvendigt at indtaste matrixformler med kontrol + shift + enter. Når en formel er oprettet, kontrollerer Excel, om formlen muligvis returnerer flere værdier. I så fald vil det automatisk blive gemt som en dynamisk matrixformel, men du vil ikke se krøllede seler. Nedenstående eksempel viser en typisk matrixformel, der er angivet i Dynamic Excel:
Hvis du åbner den samme formel i traditionel Excel, ser du krøllede seler:
Går den anden retning, når en 'traditionel' matrixformel åbnes i Dynamic Excel, vil du se de krøllede seler i formellinjen. For eksempel viser skærmen nedenfor en simpel matrixformel i traditionel Excel:
Men hvis du indtaster formlen igen uden ændringer, fjernes de krøllede seler, og formlen returnerer det samme resultat:
Den nederste linje er, at matrixformler, der er indtastet med kontrol + skift + enter (CSE) stadig fungerer for at opretholde kompatibilitet, men du skal ikke indtaste matrixformler med CSE i Dynamic Excel.
Karakteren
Med introduktionen af dynamiske arrays vil du se @ -tegnet blive vist oftere i formler. @ -Tegnet muliggør en adfærd kendt som ' implicit kryds '. Implicit skæringspunkt er en logisk proces, hvor mange værdier reduceres til en værdi.
I traditionel Excel er implicit krydsning en lydløs adfærd, der bruges (når det er nødvendigt) til at reducere flere værdier til et enkelt resultat i en celle. I Dynamisk Excel er det typisk ikke nødvendigt, da flere resultater kan spildes på regnearket. Når det er nødvendigt, påberåbes implicit kryds manuelt med @ -tegnet.
Når du åbner regneark oprettet en ældre version af Excel, kan du muligvis se @ -tegnet automatisk til eksisterende formler, der har potentiel at returnere mange værdier. I traditionel Excel spildes en formel, der returnerer flere værdier, ikke på regnearket. @ -Tegnet tvinger den samme adfærd i Dynamic Excel, så formlen fungerer på samme måde og returnerer det samme resultat, som den gjorde i den originale Excel -version.
Med andre ord tilføjes @ for at forhindre en ældre formel i at spilde flere resultater på regnearket. Afhængigt af formlen kan du muligvis fjerne @ -tegnet, og formelens adfærd ændres ikke.
Resumé
- Dynamiske arrays gør visse formler meget lettere at skrive.
- Du kan nu filtrere matchende data, sortere og udtrække unikke værdier let med formler.
- Dynamic Array -formler kan lænkes (indlejret) for at gøre ting som at filtrere og sortere.
- Formler, der returnerer mere end én værdi, spildes automatisk.
- Det er ikke nødvendigt at bruge Ctrl+Shift+Enter for at indtaste en matrixformel.
- Dynamiske matrixformler er kun tilgængelige i Excel 365.