Dynamiske arrays i Excel

Hvad er dynamiske arrays

I september 2018 udgav Microsoft en opdatering, der tilføjer et helt nyt værktøj til Microsoft Excel: Dynamic Arrays og 7 nye funktioner til at arbejde med dem. Disse ting ændrer uden overdrivelse radikalt al den sædvanlige teknik til at arbejde med formler og funktioner og vedrører bogstaveligt talt enhver bruger.

Overvej et simpelt eksempel for at forklare essensen.

Antag, at vi har en simpel tabel med data om by-måneder. Hvad vil der ske, hvis vi vælger en tom celle til højre på arket og indtaster en formel i den, der ikke linker til én celle, men umiddelbart til et område?

I alle tidligere versioner af Excel, efter at have klikket på Indtast vi ville kun få indholdet af én første celle B2. Hvordan ellers?

Nå, ellers ville det være muligt at pakke dette interval ind i en form for aggregeringsfunktion som =SUM(B2:C4) og få en totalsum for det.

Hvis vi havde brug for mere komplekse operationer end en primitiv sum, såsom at udtrække unikke værdier eller Top 3, så skulle vi indtaste vores formel som en matrixformel ved hjælp af en tastaturgenvej Ctrl+Flytte+Indtast.

Nu er alt anderledes.

Nu efter at have indtastet en sådan formel, kan vi blot klikke videre Indtast – og få som et resultat straks alle værdierne uXNUMXbuXNUMXb, som vi henviste til:

Dette er ikke magi, men de nye dynamiske arrays, som Microsoft Excel nu har. Velkommen til den nye verden 🙂

Funktioner ved at arbejde med dynamiske arrays

Teknisk set er hele vores dynamiske array gemt i den første celle G4, og fylder det nødvendige antal celler til højre og ned med sine data. Hvis du vælger en anden celle i arrayet, så vil linket i formellinjen være inaktivt, hvilket viser, at vi er i en af ​​"underordnede" cellerne:

Et forsøg på at slette en eller flere "underordnede" celler vil ikke føre til noget - Excel vil straks genberegne og udfylde dem.

Samtidig kan vi trygt henvise til disse "barneceller" i andre formler:

Hvis du kopierer den første celle i et array (for eksempel fra G4 til F8), vil hele arrayet (dets referencer) bevæge sig i samme retning som i almindelige formler:

Hvis vi har brug for at flytte arrayet, så vil det være nok at flytte (med musen eller en kombination af Ctrl+X, Ctrl+V), igen, kun den første hovedcelle G4 - efter den vil den blive overført til et nyt sted, og hele vores array vil blive udvidet igen.

Hvis du har brug for at henvise et andet sted på arket til det oprettede dynamiske array, kan du bruge specialtegnet # ("pund") efter adressen på dens indledende celle:

For eksempel kan du nu nemt lave en rulleliste i en celle, der refererer til det oprettede dynamiske array:

Dynamic array fejl

Men hvad sker der, hvis der ikke er plads nok til at udvide arrayet, eller hvis der allerede er celler optaget af andre data på dens vej? Mød en fundamentalt ny type fejl i Excel – #OVERFØRSEL! (#SPILDE!):

Som altid, hvis vi klikker på ikonet med en gul diamant og et udråbstegn, får vi en mere detaljeret forklaring på kilden til problemet, og vi kan hurtigt finde forstyrrende celler:

Lignende fejl vil opstå, hvis arrayet forsvinder fra arket eller rammer en flettet celle. Hvis du fjerner forhindringen, vil alt straks blive rettet i farten.

Dynamiske arrays og smarte tabeller

Hvis det dynamiske array peger på en "smart" tabel oprettet af en tastaturgenvej Ctrl+T eller Hjem – Formater som en tabel (Hjem — Formater som tabel), så vil den også arve sin hovedkvalitet – auto-sizing.

Når du tilføjer nye data til bunden eller til højre, vil den smarte tabel og det dynamiske område også automatisk strække sig:

Der er dog én begrænsning: vi kan ikke bruge en dynamisk områdereference i fora inde i en smart tabel:

Dynamiske arrays og andre Excel-funktioner

Okay, siger du. Alt dette er interessant og sjovt. Intet behov, som før, manuelt at strække formlen med en reference til den første celle i det oprindelige område ned og til højre og alt det der. Og det er alt?

Ikke helt.

