УНИКАЛНО От несъседни колони - Съвети на Excel

Онзи ден щях да създам уникална комбинация от две несъседни колони в Excel. Обикновено правя това с Remove Duplicates или с Advanced Filter, но мислех, че ще се опитам да го направя с новата UNIQUE функция, идваща в Office 365 през 2019 г. Опитах няколко идеи и нито една нямаше да работи. И така, отидох при майстора на Dynamic Arrays, Джо Макдейд, за помощ. Отговорът е доста готин и съм сигурен, че ще го забравя, затова го документирам за вас и за мен. Сигурен съм, че след две години ще потърся в Google как да направя това и ще осъзная "О, вижте! Аз съм този, който е написал статията за това!"

Преди да стигнете до функцията UNIQUE, погледнете какво се опитвам да направя. Искам всяка уникална комбинация от търговски представител от колона Б и продукт от колона В. Обикновено следвам следните стъпки:

Копирайте заглавията от B1 и D1 в празен раздел на работния лист
От B1 изберете Data, Filter, Advanced
В диалоговия прозорец Разширен филтър изберете Копиране на ново място
Посочете заглавията от стъпка 1 като обхват на изхода
Изберете квадратчето само за уникални стойности
Щракнете върху OK

Копирайте двете заглавия в празен раздел, който се превръща в изходния диапазон

Резултатът е всяка уникална комбинация от двете полета. Имайте предвид, че разширеният филтър не сортира елементите - те се появяват в оригиналната последователност.

Получаването на уникален списък е едно от любимите ми приложения за разширен филтър

Този процес стана по-лесен в Excel 2010 благодарение на командата Remove Duplicates в раздела Data на лентата. Следвай тези стъпки:

Изберете B1: D227 и Ctrl + C, за да копирате
Поставете в празен раздел на работния лист.

Направете копие на данните, тъй като премахването на дубликати е разрушително
Изберете Данни, Премахване на дубликати
В диалоговия прозорец Премахване на дубликати премахнете отметката от Дата. Това казва на Excel да разглежда само Rep и Product.
Щракнете върху OK

Кажете на Remove Duplicates да вземат предвид само Rep и Date

Резултатите са почти перфектни - просто трябва да изтриете колоната Дата.

Изтрийте допълнителната колона

Въпросът: Има ли някакъв начин функцията UNIQUE да разглежда само колони B & D? (Ако все още не сте виждали новата функция UNIQUE, прочетете: UNIQUE функция в Excel.)

Питам за =UNIQUE(B2:D227)да получавате всяка уникална комбинация от Rep, дата и продукт, който не е това, което ние търсим.

Как можем да предадем две несъседни колони на функцията UNIQUE?

Когато през септември бяха въведени динамични масиви, казах, че никога повече няма да се тревожим за сложността на формулите Ctrl + Shift + Enter. Но за да разрешите този проблем, ще използвате концепция, наречена повдигане. Надяваме се, че вече сте изтеглили електронната ми книга Dynamic Arrays Straight To The Point. Обърнете се на страници 31-33 за пълно обяснение на повдигането.

Вижте моята книга за пълно обяснение на повдигане (и по-късно, когато отидете да сортирате резултатите, двойно повдигане)

Вземете функция на Excel, която очаква една стойност. Например, =CHOOSE(Z1,"Apple","Banana")би върнал или Apple, или Banana в зависимост от това дали Z1 съдържа 1 (за Apple) или 2 (за Banana). Функцията CHOOSE очаква скалар като първи аргумент.

Но вместо това ще предадете константа на масив от (1,2) като първи аргумент на CHOOSE. Excel ще извърши операцията Повдигане и ще изчисли ИЗБЕРЕТЕ два пъти. За стойността 1 искате търговските представители в B2: B227. За стойността 2 искате продуктите в D2: D227.

Кажете на CHOOSE да върнете два отговора

Обикновено в стария Excel неявното пресичане би объркало резултатите. Но сега, когато Excel може да разлее резултатите в много клетки, горната формула успешно връща масив от всички отговори в B и D:

Успех! Всичко е надолу от тук

Чувствам, че бих обидил вашата интелигентност, за да напишете останалата част от статията, защото оттук е супер просто.

Опаковайте формулата от предишната екранна снимка в UNIQUE и получавате само уникалните комбинации от Sales Rep и Product using =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Все още не е сортирано

За да проверите вашето разбиране, опитайте да промените горната формула, за да върнете всички уникални комбинации от три колони: търговски представител, продукт, цвят.

Първо, променете константата на масива, за да се позовавате на (1,2,3).

След това добавете четвърти аргумент да се избере да се върне цвят от E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Върнете уникалната комбинация от три колони

Би било хубаво да сортираме тези резултати, затова се обръщаме към Сортиране с формула, използваща СОРТИРАНЕ и СОРТБИРАНЕ.

Обикновено функцията за сортиране по възходяща първа колона ще бъде =SORT(Array)или =SORT(Array,1,1).

За да сортирате по три колони, трябва да направите повдигане по двойки с =SORT(Array,(1,2,3),(1,1,1)). В тази формула, когато стигнете до втория аргумент на SORT, Excel иска да знае по коя колона да сортира. Вместо единична стойност, изпратете три колони в константата на масива: (1,2,3). Когато стигнете до третия аргумент, където посочете 1 за Ascending или -1 за Descending, изпратете константа на масив с три 1, за да посочите Ascending, Ascending, Ascending. Следващата екранна снимка показва =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

За повече информация относно повдигането по двойки вижте стр. 34 на Dynamic Arrays на Excel направо до точката.

Поне до края на 2018 г. можете да изтеглите книгата на Excel Dynamic Arrays безплатно, като използвате връзката в долната част на тази страница.

Насърчавам се да открия, че отговорът на днешния въпрос е малко сложен. Когато излязоха Dynamic Arrays, веднага се сетих за всички невероятни формули, публикувани на борда за съобщения от Aladin Akyurek и други, и как тези формули ще станат много по-опростени в новия Excel. Но днешният пример показва, че все още ще има нужда от формулирани гении да създават нови начини за използване на динамичните масиви.