УНІКАЛЬНІ З сусідніх стовпців - Поради Excel

Днями я збирався створити унікальну комбінацію двох не суміжних стовпців у Excel. Зазвичай я роблю це з функцією «Видалити дублікати» або з розширеним фільтром, але я думав, що спробую це зробити за допомогою нової функції «УНІКАЛЬНИЙ», що з’явиться в Office 365 у 2019 році. Я спробував кілька ідей, і жодна з них не спрацювала. Отже, я звернувся до майстра Dynamic Arrays Джо Макдейда за допомогою. Відповідь досить класна, і я впевнений, що забуду про це, тому документую це для вас і для мене. Я впевнений, що через два роки я погуглю, як це зробити, і зрозумію: "О, дивіться! Я той, хто написав статтю про це!"

Перш ніж перейти до функції UNIQUE, подивіться, що я намагаюся зробити. Я хочу, щоб кожна унікальна комбінація торгового представника зі стовпця B та товару зі стовпця C. Зазвичай я виконувала такі дії:

Скопіюйте заголовки з B1 і D1 у порожній розділ аркуша
З B1 виберіть Data, Filter, Advanced
У діалоговому вікні Додатковий фільтр виберіть Копіювати в нове місце
Вкажіть заголовки з кроку 1 як діапазон вихідних даних
Установіть прапорець лише для унікальних цінностей
Клацніть OK

Скопіюйте два заголовки у порожній розділ, який стає діапазоном виводу

Результатом є кожна унікальна комбінація двох полів. Зверніть увагу, що розширений фільтр не сортує елементи - вони відображаються у вихідній послідовності.

Отримання унікального списку - одне з моїх улюблених способів розширеного фільтра

Цей процес став простішим у програмі Excel 2010 завдяки команді Видалити дублікати на вкладці Дані стрічки. Виконайте такі дії:

Виберіть B1: D227 та Ctrl + C для копіювання
Вставте в порожній розділ аркуша.

Зробіть копію даних, оскільки видалення дублікатів є руйнівним
Виберіть Дані, Видалити дублікати
У діалоговому вікні Видалити дублікати скасуйте вибір дати. Це вказує Excel, щоб дивитись лише на Rep і Product.
Клацніть OK

Скажіть "Видалити дублікати", щоб враховувати лише Rep та Date

Результати майже ідеальні - вам просто потрібно видалити стовпець 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 (для Banana). Функція CHOOSE очікує скаляр як перший аргумент.

Але замість цього ви збираєтесь передати константу масиву ((1,2) як перший аргумент для ВИБОРУ. 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(Array)або =SORT(Array,1,1).

Для того, щоб сортувати за трьома стовпчиками, потрібно виконати кілька парних підйомів =SORT(Array,(1,2,3),(1,1,1)). У цій формулі, переходячи до другого аргументу SORT, 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. Але сьогоднішній приклад показує, що геніям-формулам все одно буде потрібно створювати нові способи використання динамічних масивів.