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

  

• Her beregner vi det samlede salg af kuponer og sedans for hver sælger ved at skrive =F10:F19*G10:G19 i celle H10.

  Når du trykker Enter, ser du resultaterne overløbe ned til cellerne H10:H19. Bemærk, at overløbsområdet fremhæves med en kant, når du markerer en celle indenfor overløbsområdet. Du har muligvis også bemærket, at formlerne i cellerne H10:H19 er nedtonet. De er der kun til reference, så hvis du vil justere formlen, skal du markere celle H10, hvor masterformlen findes.

• Matrixformel med én celle

  

  I celle H20 i eksempelprojektmappen skal du skrive eller kopiere og indsætte =SUM(F10:F19*G10:G19) og derefter trykke på Enter.

  I dette tilfælde, multiplicerer Excel værdierne i matrixen (celleområdet F10 til og med G19), og derefter bruges funktionen SUM til 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 enkeltcelle-formlen i celle H20 er helt uafhængig af flercelle-formlen (formlen i cellerne H10 til og med H19). Dette er en anden fordel ved at bruge matrixformler – fleksibilitet. Du kan ændre de andre formler i kolonne H uden at påvirke formlen i H20. Det kan også være god praksis at have uafhængige totaler på denne måde, da det hjælper med at validere nøjagtigheden af dine resultater.

• Dynamiske matrixformler har også disse fordele:

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

  • Sikkerhed    Du kan ikke overskrive en komponent i en matrixformel med flere celler. Klik f.eks. på celle H11, og tryk på Delete. Excel ændrer ikke matrixens output. Hvis du vil ændre det, skal du markere den øverste venstre celle i matrixen eller celle H10.

  • Mindre filstørrelser    You can often use a single array formula instead of several intermediate formulas. I eksemplet med bilsalg bruges f.eks. én matrixformel til at beregne resultaterne i kolonne E. Hvis du havde brugt standardformler f.eks. =F10*G10, F11*G11, F12*G12 osv., ville du have brugt 11 forskellige formler til at beregne de samme resultater. Det er ikke noget stort problem, men hvad nu, hvis du havde tusindvis af rækker, hvor der skulle regnes en total? Så kan det gøre en stor forskel.

  • Effektivitet    Matrixfunktioner kan være en effektiv måde at bygge komplekse formler på. Matrixformlen =SUM(F10:F19*G10:G19) er den samme som denne: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

  • Overløb    Dynamiske matrixformler overløber automatisk i outputområdet. Hvis din kildedata er i en Excel-tabel, ændres størrelsen af dine dynamisk matrixformler automatisk, når du tilføjer eller fjerner data.

  • #OVERLØB! fejl    Dynamiske matrixer introducerede fejlen #SPILL!, hvilket angiver, at det tilsigtede overløbsområde af en eller anden grund er blokeret. Når du løser blokeringen, overløber formlen automatisk.

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} eller ={"Januar"\"Februar"\"Marts"}

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 semikolon.

Med de følgende procedurer får du øvelse i at oprette vandrette, lodrette og todimensionale konstanter. Vi viser eksempler, der bruger funktionen SEKVENS-funktion til automatisk at generere matrixkonstanter samt manuelt indtastede matrixkonstanter.

• Oprette en vandret konstant

  Brug projektmappen fra de tidligere eksempler, eller opret en ny projektmappe. Markér en tom celle, og indtast =SEKVENS(1,5). Funktionen SEKVENS opbygger en matrix af typen 1 række og 5 kolonner på samme måde som ={1\2\3\4\5}. Følgende resultat vises:

  

• Oprette en lodret konstant

  Markér en tom celle med plads nedenunder, og skriv =SEKVENS(5)eller ={1;2;3;4;5}. Følgende resultat vises:

  

