Retningslinjer og eksempler på matriseformler

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 tabellen nedenfor, og lim den inn i celle A1 i et tomt regneark.

Salg  Person	Biltype	Antall  Solgt	Enhetspris	Totale  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 kupébiler og sedanbiler for hver selger, merker du cellene E2:E11, skriver inn formelen =C2:C11*D2:D11, og trykker deretter på CTRL+SKIFT+ENTER.

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

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

• Opprett en encellet matriseformel

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

=SUMMER(C2:C11*D2:D11)

I dette tilfellet multipliseres verdiene i matrisen (celleområdet C2 til D11), og deretter brukes SUMMER-funksjonentil å 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 D13) 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 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 på celle E3, og trykk på Slett. 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 Excel-funksjonene i matriseformlene. Hovedforskjellen er at når du bruker en matriseformel, trykker du på CTRL+SKIFT+ENTER for å skrive inn formelen. Når du gjør dette, omsluttes matriseformelen med parenteser – hvis du skriver inn parenteser 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).

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 arbeidsboken og trykke på Slett. Excel viser en melding som informerer deg om 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 trykker deretter DELETE.

• Du kan ikke sette inn tomme celler eller slette celler i 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 på F2 for å redigere formelen, og trykk deretter på CTRL+SKIFT+ENTER for å bekrefte formelen når du har justert formelområdet. Trikset er å merke hele området og starte med cellen øverst til venstre i matrisen. Cellen øverst til venstre er den som redigeres.

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.

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 på 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 å angi 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 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 matrisen med to rader 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 formelen nedenfor på formellinjen, og trykk deretter på CTRL+SKIFT+ENTER:

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

   I dette tilfellet bør du skrive inn venstre og høyre klammeparentes ({ }), og Excel legger til det andre settet for deg.

   Følgende resultat vises.

   

Opprett loddrette konstanter

1. Merk en kolonne med fem celler i arbeidsboken.

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

   ={1.2.3.4.5}

   Følgende resultat vises:

   

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 på CTRL+SKIFT+ENTER:

   ={1\2\3\4.5\6\7\8.9\10\11\12}

   Følgende resultat vises:

   

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 A3, og trykk deretter på 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.

   

   Verdien 85 vises i celle A3.

Neste del inneholder informasjon om hvordan formelen fungerer.

Formelen du nettopp brukte, inneholder flere deler.

1. Funksjon

2. Lagret matrise

3. Operator

4. Matrisekonstant

Det siste elementet i parentesene er matrisekonstanten: {1\2\3\4\5}. Husk at Excel ikke omslutter matrisekonstanter med klammeparenter – du skriver dem faktisk. Husk også at når du har lagt til en konstant i en matriseformel, trykker du på 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})

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

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.
   Definer navn-dialogboksen 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:

   

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

5. Skriv inn formelen nedenfor, og trykk deretter på Ctrl+Shift+Enter.

   =Kvartal1

   Følgende resultat vises:

   

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

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 på Ctrl+Shift+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 på Ctrl+Shift+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 på 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 på 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 deretter på 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 du ser i cellene C8 til E10.

Opprett matrisekonstanter fra eksisterende verdier

1. Trykk på F2 med cellene C1:C3 merket for å bytte til redigeringsmodus. 

2. Trykk på 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 på 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 det totale antallet tegn i cellene A2:A6 (66).

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

Formelen nedenfor som brukes i celle A8, 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 (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 celle C1 til C3. Dette settet med celler kommer til å inneholde resultatene som returneres av matriseformelen.

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

   =N.STØRST(A1:A11,{1.2.3})

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. Du kan også bruke tilleggsfunksjoner med denne formelen, for eksempel SUMMER eller GJENNOMSNITT. For eksempel:

=SUMMER(N.MINST(A1:A10,{1\2\3})

=GJENNOMSNITT(N.MINST(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 celle D1 til D3.

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

   =N.STØRST(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 – =N.STØRST(A5:A14,RAD(INDIREKTE("1:3"))) – med start fra de indre parentesene og arbeide utover: INDIREKTE-funksjonen returnerer et sett med tekstverdier, i dette tilfellet verdiene fra 1 til 3. RAD-funksjonen genererer i sin tur en trecellet kolonnematrise. N.STØRST-funksjonen bruker verdiene i celleområdet A5:A14 og evalueres tre ganger 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 formelen nedenfor i en tom celle, og trykk på CTRL+SKIFT+ENTER:

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

Teksten «en samling med celler som» vises.

La oss ta en nærmere titt på formelen – vi begynner fra de indre elementene og jobber oss utover. Funksjonen LEN 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:

(STØRST(LEN(A2:A6))

og den aktuelle strengen finnes i følgende matrise:

LEN(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 angir matrisen, SAMMENLIGNE-funksjonen angir 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. Hvis du starter fra de indre funksjonene og arbeider utover, søker funksjonen ERFEIL i celleområdet (Data) etter feil. HVIS-funksjonen returnerer en spesifikk verdi hvis et vilkår du angir, evalueres som SANN, og en annen verdi det evalueres som USANN. I dette tilfellet returnerer den tomme strenger ("") for alle feilverdier fordi de evalueres til SANN, og den returnerer de gjenværende 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(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))