UNIKALA iš gretimų stulpelių - „Excel“ patarimai

Kitą dieną ketinau sukurti unikalų dviejų gretimų „Excel“ stulpelių derinį. Dažniausiai tai darau naudodamas „Pašalinti dublikatus“ arba naudodamasis išplėstiniu filtru, tačiau maniau, kad pabandysiu tai padaryti naudodamas naują UNIKALIĄ funkciją, ateinančią į „Office 365“ 2019 m. Išbandžiau keletą idėjų ir nė viena neveiks. Taigi, aš nuėjau pas „Dynamic Arrays“ meistrą Joe McDaidą pagalbos. Atsakymas yra gana šaunus ir esu tikras, kad jį pamiršiu, todėl dokumentuoju tai jums ir man. Esu tikras, kad po dvejų metų aš „Google“ kaip tai padaryti ir suprasiu „O, žiūrėk! Aš esu tas, kuris parašė straipsnį apie tai!“

Prieš pradėdami naudotis UNIKALI funkcija, pažvelkite į tai, ką bandau padaryti. Noriu kiekvieno unikalaus pardavimo atstovo iš B stulpelio ir produkto iš C stulpelio derinio. Paprastai aš atlikčiau šiuos veiksmus:

Nukopijuokite antraštes iš B1 ir D1 į tuščią darbalapio dalį
Iš B1 pasirinkite Duomenys, Filtras, Išplėstiniai
Dialogo lange Išplėstinis filtras pasirinkite Kopijuoti į naują vietą
Nurodykite 1 žingsnio antraštes kaip Išvesties diapazoną
Pažymėkite laukelį Tik unikalios vertės
Spustelėkite Gerai

Nukopijuokite dvi antraštes į tuščią sekciją, kuri tampa išvesties diapazonu

Rezultatas yra kiekvienas unikalus dviejų laukų derinys. Atminkite, kad išplėstinis filtras nerūšiuoja elementų - jie rodomi originalia seka.

Unikalaus sąrašo gavimas yra vienas iš mano mėgstamiausių „Advanced Filter“ naudojimo būdų

Šis procesas tapo lengvesnis programoje „Excel 2010“ dėl juostos skirtuke Duomenys esančios komandos Pašalinti kopijas. Atlikite šiuos veiksmus:

Norėdami kopijuoti, pasirinkite B1: D227 ir „Ctrl“ + C
Įklijuokite į tuščią darbalapio skiltį.

Padarykite duomenų kopiją, nes Pašalinti dublikatus yra žalingas
Pasirinkite Duomenys, Pašalinti dublikatus
Dialogo lange Pašalinti dublikatus panaikinkite datą. Tai nurodo „Excel“ žiūrėti tik į „Rep and Product“.
Spustelėkite Gerai

Nurodykite šalinti dublikatus atsižvelgti tik į Rep ir Date

Rezultatai yra beveik tobuli - jūs tiesiog turite ištrinti stulpelį Data.

Ištrinkite papildomą stulpelį

Klausimas: Ar yra koks nors būdas, kad UNIKALI funkcija žiūrėtų tik į B ir D stulpelius? (Jei dar nematėte naujos UNIQUE funkcijos, skaitykite: UNIQUE funkcija „Excel“.)

Prašydami =UNIQUE(B2:D227)gausite kiekvieną unikalų „Rep“, „Date“ ir „Product“ derinį, kurio mes ne ieškome.

Kaip mes galime perduoti du gretimus stulpelius funkcija UNIQUE?

Kai rugsėjį buvo pristatytos dinaminės masyvos, sakiau, kad mums niekada nereikės jaudintis dėl „Ctrl“ + „Shift“ + „Enter“ formulių sudėtingumo. Bet norėdami išspręsti šią problemą, jūs ketinate naudoti koncepciją, vadinamą „Kėlimas“. Tikimės, kad jau esate atsisiuntę mano „Excel Dynamic Arrays Straight To The Point“ el. Knygą. Norėdami gauti išsamų „Pakėlimo“ paaiškinimą, skaitykite 31–33 puslapiuose.

