УНИКАЛЬНО из несмежных столбцов - Советы по Excel

На днях я собирался создать уникальную комбинацию двух несмежных столбцов в Excel. Обычно я делаю это с помощью «Удалить дубликаты» или с помощью расширенного фильтра, но я подумал, что попробую сделать это с помощью новой функции UNIQUE, которая появится в Office 365 в 2019 году. Я пробовал несколько идей, но ни одна из них не сработала. Итак, я обратился за помощью к мастеру динамических массивов Джо МакДейду. Ответ довольно крутой, и я уверен, что забуду его, поэтому я документирую его для вас и для себя. Я уверен, что через два года я найду в Google, как это сделать, и пойму: «Ой, смотрите! Это я написал об этом статью!»

Прежде чем перейти к функции UNIQUE, взгляните на то, что я пытаюсь сделать. Мне нужна каждая уникальная комбинация торгового представителя из столбца B и продукта из столбца C. Обычно я выполняю следующие действия:

Скопируйте заголовки из B1 и D1 в пустой раздел рабочего листа.
В B1 выберите Data, Filter, Advanced
В диалоговом окне Advanced Filter выберите Copy To A New Location.
Задайте заголовки из шага 1 в качестве диапазона вывода
Установите флажок "Только уникальные значения"
Нажмите ОК

Скопируйте два заголовка в пустой раздел, который станет диапазоном вывода

Результатом является каждая уникальная комбинация двух полей. Обратите внимание, что расширенный фильтр не сортирует элементы - они отображаются в исходной последовательности.

Получение уникального списка - одно из моих любимых применений расширенного фильтра.

Этот процесс стал проще в Excel 2010 благодаря команде «Удалить дубликаты» на вкладке «Данные» ленты. Следуй этим шагам:

Выберите B1: D227 и Ctrl + C для копирования
Вставьте в пустой раздел рабочего листа.

Сделайте копию данных, поскольку удаление дубликатов разрушительно
Выберите данные, удалите дубликаты
В диалоговом окне «Удалить дубликаты» снимите флажок «Дата». Это указывает Excel, что нужно смотреть только на Rep и Product.
Нажмите ОК

Скажите Remove Duplicates, чтобы учитывать только Rep и Date

Результаты почти идеальные - вам просто нужно удалить столбец Дата.

Удалить лишний столбец

Вопрос: есть ли способ заставить функцию UNIQUE просматривать только столбцы B и D? (Если вы еще не видели новую функцию UNIQUE, прочтите: Функция UNIQUE в Excel.)

Запрашивая =UNIQUE(B2:D227), вы получите каждую уникальную комбинацию Rep, Date и Product, которая не является тем, что мы ищем.

Как мы можем передать два несмежных столбца функции UNIQUE?

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

См. Мою книгу для полного объяснения подъема (и позже, когда вы перейдете для сортировки результатов, парного подъема)

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

Но вместо этого вы собираетесь передать константу массива (1,2) в качестве первого аргумента для CHOOSE. Excel выполнит операцию подъема и дважды вычислит ВЫБРАТЬ. Для значения 1 вам нужны торговые представители в B2: B227. Для значения 2 вам нужны продукты в D2: D227.

Скажите ВЫБРАТЬ, чтобы вернуть два ответа

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

Успех! Отсюда все идет под гору

Я чувствую, что буду оскорблять ваш интеллект, если буду писать остальную часть статьи, потому что отсюда это очень просто.

Оберните формулу с предыдущего снимка экрана в UNIQUE, и вы получите только уникальные комбинации использования торгового представителя и продукта =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 и SORTBY.

Обычно сортировка по первому столбцу по возрастанию выполняется функцией =SORT(Array)или =SORT(Array,1,1).

Чтобы отсортировать по трем столбцам, вам нужно выполнить попарный подъем с помощью =SORT(Array,(1,2,3),(1,1,1)). В этой формуле, когда вы переходите ко второму аргументу СОРТИРОВКИ, Excel хочет знать, по какому столбцу сортировать. Вместо одного значения отправьте три столбца внутри константы массива: (1,2,3). Когда вы дойдете до третьего аргумента, где укажите 1 для возрастания или -1 для убывания, отправьте константу массива с тремя единицами, чтобы указать возрастание, возрастание, возрастание. На следующем снимке экрана показано =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Для получения дополнительной информации о попарном подъеме см. Стр. 34 книги «Динамические массивы Excel прямо к делу».

По крайней мере, до конца 2018 года вы можете бесплатно скачать книгу Excel Dynamic Arrays по ссылке внизу этой страницы.

Меня воодушевляет то, что ответ на сегодняшний вопрос немного сложен. Когда появились динамические массивы, я сразу подумал обо всех удивительных формулах, опубликованных на доске объявлений Аладином Акюреком и другими, и о том, как эти формулы станут намного проще в новом Excel. Но сегодняшний пример показывает, что гении формул по-прежнему будут нуждаться в разработке новых способов использования динамических массивов.