Retningslinjer og eksempler på matriseformler

Retningslinjer og eksempler på matriseformler

En matriseformel er en formel som kan utføre flere beregninger på ett eller flere elementer i en matrise. Du kan tenke på en matrise som en rad eller kolonne med verdier, eller en kombinasjon av rader og kolonner med verdier. Matriseformler kan returnere flere resultater eller ett enkelt resultat.

Fra og med september 2018-oppdateringen for Microsoft 365 vil alle formler som kan returnere flere resultater,automatisk søle dem ned eller over i nærliggende celler. Denne endringen i virkemåten er også ledsaget av flere nye dynamiske matrisefunksjoner. Dynamiske matriseformler, enten de bruker eksisterende funksjoner eller de dynamiske matrisefunksjonene, trenger bare å legges inn i én enkelt celle, og deretter bekreftes ved å trykke ENTER. Tidligere eldre matriseformler krever først at du merker hele utdataområdet og deretter bekrefter formelen med CTRL+SKIFT+ENTER. De kalles ofte CSE-formler.

Du kan bruke matriseformler til å utføre kompliserte oppgaver, for eksempel:

  • Opprett raskt eksempeldatasett.

  • Tell antall tegn i et celleområde.

  • Summer bare tall som oppfyller bestemte betingelser, for eksempel de laveste verdiene i et område, eller tall som faller mellom en øvre og nedre grense.

  • Summer hver N.N.-verdi i et verdiområde.

Eksemplene nedenfor viser deg hvordan du oppretter matriseformler med flere celler og én celle. Der det er mulig, har vi tatt med eksempler med noen av de dynamiske matrisefunksjonene, i tillegg til eksisterende matriseformler som er angitt som både dynamiske og eldre matriser.

Last ned eksemplene våre

Last ned en eksempelarbeidsbok med alle eksemplene på matriseformler i denne artikkelen.

Denne øvelsen viser deg hvordan du bruker flercellede og encellede matriseformler til å beregne et sett med salgstall. De første trinnene i fremgangsmåten bruker en flercellet formel til å beregne et sett med delsummer. De andre trinnene bruker en encellet formel til å beregne en totalsum.

  • Flercellet matriseformel

    Flercellet matrisefunksjon i celle H10 =F10:F19*G10:G19 for å beregne antall biler solgt etter enhetspris

  • Her beregner vi totalt salg av coupes og sedans for hver selger ved å skrive inn =F10:F19*G10:G19 i celle H10.

    Når du trykker ENTER, ser du at resultatene går ned til cellene H10:H19. Legg merke til at spillområdet er uthevet med en kantlinje når du merker en celle innenfor spillområdet. Du legger kanskje også merke til at formlene i cellene H10:H19 er nedtonet. De er bare der for referanse, så hvis du vil justere formelen, må du merke celle H10, der hovedformelen finnes.

  • Matriseformel med én celle

    Matriseformel med én celle for å beregne en totalsum med =SUMMER(F10:F19*G10:G19)

    Skriv inn eller kopier og lim inn =SUMMER(F10:F19*G10:G19)i celle H20 i eksempelarbeidsboken, og trykk deretter ENTER.

    I dette tilfellet Excel verdiene i matrisen (celleområdet F10 til G19), og bruker deretter SUMMER-funksjonen til å legge sammen totalene. Resultatet er en totalsum på kr 15 900 000 for salg.

    Dette eksemplet viser hvor effektiv denne typen formel kan være. La oss for eksempel si at du har 1 000 rader med data. Du kan summere deler av eller alle dataene ved å opprette en matriseformel i én celle i stedet for å dra formelen ned gjennom de 1 000 radene. Legg også merke til at encellede formelen i celle H20 er helt uavhengig av formelen med flere celler (formelen i cellene H10 til og med H19). Dette er en annen fordel med å bruke matriseformler \endash fleksibilitet. Du kan endre de andre formlene i kolonne H uten at det påvirker formelen i H20. Det kan også være god praksis å ha uavhengige totalsummer som dette, da det bidrar til å validere nøyaktigheten til resultatene.

  • Dynamiske matriseformler gir også disse fordelene:

    • Konsekvens    Hvis du klikker noen av cellene fra H10 nedover, ser du den samme formelen. Denne konsekvensen kan bidra til å sikre større presisjon.

    • Trygghet    Du kan ikke overskrive en komponent i en flercellet matriseformel. Klikk for eksempel celle H11, og trykk del. Excel ikke matrisens utdata. Hvis du vil endre den, må du merke cellen øverst til venstre i matrisen eller celle H10.

    • Mindre filstørrelser    Du kan ofte bruke én matriseformel i stedet for flere mellomliggende formler. Bilsalgseksempelet bruker for eksempel én matriseformel til å beregne resultatene i kolonne E. Hvis du hadde brukt standardformler som =F10*G10, F11*G11, F12*G12 og så videre, ville du ha brukt 11 forskjellige formler til å beregne de samme resultatene. Det er ikke en stor avtale, men hva om du hadde tusenvis av rader å summere? Da kan det utgjøre en stor forskjell.

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

    • Søl    Dynamiske matriseformler vil automatisk gå over til utdataområdet. Hvis kildedataene er i en Excel tabell, vil de dynamiske matriseformlene automatisk endre størrelse når du legger til eller fjerner data.

    • #SPILL! feil    Dynamiske matriser introduserte #SPILL! feil, som indikerer at det tiltenkte søleområdet av en eller annen grunn er blokkert. Når du løser blokkeringen, vil formelen automatisk søle.

Matrisekonstanter er en komponent i matriseformler. Du oppretter matrisekonstanter ved å angi en liste med elementer og deretter omslutte listen med klammeparenteser manuelt ({ }), slik:

={1,2,3,4,5} eller ={"Januar","Februar","Mars"}

Hvis du skiller elementene med semikolon, oppretter du en vannrett matrise (en rad). Hvis du skiller elementene med omvendt skråstrek, oppretter du en loddrett matrise (en kolonne). Hvis du vil opprette en todimensjonal matrise, skiller du elementene i hver rad med komma og skiller hver rad med semikolon.

Fremgangsmåtene nedenfor gir deg øvelse i å opprette vannrette, loddrette og todimensjonale konstanter. Vi viser eksempler ved hjelp av SEKVENS-funksjonen til å generere matrisekonstanter automatisk, i tillegg til manuelle matrisekonstanter.

  • Opprett vannrette konstanter

    Bruk arbeidsboken fra de tidligere eksemplene, eller opprett en ny arbeidsbok. Merk en tom celle, og skriv inn =SEKVENS(1,5). SEKVENS-funksjonen bygger en 1 rad og 5 kolonnematrise på samme måte som ={1,2,3,4,5}. Følgende resultat vises:

    Opprette en vannrett matrisekonstant med =SEKVENS(1;5) eller ={1;2;3;4;5}

  • Opprett loddrette konstanter

    Merk en tom celle med rom under, og skriv inn =SEKVENS(5)eller ={1;2;3;4;5}. Følgende resultat vises:

    Opprette en loddrett matrisekonstant med =SEKVENS(5) eller ={1;2;3;4;5}

  • Opprett todimensjonale konstanter

    Merk en tom celle med plass til høyre og under den, og skriv inn =SEKVENS(3,4). Følgende resultat vises:

    Opprette en matrisekonstant med 3 rader og 4 kolonner med =SEKVENS(3;4)

    Du kan også angi: eller ={1;2;3;4;5;6;7;8;9;10;11;12}, men du bør ta hensyn til hvor du setter semikolon kontra komma.

    Som du kan se, gir SEKVENS-alternativet betydelige fordeler i forhold til manuell innskriving av matrisekonstantverdier. Det sparer deg hovedsakelig tid, men det kan også bidra til å redusere feil fra manuell inntasting. Det er også enklere å lese, spesielt siden semikolon kan være vanskelig å skille fra kommaskilletegnene.

