Retningslinjer for og eksempler på matrixformler

Retningslinjer for og eksempler på matrixformler

Bemærk!: Vi vil gerne give dig den mest opdaterede hjælp, så hurtigt vi kan, på dit eget sprog. Denne side er oversat ved hjælp af automatisering og kan indeholde grammatiske fejl og unøjagtigheder. Det er vores hensigt, at dette indhold skal være nyttigt for dig. Vil du fortælle os, om oplysningerne var nyttige for dig, nederst på denne side? Her er artiklen på engelsk så du kan sammenligne.

En matrixformel er en formel, der kan udføre flere beregninger på et eller flere elementer i en matrix. Du kan opfatte en matrix som en række eller en kolonne med værdier eller en kombination af rækker og kolonner med værdier. Matrixformler kan returnere enten flere resultater eller et enkelt resultat.

Fra og med september 2018-opdateringen til Office 365vil alle formler, der kan returnere flere resultater, automatisk nedskyde dem eller på tværs af tilstødende celler. Denne ændring af funktionsmåden er også ledsaget af flere nye dynamiske matrixfunktioner. Dynamiske matrixformler, uanset om de bruger eksisterende funktioner eller de dynamiske matrixfunktioner, skal kun indtastes i en enkelt celle og derefter bekræfte ved at trykke på Enter. Ældre matrixformler kræver først at markere hele outputområdet og bekræfter derefter formlen med CTRL + SKIFT + ENTER. De kaldes almindeligvis for CSE -formler.

Du kan bruge matrixformler til at udføre komplekse opgaver, f. eks.:

  • Opret hurtigt eksempel datasæt.

  • Tæl antallet af tegn, der er indeholdt i et celleområde.

  • Adder kun tal, der opfylder bestemte betingelser, f. eks de laveste værdier i et område, eller tal, der falder mellem en øvre og en nedre grænse.

  • Adder hver n'te værdi i et område med værdier.

I følgende eksempler vises, hvordan du kan oprette matrixformler med flere celler og en enkelt celle. Hvor det er muligt, har vi inkluderet eksempler med nogle af de dynamiske matrixfunktioner samt eksisterende matrixformler, der er angivet som både dynamiske og ældre rækker.

Hent vores eksempler

