Excellence Utvikling

Excel utvikling og VBA makro programmering

Dynamiske matriser er ikke bare nye funksjoner – dem endrer måten vi har brukt Excel

I over et år har dynamiske matriser vært testet i det som kalles «Office Insider» program. Nå har dem etter hvert blitt rullet ut til alle som bruker en oppdatert utgave av Microsoft 365. Disse array funksjoner er ikke bare «vanlige», nye funksjoner. Måten dem funger på er helt annerledes enn det vi er vant til. Microsoft omtalte det med «Nå kommer kunstig intelligens i Excel (AI)».

Når du taster disse formler inn i en celle i Excel, så vil dem automatisk strekke seg over såpass mange celler som nødvendig. I tillegg, når inndataområdet for en slik funksjon vokser, vil denne funksjonen automatisk tilpasse seg deretter.

Du kan gjør svært nyttige ting med disse funksjoner:

  • UNIK: Du får en liste med unike verdier
  • SORTER: Du får en alfabetisk/numerisk sortert liste med data
  • SORTER.ETTER: Sorter et område (over flere kolonner/rader) basert på verdier i samme området
  • FILTRER: Filtrer et område. Du får en liste med verdier som er filtrert etter et eller flere kriterier
  • TRANSPONER: Transponer et område

Dessuten skal vi ta en kort titt på SEKVENS og TILFELDIGMATRISE.

UNIK funksjon - fjern duplikater fra et område med verdier

UNIK funkjonen fjerner alle duplikater fra et område med verdier.

=UNIK(array;[by_col];[exactly_once])

array: En matrise/område i arket som inneholder verdiene

[by_col]: SANN: Returner unike kolonner, USANN: Returner unike rader

[exactly_once]: Returner kun verdier som forekommer bare en gang

I eksempelen under ser du en liste med frukt og vekt i kolonnene B og C. Vil du nå bare ha type frukt uten duplikater, så taster du inn formelen (i E7):

=UNIK(B7:B12)

Som du ser, utvider seg formelen automatisk til E7:E10.

Når du utvider UNIK funksjonen litt, slik at den gjelder for området B7:C12, så ser du (i G7:H11) at du får et resultat mer i listen. Dette fordi «Druer» dukker opp to ganger i resultatet (fordi «kg» er ikke lik med 12, hhv. 14).

En tabell med frukt og vekt i kolonnene B og C. Formelen UNIK i E7 gir en liste med unike frukter, mens en utvidet formel i G7:H11 gir en liste med unike frukter og tilhørende vekt.
Eksempel på UNIK-funksjonen i Excel. Formelen som kun tastes inn i E7 strekker seg umiddelbart fire rader nedover, slik at alle resultater kan vises.

SORTER funksjon – Få en sortert liste med verdier i Excel

SORTER funksjonen leverer en liste med sorterte verdier. Det er mulig å velge hvilken kolonner sorteringen skal skje etter og om det er stigende eller synkende rekkefølge.

