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 mange beregninger på en eller flere elementer i en matrix. Du kan betragte en matrix som en række eller kolonne med værdier eller en kombination af rækker og kolonner med værdier. Matrixformler kan returnere flere resultater eller et enkelt resultat.

Begynder med September 2018 opdateringen til Office 365, bliver en formel, der kan returnere flere resultater automatisk spilder dem ned eller på tværs af i tilstødende celler. Denne ændring i tilpasning leveres også med flere nye dynamiske række funktioner. Dynamisk matrixformler, skal uanset om de bruger eksisterende funktioner eller matrixfunktioner dynamisk kun være indtastet i en enkelt celle og derefter bekræftet ved at trykke på Enter. Tidligere, ældre matrixformler kræver først at markere hele outputområde og derefter bekræfte af formlen med Ctrl + Skift + Enter. De er ofte kaldes formler .

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

  • Hurtigt oprette eksempel datasæt.

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

  • Sum kun tal, der opfylder bestemte betingelser, som den laveste værdi i et interval, eller tal, der falder mellem en øverste og nederste grænse.

  • Lægge hver n'te værdi i en række værdier.

I følgende eksempler viser, hvordan du opretter med flere celler og én celle matrixformler. Hvor det er muligt, har vi medtaget eksempler med nogle af de dynamiske matrixfunktioner samt eksisterende matrixformler, der er angivet som både dynamiske og ældre matrixer.

Download vores eksempler

Hente en eksempel-projektmappe med alle matrix formel eksemplerne 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

    Med flere celler matrixfunktion i celle H10 = F10:F19 * G10:G19 til at beregne antallet af biler solgt af enhedspris

  • Vi her beregning samlet salg af coupéer og sedaner for hver sælger ved at angive = F19:F19 * G10:G19 i celle H10.

    Når du trykker på Enter, får du vist resultaterne spilder ned til celler H10:H19. Bemærk, at området vandtætte er fremhævet med en kant, når du vælger en vilkårlig celle i området vandtætte. Du kan også bemærke, at formler i celler H10:H19 er nedtonet. De er lige der til reference, så hvis du vil justere formlen, skal du markere celle H10, hvor master formlen findes.

  • Matrixformel med én celle

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

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

    I dette tilfælde Excel ganger værdierne i matrixen (celleområdet F10 gennem G19), og derefter bruger funktionen SUM til at tilføje totalerne sammen. Resultatet er et hovedtotalen for 1,590,000 i salg.

    I dette eksempel vises, hvordan effektive denne type formel kan være. Lad os antage, at du har 1.000 rækker af data. Du kan lægge en del af eller alle disse data ved at oprette en matrixformel i en enkelt celle i stedet for at trække formlen gennem de 1.000 rækker. Bemærk også, at én celle formlen i celle H20 er helt uafhængig af formlen med flere celler (formel i celle H10 til H19). Dette er en anden fordel ved brug af matrixformler – fleksibilitet. Du kan ændre de andre formler i kolonne H uden at påvirke formlen i H20. Det kan også være god fremgangsmåde at have uafhængige totaler Sådan her ud, som det hjælper med at kontrollere nøjagtigheden af dine resultater.

  • Dynamisk matrixformler også fordele følgende:

    • Konsistens    Hvis du klikker på en af cellerne fra H10 nedad, vises den samme formel. Pågældende konsistens kan hjælpe dig med at sikre større nøjagtighed.

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

    • Mindre filstørrelser    Du kan bruge en enkelt matrixformel ofte i stedet for flere mellemliggende formler. For eksempel bruges i bil salg eksempel en matrixformel til at beregne resultaterne i kolonne E. Hvis du havde brugt almindelige formler såsom = F10 * G10, F11 * G11, F12 * G12, osv, du vil har brugt 11 forskellige formler til at beregne det samme resultat. Det er ikke en fordelen, men hvad nu, hvis du havde tusindvis af rækker til total? Derefter kan det gøre en stor forskel.

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

    • Går    Dynamisk matrixformler bliver automatisk spilder i outputområdet. Hvis dataene er i en Excel-tabel, derefter dine dynamiske matrixformler tilpasser automatisk størrelsen på når du tilføjer eller fjerner data.

    • #SPILL! fejl    Dynamiske matrixer introduceret på #SPILL! fejl, hvilket angiver, at området tilsigtede vandtætte blokeres eller anden grund. Når du fortolker blokering, spilder 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 skal adskille elementer ved hjælp af kommaer, kan du oprette en vandret matrix (en række). Hvis du skal adskille elementer ved hjælp af semikoloner, kan du oprette en lodret matrix (en kolonne). Hvis du vil oprette en todimensional matrix, du afgrænse elementer i hver række med kommaer og afgrænse hver række med semikolon.

