Retningslinjer for og eksempler på matrixformler

I denne øvelse kan du se, hvordan du kan bruge matrixformler i en eller flere celler til at beregne nogle salgstal. På de første trin bruges en formel med flere celler til at beregne et sæt subtotaler. På de næste trin bruges en formel med én celle til at beregne en hovedtotal.

• Matrixformel med flere celler

Kopiér hele tabellen herunder, og indsæt den i celle A1 i et tomt regneark.

Salg Person	Bil Type	Antal Solgte	Enheds- pris	Samlet Salg
Pedersen	Sedan	5	33000
	Coupé	4	37000
Ingle	Sedan	6	24000
	Coupé	8	21000
Jordan	Sedan	3	29000
	Coupé	1	31000
Pica	Sedan	9	24000
	Coupé	5	37000
Sanchez	Sedan	6	33000
	Coupé	8	31000
Formel (hovedtotal)			Hovedtotal
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. Hvis du vil se det samlede Salg af coupéer og sedans for hver sælger, skal du markere cellerne E2:E11, indtaste formlen =C2:C11*D2:D11 og derefter trykke på Ctrl+Skift+Enter.

2. Hvis du vil se hovedtotalen for alle salg, skal du markere celle F11, skrive formlen =SUM(C2:C11*D2:D11) og trykke på Ctrl+Skift+Enter.

Når du trykker på Ctrl+Skift+Enter, omgiver Excel formlen med klammeparenteser ({ }) og indsætter en forekomst af formlen i hver celle i det markerede område. Dette sker meget hurtigt, så det, som du ser i kolonne E, er det samlede salgsbeløb for hver biltype for hver sælger. Hvis du vælger E2 og derefter vælger E3, E4 osv., vil du se, at den samme formel vises: {=C2:C11*D2:D11}. 

• Opret en matrixformel med én celle

Skriv følgende formel i celle D13 i projektmappen, og tryk derefter på Ctrl+Skift+Enter:

=SUM(C2:C11*D2:D11)

I dette tilfælde, multiplicerer Excel værdierne i matrixen (celleområdet C2 til D11) og bruger derefter funktionen SUMtil at lægge totalerne sammen. Resultatet er et samlet beløb på $1.590.000 i salg. Dette eksempel viser, hvor effektiv denne type formel kan være. Antag f.eks., at du har 1.000 rækker med data. Du kan summere en del af eller alle disse data ved at oprette en matrixformel i en enkelt celle i stedet for at trække formlen ned gennem de 1.000 rækker.

Bemærk også, at formlen med én celle i celle D13 er helt uafhængig af formlen med flere celler (formlen i cellerne E2 til og med E11). Dette er en anden fordel ved at bruge matrixformler – fleksibilitet. Du kan ændre formlerne i kolonne E eller slette kolonnen helt, uden at det påvirker formlen i D13.

Matrixformler har også følgende fordele:

• Konsekvens    Hvis du klikker på en af cellerne fra E2 og nedad, vises den samme formel. Denne konsistent kan sikre en større nøjagtighed.

• Sikkerhed    You cannot overwrite a component of a multi-cell array formula. Klik f.eks. på celle E3 og tryk Slet. You have to either select the entire range of cells (E2 through E11) and change the formula for the entire array, or leave the array as is. Som en sikkerhedsforanstaltning skal du trykke på Ctrl+Skift+Enter for at bekræfte enhver ændring af formlen.

• Mindre filstørrelser    You can often use a single array formula instead of several intermediate formulas. I projektmappen bruges der f.eks. kun én matrixformel til at beregne resultaterne i kolonne E. Hvis du havde brugt standardformler (f.eks. =C2*D2, C3*D3, C4*D4…), skulle du have brugt 11 forskellige formler til at beregne de samme resultater.

Matrixformler bruger overordnet set den samme syntaks som standardformler. De starter med et lighedstegn (=), og du kan bruge alle de indbyggede Excel-funktioner i dine matrixformler. Den primære forskel, når du bruger matrixformler, er, at du skal trykke på Ctrl+Skift+Enter for at angive en formel. Når du gør det, angives matrixformlen i klammeparenteser – hvis du skriver klammeparenteserne manuelt, bliver formlen konverteret til en tekststreng, og den virker ikke.