• Oprette en todimensional konstant

  Markér en tom celle med plads til højre og under den, og skriv =SEKVENS(3,4). Der vises følgende resultat:

  

  Du kan også skrive: eller ={1\2\3\4;5\6\7\8;9\10\11\12}, men du skal være opmærksom på, hvor du placerede semikoloner kontra kommaer.

  Som du kan se, giver indstillingen SEKVENS betydelige fordele i forhold til manuelt at indtaste matrixkonstantværdier. Primært sparer det dig tid, men det kan også reducere fejl fra manuel indtastning. Det er også nemmere at læse, især da semikolon kan være svære at skelne fra kommaseparatorer.

Her er et eksempel, der bruger matrixkonstanter som del af en større formel. I eksempelprojektmappen skal du gå til Konstant i et formelregneark eller oprette et nyt regneark.

I celle D9 indtastede vi =SEKVENS(1,5,3,1), men du kan også angive 3, 4, 5, 6 og 7 i cellerne A9:H9. Der er ikke noget særligt ved det pågældende talvalg. Vi valgte bare noget andet end 1-5 for at differentiere.

I celle E11 skal du indtaste =SUM(D9:H9*SEKVENS(1,5)) eller =SUM(D9:H9*{1\2\3\4\5}). Formlerne returnerer 85.

Funktionen SEKVENS opbygger det, der svarer til matrixkonstanten {1\2\3\4\5}. Da Excel først udfører handlinger på de udtryk, der er omsluttet af parenteser, er de næste to elementer, der afspilles, celleværdierne i D9:H9 og multiplikationsoperatoren (*). På dette tidspunkt ganger formlen værdierne i den lagrede matrix med de tilsvarende værdier i konstanten. Dette svarer til:

=SUM(D9*1;E9*2;F9*3;G9*4;H9*5) eller =SUM(3*1;4*2;5*3;6*4;7*5)

Til sidst lægger funktionen SUM værdierne sammen og returnerer 85.

Hvis du vil undgå at bruge den lagrede matrix og udelukkende gemme handlingen i hukommelsen, kan du erstatte den med en anden matrixkonstant:

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

Elementer, du kan bruge i matrixkonstanter

• Matrixkonstanter kan indeholde tal, tekst, logiske værdier (f.eks. SANDT og FALSK) og fejlværdier som f.eks. #N/A. Du kan bruge tal i heltals-, decimal- og videnskabelige formater. Hvis du medtager tekst, skal du omslutte den med anførselstegn ("tekst").

• 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:

Gå til Formler > Definerede Navne > Definér Navn. Skriv Kvartal1 i feltet Navn. Skriv følgende konstant (husk at skrive klammeparenteserne manuelt) i feltet Refererer til:

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

Dialogboksen bør nu se sådan ud:

Klik på OK, vælg derefter en række med tre tomme celler, og skriv =Kvartal1.

Følgende resultat vises:

Hvis du vil have, at resultaterne overløber lodret i stedet for vandret, kan du bruge =TRANSPONER(Kvartal1).

Hvis du vil have vist en liste med 12 måneder, som du måske bruger, når du opbygger en regnskabsopgørelse, kan du basere en på det aktuelle år med funktionen SEKVENS. Det smarte ved denne funktion er, at selvom kun måneden vises, er der en gyldig dato bag den, som du kan bruge i andre beregninger. Du kan finde disse eksempler i regnearkene Navngivet matrixkonstant og Eksempeldatasæt i eksempelprojektmappen.

=TEKST(DATO(ÅR(IDAG()),SEKVENS(1,12),1),"mmm")

Dette bruger funktionen DATO til at oprette en dato, der er baseret på det aktuelle år, SEKVENS opretter en matrixkonstant fra 1 til 12 for januar til december, derefter konverterer funktionen TEKST visningsformatet til "mmm" (jan., feb., mar. osv.). Hvis du vil have vist det fulde månedsnavn, f.eks. januar, skal du bruge "mmmm".

