Formler med arrayer i Excel: en komplett guide och praktiska exempel

den formler med arrayer i Excel De är ett av de där verktygen som verkar som "svart magi" första gången man ser dem, men när man väl bemästrar dem låter de en göra i en enda formel det som tidigare krävde dussintals eller hundratals hjälpceller.

Även om de inledningsvis kan kräva lite respekt, matriser och matrisformler De är en av Excels kraftfullaste resurser för dataanalys, modellering, ekonomi, teknik eller helt enkelt för att göra dina kalkylblad mycket renare och snabbare.

Vad är en array och vad är en arrayformel i Excel?

I Excel är en matris helt enkelt en samling av värden som behandlas som en mängd: de kan finnas i en enda rad, i en enda kolumn eller bilda ett block av flera rader och kolumner.

Till exempel kan en mycket typisk matris innehålla årets månader skrivna under varandra eller bredvid varandra, och Excel skulle arbeta med den gruppen som om det vore ett enda dataobjekt.

en matrisformel Det är en formel som, istället för att arbeta med ett enda värde, arbetar samtidigt med en komplett matris av element: den kan utföra flera beräkningar samtidigt och returnera ett enda resultat eller en matris av resultat.

Huvudtanken är att en matrisformel gör att Excel bearbeta många artiklar i bulkinternt utvärdera alla värden och, om nödvändigt, även returnera flera resultat samtidigt i olika celler.

Tänk dig till exempel att du har antalet sålda enheter i kolumn B och priset för varje enhet i kolumn C. Med en matrisformel som =SUMMA(B2:B11*C2:C11)Excel multiplicerar varje rad (enheter med pris) och summerar sedan alla dessa produkter, utan behov av mellanliggande kolumner.

Hur man matar in och känner igen en klassisk matrisformel

Traditionella matrisformler i Excel matas in med en speciell tangentkombination: Ctrl + Skift + Enter (Ctrl + Shift + Enter). Det räcker inte att bara trycka på Enter.

När du skriver en matrisformel och bekräftar med den kombinationen visar Excel formeln i formelfältet omgivet av nycklarDessa nycklar skrivs inte in för hand: Excel lägger till dem automatiskt när det upptäcker att det är en matrisformel.

Om du försöker skriva in klammerparenteserna själv, kommer Excel inte att behandla dem som en matrisformelmen som en vanlig formel är det obligatoriskt att använda tangentkombinationen för att den ska fungera.

Varje gång du redigerar en matrisformel försvinner klammerparenteserna tillfälligt: ​​du måste trycka på [lämplig tangent] igen. Ctrl + Skift + Enter När redigeringen är klar kommer formeln inte längre att vara matrisbaserad och kommer endast att beräknas på det första elementet i intervallet.

Och en viktig detalj: om du glömmer att använda kombinationen och bara trycker på Enter, kommer formeln att bete sig som en standardformelAtt bara ta det första värdet från varje intervall kan leda till felaktiga resultat utan att du inser det.

Typer av matrisformler: ett resultat eller flera resultat

Vi kan urskilja två huvudtyper av formler med arrayer i Excel: de som returnerar ett enda värde och de som returnerar en uppsättning resultat utspridda över flera celler.

I det första fallet tar formeln en datamatris, utför beräkningarna och returnerar en bara ett resultat i en cell (till exempel en summa, ett medelvärde, ett antal, ett minimum eller ett maximum).

I den andra typen genererar själva formeln en utgångsmatris som upptar två eller fler celler. I det här fallet är alla resultat en del av en enda matrisformel som "finns" i flera celler samtidigt.

funktioner som Suma, GENOMSNITT, MAX o MIN (och dess varianter, MINIFS och MAXIFS) kan arbeta med arrayer om de anges som arrayformler i en enda cell, medan andra, som till exempel TRANSPONERA, TREND o FREKVENSDe är utformade för att returnera matriser med flera celler.

Det kraftfulla med allt detta är att en enda formel kan ersätta många hjälpkolumner, underhålla ditt renare, lättare blad och med mindre risk för kopierings- eller uppdateringsfel.

Avancerade exempel på klassiska matrisformler

