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

Способ 1. (Используем программу) Ищем в поисковике и загружаем программу

Например, мы имеем много рабочих книг Excel, и мы хотим

Нам в работе иногда не хватает стандартных возможностей Эксель и приходится напрягать

Достаточно часто при заполнении ячейки текстом, возникает необходимость ввести текст

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

Предположим, что у нас есть такая таблица с перечнем соглашений,

В Excel есть одна интересная особенность, а именно возможность вводить

Многие сталкивались с файлами Ексель,  в которых создано огромное количество

Источник: https://excel.su/otsortirovat-unikalnye-znacheniya-v-excel/

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

Я недавно много писал про то, как организовать каскадные взаимозависимые списки. Спору нет — списки полезная и нужная штука. Однако, списки строятся на основе неких справочников. Хорошо, когда эти справочники статичны. Вы единожды занесёте в них информацию, а далее будете только пользоваться.

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

Описание задачи

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

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

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

Два пути

В начале я стал решать эту задачу при помощи формул рабочего листа. Решил и очень обрадовался. Однако, суровая реальность не оставила на этом решении камня на камне. Фундаментальный недостаток — огромное количество формул, которые губят всё, когда у вас громоздкие таблицы (а в реальной жизни они именно такие).

Да, я понимаю, что там есть возможности для оптимизации, что формулы можно написать иначе и считаться они будут, например, в 2 раза быстрее. Но это не спасёт, когда в вашей таблице будет 50 000 строк, а формулы будут пересчитываться 5 минут. Мне абсолютно не легче, если вместо 5 минут, это будет обсчитано за 2. Меня устроит только 1 секунда и меньше.

Есть и другие мелкие неприятности, о которых нет смысла упоминать, так как решение идёт в корзину, даже если всё остальное было бы идеально. У формул одно преимущество — не нужны макросы. Можно рекомендовать этот путь только для небольшого количества данных — до 3000 строк это всё считается довольно шустро и незаметно.

Решение, которое я смело могу вам рекомендовать, основано на сводных таблицах и буквально одной строчке VBA кода.

Файлы примеров

Решение на основе сводной таблицы

Формульное решение

Решение на основе сводной таблицы

1. На листе Data расположены наши исходные данные, оформленные в виде умной таблицы tblData, что очень важно

   

2. На листе PT располагается наша сводная таблица ptCars. У неё отключены вывод Общих итогов и включена сортировка по алфавиту. Сводную таблицу важно строить на основе tblData

   

3. Именованный диапазон CARS_Sorted ссылается на сводную таблицу! Я уже описывал способ формирования ИД при помощи формулы ИНДЕКС.

   

4. ИД CARS_Sorted используется в качестве источника строк для нашего выпадающего списка на листе Result

   

5. То, ради чего всё затевалось.

   

6. А это элементарный VBA код, который мы подвесили на событие Activate рабочего листа Result. Таким образом, каждый раз, когда вы заходите на лист Result, данный макрос рефрешит сводную таблицу, в ней появляются все новые значения, которые тут же фотографирует ИД CARS_Sorted и к моменту, когда вы увидите выпадающий список, он уже будет содержать полный список значений моделей автомобилей.

Преимущества решения

• Высокая скорость работы на массивных данных, так как данные обрабатываются сводной таблицей
• Простота реализации
• Универсальность

Оп!

Источник: http://perfect-excel.ru/publ/excel/razlichnye_instrumenty_excel/izvlekaem_spisok_unikalnykh_znachenij_iz_neobrabotannykh_dannykh/10-1-0-85

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

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

Список уникальных значений возможно создать 6-ю способами:

1. С помощью специальной функции;
2. С помощью расширенного фильтра;
3. С помощью формул;
4. С помощью сводных таблиц;
5. С помощью условного форматирования;
6. С помощью возможностей макроса.

Создать список уникальных значений с помощью специальной функции

     Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».

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

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

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

Создать список уникальных значений с помощью расширенного фильтра

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

Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно».

Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте.      После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок». Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.

    Внимание!Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.

Создать список уникальных значений с помощью формул

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

     Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:

    =ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+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. Выборка уникальных записей формулой

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

1. Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:
2. Первая задача — пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:
3. =ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)
4. В английской версии это будет:
5. =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)

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

Пример выгрузки

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

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

• В Excel 2003 расширенный фильтр находится в разделе Данные | Фильтр | Расширенный фильтр

• В Excel 2007 и 2010 расширенный фильтр находится в меню Данные | Дополнительно

