Retningslinjer og eksempler på matriseformler

Retningslinjer og eksempler på matriseformler

Obs!: Vi ønsker å gi deg det nyeste hjelpeinnholdet så raskt som mulig, på ditt eget språk. Denne siden er oversatt gjennom automatisering og kan inneholde grammatiske feil eller unøyaktigheter. Formålet vårt er at innholdet skal være nyttig for deg. Kan du fortelle oss om informasjonen var nyttig for deg nederst på denne siden? Her er den engelske artikkelen for enkel referanse.

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

Når du begynner med oppdateringen for september 2018 for Office 365, vil alle formler som kan returnere flere resultater, automatisk flyte opp eller opp i nabo cellene. Denne endringen i virke måte er også fulgt av flere nye dynamiske array-funksjoner. Dynamiske matriseformler, enten de bruker eksisterende funksjoner eller de dynamiske array-funksjonene, trenger bare å være inn data i én enkelt celle, og deretter bekreftes ved å trykke på Enter. Tidligere krever eldre matriseformler først å merke hele utdataområdet, og deretter bekrefte formelen med CTRL + SKIFT + ENTER. De kalles ofte CSE -formler.

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

  • Opprett eksempel data sett raskt.

  • Telle antall tegn i et celle område.

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

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

Eksemplene nedenfor viser hvordan du oppretter matriseformler med flere celler og én celle. Hvis det er mulig, har vi inkludert eksempler med noen av de dynamiske array-funksjonene, samt eksisterende matriseformler som er angitt som både dynamiske og gamle matriser.

Laste ned eksemplene våre

Last ned en eksempel arbeids bok med alle matriseformler-eksemplene i denne artikkelen.

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

  • Flercellet matriseformel

    Matrise med flere celler i celle H10 = F10: F19 * G10: G19 til å beregne antall biler som er solgt med enhets pris

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

    Når du trykker Enter, vil du se resultatene flyte ned til cellene H10: H19. Legg merke til at søl-området er uthevet med en kant linje når du merker en celle i søl-området. Du kan også legge merke til at formlene i cellene H10: H19 er nedtonet. De er bare der for referanse, så hvis du vil justere formelen, må du merke celle H10, der mal formelen bor.

  • Matriseformel med én celle

    Matriseformel med én celle for å beregne en samlet sum med = Summer (F10: F19 * G10: G19)

    Skriv inn eller kopier og lim inn = Summer (F10: F19 * G10: G19)i celle H20 i eksempel arbeids boken, og trykk deretter Enter.

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

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

  • Dynamiske matriseformler har også følgende fordeler:

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

    • Trygghet    Du kan ikke overskrive en komponent av en matriseformel med flere celler. Klikk for eksempel celle h11, og trykk Slett. Excel endrer ikke mat risens 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. Eksempel på bil salg bruker én matriseformel til å beregne resultatene i kolonne E. Hvis du har brukt standard formler, for eksempel = F10 * G10, F11 * G11, F12 * G12, osv., har du brukt 11 forskjellige formler til å beregne de samme resultatene. Det er ikke en stor avtale, men hva om du hadde tusenvis av rader til totalt? Deretter kan det gi en stor forskjell.

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

    • Går    Dynamiske matriseformler vil automatisk flyte inn i utdata-området. Hvis kilde dataene er i en Excel-tabell, vil de dynamiske matriseformlene automatisk endre størrelse etter hvert som du legger til eller fjerner data.

    • #SPILL! oppstår    Dynamiske matriser innført #SPILL!-feil, som angir at det tiltenkte søl-området er blokkert av en eller annen grunn. Når du løser blokk beholdet, flyter formelen automatisk.

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

= {1; 2; 3; 4; 5} eller = {"januar", "februar", "mars"}

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

