UNIKĀLI no blakus esošām kolonnām - Excel padomi

Kādu dienu es gatavojos izveidot unikālu divu blakus esošu kolonnu kombināciju programmā Excel. Es to parasti daru, izmantojot Noņemt dublikātus vai Ar papildu filtru, bet es domāju, ka mēģināšu to izdarīt ar jauno UNIKĀLO funkciju, kas 2019. gadā nonāks Office 365. Es izmēģināju vairākas idejas, un neviena nedarbosies. Tātad, es devos pie Dynamic Array meistara Džo Makdeida pēc palīdzības. Atbilde ir diezgan forša, un es esmu pārliecināta, ka to aizmiršu, tāpēc es to dokumentēju jums un man. Esmu pārliecināts, ka pēc diviem gadiem es Google mēģināšu to izdarīt un sapratīšu: "Ak, skaties! Es esmu tas, kurš par šo rakstu uzrakstīja!"

Pirms nokļūšanas funkcija UNIKĀLĀ, ieskatieties, ko es cenšos darīt. Es vēlos katru unikālo pārdošanas pārstāvja kombināciju no B slejas un produktu no kolonnas C. Parasti es rīkojos šādi:

Kopējiet virsrakstus no B1 un D1 tukšā darblapas sadaļā
No B1 izvēlieties Dati, Filtrs, Papildu
Dialoglodziņā Papildu filtrs izvēlieties Kopēt uz jaunu vietu
Kā izvades diapazonu norādiet virsrakstus no 1. darbības
Atzīmējiet izvēles rūtiņu Tikai unikālās vērtības
Noklikšķiniet uz Labi

Kopējiet abus virsrakstus tukšā sadaļā, kas kļūst par izvades diapazonu

Rezultāts ir katra unikālā abu lauku kombinācija. Ņemiet vērā, ka papildu filtrā elementi netiek kārtoti - tie tiek parādīti sākotnējā secībā.

Unikāla saraksta iegūšana ir viens no maniem iecienītākajiem uzlabotā filtra izmantošanas veidiem

Šis process programmā Excel 2010 kļuva vienkāršāks, pateicoties lentes cilnes Dati komandai Noņemt dublikātus. Veiciet šīs darbības:

Atlasiet B1: D227 un Ctrl + C, lai kopētu
Ielīmējiet to tukšā darblapas sadaļā.

Izveidojiet datu kopiju, jo dublikātu noņemšana ir postoša
Izvēlieties Dati, Noņemt dublikātus
Dialoglodziņā Noņemt dublikātus noņemiet datuma atlasi. Tas liek programmai Excel apskatīt tikai Rep un Product.
Noklikšķiniet uz Labi

Sakiet Noņemt dublikātus, lai ņemtu vērā tikai Rep un Date

Rezultāti ir gandrīz ideāli - jums vienkārši jāizdzēš kolonna Datums.

Izdzēsiet papildu kolonnu

Jautājums: Vai ir kaut kāds veids, kā funkcija UNIKĀLĀ apskatīt tikai B un D slejas? (Ja vēl neesat redzējis jauno UNIQUE funkciju, lasiet: UNIQUE funkcija programmā Excel.)

Pieprasot, =UNIQUE(B2:D227)jūs iegūsit katru unikālo Rep, Date un Product kombināciju, kas nav tas, ko mēs meklējam.

Kā mēs varam nodot divas blakus esošas kolonnas funkcija UNIKĀLĀ?

Kad septembrī tika ieviesti dinamiskie masīvi, es teicu, ka mums nekad vairs nebūs jāuztraucas par Ctrl + Shift + Enter formulu sarežģītību. Bet, lai atrisinātu šo problēmu, jūs izmantojat jēdzienu, ko sauc par Celšana. Cerams, ka līdz šim esat lejupielādējis manu e-grāmatu Excel Dynamic Arrays Straight To The Point. Lai iegūtu pilnīgu skaidrojumu par pacelšanu, skatiet 31. – 33. Lpp.

