Retningslinjer og eksempler på matriseformler

Retningslinjer og eksempler på matriseformler

Viktig: Denne artikkelen er maskinoversatt, se ansvarsfraskrivelsen. Du finner den engelske versjonen av artikkelen her som referanse.

For å bli en Excel Privilegerte brukere, må du vite hvordan du bruker matriseformler, som kan utføre beregninger som du ikke kan gjøre ved hjelp av ikke-matriseformler. Følgende artikkel er basert på en serie med Excel Power brukerkolonner skrevet av Colin Wilcox og tilpasset fra kapitler 14 og 15 av Excel 2002-formler, en bok skrevet av John Walkenbach, en Excel-MVP.

Lær om matriseformler

Matriseformler kalles ofte CSE-formler (CTRL+SKIFT+ENTER) fordi i stedet for bare å trykke ENTER, trykker du CTRL+SKIFT+ENTER for å fullføre formelen.

Hvorfor bruke matriseformler?

Hvis du har erfaring med å bruke formler i Excel, vet du at du kan utføre noen ganske avanserte handlinger. Du kan for eksempel beregne den totale kostnaden for et lån over et bestemt antall år. Du kan bruke matriseformler til å utføre kompliserte oppgaver, for eksempel følgende:

  • Tell antall tegn i et celleområde.

  • Summer bare tall som oppfyller bestemte vilkår, for eksempel de minste verdiene i et tallområde eller tall som er mellom en øvre og en nedre grense.

  • Summer hver n-te verdi i et verdiområde.

Rask innføring i matriser og matriseformler

En matriseformel er en formel som kan utføre flere beregninger på ett eller flere av elementene i en matrise. Du kan tenke på en matrise som en rad med verdier, en kolonne med verdier eller en kombinasjon av rader og kolonner med verdier. Matriseformler kan returnere flere resultater eller ett resultat. Du kan for eksempel opprette en matriseformel i et celleområde og bruke matriseformelen til å beregne en kolonne eller rad med delsummer. Du kan også plassere en matriseformel i én celle og beregne ett beløp. En matriseformel som omfatter flere celler, kalles en flercellet formel, og en matriseformel i én celle, kalles en encellet formel.

Eksemplene i neste del viser deg hvordan du oppretter flercellede og encellede matriseformler.

Prøv det!

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

Her er en arbeidsbok innebygd i nettleseren. Selv om den inneholder eksempeldata, må du vite at du ikke kan opprette eller endre matriseformler i en innbygd arbeidsbok, du må ha Excel-programmet. Du kan se svarene i den innebygde arbeidsboken, og noe av teksten forklarer hvordan matriseformler fungerer. Hvis du virkelig vil lære å sette pris på matriseformler, må du imidlertid se arbeidsboken i Excel.

Opprett en flercellet matriseformel
  1. Kopier tabellen nedenfor, og lim den inn i celle A1 i et tomt regneark i Excel.

    Selger

    Bil-
    type

    Antall
    solgt

    Enhets-
    pris

    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)

  2. Hvis du vil vise totalt salg av coupe og sedan for hver selger, merker du E2:E11, skriver inn formelen =C2:C11*D2:D11 og trykker deretter Ctrl+Skift+Enter.

  3. Hvis du vil finne totalsummen for alle salg, merker du celle F11, skriver inn formelen =SUMMER(C2:C11*D2:D11) og trykker deretter Ctrl+Skift+Enter.

Du kan laste nede arbeidsboken ved å klikke den grønne Excel-knappen på det svarte feltet nederst i arbeidsboken. Deretter kan du åpne filen i Excel, merke cellene som inneholder matriseformlene, og trykke CTRL+SKIFT+ENTER for å få formelen til å fungere.