Når du bruger en navngivet konstant som en matrixformel, skal du huske at angive lighedstegnet som i =Kvartal1, ikke kun Kvartal1. 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 funktioner, tekst og tal. Det hele afhænger af, hvor kreativt du vil have det.

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 element i en matrix

  Skriv =SEKVENS(1,12)*2eller ={1\2\3\4;5\6\7\8;9\10\11\12}*2

  Du kan også dividere med (/), addere med (+) og subtrahere med (-).

• Tag kvadratroden af elementerne i en matrix

  Skriv =SEKVENS(1,12)^2eller ={1\2\3\4;5\6\7\8;9\10\11\12}^2

• Find kvadratroden af kvadrerede elementer i en matrix

  Angiv =SQRT(SEKVENS(1,12)^2)eller =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)

• Transponer en endimensional række

  Skriv =TRANSPONER(SEKVENS(1,5))eller =TRANSPONER({1\2\3\4\5})

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

• Transponer en endimensional kolonne

  Skriv =TRANSPONER(SEKVENS(5,1))eller =TRANSPONER({1\2\3\4\5})

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

• Transponer en todimensional konstant

  Skriv =TRANSPONER(SEKVENS(3,4))eller =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.

• Opret en matrix ud fra eksisterende værdier

  I følgende eksempel forklares det, hvordan du bruger matrixformler til at oprette en ny matrix ud fra en eksisterende matrix.

  Skriv =SEKVENS(3,6,10,10) eller ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

  Sørg for at skrive { (startklammeparentes) før du skriver 10, og } (slutklammeparentes), efter at du har skrevet 180, fordi du opretter en matrix med tal.

  Skriv derefter =D9# eller =D9:I11 i en tom celle. Der vises en 3 x 6 cellematrix med de samme værdier, som du ser i D9:D11. #-tegnet kaldes operatoren for overløbssområdet, og det er Excels måde at referere til hele matrixområdet i stedet for at skulle skrive det.

  

• Opret en matrixkonstant ud fra eksisterende værdier

  Du kan tage resultaterne af en overløbet matrixformel og konverter den til dens komponentdele. Markér celle D9, og tryk derefter på F2 for at skifte til redigeringstilstand. Tryk derefter på F9 for at konvertere cellereferencerne til værdier, som Excel derefter konverterer til en matrixkonstant. Når du trykker på Enter, skulle formlen, = D9#, nu være ={10\20\30;40\50\60;70\80\90}.

• Tæl antal tegn i et celleområde

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

  

  =SUM(LÆNGDE(C9:C13))

  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). Hvis du vil have det gennemsnitlige antal tegn, kan du bruge:

  =MIDDEL(LÆNGDE(C9:C13))