Dynamiske arrays er ikke bare endnu et værktøj i Excel. Nu er de indlejret i hjertet (eller hjernen) af Microsoft Excel - dets beregningsmotor. Det betyder, at andre Excel-formler og -funktioner, vi kender, nu også understøtter arbejdet med dynamiske arrays. Lad os tage et kig på et par eksempler for at give dig en idé om dybden af ​​de ændringer, der har fundet sted.

Transpose

For at transponere et område (bytte rækker og kolonner) har Microsoft Excel altid haft en indbygget funktion TRANSP (TRANSPOSERER). Men for at bruge det skal du først vælge området for resultaterne korrekt (hvis input f.eks. var et område på 5×3, så skal du have valgt 3×5), derefter indtaste funktionen og trykke på kombination Ctrl+Flytte+Indtast, fordi det kun kunne fungere i matrixformeltilstand.

Nu kan du bare vælge en celle, indtaste den samme formel i den og klikke på den normale Indtast – dynamisk array vil gøre alt af sig selv:

Multiplikationstabel

Dette er det eksempel, jeg plejede at give, da jeg blev bedt om at visualisere fordelene ved matrixformler i Excel. Nu, for at beregne hele Pythagoras tabel, er det nok at stå i den første celle B2, indtaste en formel der multiplicerer to arrays (lodret og vandret sæt af tal 1..10) og blot klikke på Indtast:

Limning og kasseombygning

Arrays kan ikke kun multipliceres, men også limes sammen med standardoperatoren & (ampersand). Antag, at vi skal udtrække for- og efternavnet fra to kolonner og rette springende tilfælde i de originale data. Det gør vi med én kort formel, der danner hele arrayet, og derefter anvender vi funktionen på den PROPNAK (PASSENDE)at rydde op i registeret:

Konklusion Top 3

Antag, at vi har en masse tal, som vi ønsker at udlede de tre øverste resultater fra, og arrangere dem i faldende rækkefølge. Nu gøres dette med én formel og igen uden nogen Ctrl+Flytte+Indtast ligesom før:

Hvis du ønsker, at resultaterne ikke skal placeres i en kolonne, men i en række, så er det nok at erstatte kolonerne (linjeseparator) i denne formel med et semikolon (elementseparator inden for en linje). I den engelske version af Excel er disse separatorer henholdsvis semikolon og komma.

VLOOKUP udtrækker flere kolonner på én gang

Funktioner VPR (OPLYSNING) nu kan du trække værdier ikke fra én, men fra flere kolonner på én gang - bare angiv deres numre (i en hvilken som helst ønsket rækkefølge) som en matrix i funktionens tredje argument:

OFFSET-funktion, der returnerer et dynamisk array

En af de mest interessante og nyttige (efter VLOOKUP) funktioner til dataanalyse er funktionen BORTSKAFFELSE (OFFSET), som jeg på én gang viede et helt kapitel i min bog og en artikel her. Vanskeligheden ved at forstå og mestre denne funktion har altid været, at den returnerede et array (område) af data som et resultat, men vi kunne ikke se det, fordi Excel stadig ikke vidste, hvordan man arbejdede med arrays ud af boksen.

Nu er dette problem i fortiden. Se, hvordan du nu, ved at bruge en enkelt formel og et dynamisk array returneret af OFFSET, kan udtrække alle rækker for et givet produkt fra enhver sorteret tabel:

Lad os tage et kig på hendes argumenter:

  • A1 – startcelle (referencepunkt)
  • ПОИСКПОЗ(F2;A2:A30;0) – beregning af skiftet fra startcellen ned – til den først fundne kål.
  • 0 – forskydning af "vinduet" til højre i forhold til startcellen
  • СЧЁТЕСЛИ(A2:A30;F2) – beregning af højden på det returnerede “vindue” – antallet af linjer, hvor der er kål.
  • 4 — Størrelsen af ​​"vinduet" vandret, dvs. output 4 kolonner

Nye funktioner til dynamiske arrays

Udover at understøtte den dynamiske array-mekanisme i gamle funktioner, er der tilføjet flere helt nye funktioner til Microsoft Excel, der er skærpet specifikt til at arbejde med dynamiske arrays. Disse er især:

  • GRADE (SORTERE) – sorterer inputområdet og producerer et dynamisk array på outputtet
  • SORTPO (SORTER EFTER) – kan sortere et område efter værdier fra et andet
  • FILTER (FILTER) – henter rækker fra kildeområdet, der opfylder de angivne betingelser
  • ENESTÅENDE (ENESTÅENDE) - udtrækker unikke værdier fra et område eller fjerner dubletter
  • SLMASSIVT (RANDARRAY) – genererer en matrix af tilfældige tal af en given størrelse
  • EFTERFØDSEL (SEKVENS) — danner en matrix ud fra en række tal med et givet trin