Matrixfunktioner kan være en effektiv måde at bygge komplekse formler på. Matrixformlen =SUM(C2:C11*D2:D11) er den samme som: =SUM(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Når du arbejder med formler i flere celler, skal du også huske følgende:

• Markér det celleområde, der skal indeholde resultaterne, før du skrive formlen. Du gjorde dette, da du oprettede matrixformlen med flere celler, da du markerede cellerne E2 til og med E11.

• Du kan ikke ændre indholdet af en enkelt celle i en matrixformel. Du kan prøve det ved at markere celle E3 i projektmappen og trykke på Delete. Der vises en meddelelse i Excel om, at du ikke kan ændre en del af en matrix

• Du kan flytte eller slette en hel matrixformel, men du kan ikke flytte eller slette en del af den. Hvis du vil gøre en matrixformel mindre, skal du først slette den eksisterende formel og derefter starte forfra.

• Hvis du vil slette en matrixformel, skal du markere hele formelområdet (f.eks. E2:E11) og derefter trykke på Slet.

• Du kan ikke indsætte tomme celler i eller slette celler fra en matrixformel med flere celler.

Der vil være tilfælde, hvor du har brug for at udvide en matrixformel. Markér den første celle i et eksisterende matrixområde og fortsæt, indtil du har markeret hele det område, du vil udvide formlen til. Tryk F2 for at redigere formlen, og tryk derefter på CTRL+SKIFT+ENTER for at bekræfte formlen, når du har justeret formelområdet. Nøglen er at markere hele området, startende med cellen øverst til venstre i matrixen. Cellen øverst til venstre er den, der bliver redigeret.

Matrixformler er fantastiske, men der er også ulemper:

• Du kan nemt komme til at glemme at trykke på Ctrl+Skift+Enter. Det kan ske for selv de mest erfarne Excel-brugere. Husk at bruge denne tastekombination, når du opretter eller redigerer en matrixformel.

• Andre brugere af projektmappen forstår muligvis ikke dine formler. Matrixformler er generelt ikke forklaret i regneark. Hvis andre skal redigere dine projektmapper, bør du derfor enten undgå at bruge matrixformler, eller du skal sikre dig, at de kender til matrixformler og ved, hvordan de kan ændre dem, hvis de har brug for det.

• Afhængigt af computeren hastighed og hukommelse kan store matrixformler gøre beregningerne langsommere.

Matrixkonstanter er en komponent af matrixformler. Du kan oprette matrixkonstanter ved at oprette en liste med elementer, som du derefter manuelt angiver i klammeparenteser ({ }), f.eks.:

={1\2\3\4\5}

Nu ved du, at du skal trykke på Ctrl+Skift+Enter, når du opretter matrixformler. Da matrixkonstanter er en komponent af matrixformler, skal du manuelt skrive klammeparenteserne om konstanterne. Du trykker derefter på Ctrl+Skift+Enter, når du vil oprette hele formlen.

Hvis du afgrænser elementerne ved hjælp af kommaer, oprettes der en vandret matrix (en række). Hvis du afgrænser elementerne med semikolonner, opretter du en lodret matrix (en kolonne). Hvis du vil oprette en todimensional matrix, skal du afgrænse elementerne i hver række med kommaer og afgrænse hver række med semikoloner.

Her er en matrix i en enkelt række: {1\2\3\4}. Her er en matrix i en enkelt kolonne: {1;2;3;4}. Og her er en matrix med to rækker og fire kolonner: {1\2\3\4;5\6\7\8}. I matrixen med to rækker er den første række 1, 2, 3 og 4, og den anden række er 5, 6, 7 og 8. Et enkelt komma afgrænser de to rækker mellem 4 og 5.

På samme måde som med matrixformler kan du bruge matrixkonstanter med de fleste af de indbyggede funktioner i Excel. I de følgende afsnit beskrives det, hvordan du kan oprette hver enkelt type kontakt, og hvordan du bruger disse konstanter i funktioner i Excel.

Med de følgende procedurer får du øvelse i at oprette vandrette, lodrette og todimensionale konstanter.

Oprette en vandret konstant

1. Markér cellerne A1 til og med E1 i et tomt regneark.

2. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

   ={1\2\3\4\5}

   I dette tilfælde skal du skrive start- og slutklammeparenteserne ({ }), og Excel tilføjer derefter det andet sæt for dig.

   Følgende resultat vises.

   

Oprette en lodret konstant

1. Markér en kolonne med fem celler i projektmappen.

2. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

   ={1;2;3;4;5}

   Følgende resultat vises:

   

Oprette en todimensional konstant

1. Markér en blok celler i projektmappen på fire kolonner i bredden og tre rækker i højden.

2. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}

   Der vises følgende resultat:

   