Peržiūrėkite mano knygą, kur rasite išsamų kėlimo paaiškinimą (ir vėliau, kai eisite rūšiuoti rezultatus, „Pairwise Lifting“)

Paimkite „Excel“ funkciją, kuri tikisi vienos vertės. Pavyzdžiui, =CHOOSE(Z1,"Apple","Banana")grąžintų „Apple“ arba „Banana“, atsižvelgiant į tai, ar Z1 yra 1 („Apple“), ar 2 („Banana“). Funkcija CHOOSE kaip pirmąjį argumentą tikisi skaliaro.

Tačiau vietoj to jūs pateiksite (1,2) masyvo konstantą kaip pirmąjį argumentą PASIRINKTI. „Excel“ atliks kėlimo operaciją ir du kartus apskaičiuos PASIRINKTI. Norėdami gauti 1 vertę, norite, kad pardavimo atstovai būtų B2: B227. Norint gauti 2 vertę, reikia produktų, esančių D2: D227.

Liepkite PASIRINKTI grąžinti du atsakymus

Paprastai senojoje „Excel“ sistemoje implicitinė sankirta būtų sugadinusi rezultatus. Bet dabar, kai „Excel“ gali išpilti rezultatus į daugelį langelių, aukščiau pateikta formulė sėkmingai pateikia visų atsakymų masyvą B ir D:

Sėkmė! Iš čia viskas yra nuokalnė

Manau, kad įžeisčiau jūsų intelektą parašyti likusią straipsnio dalį, nes iš čia tai yra labai paprasta.

Apvyniokite formulę iš ankstesnio ekrano paveikslėlio UNIQUE ir gausite tik unikalius „Sales Rep“ ir „Product“ derinius =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Vis dar nerūšiuota

Norėdami patikrinti savo supratimą, pabandykite pakeisti aukščiau pateiktą formulę, kad būtų pateikti visi unikalūs trijų stulpelių deriniai: pardavimo atstovas, produktas, spalva.

Pirmiausia pakeiskite masyvo konstantą, nurodydami (1,2,3).

Tada pridėti ketvirtą argumentą rinktis grįžti spalvą iš E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Grąžinkite unikalų trijų stulpelių derinį

Būtų malonu rūšiuoti tuos rezultatus, todėl mes einame į Rūšiuoti pagal formulę naudodami Rūšiuoti ir Rūšiuoti.

Paprastai funkcija rūšiuoti pagal didėjantį pirmąjį stulpelį būtų =SORT(Array)arba =SORT(Array,1,1).

Norint rūšiuoti pagal tris stulpelius, reikia kelti poromis =SORT(Array,(1,2,3),(1,1,1)). Šioje formulėje, kai pateksite į antrą SORT rūšies argumentą, „Excel“ nori sužinoti, pagal kurį stulpelį rūšiuoti. Vietoj vienos vertės masyvo konstantos viduje siųskite tris stulpelius: (1,2,3). Kai pateksite į trečiąjį argumentą, kuriame nurodykite 1 kaip Ascending arba -1 - Descending, nusiųskite masyvo konstantą su trimis 1, kad nurodytumėte Ascending, Ascending, Ascending. Ši ekrano kopija rodo =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Daugiau apie porinį kėlimą ieškokite „Excel“ dinaminių masyvų tiesiai į tašką 34 puslapyje.

Bent iki 2018 m. Pabaigos galite nemokamai atsisiųsti „Excel Dynamic Arrays“ knygą naudodami nuorodą šio puslapio apačioje.

Esu raginama įsitikinti, kad atsakymas į šiandienos klausimą yra šiek tiek sudėtingas. Kai pasirodė „Dinaminiai masyvai“, iškart pagalvojau apie visas nuostabias formules, kurias pranešimų lentoje paskelbė Aladinas Akyurekas ir kiti, ir kaip šios formulės taps kur kas paprastesnės naujojoje „Excel“. Tačiau šiandienos pavyzdys rodo, kad vis tiek reikės formulės genijų, kurie galėtų sukurti naujus dinaminių masyvų naudojimo būdus.