После этого появится предупреждающее окно “Приложению Microsoft Excel не удается определить, в какой строке списка или выделенного диапазона содержатся названия столбцов, необходимые для выполнения этой команды.”, которое можно проигнорировать нажав “Ок”.

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

1. Переставить переключатель в поле скопировать результаты в другое место (можно не переставлять, но в этом случае ваш старый список будет заменен)
2. Проверить, что исходный диапазон выбран правильно – исходный диапазон списка всех телефонов.
3. Поставить галочку в поле Только уникальные значения
4. В поле Поместить результат в диапазон выбрать начальную ячейку куда вы хотите поместить уникальный список. (на рисунке мы выбрали ячейку B1)
5. Нажать ОК

Полученный результат Вы можете посмотреть во вложении ниже.

Пример сформированного списока уникальных значений

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

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

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

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

Формула массива будет выглядеть следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС(Список;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(B$1:B1; Список));СЧЁТЕСЛИ(Список;»

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

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

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

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

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

1. Выделите первый столбец таблицы A1:A19.
2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

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

1. Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
2. На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
3. В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

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

Выборка ячеек из таблицы по условию в Excel:

1. Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
2. Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Готово!

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

Скачать пример выборки из списка с условным форматированием.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel.

Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат.

Источник: http://word-office.ru/kak-sdelat-spisok-unikal-nyh-znacheniy-v-excel.html

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

8660 10.11.2012 Скачать пример

Способ 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. Выборка уникальных записей формулой

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

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

1. Первая задача — пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:
2. =ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)
3. В английской версии это будет:

=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)

Источник: https://www.planetaexcel.ru/techniques/2/103/

Подсчет уникальных значений в Excel — Офис Ассист

Здравствуй уважаемый пользователь!

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

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

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

Список уникальных значений возможно создать 6-ю способами:

1. С помощью специальной функции;
2. С помощью расширенного фильтра;
3. С помощью формул;
4. С помощью сводных таблиц;
5. С помощью условного форматирования;
6. С помощью возможностей макроса.

Создать список уникальных значений с помощью специальной функции

Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».

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

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

Создать список уникальных значений с помощью расширенного фильтра

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

Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте.

После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок».

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

Внимание!Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.

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

• Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:
• =ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)
• Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку.