Hvis du arbeider i Excel, må du passe på at Ark1 er aktivt og deretter merke cellene E2:E11. Trykk F2 og skriv inn formelen =C2:C11*D2:D11 i gjeldende celle, E2. Hvis du trykker Enter, vil du se at formelen bare legges inn i celle E2 og viser 165000. Etter å ha skrevet formelen, trykker du Ctrl+Skift+Enter i stedet for bare Enter. Nå vises resultatene i cellene E2:E11. Legg også merke til formellinjen, den vises om {=C2:C11*D2:D11}. Da vet du at det er en matriseformel. Dette vises i tabellen nedenfor.

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

Totalene i kolonne E beregnes av en matriseformel

Opprett en encellet matriseformel

Skriv inn formelen nedenfor i celle F10 i arbeidsboken, og trykk deretter CTRL+SKIFT+ENTER:

=SUMMER(C2:C11*D2:D11)

I dette tilfellet multipliseres verdiene i matrisen (celleområdet C2 til D11), og deretter brukes SUMMER-funksjonen til å legge sammen delsummene. 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 den encellede formelen (i celle G11) er helt uavhengig av den flercellede formelen (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 hele kolonnen, uten at dette påvirker formelen i G11.

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

Matriseformel-syntaks

Matriseformler bruker vanligvis standard formelsyntaks. Alle begynner med et likhetstegn (=), og du kan bruke alle de innebygde Excel-funksjonene i matriseformlene. Hovedforskjellen er at når du bruker en matriseformel, må du trykke CTRL+SKIFT+ENTER for å angi formelen. Når du gjør dette, omsluttes matriseformelen med klammeparenteser. Hvis du skriver inn klammeparentesene manuelt, konverteres formelen til en tekststreng, og den vil ikke fungere.

Matriseformler er en svært effektiv metode for å bygge en komplisert formel. 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).

Angi og endre matriseformler

Viktig   Trykk Ctrl+Skift+Enter når du trenger å angi eller redigere 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. Du kan prøve dette ved å merke celle E3 i eksempelarbeidsboken og trykke DEL.

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

  • Du kan slette en matriseformel ved å merke hele formelen (for eksempel =C2:C11*D2:D11), trykke DEL og deretter trykke CTRL+SKIFT+ENTER.

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

Utvid matriseformler

Noen ganger trenger du kanskje å utvide en matriseformel. Prosessen er ikke komplisert, men pass på å følge veiledningen ovenfor.

I dette regnearket har vi lagt til noen flere rader med salgsdata, i rad 12 til 17. Her vil vi oppdatere matriseformlene slik at de nå omfatter de nye radene.

Pass på at du gjør det i Excel-skrivebordsprogrammet (etter at du har lastet ned arbeidsboken til datamaskinen din).

Utvid en matriseformel
  1. Kopier hele denne tabellen i celle A1 i et Excel-regneark.

    Selger

    Bil-
    type

    Antall
    solgt

    Enhets-
    pris

    Totalt
    salg

    Barnhill

    Sedan

    5

    33000

    165000

    Coupe

    4

    37000

    148000

    Ingle

    Sedan

    6

    24000

    144000

    Coupe

    8

    21000

    168000

    Jordan

    Sedan

    3

    29000

    87000

    Coupe

    1

    31000

    31000

    Pica

    Sedan

    9

    24000

    216000

    Coupe

    5

    37000

    185000

    Sanchez

    Sedan

    6

    33000

    198000

    Coupe

    8

    31000

    248000

    Toth

    Sedan

    2

    27000

    Coupe

    3

    30000

    Wang

    Sedan

    4

    22000

    Coupe

    1

    41000

    Young

    Sedan

    5

    32000

    Coupe

    3

    36000

    Totalsum

  2. Merk celle E18, skriv inn Totalsum-formelen =SUMMER(C2:C17*D2:D17) i celle A20, og trykk Ctrl+Skift+Enter.
    Svaret skal være 2 131 000.

  3. Merk celleområdet som inneholder gjeldende matriseformel (E2:E11), i tillegg til de tomme cellene (E12:E17) som er ved siden av de nye dataene. Merk med andre ord cellene E2:E17.

  4. Trykk F2 for å bytte til redigeringsmodus.

  5. På formellinjen endrer du C11 til C17, D11 til D17, og deretter trykker du Ctrl+Skift+Enter.
    Formelen oppdateres i cellene E2 til E11, og en forekomst av formelen plasseres i de nye cellene, E12 til E17.

  6. Skriv inn matriseformelen = SUMMER(C2:C17*D2*D17) i celle F17, slik at den refererer til celler fra rad 2 til rad 17, og trykk Ctrl+Skift+Enter for å legge inn matriseformelen.
    Den nye samlede summen skal bli kr 2 131 000.

