Как найти все уникальные значение в столбце

Функция УНИК возвращает список уникальных значений в списке или диапазоне. 

Возвращение уникальных значений из списка значений

Возвращение уникальных имен из списка имен

=УНИК(массив,[by_col],[exactly_once])

Функция УНИК имеет следующие аргументы:

Аргумент	Описание
массив Обязательный	Диапазон или массив, из которого возвращаются уникальные строки или столбцы
[by_col] Необязательный	Аргумент by_col является логическим значением, указывающим, как проводить сравнение. Значение ИСТИНА сравнивает столбцы друг с другом и возвращает уникальные столбцы Значение ЛОЖЬ (или отсутствующее значение) сравнивает строки друг с другом и возвращает уникальные строки
[exactly_once] Необязательно	Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, встречающиеся в диапазоне или массиве только один раз. Это концепция базы данных УНИК. Значение ИСТИНА возвращает из диапазона или массива все отдельные строки или столбцы, которые встречаются только один раз Значение ЛОЖЬ (или отсутствующее значение) возвращает из диапазона или массива все отдельные строки или столбцы

Примечания: 

• Массив может рассматриваться как строка или столбец со значениями либо комбинация строк и столбцов со значениями. В примерах выше массивы для наших формул УНИК являются диапазонами D2:D11 и D2:D17 соответственно.

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

• Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Примеры

Пример 1

В этом примере СОРТ и УНИК используются совместно для возврата уникального списка имен в порядке возрастания.

Пример 2

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

Пример 3

В этом примере используется амперсанд (&) для сцепления фамилии и имени в полное имя. Обратите внимание, что формула ссылается на весь диапазон имен в массивах A2:A12 и B2:B12. Это позволяет Excel вернуть массив всех имен.

Советы: 

• Если указать диапазон имен в формате таблицы Excel, формула автоматически обновляется при добавлении или удалении имен.

• Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИК(B2:B12&» «&A2:A12))

Пример 4

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Ошибки #SPILL! в Excel

Динамические массивы и поведение массива с переносом

Оператор неявного пересечения: @

Как найти и выделить уникальные значения в столбце

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

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

• Уникальные значения – это элементы, которые появляются в наборе данных только один раз.
• Различные  – это элементы, которые появляются хотя бы один раз, то есть неповторяющиеся и первые вхождения повторяющихся значений.

А теперь давайте исследуем наиболее эффективные методы работы с уникальными и различными значениями в таблицах Excel.

• Как найти уникальные значения формулами.
• Фильтр для уникальных данных.
• Выделение цветом и условное форматирование.
• Быстрый и простой способ — Duplicate Remover.

Как найти уникальные значения при помощи формул.

Самый простой способ сделать это – использовать функции ЕСЛИ и СЧЁТЕСЛИ. В зависимости от типа данных, которые вы хотите найти, может быть несколько вариантов формулы, как показано в следующих примерах.

Как найти уникальные значения в столбце.

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

Чтобы найти уникальные значения в Excel:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10;  $A2)=1; «Уникальный»; «»)

Чтобы определить различные значения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2;  $A2)=1; «Различный»; «»)

Во второй формуле есть только одно небольшое отличие во второй ссылке на ячейку, что, однако, имеет большое значение:

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

Уникальные строки в таблице.

Аналогичным образом вы можете найти уникальные строки в таблице Excel на основе изучения записей не в одном, а в двух или более столбцах. В этом случае вам необходимо использовать СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ для оценки значений (до 127 пар диапазон/критерий можно обработать в одной формуле).

Формула для получения уникальных строк:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2)=1; «Уникальная»; «»)

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

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2)=1; «Различная»; «»)

В нашем случае уникальная комбинация Имя+Фамилия встречается 2 раза. А всего в списке 6 человек, из которых трое дублируются.

Как найти уникальные записи с учетом регистра?

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

Поиск уникальных значений с учетом регистра :

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$10;A2)))=1;»Уникальный»;»»)}

Поиск различных значений с учетом регистра :

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A2;$A2)))=1;»Различный»;»»)}

Поскольку обе они являются формулами массива, обязательно нажмите Ctrl + Shift + Enter, чтобы правильно их записать.

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

Фильтр для уникальных значений.

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

1. Примените одну из приведенных выше формул для определения уникальных или различных ячеек или строк.
2. Выберите диапазон и нажмите кнопку «Фильтр» на вкладке «Данные». 
3. Щелкните стрелку фильтрации в заголовке столбца, содержащего формулу, и выберите то, что хотите просмотреть:

