UNIKATNO iz sosednjih stolpcev - nasveti za Excel

Pred dnevi sem hotel ustvariti edinstveno kombinacijo dveh sosednjih stolpcev v Excelu. Običajno to počnem z odstranjevanjem dvojnikov ali z naprednim filtrom, vendar sem mislil, da bom to poskusil z novo funkcijo UNIQUE, ki bo v Office 365 prišla leta 2019. Poskusil sem več idej in nobena ne bi delovala. Torej, šel sem po pomoč k mojstru Dynamic Arrays, Joeju McDaidu. Odgovor je precej kul in prepričan sem, da ga bom pozabil, zato ga dokumentiram za vas in zame. Prepričan sem, da bom čez dve leti Googlal, kako to narediti, in se zavedel: "Oh, glejte! Jaz sem tisti, ki je napisal članek o tem!"

Preden pridete do funkcije UNIQUE, si oglejte, kaj poskušam narediti. Želim vsako unikatno kombinacijo prodajnega predstavnika iz stolpca B in izdelka iz stolpca C. Običajno bi sledil tem korakom:

Kopirajte naslove iz B1 in D1 v prazen del delovnega lista
Med B1 izberite Podatki, Filter, Napredno
V pogovornem oknu Napredni filter izberite Kopiraj na novo lokacijo
Kot obseg izhoda določite naslove iz 1. koraka
Izberite polje Samo za edinstvene vrednosti
Kliknite V redu

Kopirajte dva naslova v prazen odsek, ki postane izhodno območje

Rezultat je vsaka unikatna kombinacija obeh polj. Napredni filter elementov ne razvrsti - pojavijo se v prvotnem zaporedju.

Pridobivanje edinstvenega seznama je ena mojih najljubših načinov uporabe naprednega filtra

Ta postopek je postal lažji v programu Excel 2010 zaradi ukaza Odstrani podvojene na zavihku Podatki na traku. Sledite tem korakom:

Za kopiranje izberite B1: D227 in Ctrl + C
Prilepite v prazen del delovnega lista.

Naredite kopijo podatkov, saj je odstranjevanje dvojnikov uničujoče
Izberite Podatki, Odstrani dvojnike
V pogovornem oknu Odstrani dvojnike počistite polje Datum. To pove Excelu, da si ogleduje samo Rep in Product.
Kliknite V redu

Povejte Odstrani dvojnike, naj upošteva samo repliko in datum

Rezultati so skoraj popolni - samo izbrisati morate stolpec Datum.

Izbrišite dodatni stolpec

Vprašanje: Ali obstaja možnost, da funkcija UNIQUE gleda samo stolpce B & D? (Če nove funkcije UNIQUE še niste videli, preberite: UNIQUE funkcija v Excelu.)

Če =UNIQUE(B2:D227)bi vas prosili, bi dobili vsako unikatno kombinacijo Rep, Datum in Izdelek, ki ni tisto, kar iščemo.

Kako lahko funkciji UNIQUE posredujemo dva sosednja stolpca?

Ko so bili septembra uvedeni Dynamic Arrays, sem rekel, da nam ne bo treba več skrbeti zaradi zapletenosti formul Ctrl + Shift + Enter. Toda za rešitev tega problema boste uporabili koncept, imenovan dviganje. Upajmo, da ste do zdaj že prenesli mojo e-knjigo Excel Dynamic Arrays naravnost do točke. Za popolno razlago dviganja pojdite na strani 31-33.

Za popolno razlago dviganja glejte mojo knjigo (in kasneje, ko boste razvrščali rezultate, dviganje v parih)

Vzemite Excelovo funkcijo, ki pričakuje eno vrednost. =CHOOSE(Z1,"Apple","Banana")Vrnil bi na primer Apple ali Banana, odvisno od tega, ali Z1 vsebuje 1 (za Apple) ali 2 (za Banana). Funkcija CHOOSE pričakuje skalar kot prvi argument.

Namesto tega boste kot prvi argument za CHOOSE predali matrično konstanto (1,2). Excel bo izvedel operacijo dviganja in dvakrat izračunal IZBERI. Za vrednost 1 želite prodajne predstavnike v B2: B227. Za vrednost 2 želite izdelke v D2: D227.

Povejte IZBERI, naj vrne dva odgovora

Običajno bi v starem Excelu implicitno presečišče zakrivilo rezultate. Toda zdaj, ko lahko Excel rezultate razlije v številne celice, zgornja formula uspešno vrne vrsto vseh odgovorov v B in D:

Uspeh! Od tu je vse navzdol

Zdi se mi, da bi žalil vašo inteligenco, če bi napisal preostanek članka, saj je od tu nadvse preprosto.

Formulo s prejšnjega posnetka zaslona zavijte v UNIQUE in dobili boste samo edinstvene kombinacije prodajnega predstavnika in izdelka =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Še vedno ni razvrščeno

Če želite preveriti svoje razumevanje, poskusite spremeniti zgornjo formulo, da vrnete vse unikatne kombinacije treh stolpcev: prodajni zastopnik, izdelek, barva.

Najprej spremenite konstanto matrike, da se sklicuje na (1,2,3).

Nato dodamo četrto trditev, da se odločijo za vrnitev barvo iz E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Vrni edinstveno kombinacijo treh stolpcev

Bilo bi lepo razvrstiti te rezultate, zato se obrnemo na Razvrsti s formulo z uporabo SORT in SORTBY.

Običajno bi bila funkcija za razvrščanje po prvem naraščajočem stolpcu =SORT(Array)oz =SORT(Array,1,1).

Če želite razvrstiti po treh stolpcih, morate nekaj dvigniti v paru =SORT(Array,(1,2,3),(1,1,1)). Ko pride do drugega argumenta SORT, Excel v tej formuli želi vedeti, po katerem stolpcu je treba razvrstiti. Namesto ene same vrednosti v konstanto matrike pošljite tri stolpce: (1,2,3). Ko pridete do tretjega argumenta, kjer določite 1 za naraščajoče ali -1 za padajoče, pošljite konstanto matrike s tremi enotami, ki označuje naraščajoče, naraščajoče in naraščajoče. Naslednja slika zaslona prikazuje =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Za več informacij o dvigovanju v parih glejte stran 34 Excelovih dinamičnih nizov naravnost do točke.

Vsaj do konca leta 2018 lahko knjigo Excel Dynamic Arrays brezplačno prenesete s povezavo na dnu te strani.

Spodbuja me, da je odgovor na današnje vprašanje nekoliko zapleten. Ko so izšli Dynamic Arrays, sem takoj pomislil na vse neverjetne formule, ki jih je na oglasni deski objavil Aladin Akyurek in drugi, in kako bi te formule v novem Excelu postale veliko enostavnejše. Toda današnji primer kaže, da bodo geni formule še vedno potrebovali nove načine uporabe dinamičnih nizov.