Hent en eksempelprojekt mappe med alle eksempler på matrixformler i denne artikel.

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

    Matrix funktionen flere celler i celle H10 = F10: F19 * G10: G19 til beregning af antal biler solgt efter enhedspris

  • Her beregner vi samlet salg af coupéer og sedaner for hver sælger ved at skrive = F10: F19 * G10: G19 i celle H10.

    Når du trykker på Enter, får du vist resultatet overløb i cellerne H10: H19. Bemærk, at overløbsområdet er fremhævet med en kant, når du markerer en celle i overløbsområdet. Du kan også bemærke, at formlerne i cellerne H10: H19 er nedtonet. De er blot til reference, så hvis du vil justere formlen, skal du markere celle H10, hvor Master formlen bor.

  • Matrixformel med én celle

    Matrixformel med én celle til beregning af en hovedtotal med = SUM (F10: F19 * G10: G19)

    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 ganger Excel værdierne i matrixen (celleområdet F10 til G19) og bruger derefter funktionen SUM til at lægge totalerne sammen. Resultatet er en samlet total på $1.590.000 i salg.

    This example shows how powerful this type of formula can be. Antag f. eks., at du har 1.000 rækker med data. Du kan lægge en del af eller alle dataene sammen ved at oprette en matrixformel i en enkelt celle i stedet for at trække formlen ned gennem 1.000-rækkerne. Bemærk også, at formlen for en enkelt celle i celle H20 er helt uafhængig af formlen med flere celler (formlen i cellerne H10 til 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 en god ide at have uafhængige totaler som dette, da det hjælper med at validere nøjagtigheden af resultaterne.

  • Dynamiske matrixformler giver også disse fordele:

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

    • Safety    Du kan ikke overskrive en komponent i en matrixformel med flere celler. Klik for eksempel på celle H11, og tryk på DELETE. Excel ændrer ikke systemets output. Hvis du vil ændre det, skal du markere cellen øverst til venstre i matrixen eller celle H10.

    • Smaller file sizes    You can often use a single array formula instead of several intermediate formulas. Eksempelvis bruger eksempelvisningen én matrixformel til at beregne resultatet i kolonne E. Hvis du har brugt standard formler som f. eks = F10 * G10, F11 * G11, F12 * G12 osv., har du brugt 11 forskellige formler til at beregne de samme resultater. Det er ikke en stor løsning, men hvad nu, hvis du har tusindvis af rækker, der skal lægges sammen? Derefter kan det være en stor forskel.

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

    • Ført    Dynamiske matrixformler afbrydes automatisk i outputområdet. Hvis dine kildedata er i en Excel-tabel, vil dine dynamiske matrixformler automatisk ændre størrelsen, når du tilføjer eller fjerner data.

    • #SPILL! fejlen    Dynamiske matrixer introducerede #SPILL!-fejlen, hvilket angiver, at det tilsigtede overløbsområde er blokeret af en eller anden grund. Når du løser blokering, afrundes 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 komma 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 på, hvordan du bruger funktionen sekvens til automatisk at oprette matrixkonstanter samt manuelt angivne matrixkonstanter.

  • Oprette en vandret konstant

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

    Opret en vandret matrixkonstant med = SEKVENS (1, 5) eller = {1, 2, 3, 4, 5}

  • 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 lodret matrixkonstant med = SEKVENS (5) eller = {1; 2; 3; 4; 5}

  • Oprette en todimensional konstant

    Markér en tom celle med plads til højre og nedenunder, og Skriv = sekvens (3, 4). Der vises følgende resultat:

    Oprette en 3-række med 4 kolonne matrixkonstant med = SEKVENS (3, 4)

    Du kan også angive: eller = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, men du skal være opmærksom på det sted, hvor du placerer semikoloner i forhold til kommaer.

    Som du kan se, giver indstillingen SEKVENS betydelige fordele ved at angive dine matrixkonstant værdier manuelt. Det sparer især tid, men det kan også være med til at reducere fejlene fra manuel indtastning. Det er også nemmere at læse, især da semikolonerne kan være svært at skelne fra komma separatorerne.

Her er et eksempel, der bruger matrixkonstanter som en del af en større formel. Gå til konstanten i et formel regneark i eksempelprojektmappen, eller Opret et nyt regneark.

I celle 3, angivet = 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 for det bestemte nummer til valg af numre, vi har lige valgt noget andet end 1-5 til differentiering.

I celle E11 skal du skrive = Sum (c: H9 * sekvens (1, 5))eller = Sum (c-c: H9 * {1, 2, 3, 4, 5}). Formlen returnerer 85.

Brug matrixkonstanter i formler. I dette eksempel har vi brugt = SUM (c-c: H (* SEKVENS (1; 5))

Funktionen SEKVENS opbygger den tilsvarende matrixkonstant {1, 2, 3, 4, 5}. Da Excel udfører handlinger på udtryk, der er omsluttet af parenteser først, er de næste to elementer, der kommer i gang, de celleværdier i c: 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 (c * 1, E9 * 2; F9 * 3, G9 * 4, H9 * 5)eller = Sum (3 * 1, 4 * 2; 5 * 3, 6 * 4, 7 * 5)

Til sidst adderer funktionen SUM værdierne og returnerer 85.

Hvis du vil undgå at bruge den gemte matrix og holde handlingen helt i hukommelsen, kan du erstatte den med en anden matrixkonstant:

= Sum (rækkefølge (1; 5; 3; 1) * række (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. sand og falsk) og fejlværdier som f. eks #N/A. Du kan bruge tal i heltal, decimaler og videnskabelige formater. Hvis du medtager tekst, skal du omslutte det 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 _GT_ definerede navne > Definer 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:

Tilføje en navngivet matrixkonstant fra formler > definerede navne > navn Manager > ny

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

Følgende resultat vises:

Brug en navngivet matrixkonstant i en formel som = Kvartal1, hvor Kvartal1 er defineret som = {"januar", "februar", "marts"}

Hvis du vil have resultatet til at lave lodret i stedet for vandret, kan du bruge =TRANSPONER(kvartal1).

Hvis du vil have vist en liste over 12 måneder, som du kan bruge til at opbygge en konto, kan du basere et af det aktuelle år med funktionen SEKVENS. Den indbyggede funktion for 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 på de navngivne matrixkonstanter og hurtige eksempel datasæt i regneark i projektmappen eksempel.

= TEKST (dato (år (dag ()), SEKVENS (1, 12), 1), "mmm")

Brug en kombination af funktionerne tekst, dato, år, dag og SEKVENS til at oprette en dynamisk liste over 12 måneder

Dette bruger funktionen dato til at oprette en dato, der er baseret på det aktuelle år, sekvensen opretter en matrixkonstant fra 1 til 12 for januar til og med december, og funktionen tekst konverterer visningsformatet til "mmm" (Jan, Feb, Mar osv.). Hvis du vil have vist hele månedsnavnet, f. eks januar, skal du bruge "d".

Når du bruger en navngivet konstant som 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. Til sidst skal du være opmærksom på, at du kan bruge kombinationer af funktioner, tekst og tal. Det afhænger af, hvor kreativ du vil have.

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

  • Flere enkelte elementer i en matrix

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

    Du kan også opdele med (/), tilføje med (+) og subtrahere med (-).

  • Tag kvadratroden af elementerne i en matrix

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

  • Finde kvadratroden af kvadrerede elementer i en matrix

    ENTER =KVROD(sekvens (1, 12) ^ 2)eller = KVROD ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Transponere en endimensional række

    ENTER = 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.

  • Transponere en endimensional kolonne

    ENTER = 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.

  • Transponere en todimensional konstant

    ENTER = 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.

  • Oprette en matrix ud fra eksisterende værdier

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

    ENTER = sekvens (3, 6, 10, 10)eller = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Sørg for at skrive {(venstre klammeparentes), før du skriver 10, og} (højre klammeparentes), efter at du har skrevet 180, fordi du opretter en matrix af tal.

    Derefter skal du indtaste = en efter @ #eller = en-værdi : i11 i en tom celle. En 3 x 6-matrix af celler vises med de samme værdier, som du kan se i efter stykke: D11. Tegnet # kaldes operatoren for et udgået område, og det er Excel's måde at referere til hele matrix området i stedet for at skulle skrive det.

    Brug af operatoren overløbsområde (#) for at referere til en eksisterende matrix

  • Oprette en matrixkonstant ud fra eksisterende værdier

    Du kan tage resultaterne af en overløbs matrixformel og konvertere den til dens komponentdele. Markér celle-=, og tryk derefter på F2 for at skifte til redigeringstilstand. Derefter skal du trykke på F9 for at konvertere cellereferencer til værdier, som Excel derefter konverterer til en matrixkonstant. Når du trykker på Enter, skal formlen, = "efter @ #, være = {10; 20; 30; 40; 50; 60; 70; 80; 90}.

  • Tælle antal tegn i et celleområde

    I følgende eksempel vises det, hvordan du tæller antallet af tegn i et celleområde. Dette omfatter mellemrum.

    Tæl det samlede antal tegn i et område og andre matrixer til at arbejde med tekststrenge

    = 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 adderer derefter disse værdier sammen og viser resultatet (66). Hvis du ønsker at få gennemsnitligt antal tegn, kan du bruge:

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

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

    = INDEKS (C9: C13; MATCH (MAX) (LEN (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 hvert element i celleområdet D2: D6. Funktionen maks beregner den største værdi blandt disse elementer, som svarer til den længste tekststreng, som er i celle D3.

    Nu begynder det at blive mere kompliceret. Funktionen Sammenlign beregner forskydningen (den relative position) for 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:

    MAX (LEN (C9: C13)

    og strengen findes i denne matrix:

    LÆNGDE (C9: C13)

    Argumentet matchtype i dette tilfælde er 0. Typen match kan være en værdi på 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 nøjagtigt 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 matchningstypen, anvendes værdien 1.

    Til sidst tager funktionen indeks disse argumenter: en matrix og et række-og kolonnenummer i matrixen. Celleområdet C9: C13 leverer matrixen, funktionen MATCH indeholder celleadressen, og det endelige argument (1) angiver, at værdien kommer fra den første kolonne i matrixen.

    Hvis du vil hente indholdet af den mindste tekststreng, så skal du erstatte det maksimale i eksemplet med min.

  • Finde 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 af eksempeldata i cellerne B9: B18has er oprettet med: = int (SLUMP matrix(10; 1) * 100). Bemærk, at SLUMP matrix er en midlertidig funktion, så du får et nyt sæt tilfældige tal, hver gang Excel beregner.

    Excel-matrixformel til at finde den n'te mindste værdi: = lille (B9 #, SEQUENCe (c))

    ENTER = lille (B9 #, Sequence (efterse), = lille (B9: B18, {1; 2; 3})

    Denne formel bruger en matrixkonstant til at evaluere den lille funktion tre gange og returnere de mindste 3 medlemmer i den matrix, der er indeholdt i cellerne B9: B18, hvor 3 er en variabelværdi i celle 3. 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 med denne formel, f. eks Sum eller gennemsnit. Det kunne f.eks. være:

    = SUM (LILLE (B9 #; SEQUENCE))

    = MIDDEL (LILLE (B9 #; SEKVENS))

  • 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 den lille funktion med funktionen stor. I det følgende eksempel bruges funktioner RÆKKE og INDIREKTE også.

    ENTER = store (B9 #, række (indirekte ("1:3")))eller = stort (B9: B18, 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. Du kan f. eks vælge et tomt og skrive:

    =RÆKKE(1:10)

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

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

    Funktionen indirekte bruger tekststrenge som argumenter (som er årsag til, at området 1:10 er omgivet af 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å let bruge rækkefølge:

    = SEKVENS (10)

    Lad os undersøge formlen, du har brugt tidligere – = stor (B9 #, række (indirekte ("1:3")) – startende fra de inderste parenteser, og arbejd udad: funktionen indirekte returnerer et sæt tekstværdier, i dette tilfælde værdierne 1 til 3. Funktionen række efter tur genererer en kolonne matrix med tre celler. Funktionen stor bruger værdierne i celleområdet B9: B18, og den evalueres tre gange, én gang for hver reference, der returneres af funktionen række. If you want to find more values, you add a greater cell range to the INDIRECT function. Til sidst med de små eksempler kan du bruge denne formel sammen med andre funktioner, f. eks SUM og gennemsnit.

  • Opsummere et område, der indeholder fejlværdier

    Funktionen SUM i Excel fungerer ikke, når du forsøger at beregne summen af et område, der indeholder en fejlværdi, såsom #VALUE! eller #N/A. Dette eksempel viser, hvordan du Adderer værdierne i et område med navnet data, der indeholder fejl:

    Brug matrixer til at håndtere fejl. F. eks. = SUM (hvis (fejl (data), "", data) adderer området navngivne data, selvom der er fejl, som #VALUE! eller #NA!.

  • =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 er som den forrige formel, men den returnerer antallet af fejlværdier i et område med navnet data i stedet for at filtrere dem:

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

Du kan bruge matrixer til at beregne baseret på bestemte betingelser. = SUM (hvis (Sales>0; salg)) adderer alle værdier, der er større end 0 i et område kaldet salg.

For eksempel lægger denne matrixformel kun de positive heltal i et område, der kaldes 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. For eksempel beregner denne matrixformel værdier, der er større end 0 og mindre end 2500:

= SUM ((Sales>0) * (Sales<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 eller betingelse. Du kan for eksempel beregne værdier, der er større end 0 eller mindre end 2500:

= SUM ((((Sales>0) + (Sales<2500); salg))

Du kan ikke bruge funktionerne AND og OR i matrixformler direkte, da disse funktioner returnerer et enkelt resultat, enten sand eller falsk, og matrixfunktioner kræver matrixer med resultater. You can work around the problem by using the logic shown in the previous formula. Det betyder, at du udfører matematiske handlinger som addition eller multiplikation på værdier, der opfylder betingelsen OR eller AND og.

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 celle områderne have samme størrelse og for den samme dimension. Hvis mine er et interval på 3 rækker med 5 kolonner, skal data 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 * (MyData<>YourData))

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 kan finde lignende eksempler i eksempelprojektmappen på regnearket forskelle mellem datasæt .

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 Skriv

Nummer Solgt

Enhed Pris

Samlet antal 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 sedaner for hver sælger, skal du markere cellerne E2: E11, skrive formlen = C2: C11 * D2: D11og 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 derefter trykke på CTRL + SKIFT + ENTER.

Når du trykker på CTRL + SKIFT + ENTER, omgives formlen med klammeparenteser ({}), og der indsættes 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}

Totalerne i kolonne E beregnes ved hjælp af en matrixformel

  • Oprette 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 ganger Excel værdierne i matrixen (celleområdet C2 til D11) og bruger derefter funktionen SUMtil at lægge totalerne sammen. Resultatet er en samlet total på $1.590.000 i salg. This example shows how powerful this type of formula can be. Antag f. eks., at du har 1.000 rækker med data. Du kan lægge en del af eller alle dataene sammen ved at oprette en matrixformel i en enkelt celle i stedet for at trække formlen ned gennem 1.000-rækkerne.

Bemærk også, at formlen for en enkelt celle i celle D13 er helt uafhængig af formlen med flere celler (formlen i cellerne E2 til E11). Dette er en anden fordel ved at bruge matrixformler  – fleksibilitet. Du kan ændre formlerne i kolonne E eller slette den pågældende kolonne helt uden at påvirke 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.

  • Safety    You cannot overwrite a component of a multi-cell array formula. Klik for eksempel på celle E3, og tryk på DELETE. 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 ekstra sikkerhedsforanstaltning skal du trykke på CTRL + SKIFT + ENTER for at bekræfte eventuelle ændringer i formlen.

  • Smaller file sizes    You can often use a single array formula instead of several intermediate formulas. For eksempel bruger projektmappen én matrixformel til at beregne resultatet i kolonne E. Hvis du har brugt standard formler (f. eks.: C2 * D2, C3 * D3, C4 * d4...), har du 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 vigtigste forskel er, at når du bruger en matrixformel, skal du trykke på CTRL + SKIFT + ENTER for at indtaste formlen. 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 oprette 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).

Vigtigt!: Tryk på CTRL + SKIFT + ENTER , når du har brug for at angive en matrixformel. Dette gælder både formler i flere celler og i én celle.

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 formel området (f. eks. E2: E11) og derefter trykke på DELETE.

  • 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 eksisterende matrix område, og Fortsæt, indtil du har markeret hele området, som du vil udvide formlen til. Tryk på F2 for at redigere formlen, og tryk derefter på CTRL + SKIFT + ENTER for at bekræfte formlen, når du har justeret formel området. Hvis du vil markere hele området, skal du starte med cellen øverst til venstre i matrixen. Den øverste venstre celle er den, der bliver redigeret.

Matrixformler er fantastiske, men der er også ulemper:

  • Du kan undertiden huske 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 du derfor har brug for at ændre dine projektmapper, skal du enten undgå matrixformler eller sørge for, at disse personer kender alle matrixformler, og forstå, hvordan du 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}

Du ved nu, at du skal trykke på CTRL + SKIFT + ENTER , når du opretter matrixformler. Da matrixkonstanter er en komponent af matrixformler, skal du skrive klammeparenteserne om konstanterne manuelt, når du skriver dem. Du kan derefter bruge CTRL + SKIFT + ENTER til at indtaste 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 den to række matrix 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 de indledende og afsluttende klammeparenteser ({}), og Excel tilføjer det andet sæt for dig.

    Følgende resultat vises:

    Vandret matrixkonstant i formel

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:

    Lodret matrixkontakt i matrixformel

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:

    Todimensional matrixkonstant i matrixformel

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.

    Matrixformel med matrixkonstant

    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.

Syntaksen for en matrixformel med en matrixkonstant

1. Funktion

2. Lagret matrix

3. Operator

4. Matrixkonstant

Det sidste element i parentesen er matrixkonstanten: {1, 2, 3, 4, 5}. Husk, at Excel ikke omgiver matrixkonstanter med klammeparenteser. du skriver dem faktisk. 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, skal du kopiere funktionen, markere en tom celle i projektmappen, indsætte formlen i 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 teksten 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:

    Dialogboksen Rediger navn med formel

  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:

    Navngivet matrix skrevet som formel

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 semikolon, eller hvis du placerer et et forkert område, er matrixkonstanten muligvis ikke oprettet korrekt, eller du kan få vist en advarselsmeddelelse.

  • Du har muligvis markeret et celleområde, der ikke svarer til 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 Brug 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, som angiver denne matrix af tal 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

    mindst

    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

    En 3x3 matrix af celler vises i cellerne C1 til E3 med de samme værdier, du ser i C8, til E10.

Oprette en matrixkonstant ud fra eksisterende værdier

  1. Med cellerne C1: C3 markeret skal du trykke på F2 for at skifte til redigeringstilstand. 

  2. Tryk på F9 for at konvertere cellereferencer til værdier. Excel converts the values into an array constant. Formlen bør nu være = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Tryk på CTRL + SKIFT + ENTER for at angive 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 se det samlede antal tegn i cellerne A2: A6 (66).

  3. Markér celle A10, og tryk derefter på CTRL + SKIFT + ENTER for at se indholdet af den længste celle i cellerne A2: A6 (celle A3).

Følgende formel bruges i celle A8 tæller det samlede antal tegn (66) i cellerne a2 til 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 adderer 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. Angiv nogle tilfældige tal i cellerne a1: A11.

  2. Markér cellerne C1 til og med C3. This set of cells will hold the results returned by the array formula.

  3. Skriv følgende formel, og tryk derefter på CTRL + SKIFT + ENTER:

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

Denne formel bruger en matrixkonstant til at evaluere den lille funktion tre gange og returnere de mindste (1), mindste (2) og tredje mindste (3) medlemmer i matrixen, der er indeholdt i cellerne a1: A10 hvis du vil finde flere værdier, skal du føje flere argumenter til CVS. Du kan også bruge flere funktioner med denne formel, f. eks Sum eller gennemsnit. Det kunne f.eks. være:

= SUM (LILLE (A1: A10; {1; 2; 3})

= MIDDEL (LILLE (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 cellerne D1 til D3.

  2. Skriv denne formel på formellinjen, og tryk derefter på CTRL + SKIFT + ENTER:

    =LARGE(A1:A10,ROW(INDIRECT("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 for eksempel en tom kolonne med 10 celler i øvelsesprojektmappen, Angiv 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 have vist et potentielt problem, skal du indsætte en række over det område, der indeholder matrixformlen (dvs. over række 1). Excel justerer række referencerne, og formlen genererer heltal fra 2 til 11. For at løse dette problem skal du føje den indirekte funktion 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 har brugt tidligere – = store (A5: A14, række (indirekte ("1:3") ) – startende fra de inderste parenteser, og arbejd udad: funktionen indirekte returnerer et sæt tekstværdier, i dette tilfælde værdierne 1 til 3. The ROW function in turn generates a three-cell columnar array. Funktionen stor bruger 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 sammen med andre funktioner, f. eks Sum og gennemsnit.

Finde den længste tekststreng i et celleområde

Gå tilbage til det tidligere tekststreng eksempel, Skriv 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 "en masse celler, der" vises.

Lad os se nærmere på formlen startende fra de inderste elementer og udad. Funktionen længde returnerer længden af hvert element i celleområdet A2: A6. Funktionen Maks beregner den største værdi blandt disse elementer, som 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:

(MAX (LEN (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 leverer matrixen, funktionen Sammenlign angiver celleadressen, og det endelige 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 fungerer ikke, når du forsøger at beregne summen af et område, der indeholder en fejlværdi, såsom #N/a. Dette eksempel viser, hvordan du Adderer 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. 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 eller betingelse. Du kan for eksempel beregne værdier, der 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 and og or i matrixformler direkte, da disse funktioner returnerer et enkelt resultat, enten sand 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 denne formel, skal celle områderne have samme størrelse og for den samme dimension (hvis mine er et interval på 3 rækker med 5 kolonner, skal data også være 3 rækker med 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 * (MyData<>YourData))

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

Turnering

Dele af denne artikel er baseret på en række Excel-Power-kolonner, som er skrevet af hans Jensen, og som er tilpasset fra kapitel 14 og 15 i Excel 2002-formler, en bog skrevet af John Walkenbach, en tidligere Excel MVP.

Har du brug for mere hjælp?

Du kan altid spørge en ekspert i Excel Tech Community, få support i Answers community eller foreslå en ny funktion eller forbedring i Excel User Voice.

Se også

Dynamiske matrixer og funktionsmåde for overløbsmatrix

Dynamiske matrixformler vs. ældre CSE matrixformler

Funktionen FILTRER

Funktionen SLUMPMATRIX

Funktionen SEKVENS

Funktionen ENKELT

Funktionen SORTER

Funktionen SORTER.EFTER

Funktionen ENTYDIGE

Fejl med #OVERLØB! i Excel

Oversigt over formler

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

Tak for din feedback!

Tak for din feedback! Det lyder, som om det vil kunne hjælpe, hvis du bliver sat i forbindelse med en af vores Office-supportteknikere.

×