Как выбрать уникальные из фильтра.

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

Чтобы быстро выбрать весь получившийся список, включая заголовки столбцов, отфильтруйте уникальные значения, щелкните любую ячейку в получившемся списке, а затем нажмите Ctrl + A.

Чтобы выбрать уникальные значения без заголовков столбцов, отфильтруйте их, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.

Примечание. В некоторых редких случаях, в основном в очень больших книгах, рекомендованные выше комбинации клавиш могут выбирать как видимые, так и невидимые ячейки. Чтобы исправить это, нажмите сначала либо Ctrl + A или же Ctrl + Shift + End, а затем нажмите Alt +;  для выбора только видимых ячеек, игнорируя скрытые строки.

Если вам сложно запомнить такое количество комбинаций, используйте этот визуальный способ: выделите весь список, затем перейдите на вкладку «Главная» > «Найти и выделить» > «Выделить группу ячеек» и выберите «Только видимые ячейки».

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

Чтобы скопировать список на новое место, сделайте следующее:

• Выберите отфильтрованные значения с помощью мыши или вышеупомянутых комбинаций клавиш.
• Нажмите Ctrl + C для копирования выбранных значений.
• Выберите верхнюю левую ячейку в целевом диапазоне (она может находиться на том же или другом листе) и нажмите Ctrl + V , чтобы вставить данные.

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

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

Самый быстрый и простой способ выделить уникальные значения в Excel — применить встроенное правило условного форматирования:

1. Выберите столбец данных, в котором вы хотите выделить уникальные.
2. На вкладке Главная в группе Стили щелкните Условное форматирование > Правила выделения ячеек > Повторяющиеся значения …

1. В диалоговом окне « Повторяющиеся значения » выберите «Уникальный» в левом поле и выберите желаемое форматирование в правом поле, затем нажмите « ОК» .

Совет. Если вас не устраивает какой-либо из предопределенных форматов, щелкните «Пользовательский формат …» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.

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

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

Как создать правило для условного форматирования уникальных значений?

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

Чтобы создать правило условного форматирования на основе формулы, выполните следующие действия:

1. Перейдите на вкладку «Главная » и щелкните « Условное форматирование» > « Новое правило» > «Использовать формулу», чтобы с ее помощью определить, какие ячейки нужно форматировать .
2. Введите формулу в поле «Форматировать значения …».
3. Нажмите кнопку «Формат …» и выберите нужный цвет заливки и/или цвет шрифта.
4. Наконец, нажмите кнопку ОК , чтобы применить правило.

Более подробные инструкции см. в статье: Как создать правила условного форматирования Excel на основе другого значения ячейки .
А теперь поговорим о том, какие формулы использовать и в каких случаях.

Выделяем цветом отдельные уникальные значения.

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

=СЧЁТЕСЛИ($A$2:$A$10;$A2)=1

Где A2 — первая, а A10 — последняя ячейка диапазона.

Чтобы выделить все различные значения в столбце, то есть встречающиеся хотя бы однажды, используйте это выражение:

= СЧЁТЕСЛИ($A$2:$A2;$A2)=1

Где A2 — самая верхняя ячейка диапазона.

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

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

На следующем скриншоте показано, как выглядит правило, выделяющее строки на основе уникальных значений в столбце A:

Как видите, формула

=СЧЁТЕСЛИ($A$2:$A$10;$A2)=1

та же самая, что и раньше, но строка в диапазоне выделена вся.

А можно использовать и такое выражение:

=СУММ(Ч($A2&$B2=$A$2:$A$10&$B$2:$B$10))<2

Результат будет таким же.

Как выделить уникальные строки?

Если вы хотите выделить строки на основе значений в двух или более столбцах, используйте функцию СЧЁТЕСЛИМН, которая позволяет указать несколько критериев в одной формуле.

Чтобы выделить уникальные строки:

=СЧЁТЕСЛИМН($A$2:$A$10;$A2; $B$2:$B$10;$B2)=1

Чтобы выделить различные строки:

=СЧЁТЕСЛИМН($A$2:$A2;$A2; $B$2:$B2;$B2)=1

Быстрый и простой способ найти и выделить уникальные значения

Как вы только что видели, Microsoft Excel предоставляет довольно много полезных функций, которые могут помочь вам идентифицировать и выделять уникальные значения на ваших листах. 

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

Для тех пользователей Excel, которые хотят сэкономить свое время и силы, позвольте мне показать быстрый и простой способ поиска уникальных значений в Excel.

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

