Как получить список уникальных значений

В этой статье описывается, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить уникальный список с помощью расширенного фильтра Excel и как извлекать уникальные записи с помощью Duplicate Remover.

В последних нескольких статьях мы обсудили различные методы подсчета и поиска уникальных значений в Excel. Если у вас был шанс прочитать эти руководства, вы уже знаете, как получить этот список с помощью идентификации, фильтрации и копирования. Но это немного длинный и далеко не единственный способ извлечения уникальных значений в Excel. Вы можете сделать это гораздо быстрее с помощью специальной подготовки. И сейчас я покажу вам это и еще несколько приемов.

• Формулы для уникальных значений в столбце.
• Как извлечь уникальные + 1. встречающиеся дубликаты.
• Если вам нужно игнорировать пустые ячейки.
• Выбор уникального случая.
• Выбор уникальных значений по штату.
• Как извлечь уникальные значения из диапазона.
• Использование встроенного инструмента для удаления дубликатов.
• Уникальный листинг с использованием расширенного фильтра.
• Извлечение уникальных данных с помощью Duplicate Remover.

Базовые формулы для получения уникальных значений.

Чтобы избежать путаницы, давайте сначала договоримся о том, что мы называем уникальными значениями в Excel. 

Уникальные значения - это значения, которые встречаются в списке только один раз. Например:

 

 

Чтобы получить список уникальных значений в Excel, используйте одну из следующих формул.

Формула для уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):

=IFIFERROR(INDEX($A$2:$A$10; SEARCHPOS(0; COUNTSLICE($B$1:B1;$A$2:$A$10) + (COUNTSLICE($A$2:$A$10; $A$2:$A$10)1); 0))); "")

Вы также можете использовать обычную формулу (вводится нажатием клавиши Enter):

