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

  

• Her beregner vi totale salg av kupébiler og sedanbiler for hver selger ved å skrive inn =F10:F19*G10:G19 i celle H10.

  Når du trykker på Enter, vil du se at resultatene flyter ned til cellene H10:H19. Legg merke til at overflytsområdet er uthevet med en kantlinje når du merker en celle innenfor overflytsområdet. Du legger kanskje også merke til at formlene i cellene H10:H19 er nedtonet. De er der bare for referanse, så hvis du vil justere formelen, må du velge celle H10, hvor originalformelen ligger.

• Encellet matriseformel

  

  I celle H20 i eksempelarbeidsboken skriver du inn eller kopierer og limer inn =SUMMER(F10:F19*G10:G19) og trykker deretter på Enter.

  I dette tilfellet multipliserer Excel verdiene i matrisen (celleområdet F10 til G19), 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 H20) er helt uavhengig av den flercellede formelen (formelen i cellene H10 til H19). Dette er en annen fordel med å bruke matriseformler – fleksibilitet. Du kan endre de andre formlene i kolonne H eller slette hele kolonnen i H20. Det kan også være god øving å ha uavhengige totalsummer som dette, da det bidrar til å validere nøyaktigheten av resultatene.

• Dynamiske matriseformler har også følgende fordeler:

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

  • Trygghet    Du kan ikke overskrive en komponent i en flercellet matriseformel. Klikk for eksempel på celle H11, og trykk på Slett. Excel endrer 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. Bilsalgeksempelet bruker for eksempel én matriseformel til å beregne resultatene for kolonne E. Hvis du hadde brukt standardformler, for eksempel =F10*G10; F11*G11; F12*G12 osv., ville du brukt 11 ulike formler til å beregne det samme resultatet. Det er ikke så farlig, men hva om du hadde tusenvis av rader å summere? Deretter 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).

  • Overflyt    Dynamiske matriseformler vil automatisk flyte inn i utdataområdet. Hvis støttedataene 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 førte til #SPILL!-Feil, som indikerer at det tiltenkte overflytsområdet blokkeres av en eller annen grunn. Når du løser blokkeringen, vil formelen automatisk flyte over.

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 ={"January"\"February"\"March"}

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 komma og skille hver rad med semikolon.

Fremgangsmåtene nedenfor gir deg øvelse i å opprette vannrette, loddrette og todimensjonale konstanter. Vi vil vise eksempler ved hjelp av SEKVENS-funksjonen for å generere matrisekonstanter automatisk, i tillegg til å legge inn matrisekonstanter manuelt.

• 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 matrise med 1 rad ganger 5 kolonner, som er lik ={1\2\3\4\5}. Følgende resultat vises:

  

• Opprett loddrette konstanter

  Velg en tom celle med plass under den, og angi =SEKVENS(5)eller ={1.2.3.4.5}. Følgende resultat vises:

  

• Opprett todimensjonale konstanter

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

  

  Du kan også angi: eller ={1\2\3\4.5\6\7\8.9\10\11\12}, men du bør være oppmerksom på hvor du legger inn semikolon kontra komma.

  Som du kan se, gir SEKVENS-alternativet betydelige fordeler i forhold til å skrive inn konstante matriseverdier manuelt. Det sparer hovedsakelig tid, men det kan også bidra til å redusere feil fra manuelle inntastinger. Det er også enklere å lese, spesielt siden semikolon kan være vanskelig å skille fra kommaseparatorene.

Her er et eksempel som bruker matrisekonstanter som en del av en større formel. I eksempelarbeidsboken kan du gå til Konstant i en formel-regnearket eller opprette et nytt regneark.

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

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

SEKVENS-funksjonen bygger den tilsvarende matrisekonstanten {1\2\3\4\5}. Ettersom det utføres operasjoner i Excel først på uttrykk som er omsluttet med klammeparenteser, er de neste to elementene som blir behandlet, 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.

For å unngå å bruke den lagrede matrisen og holde 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 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 formater som heltall, desimal og vitenskapelig. Hvis du tar med 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 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 boksen Navn. Skriv inn følgende konstant nedenfor i boksen Refererer til (husk å skrive inn klammeparentesene manuelt):

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

Dialogboksen skal nå se slik ut:

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

Følgende resultat vises:

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

Hvis du vil vise en liste på 12 måneder, slik du kan bruke når du bygger en regnskapsrapport, kan du basere ett av gjeldende år med SEKVENS-funksjonen. Det flotteste 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»)

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