Følgende procedurer får du øvelse i at oprette vandrette, lodrette og todimensionale konstanter. Vi viser eksempler på brug af SEKVENS funktionen til automatisk for at oprette matrixkonstanter, samt manuelt angivet matrixkonstanter.

  • Oprette en vandret konstant

    Brug projektmappen fra de tidligere eksempler, eller Opret en ny projektmappe. Markere en tom celle, og angiv =SEQUENCE(1,5). Funktionen SEKVENS opbygger en 1 række af 5 kolonne matrix, den samme som = {1,2,3,4,5}. Følgende resultat vises:

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

  • Oprette en lodret konstant

    Vælg en tom celle med plads nedenunder, og angiv =SEQUENCE(5)eller = {1; 2; 3; 4; 5}. Følgende resultat vises:

    Oprette en lodret matrixkonstant med = SEQUENCE(5) eller = {1; 2; 3; 4; 5}

  • Oprette en todimensional konstant

    Vælg en tom celle med plads til højre og nedenunder, og angiv =SEQUENCE(3,4). Der vises følgende resultat:

    Oprette en 3 rækker ved 4 kolonner matrixkonstant med =SEQUENCE(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å, hvor du placerer semikoloner kontra kommaer.

    Som du kan se, indeholder indstillingen SEKVENS betydeligt fordele i forhold til manuelt at angive din konstant matrix-værdier. Primært, gemmer den tid, men det kan også hjælpe reducere fejl fra manuel indtastning. Det er også nemmere at læse, især som semikoloner kan være svært at skelne mellem kommaseparatorer.

Her er et eksempel, bruger array konstanter som en del af en større formel. Gå til konstant i en formel regnearket i eksempelprojektmappen, eller Opret et nyt regneark.

Vi har angivet =SEQUENCE(1,5,3,1)i celle D9, men du kan også angive 3, 4, 5, 6 og 7 i celler A9:H9. Der er intet speciel om dette bestemt tal valg, vi vælger blot et andet tal end 1-5 for graduering.

I celle E11, skrive = SUM (D9:H9*SEQUENCE(1,5)), eller = SUM (D9:H9* {1,2,3,4,5}). Formlerne returnerer 85.

Brug af matrixkonstanter i formler. I dette eksempel brugte vi = SUM (D9:H(*SEQUENCE(1,5))

Funktionen SEKVENS opbygger udgave af matrixen konstant {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 ind i Afspil celleværdier i D9:H9 og multiplikationsoperator (*). På dette tidspunkt multiplicerer formlen værdierne i den lagrede matrix med de tilsvarende værdier i konstanten. Det er udgave af:

=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 skal funktionen SUM Adderer værdierne, og returnerer 85.

Hvis du vil undgå at bruge den lagrede matrix og holde hele behandlingen 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 (som SAND og FALSK) og fejlværdier som # i/t. Du kan bruge tal i heltal, decimal og videnskabeligt format. Hvis du medtager tekst, skal du sætte den i 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 er dem navne. Navngivne konstanter kan være meget lettere at bruge, og de kan skjule nogle af dine matrixformler fra andre kompleksitet. Navngive en matrixkonstant og bruge det i en formel, skal du gøre følgende:

Gå til formler > definerede navne > Definer navn. Skriv Kvartal1 i feltet navn. I feltet refererer til, Skriv følgende konstant (Husk at skrive klammeparenteserne manuelt):

={"Januar","Februar","Marts"}

Dialogboksen ser nu sådan ud:

Tilføj en navngivet matrixkonstant fra formler > definerede navne > Navnestyring > ny

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

Følgende resultat vises:

Brug en navngivet matrixkonstant i en formel, vil = Kvartal1, hvor Kvartal1 er defineret som = {"Januar", "Februar", "marts"}

Hvis du vil have resultatet af at spilder 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, når du opbygger en balance, kan du basere et fra det aktuelle år med funktionen SEKVENS. Pæne ting om denne funktion er, selvom der vises kun måneden, der er en gyldig dato bagved, som du kan bruge i andre beregninger. Du finder disse eksempler på de navngivne matrixkonstant og Hurtig eksempel datasæt regneark i eksempelprojektmappen.

=Text(Date(YEAR(Today()),SEQUENCE(1,12),1),"mmm")

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

Det bruges funktionen DATE til at oprette en dato baseret på det aktuelle år, SEKVENS opretter en matrixkonstant fra 1 til 12 for januar til December, og funktionen tekst konverterer visningsformat til "mmm" (Jan, Feb, marts, osv.). Hvis du ønsker at få vist hele månedens navn, som januar, skal du bruge "mmmm".

Når du bruger en navngiven konstant som en matrixformel, Husk at angive lighedstegnet, som i = Kvartal1, ikke kun Kvartal1. Hvis du ikke Excel fortolker matrixen som en tekststreng, og formlen virker ikke som forventet. Til sidst skal huske på, at du kan bruge kombinationer af funktioner, tekst og tal. Det hele afhænger af, hvordan kreativ, du vil have.

I følgende eksempler viser nogle af de måder, som du kan anbringe matrixkonstanter til brug i matrixformler. Nogle eksempler på Brug funktionen TRANSPONER til at konvertere rækker til kolonner og omvendt.

  • Flere hvert element i en matrix

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

    Du kan også dividere med (/), tilføje med (+) og trække med (-).

  • Tag kvadratroden af elementerne i en matrix

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

  • Finde kvadratroden af kvadrerede elementer i en matrix

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

  • Transponere en endimensional række

    Angiv =TRANSPOSE(SEQUENCE(1,5))eller =TRANSPOSE({1,2,3,4,5})

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

  • Transponere en endimensional kolonne

    Angiv =TRANSPOSE(SEQUENCE(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

    Angiv =TRANSPOSE(SEQUENCE(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 forklares, hvordan du bruger matrixformler til at oprette en ny matrix ud fra en eksisterende matrix.

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

    Husk at skrive {(venstreparentes) før du skriver 10, og} (slutklammeparentes) når du har skrevet 180, da du opretter en matrix med tal.

    Derefter skal du angive = D9 #eller = D9:I11 i en tom celle. Der vises en cellematrix 3 x 6 med de samme værdier, du ser i D9: D11. Tegnet # kaldes flyttet områdeoperator, og det er Excels metode til at referere til hele matrixformlen i stedet for at de skal du skrive den ud.

    Bruge operatoren spildt område (#) til at referere til en eksisterende matrix

  • Oprette en matrixkonstant ud fra eksisterende værdier

    Du kan tage resultaterne af en matrixformel med spildt og konvertere, til de enkelte komponenter. Markér celle D9 og derefter trykke på F2 for at skifte til redigeringstilstand. Derefter skal du trykke på F9 for at konvertere cellereferencerne til værdier, som Excel derefter konverterer til en matrixkonstant. Når du trykker på Enter, formlen = D9 #, bør nu være = {10,20,30; 40,50,60; 70,80,90}.

  • Tælle antal tegn i et celleområde

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

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

    = SUM (LEN(C9:C13))

    I dette tilfælde returnerer funktionen længde længden af hver tekststreng i hver af cellerne i området. Funktionen SUM derefter tilføjer disse værdier sammen og viser resultatet (66). Hvis du ønsker at få gennemsnitlige antal tegn, kan du bruge:

    = MIDDEL (LEN(C9:C13))

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

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

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

    Lad os se nærmere på formlen, begyndende fra de indre elementer og arbejde udad. Funktionen længde returnerer længden af hvert element i celleområdet D2: D6. Funktionen MAX beregner den største værdi blandt disse elementer, som svarer til den længste tekststreng, som er i celle D3.

    Her er, hvor ting få lidt komplekse. Funktionen Sammenlign beregner forskydning (den relative placering) på den celle, der indeholder den længste tekststreng. Det gør det kræver tre argumenter: en opslagsværdi, matrix et opslag og en resultattype. Funktionen Sammenlign søger matrixen opslag for den angivne opslagsværdien. I dette tilfælde er opslagsværdien den længste tekststreng:

    MAX(LEN(C9:C13)

    og strengen findes i denne matrix:

    LEN(C9:C13)

    Argumentet Søg type er i dette tilfælde 0. Resultattype kan være en 1, 0 eller værdien-1.

    • 1 – returnerer den største værdi, der er mindre end eller lig med opslaget val

    • 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ærdien

    • Hvis du udelader en resultattype, antager Excel 1.

    Til sidst skal funktionen indeks bruger følgende argumenter: en matrix og en række og kolonne tal i denne matrix. Celleområdet C9:C13 indeholder matrixen, funktionen Sammenlign giver celleadressen og argumentet endelige (1) angiver, at værdien stammer fra den første kolonne i matrixen.

    Hvis du ønsker at få indholdet af den mindste tekststreng, vil du erstatte Maks i eksemplet ovenfor med MIN.

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

    I dette eksempel viser, hvordan du kan finde de tre mindste værdier i et område af celler, hvor en matrix med eksempeldata i celler B9:B18has oprettet med: = INT (RANDARRAY(10,1) * 100). Bemærk, at RANDARRAY en flygtig funktion, så du får vist et nyt sæt af tilfældige tal, hver gang, beregner Microsoft Excel.

    Matrixformel til at finde den n'te mindste værdi i Excel: =SMALL(B9#,SEQUENCE(D9))

    Angiv =SMALL(B9#,SEQUENCE(D9), = små (B9:B18, {1; 2; 3})

    Denne formel bruger en matrixkonstant evaluering af funktionen mindste tre gange og returnere de mindste 3 medlemmer i matrix, der er indeholdt i cellerne B9:B18, hvor 3 er en variabel værdi i celle D9. Du kan finde flere værdier, kan du forøge værdien i funktionen SEKVENS, eller tilføje flere argumenter til konstanten. Du kan også bruge flere funktioner med denne formel, som SUM eller gennemsnit. Eksempel:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

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

    Hvis du vil finde den største værdi i et område, kan du erstatte funktionen mindste med funktionen største. Desuden bruges i følgende eksempel funktionerne række og indirekte .

    Skriv = stor (B9 #, række (indirekte ("1:3"))), eller = stor (B9:B18,ROW(INDIRECT("1:3")))

    På dette tidspunkt, kan det hjælpe at lidt for at vide om funktionerne række og indirekte. Du kan bruge funktionen række til at oprette en matrix med fortløbende heltal. Markér en tom f.eks, og Angiv:

    =ROW(1:10)

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

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

    Funktionen indirekte bruger tekststrenge som argumenterne (som er derfor område 1:10 er omsluttet af anførselstegn). Excel justerer tekstværdier ikke, når du indsætter rækker eller Ellers skal du gå matrixformlen. Funktionen række genererer derfor altid matrix af heltal, du vil. Du kan lige så nemt bruge rækkefølge:

    =SEQUENCE(10)

    Lad os undersøge den formel, du tidligere har brugt – = stor (B9 #, række (indirekte ("1:3"))) – begyndende fra de indre parenteser og arbejde udad: feltet indirekte, funktionen returnerer et sæt af værdier, tekst i dette tilfælde værdier 1 til 3. Funktionen række genererer tur. en tre-celle kolonne matrix. Funktionen største bruger værdierne i celleområdet B9:B18 og den evalueres tre gange, én gang til hver reference, der returneres af funktionen række. Hvis du vil søge efter flere værdier, kan du tilføje et større celleområde til funktionen indirekte. Som i den lille eksempler, kan du bruge denne formel med andre funktioner som SUM og gennemsnit.

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

    Funktionen SUM i Excel fungerer ikke, når du forsøger at opsummere et område, der indeholder en fejlværdi, såsom #VALUE! eller # i/t. I dette eksempel viser, hvordan du summere værdierne i det navngivne område Data, der indeholder fejl:

    Brug matrixer til at håndtere fejl. For eksempel =SUM(IF(ISERROR(Data),"",Data) kan lægge det navngivne område Data selvom den indeholder 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

    I dette eksempel minder om den foregående formel, men her returneres antallet af fejlværdier i det navngivne område Data i stedet for 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.

Det være nødvendigt at lægge værdier sammen baseret på betingelser.

Du kan bruge matrixer til at beregne baseret på bestemte betingelser. =SUM(IF(Sales>0,Sales)) kan lægge alle værdier, der er større end 0 i et området med navnet salg.

For eksempel opsummerer denne matrixformel lige positivt heltal i et område med navnet salg, der repræsenterer celler E9:E24 i det foregående eksempel:

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

Funktionen Hvis opretter en matrix med positive og FALSK værdier. Funktionen SUM ignorerer grundlæggende falsk værdierne, fordi 0 + 0 = 0. Det celleområde, du bruger i denne formel kan bestå af en hvilken som helst antal rækker og kolonner.

Du kan også lægge værdier, der opfylder mere end én betingelse. For eksempel beregnes denne matrixformel værdier, der er større end 0 og mindre end 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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 OR-betingelse. For eksempel kan du lægge værdier, der er større end 0 eller mindre end 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Du kan ikke bruge funktionerne og og eller funktionerne i matrixformler direkte, fordi disse funktioner returnerer en enkelt resultat, SAND eller FALSK, og matrixfunktioner kræver matrixer af resultater. Du kan løse problemet ved hjælp af den logik, der vises i den foregående formel. Med andre ord, du udfører matematiske handlinger, som tilføjelse eller multiplikation på værdier, der opfylder eller eller og betingelse.

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.

Denne matrixformel sammenligner værdierne i to celleområder med navnet Minedata og Dinedata og returnerer antallet af forskellene mellem to. Hvis indholdet af de to områder er identiske, returnerer formlen 0. Hvis du vil bruge denne formel, skal celleområderne være den samme størrelse og i den samme dimension. Eksempelvis hvis Minedata er et område af 3 rækker efter 5 kolonner, være Dinedata også 3 rækker efter 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 kan finde lignende eksempler i eksempelprojektmappen i 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 sætte det ind i celle A1 i et tomt regneark.

Salg Person

Bil Type

Tal Solgte

Enhed 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 sedaner for hver sælger, Vælg cellerne E2: E11, skrive formlen = C2: C11 * D2: D11, og tryk derefter 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, omgiver formlen med klammeparentes ({}) i Excel, og indsætter en forekomst af formlen i hver celle i det markerede område. Dette sker meget hurtigt, så hvad du ser i kolonne E er det samlede salgsbeløb for hver biltype for hver sælger. Hvis du vælger E2, og vælg derefter E3, E4 og osv., får du vist, vises den samme formel: {= 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 Excel ganger værdierne i matrixen (celleområdet C2 til D11) og derefter bruges funktionen SUMtil at tilføje totalerne sammen. Resultatet er et hovedtotalen for 1,590,000 i salg. I dette eksempel vises, hvordan effektive denne type formel kan være. Lad os antage, at du har 1.000 rækker af data. Du kan lægge en del af eller alle disse data ved at oprette en matrixformel i en enkelt celle i stedet for at trække formlen gennem de 1.000 rækker.

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

Matrixformler har også følgende fordele:

  • Konsistens    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    Du kan ikke overskrive en komponent i en matrixformel med flere celler. Klik på celle E3 f.eks, og tryk på Delete. Du skal enten markere hele celleområdet (E2 gennem E11) og ændre formlen for hele systemet, eller lad matrixen som den er. Som et mål for ekstra sikkerhed skal du trykke på Ctrl + Skift + Enter for at bekræfte nogen ændringer af formlen.

  • Mindre filstørrelser    Du kan ofte bruge en enkelt matrixformel i stedet for flere mellemliggende formler. I projektmappen bruges der f.eks. kun én matrixformel til at beregne resultaterne i kolonne E. Hvis du havde brugt almindelige formler, f.eks. =C2*D2; C3*D3; C4*D4...), skulle du have brugt 11 forskellige formler til at beregne de samme resultater.

Generelt, bruge matrixformler formel standardsyntaksen. De alle begynder med et lighedstegn (=), og du kan bruge de fleste af de indbyggede Excel-funktioner i dine matrixformler. Vigtige forskellen er, når du bruger en matrixformel, du trykker på Ctrl + Skift + Enter for at angive formlen. Når du gør dette, at Excel angiver din matrixformel med klammeparenteser – Hvis du skriver klammeparenteserne manuelt, konverteres formlen til en tekststreng og den virker ikke.

Matrixfunktioner kan være en effektiv måde til at oprette komplekse formler. Matrixen formlen =SUM(C2:C11*D2:D11) er den samme som dette: =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 at angive en matrixformel. Dette gælder for både én celle og med flere celler formler.

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.

  • For at slette en matrixformel, skal du markere hele formlen området (for eksempel E2: E11), og derefter trykke på Delete.

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

Nogle gange, skal du kan udvide en matrixformel. Vælg den første celle i eksisterende matrix område, og Fortsæt, indtil du har markeret det hele område, du vil udvide formlen til. Tryk på F2 for at redigere formlen, og derefter trykke på CTRL + SKIFT + ENTER for at bekræfte formlen, når du har justeret formel området. Tasten er at markere hele det celleområde, startende med den øverste venstre celle i matrix. Den øverste venstre celle er det, der bliver redigeret.

Matrixformler er fantastiske, men der er også ulemper:

  • Du kan undertiden glemmer at trykke på Ctrl + Skift + Enter. Det kan ske, at lige de mest erfarne brugere af Excel. Husk at trykke på denne tastekombination, når du indtaster eller redigerer en matrixformel.

  • Andre brugere af projektmappen kan ikke forstår dine formler. I praksis gennemgås matrixformler generelt ikke i et regneark. Derfor, hvis andre har brug at ændre dine projektmapper, du skal enten undgå matrixformler eller Sørg for, at personerne, der kender til en hvilken som helst matrixformler og forstå, hvordan du kan ændre dem, hvis det er nødvendigt.

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

På nuværende tidspunkt kender du, skal du trykke på Ctrl + Skift + Enter, når du opretter matrixformler. Fordi matrixkonstanter er en komponent i matrixformler, skal omslutte du konstanter med klammeparenteser ved manuelt at skrive dem. Derefter kan du bruge Ctrl + Skift + Enter til at skrive 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}. Den første række er 1, 2, 3 og 4 i matrixen to række, og den anden række er 5, 6, 7 og 8. Et enkelt semikolon adskiller 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 du skal skrive Klammeparenteserne ({}), og føjer Excel 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 rent faktisk skrive dem. Husk også, når du føjer en konstant til en matrixformel, du trykker på Ctrl + Skift + Enter for at angive 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})

Du kan prøve dette, kopiere funktionen, Markér en tom celle i din projektmappe, indsætte formlen i formellinjen og derefter trykke på Ctrl + Skift + Enter. Du får vist det samme resultat, som du gjorde i den tidligere opgave, bruges matrixformlen:

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

Matrixkonstanter kan indeholde tal, tekst, logiske værdier, f.eks. SAND og FALSK, og fejlværdier, f.eks. #I/T. Du kan bruge tal i formaterne heltal, decimal og videnskabelige formater. Hvis du medtager tekst, skal du angive teksten i 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 den bedste måde at bruge matrixkonstanter er dem navne. Navngivne konstanter kan være meget lettere at bruge, og de kan skjule nogle af dine matrixformler fra andre kompleksitet. Navngive en matrixkonstant og bruge det i en formel, skal du gøre følgende:

  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 kan ikke adskilles med stort tegn. Hvis du udelader et komma eller semikolon, eller hvis du lægger i det forkerte sted, matrixkonstanten kan ikke oprettes korrekt, eller du muligvis vist en advarselsmeddelelse.

  • Du har muligvis markeret et celleområde, der ikke stemmer overens med antallet af elementer i konstanten. Hvis du f.eks. markerer en kolonne med seks celler til brug med en konstant med fem celler, vises fejlværdien #I/T i den tomme celle. Hvis du markerer for få celler, vil Excel udelade de værdier, der ikke har en tilsvarende celle.

I følgende eksempler viser nogle af de måder, som du kan anbringe matrixkonstanter til brug i matrixformler. Nogle eksempler på 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 indsætter denne matrix af tal i celleområdet C8:E10 ved hjælp af en matrixformel. I regnearket ser C8 til og med E10 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 3 x 3 matrix af celler i cellerne C1 til og med E3 med de samme værdier, du ser i C8 til og med E10.

Oprette en matrixkonstant ud fra eksisterende værdier

  1. Med cellerne C1: C3 har valgt, 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 skulle 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 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 af 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 bruger funktionen længde længden af hver tekststreng i hver af cellerne i området. Funktionen SUM derefter tilføjer disse værdier sammen og viser resultatet (66).

Finde n mindste værdier i et område

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

  1. Du kan angive nogle vilkårlige tal i celler A1:A11.

  2. Markér cellerne C1 til og C3. Denne række celler skal indeholde de resultater, der returneres af matrixformlen.

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

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

Denne formel bruger en matrixkonstant for at evaluere funktionen mindste tre gange og returnerer den mindste (1), andet mindste (2) og tredje mindste (3) medlemmer i matrixen, der er indeholdt i cellerne a1: a10 til at søge efter flere værdier, kan du føje flere argumenter til den konstant. Du kan også bruge flere funktioner med denne formel, som SUM eller gennemsnit. Eksempel:

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

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

Finde n 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. Marker cellerne D1 til D3.

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

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

På dette tidspunkt, kan det hjælpe at lidt for at vide om de række og indirekte funktioner. Du kan bruge funktionen række til at oprette en matrix med fortløbende heltal. Vælg en tom kolonne af 10 celler i øvelsesprojektmappen, angive denne matrixformel, og tryk derefter på Ctrl + Skift + Enter:

=RÆKKE(1:10)

Formlen opretter en kolonne med ti efterfølgende heltal. Hvis du vil se et potentielt problem, kan du indsætte en række over det område, der indeholder matrixformlen, dvs. over række 1. Excel justerer rækkereferencerne, og formlen opretter heltal fra 2 til 11. Du kan løse det problem ved at indsætte funktionen INDIREKTE i 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 tidligere har brugt – = stor (A5:A14,ROW(INDIRECT("1:3"))) – begyndende fra de indre parenteser og arbejde udad: funktionen indirekte returnerer et sæt af værdier, tekst i dette tilfælde værdierne 1 til 3. Funktionen række genererer tur. en tre-celle kolonner matrix. Funktionen stor bruger værdierne i celleområdet A5:A14, og det evalueres tre gange, én gang til hver reference, der returneres af funktionen række . Værdierne 3200, 2700 og 2000 returneres til matrixen tre celle kolonner. Hvis du vil søge efter flere værdier, kan du tilføje et større celleområde til funktionen indirekte .

Med tidligere eksempler, kan som du bruge denne formel med andre funktioner som SUM og gennemsnit.

Finde den længste tekststreng i et celleområde

Gå tilbage til den 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 "bunch of cells that" vises.

Lad os se nærmere på formlen, begyndende fra de indre elementer og arbejde 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:

(MAKS (LEN(A2:A6))

og strengen findes i denne matrix:

LEN(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.

Til sidst skal funktionen indeks tager følgende argumenter: en matrix og en række og kolonne tal i denne matrix. Celleområdet a2: a6 indeholder matrixen, funktionen Sammenlign indeholder celleadressen, og det sidste argument (1) angiver, at værdien stammer 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, hvis du forsøger at opsummere et område, der indeholder fejlværdier, f.eks. #I/T. I dette eksempel kan du se, hvordan du kan opsummere 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 funktionerne returnerer et enkelt resultat, dvs. enten SAND eller FALSK, og matrixfunktioner kræver resultater i en matrix. Du kan løse problemet ved hjælp af den logik, der vises i den foregående formel. Du kan med andre ord foretage matematiske handlinger, f.eks. addition eller multiplikation, på de værdier, der opfylder betingelsen ELLER eller OG.

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

Denne matrixformel sammenligner værdierne i to områder med navnene MineData og DineData og returnerer antallet af forskelle mellem de to områder. Hvis indholdet af de to områder er identisk, returneres 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))

Bekræftelse

Dele af denne artikel er baseret på en serie af Excel-Superbrugere kolonner skrevet af Colin Wilcox og tilpasses fra kapitler 14 og 15 i Excel 2002-formler, et adressekartotek, der er 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

Dynamisk matrixformler kontra æ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.

×