• Indholdet af den længste celle i området C9:C13

  =INDEX(C9:C13,SAMMENLIGN(MAKS(LÆNGDE(C9:C13)),LÆNGDE(C9:C13),0),1)

  Denne formel virker kun, når et dataområde indeholder en enkelt kolonne med celler.

  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 D2:D6. Funktionen MAKS beregner den største værdi blandt elementerne, hvilket svarer til den længste tekststreng, som er i celle D3.

  Nu begynder det at blive mere kompliceret. Funktionen SAMMENLIGNING beregner forskydningen (den relative placering) af den celle, der 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(C9:C13)

  og strengen findes i denne matrix:

  LÆNGDE(C9:C13)

  Argumentet for matchtype er i dette tilfælde 0. Sammenligningstypen kan være værdien 1, 0 eller -1.

  • 1 - returnerer den største værdi, der er mindre end eller lig med opslagsværdien

  • 0 - returnerer den første værdi, der er nøjagtig lig med opslagsværdien

  • -1- returnerer den mindste værdi, der er større end eller lig med den angivne opslagsværdi

  • Hvis du ikke angiver en værdi for sammenligningstypen, antager Excle, at værdien er 1.

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

  Hvis du vil have indholdet af den mindste tekststreng, skal du erstatte MAKS i eksemplet ovenfor med MIN.

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

  I dette eksempel vises det, hvordan du finder de tre mindste værdier i et celleområde, hvor en matrix med eksempeldata i cellerne B9:B18 er oprettet med: =HELTAL(SLUMPMATRIX(10,1)*100). Bemærk, at SLUMPMATRIX er en flygtig funktion, så du får et nyt sæt tilfældige tal, hver gang Excel beregner.

  

  Skriv =MINDSTE(B9#,SEKVENS(D9), =MINDSTE(B9:B18,{1;2;3})

  Denne formel bruger en matrixkonstant til at evaluere funktionen MINDSTE tre gange og returnerer de mindste 3 medlemmer i matrixen i cellerne B9:B18, hvor 3 er en variabel værdi i celle D9. Hvis du vil finde flere værdier, kan du øge værdien i funktionen SEKVENS eller føje flere argumenter til konstanten. Du kan også bruge flere funktioner sammen med denne formel, f.eks. SUM eller MIDDEL. Eksempler:

  =SUM(MINDSTE(B9#,SEKVENS(D9))

  =MIDDEL(MINDSTE(B9#,SEKVENS(D9))

• Find 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 funktionerne RÆKKE og INDIREKTE også.

  Angiv =STØRSTE(B9#,RÆKKE(INDIREKTE("1:3"))) eller =STØRSTE(B9:B18,RÆKKE(INDIREKTE("1:3")))

  På dette tidspunkt kan det være en hjælp at kende funktionerne RÆKKE og INDIREKTE. Du kan bruge funktionen RÆKKE til at oprette en matrix med fortløbende heltal. Markér f.eks. en tom, og skriv:

  =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. Du kan lige så nemt bruge SEKVENS:

  =SEKVENS(10)

  Lad os undersøge den formel, du brugte tidligere – =STØRSTE(B9#,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 genererer til gengæld en kolonnematrix med tre celler. Funktionen STØRSTE bruger værdierne i celleområdet B9:B18, og den evalueres tre gange, én gang for hver reference, der returneres af funktionen RÆKKE. Hvis du vil finde flere værdier, skal du føje et større celleområde til funktionen INDIREKTE. Som med eksemplerne på MINDSTE kan du bruge denne formel med andre funktioner, f.eks. SUM og MIDDEL.

• Opsummer 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. #VALUE! eller #N/A. I dette eksempel kan du se, hvordan du summerer værdierne i et område med navnet Data, der indeholder fejl:

  

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

  Formlen opretter en ny matrix, der indeholder de oprindelige værdier minus eventuelle fejlværdier. Startende fra de indre funktioner og udefter, søger ER.FEJL-funktionen 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æl antal fejlværdier i et område

  Dette eksempel er ligesom den forrige formel, men returnerer antallet af fejlværdier i området Data i stedet for at filtrere dem 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.

Du kan få brug for at lægge værdier sammen baseret på betingelser.

Denne matrixformel summerer f.eks. kun positive heltal i et område med navnet Salg, som repræsenterer cellerne E9:E24 i eksemplet ovenfor:

=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 f.eks. værdier, der er større end 0 OG mindre end 2500:

=SUM((Salg>0)*(Salg<2500)*(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. summere værdier, der er større end 0 ELLER mindre end 2500:

=SUM(HVIS((Salg>0)+(Salg<2500),Salg))

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. Du kan omgå problemet ved at bruge den logik, der er vist i den forrige formel. Med andre ord udfører du matematiske handlinger, f.eks. addition eller multiplikation på værdier, der opfylder ELLER- eller OG-betingelsen.

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.

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 denne formel, skal celleområdet have samme størrelse og dimension. Hvis MineData f.eks. 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.

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))

Du finder lignende eksempler i eksempelprojektmappen på siden Forskelle mellem datasæt regnearket.

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))