Fremgangsmåtene nedenfor gir deg øvelse i å opprette vannrette, loddrette og todimensjonale konstanter. Vi viser eksempler ved bruk av SEQUENCE-funksjonen til automatisk å generere matrisekonstanter, samt angitte 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). SEQUENCE-funksjonen bygger en 1 rad med fem Kol onne matriser på samme måte som = {1; 2; 3; 4; 5}. Følgende resultat vises:

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

  • Opprett loddrette konstanter

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

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

  • Opprett todimensjonale konstanter

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

    Opprette en 3-rad med 4 Column array-konstant med = SEKVENS (3; 4)

    Du kan også skrive inn: eller = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, men du ønsker å betale oppmerksomheten til stedet der du setter inn semikolon i forhold til komma.

    Som du kan se, gir SEKVENS-alternativet betydelige fordeler ved å skrive inn matrisekonstant verdier manuelt. Det er primært at du sparer tid, men det kan også hjelpe å redusere feil fra manuell registrering. Det er også enklere å lese, spesielt hvis semikolonet kan være vanskelig å skille fra kommategnene.

Her er et eksempel som bruker matrisekonstanter som en del av en større formel. Gå til konstanten i et formel regne ark i eksempel arbeids boken, eller Opprett et nytt regne ark.

I celle D9 skrev vi inn = sekvens (1; 5; 3; 1), men du kan også angi 3, 4, 5, 6 og 7 i celle A9: H9. Det finnes ingenting spesielt om det bestemte nummeret, og vi har nettopp valgt noe annet enn 1-5 for differensiering.

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

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

SEKVENS-funksjonen bygger ekvivalenten til matrisekonstanten {1; 2; 3; 4; 5}. Siden Excel utfører operasjoner på uttrykk omsluttet i parentes først, er de to neste elementene som kommer til spillet, celle verdiene i D9: H9 og multiplikasjon (*). 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 verdiene og returnerer 85.

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

= Summer (sekvens (1; 5; 3; 1) * sekvens (1; 5))eller = Summer ({3; 4; 5; 6; 7} * {1; 2; 3; 4; 5})

Elementer du kan bruke i matrisekonstanter

  • Matrisekonstanter kan inneholde tall, tekst, logiske verdier (for eksempel sann og USANN) og feil verdier, for eksempel #N/A. Du kan bruke tall i hel tall, desimaler og vitenskapelige formater. Hvis du inkluderer tekst, må du omslutte den med anførsels tegn ("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 _GT_ definerte navn > definere navn. Skriv inn 1Kvartal i navn -boksen. Skriv inn konstanten nedenfor i boksen Refererer til (husk å skrive inn klammeparentesen manuelt):

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

Dialog boksen skal nå se slik ut:

Legge til en navngitt matrisekonstant fra formler > definerte navn > navne behandling > ny

Klikk OK, og velg deretter en hvilken som helst rad med tre tomme celler, og skriv inn = 1kvartal.

Følgende resultat vises:

Bruk en navngitt matrisekonstant i en formel, for eksempel = 1Kvartal, der 1Kvartal er definert som = {"januar", "februar"}

Hvis du vil at resultatene skal flyte loddrett i stedet for vannrett, kan du bruke =TRANSPONER(1kvartal).

Hvis du vil vise en liste med 12 måneder, for eksempel du kan bruke når du lager et regnskaps utdrag, kan du basere en av det gjeldende året med SEKVENS-funksjonen. Det er en flott ting om 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å de navngitte matrisekonstantene og hurtig eksempel data sett regne arkene i eksempel arbeids boken.

= TEKST (dato (år (idag ()), SEKVENS (1; 12); 1); "MMM")

Bruk en kombinasjon av funksjonene tekst, dato, år, dag og SEKVENS til å bygge en dynamisk liste med 12 måneder

Dette bruker Dato-funksjonen til å opprette en dato basert på inne værende år, oppretter sekvensen en matrisekonstant fra 1 til 12 for januar til desember, og tekst-funksjonen konverterer visnings formatet til "MMM" (Jan, Feb, Mar osv.). Hvis du vil vise hele måneds navnet, for eksempel januar, bruker du «mmmm».

Når du bruker en navngitt konstant som en matriseformel, må du huske å skrive inn likhets tegnet, for eksempel in = 1Kvartal, ikke bare 1Kvartal. Hvis du ikke gjør det, tolker Excel matrisen som en streng med tekst, og formelen vil ikke fungere som forventet. Husk til slutt at du kan bruke kombinasjoner av funksjoner, tekst og tall. Alt dette avhenger av hvordan kreativiteten du vil ha.

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

  • Flere elementer i en matrise

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

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

  • Kvadrer elementene i en matrise

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

  • Finne kvadrat roten av de kvadrerte elementene i en matrise

    ENTER =rot(sekvens (1; 12) ^ 2)eller = rot ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Transponer en endimensjonal rad

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

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

    ENTER = TRANSPONER (sekvens (3; 4)), eller = TRANSPONER ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10, 11; 12})

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