Давайте посмотрим.

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

Мастер запустится, и вся таблица будет выбрана автоматически. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.

1. В зависимости от вашей цели выберите один из следующих вариантов и нажмите Далее :
   • Уникальные
   • Уникальные + 1^е вхождения (различные)

1. Выберите один или несколько столбцов, в которых вы хотите проверить значения.
   В этом примере мы хотим найти уникальные сочетания Заказчик + Товар на основе значений в двух столбцах. Их и выбираем при помощи галочки.
2. Выберите один или несколько столбцов, в которых вы хотите проверить значения.

Если у вашей таблицы есть заголовки, обязательно установите флажок Mytable has headers. И если в вашей таблице могут встретиться пустые ячейки, то убедитесь, что установлен флажок Skipempty cells. Оба параметра находятся в верхней части диалогового окна и обычно выбираются по умолчанию.

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

Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не активируем опцию Case-sensitive match.

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

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

В нашем случае чтобы найти уникальные значения, вполне достаточно будет просто выделить их цветом. Поэтому выберем Highlight with color.

Нажмите кнопку «Готово» и получите результат:

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

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

Извлечение уникальных элементов из диапазона

Способ 1. Штатная функция в Excel 2007

Начиная с 2007-й версии функция удаления дубликатов является стандартной — найти ее можно на вкладке Данные — Удаление дубликатов (Data — Remove Duplicates):

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

Способ 2. Расширенный фильтр

Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data).

Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:

Выбираем в меню Данные — Фильтр — Расширенный фильтр (Data — Filter — Advanced Filter). Получаем окно:

В нем:

• Выделяем наш список компаний в Исходный диапазон (List Range).
• Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
• Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE):

Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.

Способ 3. Выборка уникальных записей формулой

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

Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:

Первая задача — пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)

В английской версии это будет:

=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз — дает ему последовательно возрастающий номер.

Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы — Диспетчер имен (Formulas — Name manager) или в старых версиях — через меню Вставка — Имя — Присвоить (Insert — Name — Define):

• диапазону номеров (A1:A100) — имя NameCount
• всему списку с номерами (A1:B100) — имя NameList

Теперь осталось выбрать из списка NameList все элементы имеющие номер — это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:

=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);»»;ВПР(СТРОКА(1:1);NameList;2))

или в английской версии Excel:

=IF(MAX(NameCount)

Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:

Ссылки по теме

• Выделение дубликатов по одному или нескольким столбцам в списке цветом
• Запрет ввода повторяющихся значений
• Извлечение уникальных значений при помощи надстройки PLEX

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

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

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

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

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

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

= SEERROR (ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК (0; СЧЁТЕСЛИ ($ B $ 1: B1; $ A $ 2: $ A $ 10) + (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10; $ A $ 2: $ A $ 10) 1); 0)); «»)

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

= ЕСЛИ ОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК (0; ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1; $ A $ 2: $ A $ 10)) + (СЧЁТЕСЛИ ($ A $ 2 : $ A $ 10; $ A $ 2: $ A $ 10) 1); 0; 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.
• При необходимости скопируйте, перетащив маркер заливки мышью. Поскольку обе формулы заключены в функцию SEERROR, вы можете скопировать ее с полем. Это не испортит ваши данные ошибками, сколько бы уникальных значений ни было восстановлено.

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

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

Например:

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

Формула массива (нужно нажать Ctrl + Shift + Enter):

{= SEERROR (ИНДЕКС ($ A $ 2: $ A $ 13, ПОИСК (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 13), 0)); «»)}

или вы можете сделать это:

{= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; МАЛЫЙ (ЕСЛИ (UND (ПОИСК ($ A $ 2: $ A $ 13, $ B $ 1: B1,0)))), СТРОКА ($ A $ 1: $ A $ 15); «»); 1));»»)}

Обычная формула:

= SEERROR (ИНДЕКС ($ A $ 2: $ A $ 13, ПОИСК (0, ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 13), 0, 0), 0)); «»)

Где:

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

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

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

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

{= ЕСЛИ ОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; ПОИСК (0, СЧЁТЕСЛИ ($ C $ 1: C1; $ A $ 2: $ A $ 13 & «»)) + IF ($ A $ 2: $ A $ 13 = «»; 1; 0); 0)); «»)}

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

{= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; ПОИСК (0; СЧЁТЕСЛИ ($ D $ 1: D1; $ A $ 2: $ A $ 13 & «»)) + SE (ETEXT ($ A $ 2: $ A $ 13) = FALSE; 1; 0); 0)); «»)}

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

