Excel

Dynamiske matrixformler i Excel

Dynamic Array Formulas Excel

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

UNIKT funktionseksempel

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:

UNIKT funktionseksempel efter ændring

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

Eksempel på dynamisk array spildområde reference

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

Eksempel på kun en formel i dynamisk array

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:

Eksempel på UNIKT og SORT sammen

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:

LEN -funktionen med arrays - gammel og ny

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:

Flere resultater med VLOOKUP og dynamiske arrays

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:

Dynamisk array multiplikationstabel

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'

Array operation eksempel test a

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

Array drift eksempel test b

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

Arraydriftseksempel test a og b

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:

Arraydrift med FILTER -funktion

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:

Grundlæggende matrixformel i traditionel Excel

Hvis du åbner den samme formel i traditionel Excel, ser du krøllede seler:

Grundlæggende matrixformel i dynamisk Excel

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:

Enkel matrixformel med krøllede seler synlige

Men hvis du indtaster formlen igen uden ændringer, fjernes de krøllede seler, og formlen returnerer det samme resultat:

Enkel matrixformel med krøllede seler ikke synlige

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.
Forfatter Dave Bruns


^