Denne delen inneholder eksempler på enkle matriseformler.

  • Opprett matriser fra eksisterende verdier

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

    ENTER = sekvens (3; 6; 10; 10)eller = {10; 20; 30; 40; 50; 60; 70; 80, 90100110120; 130140150160170180}

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

    Deretter ENTER = D9 #, eller = D9: I11 i en tom celle. En 3 x 6-matrise med celler vises med de samme verdiene som du ser i D9: D11. #-Tegnet kalles en flyte område operator, og det er Excel's måte å referere til hele matrisen i, i stedet for å måtte skrive det ut.

    Bruke operatoren med flyte område (#) til å referere til en eksisterende matrise

  • Opprett matrisekonstanter fra eksisterende verdier

    Du kan gjøre resultatene av en nedflytert matriseformel og konvertere den til komponent delene. Merk celle D9, og trykk deretter F2 for å bytte til redigerings modus. Deretter trykker du F9 for å konvertere celle referansene til verdier, som Excel deretter konverterer til en matrisekonstant. Når du trykker Enter, skal formelen, = D9 #, nå være = {10; 20; 30; 40, 50, 60; 70, 80, 90}.

  • Tell tegn i et celleområde

    Følgende eksempel viser deg hvordan du kan telle antall tegn i et celle område. Dette inkluderer mellomrom.

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

    = SUMMER (LENGDE (C9: C13))

    I dette tilfellet returnerer Len-funksjonen lengden på hver tekst streng i hver av cellene i området. SUMMER-funksjonen adderer deretter verdiene sammen og viser resultatet (66). Hvis du ville få gjennomsnittlig antall tegn, kan du bruke:

    = GJENNOMSNITT (LENGDE (C9: C13))

  • Innhold av lengste celle i området C9: C13

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

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

    La oss ta en nærmere titt på formelen, og starte fra de indre elementene og arbeide utover. Lengde- funksjonen returnerer lengden på hvert av elementene i celle området D2: D6. Maksimum-funksjonen beregner den største verdien blant de elementene, som tilsvarer den lengste tekst strengen, som er i celle D3.

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

    MAKS (LENGDE (C9: C13)

    og den aktuelle strengen finnes i følgende matrise:

    LENGDE (C9: C13)

    Argumentet samsvars type i dette tilfellet er 0. Samsvars typen kan være en verdi på 1, 0 eller-1.

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

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

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

    • Hvis du utelater en sammenligningstype, forutsettes verdien 1.

    Til slutt tar indeks-funksjonen disse argumentene: en matrise og et rad-og Kol onne nummer i matrisen. Celle området C9: C13 gir matrisen, funksjonen sammenligne inneholder celle adressen, og det endelige argumentet (1) angir at verdien kommer fra den første kolonnen i matrisen.

    Hvis du vil hente innholdet i den minste tekst strengen, erstatter du MAX i eksemplet ovenfor med min.

  • Finn de n minste verdiene i et område

    Dette eksemplet viser hvordan du finner de tre minste verdiene i et celle område, der en matrise med eksempel data i cellene B9: B18has er opprettet med: = Int (tilfeldigmatrise(10; 1) * 100). Vær oppmerksom på at TILFELDIGMATRISE er en flyktig funksjon, slik at du får et nytt sett med tilfeldige tall hver gang Excel beregnes.

    Excel-matriseformel for å finne den n-te minste verdien: = SMALL (B9 #, SEQUENCE (D9))

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

    Denne formelen bruker en matrisekonstant for å evaluere den lille funksjonen tre ganger og returnerer de minste 3 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:

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

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

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

    Hvis du vil finne de største verdiene i et område, kan du erstatte den lille funksjonen med store-funksjonen. I tillegg bruker eksemplet nedenfor RAD- og INDIREKTE-funksjonene.

    ENTER = store (B9 #, rad (indirekte ("1:3"))); eller = stor (B9: B18; rad (indirekte ("1:3") ))

    På dette tidspunktet kan det være nyttig å vite litt om RAD- og INDIREKTE-funksjonene. Du kan bruke RAD-funksjonen til å opprette en matrise med etterfølgende heltall. Velg for eksempel et tomt og 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). Excel justerer rad referansene, og formelen genererer nå hel tall fra 2 til 11. Du kan løse dette problemet ved å legge til INDIREKTE-funksjonen i formelen:

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

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

    = SEKVENS (10)

    La oss undersøke formelen som du brukte tidligere – = store (B9 #, rad (indirekte ("1:3"))))))))))))))))); i dette tilfellet verdiene 1 til 3. RAD-funksjonen i Aktiver genererer en Kol onne matrise med tre celler. STORE-funksjonen bruker verdiene i celle området B9: B18, og den evalueres tre ganger, én gang for hver referanse som returneres av rad-funksjonen. Hvis du vil finne flere verdier, legger du til et større celle område i den indirekte funksjonen. Til slutt, som med de små eksemplene, 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 feil verdi, for eksempel #VALUE! eller #N/A. Dette eksemplet viser hvordan du kan summere verdiene i et område kalt data som inneholder feil:

    Bruk matriser til å håndtere feil. Eksempel: = Summer (hvis (ERFEIL (data), ""; data) vil summere området kalt data selv om det inneholder feil, som #VALUE! eller #NA!.

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

    Formelen oppretter en ny matrise som inneholder de opprinnelige verdiene minus eventuelle feil verdier. Når du starter fra de indre funksjonene og arbeider utover, søker funksjonen ERFEIL i celle området (data) for feil. Hvis-funksjonen returnerer en bestemt verdi hvis en betingelse du angir, evalueres til sann og en annen verdi hvis den evalueres til Usann. I dette tilfellet returnerer den tomme strenger ("") for alle feil verdier fordi de evalueres til sann, og returnerer de gjenværende verdiene fra området (data) fordi de evalueres til USANN, noe som betyr at de ikke inneholder feil verdier. SUMMER-funksjonen beregner deretter totalen for den filtrerte matrisen.

  • Tell antall feilverdier i et område

    Dette eksemplet er lik den forrige formelen, men returnerer antallet feil verdier i et område med navnet data i stedet for å filtrere dem ut:

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

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

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

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

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

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

Du trenger kanskje å summere verdier basert på betingelser.

Du kan bruke matriser til å beregne basert på bestemte betingelser. = Summer (hvis (Sales>0; salg)) summerer alle verdier som er større enn 0 i et område som heter salg.

Denne matriseformelen summerer for eksempel bare de positive hel tallene i et område som heter salg, som representerer cellene E9: E24 i eksemplet ovenfor:

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

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

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

= Summer ((Sales>0) * (Sales<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 ((Sales>0) + (Sales<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. Med andre ord utfører du matematikk operasjoner, for eksempel addisjon eller multiplikasjon på verdier som oppfyller OR-or og AND-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å celle områdene ha samme størrelse og samme dimensjon. Hvis for eksempel MineData er et område på tre rader med 5 kolonner, må DineData også være 3 rader x 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 * (MyData<>YourData))

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

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

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

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

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

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

Du finner lignende eksempler i eksempel arbeids boken på forskjeller mellom DataSet -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 hele tabellen nedenfor, og lim den inn i celle a1 i et tomt regne ark.

Salg Person

Bil Skriv inn

Nummer Solgt

Enhet Pris

Totalt antall 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 Coupe og sedan for hver selger, merker du cellene E2: E11, skriver inn formelen = C2: C11 * D2: D11, og deretter trykker du CTRL + SKIFT + ENTER.

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

Når du trykker på CTRL + SKIFT + ENTER, omgir Excel formelen med klamme parenteser ({}) og setter inn en forekomst av formelen i hver celle i det merkede området. Dette skjer veldig raskt, slik at det du ser i kolonne E, er totalt salgs beløp for hver bil type for hver enkelt selger. Hvis du velger E2, velger du E3, E4 og så videre, ser du at den samme formelen vises: {= C2: C11 * D2: D11}

Totalene i kolonne E beregnes av en matriseformel

  • Opprett en encellet matriseformel

Skriv inn formelen nedenfor i celle D13 i arbeids boken, og trykk deretter CTRL + SKIFT + ENTER:

=SUMMER(C2:C11*D2:D11)

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

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

Matriseformler har også følgende fordeler:

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

  • Trygghet    Du kan ikke skrive over en komponent i en flercellet matriseformel. Klikk for eksempel celle E3, og trykk 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 sikkerhets tiltak 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.

Vanligvis bruker matriseformler standard formel syn taks. De begynner med et likhets tegn (=), og du kan bruke de innebygde Excel-funksjonene i matriseformlene. Hoved forskjellen er at når du bruker en matriseformel, trykker du CTRL + SKIFT + ENTER for å skrive inn formelen. Når du gjør dette, omslutter Excel matriseformelen med klamme parenteser – hvis du skriver inn klamme parentesene manuelt, konverteres formelen til en tekst streng, og den vil ikke fungere.

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

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

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

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

  • Du kan ikke endre innholdet i én enkelt celle i en matriseformel. Hvis du vil prøve dette, velger du celle E3 i arbeids boken, og trykker Slett. Excel viser en melding som forteller deg at du ikke kan endre deler 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 formel området (for eksempel E2: E11), og deretter trykker du del.

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

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

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

  • Du kan av og til 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. Hvis andre personer må endre arbeids bøker, må du derfor unngå matriseformler eller forsikre deg om at de andre kjenner til matriseformler og forstår hvordan du endrer dem, hvis de trenger det.

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

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

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

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

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

Her er en matrise i en 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 den to rad mat risen 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 regne ark.

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

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

    I dette tilfellet bør du skrive inn åpnings-og høyre klamme parenteser ({}), og Excel vil legge til det andre settet for deg.

    Følgende resultat vises:

    Vannrett matrisekonstant i formel

Opprett loddrette konstanter

  1. Merk en kolonne med fem celler i arbeidsboken.

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

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

    Følgende resultat vises:

    Loddrett matrisekonstant i matriseformel

Opprett todimensjonale konstanter

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

  2. Skriv inn følgende formel på formel linjen, 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 a3, og trykk deretter CTRL + SKIFT + ENTER:

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

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

    Matriseformel med matrisekonstant

    Verdien 85 vises i celle A3.

Neste del inneholder informasjon om hvordan formelen fungerer.

Formelen du nettopp brukte, inneholder flere deler.

Syntaks for matriseformel med matrisekonstant

1. Funksjon

2. Lagret matrise

3. Operator

4. Matrisekonstant

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

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

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

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

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

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

Hvis du vil prøve dette, kan du kopiere funksjonen, velge en tom celle i arbeids boken, lime inn formelen på formel linjen 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})

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

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

En av de beste måte å 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.
    Dialog boksen 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.

Se etter problemene nedenfor når matrisekonstantene ikke fungerer:

  • Noen elementer er kanskje ikke skilt med riktig tegn. Hvis du utelater et komma eller semikolon, eller hvis du setter inn et på feil sted, kan det hende at matrisekonstanten ikke opprettes på riktig måte, eller at det vises 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 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 følgende konstant, og trykk deretter CTRL + SKIFT + ENTER:

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

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

Denne delen inneholder eksempler på enkle matriseformler.

Opprett matriser og matrisekonstanter fra eksisterende verdier

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

Opprett matriser fra eksisterende verdier

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

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

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

  2. Trykk CTRL + SKIFT + ENTER, som legger inn denne matrisen med tall i celle området C8: E10 ved å bruke en matriseformel. C8 til E10 skal se slik ut i regnearket ditt:

    10

    20

    innen

    40

    50

    60

    70

    80

    90

  3. Merk celleområdet C1 til E3.

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

    =C8:E10

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

Opprett matrisekonstanter fra eksisterende verdier

  1. Med cellene C1: C3 valgt, trykker du F2 for å bytte til redigerings modus. 

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

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

Tell tegn i et celleområde

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

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

    Data

    Dette er

    en rekke celler som

    samles sammen

    for å gi en

    enkelt setning.

    Totalt antall tegn i A2:A6

    =SUMMER(LENGDE(A2:A6))

    Innhold i den lengste cellen (A3)

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

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

  3. Merk celle A10, og trykk deretter CTRL + SKIFT + ENTER for å vise innholdet i den lengste av cellene a2: a6 (celle a3).

Følgende formel brukes i celle A8 og teller det totale antallet tegn (66) i celle a2 til og med a6.

=SUMMER(LENGDE(A2:A6))

I dette tilfellet returnerer LENGDE-funksjonen lengden på hver tekststreng i hver av cellene i området. Summer -funksjonen adderer deretter verdiene sammen 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 celle a1: tekst-området.

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

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

    = LITEN (A1: GJØDSEL; {1; 2; 3})

Denne formelen bruker en matrisekonstant for å evaluere den lille funksjonen tre ganger og returnerer den minste (1), andre minste (2) og tredje minste (3) medlemmer i matrisen som finnes i celle a1: A10 for å finne flere verdier, legger du til flere argumenter i konstant. Du kan også bruke tilleggsfunksjoner med denne formelen, for eksempel SUMMER eller GJENNOMSNITT. For eksempel:

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

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

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

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

  1. Merk cellene D1 til D3.

  2. Skriv inn denne formelen i formel linjen, og trykk deretter CTRL + SKIFT + ENTER:

    = STORE (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. Velg for eksempel en tom kolonne med 10 celler i øvelses arbeids boken, 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 – = store (A5: A14, rad (indirekte ("1:3"))))))) (fra og med de indre parentesene og arbeidet utover: den indirekte funksjonen returnerer et sett med tekst verdier, i dette tilfellet verdiene 1 til 3. Rad -funksjonen i Aktiver genererer en Kol onne tabell med tre celler. Store -funksjonen bruker verdiene i celle områ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 tabell Kol onne gruppen på tre celler. Hvis du vil finne flere verdier, legger du til et større celle område i den 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 eksempel på forrige tekst streng, Skriv inn følgende formel i en tom celle, og trykk CTRL + SKIFT + ENTER:

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

Teksten «mange celler som vises.

La oss ta en nærmere titt på formelen, og starte fra de indre elementene og arbeide utover. Lengde -funksjonen returnerer lengden på hvert av elementene i celle området a2: a6. Maksimum -funksjonen beregner den største verdien blant de elementene, som tilsvarer den lengste tekst strengen, som er i celle a3.

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

(MAKS (LENGDE (A2: A6))

og den aktuelle strengen finnes i følgende matrise:

LENGDE (A2: A6)

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

Til slutt tar INDEKS-funksjonen følgende argumenter: en matrise og et rad- og kolonnenummer i den aktuelle matrisen. Celle området a2: a6 gir matrisen, funksjonen sammenligne inneholder celle adressen, 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 feil verdier. Når du starter fra de indre funksjonene og arbeider utover, søker funksjonen ERFEIL i celle området (data) for feil. Hvis -funksjonen returnerer en bestemt verdi hvis en betingelse du angir, evalueres til sann og en annen verdi hvis den evalueres til Usann. I dette tilfellet returnerer den tomme strenger ("") for alle feil verdier fordi de evalueres til sann, og returnerer de gjenværende verdiene fra området (data) fordi de evalueres til USANN, noe som betyr at de ikke inneholder feil verdier. 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 * (MyData<>YourData))

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

Finn plasseringen til maksimumsverdien i et område

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

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

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

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

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

Kvittering

Deler av denne artikkelen var basert på en serie med Excel Power User-kolonner som er skrevet av Karen Berg, og som er tilpasset fra Kapittel 14 og 15 i Excel 2002-formler, en bok skrevet av John Walkenbach, en tidligere Excel MVP.

Trenger du mer hjelp?

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

Se også

Dynamiske matriser og overflytende matrise

Formler for dynamiske matriseformler kontra kjeder med eldre CSE

FILTER-funksjonen

TILFELDIGMATRISE-funksjonen

SEKVENS-funksjonen

ENKEL-funksjonen

SORTER-funksjonen

SORTER ETTER-funksjonen

UNIK-funksjonen

#SPILL! feil i Excel

Oversikt over formler

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

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

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

×