=IFERROR(INDEX($A$2:$A$10; SEARCHPOS(0;INDEX(COUNTSLICE($B$1:B1; $A$2:$A$10) + (COUNTSLICE($A$2:$A$10; $A$2:$A$10)1);0;0); "")

В приведенных выше формулах были использованы следующие ссылки:

• A2: A10 - это список исходных данных.
• B1 - это верхняя ячейка списка уникальных ячеек минус одна строка. В этом примере мы начинаем список уникальных строк с B2, поэтому в формулу вводим B1 (B2 - 1 строка = B1). Если ваш список начинается, скажем, с ячейки C3, измените $B$1:B1 на $C$2:C2.

Предупреждение. Поскольку формула ссылается на ячейку над первой ячейкой в созданном вами списке, которая обычно является заголовком столбца (B1 в данном примере), убедитесь, что ваш заголовок имеет уникальное имя, которое не встречается больше нигде в этом столбце.

В этом примере мы извлекаем уникальные имена из столбца A (точнее, из диапазона A2: A10), а приведенный ниже скриншот демонстрирует формулу в действии:

 

 

Вот наша процедура:

• Измените каждую формулу в соответствии с диапазоном данных.
• Введите его в первую ячейку, с которой начинается список (B2 в данном примере).
• Если вы используете формулу массива, нажмите Ctrl + Shift + Enter. Если вы выбрали обычную формулу, просто нажмите Enter.
• Скопируйте вниз до необходимого уровня, перетащив маркер заливки с помощью мыши. Поскольку обе формулы заключены в функцию IFRIC, вы можете скопировать их с полем. Это не приведет к повреждению данных какими-либо ошибками, независимо от того, сколько уникальных значений будет извлечено.

Как извлечь различные значения.

Разные значения - появляются хотя бы один раз в списке данных. Все они уникальны и являются первым появлением повторяющихся значений.

Например:

 

 

Чтобы получить их список в Excel, используйте следующие формулы.

Формула массива (требуется Ctrl + Shift + Enter):

{=IF ERROR(INDEX($A$2:$A$13; SEARCHPOS(0; COUNTSL($B$1:B1; $A$2:$A$13); 0)); "")}

Вы также можете сделать это следующим образом:

{=IF ERROR(INDEX($A$2:$A$13; LEAST(IF(ND(SEARCHPOS($A$2:$A$13;$B$1:B1;0)); LINE($A$1:$A$15);""); 1));"")}

Нормальная формула:

=IF ERROR(INDEX($A$2:$A$13; SEARCHPOS(0; INDEX(COUNTSLICE($B$1:B1; $A$2:$A$13); 0; 0)); "")

Где:

• A2: A13 - список источников.
• B1 - это ячейка над первой ячейкой отдельного списка. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на B1.

 

 

Как извлечь значения, игнорируя пустые ячейки

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

Формула массива для извлечения различных значений, исключая пустые ячейки:

{=IF ERROR(INDEX($A$2:$A$13; SEARCHPOS(0;COUNTSL($C$1:C1;$A$2:$A$13&"") + IF($A$2:$A$13=""";1;0); 0))); "")}

Аналогичным образом можно получить список различных значений, исключая пустые ячейки и ячейки с числами:

{=IF ERROR(INDEX($A$2:$A$13; SEARCHPOS(0;COUNTSL($D$1:D1;$A$2:$A$13&"") + IF(ETEXT($A$2:$A$13)=FALSE;1;0); 0))); "")}

Напомним, что в приведенных выше формулах A2:A13 - это исходный список, а B1 - ячейка, расположенная непосредственно над первой позицией сгенерированного списка.

На этом снимке экрана показан результат выбора:

 

 

Возможно, кому-то пригодится другая формула -.

=IFIF ERROR(INDEX($A$2:$A$13;AGGREGATE(15;6;(LINE($A$2:$A$13)-LINE($A$2)+1) / (LOOKUP($A$2:$A$13;$A$2:$A$13;0)=LINE($A$2:$A$13)-LINE($A$2)+1)); HSTRING($A$2:$A$2)+1);")

Работает с числами и текстом, игнорирует пустые ячейки.

Как извлечь отдельные значения с учетом регистра в Excel

При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вы можете захотеть выводить отдельные значения с учетом регистра. 

Для этого используйте формулу массива, где A2: A10 - исходный список, а B1 - ячейка над первой ячейкой индивидуального списка.

Формула массива для получения различных значений с учетом регистра (требуется Ctrl + Shift + Enter)

{=IF ERROR(INDEX($A$2:$A$10; SEARCHPOS(0; FREQUENCY(IF(MATCH($A$2:$A$10; TRANSP($B$1: B1)))); SEARCHPOS(STRING($A$2:$A$10); STRING($A$2:$A$10)); ""); SEARCHPOS(STRING($A$2:$A$10); STRING($A$2:$A$10)); 0)); "")}

 

 

Как видите, при выборе имеет значение случай.

Отбор уникальных значений по условию.

Представьте, что у нас есть таблица с данными о продажах. Нам нужно определить, какие товары были заказаны конкретным клиентом.

Сначала выберите только те строки из таблицы, которые удовлетворяют заданным условиям, а затем выберите уникальные элементы из этих строк.

 

 

В ячейке G2 мы указываем интересующего нас клиента, а в H2 вводим формулу массива следующим образом:

{=IF ERROR(INDEX($B$2:$B$20; SEARCHPOS(0;IF(($A$2:$A$20=$G$2)); COUNTSLIP($H$1:H1;$B$2:$B$20);"");0));"")})}

Помните, что формула массива должна быть введена в ячейку EXCEL путем одновременного нажатия CTRL+SHIFT+ENTER. Скопируйте его вниз по колонке с помощью маркера заливки. Вы получите список из четырех пунктов.

Давайте усложним задачу. Давайте определим список не только для этого клиента, но и для определенного менеджера.

 

 

Вот наша формула массива:

{=IF ERROR(INDEX($B$2:$B$20;SEARCHPOS(0; IF((($A$2:$A$20=$G$2)+($D$2:$D$20=$H$2))=2; COUNTSL($I$1:I1;$B$2:$B$20);");0)))})}

Как вы можете видеть, теперь есть только два товара. Только те строки, которые удовлетворяют одновременно двум условиям: название компании и имя менеджера должны совпадать. Только из них мы извлекаем уникальные названия продуктов.

Если условий больше, просто добавьте соответствующий критерий в функцию IF и измените число 2 на 3 или больше (в зависимости от количества условий).

Извлечь уникальные значения из диапазона.

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

 

 

Мы используем формулу массива

{=DSLINK(TEXT(MIN(IF(($A$2:$C$9"") * (COUNT($E$1:E1;$A$2:$C$9)=0); LINE($2:$9)*100 + COLUMN($A:$C);7^8)); "R0C00");)&""}

Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 - это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывают столбцы, из которых взяты исходные данные. Пожалуйста, измените их на свои собственные.

Нажмите Shift + Ctrl + Enter , затем перетащите маркер заполнения для извлечения уникальных значений, пока не увидите пустые ячейки.

Как видите, извлекаются все уникальные и первые вхождения дубликатов.

Встроенный инструмент удаления дубликатов.

Начиная с Excel 2007, функция удаления дубликатов является стандартной. Вы найдете его в разделе Данные > Удалить дубликаты.

 

 

Вы должны использовать флажок, чтобы указать столбцы, в которых вы хотите найти и удалить дубликаты значений. Если вы сделаете это, как показано на скриншоте, в таблице останутся только уникальные пары "Клиент - Товар". Остальные будут удалены. Если вы включите только флажок 'Customer', останется только одна строка для каждого клиента и т.д.

Использование расширенного фильтра.

Если вы не хотите тратить время на то, чтобы разобраться в загадочных формульных фразах, вы можете быстро получить список уникальных значений с помощью расширенного фильтра. Подробные инструкции приведены ниже.

1. Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
2. Перейдите на вкладку Данные > группа Сортировка и фильтр и нажмите Дополнительно .
3. В диалоговом окне Расширенный фильтр выберите следующие параметры:
   • Установите флажок Копировать в другое место .
   • В поле Source Range (Диапазон источника) убедитесь в правильности диапазона.
   • На месте результат... выберите самую верхнюю ячейку целевого диапазона. Обратите внимание, что скопировать отфильтрованные данные можно только на текущий лист.
   • Выберите "Только уникальные записи".
4. Наконец, нажмите OK и проверьте результат.

 

 

Как видите, мы проверили столбец B, а затем поместили список найденных в нем уникальных элементов в столбец K.

Обратите внимание, что хотя опция расширенного фильтра называется "Только уникальные записи", она принимает разные значения, т.е. уникальные и первые вхождения дубликатов.

Теперь давайте немного усложним задачу.

Если вы хотите искать записи более чем в одном столбце, их можно предварительно "объединить" с помощью функции SHIFT.

=JOIN(A2;B2)

Введите его в колонку F и скопируйте вниз. Мы получаем вспомогательную колонку.

 

 

Мы продолжаем выбирать данные в качестве исходного диапазона, из которого мы извлекаем уникальные значения. Теперь есть два столбца, A и B.

Но мы по-прежнему можем искать уникальные значения только в одном столбце. Именно для этого предназначена вспомогательная колонка F с объединенными данными. Это то, что мы указываем в поле 'Диапазон условий'.

Все остальное так же, как и в предыдущем примере.

В результате получаются все комбинации в таблице "Клиент - Товар" на основе данных во вспомогательном столбце F.

Думаю, вы понимаете, что то же самое можно сделать с тремя колонками (например, Фамилия - Имя - Имя отца). Главное условие - исходный диапазон должен быть непрерывным, т.е. все столбцы должны находиться рядом друг с другом.

Как видите, формулы здесь не нужны. Однако если исходные данные изменятся, все манипуляции придется повторить.

Извлечение уникальных значений с помощью Duplicate Remover.

В последней части этого руководства я покажу вам интересное решение для поиска и извлечения различных и уникальных значений в рабочих листах Excel. Это решение сочетает в себе универсальность формул Excel с простотой расширенного фильтра. Кроме того, вы найдете некоторые уникальные особенности:

• Поиск и извлечение уникальных или различных значений на основе записей в одном или нескольких столбцах.
• Найти, выбрать и скопировать уникальные значения в другом месте той же или другой рабочей книги Excel.

Теперь давайте посмотрим, как работает инструмент Duplicate Remover.

Предположим, у вас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что она содержит много повторяющихся строк, и ваша задача - извлечь уникальные строки, которые появляются в таблице только один раз, или различные строки, содержащие уникальные и впервые повторяющиеся вхождения. В любом случае, с помощью дополнения Duplicate Remover работа выполняется в несколько шагов.

1. Выберите любую ячейку в исходной таблице и нажмите DuplicateRemover на вкладке AblebitsData в группе Dedupe.

 

 

Будет запущен мастер удаления дубликатов, выбирающий всю таблицу. Поэтому просто нажмите кнопку "Далее", чтобы перейти к следующему шагу.

 

 

1. Выберите тип значения, которое вы хотите найти, и нажмите Далее :
   • Уникальный
   • Уникальный + 1-е появление (разное)

В данном примере мы хотим получить различные строки, которые хотя бы раз появляются в исходной таблице, поэтому мы выбираем Unique + 1st occurrences:
 

 

 

На полях. Как вы можете видеть на скриншоте выше, есть также 2 варианта поиска дубликатов. Просто имейте это в виду, если вам нужно искать дубликаты в таблице.

1. Выберите один или несколько столбцов для проверки уникальных значений.

В этом примере мы хотим удалить все дубликаты значений на основе значений в двух столбцах (customer и product), поэтому мы выбираем только те столбцы, которые нам нужны.

В нашем случае таблица имеет заголовок, поэтому установите флажок Моя таблица имеет заголовки.

Я думаю, нам не нужны пустые строки, которые могут случайно появиться при объединении данных из разных таблиц. Поэтому проверьте также пустые ячейки.

Если в наших записях есть лишние пробелы, я думаю, мы должны их игнорировать. Поэтому также проверьте Игнорируйте лишние пробелы.

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

 

 

1. Выберите действие, которое мы будем выполнять с найденными значениями. У вас есть следующие возможности:
   • Выделите цветом.
   • Выберите и выделите.
   • Выберите в колонке состояния.
   • Скопировать в другое место.

Чтобы сохранить исходные данные неизменными, выберите Копировать в другое место, а затем укажите, где должна появиться новая таблица - на том же листе (выберите Пользовательское местоположение и укажите верхнюю ячейку целевого диапазона), на новом листе или в новой рабочей книге.

В этом примере выберем новый лист:

 

 

1. Нажмите Готово. и готово!