En Excel matrix formel er en formel, der udfører beregninger af værdierne i en eller flere arrays i stedet for en enkelt dataværdi. I regnearksprogrammer er et array en række eller en række relaterede dataværdier, der normalt gemmes i tilstødende celler i et regneark. Denne vejledning forklarer, hvordan du bruger array formler i alle moderne versioner af Excel.
Denne vejledning er kompatibel med de fleste versioner af Excel, herunder 2007, 2010, 2013, 2016 og Office 365.
Hvad er Array Formler?
Array formler ligner regelmæssige formler:
- De begynder med et lighedstegn ( = )
- Brug den samme syntaks som almindelige formler
- Brug de samme matematiske operatører
- Følg samme rækkefølge
I Excel er matrixformler omgivet af krøllede seler { } - disse seler kan ikke bare indtastes de skal tilføjes til en formel ved at trykke på Ctrl , Flytte , og Gå ind nøgler efter indtastning af formlen i en celle eller celler. Af denne grund henvises en matrixformel nogle gange til a CSE formel i Excel.
En undtagelse til denne regel er, når krølletbøjler bruges til at indtaste en array som et argument for en funktion, der normalt kun indeholder en enkelt værdi eller cellehenvisning. For eksempel i undervisningen nedenfor, der bruger LOPSLAG og VÆLGE funktion til at oprette en venstre lookup formel, er et array oprettet til VÆLGE funktionens indeksnr argument ved at skrive bøjlerne omkring det indtastede array.
Skridt til at oprette en array formel
- Indtast formlen.
- Hold nede i Ctrl og Flytte nøgler på tastaturet.
- Tryk og slip på Gå ind nøgle til at oprette array formel.
- Slip afCtrl og Flytte nøgler.
Hvis det gøres korrekt, vil formlen blive omgivet af krøllede seler, og hver celle, der holder formlen, vil indeholde et andet resultat.
Redigering af en array-formel
Hver gang en matrixformel er redigeret, forsvinder de krøllede seler fra omkring arrayformlen. For at få dem tilbage skal arrayformlen indtastes ved at trykke på Ctrl, Flytte, og Gå ind nøgler igen, ligesom når matrixformlen først blev oprettet.
Typer af arrayformler
Der er to hovedtyper af matrixformler:
- Enkeltcelleopstillingsformler, som udfører flere beregninger i et enkelt arbejdsark.
- Multicelle matrixformler, der er placeret i mere end et regneark celle.
Single Cell Array Formulas
Denne type array formel bruger en funktion, f.eks SUM, GENNEMSNIT, eller TÆLLE, at kombinere output fra en multicell array-formel til en enkelt værdi i en enkelt celle. Et eksempel på en enkelt celle array formel ville være:
{= SUM (A1: A2 * B1: B2)}
Denne formel tilføjer sammen produkt af A1 * B1 og A2 * B2, og så returnerer det et enkelt resultat i en enkelt celle i regnearket. En anden måde at skrive ovenstående formel på er:
Multicelle Array Formler
Som navnet antyder, er disse matrixformler placeret i flere regnearkceller, og de returnerer en matrix som et svar. Med andre ord er den samme formel placeret i to eller flere celler, og den returnerer forskellige svar i hver celle.
Hver kopi, eller eksempelvis af arrayformlen udfører den samme beregning i hver celle den befinder sig i, men hver forekomst af formlen bruger forskellige data i sine beregninger. Derfor producerer hver instans forskellige resultater. Et eksempel på en multipel celle array formel ville se ud:
{= A1: A2 * B1: B2}
Hvis ovenstående eksempel er placeret i celler C1 og C2 i et regneark, så resultaterne ville være som følger:
- Dataene i A1 multipliceres med dataene i B1, og resultaterne gemmes i celle C1.
- Dataene i A2 multipliceres med dataene i B2, og resultaterne gemmes i celle C2.
Brug af arrays til transponering af rækker og kolonner
Det TRANSPOSE funktion bruges til at kopiere data fra en række til en kolonne eller omvendt. Denne funktion er et af de få i Excel, som altid skal bruges som en matrixformel.
Array formler og Excel funktioner
Mange af Excel's indbyggede funktioner, f.eks SUM, GENNEMSNIT, og TÆLLE, kan også bruges i en array formel. Der er også nogle få funktioner, som f.eks TRANSPOSE funktion, der altid skal indtastes som et array for at det skal fungere korrekt. Nyttigheden af mange funktioner som f.eks INDEKS og MATCH eller MAX og HVIS kan udvides ved at bruge dem sammen i en matrixformel.
Eksempler på Array Formler i Excel:
01 af 08Opret en enkel Single Cell Array Formula
Enkeltcelleopstillingsformler udfører normalt først en multi-celleberegning og bruger derefter en funktion som f.eks GENNEMSNIT eller SUM at kombinere output fra arrayet til et enkelt resultat.
02 af 08Ignorer fejlværdier, når du finder data
Denne array formel bruger GENNEMSNIT, HVIS, og ISNUMBER Fungerer for at finde gennemsnitsværdien for de eksisterende data, mens du ignorerer fejlværdier som f.eks # DIV / 0! og #NAVN?
03 af 08Tæller celler af data
Brug SUM ogHVIS funktioner i en array formel til at tælle celler af data, der opfylder en af flere betingelser; Denne teknik adskiller sig fra at bruge Excel TÆL.HVISER funktion, som kræver, at alle indstillede betingelser skal opfyldes, før cellen tælles.
04 af 08Find det største positive eller negative tal
Dette eksempel kombinerer MAX funktion ogHVIS funktion i en array formel, der finder den største eller maksimale værdi for en række data, når specifikke kriterier er opfyldt.
05 af 08Find det mindste positive eller negative tal
I lighed med eksemplet ovenfor kombinerer denne en MIN funktion og IF funktion i en array formel for at finde den mindste eller minimale værdi for en række data, når specifikke kriterier er opfyldt.
06 af 08Find middel- eller medianværdien
Det MEDIAN funktion i Excel finder mellemværdien for en liste over data. Ved at kombinere det med HVIS funktion i en array formel, kan middelværdien for forskellige grupper af relaterede data findes.
07 af 08Lav en opslagsformel med flere kriterier
Denne array formel indebærer nesting af MATCH og INDEKS funktioner til at finde specifikke oplysninger i en database.
08 af 08Lav en Venstre Opslag Formel
Det LOPSLAG Funktionen søger normalt kun efter data i kolonner til højre, men ved at kombinere det med VÆLGE funktion, kan du oprette en venstre opslag formel, der vil søge kolonner af data til venstre for opslagsværdi argument.
