UNIKT från icke-angränsande kolumner - Excel-tips

Häromdagen skulle jag skapa en unik kombination av två icke-intilliggande kolumner i Excel. Jag brukar göra detta med Ta bort dubbletter eller med Avancerat filter, men jag trodde att jag skulle försöka göra det med den nya UNIKA funktionen som kommer till Office 365 2019. Jag försökte flera idéer och ingen skulle fungera. Så jag gick till befälhavaren för Dynamic Arrays, Joe McDaid, för att få hjälp. Svaret är ganska coolt och jag är säker på att jag kommer att glömma det, så jag dokumenterar det för dig och för mig. Jag är säker på att jag om två år kommer att Google hur man gör detta och inser "Åh, titta! Det är jag som skrev artikeln om detta!"

Innan du går till UNIK funktion, ta en titt på vad jag försöker göra. Jag vill ha alla unika kombinationer av säljare från kolumn B och produkt från kolumn C. Normalt skulle jag följa dessa steg:

Kopiera rubrikerna från B1 och D1 till en tom del av kalkylbladet
Från B1 väljer du Data, Filter, Advanced
I dialogrutan Avancerat filter väljer du Kopiera till en ny plats
Ange rubrikerna från steg 1 som utdataområdet
Välj rutan Endast unika värden
Klicka på OK

Kopiera de två rubrikerna till ett tomt avsnitt som blir utmatningsområdet

Resultatet är varje unik kombination av de två fälten. Observera att Advanced Filter inte sorterar objekten - de visas i originalsekvensen.

Att få en unik lista är en av mina favoritanvändningar för Advanced Filter

Denna process blev enklare i Excel 2010 tack vare kommandot Ta bort dubbletter på fliken Data i menyfliksområdet. Följ dessa steg:

Välj B1: D227 och Ctrl + C för att kopiera
Klistra in i en tom del av kalkylbladet.

Gör en kopia av data eftersom Ta bort dubbletter är destruktivt
Välj Data, ta bort dubbletter
Avmarkera datum i dialogrutan Ta bort dubbletter. Detta säger till Excel att endast titta på rep och produkt.
Klicka på OK

Berätta för Ta bort dubbletter för att bara överväga rep och datum

Resultaten är nästan perfekta - du måste bara ta bort kolumnen Datum.

Ta bort den extra kolumnen

Frågan: Finns det något sätt att få UNIQUE-funktionen att se på endast kolumner B & D? (Om du inte har sett den nya UNIQUE-funktionen än, läs: UNIQUE-funktionen i Excel.)

Att be om =UNIQUE(B2:D227)skulle ge dig alla unika kombinationer av rep, datum och produkt som inte är vad vi letar efter.

Hur kan vi skicka två icke-intilliggande kolumner till UNIQUE-funktionen?

När Dynamic Arrays introducerades i september sa jag att vi aldrig mer skulle behöva oroa oss för komplexiteten i Ctrl + Shift + Enter. Men för att lösa detta problem kommer du att använda ett koncept som heter Lifting. Förhoppningsvis har du nu laddat ner min Excel Dynamic Arrays Straight To The Point e-bok. Gå till sidorna 31-33 för en fullständig förklaring av lyft.

Se min bok för en fullständig förklaring av lyft (och senare, när du går för att sortera resultaten, parvis lyft)

Ta en Excel-funktion som förväntar ett enda värde. Till exempel =CHOOSE(Z1,"Apple","Banana")skulle returnera antingen Apple eller Banana beroende på om Z1 innehåller 1 (för Apple) eller 2 (för Banana). VÄLJ-funktionen förväntar sig en skalär som det första argumentet.

Men istället kommer du att skicka en arraykonstant på (1,2) som det första argumentet för att VÄLJ. Excel utför lyftoperationen och beräknar VÄLJ två gånger. För värdet 1 vill du att säljare i B2: B227. För värdet 2 vill du ha produkterna i D2: D227.

Be CHOOSE att returnera två svar

Normalt, i gamla Excel, skulle implicit korsning ha skruvat upp resultaten. Men nu när Excel kan spilla resultat till många celler returnerar formeln ovan framgångsrikt en matris med alla svar i B och D:

Framgång! Härifrån går det nerför

Jag känner att jag skulle förolämpa din intelligens att skriva resten av artikeln, för härifrån är det superenkelt.

Slå in formeln från föregående skärmdump i UNIK och du får bara de unika kombinationerna av försäljningsrepresentant och produkt med =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Fortfarande inte sorterad

För att kontrollera din förståelse, försök att ändra formeln ovan för att returnera alla unika kombinationer av tre kolumner: Försäljningsrepresentant, produkt, färg.

Ändra först arraykonstanten för att referera till (1,2,3).

Lägg sedan till ett fjärde argument att välja att återvända färg från E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Returnera den unika kombinationen av tre kolumner

Det skulle vara trevligt att sortera dessa resultat, så vi vänder oss till Sortera med en formel med SORT och SORTBY.

Normalt skulle funktionen att sortera efter den första kolumnen stigande vara =SORT(Array)eller =SORT(Array,1,1).

För att sortera efter tre kolumner måste du lyfta parvis med =SORT(Array,(1,2,3),(1,1,1)). I den här formeln, när du kommer till det andra argumentet i SORT, vill Excel veta efter vilken kolumn som ska sorteras. Istället för ett enda värde, skicka tre kolumner inuti en arraykonstant: (1,2,3). När du kommer till det tredje argumentet där du anger 1 för Ascending eller -1 för Descending, skicka en arraykonstant med tre 1's för att indikera Ascending, Ascending, Ascending. Följande skärmdump visar =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Mer information om lyftning i parvisa riktning finns på sidan 34 i Excel Dynamic Arrays Straight to the Point.

Åtminstone till slutet av 2018 kan du ladda ner Excel Dynamic Arrays-boken gratis med länken längst ner på denna sida.

Jag uppmuntras att finna att svaret på dagens fråga är lite komplicerat. När Dynamic Arrays kom ut, tänkte jag omedelbart på alla fantastiska formler som publicerades på anslagstavlan av Aladin Akyurek och andra och hur dessa formler skulle bli mycket enklare i nya Excel. Men dagens exempel visar att det fortfarande kommer att finnas ett behov av formelgenier för att skapa nya sätt att använda Dynamic Arrays.