JEDINEČNÉ od nesousedících sloupců - Excel tipy

Druhý den jsem se chystal vytvořit jedinečnou kombinaci dvou nesousedících sloupců v aplikaci Excel. Obvykle to dělám pomocí Remove Duplicates nebo Advanced Filter, ale myslel jsem si, že to zkusím s novou UNIQUE funkcí přicházející do Office 365 v roce 2019. Zkusil jsem několik nápadů a žádný by nefungoval. Šel jsem tedy k pomoci k mistrovi dynamických polí Joeovi McDaidovi. Odpověď je docela v pohodě a jsem si jistý, že na ni zapomenu, takže ji dokumentuji pro vás i pro mě. Jsem si jistý, že za dva roky budu vyhledávat Google, jak to udělat, a uvědomím si: „Podívej! Jsem ten, kdo o tom napsal článek!“

Než se dostanete k funkci UNIQUE, podívejte se, co se snažím dělat. Chci každou jedinečnou kombinaci prodejního zástupce ze sloupce B a produktu ze sloupce C. Normálně bych postupoval podle těchto kroků:

Zkopírujte záhlaví z B1 a D1 do prázdné části listu
Z B1 vyberte Data, Filtr, Pokročilé
V dialogovém okně Rozšířený filtr vyberte možnost Kopírovat do nového umístění
Zadejte záhlaví z kroku 1 jako výstupní rozsah
Zaškrtněte políčko Pouze pro jedinečné hodnoty
Klikněte na OK

Zkopírujte dvě záhlaví do prázdné části, která se stane výstupním rozsahem

Výsledkem je každá jedinečná kombinace těchto dvou polí. Všimněte si, že Rozšířený filtr netřídí položky - objevují se v původním pořadí.

Získání jedinečného seznamu je jedním z mých oblíbených způsobů použití rozšířeného filtru

Tento proces se v aplikaci Excel 2010 usnadnil díky příkazu Odebrat duplikáty na kartě Data na pásu karet. Následuj tyto kroky:

Vyberte B1: D227 a Ctrl + C pro kopírování
Vložte do prázdné části listu.

Vytvořte kopii dat, protože Odstranit duplikáty je destruktivní
Zvolte Data, Odebrat duplikáty
V dialogovém okně Odebrat duplikáty zrušte výběr Datum. To řekne aplikaci Excel, aby se dívala pouze na zástupce a produkt.
Klikněte na OK

Řekněte Odstranit duplikáty, aby se braly v úvahu pouze Rep a datum

Výsledky jsou téměř dokonalé - stačí smazat sloupec Datum.

Odstraňte další sloupec

Otázka: Existuje nějaký způsob, jak funkce UNIQUE sledovat pouze sloupce B & D? (Pokud jste novou UNIQUE funkci ještě neviděli, přečtěte si: UNIQUE funkce v Excelu.)

Pokud =UNIQUE(B2:D227)byste o to požádali, získáte každou jedinečnou kombinaci Rep, Date a Product, což není to, co hledáme.

Jak můžeme předat dva nesousedící sloupce funkci UNIQUE?

Když jsme v září představili Dynamic Arrays, řekl jsem, že se už nikdy nebudeme muset starat o složitost vzorců Ctrl + Shift + Enter. Ale k vyřešení tohoto problému použijete koncept zvaný Lifting. Doufejme, že jste si nyní stáhli moji e-knihu Excel Dynamic Arrays Straight To The Point. Úplné vysvětlení zvedání najdete na stranách 31–33.

Úplné vysvětlení zvedání najdete v mé knize (a později, když přejdete k seřazení výsledků, Pairwise Lifting)

Vezměte funkci aplikace Excel, která očekává jednu hodnotu. Například =CHOOSE(Z1,"Apple","Banana")vrátí Apple nebo Banana v závislosti na tom, zda Z1 obsahuje 1 (pro Apple) nebo 2 (pro Banana). Funkce CHOOSE očekává jako první argument skalár.

Ale místo toho předáte konstantní matici pole (1,2) jako první argument CHOOSE. Excel provede operaci Zvedání a dvakrát vypočítá VÝBĚR. Pro hodnotu 1 chcete obchodní zástupce v B2: B227. Za hodnotu 2 chcete produkty v D2: D227.

Řekněte CHOOSE, abyste vrátili dvě odpovědi

Za normálních okolností by ve starém Excelu implicitní křižovatka zpackala výsledky. Ale teď, když Excel dokáže rozlit výsledky do mnoha buněk, výše uvedený vzorec úspěšně vrací pole všech odpovědí v B a D:

Úspěch! Je to odtud všechno z kopce

Mám pocit, že bych urazil vaši inteligenci, abyste napsali zbytek článku, protože odtud je to super jednoduché.

Zabalte vzorec z předchozího snímku obrazovky do JEDINEČNÉHO a získáte pouze jedinečné kombinace Sales Rep a Product using =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Stále není tříděno

Chcete-li ověřit své porozumění, zkuste změnit výše uvedený vzorec a vrátit všechny jedinečné kombinace tří sloupců: Prodejní zástupce, Produkt, Barva.

Nejprve změňte konstantu pole tak, aby odkazovala na (1,2,3).

Poté přidejte čtvrtý argument volbu vrátit barvu z E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Vraťte jedinečnou kombinaci tří sloupců

Bylo by hezké třídit tyto výsledky, takže se obrátíme na řazení pomocí vzorce pomocí SORT a SORTBY.

Normálně by funkce řazení podle prvního sloupce vzestupně byla =SORT(Array)nebo =SORT(Array,1,1).

Chcete-li seřadit podle tří sloupců, musíte provést párové zvedání pomocí =SORT(Array,(1,2,3),(1,1,1)). Když se v tomto vzorci dostanete k druhému argumentu SORT, Excel chce vědět, podle kterého sloupce seřadit. Místo jedné hodnoty odešlete tři sloupce uvnitř konstanty pole: (1,2,3). Když se dostanete ke třetímu argumentu, kde určete 1 pro vzestupně nebo -1 pro sestupně, odešlete konstantu pole se třemi 1 k označení vzestupně, vzestupně, vzestupně. Následující snímek obrazovky ukazuje =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Další informace o párovém zvedání najdete na stránce 34 v dynamických polích Excel přímo do bodu.

Minimálně do konce roku 2018 si můžete zdarma stáhnout knihu Excel Dynamic Arrays pomocí odkazu ve spodní části této stránky.

S potěšením konstatuji, že odpověď na dnešní otázku je trochu komplikovaná. Když vyšly Dynamic Arrays, okamžitě jsem přemýšlel o všech úžasných vzorcích zveřejněných na nástěnce Aladinem Akyurekem a dalšími a o tom, jak by se tyto vzorce v novém Excelu výrazně zjednodušily. Ale dnešní příklad ukazuje, že stále bude potřeba, aby géniové s formulemi vytvořili nové způsoby použití dynamických polí.