Innhold
Hva er dynamiske arrays
I september 2018 slapp Microsoft en oppdatering som legger til et helt nytt verktøy til Microsoft Excel: Dynamic Arrays og 7 nye funksjoner for å jobbe med dem. Disse tingene, uten overdrivelse, endrer radikalt all den vanlige teknikken for å jobbe med formler og funksjoner og angår bokstavelig talt hver bruker.
Tenk på et enkelt eksempel for å forklare essensen.
Anta at vi har en enkel tabell med data om bymåneder. Hva vil skje hvis vi velger en tom celle til høyre på arket og legger inn en formel som kobler ikke til én celle, men umiddelbart til et område?
I alle tidligere versjoner av Excel, etter å ha klikket på Enter vi ville få innholdet i bare én første celle B2. Hvordan ellers?
Vel, eller det ville være mulig å pakke dette området inn i en slags aggregeringsfunksjon som =SUM(B2:C4) og få en totalsum for det.
Hvis vi trengte mer komplekse operasjoner enn en primitiv sum, for eksempel å trekke ut unike verdier eller Topp 3, så måtte vi legge inn formelen vår som en matriseformel ved hjelp av en hurtigtast Ctrl+Skift+Enter.
Nå er alt annerledes.
Nå etter å ha lagt inn en slik formel, kan vi ganske enkelt klikke videre Enter – og få som et resultat umiddelbart alle verdiene uXNUMXbuXNUMXb som vi refererte til:
Dette er ikke magi, men de nye dynamiske arrayene som Microsoft Excel nå har. Velkommen til den nye verden 🙂
Funksjoner ved å jobbe med dynamiske matriser
Teknisk sett er hele den dynamiske matrisen vår lagret i den første cellen G4, og fyller det nødvendige antallet celler til høyre og ned med dataene. Hvis du velger en annen celle i matrisen, vil koblingen i formellinjen være inaktiv, noe som viser at vi er i en av "barn"-cellene:
Et forsøk på å slette en eller flere "barneceller" vil ikke føre til noe - Excel vil umiddelbart beregne og fylle dem på nytt.
Samtidig kan vi trygt referere til disse "barne"-cellene i andre formler:
Hvis du kopierer den første cellen i en matrise (for eksempel fra G4 til F8), vil hele matrisen (referansene) bevege seg i samme retning som i vanlige formler:
Hvis vi trenger å flytte matrisen, vil det være nok å flytte (med musen eller en kombinasjon av Ctrl+X, Ctrl+V), igjen, bare den første hovedcellen G4 – etter den vil den bli overført til et nytt sted, og hele arrayet vårt utvides igjen.
Hvis du trenger å referere et annet sted på arket til den opprettede dynamiske matrisen, kan du bruke spesialtegnet # ("pund") etter adressen til den ledende cellen:
For eksempel, nå kan du enkelt lage en rullegardinliste i en celle som refererer til den opprettede dynamiske matrisen:
Dynamiske array-feil
Men hva skjer hvis det ikke er nok plass til å utvide matrisen, eller hvis det er celler som allerede er okkupert av andre data i veien? Møt en fundamentalt ny type feil i Excel – #OVERFØRE! (#SØLE!):
Som alltid, hvis vi klikker på ikonet med en gul diamant og et utropstegn, vil vi få en mer detaljert forklaring på kilden til problemet, og vi kan raskt finne forstyrrende celler:
Lignende feil vil oppstå hvis matrisen forsvinner fra arket eller treffer en sammenslått celle. Hvis du fjerner hindringen, vil alt umiddelbart bli rettet opp.
Dynamiske arrayer og smarte tabeller
Hvis den dynamiske matrisen peker til en "smart" tabell opprettet av en hurtigtast Ctrl+T eller ved Hjem – Formater som en tabell (Hjem — Formater som tabell), så vil den også arve hovedkvaliteten – automatisk størrelse.
Når du legger til nye data nederst eller til høyre, vil smarttabellen og det dynamiske området også automatisk strekke seg:
Det er imidlertid en begrensning: vi kan ikke bruke en dynamisk områdereferanse i forumer inne i en smart tabell:
Dynamiske matriser og andre Excel-funksjoner
Ok, sier du. Alt dette er interessant og morsomt. Du trenger ikke, som før, manuelt å strekke formelen med en referanse til den første cellen i det opprinnelige området ned og til høyre og alt det der. Og det er alt?
Ikke helt.
Dynamiske arrays er ikke bare et annet verktøy i Excel. Nå er de innebygd i hjertet (eller hjernen) til Microsoft Excel – beregningsmotoren. Dette betyr at andre Excel-formler og -funksjoner som er kjent for oss nå også støtter arbeid med dynamiske matriser. La oss ta en titt på noen få eksempler for å gi deg en ide om dybden av endringene som har funnet sted.
transponere
For å transponere et område (bytte rader og kolonner) har Microsoft Excel alltid hatt en innebygd funksjon TRANSP (TRANSPOSER). Men for å bruke det, må du først velge området riktig for resultatene (hvis for eksempel inndata var et område på 5×3, så må du ha valgt 3×5), deretter angi funksjonen og trykk på kombinasjon Ctrl+Skift+Enter, fordi det bare kunne fungere i matriseformelmodus.
Nå kan du bare velge én celle, skrive inn den samme formelen i den og klikke på den normale Enter – dynamisk array vil gjøre alt av seg selv:
Gangetabell
Dette er eksemplet jeg pleide å gi da jeg ble bedt om å visualisere fordelene med matriseformler i Excel. Nå, for å beregne hele Pythagoras-tabellen, er det nok å stå i den første cellen B2, skrive inn en formel som multipliserer to matriser (vertikalt og horisontalt sett med tall 1..10) og bare klikk på Enter:
Liming og kasseombygging
Arrays kan ikke bare multipliseres, men også limes sammen med standardoperatoren & (ampersand). Anta at vi må trekke ut for- og etternavn fra to kolonner og korrigere hoppsaken i de opprinnelige dataene. Vi gjør dette med én kort formel som danner hele matrisen, og deretter bruker vi funksjonen på den PROPNAK (ORDENTLIG)for å rydde opp i registeret:
Konklusjon Topp 3
Anta at vi har en haug med tall som vi ønsker å utlede de tre beste resultatene fra, og ordne dem i synkende rekkefølge. Nå gjøres dette med én formel og, igjen, uten noen Ctrl+Skift+Enter som før:
Hvis du vil at resultatene ikke skal plasseres i en kolonne, men i en rad, er det nok å erstatte kolonene (linjeskilletegn) i denne formelen med semikolon (elementseparator innenfor en linje). I den engelske versjonen av Excel er disse skilletegnene henholdsvis semikolon og komma.
VLOOKUP trekker ut flere kolonner samtidig
Funksjoner VPR (VISNING) nå kan du trekke verdier ikke fra én, men fra flere kolonner samtidig - bare spesifiser tallene deres (i ønsket rekkefølge) som en matrise i funksjonens tredje argument:
OFFSET-funksjon som returnerer en dynamisk matrise
En av de mest interessante og nyttige (etter VLOOKUP) funksjonene for dataanalyse er funksjonen FJERNING (OFFSET), som jeg på en gang viet et helt kapittel i boken min og en artikkel her. Vanskeligheten med å forstå og mestre denne funksjonen har alltid vært at den returnerte en matrise (rekkevidde) med data som et resultat, men vi kunne ikke se det, fordi Excel fortsatt ikke visste hvordan man skulle jobbe med matriser ut av boksen.
Nå er dette problemet i fortiden. Se hvordan du nå, ved å bruke en enkelt formel og en dynamisk matrise returnert av OFFSET, kan trekke ut alle radene for et gitt produkt fra en hvilken som helst sortert tabell:
La oss ta en titt på argumentene hennes:
- A1 – startcelle (referansepunkt)
- ПОИСКПОЗ(F2;A2:A30;0) – beregning av skiftet fra startcellen ned – til den først funnet kålen.
- 0 – forskyvning av "vinduet" til høyre i forhold til startcellen
- СЧЁТЕСЛИ(A2:A30;F2) – beregning av høyden på det returnerte “vinduet” – antall linjer der det er kål.
- 4 — Størrelsen på "vinduet" horisontalt, dvs. skriv ut 4 kolonner
Nye funksjoner for dynamiske matriser
I tillegg til å støtte den dynamiske array-mekanismen i gamle funksjoner, er det lagt til flere helt nye funksjoner i Microsoft Excel, spisset spesifikt for arbeid med dynamiske arrays. Spesielt er disse:
- GRADE (SORTERE) – sorterer inngangsområdet og produserer en dynamisk matrise på utgangen
- SORTPO (SORTER ETTER) – kan sortere ett område etter verdier fra et annet
- FILTER (FILTER) – henter rader fra kildeområdet som oppfyller de angitte betingelsene
- UNIK (UNIK) – trekker ut unike verdier fra et område eller fjerner duplikater
- SLMASSIVT (RANDARRAY) – genererer en rekke tilfeldige tall av en gitt størrelse
- ETTERFØDSEL (SEKVENS) — danner en matrise fra en tallsekvens med et gitt trinn
Mer om dem – litt senere. De er verdt en egen artikkel (og ikke en) for gjennomtenkt studium 🙂
Konklusjoner
Hvis du har lest alt som er skrevet ovenfor, så tror jeg du allerede er klar over omfanget av endringene som har skjedd. Så mange ting i Excel kan nå gjøres enklere, enklere og mer logisk. Jeg må innrømme at jeg er litt sjokkert over hvor mange artikler som nå må rettes her, på denne siden og i bøkene mine, men jeg er klar til å gjøre dette med lett hjerte.
Oppsummerer resultatene, плюсы dynamiske matriser, kan du skrive følgende:
- Du kan glemme kombinasjonen Ctrl+Skift+Enter. Excel ser nå ingen forskjell mellom "vanlige formler" og "matriseformler" og behandler dem på samme måte.
- Om funksjonen SUMPRODUCT (SUMPRODUKT), som tidligere ble brukt til å angi matriseformler uten Ctrl+Skift+Enter du kan også glemme – nå er det enkelt nok SUM и Enter.
- Smarte tabeller og kjente funksjoner (SUM, IF, VLOOKUP, SUMIFS, etc.) støtter nå også helt eller delvis dynamiske arrays.
- Det er bakoverkompatibilitet: Hvis du åpner en arbeidsbok med dynamiske matriser i en gammel versjon av Excel, blir de til matriseformler (i krøllete klammeparenteser) og fortsetter å jobbe i "gammel stil".
Fant et nummer minus:
- Du kan ikke slette individuelle rader, kolonner eller celler fra en dynamisk matrise, dvs. den lever som en enkelt enhet.
- Du kan ikke sortere en dynamisk matrise på vanlig måte Data – Sortering (Data – Sorter). Det er nå en spesiell funksjon for dette. GRADE (SORTERE).
- Et dynamisk område kan ikke gjøres om til en smart tabell (men du kan lage et dynamisk område basert på en smart tabell).
Dette er selvfølgelig ikke slutten, og jeg er sikker på at Microsoft vil fortsette å forbedre denne mekanismen i fremtiden.
Hvor kan jeg laste ned?
Og til slutt, hovedspørsmålet 🙂
Microsoft annonserte og viste først en forhåndsvisning av dynamiske arrays i Excel tilbake i september 2018 på en konferanse Ignite. I løpet av de neste månedene var det en grundig testing og innkjøring av nye funksjoner, først med katter ansatte i Microsoft selv, og deretter på frivillige testere fra kretsen av Office Insiders. I år begynte oppdateringen som legger til dynamiske arrays gradvis å bli rullet ut til vanlige Office 365-abonnenter. For eksempel mottok jeg den først i august med Office 365 Pro Plus (månedlig målrettet) abonnement.
Hvis Excel ennå ikke har dynamiske matriser, men du virkelig ønsker å jobbe med dem, er det følgende alternativer:
- Hvis du har et Office 365-abonnement, kan du ganske enkelt vente til denne oppdateringen når deg. Hvor raskt dette skjer avhenger av hvor ofte oppdateringer leveres til Office (en gang i året, en gang hver sjette måned, en gang i måneden). Hvis du har en bedrifts-PC, kan du be administratoren om å sette opp oppdateringer som skal lastes ned oftere.
- Du kan bli med i rekken av disse Office Insiders testfrivillige – da vil du være den første til å motta alle de nye funksjonene og funksjonene (men det er en sjanse for økt buggy i Excel, selvfølgelig).
- Hvis du ikke har et abonnement, men en eske frittstående versjon av Excel, må du vente til utgivelsen av neste versjon av Office og Excel i 2022, minst. Brukere av slike versjoner mottar bare sikkerhetsoppdateringer og feilrettinger, og alle de nye "godbitene" går nå kun til Office 365-abonnenter. Trist, men sant 🙂
I alle fall, når dynamiske matriser vises i Excel - etter denne artikkelen vil du være klar for det 🙂
- Hva er matriseformler og hvordan du bruker dem i Excel
- Vindu (område) summering ved bruk av OFFSET-funksjonen
- 3 måter å transponere en tabell i Excel