=SORTER(matrise;[sorteringsindeks];[sorteringsrekkefølge];[etter kolonne]

matrise: Område/matrise/liste med verdier som skal sorteres

[sorteringsindeks]: Kolonnetall som du skal sortere (her menes relative kolonnetall, for eksempel 2 for andre kolonne i matrisen.

[sorteringsrekkefølge]: Velg om du ønsker å sortere i stigende (1) eller synkende (-1) rekkefølge

[etter_kolonne]: Oppgi om du vil sortere etter rad (USANN) eller kolonne (SANN). Tar du ingen valg her er standard sortering etter rad.

I eksemplet skal vi sortere fruktene. I E7 taster vi inn

=SORTER(B7:C12)

Siden vi lar vekk alle parameter brukes første kolonne for sortering i stigende rekkefølge (sortering etter rad)

Når vi nå endrer litt på formelen (i H7)

=SORTER(B7:C12;2;-1)

sorterer vi etter kolonne nummer 2 (dvs. etter vekt) i synkende rekkefølge.

I eksemplet ser du at vi sorterer listen først uten noe parameter (i kolonne E:F). Da brukes automatisk første kolonne (her «Produkt») for sortering i alfabetisk stigende rekkefølge. Deretter sorterer vi etter kolonne «kg» og i synkende rekkefølge (i kolonne H:I).

Demonstrasjon av SORTER funksjonen - både uten parameter og med parameter
Eksempel på SORTER-funksjonen i Excel. I E7 har vi tastet inn SORTER funksjonen uten flere parameter, som da utfører en alfabetisk sortering i første kolonne med data. I H7 har vi spesifisert at sorteringen skal skje etter kolonne nummer 2 - og da i fallende rekkefølge.

SORTER.ETTER funksjon – Sorter verdier etter en matrise (uten å vise sorteringsmatrisen)

Denne funksjonen er ganske lik SORTER funksjonen. Også den sorterer en matrise med verdier. Men her kan du velge at sorteringen skal skje basert på en annen kolonne – uten at kolonnen du bruker for å bestemme sorteringen er med i resultatet.

=SORTER.ETTER(matrise, etter_matrise1, [sorteringsrekkefølge1], …)

matrise: Matrisen som skal sortes (og vises i den sorterte matrisen)

etter_matrise1: Det er en kolonne som skal bestemme sorteringsrekkefølgen. Merk at data i denne kolonnen ikke kommer med i resultatet

[sorteringsrekkefølge1]: Stigende rekkefølge (1) eller synkende rekkefølge (2)

Legg merke til at du kan legge inn flere «etter_matrise», så det er mulig å bruke flere hierarkier i sorteringen.

I eksemplet som vises ser du at vi igjen sorterer kolonnen «Produkt» og vi bruker kolonnen «kg» for å bestemme sorteringsrekkefølgen. Som du sikkert har lagt merke til, dukker kun kolonnen «Produkt» opp (i kolonne E) – kolonnen «kg» vises ikke, selv om den er i bruk for sortering. Det er den store forskjellen til funksjonen SORTER.

Eksempel på hvordan SORTER.ETTER i Excel tillatter sortering etter en kolonne som ikke vises i resultatet
Med SORTER.ETTER funksjonen kan vi sortere resultatet etter en annen kolonne - uten at sorteringskolonnen dukker opp i resultatet.

FILTRER funksjon – Filtrer en liste med verdier og bruk kriterier

FILTRER funksjonen er kanskje en av de mest avanserte blant de nye dynamiske matrise funksjonene. Vi vil derfor om kort tid publisere en egen artikkel om denne funksjonen. I enkle tilfeller marker du – igjen – området som inneholder dine verdier. Deretter «bygger» du en logikk med kriterier.

=FILTRER(matrise;inkluder;[hvis_tom])

matrise: Området i arket du ønsker å filtrere.

inkluder: Her spesifiserer du betingelser for hvilke rader skal være med. Det er en litt mer kompleks logikk som ligger bak (se egen artikkel som vi publiserer snart). Se også eksemplet under.

[hvis_tom]: Her kan du spesifisere om det skal vises en verdi dersom alle verdier blir filtrert bort.

I eksemplet under ser du først en sortering (kolonne E:F) der betingelsen er at alle produkter «Druer» skal vises. Inkluder setningen blir da (B7:B12="Druer"). I tillegg har vi et eksempel (kolonne H:I) der alle produkter «Druer» med vekt større 13 kg skal vises. Her blir inkluder setning (B7:B12="Druer")*(C7:C12>13). Legg merke til: Excel går gjennom matrisen rad for rad. Når en betingelse er oppfylt blir resultatet 1. Bare når begge betingelser er oppfylt blir produktet 1*1 = 1 (som da betyr at raden vises).

To eksempler på bruk av FILTRER funksjon i Excel. Filtrering med kun en betingelse til venstre og filtrering med flere betingelser til høyre, inkludert forklaring på hvordan filtreringen fungerer.
I eksemplet til venstre bruker vi FILTRER funksjonen for å vise kun rader med produktet "Druer". Til høyre har vi et mer komplekst eksempel, der vi bygger opp en filtrering med to betingelser, "Druer" og større enn 13 kg. Begge betingelser evaluerer enten til "1" eller "0". Kun når begge evaluarer til 1 så vises raden i Excel.

TRANSPONER funksjon - Bytter plass på rader og kolonner

TRANSPONER-funksjonen er et nyttig verktøy når du vil endre formatet på dataene dine. Funksjonen lar deg bytte plass på rader og kolonner i en matrise, slik at kolonnene blir til rader og vice versa.

=TRANSPONER(matrise)

matrise: Området i arket som du ønsker å transponere.

I eksempelet under ser du en matrise (B7:B12) som inneholder data om produkter. Ved hjelp av TRANSPONER-funksjonen (i celle E7) blir matrisen endret slik at kolonnene blir til rader.

TRANSPONER funksjonen leser inn to kolonner med data og bytter ut rad med kolonne. 2 kolonner og 6 rader blir da til 6 kolonner og 2 rader.
Med TRANSPONER funksjonen bytter vi ut rad og kolonne.

SEKVENS og TILFELDIGMATRISE funksjon - Effektivt generere sekvenser og tilfeldige tall

SEKVENS funksjonen lar deg generere en sekvens av tall, datoer eller tekst basert på bestemte kriterier. Du spesifiserer startverdi, sluttverdi og hvor mye hver verdi skal økes eller reduseres.

=SEKVENS(start;slutt;[steg])

start: Startverdien for sekvensen

slutt: Sluttverdien for sekvensen

[steg]: Hvor mye hver verdi skal økes eller reduseres (valgfritt)

TILFELDIGMATRISE funksjonen lar deg generere en matrise med tilfeldige tall. Du kan spesifisere antall rader og kolonner i matrisen, samt spesifisere hvilken type tall du ønsker (heltall, desimaler, etc.).

Kombiner dynamiske matrise funksjoner for optimal dataanalyse – Bruk funksjoner som UNIK, SORTER, FILTRER og SORTER.ETTER sammen

Kombinasjonen av dynamiske matrise funksjoner i Microsoft Excel gir deg mulighet til å analysere og manipulere store mengder data på en effektiv måte. Ved å kombinere funksjoner som UNIK, SORTER, FILTRER og SORTER.ETTER kan du fjerne duplikater, sortere data og filtrere data basert på kriterier, og gjøre det enklere å forstå og analysere store mengder data. Kombiner funksjonene for å oppnå optimal dataanalyse.

UNIK og SORTER funksjonene kan f.eks. kombineres for å fjerne duplikater og sortere en liste på en enkel måte. Først bruker vi UNIK funksjonen for å fjerne duplikater, og deretter bruker vi SORTER funksjonen for å sortere listen etter ønsket kriterium.

=UNIK(SORTER(matrise, [kriterium]))

I eksempeldataene over (B7:B12) har vi en liste med produkter og deres vekt. Vi ønsker å fjerne duplikater og sortere listen etter vekt i stigende rekkefølge.

Først bruker vi UNIK funksjonen for å fjerne duplikater. Deretter nester vi inn en SORTER funksjonen for å sortere listen:

=UNIK(SORTER(B7:B12;2;1))

Listen vises nå med sortert og unike verdier:

Her er det store muligheter for å kombinere dynamiske matriser – også med vanlige Excel funksjoner.

Demonstrasjon av kombinasjon av UNIK og SORTER i Excel. UNIK skaper en unik liste med prodkter mans SORTEr sortere listen alfabetisk.
Her kombinerer vi to dynamiske matrise funksjoner. Den indre funksjonen SORTER leverer først en sortert array med produkter. Den ytre funksjonen UNIK fjerner deretter duplikater. Resultatet blir en sortert og duplikat-fri liste med produkter.

Bruk av #-tegnet for å referere til dynamiske matriser i Microsoft Excel

#-tegnet i Excel er et verktøy for å referere til områder med dynamiske matriser. Med andre ord lar #-tegnet deg oppdatere formler automatisk når størrelsen på en dynamisk matrise endrer seg.

La oss se på et eksempel. Vi har en tabell i området B7:C12, med produkter og vekt.

I celle E7 har vi en UNIK-formel, som lister opp de unike verdiene i kolonne B:

=UNIK(B7:B12)

Resultatet av formelen blir en unik liste med produkter

Ved å bruke #-tegnet, kan vi nå beregne antall forekomster av hver unik verdi ved hjelp av formelen ANTALLA:

=ANTALLA(E7#)

Så med ANTALLA-formelen og #-tegnet kan vi beregne antall forekomster av hver unik verdi i en dynamisk matrise, og resultatet oppdateres automatisk når størrelsen på den dynamiske matrisen endrer seg.

Det er verdt å merke seg at vi selvsagt kunne beregnet antallet på en enklere måte, men vi har valgt dette eksemplet for å demonstrere bruken av #-tegnet i kombinasjon med dynamiske array funksjoner.

På venstre side bruker viser vi hvordan UNIK funksjonen lager en liste med unike produkter. Deretter viser vi hvordan vi refererer til denne listen med # tegnet for å beregne antallet med ANTALLA funksjon.
Med # tegnet er det lett å referere til eksisterende, dynamiske matrise funksjoner i arket. I E7 har vi en UNIK funksjon. I G7 bruker vi ANTALLA funksjon og en #-referanse for å telle hvor mange resultater vi får fra UNIK funksjonen. Det holder å referere til cellen der man har skrevet inn den opprinelige matrisen. Deretter spiller det ingen rolle hvor stort spill-området er.

Last ned eksemplet her:

Last ned Excel fil
dynamiske matriser.xlsx

Ønsker du hjelp med dine Excel utfordringer?

Excellence Utvikling hjelper bedrifter med alle oppgaver rundt Microsoft Excel, blant annet utvikling av kalkyler, dashboards, makroer og mye mer. Ta gjerne kontakt med alle typer spørsmål eller finn ut mer om våre Excel konsulent tjenester. Lik gjerne siden på Facebook, så får du alle nyheter.