Brug af konstanter i formler

Her er et simpelt eksempel, hvor der bruges konstanter:

1. Opret et nyt regneark i eksempelprojektmappen.

2. Skriv 3 i celle A1, og skriv derefter 4 i B1, 5 i C1, 6 i D1 og 7 i E1.

3. Skriv følgende formel i celle A3, og tryk derefter på Ctrl+Skift+Enter:

   =SUM(A1:E1*{1\2\3\4\5})

   Bemærk, at Excel angiver konstanten i et ekstra sæt klammeparenteser, fordi du har oprettet den som en matrixformel.

   

   Værdien 85 vises i celle A3.

I næste afsnit beskrives det, hvordan formlen virker.

Den formel, du netop har brugt, består af flere dele.

1. Funktion

2. Lagret matrix

3. Operator

4. Matrixkonstant

Det sidste element i parenteserne er matrixkonstanten: {1\2\3\4\5}. Husk, at Excel ikke omgiver matrixkonstanter med klammeparenteser; så du skriver dem. Husk også, at når du har føjet en konstant til en matrixformel, skal du trykke på Ctrl+Skift+Enter for at indtaste formlen.

Da Excel først udfører handlinger på de udtryk, der er angivet i parentes, vil de næste to elementer, der behandles, være de værdier, der er gemt i projektmappen (A1:E1) og operatoren. På dette tidspunkt ganger formlen værdierne i den lagrede matrix med de tilsvarende værdier i konstanten. Dette svarer til:

=SUM(A1*1;B1*2;C1*3;D1*4;E1*5)

Til sidst lægger funktionen SUM værdierne sammen, og summen 85 vises i celle A3.

Hvis du vil undlade at bruge den lagrede matrix og holde hele behandlingen i hukommelsen, kan du erstatte den lagrede matrix med en anden matrixkonstant:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Hvis du vil prøve dette, kan du kopiere funktionen, markere en tom celle i projektmappen, indsætte en formel på formellinjen og derefter trykke på Ctrl+Skift+Enter. Der vises det samme resultat som i den tidligere øvelse, som benyttede matrixformlen:

=SUM(A1:E1*{1\2\3\4\5})

