UNIKAALNE külgnevatest veergudest - Exceli näpunäited

Teisel päeval olin just looma Excelis kahest külgnevast veerust ainulaadse kombinatsiooni. Ma teen seda tavaliselt duplikaatide eemaldamise või täiustatud filtriga, kuid mõtlesin, et prooviksin seda teha uue funktsiooniga UNIQUE, mis 2019. aastal Office 365-sse jõuab. Proovisin mitut ideed ja ükski ei tööta. Niisiis, läksin abi saamiseks dünaamiliste massiivide kapteni Joe McDaidi juurde. Vastus on päris lahe ja olen kindel, et unustan selle ära, seega dokumenteerin selle teile ja minu jaoks. Olen kindel, et kahe aasta pärast teen Google, kuidas seda teha ja saan aru: "Oh, vaata! Ma olen see, kes selle artikli kirjutas!"

Enne UNIQUE funktsiooni jõudmist vaadake, mida ma proovin teha. Soovin veerust B kõiki unikaalseid müügiesindaja ja veeru C tooteid. Tavaliselt järgiksin neid samme:

Kopeerige päised punktidest B1 ja D1 töölehe tühja sektsiooni
Valige B1-st Data, Filter, Advanced
Dialoogis Täpsem filter valige Kopeeri uude asukohta
Määrake väljundivahemikuks 1. sammu pealkirjad
Valige ruut Ainult unikaalsete väärtuste jaoks
Klõpsake nuppu OK

Kopeerige kaks pealkirja tühja sektsiooni, millest saab väljundivahemik

Tulemuseks on iga kahe välja ainulaadne kombinatsioon. Pange tähele, et täpsem filter ei sorteeri üksusi - need kuvatakse algses järjestuses.

Unikaalse loendi saamine on üks minu lemmikkasutusi Advanced Filter jaoks

See protsess muutus Excelis 2010 lihtsamaks tänu lindi vahekaardi Andmed käsule Eemalda duplikaadid. Järgige neid samme:

Valige kopeerimiseks B1: D227 ja Ctrl + C
Kleebi töölehe tühjale osale.

Andmetest koopia tegemine, kuna duplikaatide eemaldamine on hävitav
Valige Andmed, eemaldage duplikaadid
Tühjendage dialoogiboksis duplikaatide eemaldamine kuupäev. See käsib Excelil vaadata ainult Repi ja Productit.
Klõpsake nuppu OK

Öelge, et eemaldage duplikaadid, et arvestada ainult Repi ja Date'iga

Tulemused on peaaegu täiuslikud - peate lihtsalt veeru Kuupäev kustutama.

Kustutage lisaveerg

Küsimus: kas on võimalik kuidagi lasta funktsioonil UNIKAALNE vaadata ainult veerge B ja D? (Kui te pole uut UNIKAALSET funktsiooni veel näinud, lugege Exceli funktsiooni UNIKAALNE.)

Küsides =UNIQUE(B2:D227)saaksite kõik Repi, Kuupäeva ja Toote ainulaadsed kombinatsioonid, mida me ei otsi.

Kuidas saaksime funktsiooni UNIQUE edastada kaks külgnevat veergu?

Kui septembris võeti kasutusele dünaamilised massiivid, ütlesin, et me ei pea enam kunagi valemite Ctrl + Shift + Enter keerukuse pärast muretsema. Kuid selle probleemi lahendamiseks kasutate kontseptsiooni, mille nimi on Tõstmine. Loodetavasti olete praeguseks alla laadinud minu Exceli dünaamilised massiivid otse punkti punkti e-raamatu. Tõstmise täieliku selgituse leiate lehekülgedelt 31-33.

Tõstmise täieliku selgituse leiate minu raamatust (ja hiljem, kui lähete tulemusi sorteerima, siis paarikaupa tõstmine)

Võtke Exceli funktsioon, mis ootab ühte väärtust. Näiteks =CHOOSE(Z1,"Apple","Banana")tagastaks kas Apple või Banana olenevalt sellest, kas Z1 sisaldab 1 (Apple'i jaoks) või 2 (Banana jaoks). Funktsioon CHOOSE ootab esimese argumendina skalaari.

Selle asemel edastate massiivikonstandi (1,2) esimese argumendina VALI. Excel teostab tõstmise ja arvutab VALI kaks korda. 1 väärtuse jaoks soovite müügiesindajaid B2: B227-s. 2 väärtuse jaoks soovite D2: D227 tooteid.

Öelge, et CHOOSE tagastaks kaks vastust

Tavaliselt oleks vanas Excelis implitsiitne ristmik tulemusi segamini ajanud. Kuid nüüd, kui Excel suudab tulemusi levitada paljudele lahtritele, tagastab ülaltoodud valem edukalt massiivi kõikidest vastustest punktides B ja D:

Edu! Siit on kõik allamäge

Mulle tundub, et solvaksin teie arukust ülejäänud artikli kirjutamiseks, sest siit on see ülilihtne.

Mähkige eelmise ekraanipildi valem unikaalseks ja saate ainult müügiesindaja ja toote ainulaadsed kombinatsioonid =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Ikka pole sorteeritud

Mõistmise kontrollimiseks proovige ülaltoodud valemit muuta, et tagastada kolme veeru kõik unikaalsed kombinatsioonid: müügiesindaja, toode, värv.

Kõigepealt muutke massiivi konstanti, et viidata (1,2,3) -le.

Seejärel lisage neljas argument VALIDA tagasi värvi E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Tagastab kolme veeru unikaalse kombinatsiooni

Tore oleks neid tulemusi sorteerida, seega pöördume valemi Sordi poole, kasutades valikuid SORT ja SORTBY.

Tavaliselt oleks esimese veeru tõusva sortimise funktsioon funktsioon =SORT(Array)või =SORT(Array,1,1).

Kolme veeru järgi sortimiseks peate paariga tõstma =SORT(Array,(1,2,3),(1,1,1)). Selles valemis soovib Excel jõuda SORT teise argumenti juurde, millise veeru järgi sortida. Saada ühe väärtuse asemel kolm veergu massiivikonstandi sisse: (1,2,3). Kui jõuate kolmanda argumendini, kus määrake kasvavaks 1 või kahanevaks -1, saatke kolme 1-ga massiivikonstand, tähistamaks kasvavat, kasvavat ja kasvavat. Järgmine ekraanipilt näitab =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Paarikaupa tõstmise kohta leiate lisateavet Exceli dünaamiliste massiivide sirgelt punktini lk 34.

Vähemalt 2018. aasta lõpuni saate Exceli dünaamiliste massiivide raamatu tasuta alla laadida, kasutades selle lehe allosas olevat linki.

Mul on soovitatav leida, et vastus tänasele küsimusele on veidi keeruline. Kui dünaamilised massiivid välja tulid, mõtlesin koheselt kõigile hämmastavatele valemitele, mille Aladin Akyurek ja teised postitasid teadetetahvlile ning kuidas need valemid uues Excelis palju lihtsamaks muutuvad. Kuid tänane näide näitab, et endiselt on vaja valemigeeniusi, et luua uusi viise dünaamiliste massiivide kasutamiseks.