Mere om dem – lidt senere. De er værd at have en separat artikel (og ikke en) til eftertænksom undersøgelse 🙂

konklusioner

Hvis du har læst alt skrevet ovenfor, så tror jeg, du allerede er klar over omfanget af de ændringer, der er sket. Så mange ting i Excel kan nu gøres nemmere, nemmere og mere logisk. Jeg må indrømme, at jeg er lidt chokeret over, hvor mange artikler der nu skal rettes her, på denne side og i mine bøger, men jeg er klar til at gøre dette med let hjerte.

Opsummerer resultaterne, плюсы dynamiske arrays, kan du skrive følgende:

  • Du kan glemme alt om kombinationen Ctrl+Flytte+Indtast. Excel ser nu ingen forskel mellem "almindelige formler" og "matrixformler" og behandler dem på samme måde.
  • Om funktionen SUMPRODUCT (SUMPRODUKT), som tidligere blev brugt til at indtaste matrixformler uden Ctrl+Flytte+Indtast du kan også glemme – nu er det nemt nok SUM и Indtast.
  • Smarte tabeller og velkendte funktioner (SUM, IF, VLOOKUP, SUMIFS osv.) understøtter nu også helt eller delvist dynamiske arrays.
  • Der er bagudkompatibilitet: Hvis du åbner en projektmappe med dynamiske arrays i en gammel version af Excel, bliver de til arrayformler (i krøllede klammeparenteser) og fortsætter med at arbejde i den "gamle stil".

Fandt et eller andet nummer minus:

  • Du kan ikke slette individuelle rækker, kolonner eller celler fra et dynamisk array, dvs. det lever som en enkelt enhed.
  • Du kan ikke sortere et dynamisk array på den sædvanlige måde Data – Sortering (Data — Sorter). Der er nu en særlig funktion til dette. GRADE (SORTERE).
  • Et dynamisk område kan ikke omdannes til en smart tabel (men du kan lave et dynamisk område baseret på en smart tabel).

Dette er selvfølgelig ikke slutningen, og jeg er sikker på, at Microsoft vil fortsætte med at forbedre denne mekanisme i fremtiden.

Hvor kan jeg downloade?

Og til sidst hovedspørgsmålet 🙂

Microsoft annoncerede og viste først en forhåndsvisning af dynamiske arrays i Excel tilbage i september 2018 på en konference Ignite. I de næste par måneder var der en grundig test og indkøring af nye funktioner, først til katte ansatte hos Microsoft selv, og så på frivillige testere fra kredsen af ​​Office Insiders. I år begyndte opdateringen, der tilføjer dynamiske arrays, gradvist at blive rullet ud til almindelige Office 365-abonnenter. For eksempel modtog jeg den først i august med mit Office 365 Pro Plus (månedligt målrettet) abonnement.

Hvis din Excel endnu ikke har dynamiske arrays, men du virkelig gerne vil arbejde med dem, så er der følgende muligheder:

  • Hvis du har et Office 365-abonnement, kan du blot vente, indtil denne opdatering når dig. Hvor hurtigt dette sker afhænger af, hvor ofte opdateringer leveres til dit Office (en gang om året, en gang hver sjette måned, en gang om måneden). Hvis du har en virksomheds-pc, kan du bede din administrator om at konfigurere opdateringer til at blive downloadet oftere.
  • Du kan slutte dig til rækken af ​​disse Office Insiders testfrivillige – så vil du være den første til at modtage alle de nye funktioner og funktioner (men der er selvfølgelig en chance for øget buggy i Excel).
  • Hvis du ikke har et abonnement, men en selvstændig version af Excel i boks, så skal du i hvert fald vente til udgivelsen af ​​den næste version af Office og Excel i 2022. Brugere af sådanne versioner modtager kun sikkerhedsopdateringer og fejlrettelser, og alle de nye "godbidder" går nu kun til Office 365-abonnenter. Trist men sandt 🙂

Under alle omstændigheder, når dynamiske arrays dukker op i din Excel – efter denne artikel er du klar til det 🙂

  • Hvad er matrixformler, og hvordan man bruger dem i Excel
  • Vindue (område) summering ved hjælp af OFFSET-funktionen
  • 3 måder at transponere en tabel i Excel

Giv en kommentar