Как вывести уникальные значения excel
6 способов создать список уникальных значений в Excel
Здравствуй уважаемый пользователь!
В этой статье я хочу рассказать о возможности создать список уникальных значений в таблицах Excel. Эта возможность очень часто используется при работе с таблицами, так как часто возникает потребность с большого массива данных выбрать уникальные данные, которые не повторяются. Это может быть нужно для разнообразных целей, и уже вам решать каким способом и как произвести отбор нужных вам уникальных значений.
Список уникальных значений возможно создать 6-ю способами:
Создать список уникальных значений с помощью специальной функции
Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».
В появившемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где необходимо произвести отсев уникальных значений и нажимаете «Ок». В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные. 
Внимание! Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками.
Создать список уникальных значений с помощью расширенного фильтра
Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте. 
После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок». 
Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.
Внимание! Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.
Создать список уникальных значений с помощью формул
Этот способ более сложен, нежели те, что мы рассматривали ранее, но его преимущество в том, что он более динамичен и работает на постоянной основе. В разных случаях вам будут нужны разные формулы, вот и рассмотрим несколько вариантов и примеров.
Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)
Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку. 
Теперь можно произвести отбор уникальных значений, которые были ранее пронумерованы. Сделать это возможно в любом из соседних столбиков используя функцию ВПР и копируя ее вниз:
=ЕСЛИ(МАКС(A1:A100)
С ростом богатства растут и заботы. Гораций
Отбор уникальных значений (убираем повторы из списка) в EXCEL
history 22 апреля 2013 г.
Имея список с повторяющимися значениями, создадим список, состоящий только из уникальных значений. При добавлении новых значений в исходный список, список уникальных значений должен автоматически обновляться.
Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний.
Задача
Решение
Для создания Динамического диапазона :
Список уникальных значений создадим в столбце B с помощью формулы массива (см. файл примера ). Для этого введите следующую формулу в ячейку B5 :
Разберем работу формулу подробнее:
Формула будет работать и в случае если исходный список содержит числовые значения.
Решение для списков с пустыми ячейками
Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ): =ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19; ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0) );»»)
Решение без формул массива
Примечание : В статье Восстанавливаем последовательности из списка без повторов в MS EXCEL решена обратная задача: из списка уникальных значений, в котором для каждого значения задано количество повторов, создается список этих значений с повторами.
Извлечение уникальных элементов из диапазона
Способ 1. Штатная функция в Excel 2007
В открывшемся окне нужно с помощью флажков задать те столбцы, по которым необходимо обеспечивать уникальность. Т.е. если включить все флажки, то будут удалены только полностью совпадающие строки. Если включить только флажок заказчик, то останется только по одной строке для каждого заказчика и т.д.
Способ 2. Расширенный фильтр
Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:
Получите список без дубликатов:
Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :
Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.
Способ 3. Выборка уникальных записей формулой
Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:
В английской версии это будет:
=ЕСЛИ(МАКС(NameCount) или в английской версии Excel:
Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:
Как получить список уникальных значений
В статье описано, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить отдельный список с помощью расширенного фильтра Excel и как извлечь уникальные записи с помощью Duplicate Remover.
В нескольких недавних статьях мы обсудили различные методы подсчета и поиска уникальных значений в Excel. Если у вас была возможность прочитать эти руководства, вы уже знаете, как получить этот список при помощи идентификации, фильтрации и копирования. Но это немного длинный и далеко не единственный способ извлечения уникальных значений в Excel. Вы можете сделать это намного быстрее, используя специальную формулу. И сейчас я покажу вам этот и несколько других приёмов.
Базовые формулы для получения уникальных значений.
Чтобы избежать путаницы, сначала давайте договоримся о том, что мы называем уникальными значениями в Excel.
Чтобы получить список уникальных значений в Excel, используйте одну из следующих формул.
Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):
Можно воспользоваться и обычной формулой (вводится нажатием Enter):
В приведенных выше формулах используются следующие ссылки:
Примечание. Поскольку формула ссылается на ячейку, расположенную над первой ячейкой создаваемого списка, который обычно является заголовком столбца (B1 в этом примере), то убедитесь, что ваш заголовок имеет уникальное имя, которое больше нигде в этом столбце не появляется.
В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:
Вот наш порядок действий:
Как извлечь различные значения.
Различные значения — появляются в перечне данных хотя бы один раз. Это все уникальные и первое вхождение повторяющихся значений.
Чтобы получить их список в Excel, используйте следующие формулы.
Формула массива (требуется нажать Ctrl + Shift + Enter ):
Обычная формула:
Как извлечь значения, игнорируя пустые ячейки
Если исходный список содержит пустые ячейки, формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Это вы и наблюдаете на скриншоте чуть выше. Чтобы исправить это, сделаем несколько небольших корректировок.
Формула массива для извлечения различных значений, исключая пустые ячейки:
Аналогичным образом вы можете получить список различных значений, исключая пустые ячейки и ячейки с числами:
Напоминаем, что в приведенных выше формулах 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)));»»)
Она работает с числами и текстом, игнорирует пустые ячейки.
Как извлечь отдельные значения с учетом регистра в Excel
При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вам может потребоваться список отдельных значений с учетом заглавных и прописных букв.
Формула массива для получения различных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter )
Как видите, при отборе регистр здесь имеет значение.
Отбор уникальных значений по условию.
Представим, что у нас есть таблица с данными о продажах. Нам необходимо определить, какие наименования товаров заказывал определенный покупатель.
Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем уникальные наименования товаров.
В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:
Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.
Вот наша формула массива:
Как видите, теперь товаров всего два. В подсчете принимают участие только те строки, которые удовлетворяют сразу двум условиям: должно совпасть название фирмы и фамилия менеджера. Только из них мы извлекаем уникальные названия товаров.
В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).
Извлечь уникальные значения из диапазона.
Формулы, которые мы описывали выше, позволяют сформировать список значений из данных определенного столбца. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных. К примеру, вы получили несколько списков товаров из различных файлов и расположили их в соседних столбцах.
Используем формулу массива
Как видите, извлекаются все уникальные и первые вхождения дубликатов.
Встроенный инструмент удаления дубликатов.
Начиная с Excel 2007 функция удаления дубликатов является стандартной. Найти ее можно на вкладке Данные > Удаление дубликатов.
Вам нужно при помощи птички указать столбцы, в которых нужно найти и удалить повторяющиеся значения. Если сделать так, как на скриншоте, то в таблице останутся только уникальные пары «Заказчик – Товар». Остальное будет удалено. Если включить только флажок «Заказчик», то останется только по одной строке для каждого заказчика и т.д.
Использование расширенного фильтра.
Если вы не хотите тратить время на выяснение загадочных поворотов формул, вы можете быстро получить список уникальных значений с помощью расширенного фильтра. Подробные инструкции приведены ниже.
Как видите, мы проверили колонку B, и затем список уникальных наименований товара, найденных в ней, поместили в столбец K.
Обратите внимание, что хотя опция расширенного фильтра называется «Только уникальные записи», она извлекает различные значения, то есть уникальные и первые вхождения повторяющихся.
Теперь немного усложним задачу.
Если требуется искать записи не по одному, а по нескольким столбцам, то можно их предварительно «склеить» при помощи функции СЦЕПИТЬ.
Записываем это в столбец F и копируем вниз. Получаем вспомогательную колонку.
В качестве исходного диапазона мы по-прежнему выбираем данные, из которых извлекаем уникальные значения. Теперь это два столбца – A и B.
Но искать уникальные мы по-прежнему можем только в одном столбце. Вот для этого нам и пригодится вспомогательная колонка F с объединенными данными. Ее то мы и указываем в поле «Диапазон условий».
Все остальное – так же, как и в предыдущем примере.
В результате мы получили все имеющиеся в таблице комбинации «Заказчик — Товар» на основе данных во вспомогательном столбце F.
Думаю, вы понимаете, что аналогичные действия можно произвести и с тремя столбцами (например Фамилия – Имя – Отчество). Главное условие – исходный диапазон должен быть непрерывным, то есть все столбцы должны находиться рядом.
Как видите формулы здесь не нужны. Однако, если исходные данные изменятся, то все манипуляции придется повторять заново.
Извлечение уникальных значений с помощью Duplicate Remover.
В заключительной части этого руководства я покажу вам интересное решение для поиска и извлечения различных и уникальных значений в таблицах Excel. Это решение сочетает в себе универсальность формул Excel и простоту расширенного фильтра. Кроме того, здесь есть несколько уникальных функций:
А теперь давайте посмотрим, как работает инструмент Duplicate Remover.
Предположим, у вас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что она содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или различные строки, включая уникальные и первые повторяющиеся вхождения. В любом случае, с надстройкой Duplicate Remover работа выполняется за несколько шагов.
Мастер Duplicate Remover запустится и выберет всю таблицу. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.
В этом примере мы хотим извлечь различные строки, которые появляются в исходной таблице хотя бы один раз, поэтому мы выбираем опцию Unique + 1st occurences:
На заметку. Как вы можете видеть на приведенном выше скриншоте, есть также 2 варианта поиска дубликатов. Просто имейте это в виду, если нужно будет искать повторы в таблице.
В этом примере мы хотим убрать все повторяющиеся значения на основе значений в 2 столбцах ( заказчик и товар), поэтому мы выбираем только нужные нам столбцы.
В нашем случае таблица имеет заголовок, поэтому отмечаем птичкой пункт My table has headers.
Думаю, нам не нужны пустые строки, которые могут случайно встретиться при объединении данных из разных таблиц. Поэтому отмечаем такжеSkip empty cells.
Если вдруг в наших записях случайно появились лишние пробелы, то, думаю, стоит их игнорировать. Поэтому отмечаем также Ignore extra spaces.
Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не трогаем опцию Case-sensitive match.
Чтобы не менять исходные данные, выберите «Копировать в другое место» (Copy to another location), а затем укажите, где именно вы хотите видеть новую таблицу – на этом же листе (выберите параметр «Custom Location» и укажите верхнюю ячейку целевого диапазона), на новом листе (New worksheet) или в новой книге (New workbook).
В этом примере давайте выберем новый лист:
В итоге у нас осталось всего 20 записей.
Понравился этот быстрый и простой способ получить список уникальных значений или записей в Excel? Если да, то я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover.
В Ultimate Suite for Excel также включено много других полезных инструментов, которые помогут вам сэкономить много времени. Мы о них также будем подробно рассказывать в других материалах на сайте.
Фильтр уникальных значений или удаление повторяющихся значений
В Excel есть несколько способов отфильтровать уникальные значения или удалить повторяющиеся значения:
Чтобы отфильтровать уникальные значения, щелкните Ссылки > сортировки & фильтр > Расширенные.
Чтобы удалить повторяющиеся значения, выберите в > в > удалить дубликаты. 
Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная.
Фильтрация уникальных значений и удаление повторяюющихся значений — две похожие задачи, так как их цель — представить список уникальных значений. Однако существует критическое различие: при фильтрации уникальных значений повторяющиеся значения скрываются только временно. Однако удаление повторяюющихся значений означает, что повторяющиеся значения удаляются окончательно.
Повторяютая строка — это значение, в котором все значения хотя бы в одной строке совпадают со всеми значениями в другой строке. Сравнение повторяюющихся значений зависит от того, что отображается в ячейке, а не от значения, хранимого в ячейке. Например, если в разных ячейках есть одно и то же значение даты в формате «08.03.2006», а в другом — «8 марта 2006 г.», значения будут уникальными.
Прежде чем удалять дубликаты, проверьте: Прежде чем удалять повторяющиеся значения, сначала попробуйте отфильтровать уникальные значения (или отформатировать их с условием), чтобы достичь нужного результата.
Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
Щелкните > дополнительные данные (в группе Фильтр & сортировки).
Во всплывающее окно Расширенный фильтр сделайте следующее:
Чтобы отфильтровать диапазон ячеек или таблицу на месте:
Щелкните Фильтровать список на месте.
Чтобы скопировать результаты фильтра в другое место:
Нажмите кнопку Копировать в другое место.
В поле Копировать в введите ссылку на ячейку.
Вы также можете нажать кнопку Свернуть 
, чтобы временно скрыть всплывающее окно, выбрать ячейку на этом сайте и нажать кнопку Развернуть 
.
Проверьте только уникальные записии нажмите кнопку ОК.
Уникальные значения из диапазона копируются в новое место.
При удалите повторяющиеся значения, только на значения в диапазоне ячеек или таблице. Другие значения за пределами диапазона ячеек или таблицы не изменяются и не перемещаются. При удалении дубликатов первое вхождение значения в списке будет сохранено, а другие одинаковые значения будут удалены.
Так как данные удаляются окончательно, перед удалением повторяюющихся значений лучше скопировать исходный диапазон ячеек или таблицу на другой.
Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
На вкладке Данные нажмите кнопку Удалить дубликаты (в группе Инструменты для работы с данными).
Выполните одно или несколько из указанных ниже действий.
В области Столбцывыберите один или несколько столбцов.
Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
Чтобы быстро очистить все столбцы, нажмите кнопку Отклоните все.
Если диапазон ячеек или таблицы содержит много столбцов и нужно выбрать только несколько столбцов, вам может быть проще нажать кнопку Отобрать все,а затем в столбцах выберите эти столбцы.
Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключом, используемым для поиска дубликатов, будет значение BOTH Column1 & Column2. Если в этих столбцах найдено повторяющиеся записи, удаляется вся строка, включая другие столбцы в таблице или диапазоне.
Нажмите кнопкуОК, и появится сообщение, в которое будет указано, сколько повторяюных значений было удалено или сколько уникальных значений осталось. Нажмите кнопку ОК, чтобы отклонять это сообщение.
Чтобы отменить изменение, нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).
Повторяющиеся значения невозможно удалить из структурных данных или с суммами. Чтобы удалить дубликаты, необходимо удалить структуру и подытогов. Дополнительные сведения см. в таблицах Структурная схема данных на листе и Удаление подытогов.
Примечание: Нельзя условно отформатировать поля в области значений отчета отчетов данных по уникальным или повторяемым значениям.
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
Введите нужные значения и выберите формат.
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку для команды Условное форматирование ивыберите управление правилами, чтобы отобразить всплывающее окно Диспетчер правил условного форматирования.
Выполните одно из следующих действий:
Чтобы добавить условное форматирование, нажмите кнопку Новое правило, чтобы отобразить всплывающее окно Новое правило форматирования.
Чтобы изменить условное форматирование, начните с того, что в списке Показать правила форматирования для выбран соответствующий лист или таблица. При необходимости выберите другой диапазон ячеек, нажав кнопку Свернуть во всплывающее окно Применяется к временно скрыть его. Выберите новый диапазон ячеек на этом сайте, а затем снова разширив всплывающее окно, . Выберите правило и нажмите кнопку Изменить правило, чтобы отобразить всплывающее окно Изменение правила форматирования.
В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.
В списке Форматировать все выберите изменить описание правила, выберите уникальный или дубликат.
Нажмите кнопку Формат, чтобы отобразить всплывающее окно Формат ячеек.
Выберите формат числа, шрифта, границы или заливки, который вы хотите применить, когда значение ячейки удовлетворяет условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы отображаются на панели предварительного просмотра.
В Excel в Интернете можно удалить повторяющиеся значения.
Удаление повторяющихся значений
При удалите повторяющиеся значения, только на значения в диапазоне ячеек или таблице. Другие значения за пределами диапазона ячеек или таблицы не изменяются и не перемещаются. При удалении дубликатов первое вхождение значения в списке будет сохранено, а другие одинаковые значения будут удалены.
Важно: Вы всегда можете нажать кнопку Отменить, чтобы вернуть данные после удаления дубликатов. При этом перед удалением повторяюющихся значений лучше скопировать исходный диапазон ячеек или таблицу на другой рабочий или другой.
Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
На вкладке Данные нажмите кнопку Удалить дубликаты.
В диалоговом окне Удаление дубликатов снимите с нее все столбцы, в которых не нужно удалять повторяющиеся значения.
Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключом, используемым для поиска дубликатов, будет значение BOTH Column1 & Column2. Если в столбцах «Столбец1» и «Столбец2» найдено повторяющиеся данные, удаляется вся строка, включая данные из столбца «Столбец3».
Нажмите кнопкуОК, и появится сообщение, в которое будет указано, сколько повторяюных значений было удалено. Нажмите кнопку ОК, чтобы отклонять это сообщение.
Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.