Ulemper ved å bruke matriseformler

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

  • Du kan noen ganger glemme å 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. Derfor, hvis andre personer trenger å endre arbeidsbøkene dine, bør du enten unngå å bruke matriseformler eller passe på at de aktuelle personene kjenner til matriseformler og forstår hvordan de kan endre formelen hvis det trengs.

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

Til toppen av siden

Lær om matrisekonstanter

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 til å angi hele matriseformelen.

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 enkelt 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 de to radkolonnene, 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.

Til toppen av siden

Opprett endimensjonale og todimensjonale konstanter

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

Opprett vannrette konstanter

  1. Bruk arbeidsboken fra de tidligere eksemplene, eller opprett en ny arbeidsbok.

  2. Merk cellene A1 til E1.

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

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

    I dette tilfellet du skrive inn start- og sluttklammeparentes ({ }).

    Følgende resultat vises:

    Vannrett matrisekonstant i formel

Opprett loddrette konstanter

  1. Merk en kolonne med fem celler i arbeidsboken.

  2. Skriv inn formelen nedenfor 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 formelen nedenfor 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 formelen nedenfor i celle B13, 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.

Matrisekonstant-syntaks

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 matrisekonstanter ikke omsluttes med klammeparenteser i Excel. Du må faktisk skrive dem. Husk også at når du har lagt til en konstant i en matriseformel, må du trykke CTRL+SKIFT+ENTER for å angi 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})

Du kan prøve dette ved å kopiere funksjonen, merke en tom celle i arbeidsboken, lime inn formelen på formellinjen, og deretter trykke 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})

Elementer du kan bruke i konstanter

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.

Gi navn til matrisekonstanter

Den beste måten å bruke matrisekonstanter på, er å gi dem 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 formelen nedenfor, 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.

Feilsøk matrisekonstanter

Se etter problemene nedenfor når matrisekonstantene ikke fungerer:

  • Noen elementer er kanskje ikke skilt med riktig tegn. Hvis du utelater et semikolon eller omvendt skråstrek, eller hvis du setter ett på feil sted, kan det hende at matrisekonstanten ikke opprettes på riktig måte, eller du får kanskje se 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.

Matrisekonstanter i praksis

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 formelen nedenfor, 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 matriseformelen nedenfor, 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 formelen nedenfor, 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 formelen nedenfor, 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 konstanten nedenfor, 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.

Til toppen av siden

Ta i bruk enkle matriseformler

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 deretter Ctrl+Skift+Enter, som legger inn denne matrisen med tall i celleområdet C8:E10 ved å bruke 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 formelen nedenfor på formellinjen, og trykk deretter CTRL+SKIFT+ENTER:

    =C8:E10

    En 3x3-matrise av celler vises i cellene C1 til E3 med de samme verdiene som som du så i cellene C8 til E10.

Opprett matrisekonstanter fra eksisterende verdier

  1. Trykk F2 med cellene C1:C3 merket for å bytte til redigeringsmodus.
    Matriseformelen skal fremdeles være =C8:E10.

  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}, akkurat som C8:E10

  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.

  2. Merk celle A9, og trykk deretter Ctrl+Skift+Enter for å vise totalt antall tegn i cellene A2:A6 (66).

  3. Merk celle A12, og trykk deretter Ctrl+Skift+Enter for å vise innholdet i den lengste av cellene A2:A6 (celle A3).

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)