Matrisformler låter oss lösa situationer som skulle vara besvärliga eller helt enkelt omöjliga med standardformler. Flera exempel listas nedan. exempel typiska och avancerade intervallbaserade med namn som Data, Försäljning, MinaData eller DinaData.

Summaintervall som innehåller fel

När ett intervall innehåller fel som # N / AEn normal summa med SUM kommer att misslyckas. Med en arrayformel kan du ignorera dessa fel och bara summera de giltiga värdena:

=SUMMA(OM(ÄRFEL(Data),"",Data))

Funktionen UTFÄRDAREFEL Upptäcker celler med fel inom dataområdet och funktionen SI Skapa en ny array där du, istället för fel, placerar tomma strängar. "", och där celler utan fel behåller sitt ursprungliga värde.

På den rena matrisen, funktionen Suma Den beräknar totalsumman utan att ignorera tomma element, så att du kan få summan även om det ursprungliga området har defekta celler.

Räkna hur många fel det finns i ett intervall

Om det du behöver är räknar misstagen Istället för att lägga ihop dem kan du använda en liknande variant:

=SUMMA(OM(ÄR FEL(Data);1;0))

Denna formel skapar en matris där varje cell med ett fel omvandlas till en 1 och varje cell utan fel i en 0så att summan av alla dessa ettor och nollor ger dig det totala antalet fel.

Formeln kan förenkla Tar bort det tredje argumentet för OM, eftersom villkoret returnerar FALSKT när det är falskt, och SUMMA tolkar FALSKT som 0:

=SUMMA(OM(ÄR FEL(Data);1;0))

Och det är fortfarande möjligt att förkorta det ytterligare genom att direkt multiplicera det booleska resultatet med 1, och utnyttja det faktum att SANT*1=1 y FALSKT*1=0:

=SUMMA(OM(ÄR FEL(Data)*1))

Addera värden som uppfyller villkor (OCH och ELLER "för hand")

Med matrisformler kan du bara summera de värden som uppfyller vissa villkor utan att alltid behöva använda SUMIFS. Till exempel, för att bara summera värdena positiv av försäljningssortimentet:

=SUMMA(OM(Försäljning>0;Försäljning))

Här är funktionen SI Den genererar en array där celler med ett värde större än 0 behåller sina värden och de andra blir FALSKT; funktionen Suma Ignorera de FALSKA talen och lägg endast till de positiva talen.

Du kan också kombinera flera villkor med hjälp av multiplikation (motsvarande en Och logiskt) eller summor (motsvarande en Eller logiskt). Till exempel, för att addera värden större än 0 och mindre än eller lika med 5:

=SUMMA((Försäljning>0)*(Försäljning<=5)*(Försäljning))

I det här fallet returnerar logiska uttryck TRUE/FALSE-matriser, vilka när de multipliceras blir 1 eller 0 och fungerar som Filtros på försäljningsvärden.

Om det du behöver är beteende av typ O kan du använda summan av logiska villkor inom SI, som i denna formel som adderar värden mindre än 5 eller större än 15:

=SUMMA(OM((Försäljning<5)+(Försäljning>15);Försäljning))

funktioner Y y O De returnerar ett enda SANT eller FALSKT värde, så de används inte direkt med flera arrayer; lösningen är att emulera dem med multiplikationer och additioner som i de föregående exemplen.

Beräkna ett medelvärde exklusive nollor

Om du vill få en genomsnitt utan att ta hänsyn till nollorDu kan kombinera AVERAGE med ett arrayvillkor i Sales-intervallet:

=MEDEL(OM(Försäljning<>0;Försäljning))

Den resulterande SI-matrisen innehåller endast de värden som inte är 0, vilka är de som slutligen används GENOMSNITT att beräkna medelvärdet.

Räkna skillnader mellan två intervall

Anta att du har två områden av samma storlek och form, kallade MinaData och DinaData, och du vill veta i hur många celler skiljer de sig åtEn matrisformel löser det så här:

=SUMMA(OM(MinaData=DinaData,0,1))

Funktionen OM genererar en array där varje matchning blir en 0 och varje avvikelse en 1. Att summera arrayen ger dig räkna från olika celler.

Det finns också en mer kompakt version här som direkt använder ojämlik jämförelse (<>) multiplicerat med 1:

=SUMMA(1*(MinaData<>DinaData))

