UNIKT fra ikke-tilstøtende kolonner - Excel-tips

Forleden var jeg i ferd med å lage en unik kombinasjon av to ikke-tilstøtende kolonner i Excel. Jeg pleier å gjøre dette med Fjern duplikater eller med Advanced Filter, men jeg trodde jeg skulle prøve å gjøre det med den nye UNIQUE-funksjonen som kom til Office 365 i 2019. Jeg prøvde flere ideer og ingen ville fungere. Så jeg gikk til mesteren i Dynamic Arrays, Joe McDaid, for å få hjelp. Svaret er ganske kult, og jeg er sikker på at jeg vil glemme det, så jeg dokumenterer det for deg og for meg. Jeg er sikker på at jeg om to år vil google hvordan jeg gjør dette og innse "Å, se! Jeg er den som skrev artikkelen om dette!"

Før du går til UNIQUE-funksjonen, kan du ta en titt på hva jeg prøver å gjøre. Jeg vil ha alle unike kombinasjoner av salgsrepresentant fra kolonne B og produkt fra kolonne C. Normalt vil jeg følge disse trinnene:

Kopier overskriftene fra B1 og D1 til en tom del av regnearket
Fra Data B1 velger du Data, Filter, Avansert
I dialogboksen Avansert filter velger du Kopier til en ny plassering
Spesifiser overskriftene fra trinn 1 som utdataområdet
Velg boksen for bare unike verdier
Klikk OK

Kopier de to overskriftene til en tom del som blir utgangsområdet

Resultatet er hver unike kombinasjon av de to feltene. Merk at Advanced Filter ikke sorterer elementene - de vises i den originale sekvensen.

Å få en unik liste er en av mine favorittbruk for Advanced Filter

Denne prosessen ble enklere i Excel 2010 takket være kommandoen Fjern duplikater i kategorien Data på båndet. Følg disse trinnene:

Velg B1: D227 og Ctrl + C for å kopiere
Lim inn i en tom del av regnearket.

Lag en kopi av dataene siden Fjern duplikater er ødeleggende
Velg Data, Fjern duplikater
Fjern merket for dato i dialogboksen Fjern duplikater. Dette forteller Excel å bare se på Rep og Product.
Klikk OK

Fortell Fjern duplikater for å bare vurdere rep og dato

Resultatene er nesten perfekte - du må bare slette dato-kolonnen.

Slett den ekstra kolonnen

Spørsmålet: Er det noen måte å la UNIQUE-funksjonen se på bare kolonnene B & D? (Hvis du ikke har sett den nye UNIQUE-funksjonen ennå, kan du lese: UNIQUE-funksjonen i Excel.)

Å be om =UNIQUE(B2:D227)vil gi deg alle unike kombinasjoner av rep, dato og produkt som ikke er det vi leter etter.

Hvordan kan vi sende to ikke-tilstøtende kolonner til UNIQUE-funksjonen?

Da Dynamic Arrays ble introdusert i september, sa jeg at vi aldri trenger å bekymre deg for kompleksiteten til Ctrl + Shift + Enter formler lenger. Men for å løse dette problemet, skal du bruke et konsept som heter Lifting. Forhåpentligvis har du nå lastet ned min Excel Dynamic Arrays Straight To The Point e-bok. Gå til side 31-33 for en fullstendig forklaring på løfting.

Se boken min for en fullstendig forklaring på løfting (og senere, når du går for å sortere resultatene, parvis løfting)

Ta en Excel-funksjon som forventer en enkelt verdi. Vil for eksempel =CHOOSE(Z1,"Apple","Banana")returnere enten Apple eller Banana, avhengig av om Z1 inneholder 1 (for Apple) eller 2 (for Banana). VELG-funksjonen forventer en skalar som det første argumentet.

Men i stedet skal du passere en matrisekonstant på (1,2) som det første argumentet for å VELGE. Excel vil utføre løfteoperasjonen og beregne VELG to ganger. For verdien 1 vil du ha selgere i B2: B227. For verdien 2 vil du ha produktene i D2: D227.

Be VELG om å returnere to svar

Normalt, i gamle Excel, ville implisitt skjæringspunkt ha skrudd opp resultatene. Men nå som Excel kan søle resultater til mange celler, returnerer formelen ovenfor en rekke med alle svarene i B og D:

Suksess! Det hele er utfor herfra

Jeg føler at jeg ville fornærme intelligensen din for å skrive resten av artikkelen, for herfra er det superenkelt.

Pakk formelen fra forrige skjermbilde i UNIK, og du får bare de unike kombinasjonene av salgsrepresentant og produkt som brukes =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Fortsatt ikke sortert

For å kontrollere forståelsen din, prøv å endre formelen ovenfor for å returnere alle unike kombinasjoner av tre kolonner: Salgsrepresentant, produkt, farge.

Først endrer du arraykonstanten for å referere til (1,2,3).

Deretter kan du legge en fjerde argument for å velge å returnere farge fra E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Returner den unike kombinasjonen av tre kolonner

Det ville være fint å sortere resultatene, så vi går til Sorter med en formel ved å bruke SORT og SORTBY.

Normalt vil funksjonen som skal sorteres etter den første kolonnen stigende være =SORT(Array)eller =SORT(Array,1,1).

For å sortere etter tre kolonner, må du løfte parvis med =SORT(Array,(1,2,3),(1,1,1)). I denne formelen, når du kommer til det andre argumentet til SORT, vil Excel vite hvilken kolonne du skal sortere. I stedet for en enkelt verdi, send tre kolonner inne i en matrisekonstant: (1,2,3). Når du kommer til det tredje argumentet der du angir 1 for stigende eller -1 for synkende, send en matrisekonstant med tre 1 for å indikere stigende, stigende, stigende. Følgende skjermbilde viser =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

For mer om parvis løfting, se side 34 i Excel Dynamic Arrays Straight to the Point.

I det minste til slutten av 2018 kan du laste ned Excel Dynamic Arrays-boken gratis ved hjelp av lenken nederst på denne siden.

Jeg oppfordres til å oppdage at svaret på dagens spørsmål er litt komplisert. Da Dynamic Arrays kom ut, tenkte jeg øyeblikkelig på alle de fantastiske formlene som ble lagt ut på Message Board av Aladin Akyurek og andre, og hvordan disse formlene ville bli langt enklere i det nye Excel. Men dagens eksempel viser at det fortsatt vil være behov for formelgenier for å lage nye måter å bruke Dynamic Arrays på.