Formelen nedenfor som brukes i celle A9, teller antall tegn (66) i cellene 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 de aktuelle verdiene og viser resultatet i cellen som inneholder formelen, A9.

Finn de n minste verdiene i et område

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

  1. Merk cellene A16 til A18.
    Dette settet med celler kommer til å inneholde resultatene som returneres av matriseformelen.

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

    =N.MINST(A5:A14;{1;2;3})

Verdiene 400, 475 og 500 vises i cellene A16 til A18.

Denne formelen bruker en matrisekonstant til å evaluere N.MINST-funksjonen tre ganger og returnerer det minste (1), nest minste (2) og tredje minste (3) medlemmet i matrisen som finnes i cellene A1:A10. Du kan finne flere verdier ved å legge til flere argumenter i konstanten og et tilsvarende antall resultatceller i A12:A14-området. Du kan også bruke tilleggsfunksjoner med denne formelen, for eksempel SUMMER eller GJENNOMSNITT. For eksempel:

=SUMMER(N.MINST(A5:A14;{1;2;3}))

=GJENNOMSNITT(N.MINST(A5:A14;{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 A1 til A3.

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

    =N.STØRST(A5:A14;RAD(INDIREKTE("1:3")))

Verdiene 3200, 2700 og 2000 vises i cellene A1 til A3.

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, angi denne matriseformelen i cellene A5:A14, 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 undersøke formelen du brukte tidligere \endash =N.STØRST(A5:A14;RAD(INDIREKTE(”1:3”))). Vi begynner med de innerste parentesene og arbeider oss utover: INDIREKTE-funksjonen returnerer et sett med tekstverdier, i dette tilfellet verdiene 1 til 3. RAD-funksjonen genererer igjen en trecellet kolonnematrise. N.STØRST-funksjonen bruker verdiene i celleområdet A5:A14, og det 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.

Til slutt kan du bruke denne formelen med andre funksjoner, for eksempel SUMMER og GJENNOMSNITT.

Finne den lengste tekststrengen i et celleområde

Denne formelen fungerer bare når et dataområde inneholder én kolonne med celler. Skriv inn formelen nedenfor i celle A16 i Ark3, og trykk deretter Ctrl+Skift+Enter:

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

Teksten «en samling med celler som» vises i celle A16.

La oss se litt nærmere på formelen. Vi begynner med de innerste elementene og arbeider oss utover. LENGDE-funksjonen returnerer lengden på hvert av elementene i celleområdet A6:A9. STØRST-funksjonen beregner den største verdien blant de aktuelle elementene, som tilsvarer den lengste tekststrengen, som er i celle A7.

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:

(STØRST(LENGDE(A6:A9))

og den aktuelle strengen finnes i følgende matrise:

LENGDE(A6:A9)

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 A6:A9 angir matrisen, SAMMENLIGNE-funksjonen angir celleadressen, og det endelige argumentet (1) angir at verdien kommer fra den første kolonnen i matrisen.

Til toppen av siden

Ta i bruk avanserte matriseformler

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 bortsett fra eventuelle feilverdier. ERFEIL-funksjonen søker etter feil i celleområdet (Data). Den begynner med de innerste funksjonene og arbeider seg utover. 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 den returnerer de gjenstående verdiene fra området (Data) fordi de evalueres til USANN. Det betyr at de ikke inneholder feilverdier. SUMMER-funksjonen beregner deretter totalen 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(MineData=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*(MineData<>DineData))

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

Til toppen av siden

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.

Merknad: Ansvarsfraskrivelse for maskinoversettelse: Denne artikkelen er oversatt av et datasystem i stedet for en oversetter. Microsoft tilbyr disse maskinoversettelsene slik at brukere som ikke snakker engelsk, får tilgang til innhold om Microsoft-produkter, -tjenester og –teknologier. Ettersom artikkelen er maskinoversatt, kan den inneholde feil i vokabular, syntaks eller grammatikk.

Se også

Oversikt over formler

Utvid ferdighetene dine
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.

×