UNIKT fra ikke-tilstødende kolonner - Excel-tip

Den anden dag var jeg ved at oprette en unik kombination af to ikke-tilstødende kolonner i Excel. Jeg gør normalt dette med Fjern duplikater eller med Avanceret filter, men jeg troede, jeg ville prøve at gøre det med den nye UNIQUE-funktion, der kom til Office 365 i 2019. Jeg prøvede flere ideer, og ingen ville fungere. Så jeg gik til mesteren i Dynamic Arrays, Joe McDaid, for at få hjælp. Svaret er ret sejt, og jeg er sikker på, at jeg vil glemme det, så jeg dokumenterer det for dig og for mig. Jeg er sikker på, at jeg om to år vil google, hvordan man gør dette og indse "Åh, se! Det er mig, der skrev artiklen om dette!"

Inden du går til UNIK funktion, skal du kigge på, hvad jeg prøver at gøre. Jeg vil have enhver unik kombination af salgsrepræsentant fra kolonne B og produkt fra kolonne C. Normalt vil jeg følge disse trin:

Kopier overskrifterne fra B1 og D1 til et tomt afsnit på regnearket
Fra B1 skal du vælge Data, Filter, Avanceret
I dialogboksen Avanceret filter skal du vælge Kopier til en ny placering
Angiv overskrifterne fra trin 1 som outputområdet
Vælg feltet for Kun unikke værdier
Klik på OK

Kopier de to overskrifter til et tomt afsnit, der bliver outputområdet

Resultatet er enhver unik kombination af de to felter. Bemærk, at det avancerede filter ikke sorterer elementerne - de vises i den oprindelige rækkefølge.

At få en unik liste er en af mine foretrukne anvendelser til Advanced Filter

Denne proces blev lettere i Excel 2010 takket være kommandoen Fjern duplikater på fanen Data på båndet. Følg disse trin:

Vælg B1: D227 og Ctrl + C for at kopiere
Indsæt i et tomt afsnit af regnearket.

Lav en kopi af dataene, da Fjern duplikater er ødelæggende
Vælg Data, Fjern duplikater
Fjern markeringen af dato i dialogboksen Fjern duplikater. Dette fortæller Excel at kun se på Rep og Product.
Klik på OK

Fortæl Fjern duplikater for kun at overveje rep og dato

Resultaterne er næsten perfekte - du skal bare slette kolonnen Dato.

Slet den ekstra kolonne

Spørgsmålet: Er der en eller anden måde at få UNIQUE-funktionen til kun at se på kolonner B & D? (Hvis du ikke har set den nye UNIQUE-funktion endnu, skal du læse: UNIQUE-funktionen i Excel.)

At bede om =UNIQUE(B2:D227)ville give dig alle unikke kombinationer af rep, dato og produkt, hvilket ikke er det, vi leder efter.

Hvordan kan vi sende to ikke-tilstødende kolonner til UNIQUE-funktionen?

Da dynamiske arrays blev introduceret i september, sagde jeg, at vi aldrig mere skulle bekymre os om kompleksiteten af Ctrl + Shift + Enter-formler. Men for at løse dette problem skal du bruge et koncept kaldet Lifting. Forhåbentlig har du nu downloadet min Excel Dynamic Arrays Straight To The Point e-bog. Vend til side 31-33 for en komplet forklaring af løft.

Se min bog for en komplet forklaring på løft (og senere, når du går for at sortere resultaterne, parvis løft)

Tag en Excel-funktion, der forventer en enkelt værdi. F.eks. =CHOOSE(Z1,"Apple","Banana")Ville returnere enten Apple eller Banana afhængigt af om Z1 indeholder 1 (for Apple) eller 2 (for Banana). VÆLG-funktionen forventer en skalar som det første argument.

Men i stedet sender du en matrixkonstant på (1,2) som det første argument til VÆLG. Excel udfører løfteoperationen og beregner VÆLG to gange. For værdien 1 skal du have sælgere i B2: B227. For værdien 2 skal du have produkterne i D2: D227.

Bed VÆLG om at returnere to svar

Normalt, i gammel Excel, ville implicit kryds have skruet op for resultaterne. Men nu hvor Excel kan spilde resultater til mange celler, returnerer formlen ovenfor med succes en matrix med alle svar i B og D:

Succes! Det hele er ned ad bakke herfra

Jeg har lyst til at fornærme din intelligens for at skrive resten af artiklen, for herfra er det superenkelt.

Indpak formlen fra det forrige skærmbillede i UNIK, og du får bare de unikke kombinationer af salgsrepræsentant og produkt ved hjælp =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Stadig ikke sorteret

For at kontrollere din forståelse, prøv at ændre ovenstående formel for at returnere alle unikke kombinationer af tre kolonner: Salgsrepræsentant, produkt, farve.

Først skal du ændre arraykonstanten for at henvise til (1,2,3).

Derefter skal du føje et fjerde argument for at vælge at returnere farve fra E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Returner den unikke kombination af tre kolonner

Det ville være rart at sortere disse resultater, så vi vender os til Sort med en formel ved hjælp af SORT og SORTBY.

Normalt vil funktionen til at sortere efter den første kolonne stigende være =SORT(Array)eller =SORT(Array,1,1).

For at sortere efter tre kolonner skal du løfte parvis med =SORT(Array,(1,2,3),(1,1,1)). I denne formel, når du kommer til det andet argument i SORT, vil Excel vide, hvilken kolonne der skal sorteres. I stedet for en enkelt værdi skal du sende tre kolonner inde i en arraykonstant: (1,2,3). Når du kommer til det tredje argument, hvor du angiver 1 for stigende eller -1 for faldende, skal du sende en matrixkonstant med tre 1'er for at angive stigende, stigende, stigende. Følgende skærmbillede viser =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

For mere om parvise løft, se side 34 i Excel Dynamic Arrays lige til punktet.

I det mindste indtil slutningen af 2018 kan du downloade Excel Dynamic Arrays-bogen gratis ved hjælp af linket i bunden af denne side.

Jeg opfordres til at finde ud af, at svaret på dagens spørgsmål er lidt kompliceret. Da dynamiske arrays kom ud, tænkte jeg øjeblikkeligt på alle de fantastiske formler, der blev sendt på opslagstavlen af Aladin Akyurek og andre, og hvordan disse formler ville blive langt enklere i det nye Excel. Men dagens eksempel viser, at der stadig vil være behov for formelgenier til at skabe nye måder at bruge de dynamiske arrays på.