Pilnīgu paskaidrojumu par pacelšanu skatiet manā grāmatā (un vēlāk, kad kārtojat rezultātus, Pāri pa ceļam)

Veikt Excel funkciju, kas sagaida vienu vērtību. Piemēram, =CHOOSE(Z1,"Apple","Banana")atgriezīs vai nu Apple, vai Banānu atkarībā no tā, vai Z1 satur 1 (Apple) vai 2 (Banana). Funkcija CHOOSE kā pirmo argumentu sagaida skalāru.

Bet tā vietā jūs nodosiet masīva konstanti (1,2) kā pirmo argumentu IZVĒLĒTIES. Excel veiks celšanas darbību un divas reizes aprēķinās IZVĒLES. Par vērtību 1 vēlaties, lai pārdošanas pārstāvji būtu B2: B227. Par vērtību 2 jūs vēlaties produktus D2: D227.

Sakiet IZVĒLES, lai atgrieztos divas atbildes

Parasti vecajā Excel netiešais krustojums būtu ieskrūvējis rezultātus. Bet tagad, kad Excel var izlaist rezultātus daudzām šūnām, iepriekš minētā formula veiksmīgi atgriež visu atbilžu masīvu B un D:

Panākumi! No šejienes tas viss ir lejup

Man šķiet, ka es apvainotu jūsu inteliģenci, lai uzrakstītu pārējo rakstu, jo no šejienes tas ir ļoti vienkārši.

Aptiniet iepriekšējā ekrānuzņēmuma formulu UNIKĀLĀ un jūs saņemsiet tikai unikālās pārdošanas pārstāvja un produkta kombinācijas =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Joprojām nav šķirots

Lai pārbaudītu izpratni, mēģiniet mainīt iepriekš minēto formulu, lai atgrieztu visas unikālās trīs kolonnu kombinācijas: tirdzniecības pārstāvis, produkts, krāsa.

Vispirms mainiet masīva konstanti, lai atsauktos uz (1,2,3).

Tad pievieno ceturto argumentu izvēlēties atgriezties krāsu no E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Atgriež unikālo trīs kolonnu kombināciju

Būtu jauki kārtot šos rezultātus, tāpēc mēs vēršamies pie Kārtot ar formulu, izmantojot SORT un SORTBY.

Parasti funkcija, lai kārtotu pēc pirmās kolonnas augošā secībā, būtu =SORT(Array)vai =SORT(Array,1,1).

Lai kārtotu pēc trim kolonnām, jums ir jāpaceļ pāris pa pāriem ar =SORT(Array,(1,2,3),(1,1,1)). Šajā formulā, nonākot pie SORT otrā argumenta, Excel vēlas uzzināt, pēc kuras kolonnas kārtot. Vienas vērtības vietā masīva konstanta iekšpusē nosūtiet trīs kolonnas: (1,2,3). Kad esat nonācis pie trešā argumenta, kur norādiet Ascending 1 vai -1 - Descending, nosūtiet masīva konstanti ar trim 1, lai norādītu Ascending, Ascending, Ascending. Šis ekrānuzņēmums parāda =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Lai uzzinātu vairāk par celšanu pāri, skatiet Excel dinamisko masīvu 34. lappusi tieši uz punktu.

Vismaz līdz 2018. gada beigām jūs varat bez maksas lejupielādēt Excel dinamisko masīvu grāmatu, izmantojot saiti šīs lapas apakšdaļā.

Esmu aicināts atrast, ka atbilde uz šodienas jautājumu ir nedaudz sarežģīta. Kad iznāca dinamiskie masīvi, es uzreiz iedomājos visas pārsteidzošās formulas, kuras ziņojumu panelī ievietoja Aladins Akjureks un citi, un kā šīs formulas jaunajā Excel kļūs daudz vienkāršākas. Bet šodienas piemērs parāda, ka joprojām būs nepieciešami formulu ģēniji, lai izstrādātu jaunus veidus, kā izmantot dinamiskās masīvas.