Återigen, den trick att SANT är lika med 1 och FALSKT är lika med 0 när det multipliceras med ett tal.

Lokalisera maximum och dess position inom ett intervall.

Med matrisformler kan du inte bara ta reda på vad högsta värde av en uppsättning, men också dess exakta position på arket; du kan också använda RANK funktion att beställa och lokalisera positioner.

Att hitta radnummer För det maximala värdet inom ett intervall i en kolumn med namnet Data kan du använda:

=MIN(OM(Data=MAX(Data);RAD(Data);»»))

Denna formel skapar en array där cellerna som innehåller det maximala värdet lagrar sina radnummer, och resten lämnas som tomma strängar. MIN Hitta det minsta talet i matrisen, som sammanfaller med den första raden där det största talet visas.

Om det du vill är att få cellreferens För maximalt kan du radbryta föregående beräkning med ADRESS och COLUMN:

=ADRESS(MIN(OM(Data=MAX(Data);RAD(Data);""));KOLUMN(Data))

Skapa matrisformler med flera celler steg för steg

I en exempelbok med en säljarbordFordonstyper, sålda enheter och priser kan illustrera hur en matrisformel som upptar flera celler samtidigt fungerar.

Tänk dig att du har kopierat en tabell med början från cell A1 med dessa fält: Säljare, fordonstyp, antal sålda, enhetspris och total försäljning, och att du i kolumn E vill beräkna försäljningen för varje rad med hjälp av en arrayformel.

I intervallet C2:C11 har du de sålda kvantiteterna och i D2:D11 priserna; målet är att fylla E2:E11 med produkten av varje rad utan att skriva formlerna en efter en.

För att göra detta som en matrisformel över flera celler, markera först hela området E2: E11, skriv i formelfältet:

=C2:C11*D2:D11

och bekräfta med Ctrl + Shift + EnterExcel fyller alla celler från E2 till E11 samtidigt med resultatet som motsvarar varje rad.

Formel för en enda cell i samma exempel

Med utgångspunkt från samma tabell är det möjligt att få fram total försäljning med hjälp av en enda matrisformel i en enda cell, till exempel B13.

Istället för att skriva formler rad för rad, ange helt enkelt följande i B13:

=SUMMA(C2:C11*D2:D11)

och bekräfta med Ctrl + Shift + EnterExcel utför internt multiplikationen av varje cellpar Cx*Dx och adderar sedan alla dessa produkter för att få summan.

Hur man felsöker och förstår en komplex matrisformel

När en formel är lång och lite "kryptisk" är det viktigt att kunna se vad varje del beräknarI Excel kan du utvärdera fragment av en formel med hjälp av F9-tangenten.

Tricket är att välja en specifik del i formelfältet (till exempel bara B2:B11*C2:C11), och tryck sedan på F9 så att Excel tillfälligt ersätter det fragmentet med mellanresultatet.

Genom att göra detta i en arrayformel ser du resulterande matris, med alla dess element, vilket i hög grad hjälper till att förstå formelns beteende och att lokalisera eventuella fel.

När du har inspekterat den valda delen kan du trycka på esc För att avsluta utan att spara ändringarna, eller Ctrl + Z om du av misstag har bekräftat och vill ångra utvärderingen.

Konstanta arrayer i Excel: hur man skapar och använder dem

Förutom att använda cellområden kan du i Excel arbeta med matriskonstanter, vilka är uppsättningar av fasta värden skrivna direkt i en formel och som inte ändras när formeln kopieras eller flyttas.

En matriskonstant kan innehålla siffror, texter, logiska värden (SANT/FALSKT) eller felmen den kan inte inkludera cellreferenser, definierade namn, datum, funktioner eller andra matriser.