Når du bruker en navngitt konstant som en matriseformel, må du huske å angi likhetstegnet, for eksempel =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 også på at du kan bruke kombinasjoner av funksjoner, tekst og tall. Det avhenger av hvor kreativ du vil være.

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

  Skriv =SEKVENS(1,12)*2 eller ={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 =SEKVENS(1,12)^2 eller ={1\2\3\4.5\6\7\8.9\10\11\12}^2

• Finn kvadratroten av kvadrerte elementer i en matrise

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

  Eksemplet nedenfor forklarer hvordan du bruker matriseformler til å opprette en ny matrise fra en eksisterende matrise.

  Skriv =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å at du skriver { (venstre parentes) før du skriver 10, og } (høyre parentes) etter at du har skrevet 180, fordi du oppretter en matrise med tall.

  Skriv deretter inn =D9#, eller =D9:I11 i en tom celle. En matrise på 3 x 6 celler vises med de samme verdiene du ser i D9:D11. #-tegnet kalles -operator for overflytende område, og er Excels måte å referere til hele matriseområdet på i stedet for å måtte angi i detalj.

  

• Opprett matrisekonstanter fra eksisterende verdier

  Du kan ta resultatene av en formel for overflytende matrise og konvertere dem til bestanddelene. Velg celle D9, og trykk på F2 for å bytte til redigeringsmodus. Trykk på F9 for å konvertere cellereferansene til verdier, som Excel så konverterer til en matrisekonstant. Når du trykker på Enter, skal formelen, =D9#, nå være ={10\20\30.40\50\60.70\80\90}.

• Tell tegn i et celleområde

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

  

  =SUM(LEN(C9:C13))

  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). Hvis du ville få et gjennomsnittlig antall tegn, kan du bruke:

  =GJENNOMSNITT(LEN(C9:C13))

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

  =INDEKS(C9:C13,SAMMENLIGNE(MAKS(LEN(C9:C13)),LEN(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 – vi begynner fra de indre elementene og jobber oss utover. Funksjonen LEN 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 plasseringen) 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:

  STØRST(LEN(C9:C13)

  og den aktuelle strengen finnes i følgende matrise:

  LEN(C9:C13)

  Argumentet samsvarstype i dette tilfellet er 0. Sammenligningstypen kan være verdiene 1, 0 eller -1.

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

  • 0 – returnerer den første verdien som er helt 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 følgende argumenter: en matrise og et rad- og kolonnenummer i den aktuelle matrisen. Celleområdet C9:A13 angir matrisen, SAMMENLIGNE-funksjonen angir 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 STØRST i eksemplet ovenfor med MIN.

• Finn de 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 blitt opprettet med: =INT(RANDARRAY(10,1)*100). Vær oppmerksom på at TILFELDIGMATRISE er en volatil funksjon, slik at du får et nytt sett med tilfeldige tall hver gang Excel beregner.

  

  Skriv inn =N.MINST(B9#;SEKVENS(D9), =N.MINST(B9:B18;{1.2.3})

  Denne formelen bruker en matrisekonstant til å evaluere N.MINST-funksjonen tre ganger og returnere de minste tre medlemmene i matrisen som finnes i celle 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:

  =SUM(N.MINST(B9#,SEKVENS(D9))

  =GJENNOMSNITT(N.MINST(B9#,SEKVENS(D9))

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

  Skriv inn =N.STØRST(B9#,RAD(INDIREKTE("1:3")))eller =N.STØRST(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 en, 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). Radreferansene justeres 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 sine argumenter (det er derfor området 1:10 er omsluttet 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. Like enkelt å bruke SEKVENS:

  =SEQUENCE(10)

  La oss undersøke formelen du brukte tidligere – =N.STØRST(B9#,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 B9:B18 og evalueres tre ganger 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 eksempler med N.MINST, 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 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 er som 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.

Du trenger kanskje å summere verdier basert på betingelser.

Denne matriseformelen summerer for eksempel bare de positive heltallene i et område kalt Salg, som representerer cellene E9:E24 i eksempelet 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((Salg>0)*(Salg<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)+(Salg<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 beregninger, 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ådene ha samme størrelse og ha samme dimensjon. Hvis for eksempel MineData er et område på 3 rader ganger 5 kolonner, må DineData også være 3 rader ganger 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.

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 om Forskjellene mellom datasett-regnearket.

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