Hur man använder dynamiska matrisformler i Microsoft Excel

Om du arbetar med kalkylblad dagligen har du säkert märkt att så fort de börjar växa i rader och kolumner, Klassiska Excel-formler är något otillräckliga. att bekvämt utföra komplexa beräkningar. Det är där dynamiska matrisformler kommer in i bilden, en av de där nya sakerna som du, när du väl bemästrar dem, kommer att använda i nästan varje bok.

I moderna versioner av Excel (särskilt i Microsoft 365Vi har en ny beräkningsmotor som gör att en enda formel kan generera flera resultat samtidigt, som sträcker sig över flera angränsande celler utan att manuell kopiering krävs. Tack vare detta beteende, "överflöd" av resultat i bulkNu är det mycket enklare att sortera, filtrera, generera listor eller utföra avancerade beräkningar utan att behöva tillgripa Tricks sällsynta eller till tangentkombinationer som Ctrl+Shift+Enter.

Vad är överflöde i dynamiska matrisformler?

I Excels nya beräkningsmodell kan en formel returnera inte bara ett värde, utan en hel organiserad uppsättning resultat; denna uppsättning kallas vanligtvis överfylld utmatningsmatris eller -intervallNär detta händer placerar Excel automatiskt dessa värden i närliggande celler och expanderar formeln nedåt, åt höger eller i båda riktningarna, beroende på resultatets storlek.

Till exempel när man skriver formeln =SORTERING(D2:D11;1;-1) I en enskild cell (till exempel F2) genererar Excel en fallande sorterad lista baserad på intervallet D2:D11. Resultatet upptar 10 rader, men du anger bara formeln i en cell; resten av positionerna fylls av Excel självt med hjälp av denna överflödesmekanism.

Formler som kan ändra storleken på sitt resultat baserat på källdata kallas dynamiska matrisformlerNär dessa formler returnerar ett resultatintervall som sträcker sig bortom cellen där formeln skrevs, sägs det att formeln har "överflödat" och det område den upptar kallas överflödesintervallet.

I praktiken innebär detta att många funktioner, såsom SORT, FILTER, RANDOMAR, SEQUENCE eller UNIQUE, nu är utformade för att returnera kompletta, färdiga datamatriser, på ett mycket mer direkt och läsbart sätt än med de gamla matrisformlerna.

Hur överflödesintervallet beter sig i Excel

När du bekräftar en dynamisk matrisformel genom att bara trycka på Enter-tangenten, analyserar Excel resultatet och justerar automatiskt utmatningsområdets storlek för att rymma alla värden som returneras av formeln. Placera sedan varje element i arrayen i motsvarande cell inom det överfyllda området.

Om du skriver en av dessa formler i en lista eller datatabell kan det vara ganska praktiskt att konvertera källdata till en Excel-tabell med strukturerade referenserTabellerna anpassas automatiskt när du lägger till eller tar bort rader, så dynamiska matrisformler som använder dem uppdateras utan att du behöver röra något.

Det är viktigt att veta att överfyllningsformler inte fungerar inom själva tabellerna: Överfyllnad är inte tillåtet i en Excel-tabell.Istället bör du placera dessa formler i det vanliga rutnätet (utanför tabellen) och endast använda tabellen som en datakälla. Tabeller är avsedda att lagra poster, inte att utöka ett helt utdataområde inom dem.

När du klickar på en cell inom överskottsområdet ritar Excel en markerad ruta runt hela uppsättningen berörda celler. Detta gör det mycket enkelt att se med en snabb blick. hur långt formeln sträcker sig och vilka celler som är beroende av denSå snart du markerar en cell utanför det området försvinner kanten.

En annan viktig detalj är att i ett överflödigt område innehåller endast den första cellen (den i det övre vänstra hörnet) formeln. Resten av cellerna visar resultat, men om du väljer någon av dem ser du formeln nedtonad i formelfältet. Du kommer inte att kunna ändra det direkt.Du måste alltid redigera källcellen; efter att du har ändrat den och tryckt på Enter kommer Excel att beräkna om hela det överfyllda området på en gång.

Överlappningsfel och #OVERFLOW-meddelandet!

För att en dynamisk matrisformel ska kunna expanderas smidigt behöver Excel att utdataområdet är tomt. Om data, formler eller andra element upptar någon av cellerna där resultaten ska visas kommer ett fel att uppstå. Överlappning av överflödesintervall och Excel kan inte slutföra operationen.

I så fall kommer du att se ett felmeddelande istället för det förväntade resultatet. #SVÄMMA ÖVER! i cellen där du angav formeln. Så här varnar Excel dig om att det finns ett lås som förhindrar att arrayen placeras i alla celler som behövs för att innehålla sina resultat.

Om du väljer formeln i fråga visar Excel, med en prickad kantlinje, det område där det avser att överfyllas, inklusive eventuella celler som blockerar processen. I den här vyn kan du enkelt hitta de problematiska cellerna så att de kan tömmas eller deras innehåll flyttas till en annan plats.

När du har tagit bort data som förhindrar expansionen (till exempel lösryckta värden eller tidigare formler) kommer Excel att orsaka att formeln överfylls som avsett när du beräknar om arket. I många situationer räcker det med att bara ta bort ett par celler för att felet #ÖVERFYLLNING! ska försvinna direkt.

Det är också möjligt att själva formeln är felaktigt utformad och returnerar en array som är för stor för det tillgängliga utrymmet. I dessa fall är det lämpligt att granska formeln. både formelns logik och det fria utrymmet runt för att säkerställa att utgångsområdet kan växa efter behov.

Skillnader mellan dynamiska matrisformler och äldre CSE-formler

Innan dynamiska matriser uppstod introducerades matrisformler med den berömda kombinationen Ctrl+Skift+Enter (CSE)Dessa äldre formler stöds fortfarande i Excel av bakåtkompatibilitetsskäl, men den rekommenderade metoden idag är att arbeta med den nya dynamiska modellen, som är enklare och mindre felbenägen.

En av de stora fördelarna med dynamiska matrisformler är att Du behöver bara ange formeln en gång i den övre vänstra cellen.De återstående resultaten visas automatiskt tack vare överflödet. I äldre CSE-formler var man tvungen att markera hela området där man ville att resultaten skulle visas och sedan bekräfta formeln med Ctrl+Shift+Enter.

Dynamiska formler kan också justera sin storlek när källdata ändras. Om du lägger till fler rader i datakällan kan arrayen expandera; om du tar bort data kan den krympa – allt utan att du behöver redigera området manuellt. Med äldre CSE-arrayer, om returområdet var för litetResultaten var avkortade; och om det var för stort kunde du stöta på fel som #N/A.

En annan intressant skillnad är att många klassiska funktioner, som RAND, ROW eller COLUMN, nu utvärderas i en encellskontext (1x1). Om du vill generera flera slumpmässiga resultat eller talsekvenser baserade på rader och kolumner rekommenderas det att använda funktioner som Slumpmässig matris eller sekvenssom är utformade för att returnera kompletta matriser och fungerar perfekt med den nya dynamiska motorn.

Dessutom fanns det tidigare ett fenomen som kallas "CSE-brytning", varigenom vissa äldre matrisformler som var beroende av varandra kunde beräknas oberoende och returnera inkonsekventa eller svårfelsökta resultatMed dynamiska arrayer försvinner detta beteende: om det finns cirkulära referenser kommer Excel att flagga dem som sådana istället för att bryta formelns logik.

Att modifiera en dynamisk matrisformel är också enklare. Du behöver bara redigera källcellen, så uppdateras resten automatiskt; med CSE-formler var detta nödvändigt. redigera hela det berörda området på en gångDetta blev ganska besvärligt med stora områden. Dessutom, när ett ark innehöll ett aktivt område med en CSE-formel, var det inte möjligt att infoga eller ta bort rader eller kolumner som störde det området förrän den ärvda matrisformeln först togs bort eller ändrades.

Använda nyckelfunktioner med dynamiska arrayer

Bland de kraftfullaste funktionerna som utnyttjar dynamiska arrayer är FILTER, SORTERA, SORTERA EFTER, UNIK, SLUMPMÄSSIGT MATRICK OCH SEKVENSDe returnerar alla fullständiga intervall, så de passar perfekt med överflödesbeteende, och det finns användbara verktyg som Excel Formel Bot som gör den enklare att använda.

Med funktionen FILTER kan du till exempel extrahera endast de rader som uppfyller vissa kriterier från en datatabell, vilket returnerar en resultatuppsättning som uppdateras automatiskt när källdata ändras. Den här funktionen kombineras mycket bra med UNIQUE, vilket gör det möjligt hämta listor med värden utan dubbletter från kolumner med många upprepade data.

Funktionen MATRIZALEAT genererar ett intervall av slumptal i ett block, perfekt för simuleringar eller tester; SECUENCIA, å andra sidan, returnerar matriser med serier av på varandra följande tal, vilket gör att du kan anpassa ökningen och matrisstorleken efter dina behov. Båda är baserade på den nya matrismodellen och är utformade för fylla stora områden av arket på en gång.

Slutligen gör SORT och SORTBY det mycket enklare att skapa sorterade listor från befintliga kolumner eller tabeller. Istället för att använda manuell sortering eller komplexa kombinationer av funktioner kan du nu skriva en enda formel som returnerar sorterade data och den anpassar sig automatiskt till förändringar i de ursprungliga värdena.

Alla dessa funktioner kombineras med varandra och, tack vare overflow, låter dig bygga mycket avancerade lösningar utan behov av makron eller äldre, svårunderhållna matrisformler, och för att automatisera dina arbetsböcker, Office-skript i Excel Web Det kan vara till stor hjälp.

Beräkningsskillnader mellan dynamiska matriser och ärvda matriser

Om du fortfarande arbetar med äldre böcker som använder CSE-matrisformler är det viktigt att komma ihåg att när du konverterar dem till deras motsvarande dynamik kan ändra beteendet något I vissa fall. Även om konverteringen i de flesta fall är direkt, är det lämpligt att granska resultatet.

Det vanliga sättet att konvertera från en äldre array till en dynamisk array är att leta upp den första cellen i arrayområdet, kopiera formeltexten, ta bort hela det gamla området och sedan skriv om formeln endast i den övre vänstra cellen Med den nya metoden hanterar Excel automatiskt överfyllning av resultatet.

Under övergången, var särskilt uppmärksam på funktioner som tidigare förlitade sig på implicit skärningspunktsbeteende eller speciella utvärderingar inom CSE-formler. Excel har nu implicit skärningspunktsoperator (@)vilket tjänar till att replikera det gamla beteendet i vissa fall, men den allmänna rekommendationen kvarstår att explicit skriva om formlerna och dra nytta av de nya funktionerna.

Excel erbjuder också begränsat stöd när en dynamisk array refererar till data som finns i en annan arbetsbok. Korrekt funktion garanteras endast när Båda filerna är öppna samtidigtOm du stänger källarbetsboken kan länkade dynamiska matrisformler returnera #REF!-felet vid försök att uppdatera, så det är viktigt att ha detta i åtanke för komplexa modeller med flera externa referenser. I sådana fall kan du se hur spara och dela arbetsböcker för att undvika problem.

Även om CSE-formler kommer att fortsätta existera för bakåtkompatibilitet, rekommenderar Microsoft självt att man slutar skapa dem i nya projekt och väljer dynamiska arrayer. Läsbarhet, enkel underhållning och robusthet Dessa nya formler gör dem till det föredragna alternativet för framtiden.

Överfyllningsområde och praktisk redigering på arket

När en formel överfylls kallas området den upptar för överfyllnadsområdet. I detta område, som vi redan nämnt, innehåller endast den första cellen själva formeln. De återstående cellerna visar resultat, men de "styrs" från källcellen, vilket innebär att hela matrisen beter sig som en enhet.

Om du till exempel skriver funktionen =ÖVERDEL(E7:E19) I en enda cell ser du hur Excel returnerar texten från det området konverterad till versaler över flera rader. Om du markerar någon av cellerna i utdataområdet ser du resultatet, men när du tittar på formelfältet ser du att innehållet visas nedtonat, vilket indikerar att Den är inte direkt redigerbarAlla ändringar måste göras i den cell där du först skrev formeln.

Detta beteende har en tydlig fördel: det förhindrar att du av misstag bara ändrar en del av intervallet och lämnar resten oförändrat, vilket ofta leder till fel som är svåra att hitta. Om du behöver ändra formeln redigerar du den bara en gång i källcellen, trycker på Enter så uppdaterar Excel den. beräknar om hela blocket konsekvent.

I vardagsbruk påverkar detta även hur du tar bort eller flyttar data. Om du försöker ta bort en enskild cell inom överflödsområdet varnar Excel dig om att du påverkar en dynamisk array. För att undvika problem är det oftast bäst att ta bort eller klippa ut hela cellen. direkt till källcellen, som drar resten av området., eller justera formeln för att returnera en matris av en annan storlek.

När du kombinerar överflödesområden med andra formler, tänk på att dessa celler beräknas om tillsammans. Alla referenser till en dynamisk array bör göras noggrant, så att den nya motorn utnyttjas utan att skapa onödiga cirkulära referenser eller konflikter med andra delar av arket.

Exempel på avancerade matrisformler med verkliga data

Många klassiska matrisformler är fortfarande logiska, särskilt när man arbetar med stora intervall och man behöver komplexa villkorliga operationerLåt oss titta på några typiska exempel som du kan tycka är mycket användbara i rapporter och modeller.

Tänk dig att du har ett namngivet område som heter Data och som innehåller några felvärden som #N/A. Om du använder SUM-funktionen direkt på det området får du ett felmeddelande. För att undvika detta kan du använda en array som ignorerar fel med en formel som denna: =SUMMA(OM(ÄRFEL(Data);"";Data)), vilket internt skapar en array där fel ersätts med tomma strängar innan summering.

Genom att följa samma idé kan du också räkna hur många fel det finns i ett intervall. En formel som =SUMMA(OM(ÄRFEL(Data);1;0)) Den genererar en array med 1 när det finns ett fel och 0 när det inte finns något. Den totala summan visar antalet felaktiga celler. Du kan till och med förenkla det till =SUMMA(OM(ÄR FEL(Data);1)) och gå ett steg längre till =SUMMA(OM(ÄR FEL(Data)*1)), och utnyttjar det faktum att SANT*1 är 1 och FALSKT*1 är 0.

Ett annat vanligt scenario är att summera värden baserat på villkor. Du kanske till exempel har ett intervall som heter Försäljning och vill summera endast de positiva värdena med hjälp av en formel som =SUMMA(OM(Försäljning>0;Försäljning))Här genererar OM-funktionen en array med de värden som uppfyller villkoret och med falska värden för resten, vilket SUMMA ignorerar i praktiken.

Om du behöver tillämpa mer än ett villkor åt gången kan du multiplicera logiska uttryck för att emulera en "OCH"-operation och sedan lägga ihop dem. Ett typiskt exempel skulle vara =SUMMA((Försäljning>0)*(Försäljning<=5)*(Försäljning))Detta beräknar summan av försäljningar större än 0 och mindre än eller lika med 5. Denna metod kräver dock att intervallet inte innehåller textceller, annars kan du stöta på fel.

För att emulera ett "ELLER" kan du använda summor av logiska uttryck: till exempel, =SUMMA(OM((Försäljning<5)+(Försäljning>15);Försäljning))där värden mindre än 5 eller större än 15 läggs ihop. På så sätt kan du utföra avancerade operationer utan att direkt använda OCH- och ELLER-funktionerna, vilka returnerar ett enda logiskt värde och inte en komplett matris med resultat.

Statistiska beräkningar och jämförelser med matrisformler

Matrisformler är också mycket användbara för beräkna medelvärden eller göra jämförelser mellan kompletta intervallEtt klassiskt exempel innebär att beräkna medelvärdet av en datamängd exklusive nollor. Om ditt intervall heter Försäljning, formeln =MEDEL(OM(Försäljning<>0;Försäljning)) Den skapar en array med endast värden som inte är noll och skickar dem till AVERAGE, och utelämnar därmed poster som inte ger information.

Ett annat intressant fall är att jämföra två områden av samma storlek, till exempel MinaData och DinaData. Om du vill veta hur många celler som skiljer sig mellan dem kan du använda =SUMMA(OM(MinaData=DinaData;0;1))Denna formel genererar en array med 0:or när värdena matchar och 1:or när de skiljer sig åt, och summerar sedan resultaten. Om de två områdena är identiska returnerar formeln 0.

Denna jämförelse kan ytterligare förenklas med =SUMMA(1*(MinaData<>DinaData)), och genom att göra den logiska jämförelsen (MyData<>YourData) genereras SANT eller FALSKT, vilka sedan omvandlas till 1 och 0 genom att multiplicera med 1. Återigen är nyckeln att dra nytta av beteendet hos booleska uttryck inom arrayer.

Du kan också använda matrisformler för att hitta det maximala värdet i ett område och få dess position. Om du har ett område med namnet Data är ett alternativ =MIN(OM(Data=MAX(Data);RAD(Data);»»))Denna formel skapar en array där endast de celler vars värde är lika med maxvärdet innehåller dess radnummer; resten konverteras till en tom sträng. MIN returnerar sedan det minsta radnumret bland dessa kandidater, det vill säga den första förekomsten av maxvärdet.

Om du istället för raden vill ha den fullständiga cellreferensen kan du kombinera ADRESS med logiken ovan med hjälp av något i stil med =ADRESS(MIN(OM(Data=MAX(Data);RAD(Data);»»));KOLUMN(Data))så att du får en referens som "$B$7" som identifierar exakt var maxvärdet ligger i intervallet.

Praktiskt exempel: försäljning per produkt med hjälp av matrisformler

För att bättre förstå potentialen hos matrisformler, föreställ dig en liten tabell för fordonsförsäljning med kolumner för säljare, fordonstyp, sålda enheter, enhetspris och total försäljningAntag att kolumnerna C och D innehåller respektive antal sålda och enhetspriset.

Om du kopierar den här tabellen till Excel kan du markera området E2:E11 och ange en formel som =C2:C11*D2:D11I äldre versioner skulle du ha varit tvungen att bekräfta formeln med Ctrl+Shift+Enter för att konvertera den till en klassisk array som skulle returnera multiplikationen rad för rad i bulk. Excel skulle sedan beräkna den totala försäljningen för varje rad genom att multiplicera enheter med enhetspriset.

Den viktigaste detaljen är att du alltid bör välja alla celler som kommer att innehålla resultaten innan du skriver matrisformeln. Om du inte gör det kan bara vissa av värdena beräknas, eller så kan du behöva upprepa processen flera gånger, vilket är ineffektivt.

I detta sammanhang är det också vanligt att använda en encellig matrisformel för att få den totala summan av all försäljning. Du kan till exempel gå till cell B13 och ange =SUMMA(C2:C11*D2:D11)När du bekräftar formeln (i den klassiska modellen med Ctrl+Shift+Enter) multiplicerar Excel varje värdepar i C och D och summerar alla produkter. returnera ett enda aggregerat värde.

Även om dessa exempel bygger på det ärvda beteendet hos CSE-arrayer, är de underliggande idéerna desamma som de som används med nuvarande dynamiska arrayer: att utföra operationer på hela områden samtidigt och returnera flera eller aggregerade resultat utan behov av mellanliggande hjälpformler i varje rad.

Idag kan många av dessa uppgifter lösas genom att kombinera dynamiska funktioner och overflow, vilket skapar kalkylblad. renare, lättare att läsa och enklare att underhållasärskilt när datamängden växer eller när flera användare arbetar med samma bok.

Att behärska användningen av dynamiska matrisformler i Excel förändrar helt hur du bygger kalkylblad: genom att förstå hur overflow fungerar, hur utdataområden beter sig, hur de skiljer sig från gamla CSE-formler och hur man tillämpar praktiska exempel för att ignorera fel, utföra villkorliga summor eller jämföra områden, får du mycket mer flexibla, automatiserade och tillförlitliga modeller, vilket sparar tid med varje uppdatering och minimerar manuella fel.