Выделить уникальные значения в столбце 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)
С ростом богатства растут и заботы. Гораций
Извлечение уникальных элементов из диапазона
Способ 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 и копируем затем вниз до упора следующую формулу:
В английской версии это будет:
Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз — дает ему последовательно возрастающий номер.
Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы — Диспетчер имен (Formulas — Name manager) или в старых версиях — через меню Вставка — Имя — Присвоить (Insert — Name — Define) :
- диапазону номеров (A1:A100) — имя NameCount
- всему списку с номерами (A1:B100) — имя NameList
Теперь осталось выбрать из списка NameList все элементы имеющие номер — это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:
Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:
Выделение уникальных значений в MS EXCEL
Настроим Условное форматирование для выделения только уникальных (различающихся) значений в столбце.
Необходимо выделить все ячейки за исключением им повторов, т.е. только уникальные. Выделять уникальные значения будем с помощью Условного форматирования (см. Файл примера ).
- выделите диапазон содержащий список значений, например, А3:А16;
- вызовите Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило/ Использовать формулу для определения форматируемых ячеек);
- введите формулу =ПОИСКПОЗ(A3;$A$3:$A$16;0)=СТРОКА(A3)-СТРОКА($A$2)
Обратите внимание, что в формуле использована относительная адресация, поэтому активной ячейкой при вводе формулы должна быть A3 (т.е. диапазон нужно выделять сверху вниз). Активная ячейка в выделенном диапазоне – белая и ее адрес отображается в поле Имя.
- выберите нужное форматирование;
- нажмите ОК.
Будут выделены все неповторяющиеся значения и, дополнительно, среди повторяющихся значений будут выделены только первые (самые верхние) дубликаты.
Для выделения повторяющихся значений в EXCEL существуют уже заранее созданные правила Условного форматирования. Если вызвать команду меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Повторяющиеся значения, и диалоговом окне выбрать уникальные, то будут выделены неповторяющиеся значения. О несоответствии терминологии повторяющихся значений читайте в статье Классификация значений по уникальности.
Как получить отдельные / уникальные значения в столбце Excel?
Если у меня есть столбец со значениями, и я хочу узнать, какие существуют различные значения (не сколько — но фактических отдельных значений), как я могу это сделать?
В SQL Server я бы сделал что-то вроде
Проще чем вы думаете:
- Перейдите в меню Данные Лента
- Выберите кнопку Дополнительно в Sort & amp; Фильтр
- Заполните диалоговое окно, скопировав результаты в другое место и убедившись, что отметьте только уникальные записи
Excel 2007 и 2010:
Используйте пункт меню Remove Duplicates в заголовке Данные .
Excel 2003:
Простой способ:
- Убедитесь, что ваши данные имеют заголовок
- Данные -> Фильтр -> Расширенный фильтр
- Проверить только уникальные записи
- Выберите Копировать в другое место
- Нажмите «ОК»
Напишите макрос со следующим кодом:
Это дает вам свой список. Вы можете сначала скопировать список на другой лист.
Я использую два основных метода для получения различных значений: первый — это бесценный инструмент Remove Duplicates как предлагаемый Nathan DeWitt .
Недостатком Remove Duplicates является то, что вы теряете данные. Несмотря на мощь Ctrl + z , вы можете не захотеть делать это по разным причинам — например, у вас могут быть выбранные фильтры, которые делают это довольно грязным, чтобы удалить дубликаты, чтобы получить отличный список значений.
Другой метод, не упомянутый выше, заключается в использовании сводной таблицы.
Предположим, что это ваша таблица, и вы хотите получить отдельный список платформ.
Вставьте с таблицей в качестве источника данных.
Теперь выберите столбец Платформа (ы) в качестве Row Labels .
Voila! Список различных значений.
Если вы хотите его убрать, вы можете отключить Head Headers и установить Grand Totals как Off для строк и столбцов .
Или (простой грубый способ):
и скопируйте. Он просто копирует первое вхождение каждого значения через (в строке, в которой он встречается).
Или вы можете включить параметр фильтра в макрос
Для меня самый простой способ сортировки — использовать опцию «Фильтр».
Первый шаг — найти дубликаты значений с Условным форматированием -> Выделять правила ячеек -> Дублирующие значения.
Затем щелкните стрелку на столбце, где у вас есть дубликаты (в данном случае «Платформы») -> Фильтровать по цвету (цвет или цвет ячеек).
Как сделать список с уникальными значениями в Excel
В данной статье мы рассмотрим с вами как найти уникальные значения в Excel. Предположим у вас есть выгрузка наименований телефонов, причем в данной выгрузке мобильные телефоны повторяются по несколько раз.
Ваша задача из данной выгрузки в Excel сформировать уникальный список телефонов. На практике подобные задачи приходится решать очень часто.
Сегодня мы рассмотрим первый способ нахождения и формирования списка уникальных значений в Excel с помощью инструмента Расширенный фильтр. Для этого необходимо выделить весь исходный список, затем выбрать Расширенный фильтр:
- В Excel 2003 расширенный фильтр находится в разделе Данные | Фильтр | Расширенный фильтр
- В Excel 2007 и 2010 расширенный фильтр находится в меню Данные | Дополнительно
После этого появится предупреждающее окно “Приложению Microsoft Excel не удается определить, в какой строке списка или выделенного диапазона содержатся названия столбцов, необходимые для выполнения этой команды.”, которое можно проигнорировать нажав “Ок”.
После этого откроется меню расширенного фильтра.
В окне расширенного фильтра, для создания списка уникальных значений вам необходимо:
- Переставить переключатель в поле скопировать результаты в другое место (можно не переставлять, но в этом случае ваш старый список будет заменен)
- Проверить, что исходный диапазон выбран правильно – исходный диапазон списка всех телефонов.
- Поставить галочку в поле Только уникальные значения
- В поле Поместить результат в диапазон выбрать начальную ячейку куда вы хотите поместить уникальный список. (на рисунке мы выбрали ячейку B1)
- Нажать ОК
Полученный результат Вы можете посмотреть во вложении ниже.
Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
Загрузка...