1. Теперь можно произвести отбор уникальных значений, которые были ранее пронумерованы. Сделать это возможно в любом из соседних столбиков используя функцию ВПР и копируя ее вниз:
2. =ЕСЛИ(МАКС(A1:A100)

Источник: http://officeassist.ru/excel/podschet-unikalnyx-znachenij-v-excel/

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

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

Пример выгрузки

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

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

• В Excel 2003 расширенный фильтр находится в разделе Данные | Фильтр | Расширенный фильтр

• В Excel 2007 и 2010 расширенный фильтр находится в меню Данные | Дополнительно

После этого появится предупреждающее окно “Приложению Microsoft Excel не удается определить, в какой строке списка или выделенного диапазона содержатся названия столбцов, необходимые для выполнения этой команды.”, которое можно проигнорировать нажав “Ок”.

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

1. Переставить переключатель в поле скопировать результаты в другое место (можно не переставлять, но в этом случае ваш старый список будет заменен)
2. Проверить, что исходный диапазон выбран правильно – исходный диапазон списка всех телефонов.
3. Поставить галочку в поле Только уникальные значения
4. В поле Поместить результат в диапазон выбрать начальную ячейку куда вы хотите поместить уникальный список. (на рисунке мы выбрали ячейку B1)
5. Нажать ОК

Полученный результат Вы можете посмотреть во вложении ниже.

Пример сформированного списока уникальных значений

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Источник: https://sirexcel.ru/osvaivaem-excel/kak-sdelat-spisok-s-unikalnymi-znacheniyami-v-excel/

VBA Excel. Отбор уникальных значений с помощью Collection

Отбор уникальных значений из списка в VBA Excel с помощью объекта Collection. Выгрузка уникальных элементов в ListBox и ячейки рабочего листа. Скачать файл с примером кода.

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

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

Sub ОтборУникальных()

'Объявляем переменные
'myRange — диапазон ячеек, заполненный исходным списком элементов
'myCell — отдельная ячейка диапазона
'myCollection — коллекция
'myElement — элемент коллекции (должен быть типа «Variant»)
Dim myRange As Range, myCell As Range, myCollection As New Collection, _
myElement As Variant, i As Long

'присваиваем переменной myRange диапазон ячеек с исходным списком элементов
Set myRange = Range(«A1:A20»)

'заполняем новую коллекцию уникальными элементами
On Error Resume Next
For Each myCell In myRange
myCollection.Add CStr(myCell.Value), CStr(myCell.Value)
Next myCell
On Error GoTo 0

На этом отбор уникальных значений завершен. Коллекция заполнена уникальными элементами.

Добавление уникальных элементов в ListBox

Теперь можно добавить уникальные значения в ListBox, если перед этим создать форму UserForm1 и на нее добавить элемент управления ListBox1:

For Each myElement In myCollection
UserForm1.ListBox1.AddItem myElement
Next myElement

ListBox заполнен уникальными значениями из коллекции. Другие способы заполнения ListBox и ComboBox смотрите здесь.

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

А так можно добавить уникальные элементы в ячейки столбца «В» активного рабочего листа:

For Each myElement In myCollection
i = i + 1
Cells(i, 2) = myElement
Next myElement

'при необходимости сортируем полученный список в столбце «В»
Range(Cells(1, 2), Cells(i, 2)).Sort Key1:=Range(«B1»), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'а также можно отобразить количество найденных уникальных элементов,
'если, конечно, на форму UserForm1 добавлен элемент управления Label1
UserForm1.Label1.Caption = _
«Уникальных элементов: » & myCollection.Count

'отображаем форму
UserForm1.Show

End Sub

• Если вам необходимо в ListBox или ComboBox загрузить отсортированный список, его элементы можно добавить с листа Excel после сортировки, в данном примере из диапазона Range(Cells(1, 2), Cells(i, 2)).
• Обратите внимание, что в представленном коде VBA Excel для отбора уникальных значений из списка, выгрузки их в ListBox и записи на рабочий лист идет сплошная нумерация от Sub ОтборУникальных() и до End Sub.
• Для наглядного ознакомления с работой представленного кода вы можете скачать демонстрационный файл.

Смотрите, как удалить повторяющиеся значения из диапазона ячеек в VBA Excel с помощью метода Range.RemoveDuplicates и отобрать уникальные значения из списка с помощью объекта Dictionary.

Источник: https://vremya-ne-zhdet.ru/vba-excel/otbor-unikalnykh-znacheniy-iz-spiska/

Вопрос по excel, excel-formula, excel-2010 – Игнорировать дубликаты и создать новый список уникальных значений в Excel

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

Column A Column B
a a
a b
b c
c
c

Этот столбец B действительно должен появиться на другом листе, в той же книге, поэтому я предполагаю, что он должен работать сsheet2!A1 формат стиля.

Мне не повезло с опциями меню Data / Filter, так как кажется, что это работает только по команде. Мне нужно, чтобы столбец B обновлялся автоматически всякий раз, когда новое значение вводится в столбец A.

=SORT(UNIQUE(A:A))

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

Вот.

В основном что-то похожее на:

=INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0))
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngDest As Range

If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

Set rngDest = ThisWorkbook.Sheets(«Sheet2»).Range(«A1»)

Me.Range(Me.Range(«A2»), Me.Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=rngDest, Unique:=True

End If

End Sub

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

var CatalogURL = getweb(currenturl)
+»/_api/web/lists/getbytitle('Site%20Inventory%20and%20Assets')/items?$select=Expense_x0020_Type&$orderby=Expense_x0020_Type»;

/* the column that is replicated, is ordered by */

OData.read(CatalogURL,
function(data,request){

var myhtml =»»;
var myValue =»»;

for(var i = 0; i < data.results.length; i++) { myValue = data.results[i].Expense_x0020_Type; if(i == 0) { myhtml += ""+myValue+""; } else if(myValue != data.results[i-1].Expense_x0020_Type) { myhtml += ""+myValue+""; } else { } } $("#mySelect1").append(myhtml); }); Сортировать значения в столбце A A-> ZВыберите столбец B

Пока столбец B все еще выбран, в поле ввода формулы введите

=IF(TRIM(A1)=TRIM(A2),»»,TRIM(A1))

Пока столбец B все еще выбран, выберите «Правка» -> «Заливка» -> «Вниз» (в более новых версиях просто выберите ячейку B1 и потяните вниз внешнее поле, чтобы полностью развернуть столбец)

Запись: если столбец B находится на другом листе, вы можете использовать Sheet1! A1 и Sheet1! A2.

найтиВот упомянутая выше формула с контролем ошибок

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),»»)