Her er et eksempel som bruker matrisekonstanter som en del av en større formel. Gå til Konstant i et formelregneark i eksempelarbeidsboken, eller opprett et nytt regneark.

I celle D9 skrev vi inn =SEKVENS(1;5;3;1),men du kan også skrive inn 3, 4, 5, 6 og 7 i cellene A9:H9. Det er ikke noe spesielt med det bestemte nummervalget, vi valgte bare noe annet enn 1–5 for differensiering.

I celle E11 skriver du inn =SUMMER(D9:H9*SEKVENS(1;5))eller =SUMMER(D9:H9*{1;2;3;4;5}). Formlene returnerer 85.

Bruk matrisekonstanter i formler. I dette eksemplet brukte vi =SUMMER(D9:H(*SEKVENS(1;5))

SEKVENS-funksjonen bygger det samme som matrisekonstanten {1,2,3,4,5}. Fordi Excel utfører operasjoner på uttrykk som er omsluttet av parenteser først, er de neste to elementene som spilles av, celleverdiene i D9:H9 og multiplikasjonsoperatoren (*). På dette tidspunktet multipliserer formelen verdiene i den lagrede matrisen med tilsvarende verdier i konstanten. Dette tilsvarer følgende:

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

Til slutt legger SUMMER-funksjonen sammen verdiene og returnerer 85.

Hvis du vil unngå å bruke den lagrede matrisen og beholde operasjonen helt i minnet, kan du erstatte den med en annen matrisekonstant:

=SUMMER(SEKVENS(1;5;3;1)*SEKVENS(1;5))eller =SUMMER({3;4;5;6;7}*{1;2;3;4;5})

Elementer som du kan bruke i matrisekonstanter

  • Matrisekonstanter kan inneholde tall, tekst, logiske verdier (for eksempel SANN og USANN) og feilverdier som #N/A. Du kan bruke tall i heltalls-, desimal- og vitenskapelige formater. Hvis du inkluderer tekst, må du omslutte den med anførselstegn («tekst»).

  • Matrisekonstanter kan ikke inneholde flere matriser, formler eller funksjoner. Med andre ord kan de inneholde bare tekst eller tall som er skilt med semikolon eller omvendt skråstrek. En advarsel vises når du angir en formel som {1;2;A1:D4} eller {1;2;SUMMER(Q2:Z8)}. Numeriske verdier kan heller ikke inneholde prosenttegn, valutategn eller parenteser.

En av de beste måtene å bruke matrisekonstanter på, er å gi dem et navn. Konstanter som har navn, kan være mye enklere å bruke, og de kan skjule noe av kompleksiteten til matriseformlene dine for andre. Du kan gi en matrisekonstant navn og bruke den i en formel ved å gjøre følgende:

Gå til Formler >Definerte navn >Definer navn. Skriv inn Kvartal1 i Navn-boksen. Skriv inn konstanten nedenfor i boksen Refererer til (husk å skrive inn klammeparentesen manuelt):

={"Januar";"Februar";"Mars"}

Dialogboksen skal nå se slik ut:

Legge til en navngitt matrisekonstant fra Formler > Definerte navn > Navnebehandling > Ny

Klikk OK, velg deretter en rad med tre tomme celler, og skriv inn =Kvartal1.

Følgende resultat vises:

Bruk en navngitt matrisekonstant i en formel, for eksempel =Kvartal1, der Kvartal1 er definert som ={"Januar","Februar","Mars"}

Hvis du vil at resultatene skal søle loddrett i stedet for vannrett, kan du bruke =TRANSPONER(Kvartal1).

Hvis du vil vise en liste over 12 måneder, som du kanskje bruker når du bygger et regnskapsoppgjør, kan du basere ett av gjeldende år med SEKVENS-funksjonen. Det fine med denne funksjonen er at selv om bare måneden vises, er det en gyldig dato bak den som du kan bruke i andre beregninger. Du finner disse eksemplene på regnearkene Navngitt matrisekonstant og Hurtigeksempeldatasett i eksempelarbeidsboken.

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

Bruk en kombinasjon av TEKST-, DATO-, ÅR-, IDAG- og SEKVENS-funksjonene til å bygge en dynamisk liste over 12 måneder

Dette bruker DATO-funksjonen til å opprette en dato basert på gjeldende år, SEKVENS oppretter en matrisekonstant fra 1 til 12 for januar til desember, og deretter konverterer TEKST-funksjonen visningsformatet til «mmm» (jan, februar, mar og så videre). Hvis du vil vise hele månedsnavnet, for eksempel januar, bruker du «mmmm».

Når du bruker en navngitt konstant som en matriseformel, må du huske å skrive inn likhetstegnet, som i =Kvartal1, ikke bare Kvartal1. Hvis du ikke gjør det, tolker Excel matrisen som en streng med tekst, og formelen vil ikke fungere som forventet. Husk til slutt at du kan bruke kombinasjoner av funksjoner, tekst og tall. Alt avhenger av hvor kreativ du vil ha det.

Eksemplene nedenfor viser noen av måtene du kan bruke matrisekonstanter på i matriseformler. Noen av eksemplene bruker FUNKSJONEN TRANSPONER til å konvertere rader til kolonner og omvendt.

  • Flere hvert element i en matrise

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

    Du kan også dele med (/), legge til med (+), og trekke fra med (-).

  • Kvadrer elementene i en matrise

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

  • Finne kvadratroten av kvadrerte elementer i en matrise

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

  • Transponer en endimensjonal rad

    Skriv inn =TRANSPONER(SEKVENS(1;5))eller =TRANSPONER({1;2;3;4;5})

    Selv om du skrev inn en vannrett matrisekonstant, konverterer funksjonen TRANSPONER matrisekonstanten til en kolonne.

  • Transponer en endimensjonal kolonne

    Skriv inn =TRANSPONER(SEKVENS(5;1))eller =TRANSPONER({1;2;3;4;5})

    Selv om du skrev inn en loddrett matrisekonstant, konverterer TRANSPONER-funksjonen konstanten til en rad.

  • Transponer todimensjonale konstanter

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

    TRANSPONER-funksjonen konverterer hver rad til en serie med kolonner.

Denne delen inneholder eksempler på enkle matriseformler.

  • Opprett matriser fra eksisterende verdier

    Følgende eksempel forklarer hvordan du bruker matriseformler til å opprette en ny matrise fra en eksisterende matrise.

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

    Pass på å skrive inn { (venstre klammeparenteparente) før du skriver inn 10, og } (høyre klammeparente) etter at du har skrevet inn 180, fordi du oppretter en matrise med tall.

    Skriv deretter inn =D9#eller =D9:I11 i en tom celle. En 3 x 6-matrise med celler vises med de samme verdiene som du ser i D9:D11. #-tegnet kalles operatoren for oversølt område ,og det er Excel måte å referere til hele matriseområdet på i stedet for å måtte skrive det ut.

    Bruk den oversølte områdeoperatoren (#) til å referere til en eksisterende matrise

  • Opprett matrisekonstanter fra eksisterende verdier

    Du kan ta resultatene av en oversølt matriseformel og konvertere den til komponentdelene. Merk celle D9, og trykk deretter F2 for å bytte til redigeringsmodus. Deretter trykker du F9 for å konvertere cellereferansene til verdier, som Excel konverteres til en matrisekonstant. Når du trykker ENTER, skal formelen= =D9#, nå være ={10;20;30;40;50;60;70;80;90}.

  • Tell tegn i et celleområde

    Følgende eksempel viser deg hvordan du teller antall tegn i et celleområde. Dette omfatter mellomrom.

    Telle totalt antall tegn i et område og andre matriser for å arbeide med tekststrenger

    =SUMMER(LENGDE(C9:C13))

    I dette tilfellet returnerer LENGDE-funksjonen lengden på hver tekststreng i hver av cellene i området. SUMMER-funksjonen legger deretter sammen disse verdiene og viser resultatet (66). Hvis du vil ha gjennomsnittlig antall tegn, kan du bruke følgende:

    =GJENNOMSNITT(LENGDE(C9:C13))

  • Innhold i den lengste cellen i området C9:C13

    =INDEKS(C9:C13;SAMMENLIGNE(MAKS(LENGDE(C9:C13)),LENGDE(C9:C13),0),1)

    Denne formelen fungerer bare når et dataområde inneholder én kolonne med celler.

    La oss ta en nærmere titt på formelen, fra de indre elementene og arbeide utover. LENGDE-funksjonen returnerer lengden på hvert av elementene i celleområdet D2:D6. STØRST-funksjonen beregner den største verdien blant disse elementene, som tilsvarer den lengste tekststrengen, som er i celle D3.

    Her begynner det å bli litt komplisert. SAMMENLIGNE-funksjonen beregner forskyvningen (den relative posisjonen) til cellen som inneholder den lengste tekststrengen. Du gjør dette ved å bruke tre argumenter: en oppslagsverdi, en oppslagsmatrise og en sammenligningstype. SAMMENLIGNE-funksjonen søker i oppslagsmatrisen etter den angitte oppslagsverdien. I dette tilfellet er oppslagsverdien den lengste tekststrengen:

    MAKS(LENGDE(C9:C13)

    og den aktuelle strengen finnes i følgende matrise:

    LENGDE(C9:C13)

    Argumentet samsvarstype i dette tilfellet er 0. Samsvarstypen kan være en 1-, 0- eller -1-verdi.

    • 1 – returnerer den største verdien som er mindre enn eller lik oppslagsverdien

    • 0 – returnerer den første verdien nøyaktig lik oppslagsverdien

    • -1 – returnerer den minste verdien som er større enn eller lik den angitte oppslagsverdien

    • Hvis du utelater en sammenligningstype, forutsettes verdien 1.

    Til slutt tar INDEKS-funksjonen disse argumentene: en matrise og et rad- og kolonnenummer i matrisen. Celleområdet C9:C13 inneholder matrisen, SAMMENLIGNE-funksjonen gir celleadressen, og det endelige argumentet (1) angir at verdien kommer fra den første kolonnen i matrisen.

    Hvis du vil hente innholdet i den minste tekststrengen, erstatter du MAKS i eksemplet ovenfor med MIN.

  • Finn de n minste verdiene i et område

    Dette eksemplet viser hvordan du finner de tre minste verdiene i et celleområde, der en matrise med eksempeldata i cellene B9:B18 er opprettet med: =INT(RANDARRAY(10,1)*100). Vær oppmerksom på at TILFELDIGMATRIKK er en flyktig funksjon, slik at du får et nytt sett med tilfeldige tall hver gang Excel beregner.

    Excel matriseformel for å finne den N.n.minste verdien: =N.,N.))

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

    Denne formelen bruker en matrisekonstant til å evaluere FUNKSJONEN SMÅ tre ganger og returnere de minste tre medlemmene i matrisen som finnes i cellene B9:B18, der 3 er en variabel verdi i celle D9. Hvis du vil finne flere verdier, kan du øke verdien i SEKVENS-funksjonen eller legge til flere argumenter i konstanten. Du kan også bruke tilleggsfunksjoner med denne formelen, for eksempel SUMMER eller GJENNOMSNITT. For eksempel:

    =SUMMER(LITEN(B9#;SEKVENS(D9))

    =GJENNOMSNITT(LITEN(B9#;SEKVENS(D9))

  • Finn de n største verdiene i et område

    Hvis du vil finne de største verdiene i et område, kan du erstatte FUNKSJONEN SMÅ med FUNKSJONEN STOR. I tillegg bruker eksemplet nedenfor RAD- og INDIREKTE-funksjonene.

    Skriv inn =STOR(B9#;RAD(INDIREKTE("1:3")))eller =STOR(B9:B18;RAD(INDIREKTE("1:3")))

    På dette tidspunktet kan det være nyttig å vite litt om RAD- og INDIREKTE-funksjonene. Du kan bruke RAD-funksjonen til å opprette en matrise med etterfølgende heltall. Velg for eksempel en tom, og skriv inn:

    =RAD(1:10)

    Formelen oppretter en kolonne med 10 etterfølgende heltall. Du kan se et potensielt problem hvis du setter inn en rad over området som inneholder matriseformelen (det vil si over rad 1). Excel justerer radreferansene, og formelen genererer nå heltall fra 2 til 11. Du kan løse dette problemet ved å legge til INDIREKTE-funksjonen i formelen:

    =RAD(INDIREKTE("1:10"))

    INDIREKTE-funksjonen bruker tekststrenger som argumentene (det er derfor området 1:10 er omgitt av anførselstegn). Tekstverdier justeres ikke når du setter inn rader eller flytter matriseformelen på andre måter. Som et resultat genererer alltid RAD-funksjonen ønsket matrise med heltall. Du kan like enkelt bruke SEKVENS:

    =SEKVENS(10)

    La oss undersøke formelen du brukte tidligere – =STOR(B9#,RAD(INDIREKTE("1:3"))) – og starter fra de indre parentesene og arbeider utover: INDIREKTE-funksjonen returnerer et sett med tekstverdier, i dette tilfellet verdiene fra 1 til 3. RAD-funksjonen genererer i sin tur en kolonnematrise med tre celler. FUNKSJONEN STOR bruker verdiene i celleområdet B9:B18, og den evalueres tre ganger, én gang for hver referanse som returneres av RAD-funksjonen. Hvis du vil finne flere verdier, legger du til et større celleområde i INDIREKTE-funksjonen. Til slutt, som med små eksempler, kan du bruke denne formelen med andre funksjoner, for eksempel SUMMER og GJENNOMSNITT.

  • Summer et område som inneholder feilverdier

    SUMMER-funksjonen i Excel fungerer ikke når du prøver å summere et område som inneholder en feilverdi, for eksempel #VALUE! eller #N/A. Dette eksemplet viser deg hvordan du summerer verdiene i et område kalt Data som inneholder feil:

    Bruk matriser til å håndtere feil. =SUMMER(HVIS(ERFEIL(Data),",",Data) summerer for eksempel området kalt Data selv om det inneholder feil, for eksempel #VALUE! eller #NA!.

  • =SUMMER(HVIS(ERFEIL(Data);"";Data))

    Formelen oppretter en ny matrise som inneholder de opprinnelige verdiene minus eventuelle feilverdier. Fra og med de indre funksjonene og arbeider utover, søker ERFEIL-funksjonen i celleområdet (Data) etter feil. HVIS-funksjonen returnerer en bestemt verdi hvis en betingelse du angir, evalueres til SANN og en annen verdi hvis den evalueres til USANN. I dette tilfellet returnerer den tomme strenger ("") for alle feilverdier fordi de evalueres til SANN, og returnerer de gjenstående verdiene fra området (Data) fordi de evalueres til USANN, noe som betyr at de ikke inneholder feilverdier. SUMMER-funksjonen beregner deretter totalsummen for den filtrerte matrisen.

  • Tell antall feilverdier i et område

    Dette eksemplet er som den forrige formelen, men det returnerer antall feilverdier i et område kalt Data i stedet for å filtrere dem ut:

    =SUMMER(HVIS(ERFEIL(Data);1;0))

    Denne formelen oppretter en matrise som inneholder verdien 1 for cellene som inneholder feil, og verdien 0 for cellene som ikke inneholder feil. Du kan forenkle formelen og få de samme resultatene ved å fjerne det tredje argumentet for HVIS-funksjonen, på følgende måte:

    =SUMMER(HVIS(ERFEIL(Data);1))

    Hvis du ikke angir argumentet, returnerer HVIS-funksjonen USANN hvis en celle ikke inneholder en feilverdi. Du kan forenkle formelen enda mer på følgende måte:

    =SUMMER(HVIS(ERFEIL(Data)*1))

    Denne versjonen fungerer fordi SANN*1=1 og USANN*1=0.

Du trenger kanskje å summere verdier basert på betingelser.

Du kan bruke matriser til å beregne basert på bestemte betingelser. =SUMMER(HVIS(>0;Salg)) summerer alle verdier som er større enn 0 i et område kalt Salg.

Denne matriseformelen summerer for eksempel bare de positive heltallene i et område kalt Salg, som representerer cellene E9:E24 i eksemplet ovenfor:

=SUMMER(HVIS(Salg>0;Salg))

HVIS-funksjonen oppretter en matrise med positive og usanne verdier. SUMMER-funksjonen ignorerer hovedsakelig de usanne verdiene fordi 0+0=0. Celleområdet du bruker i denne formelen, kan bestå av et hvilket som helst antall rader og kolonner.

Du kan også summere verdier som oppfyller mer enn én betingelse. Denne matriseformelen beregner for eksempel verdier som er større enn 0 OG mindre enn 2500:

=SUMMER((>0)*(<2500)*(Salg))

Husk at denne formelen returnerer en feil hvis området inneholder én eller flere ikke-numeriske celler.

Du kan også opprette matriseformler som bruker en type ELLER-betingelse. Du kan for eksempel summere verdier som er større enn 0 ELLER mindre enn 2500:

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

Du kan ikke bruke OG- og ELLER-funksjonene direkte i matriseformler fordi disse funksjonene returnerer ett resultat, enten SANN eller USANN, og matrisefunksjoner krever matriser med resultater. Du kan omgå dette problemet ved å bruke logikken som er vist i forrige formel. Du utfører med andre ord matematiske operasjoner, for eksempel addisjon eller multiplikasjon på verdier som oppfyller ELLER- eller OG-betingelsen.

Dette eksemplet viser hvordan du fjerner nuller fra et område når du trenger å finne gjennomsnittet av verdiene i det aktuelle området. Formelen bruker et dataområde kalt Salg:

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

HVIS-funksjonen oppretter en matrise med verdier som ikke er lik 0, og sender deretter de aktuelle verdiene videre til GJENNOMSNITT-funksjonen.

Denne matriseformelen sammenligner verdiene i to celleområder kalt MineData og DineData og returnerer antallet forskjeller mellom de to. Hvis innholdet i de to områdene er identiske, returnerer formelen 0. Hvis du vil bruke denne formelen, må celleområdet ha samme størrelse og samme dimensjon. Hvis For eksempel MyData er et område på 3 rader og 5 kolonner, må DineData også være 3 rader og 5 kolonner:

=SUMMER(HVIS(MyData=DineData;0;1))

Formelen oppretter en ny matrise i samme størrelse som områdene du sammenligner. HVIS-funksjonen fyller matrisen med verdiene 0 og 1 (0 for celler som ikke samsvarer, og 1 for identiske celler). SUMMER-funksjonen returnerer deretter summen av verdiene i matrisen.

Du kan forenkle formelen på følgende måte:

=SUMMER(1*(MyData<>YourData))

På samme måte som formelen som teller feilverdier i et område, fungerer denne formelen fordi SANN*1=1 og USANN*1=0.

Denne matriseformelen returnerer radnummeret til maksimumsverdien i et område med én kolonne, kalt Data:

=MIN(HVIS(Data=STØRST(Data);RAD(Data);""))

HVIS-funksjonen oppretter en ny matrise som samsvarer med området kalt Data. Hvis en tilsvarende celle inneholder maksimumsverdien i området, inneholder matrisen radnummeret. Hvis ikke inneholder matrisen en tom streng (""). MIN-funksjonen bruker den nye matrisen som sitt andre argument og returnerer den minste verdien, som samsvarer med radnummeret til maksimumsverdien i Data. Hvis området kalt Data inneholder identiske maksimumsverdier, returnerer formelen raden til den første verdien.

Hvis du vil returnere den faktiske celleadressen til en maksimumsverdi, bruker du følgende formel:

=ADRESSE(MIN(HVIS(Data=STØRST(Data);RAD(Data);""));KOLONNE(Data))

Du finner lignende eksempler i eksempelarbeidsboken i regnearket Forskjeller mellom datasett.

Denne øvelsen viser deg hvordan du bruker flercellede og encellede matriseformler til å beregne et sett med salgstall. De første trinnene i fremgangsmåten bruker en flercellet formel til å beregne et sett med delsummer. De andre trinnene bruker en encellet formel til å beregne en totalsum.

  • Flercellet matriseformel

Kopier hele tabellen nedenfor, og lim den inn i celle A1 i et tomt regneark.

Selger

Biltype

Solgt nummer

Enhetspris

Totalt salg

Barnhill

Sedan

5

33000

Coupe

4

37000

Ingle

Sedan

6

24000

Coupe

8

21000

Jordan

Sedan

3

29000

Coupe

1

31000

Pica

Sedan

9

24000

Coupe

5

37000

Sanchez

Sedan

6

33000

Coupe

8

31000

Formel (totalsum)

Totalsum

'=SUMMER(C2:C11*D2:D11)

=SUMMER(C2:C11*D2:D11)

  1. Hvis du vil se Totalt salg av coupes og sedans for hver selger, merker du cellene E2:E11, skriver inn formelen =C2:C11*D2:D11, og deretter trykker du CTRL+SKIFT+ENTER.

  2. Hvis du vil se totalsummen for alle salg, merker du celle F11, skriver inn formelen =SUMMER(C2:C11*D2:D11)og trykker deretter CTRL+SKIFT+ENTER.

Når du trykker CTRL+SKIFT+ENTER,Excel omslutter formelen med klammeparenter ({ }) og setter inn en forekomst av formelen i hver celle i det merkede området. Dette skjer svært raskt, så det du ser i kolonne E, er det totale salgsbeløpet for hver biltype for hver selger. Hvis du velger E2 og deretter E3, E4 og så videre, ser du at den samme formelen vises: {=C2:C11*D2:D11}. 

Totalene i kolonne E beregnes av en matriseformel

  • Opprett en encellet matriseformel

Skriv inn følgende formel i celle D13 i arbeidsboken, og trykk deretter CTRL+SKIFT+ENTER:

=SUMMER(C2:C11*D2:D11)

I dette tilfellet multipliserer Excel verdiene i matrisen (celleområdet C2 til D11) og bruker deretter SUMMER-funksjonentil å legge sammen totalene. Resultatet er en totalsum på kr 15 900 000 for salg. Dette eksemplet viser hvor effektiv denne typen formel kan være. La oss for eksempel si at du har 1 000 rader med data. Du kan summere deler av eller alle dataene ved å opprette en matriseformel i én celle i stedet for å dra formelen ned gjennom de 1 000 radene.

Legg også merke til at encellede formelen i celle D13 er helt uavhengig av formelen med flere celler (formelen i cellene E2 til E11). Dette er en annen fordel med å bruke matriseformler \endash fleksibilitet. Du kan endre formlene i kolonne E eller slette kolonnen helt, uten at det påvirker formelen i D13.

Matriseformler har også følgende fordeler:

  • Konsekvens    Hvis du klikker en hvilken som helst av cellene fra E2 og nedover, ser du samme formel. Denne konsekvensen kan bidra til å sikre større presisjon.

  • Trygghet    Du kan ikke skrive over en komponent i en flercellet matriseformel. Klikk for eksempel celle E3, og trykk del. Du må enten merke hele celleområdet (E2 til E11) og endre formelen for hele matrisen, eller la matrisen være som den er. Som et ekstra sikkerhetstiltak må du trykke CTRL+SKIFT+ENTER for å bekrefte eventuelle endringer i formelen.

  • Mindre filstørrelser    Du kan ofte bruke én matriseformel i stedet for flere mellomliggende formler. Arbeidsboken bruker for eksempel én matriseformel til å beregne resultatene for kolonne E. Hvis du hadde brukt standardformler (for eksempel =C2*D2; C3*D3; C4*D4 ... ), hadde du brukt 11 ulike formler til å beregne det samme resultatet.

Generelt sett bruker matriseformler standard formelsyntaks. Alle begynner med et likhetstegn (=), og du kan bruke de fleste av de innebygde funksjonene Excel i matriseformlene. Hovedforskjellen er at når du bruker en matriseformel, trykker du CTRL+SKIFT+ENTER for å skrive inn formelen. Når du gjør dette, Excel omslutter matriseformelen med leneparenter – hvis du skriver inn lene manuelt, konverteres formelen til en tekststreng, og den vil ikke fungere.

Matrisefunksjoner kan være en effektiv måte å bygge komplekse formler på. Matriseformelen =SUMMER(C2:C11*D2:D11) er det samme som dette: =SUMMER(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Viktig!: Trykk CTRL+SKIFT+ENTER når du trenger å skrive inn en matriseformel. Denne regelen gjelder for både encellede og flercellede formler.

Når du arbeider med flercellede formler, må du også huske på følgende:

  • Merk celleområdet som skal inneholde resultatene før du skriver inn formelen. Du gjorde dette da du opprettet den flercellede matriseformelen da du merket cellene E2 til E11.

  • Du kan ikke endre innholdet i en enkeltcelle i en matriseformel. Hvis du vil prøve dette, merker du celle E3 i arbeidsboken og trykker DEL. Excel viser en melding som forteller deg at du ikke kan endre en del av en matrise.

  • Du kan flytte eller slette en hel matriseformel, men du kan ikke flytte eller slette deler av den. Med andre ord må du først slette den eksisterende formelen hvis du vil forminske en matriseformel, og deretter må du begynne på nytt.

  • Hvis du vil slette en matriseformel, merker du hele formelområdet (for eksempel E2:E11), og deretter trykker du DEL.

  • Du kan ikke sette inn tomme celler i eller slette celler fra en flercellet matriseformel.

Noen ganger trenger du kanskje å utvide en matriseformel. Merk den første cellen i eksisterende matriseområde, og fortsett til du har merket hele området du vil utvide formelen til. Trykk F2 for å redigere formelen, og trykk deretter CTRL+SKIFT+ENTER for å bekrefte formelen når du har justert formelområdet. Nøkkelen er å merke hele området, og begynne med cellen øverst til venstre i matrisen. Cellen øverst til venstre er den som blir redigert.

Matriseformler er flotte, men de kan også ha noen ulemper:

  • Noen ganger glemmer du kanskje å trykke CTRL+SKIFT+ENTER. Det kan hende selv de mest erfarne Excel-brukere. Husk å trykke denne tastekombinasjonen hver gang du angir eller redigerer en matriseformel.

  • Andre brukere av arbeidsboken forstår kanskje ikke formlene dine. I praksis er matriseformler vanligvis ikke forklart i et regneark. Hvis andre må endre arbeidsbøkene, bør du derfor enten unngå matriseformler eller kontrollere at disse personene vet om matriseformler og forstå hvordan du endrer dem, hvis det er nødvendig.

  • Avhengig av behandlingshastigheten og minnet til datamaskinen kan store matriseformler senke farten på beregninger.

Matrisekonstanter er en komponent i matriseformler. Du oppretter matrisekonstanter ved å angi en liste med elementer og deretter omslutte listen med klammeparenteser manuelt ({ }), slik:

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

Nå vet du at du må trykke CTRL+SKIFT+ENTER når du oppretter matriseformler. Siden matrisekonstanter er en komponent i matriseformler, omslutter du konstantene med klammeparenteser ved å skrive dem inn manuelt. Deretter bruker du CTRL+SKIFT+ENTER for å skrive inn hele formelen.

Hvis du skiller elementene med semikolon, oppretter du en vannrett matrise (en rad). Hvis du skiller elementene med omvendt skråstrek, oppretter du en loddrett matrise (en kolonne). Du kan opprette en todimensjonal matrise ved å skille elementene i hver rad med semikolon og skille hver rad med omvendt skråstrek.

Her er en matrise i én rad: {1,2,3,4}. Her er en matrise i én enkelt kolonne: {1;2;3;4}. Og her er en matrise i to rader og fire kolonner: {1,2,3,4;5,6,7,8}. I den to radmatrisen er den første raden 1, 2, 3 og 4, og den andre raden er 5, 6, 7 og 8. Ett enkelt semikolon skiller de to radene mellom 4 og 5.

Som med matriseformler kan du bruke matrisekonstanter med de fleste innebygde funksjonene i Excel. Nedenfor finner du informasjon om hvordan du oppretter hver type konstant og hvordan du bruker disse konstantene med funksjoner i Excel.

Fremgangsmåtene nedenfor gir deg øvelse i å opprette vannrette, loddrette og todimensjonale konstanter.

Opprett vannrette konstanter

  1. Merk celle A1 til E1 i et tomt regneark.

  2. Skriv inn følgende formel på formellinjen, og trykk deretter CTRL+SKIFT+ENTER:

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

    I dette tilfellet bør du skrive inn innledende og avsluttende klammeparenter ({ }), og Excel vil legge til det andre settet for deg.

    Følgende resultat vises:

    Vannrett matrisekonstant i formel

Opprett loddrette konstanter

  1. Merk en kolonne med fem celler i arbeidsboken.

  2. Skriv inn følgende formel på formellinjen, og trykk deretter CTRL+SKIFT+ENTER:

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

    Følgende resultat vises:

    Loddrett matrisekonstant i matriseformel

Opprett todimensjonale konstanter

  1. Merk en blokk med celler som er fire kolonner bred og tre rader høy, i arbeidsboken.

  2. Skriv inn følgende formel på formellinjen, og trykk deretter CTRL+SKIFT+ENTER:

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

    Følgende resultat vises:

    Todimensjonal matrisekonstant i matriseformel

Bruk konstanter i formler

Her er et enkelt eksempel som bruker konstanter:

  1. Opprett et nytt regneark i denne eksempelarbeidsboken.

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

  3. Skriv inn følgende formel i celle A3, og trykk deretter CTRL+SKIFT+ENTER:

    =SUMMER(A1:E1*{1;2;3;4;5})

    Legg merke til at konstanten omsluttes med et nytt sett med klammeparenteser, fordi du har angitt den som en matriseformel.

    Matriseformel med matrisekonstant

    Verdien 85 vises i celle A3.

Neste del inneholder informasjon om hvordan formelen fungerer.

Formelen du nettopp brukte, inneholder flere deler.

Syntaks for matriseformel med matrisekonstant

1. Funksjon

2. Lagret matrise

3. Operator

4. Matrisekonstant

Det siste elementet i parentesen er matrisekonstanten: {1,2,3,4,5}. Husk at Excel ikke omslutter matrisekonstanter med leneparenter. du faktisk skriver dem inn. Husk også at når du har lagt til en konstant i en matriseformel, trykker du CTRL+SKIFT+ENTER for å skrive inn formelen.

Ettersom det utføres operasjoner i Excel først på uttrykk som er omsluttet med klammeparenteser, er de neste to elementene som blir behandlet, verdiene som er lagret i arbeidsboken (A1:E1), og operatoren. På dette tidspunktet multipliserer formelen verdiene i den lagrede matrisen med tilsvarende verdier i konstanten. Dette tilsvarer følgende:

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

Til slutt legger SUMMER-funksjonen sammen verdiene, og summen 85 vises i celle A3.

Du kan unngå å bruke den lagrede matrisen og bare ha operasjonen på minnet ved å erstatte den lagrede matrisen med en annen matrisekonstant:

=SUMMER({3;4;5;6;7}*{1;2;3;4;5})

Hvis du vil prøve dette, kopierer du funksjonen, merker en tom celle i arbeidsboken, limer inn formelen på formellinjen og trykker deretter CTRL+SKIFT+ENTER. Du ser det samme resultatet som du gjorde i den forrige øvelsen som brukte matriseformelen:

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

Matrisekonstanter kan inneholde tall, tekst, logiske verdier (som SANN eller USANN) og feilverdier (for eksempel #I/T). Du kan bruke tall i formatene heltall, desimal og eksponentiell. Hvis du tar med tekst, må du omslutte teksten med anførselstegn (").

Matrisekonstanter kan ikke inneholde flere matriser, formler eller funksjoner. Med andre ord kan de inneholde bare tekst eller tall som er skilt med semikolon eller omvendt skråstrek. En advarsel vises når du angir en formel som {1;2;A1:D4} eller {1;2;SUMMER(Q2:Z8)}. Numeriske verdier kan heller ikke inneholde prosenttegn, valutategn eller parenteser.

En av de beste måtene å bruke matrisekonstanter på, er å gi dem et navn. Konstanter som har navn, kan være mye enklere å bruke, og de kan skjule noe av kompleksiteten til matriseformlene dine for andre. Du kan gi en matrisekonstant navn og bruke den i en formel ved å gjøre følgende:

  1. Klikk Definer navn i Definerte navn-gruppen på fanen Formler.
    Dialogboksen Definer navn vises.

  2. Skriv inn Kvartal1 i Navn-boksen.

  3. Skriv inn konstanten nedenfor i boksen Refererer til (husk å skrive inn klammeparentesen manuelt):

    ={"Januar";"Februar";"Mars"}

    Innholdet i dialogboksen ser nå slik ut:

    Dialogboksen Rediger navn med formel

  4. Klikk OK, og merk deretter en rad med tre tomme celler.

  5. Skriv inn følgende formel, og trykk deretter CTRL+SKIFT+ENTER.

    =Kvartal1

    Følgende resultat vises:

    Navngitt matrise angitt som formel

Når du bruker en navngitt konstant som en matriseformel, må du huske å angi likhetstegnet. Hvis du ikke gjør det, tolker Excel matrisen som en streng med tekst, og formelen vil ikke fungere som forventet. Husk også på at du kan bruke kombinasjoner av tekst og tall.

Se etter problemene nedenfor når matrisekonstantene ikke fungerer:

  • Noen elementer er kanskje ikke skilt med riktig tegn. Hvis du utelater et komma eller semikolon, eller hvis du setter et komma eller semikolon på feil sted, kan det hende at matrisekonstanten ikke opprettes på riktig måte, eller det kan hende du ser en advarsel.

  • Du kan ha merket et celleområde som ikke samsvarer med antallet elementer i konstanten. Hvis du for eksempel merker en kolonne med seks celler for bruk med en femcellers konstant, vises feilverdien #I/T i den tomme cellen. Hvis du derimot merker for få celler, utelater Excel verdiene som ikke har en tilsvarende celle.

Eksemplene nedenfor viser noen av måtene du kan bruke matrisekonstanter på i matriseformler. Noen av eksemplene bruker FUNKSJONEN TRANSPONER til å konvertere rader til kolonner og omvendt.

Multipliser hvert element i en matrise

  1. Opprett et nytt regneark, og merk deretter en blokk med tomme celler som er fire kolonner bred og tre rader høy.

  2. Skriv inn følgende formel, og trykk deretter CTRL+SKIFT+ENTER:

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

Kvadrer elementene i en matrise

  1. Merk en blokk med tomme celler som er fire kolonner bred og tre rader høy.

  2. Skriv inn følgende matriseformel, og trykk deretter 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 også angi denne matriseformelen, som bruker operatoren ^ (cirkumflekstegn):

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

Transponer en endimensjonal rad

  1. Merk en kolonne med fem tomme celler.

  2. Skriv inn følgende formel, og trykk deretter CTRL+SKIFT+ENTER:

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

    Selv om du skrev inn en vannrett matrisekonstant, konverterer funksjonen TRANSPONER matrisekonstanten til en kolonne.

Transponer en endimensjonal kolonne

  1. Merk en rad med fem tomme celler.

  2. Skriv inn følgende formel, og trykk deretter CTRL+SKIFT+ENTER:

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

Selv om du skrev inn en loddrett matrisekonstant, konverterer TRANSPONER-funksjonen konstanten til en rad.

Transponer todimensjonale konstanter

  1. Merk en blokk med celler som er tre kolonner bred og fire rader høy.

  2. Skriv inn følgende konstant, og trykk deretter CTRL+SKIFT+ENTER:

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

    TRANSPONER-funksjonen konverterer hver rad til en serie med kolonner.

Denne delen inneholder eksempler på enkle matriseformler.

Opprett matriser og matrisekonstanter fra eksisterende verdier

Eksemplet nedenfor viser hvordan du bruker matriseformler til å opprette koblinger mellom celleområder i forskjellige regneark. Det viser også hvordan du oppretter en matrisekonstant fra det samme verdisettet.

Opprett matriser fra eksisterende verdier

  1. Merk celle C8:E10 i et arbeidsark i Excel, og skriv inn denne formelen:

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

    Pass på at du skriver { (venstre parentes) før du skriver 10, og } (høyre parentes) etter at du har skrevet 90, fordi du lager en matrise med tall.

  2. Trykk CTRL+SKIFT+ENTER, som angir denne matrisen med tall i celleområdet C8:E10 ved hjelp av en matriseformel. C8 til E10 skal se slik ut i regnearket ditt:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Merk celleområdet C1 til E3.

  4. Skriv inn følgende formel på formellinjen, og trykk deretter CTRL+SKIFT+ENTER:

    =C8:E10

    En 3x3-matrise med celler vises i celle C1 til E3 med de samme verdiene som du ser i C8 til E10.

Opprett matrisekonstanter fra eksisterende verdier

  1. Når cellene C1:C3 er merket, trykker du F2 for å bytte til redigeringsmodus. 

  2. Trykk F9 for å konvertere cellereferansene til verdier. Verdiene konverteres til en matrisekonstant. Formelen skal nå være ={10;20;30;40;50;60;70;80,90}.

  3. Trykk CTRL+SKIFT+ENTER for å angi matrisekonstanten som en matriseformel.

Tell tegn i et celleområde

Eksemplet nedenfor viser hvordan du teller antall tegn, inkludert mellomrom, i et celleområde.

  1. Kopier hele denne tabellen, og lim dem inn i et regneark i celle A1.

    Data

    Dette er

    en rekke celler som

    samles sammen

    for å gi en

    enkelt setning.

    Totalt antall tegn i A2:A6

    =SUMMER(LENGDE(A2:A6))

    Innhold i den lengste cellen (A3)

    =INDEKS(A2:A6;SAMMENLIGNE(STØRST(LENGDE(A2:A6));LENGDE(A2:A6);0);1)

  2. Merk celle A8, og trykk deretter CTRL+SKIFT+ENTER for å se totalt antall tegn i cellene A2:A6 (66).

  3. Merk celle A10, og trykk deretter CTRL+SKIFT+ENTER for å se innholdet i den lengste av cellene A2:A6 (celle A3).

Følgende formel brukes i celle A8 teller det totale antallet tegn (66) i celle A2 til A6.

=SUMMER(LENGDE(A2:A6))

I dette tilfellet returnerer LENGDE-funksjonen lengden på hver tekststreng i hver av cellene i området. SUMMER-funksjonen legger deretter sammen disse verdiene og viser resultatet (66).

Finn de n minste verdiene i et område

Dette eksemplet viser hvordan du finner de tre minste verdiene i et celleområde.

  1. Skriv inn noen tilfeldige tall i cellene A1:A11.

  2. Merk cellene C1 til C3. Dette settet med celler kommer til å inneholde resultatene som returneres av matriseformelen.

  3. Skriv inn følgende formel, og trykk deretter CTRL+SKIFT+ENTER:

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

Denne formelen bruker en matrisekonstant til å evaluere SMÅ-funksjonen tre ganger og returnere den minste (1), nest minste (2) og tredje minste (3) medlemmer i matrisen som finnes i cellene A1:A10 Hvis du vil finne flere verdier, legger du til flere argumenter i konstanten. Du kan også bruke tilleggsfunksjoner med denne formelen, for eksempel SUMMER eller GJENNOMSNITT. For eksempel:

=SUMMER(LITEN(A1:A10;{1;2;3})

=GJENNOMSNITT(LITEN(A1:A10;{1;2;3})

Finn de n største verdiene i et område

Du kan finne de største verdiene i et område ved å erstatte N.MINST-funksjonen med N.STØRST-funksjonen. I tillegg bruker eksemplet nedenfor RAD- og INDIREKTE-funksjonene.

  1. Merk cellene D1 til D3.

  2. Skriv inn denne formelen på formellinjen, og trykk deretter CTRL+SKIFT+ENTER:

    =STOR(A1:A10;RAD(INDIREKTE("1:3")))

På dette tidspunktet kan det være nyttig å vite litt om RAD- og INDIREKTE-funksjonene. Du kan bruke RAD-funksjonen til å opprette en matrise med etterfølgende heltall. Merk for eksempel en tom kolonne med 10 celler i øvelsesarbeidsboken, skriv inn denne matriseformelen, og trykk deretter CTRL+SKIFT+ENTER:

=RAD(1:10)

Formelen oppretter en kolonne med 10 etterfølgende heltall. Du kan se et potensielt problem hvis du setter inn en rad over området som inneholder matriseformelen (det vil si over rad 1). Radreferansene justeres og formelen genererer heltall fra 2 til 11. Du kan løse dette problemet ved å legge til INDIREKTE-funksjonen i formelen:

=RAD(INDIREKTE("1:10"))

INDIREKTE-funksjonen bruker tekststrenger som sine argumenter (det er derfor området 1:10 er omsluttet av doble anførselstegn). Tekstverdier justeres ikke når du setter inn rader eller flytter matriseformelen på andre måter. Som et resultat genererer alltid RAD-funksjonen ønsket matrise med heltall.

La oss ta en titt på formelen du brukte tidligere – =STOR(A5:A14;RAD(INDIREKTE("1:3"))) – fra de indre parentesene og arbeider utover: INDIREKTE-funksjonen returnerer et sett med tekstverdier, i dette tilfellet verdiene fra 1 til 3. RAD-funksjonen genererer i sin tur en kolonnematrise med tre celler. FUNKSJONEN STOR bruker verdiene i celleområdet A5:A14, og den evalueres tre ganger, én gang for hver referanse som returneres av RAD-funksjonen. Verdiene 3200, 2700 og 2000 returneres til den trecellede kolonnematrisen. Hvis du vil finne flere verdier, legger du til et større celleområde i INDIREKTE-funksjonen.

Som med tidligere eksempler kan du bruke denne formelen med andre funksjoner, for eksempel SUMMER og GJENNOMSNITT.

Finne den lengste tekststrengen i et celleområde

Gå tilbake til det tidligere tekststrengeksempelet, skriv inn følgende formel i en tom celle, og trykk CTRL+SKIFT+ENTER:

=INDEKS(A2:A6;SAMMENLIGNE(STØRST(LENGDE(A2:A6));LENGDE(A2:A6);0);1)

Teksten «mange celler som» vises.

La oss ta en nærmere titt på formelen, fra de indre elementene og arbeide utover. LENGDE-funksjonen returnerer lengden på hvert av elementene i celleområdet A2:A6. STØRST-funksjonen beregner den største verdien blant disse elementene, som tilsvarer den lengste tekststrengen, som er i celle A3.

Her begynner det å bli litt komplisert. SAMMENLIGNE-funksjonen beregner forskyvningen (den relative plasseringen) av cellen som inneholder den lengste tekststrengen. Du gjør dette ved å bruke tre argumenter: en oppslagsverdi, en oppslagsmatrise og en sammenligningstype. SAMMENLIGNE-funksjonen søker i oppslagsmatrisen etter den angitte oppslagsverdien. I dette tilfellet er oppslagsverdien den lengste tekststrengen:

(MAKS(LENGDE(A2:A6))

og den aktuelle strengen finnes i følgende matrise:

LENGDE(A2:A6)

Argumentet for sammenligningstypen er 0. Sammenligningstypen kan bestå av verdiene 1, 0 eller -1. Hvis du angir 1, returnerer SAMMENLIGNE den største verdien som er mindre enn eller lik oppslagsverdien. Hvis du angir 0, returnerer SAMMENLIGNE den første verdien som er helt lik oppslagsverdien. Hvis du angir -1, finner SAMMENLIGNE den minste verdien som er større enn eller lik den angitte oppslagsverdien. Hvis du utelater en sammenligningstype, forutsettes verdien 1.

Til slutt tar INDEKS-funksjonen følgende argumenter: en matrise og et rad- og kolonnenummer i den aktuelle matrisen. Celleområdet A2:A6 inneholder matrisen, SAMMENLIGNE-funksjonen inneholder celleadressen, og det endelige argumentet (1) angir at verdien kommer fra den første kolonnen i matrisen.

Denne delen inneholder eksempler på avanserte matriseformler.

Summer et område som inneholder feilverdier

SUMMER-funksjonen i Excel fungerer ikke når du prøver å summere et område som inneholder en feilverdi, for eksempel #I/T. Dette eksemplet viser hvordan du summerer verdiene i et område kalt Data, som inneholder feil.

=SUMMER(HVIS(ERFEIL(Data);"";Data))

Formelen oppretter en ny matrise som inneholder de opprinnelige verdiene minus eventuelle feilverdier. Fra og med de indre funksjonene og arbeider utover, søker ERFEIL-funksjonen i celleområdet (Data) etter feil. HVIS-funksjonen returnerer en bestemt verdi hvis en betingelse du angir, evalueres til SANN og en annen verdi hvis den evalueres til USANN. I dette tilfellet returnerer den tomme strenger ("") for alle feilverdier fordi de evalueres til SANN, og returnerer de gjenstående verdiene fra området (Data) fordi de evalueres til USANN, noe som betyr at de ikke inneholder feilverdier. SUMMER-funksjonen beregner deretter totalsummen for den filtrerte matrisen.

Tell antall feilverdier i et område

Dette eksemplet ligner på forrige formel, men her returneres antallet feilverdier i et område kalt Data i stedet for at de filtreres ut:

=SUMMER(HVIS(ERFEIL(Data);1;0))

Denne formelen oppretter en matrise som inneholder verdien 1 for cellene som inneholder feil, og verdien 0 for cellene som ikke inneholder feil. Du kan forenkle formelen og få de samme resultatene ved å fjerne det tredje argumentet for HVIS-funksjonen, på følgende måte:

=SUMMER(HVIS(ERFEIL(Data);1))

Hvis du ikke angir argumentet, returnerer HVIS-funksjonen USANN hvis en celle ikke inneholder en feilverdi. Du kan forenkle formelen enda mer på følgende måte:

=SUMMER(HVIS(ERFEIL(Data)*1))

Denne versjonen fungerer fordi SANN*1=1 og USANN*1=0.

Summer verdier basert på betingelser

Du trenger kanskje å summere verdier basert på betingelser. Matriseformelen nedenfor er et eksempel på en formel som summerer bare de positive heltallene i et område kalt Salg:

=SUMMER(HVIS(Salg>0;Salg))

HVIS-funksjonen oppretter en matrise med positive verdier og usanne verdier. SUMMER-funksjonen ignorerer hovedsakelig de usanne verdiene fordi 0+0=0. Celleområdet du bruker i denne formelen, kan bestå av et hvilket som helst antall rader og kolonner.

Du kan også summere verdier som oppfyller mer enn én betingelse. Matriseformelen nedenfor er et eksempel på en formel som beregner verdier som er større enn 0 og mindre enn eller lik 5:

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

Husk at denne formelen returnerer en feil hvis området inneholder én eller flere ikke-numeriske celler.

Du kan også opprette matriseformler som bruker en type ELLER-betingelse. Du kan for eksempel summere verdier som er mindre enn 5 og større enn 15:

=SUMMER(HVIS(Salg<5)+(Salg>15);Salg))

HVIS-funksjonen finner alle verdier som er mindre enn 5 og større enn 15, og deretter sendes de aktuelle verdiene videre til SUMMER-funksjonen.

Du kan ikke bruke OG- og ELLER-funksjonene direkte i matriseformler fordi disse funksjonene returnerer ett resultat, enten SANN eller USANN, og matrisefunksjoner krever matriser med resultater. Du kan omgå dette problemet ved å bruke logikken som er vist i forrige formel. Du utfører med andre ord matematiske beregninger, for eksempel addisjon eller multiplikasjon, på verdier som oppfyller ELLER- eller OG-betingelsen.

Beregn et gjennomsnitt som utelater nuller

Dette eksemplet viser hvordan du fjerner nuller fra et område når du trenger å finne gjennomsnittet av verdiene i det aktuelle området. Formelen bruker et dataområde kalt Salg:

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

HVIS-funksjonen oppretter en matrise med verdier som ikke er lik 0, og sender deretter de aktuelle verdiene videre til GJENNOMSNITT-funksjonen.

Tell antallet forskjeller mellom to celleområder

Denne matriseformelen sammenligner verdiene i to celleområder kalt MineData og DineData og returnerer antallet forskjeller mellom de to. Hvis innholdet i de to områdene er identiske, returnerer formelen 0. For å kunne bruke denne formelen, må celleområdene ha samme størrelse og ha like dimensjoner (for eksempel hvis MineData er et område med 3 rader og 5 kolonner, må DineData også bestå av 3 rader og 5 kolonner):

=SUMMER(HVIS(MyData=DineData;0;1))

Formelen oppretter en ny matrise i samme størrelse som områdene du sammenligner. HVIS-funksjonen fyller matrisen med verdiene 0 og 1 (0 for celler som ikke samsvarer, og 1 for identiske celler). SUMMER-funksjonen returnerer deretter summen av verdiene i matrisen.

Du kan forenkle formelen på følgende måte:

=SUMMER(1*(MyData<>YourData))

På samme måte som formelen som teller feilverdier i et område, fungerer denne formelen fordi SANN*1=1 og USANN*1=0.

Finn plasseringen til maksimumsverdien i et område

Denne matriseformelen returnerer radnummeret til maksimumsverdien i et område med én kolonne, kalt Data:

=MIN(HVIS(Data=STØRST(Data);RAD(Data);""))

HVIS-funksjonen oppretter en ny matrise som samsvarer med området kalt Data. Hvis en tilsvarende celle inneholder maksimumsverdien i området, inneholder matrisen radnummeret. Hvis ikke inneholder matrisen en tom streng (""). MIN-funksjonen bruker den nye matrisen som sitt andre argument og returnerer den minste verdien, som samsvarer med radnummeret til maksimumsverdien i Data. Hvis området kalt Data inneholder identiske maksimumsverdier, returnerer formelen raden til den første verdien.

Hvis du vil returnere den faktiske celleadressen til en maksimumsverdi, bruker du følgende formel:

=ADRESSE(MIN(HVIS(Data=STØRST(Data);RAD(Data);""));KOLONNE(Data))

Bekreftelse

Deler av denne artikkelen var basert på en serie med Excel Power User-kolonner skrevet av Colin Wilcox, og tilpasset fra kapittel 14 og 15 av Excel 2002-formler, en bok skrevet av John Walkenbach, en tidligere Excel MVP.

Trenger du mer hjelp?

Du kan alltid spørre en ekspert i Excel tekniske fellesskap, få støtte i Svar-fellesskapet eller foreslå en ny funksjon eller forbedring på Excel User Voice.

Se også

Dynamiske matriser og overflytende matrise

Dynamiske matriseformler kontra eldre CSE-matriseformler

FILTER-funksjonen

TILFELDIGMATRISE-funksjonen

SEKVENS-funksjonen

SORTER-funksjonen

SORTER ETTER-funksjonen

UNIK-funksjonen

#OVERFLYT!-feil i Excel

Implisitt skjæringspunktoperator: @

Oversikt over formler

Trenger du mer hjelp?

Bli bedre på Office
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Office Insiders

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×