Array constants can contain numbers, text, logical values (such as TRUE and FALSE), and error values ( such as #N/A). You can use numbers in the integer, decimal, and scientific formats. Hvis du medtager tekst, skal du omslutte den med anførselstegn (").

Matrixkonstanter kan ikke indeholde flere matrixer, formler eller funktioner. Det vil sige, at de kan kun indeholde tekst eller tal, der er adskilt med kommaer eller semikolonner. Excel viser en advarsel, når du skriver en formel som {1\2\A1:D4} eller {1\2\SUM(Q2:Z8)}. Desuden kan numeriske værdier ikke indeholde procenttegn, dollartegn, kommaer eller parenteser.

En af de bedste måder at bruge matrixkonstanter på, er at navngive dem. Det kan være meget nemmere at bruge matrixkonstanter, der er navngivet, og navngivningen kan være med til at skjule kompleksiteten i matrixformler fra andre. Hvis du vil navngive en matrixkonstant og bruge den i en formel, skal du benytte følgende fremgangsmåde:

1. Klik på Definer navn i gruppen Definerede navne under fanen Formler.
   Dialogboksen Definer navn vises.

2. Skriv Kvartal1 i feltet Navn.

3. Skriv følgende konstant (husk at skrive klammeparenteserne manuelt) i feltet Refererer til:

   ={"Januar"\"Februar"\"Marts"}

   Indholdet i dialogboksen ser nu sådan ud:

   

4. Klik på OK, og markér derefter en række med tre tomme celler.

5. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter.

   =Kvartal1

   Følgende resultat vises:

   

Når du bruger en navngivet konstant som en matrixformel, skal du huske at angive lighedstegnet. Hvis du ikke gør det, vil Excel fortolke matrixen som en tekststreng, og formlen vil ikke virke som forventet. Husk desuden på, at du kan bruge kombinationer af tekst og tal.

Hold øje med følgende problemer, hvis dine matrixkonstanter ikke virker:

• Nogle elementer er muligvis ikke separeret med det korrekte tegn. Hvis du udelader et komma eller et semikolon, eller hvis du placerer et af disse tegn forkert, bliver matrixkonstanten muligvis ikke oprettet korrekt, eller der vises en advarsel.

• Du kan have markeret et celleområde, der ikke stemmer overens med antallet af elementer i konstanten. For example, if you select a column of six cells for use with a five-cell constant, the #N/A error value appears in the empty cell. Conversely, if you select too few cells, Excel omits the values that don't have a corresponding cell.

I de følgende eksempler kan du se nogle eksempler på brug af matrixkonstanter i matrixformler. Nogle af eksemplerne bruger funktionen TRANSPONER til at konvertere rækker til kolonner og omvendt.

Multiplicer hvert enkelt element i en matrix

1. Opret et nyt regneark, og markér derefter en blok med tomme celler på fire kolonner i bredden og tre rækker i højden.

2. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*2

Tag kvadratroden af elementerne i en matrix

1. Markér en blok med tomme celler på fire kolonner i bredden og tre rækker i højden.

2. Skriv følgende matrixformel, og tryk derefter på Ctrl+Skift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

   Du kan eventuelt angive denne matrixformel, som bruger indsætningstegnet (^):

   ={1\2\3\4;5\6\7\8;9\10\11\12}^2

Transponere en endimensional række

1. Markér en kolonne med fem tomme celler.

2. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter:

   =TRANSPONER({1\2\3\4\5})

   Selvom du har angivet en vandret matrixkonstant, konverterer funktionen TRANSPONER matrixkonstanten til en kolonne.

Transponere en endimensional kolonne

1. Markér en række med fem tomme celler.

2. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter:

   =TRANSPONER({1;2;3;4;5})

Selvom du har angivet en lodret matrixkonstant, konverterer funktionen TRANSPONER matrixkonstanten til en række.

Transponere en todimensional konstant

1. Markér en blok med celler på tre kolonner i bredden og fire rækker i højden.

2. Skriv følgende konstant, og tryk derefter på Ctrl+Skift+Enter:

   =TRANSPONER({1\2\3\4;5\6\7\8;9\10\11\12})

   Funktionen TRANSPONER konverterer hver række til en række kolonner.

I dette afsnit får du eksempler på grundlæggende matrixformler.

Oprette matrixer og matrixkonstanter fra eksisterende værdier

I det følgende eksempel beskrives det, hvordan du kan bruge matrixformler til at oprette links mellem celleområder i forskellige regneark. Det viser dog også, hvordan du opretter en matrixkonstant fra det samme sæt af værdier.

Oprette en matrix ud fra eksisterende værdier

1. Markér cellerne C8:E10 i et regneark i Excel, og angiv denne formel:

   ={10,20,30;40,50,60;70,80,90}

   Husk at skrive { (startklammeparentes), før du skriver 10, og } (slutklammeparentes), efter at du har skrevet 90, da du opretter en matrix med tal.

2. Tryk på Ctrl+Skift+Enter, hvilket indsætter denne talmatrix i celleområdet C8:E10 ved hjælp af en matrixformel. I regnearket skal cellerne C8 til og med E10 se sådan ud:

   10	20	30
   40	50	60
   70	80	90

3. Markér celleområde C1 til og med E3.

4. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

   =C8:E10

   Der vises en matrix på 3x3 i cellerne C1 til og med E3, som har de samme værdier, som du ser i C8 til og med E10.

Opret en matrixkonstant ud fra eksisterende værdier

1. Markér cellerne C1:C3 og tryk på F2 for at skifte til redigeringstilstand. 

2. Tryk på F9 for at konvertere cellereferencerne til værdier. Excel konverterer værdierne til en matrixkonstant. Formlen bør nu være ={10\20\30;40\50\60;70\80\90}.

3. Tryk på Ctrl+Skift+Enter for at oprette matrixkonstanten som en matrixformel.

Tælle antal tegn i et celleområde

I følgende eksempel kan du se, hvordan du kan tælle antallet af tegn, herunder antal mellemrum, i et celleområde.

1. Kopiér hele denne tabel, og indsæt den i celle A1 i et regneark.

   Data
   Dette er en
   gruppe celler, der
   samles til
   en
   enkelt sætning.
   Samlet antal tegn i A2:A6
   =SUM(LÆNGDE(A2:A6))
   Indholdet af den længste celle (A3)
   =INDEKS(A2:A6;SAMMENLIGN(MAKS(LÆNGDE(A2:A6));LÆNGDE(A2:A6);0);1)

2. Markér celle A8, og tryk derefter på Ctrl+Skift+Enter for at få vist det samlede antal tegn i cellerne A2:A6 (66).

3. Markér celle A10, og tryk derefter på Ctrl+Skift+Enter for at få vist indholdet af de længste af cellerne A2:A6 (celle A3).

Følgende formel bruges i celle A8 og tæller det samlede antal tegn (66) i celle A2 til og med A6.

=SUM(LÆNGDE(A2:A6))

I dette tilfælde returnerer funktionen LÆNGDE længden af hver tekststreng i hver af cellerne i området. Funktionen SUM lægger derefter disse værdier sammen og viser resultatet (66).

Finde den n'te mindste værdi i et område

I dette eksempel kan du se, hvordan du kan finde de tre mindste værdier i et celleområde.

1. Skriv nogle tilfældige tal i cellerne A1:A11.

2. Markér celle C1 til og med celle C3. Dette sæt celler vil indeholde de resultater, der returneres af matrixformlen.

3. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter:

   =MINDSTE(A1:A11,{1;2;3})

Denne formel bruger en matrixkonstant til at evaluere funktionen MINDSTE tre gange og returnerer den mindste (1), næstmindste (2) og tredjemindste (3) værdi i matrixen i cellerne A1:A10 Hvis du vil finde flere værdier, skal du føje flere argumenter til konstanten. Du kan også bruge flere funktioner sammen med denne formel, f.eks. SUM eller MIDDEL. Eksempler:

=SUM(MINDSTE(A1:A10,{1\2\3}))

=MIDDEL(MINDSTE(A1:A10,{1\2\3}))

Finde den n'te største værdi i et område

Hvis du vil finde de største værdier i et område, kan du erstatte funktionen MINDSTE med funktionen STØRSTE. I det følgende eksempel bruges funktioner RÆKKE og INDIREKTE også.

1. Markér celle D1 til og med celle D3.

2. Skriv denne formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

   =STØRSTE(A1:A10,RÆKKE(INDIREKTE("1:3")))

At this point, it may help to know a bit about the ROW and INDIRECT functions. You can use the ROW function to create an array of consecutive integers. Markér f.eks. en tom kolonne med 10 celler i din øveprojektmappe, indtast denne matrixformel, og tryk derefter på Ctrl+Skift+Enter:

=RÆKKE(1:10)

Formlen opretter en kolonne med 10 fortløbende heltal. Hvis du vil se et potentielt problem, skal du indsætte en række over det område, der indeholder matrixformlen (det vil sige over række 1). Excel justerer rækkereferencerne, og formlen genererer nu heltal fra 2 til 11. For at løse dette problem skal du føje funktionen INDIREKTE til formlen:

=RÆKKE(INDIREKTE("1:10"))

Funktionen INDIREKTE bruger tekststrenge som argumenter (hvilket er grunden til, at intervallet 1:10 er angivet i anførselstegn). Excel justerer ikke tekstværdier, når du indsætter rækker eller flytter matrixformlen. Det medfører, at funktionen RÆKKE altid opretter det ønskede interval af heltal.

Lad os se nærmere på den formel, du brugte tidligere – =STØRSTE(A5:A14,RÆKKE(INDIREKTE("1:3"))) – startende fra de indre parenteser og udad: Funktionen INDIREKTE returnerer et sæt tekstværdier, i dette tilfælde værdierne 1 til 3. Funktionen RÆKKE generer til gengæld en kolonnematrix med tre celler. Funktionen STØRSTE anvender værdierne i celleområdet A5:A14, og det evalueres tre gange, én gang for hver reference, der returneres af funktionen RÆKKE. The values 3200, 2700, and 2000 are returned to the three-cell columnar array. If you want to find more values, you add a greater cell range to the INDIRECT function.

Som med tidligere eksempler kan du bruge denne formel med andre funktioner, f.eks. SUM og MIDDEL.

Finde den længste tekststreng i et celleområde

Gå tilbage til det tidligere eksempel på tekststreng, indtast følgende formel i en tom celle, og tryk på Ctrl+Skift+Enter:

=INDEKS(A2:A6,SAMMENLIGN(MAKS(LÆNGDE(A2:A6)),LÆNGDE(A2:A6),0),1)

Teksten "flere celler, der" vises.

Lad os se nærmere på formlen startende fra de inderste elementer og udad. Funktionen LÆNGDE returnerer længden af hver af elementerne i celleområdet A2:A6. Funktionen MAKS beregner den største værdi blandt elementerne, hvilket svarer til den længste tekststreng, som er i celle A3.

Nu begynder det at blive mere kompliceret. Funktionen SAMMENLIGNING beregner forskydningen (den relative placering) af den celle, som indeholder den længste tekststreng. Dette kræver tre argumenter: en opslagsværdi, en opslagsmatrix og en sammenligningstype. Funktionen SAMMENLIGNING søger efter den angivne opslagsværdi i opslagsmatrixen. I dette tilfælde er opslagsværdien den længste tekststreng:

(MAKS(LÆNGDE(A2:A6))

og strengen findes i denne matrix:

LÆNGDE(A2:A6)

Argumentet for sammenligningstypen er 0. Sammenligningstypen kan være værdien 1, 0 eller -1. Hvis du angiver 1, returnerer SAMMENLIGNING den største værdi, der er mindre end eller lig med opslagsværdien. Hvis du angiver 0, returnerer SAMMENLIGNING den første værdi, der er lig med opslagsværdien. Hvis du angiver -1, finder SAMMENLIGNING den mindste værdi, der er større end eller lig med den angivne opslagsværdi. Hvis du ikke angiver en værdi for matchningstypen, anvendes værdien 1.

Funktionen INDEKS accepterer følgende argumenter: en matrix og et række‑ og kolonnenummer i matrixen. Celleområdet A2:A6 indeholder matrixen, funktionen SAMMENLIGN er celleadressen, og det sidste argument (1) angiver, at værdien kommer fra den første kolonne i matrixen.

I dette afsnit kan du finde eksempler på avancerede matrixformler.

Opsummere et område, der indeholder fejlværdier

Funktionen SUM i Excel virker ikke, når du forsøger at summere et område, der indeholder en fejlværdi, f.eks. #N/A. I dette eksempel kan du se, hvordan du summere værdierne i det navngivne område Data, som indeholder fejl.

=SUM(HVIS(ER.FEJL(Data);"";Data))

Formlen opretter en ny matrix, der indeholder de oprindelige værdier minus eventuelle fejlværdier. Funktionen ER.FEJL søger efter fejl i celleområdet (Data). Funktionen HVIS returnerer en bestemt værdi, hvis den angivne betingelse evalueres til SAND, og en anden værdi, hvis betingelsen evalueres til FALSK. I dette tilfælde returneres der tomme strenge ("") for alle fejlværdier, da de evalueres til SAND, og den returnerer de resterende værdier fra området (Data), fordi de evalueres til FALSK (de indeholder altså ikke fejlværdier). Funktionen SUM beregner derefter totalen for den filtrerede matrix.

Tælle antal fejlværdier i et område

Dette eksempel minder om den foregående formel, men her returneres antallet af fejlværdier i det navngivne område Data i stedet for, at de filtreres fra:

=SUM(HVIS(ER.FEJL(Data),1,0))

Denne formel opretter en matrix, der indeholder værdien 1 for de celler, som indeholder fejl, og værdien 0 for de celler, der ikke indeholder fejl. Du kan forenkle formlen og opnå det samme resultat, hvis du fjerner det tredje argument til funktionen HVIS, som vist her:

=SUM(HVIS(ER.FEJL(Data),1))

Hvis du ikke angiver dette, returnerer funktionen HVIS værdien FALSK, hvis en celle ikke indeholder en fejlværdi. Det er muligt at forenkle formlen endnu mere:

=SUM(HVIS(ER.FEJL(Data)*1))

Denne version virker, fordi SAND*1=1 og FALSK*1=0.

Lægge værdier sammen baseret på betingelser

Du kan få brug for at lægge værdier sammen baseret på betingelser. Denne matrixformel lægger kun de positive heltal sammen i området Salg:

=SUM(HVIS(Salg>0,Salg))

Funktionen HVIS opretter en matrix med positive og falske værdier. Funktionen SUM ignorerer de falske værdier, fordi 0+0=0. Det celleområde, du bruger i denne formel, kan bestå af et vilkårligt antal rækker og kolonner.

Du kan også lægge værdier sammen, der opfylder mere end én betingelse. Denne matrixformel beregner værdier større end 0 og mindre end eller lig med 5:

=SUM((Salg>0)*(Salg<=5)*(Salg))

Husk på, at denne formel returnerer en fejl, hvis området indeholder en eller flere celler, som ikke indeholder tal.

Du kan også oprette matrixformler, der bruger en type af ELLER-betingelse. Du kan f.eks. lægge de værdier sammen, som er mindre end 5 og større end 15:

=SUM(HVIS((Salg<5)+(Salg>15),Salg))

Funktionen HVIS finder alle de værdier, der er mindre end 5 og større end 15 og overfører derefter disse værdier til funktionen SUM.

Du kan ikke bruge funktionerne OG og ELLER direkte i matrixformler, fordi disse funktioner returnerer et enkelt resultat, enten SANDT eller FALSK, og matrixfunktioner kræver matrixer med resultater. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition.

Beregne et gennemsnit, der udelader nulværdier

I dette eksempel kan du se, hvordan du kan fjerne nulværdier fra et område, når du skal finde middelværdien af værdierne i området. Formlen bruger et dataområde med navnet Salg:

=MIDDEL(HVIS(Salg<>0;Salg))

Funktionen HVIS opretter en matrix med de værdier, der ikke er lig med 0, og den overfører derefter disse værdier til funktionen MIDDEL.

Tælle antal forskelle mellem to celleområder

This array formula compares the values in two ranges of cells named MyData and YourData and returns the number of differences between the two. If the contents of the two ranges are identical, the formula returns 0. Hvis du vil bruge formlen, skal celleområderne have samme størrelse og dimension (hvis f.eks. MineData er et område på 3 rækker gange 5 kolonner, skal DineData også være 3 rækker gange 5 kolonner):

=SUM(HVIS(MineData=DineData,0,1))

Formlen opretter en ny matrix med samme størrelse som de områder, du sammenligner. Funktionen HVIS udfylder matrixen med værdien 0 og værdien 1 (0 for forskellige celler og 1 for identiske celler). Funktionen SUM returnerer derefter summen af værdierne i matrixen.

Du kan forenkle formlen på denne måde:

=SUM(1*(MineData<>DineData))

På samme måde som den formel, der tæller antal fejlværdier i et område, virker denne formel, fordi SAND*1=1 og FALSK*1=0.

Finde placeringen af den største værdi i et område

Denne matrixformel returnerer rækkenummeret for den største værdi i området Data, som består af én kolonne:

=MIN(HVIS(Data=MAKS(Data);RÆKKE(Data);""))

Funktionen HVIS opretter en ny matrix, der svarer til området Data. Hvis en tilsvarende celle indeholder den største værdi i området, indeholder matrixen rækkenummeret. Ellers indeholder matrixen en tom streng (""). Funktionen MIN bruger den nye matrix som det andet argument og returnerer den mindste værdi, hvilket svarer til rækkenummeret med den største værdi i Data. Hvis den største værdi i området Data findes i flere celler, returnerer formlen den række, som indeholder den første forekomst af værdien.

Hvis du vil returnere den faktiske celleadresse for den største værdi, skal du bruge denne formel:

=ADRESSE(MIN(HVIS(Data=MAKS(Data);RÆKKE(Data);""));KOLONNE(Data))