где: (B2: B9 — данные столбца, из которого вы хотите извлечь уникальные значения, D1 — ячейка выше, где находится ваша формула)

A8, в столбце B я хочу извлечь отдельный список имен цветов.

Выполните следующие шаги:

Выберите ячейку B2; написать формулу, чтобы получить уникальные значения из списка.=IF(COUNTIF(A$2:A2,A2)=1,A2,””)НажмитеВойти на вашей клавиатуре.Функция вернет название первого цвета.Чтобы вернуть значение для остальных ячеек, скопируйте ту же формулу вниз.

Чтобы скопировать формулу в диапазоне B3: B8, скопируйте формулу в ячейку B2, нажав клавишуCTRL + C на клавиатуре и вставьте в диапазоне B3: B8, нажав клавишуCTRL + V.Здесь вы можете увидеть вывод, где у нас есть уникальный список названий цветов.

Наотсортированный столбецВы также можете попробовать эту идею:

B2=A2
B3=IFERROR(INDEX(A:A,MATCH(B2,A:A,1)+1),»»)

B3 можно приклеить. Это приведет к 0, после последнего уникального матча. Если это нежелательно, поместите какое-нибудь утверждение IF, чтобы исключить это.

Редактировать:

Проще, чем оператор IF, по крайней мере для текстовых значений:

B3=IFERROR(T(INDEX(A:A,MATCH(B2,A:A,1)+1)),»»)

Перейти на вкладку «Данные». Выберите «Дополнительно» в разделе «Сортировка и фильтр». В действиях выберите: копировать в другое место, если хотите новый список, — Копировать в любое место. И самое главное — проверить: только уникальные записи.

В основном формула, которая вам нужна:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Здесь нужно помнить две важные вещи: полный список находится в ячейкахA2:A20, тогда эта формула должна быть вставлена в ячейкуB2 (НеB1 как это даст вам круговую ссылку). Во-вторых, это формула массива, поэтому вам нужно нажатьCtrl+сдвиг+войти или это не будет работать правильно.

Я или от Я до А, затем вы можете просто использовать одну простую формулу, как указано ниже:

=IF(A2=A3, «Duplicate», «Not Duplicate»)

Приведенная выше формула гласит, что если данные столбца A2 (A — это столбец, а 2 — номер строки) аналогичны A3 (A — это столбец, а 3 — номер строки), то будет напечатано Duplicate, иначе будет напечатано Not Duplicate.

Давайте рассмотрим пример. Столбец A состоит из адреса электронной почты, в котором некоторые дубликаты, поэтому в столбце 2 я использовал приведенную выше формулу, которая в результатах отображала мне 2 дубликата ячейки, одна из которых представляет собой строку 2 и строку 6.

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

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

• Изменить: Вот краткий обзор, как это сделать в TextFX:
• Плагины -> Диспетчер плагинов -> Показать диспетчер плагинов -> вкладка «Доступно» -> TextFX -> Установить
• После того, как TextFX установлен в NotePad ++, вы выбираете весь текст, из которого вы хотите удалить дубликаты, и затем проверяете: TextFX -> Инструменты TextFX -> Сортировать выходные данные только УНИКАЛЬНЫЕ строки
• Затем нажмите «сортировать строки с учетом регистра» или «сортировать строки с учетом регистра», и он выполнит уникальную сортировку.

B2 = ИНДЕКС ($ A $ 2: $ A $ 20, МАТЧ (0, COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 20), 0))

потому что было много строк (10000). Таким образом, я сделал по-другому, что я покажу ниже.

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

Скопировано с веб-сайта Microsoft Office:

Select all the rows, including the column headers, in the list

Вы хотите фильтровать.

Нажмите верхнюю левую ячейку диапазона и перетащите в нижнюю правую ячейку.

On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.

Отфильтрованный список отображается, а повторяющиеся строки скрыты.

On the Edit menu, click Office Clipboard.

Отобразится область задач буфера обмена.

Make sure the filtered list is still selected, and then click Copy Copy button.

On the Data menu, point to Filter, and then click Show All.

Исходный список отображается повторно.

Press the DELETE key.

Исходный список удален.

In the Clipboard, click on the filtered list item.

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

Источник: веб-сайт Microsoft Office (ссылка удалена, причина смерти)

Источник: https://vike.io/ru/172638/