Det finns horisontella endimensionella konstanter (en enda rad), vertikala endimensionella konstanter (en enda kolumn och tvådimensionell (ett block av rader och kolumner), och skiljer sig åt genom de avgränsare som används mellan elementen.

I spanska regionala miljöer är vertikala matriser vanligtvis separerade med semikolon (;), medan i horisontella arrayer kan en annan avgränsare användas (i vissa konfigurationer, omvänt snedstreck) eller kommatecknet, beroende på systemkonfigurationen.

Till exempel, a vertikal matris med årets månader kan det uttryckas som:

={"januari";"februari";"mars";"april";"maj";"juni";"juli";"augusti";"september";"oktober";"november";"december"}

Tilldela ett namn till en arraykonstant

För att göra det mer hanterbart att använda en stor konstant kan du ge det ett namn Med Excels namnhanterare kan du återanvända det utan att behöva skriva det igen.

Processen är enkel: du går till fliken Formler, använder alternativet för att Definierat namn eller Tilldela namnDu skriver önskat namn och i rutan "Refererar till" anger du matriskonstanten direkt.

Du kan till exempel skapa ett namn som heter Månader som peka på konstanten:

={"januari"\"februari"\"mars"\"april"\"maj"\"juni"\"juli"\"augusti"\"september"\"oktober"\"november"\"december"}

Markera sedan helt enkelt så många celler som det finns element i matrisen och skriv namnet =Månader och bekräfta som en arrayformel så att alla värden fördelade över arket visas.

Om konstanten orsakar problem är det en bra idé att kontrollera. de använda separatorerna och att ett lämpligt område för matrisens storlek har valts innan formeln matas in med Ctrl + Shift + Enter.

Exempel på användning av matriskonstanter

Konstanter låter dig bygga kraftfulla formler på väldigt lite utrymme. Till exempel, för att lägga till de tre högsta värdena i ett intervall Du kan använda en kombination med LARGE och en konstant som definierar önskade ordningsföljder.

På liknande sätt är det möjligt att summera de N minsta värdena med K.ESIMO.MINOR, helt enkelt ändra funktionen men behålla arrayen av positioner du vill summera.

Ett annat typiskt fall är att räkna hur många gånger en utvärderare (till exempel Pedro) har gett ett betyg med flera specifika värden utan att behöva upprepa kriterier i ANTAL.OMF om och om igen.

I ett utbud av bedömningar Du kan använda en formel som denna:

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))

Här, konstanten {3\4\5} Den samlar in de accepterade poängen (3, 4 och 5) och gör formeln mer kompakt och lättare att underhålla, även om du kan utöka den med fler värden om det behövs, alltid med respekt för den maximala teckengränsen för en formel.

Dynamiska matrisformler i Excel 365 och 2021

Med moderna versioner av Excel (Microsoft 365 och Excel 2021) infördes en mycket viktig förändring: dynamiska matrisformlervilket eliminerar behovet av att använda Ctrl + Shift + Enter i de flesta fall.

Dessa nya formler fungerar direkt med intervall och matriser och de har möjlighet att automatiskt "överflöda" till angränsande celler och uppta så många rader och kolumner som behövs för att visa alla resultat.

Den största skillnaden är att du helt enkelt skriver formeln i en cell och trycker på Enter normalt; Excel fyller i det utdataområde som behövs och markerar det med en speciell kantlinje som anger att det är en överflödesområde.

Dessutom har nya funktioner dykt upp som är specifikt utformade för att fungera med dynamiska arrayer, såsom SCREENING, BESTÄLLA, UNIK, SEKVENS, SORTERA EFTER o Slumpmässig matris, Bland annat.

Med dessa funktioner kan du filtrera, sortera, generera sekventiella listor eller slumptal, och returnera resultatmängder utan att behöva definiera storleken på destinationsintervallet i förväg.

Viktiga skillnader mellan klassiska och dynamiska matrisformler

Klassiska matrisformler kräver Ctrl + Shift + EnterDe kan vara något svårare att läsa och har i många fall begränsad förmåga att automatiskt producera resultat, till skillnad från moderna funktioner som VLOOKUP och XLOOKUP.

Dynamiska matrisformler skrivs som normala formlerDe bekräftas helt enkelt genom att trycka på Enter och visas sedan själva resultaten, utan att välja tidigare områden eller använda ärvda matrisformler.

En annan viktig skillnad är att dynamiska funktioner returnera matriser explicit och förväntas som sådana; om en gammal bok använde en funktion som returnerade en array till flera celler, skulle Excel kunna tillämpa en tyst implicit skärningspunkt.

Med dynamiska arrayer markerar Excel de gamla fallen med operatorn @, vilket indikerar var den implicita skärningspunkten inträffade, för att bevara det tidigare beteendet och undvika oväntade resultat.

