Innehåll
Vad är dynamiska arrayer
I september 2018 släppte Microsoft en uppdatering som lägger till ett helt nytt verktyg till Microsoft Excel: Dynamic Arrays och 7 nya funktioner för att arbeta med dem. Dessa saker, utan att överdriva, förändrar radikalt all den vanliga tekniken att arbeta med formler och funktioner och berör bokstavligen varje användare.
Överväg ett enkelt exempel för att förklara essensen.
Anta att vi har en enkel tabell med data om stadsmånader. Vad händer om vi markerar en tom cell till höger på arket och skriver in en formel i den som inte länkar till en cell, utan omedelbart till ett område?
I alla tidigare versioner av Excel, efter att ha klickat på ange vi skulle få innehållet i endast en första cell B2. Hur annars?
Tja, eller så skulle det vara möjligt att slå in det här intervallet i någon form av aggregeringsfunktion som =SUM(B2:C4) och få en totalsumma för det.
Om vi behövde mer komplexa operationer än en primitiv summa, som att extrahera unika värden eller Top 3, då skulle vi behöva ange vår formel som en matrisformel med en kortkommando ctrl+shift+ange.
Nu är allt annorlunda.
Nu efter att ha angett en sådan formel kan vi helt enkelt klicka på ange – och få som ett resultat omedelbart alla värden uXNUMXbuXNUMXb som vi hänvisade till:
Detta är inte magi, utan de nya dynamiska arrayerna som Microsoft Excel nu har. Välkommen till den nya världen 🙂
Funktioner för att arbeta med dynamiska arrayer
Tekniskt sett är hela vår dynamiska array lagrad i den första cellen G4, och fyller det nödvändiga antalet celler till höger och ner med dess data. Om du väljer någon annan cell i arrayen kommer länken i formelfältet att vara inaktiv, vilket visar att vi är i en av "barn"-cellerna:
Ett försök att ta bort en eller flera "barnceller" kommer inte att leda till någonting - Excel kommer omedelbart att räkna om och fylla dem.
Samtidigt kan vi säkert referera till dessa "barn" celler i andra formler:
Om du kopierar den första cellen i en array (till exempel från G4 till F8), kommer hela arrayen (dess referenser) att röra sig i samma riktning som i vanliga formler:
Om vi behöver flytta arrayen räcker det med att flytta (med musen eller en kombination av ctrl+X, ctrl+V), återigen, bara den första huvudcellen G4 – efter den kommer den att överföras till en ny plats och hela vår array kommer att utökas igen.
Om du behöver hänvisa någon annanstans på arket till den skapade dynamiska arrayen, kan du använda specialtecknet # ("pund") efter adressen till dess inledande cell:
Till exempel, nu kan du enkelt skapa en rullgardinslista i en cell som refererar till den skapade dynamiska arrayen:
Dynamiska arrayfel
Men vad händer om det inte finns tillräckligt med utrymme för att expandera arrayen, eller om det finns celler som redan är upptagna av andra data i dess väg? Möt en helt ny typ av fel i Excel – #ÖVERFÖRA! (#SPILL!):
Som alltid, om vi klickar på ikonen med en gul diamant och ett utropstecken, får vi en mer detaljerad förklaring av källan till problemet och vi kan snabbt hitta störande celler:
Liknande fel kommer att uppstå om arrayen försvinner från arket eller träffar en sammanfogad cell. Om du tar bort hindret kommer allt omedelbart att korrigeras i farten.
Dynamiska arrayer och smarta tabeller
Om den dynamiska matrisen pekar på en "smart" tabell skapad av en kortkommando ctrl+T eller genom Hem – Formatera som en tabell (Hem — Formatera som tabell), då kommer den också att ärva sin huvudsakliga kvalitet – automatisk storleksanpassning.
När du lägger till ny data längst ned eller till höger kommer den smarta tabellen och det dynamiska intervallet också automatiskt att sträcka sig:
Det finns dock en begränsning: vi kan inte använda en dynamisk intervallreferens i forum inuti en smart tabell:
Dynamiska arrayer och andra Excel-funktioner
Okej, säger du. Allt detta är intressant och roligt. Inget behov, som tidigare, att manuellt sträcka formeln med en referens till den första cellen i det ursprungliga intervallet nedåt och till höger och allt det där. Och det är allt?
Inte riktigt.
Dynamiska arrayer är inte bara ytterligare ett verktyg i Excel. Nu är de inbäddade i hjärtat (eller hjärnan) av Microsoft Excel – dess beräkningsmotor. Detta innebär att andra Excel-formler och -funktioner som vi känner till nu också stödjer att arbeta med dynamiska arrayer. Låt oss ta en titt på några exempel för att ge dig en uppfattning om djupet av de förändringar som har ägt rum.
införliva
För att transponera ett område (byta rader och kolumner) har Microsoft Excel alltid haft en inbyggd funktion ÖVERFÖRING (ÖVERFÖRA). Men för att kunna använda det måste du först välja intervallet för resultaten korrekt (om inmatningen till exempel var ett intervall på 5×3, då måste du ha valt 3×5), ange sedan funktionen och tryck på kombination ctrl+shift+ange, eftersom det bara kunde fungera i matrisformelläge.
Nu kan du bara välja en cell, ange samma formel i den och klicka på den normala ange – dynamisk array kommer att göra allt av sig själv:
Multiplikationstabell
Det här är exemplet jag brukade ge när jag blev ombedd att visualisera fördelarna med matrisformler i Excel. Nu, för att beräkna hela Pythagoras-tabellen, räcker det att stå i den första cellen B2, ange där en formel som multiplicerar två arrayer (vertikal och horisontell uppsättning siffror 1..10) och klicka helt enkelt på ange:
Limning och boettkonvertering
Arrayer kan inte bara multipliceras, utan även limmas ihop med standardoperatorn & (ampersand). Anta att vi behöver extrahera för- och efternamnet från två kolumner och korrigera fallet i originaldata. Vi gör detta med en kort formel som bildar hela arrayen, och sedan tillämpar vi funktionen på den PROPNACH (RÄTT)för att städa i registret:
Slutsats Topp 3
Anta att vi har ett gäng siffror från vilka vi vill härleda de tre bästa resultaten, ordna dem i fallande ordning. Nu görs detta med en formel och, återigen, utan någon ctrl+shift+ange som förut:
Om du vill att resultaten inte ska placeras i en kolumn, utan i en rad, räcker det med att ersätta kolonen (radavgränsare) i denna formel med ett semikolon (elementavgränsare inom en rad). I den engelska versionen av Excel är dessa avgränsare semikolon respektive kommatecken.
VLOOKUP extraherar flera kolumner samtidigt
Funktioner VPR (VISNING) nu kan du dra värden inte från en, utan från flera kolumner samtidigt - ange bara deras nummer (i valfri ordning) som en matris i det tredje argumentet för funktionen:
OFFSET-funktion som returnerar en dynamisk array
En av de mest intressanta och användbara (efter VLOOKUP) funktionerna för dataanalys är funktionen FÖRFOGANDE (OFFSET), som jag vid ett tillfälle ägnade ett helt kapitel i min bok och en artikel här. Svårigheten med att förstå och bemästra den här funktionen har alltid varit att den returnerade en array (omfång) av data som ett resultat, men vi kunde inte se det, eftersom Excel fortfarande inte visste hur man arbetar med arrays direkt.
Nu är detta problem i det förflutna. Se hur du nu, med en enda formel och en dynamisk array som returneras av OFFSET, kan extrahera alla rader för en given produkt från valfri sorterad tabell:
Låt oss ta en titt på hennes argument:
- A1 – startcell (referenspunkt)
- ПОИСКПОЗ(F2;A2:A30;0) – beräkning av förskjutningen från startcellen ner – till den först hittade kålen.
- 0 – förskjutning av "fönstret" till höger i förhållande till startcellen
- СЧЁТЕСЛИ(A2:A30;F2) – beräkning av höjden på det returnerade ”fönstret” – antalet rader där det finns kål.
- 4 — Storleken på "fönstret" horisontellt, dvs. mata ut 4 kolumner
Nya funktioner för dynamiska arrayer
Förutom att stödja den dynamiska array-mekanismen i gamla funktioner har flera helt nya funktioner lagts till i Microsoft Excel, vässade specifikt för att arbeta med dynamiska arrayer. Dessa är särskilt:
- BETYG (SORTERA) – sorterar ingångsområdet och producerar en dynamisk array på utgången
- SORTPO (SORTERA EFTER) – kan sortera ett område efter värden från ett annat
- FILTER (FILTRERA) – hämtar rader från källområdet som uppfyller de angivna villkoren
- UNIK (UNIK) - extraherar unika värden från ett intervall eller tar bort dubbletter
- SLMASSIVT (RANDARRAY) – genererar en matris med slumptal av en given storlek
- EFTERFÖDELSE (SEKVENS) — bildar en matris från en talföljd med ett givet steg
Mer om dem – lite senare. De är värda en separat artikel (och inte en) för eftertänksamma studier 🙂
Slutsatser
Om du har läst allt som skrivits ovan, så tror jag att du redan inser omfattningen av de förändringar som har skett. Så många saker i Excel kan nu göras enklare, enklare och mer logiskt. Jag måste erkänna att jag är lite chockad över hur många artiklar som nu kommer att behöva rättas till här, på denna sida och i mina böcker, men jag är redo att göra detta med lätthet.
Sammanfattning av resultaten, плюсы dynamiska arrayer kan du skriva följande:
- Du kan glömma kombinationen ctrl+shift+ange. Excel ser nu ingen skillnad mellan "vanliga formler" och "matrisformler" och behandlar dem på samma sätt.
- Om funktionen SUMPRODUCT (SUMMAPRODUKT), som tidigare användes för att ange matrisformler utan ctrl+shift+ange du kan också glömma – nu är det lätt nog SUMMA и ange.
- Smarta tabeller och välbekanta funktioner (SUM, IF, VLOOKUP, SUMIFS, etc.) stöder nu också helt eller delvis dynamiska arrayer.
- Det finns bakåtkompatibilitet: om du öppnar en arbetsbok med dynamiska arrayer i en gammal version av Excel, kommer de att förvandlas till arrayformler (med lockiga klammerparenteser) och fortsätta att arbeta i "gammal stil".
Hittade ett nummer minus:
- Du kan inte ta bort enskilda rader, kolumner eller celler från en dynamisk array, dvs den lever som en enda enhet.
- Du kan inte sortera en dynamisk array på vanligt sätt Data – Sortering (Data — Sortera). Det finns nu en speciell funktion för detta. BETYG (SORTERA).
- Ett dynamiskt område kan inte förvandlas till ett smart bord (men du kan göra ett dynamiskt område baserat på en smart tabell).
Naturligtvis är detta inte slutet, och jag är säker på att Microsoft kommer att fortsätta att förbättra denna mekanism i framtiden.
Var kan jag ladda ner?
Och till sist, huvudfrågan 🙂
Microsoft tillkännagav och visade först en förhandsvisning av dynamiska arrayer i Excel redan i september 2018 på en konferens Tänd. Under de närmaste månaderna gjordes en grundlig testning och inkörning av nya funktioner, först med katter anställda hos Microsoft själv, och sedan på frivilliga testare från kretsen av Office Insiders. I år började uppdateringen som lägger till dynamiska arrayer gradvis rullas ut till vanliga Office 365-prenumeranter. Till exempel fick jag den först i augusti med min Office 365 Pro Plus (månadsinriktad) prenumeration.
Om din Excel ännu inte har dynamiska arrayer, men du verkligen vill arbeta med dem, så finns det följande alternativ:
- Om du har ett Office 365-abonnemang kan du helt enkelt vänta tills den här uppdateringen når dig. Hur snabbt detta händer beror på hur ofta uppdateringar levereras till ditt Office (en gång om året, en gång var sjätte månad, en gång i månaden). Om du har en företagsdator kan du be din administratör att ställa in uppdateringar som ska laddas ner oftare.
- Du kan gå med i raden av dessa Office Insiders testvolontärer – då kommer du att vara den första att få alla nya funktioner och funktioner (men det finns en chans till ökad buggy i Excel, förstås).
- Om du inte har en prenumeration, men en fristående version av Excel i box, måste du vänta tills nästa version av Office och Excel släpps åtminstone 2022. Användare av sådana versioner får bara säkerhetsuppdateringar och buggfixar, och alla nya "godsaker" går nu bara till Office 365-prenumeranter. Tråkigt men sant 🙂
I alla fall, när dynamiska arrayer visas i din Excel – efter den här artikeln är du redo för det 🙂
- Vad är matrisformler och hur man använder dem i Excel
- Summering av fönster (intervall) med OFFSET-funktionen
- 3 sätt att överföra en tabell i Excel