Этот экран показывает результат выбора:

Возможно, кому-то будет полезна другая формула –

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 13; АГРЕГАТ (15,6; (СТРОКА ($ A $ 2: $ A $ 13) -ЛИНИЯ ($ A $ 2) +1)) / (ПОИСК ($ A $ 2: $ A $ 13; $ A $ 2: $ A $ 13,0) = СТРОКА ($ A $ 2: $ A $ 13) — СТРОКА ($ A $ 2) +1); СТРОКА ($ A $ 2: $ A2)));»»)

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

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

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

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

{= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК (0; ЧАСТОТА (ЕСЛИ (ПОИСКПОЗ ($ A $ 2: $ A $ 10; ТРАНСПОРТИРОВКА ($ B $ 1: B1))))); ПОИСК (СТРОКА ($ A $ 2: $ A $ 10); СТРОКА ($ A $ 2: $ A $ 10)); «»); ПОИСК ($ A $ 2: $ A $ 10); СТРОКА ($ A 2 доллара: 10 австралийских долларов))); 0)); «»)}

Как видите, здесь важна накрутка.

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

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

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

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

{= ЕСЛИ ОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 20; ПОИСК (0; ЕСЛИ ((($ A $ 2: $ A $ 20 = $ G $ 2))), СЧЁТЕСЛИ ($ H $ 1: H1 ; $ B $ 2: $ B $ 20); «»); 0));»»)}

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

Усложняем задачу. Мы определяем список не только для этого клиента, но и для конкретного менеджера.

Вот наша матричная формула:

{= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 20; ПОИСК (0; ЕСЛИ ((($ A $ 2: $ A $ 20 = $ G $ 2)) + ($ D $ 2: $ D $ 20 = $ H $ 2)) = 2; СЧЁТЕСЛИ ($ I $ 1: I1, $ B $ 2: $ B $ 20), «»); 0));»»)}

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

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

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

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

Используя формулу массива

{= КОСВЕННО (ТЕКСТ (МИН (ЕСЛИ (($ A $ 2: $ C $ 9 «») * (СЧЁТЕСЛИ ($ E $ 1: E1; $ A $ 2: $ C $ 9) = 0); ROW ($ 2: $ 9) * 100 + COLUMN ($ A: $ C); 7 ^ 8)); «R0C00″);)&»»}

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

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

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

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

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

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

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

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

1. Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
2. Перейдите на вкладку «Данные»> группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно» .
3. В диалоговом окне Advanced Filter выберите следующие параметры:
   • Установите флажок Копировать в другое место .
   • Убедитесь, что в поле Диапазон источника указано правильное значение.
   • В поле «Поместить результат в…» укажите верхнюю ячейку целевого диапазона. Помните, что вы можете копировать только отфильтрованные данные в текущий лист.
   • Выберите Только уникальные записи».
4. Наконец, нажмите ОК и проверьте результат.

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

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

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

Если вам нужно искать записи не по одному, а по нескольким столбцам, вы можете сначала «вставить» их с помощью функции СЦЕПИТЬ.

= ЦЕПЬ (A2; B2)

Записываем в столбец F и копируем. Получаем вспомогательный столбик.

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

Но мы все еще можем искать уникальные в одном столбце. Здесь пригодится вспомогательный столбец F с объединенными данными. Мы указываем это в поле «Диапазон условий».

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

В результате мы получили все доступные в таблице комбинации «Клиент — Продукт» на основе данных во вспомогательном столбце F.

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

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

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

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

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

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

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

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

Мастер удаления дубликатов запустится и выберет всю таблицу. Затем нажмите «Далее», чтобы перейти к следующему шагу.

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

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

В примечании. Как вы можете видеть на скриншоте выше, есть также 2 варианта поиска дубликатов. Имейте это в виду, если вам нужно искать дубликаты в таблице.

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

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

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

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

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

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

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

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

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

Нажмите кнопку «Готово» и все!

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

Как узнать количество уникальных значений в столбцах Excel

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

Называется она «Уник» использовать её очень просто, в любую пустую ячейку в которой хотите увидеть результат пишем «=уник» и выбираем диапазон ячеек столбца.

После выполнения она отобразить все уникальные значения из указанного диапазона.

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

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

Вот таким образом можно найти все уникальные значения столбцов в Excel, данная функция доступна во всех версиях Microsoft Office.