JEDINEČNÉ z nesusedných stĺpcov - tipy pre Excel

Na druhý deň som sa chystal vytvoriť jedinečnú kombináciu dvoch nesusediacich stĺpcov v programe Excel. Spravidla to robím pomocou nástroja Remove Duplicates alebo pomocou Advanced Filter, ale myslel som si, že to skúsim s novou UNIQUE funkciou, ktorá príde na Office 365 v roku 2019. Vyskúšal som niekoľko nápadov a žiadny by nefungoval. Išiel som teda k pomoci k majstrovi dynamických polí Joeovi McDaidovi. Odpoveď je celkom v pohode a som si istý, že na ňu zabudnem, takže ju dokumentujem pre vás a pre mňa. Som si istý, že o dva roky budem googliť, ako to urobiť, a uvedomím si: „Och, pozri sa! Som ten, kto o tom napísal článok!“

Predtým, ako sa dostanete k funkcii UNIQUE, pozrite sa, čo sa snažím robiť. Chcem každú jedinečnú kombináciu obchodného zástupcu zo stĺpca B a produktu zo stĺpca C. Normálne by som postupoval podľa týchto krokov:

Skopírujte nadpisy z B1 a D1 do prázdnej časti listu
Z B1 vyberte Údaje, Filter, Pokročilé
V dialógovom okne Rozšírený filter vyberte možnosť Kopírovať do nového umiestnenia
Zadajte nadpisy z kroku 1 ako výstupný rozsah
Začiarknite políčko Iba pre jedinečné hodnoty
Kliknite na tlačidlo OK

Skopírujte dve nadpisy do prázdnej časti, ktorá sa stane výstupným rozsahom

Výsledkom je každá jedinečná kombinácia týchto dvoch polí. Upozorňujeme, že rozšírený filter položky netriedi - zobrazujú sa v pôvodnom poradí.

Získanie jedinečného zoznamu je jedným z mojich najobľúbenejších spôsobov použitia rozšíreného filtra

Tento proces bol v programe Excel 2010 jednoduchší vďaka príkazu Odstrániť duplikáty na karte Údaje na páse s nástrojmi. Nasleduj tieto kroky:

Vyberte B1: D227 a Ctrl + C na kopírovanie
Prilepte do prázdnej časti listu.

Vytvorte kópiu údajov, pretože odstránenie duplikátov je deštruktívne
Vyberte údaje, odstrániť duplikáty
V dialógovom okne Odstrániť duplikáty zrušte výber dátumu. To znamená, že program Excel sa musí pozerať iba na zástupcov a produkty.
Kliknite na tlačidlo OK

Ak chcete brať do úvahy iba dátum a dátum, povedzte príkaz Odstrániť duplikáty

Výsledky sú takmer dokonalé - stačí odstrániť stĺpec Dátum.

Odstráňte nadbytočný stĺpec

Otázka: Existuje nejaký spôsob, ako funkcia UNIQUE pozerať iba na stĺpce B & D? (Ak ste novú UNIQUE funkciu ešte nevideli, prečítajte si: UNIQUE funkcia v Exceli.)

Ak o to požiadate, =UNIQUE(B2:D227)získate každú jedinečnú kombináciu zástupcov, dátumov a produktov, ktoré nie sú to, čo hľadáme.

Ako môžeme odovzdať dva nesusediace stĺpce funkcii UNIQUE?

Keď sme v septembri predstavili dynamické polia, povedal som si, že si už nikdy nebudeme musieť robiť starosti so zložitosťou vzorcov Ctrl + Shift + Enter. Ale na vyriešenie tohto problému použijete koncept s názvom Lifting. Dúfajme, že ste si už stiahli moju elektronickú knihu Excel Dynamic Arrays Straight To The Point. Úplné vysvetlenie zdvíhania nájdete na stranách 31–33.

V mojej knihe nájdete úplné vysvetlenie funkcie Zdvíhanie (a neskôr, keď chcete výsledky zoradiť, Pairwise Lifting)

Využite funkciu programu Excel, ktorá očakáva jednu hodnotu. Napríklad =CHOOSE(Z1,"Apple","Banana")vráti Apple alebo Banana v závislosti od toho, či Z1 obsahuje 1 (pre Apple) alebo 2 (pre Banana). Funkcia CHOOSE očakáva ako prvý argument skalár.

Namiesto toho ale odovzdáte konštantu poľa (1,2) ako prvý argument CHOOSE. Excel vykoná operáciu Zdvíhanie a dvakrát vypočíta VYBER. Za hodnotu 1 chcete, aby boli obchodní zástupcovia v B2: B227. Za hodnotu 2 chcete produkty v D2: D227.

Povedzte CHOOSE, aby vrátil dve odpovede

Za normálnych okolností by v starom Exceli implicitná križovatka skreslila výsledky. Ale teraz, keď môže Excel rozliať výsledky do mnohých buniek, vyššie uvedený vzorec úspešne vracia pole všetkých odpovedí v B a D:

Úspech! Je to odtiaľto všetko z kopca

Mám pocit, že by som urazil vašu inteligenciu, aby ste napísali zvyšok článku, pretože odtiaľto je to veľmi jednoduché.

Zbalte vzorec z predchádzajúcej snímky obrazovky do JEDINEČNÉHO režimu a získate jedinečnú kombináciu použitia nástroja Sales Rep a Product =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Stále nie je zoradené

Ak chcete pochopiť svoje porozumenie, skúste zmeniť vyššie uvedený vzorec a vráťte všetky jedinečné kombinácie troch stĺpcov: Obchodný zástupca, Produkt, Farba.

Najskôr zmeňte konštantu poľa tak, aby odkazovala na (1,2,3).

Potom pridajte štvrtý argument voľbu vrátiť farbu z E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Vráťte jedinečnú kombináciu troch stĺpcov

Bolo by pekné tieto výsledky zoradiť, preto sa obrátime na triedenie podľa vzorca pomocou SORT a SORTBY.

Za normálnych okolností by funkcia zoradenia podľa prvého stĺpca vzostupne bola =SORT(Array)alebo =SORT(Array,1,1).

Ak chcete triediť podľa troch stĺpcov, musíte to urobiť párovým zdvíhaním pomocou =SORT(Array,(1,2,3),(1,1,1)). Keď sa v tomto vzorci dostanete k druhému argumentu SORT, Excel chce vedieť, podľa ktorého stĺpca sa má zoradiť. Namiesto jednej hodnoty pošlite tri stĺpce do konštanty poľa: (1,2,3). Keď sa dostanete k tretiemu argumentu, kde zadajte 1 pre vzostupný alebo -1 pre zostupný, odošlite konštantu poľa s tromi číslicami 1 na označenie vzostupného, vzostupného, vzostupného. Nasledujúca snímka obrazovky ukazuje =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Ďalšie informácie o párovom zdvíhaní nájdete na strane 34 v dynamických poliach Excelu priamo k bodu.

Minimálne do konca roka 2018 si môžete zadarmo stiahnuť knihu Excel Dynamic Arrays pomocou odkazu v dolnej časti tejto stránky.

S povzbudením zisťujem, že odpoveď na dnešnú otázku je trochu komplikovaná. Keď vyšli dynamické polia, okamžite som premýšľal o všetkých úžasných vzorcoch zverejnených na výveske Aladinom Akyurekom a ďalšími a o tom, ako by sa tieto vzorce v novom programe Excel výrazne zjednodušili. Dnešný príklad ale ukazuje, že stále budú potrební formálni géniovia, aby vytvorili nové spôsoby použitia dynamických polí.