Det är också värt att notera att dynamiska matrisformler endast är tillgängliga i Excel 365 och Excel 2021I tidigare versioner fungerar de inte och kan visas som äldre matrisformler om dessa arbetsböcker öppnas på datorer utan stöd för dynamiska matriser.

Konfigurera och använda dynamiska matrisformler

För att använda en dynamisk formel, välj helt enkelt startcellSkriv formeln med en funktion som FILTER eller SORT och tryck på Enter. Excel sprider automatiskt resultaten nedåt och åt höger.

Det är viktigt att se till att det finns ledigt utrymme runt omkring från startcellen, eftersom om cellerna där arrayen ska dumpas redan innehåller data, kommer Excel att visa ett spillfel (#ÖVERFLOW eller liknande).

När formeln har skapats träder överflödesintervallet i kraft. som ett blockOm du ändrar formeln i huvudcellen uppdateras alla resultat; om du vill ta bort allt tar du helt enkelt bort formeln från den cellen.

Du kan också hänvisa till överflödesområde från andra formler med hjälp av overflow-operatorn (till exempel =SUMMA(F2#)), så att om den växer eller krymper i storlek kommer formlerna som använder den att anpassa sig automatiskt.

I miljöer av programmeringbibliotek som till exempel Aspose.Cells De låter dig ställa in och omberäkna dynamiska matrisformler med kod, med hjälp av specifika metoder för att tilldela dem till en cell och uppdatera dem innan du utför den allmänna formelberäkningen.

Avancerade tillämpningar av matriser: linjär algebra och finans

Att arbeta med arrayer i Excel är inte begränsat till att summera områden eller filtrera värden: det kan också användas för problem som involverar linjär algebra och optimering, såsom matrisinversion, lösning av ekvationssystem eller byggande av investeringsportföljer.

En kvadratmatris A kan inverteras i Excel med hjälp av funktionen MINVERSA, vilket kräver en matrisformel (eller dynamisk, beroende på version) över ett intervall av samma storlek som den ursprungliga matrisen.

För att få inversen av en 3×3-matris i B3:D5, skulle du välja ett tomt 3×3-block och skriva =MINVERS(B3:D5) och du skulle bekräfta den som en matrisformel, och därmed få matris A^-1.

Om du multiplicerar den ursprungliga matrisen med dess invers med hjälp av MMULT över ett lämpligt intervall får du en identitetsmatris, analogt med att multiplicera ett tal med dess invers, vilket alltid ger 1.

På liknande sätt kan du ställa in ett system av linjära ekvationer i matrisform, med A som koefficientmatris, K som vektor för okända och P som vektor för oberoende termer, och lösa det med hjälp av relationen K = A^-1 · P med hjälp av MINVERSA och MMULT.

Inom finans tillämpas denna matrismetod till exempel på problem med portföljoptimering, där kovariansmatrisen σ används_ij mellan värdepapper, den förväntade avkastningen μ_j och lönsamhetsbegränsningar för att hitta den tillgångsmix som har lägst risk för ett specifikt lönsamhetsmål.

Utgående från Lagrangefunktionen och härledningen av första ordningens villkor kommer vi återigen fram till ett matrissystem av typen A·X = P, vars lösning X = A^-1·P ger oss optimala vikter av varje tillgång i portföljen.

I ett exempel med tre aktier A, B och C, med givna kovarianser och förväntad avkastning, kan en investeringsvektor bestämmas så att portföljen får en förväntad avkastning på 4 % med minsta variansvilket resulterar i en kombination som utnyttjar diversifiering för att minska risken jämfört med att investera i ett enda värdepapper.

Allt detta implementeras med samma grundläggande verktyg: MINVERSA-, MMULT- och matrisformlerförstärker idén att Excel kan vara en mycket kompetent plattform för numerisk analys när man behärskar användningen av matriser.

Efter att ha täckt allt från grunderna i klassiska matrisformler, via matriskonstanter och dynamiska matriser, till avancerade användningsområden inom linjär algebra och finans, blir det ganska tydligt att Lär dig att hantera matriser effektivt i Excel Det är en investering som gör att du kan arbeta renare, snabbare och med lösningar som ofta är utom räckhåll för konventionella formler.