Как сделать список уникальных значений в 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)

С ростом богатства растут и заботы. Гораций

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

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

Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний.

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

Читать еще:  Как сделать чтобы в таблице excel складывались строчки?

Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования.

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

Для создания Динамического диапазона:

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
  • в поле Имя введите: Исходный_список;
  • в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
  • нажмите ОК.

Список уникальных значений создадим в столбце B с помощью формулы массива (см. файл примера ). Для этого введите следующую формулу в ячейку B5:

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

Разберем работу формулу подробнее:

  • Здесь использование функции СЧЁТЕСЛИ() не совсем обычно: в качестве критерия (второй аргумент) указано не одно значение, а целый массив Исходный_список , поэтому функция возвращает не одно значение, а целый массив нулей и единиц. Возвращается 0, если значение из исходного списка не найдено в диапазоне B4:B4(B4:B5 и т.д.), и 1 если найдено. Например, в ячейке B5 формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается массив <1:0:0:0:0:0:0:1:0:0:0:0:1:1:0>. Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» (B5). Массив легко увидеть с помощью клавиши F9 (выделите в Строке формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список) , нажмите F9: вместо формулы отобразится ее результат);
  • ПОИСКПОЗ() – возвращает позицию первого нуля в массиве из предыдущего шага. Первый нуль соответствует значению еще не найденному в исходном списке (т.е. значению «ОАО Уважаемая компания» для формулы в ячейке B5);
  • ИНДЕКС() – восстанавливает значение по его позиции в диапазоне Исходный_список ;
  • ЕСЛИОШИБКА() подавляет ошибку, возникающую, когда функция ПОИСКПОЗ() пытается в массиве нулей и единиц, возвращенном СЧЁТЕСЛИ() , найти 0, которого нет (ситуация возникает в ячейке B12, когда все уникальные значения уже извлечены из исходного списка).

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

Примечание. Функция ЕСЛИОШИБКА() будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() . В файле примера имеется лист Для 2003, где эта функция не используется.

Решение для списков с пустыми ячейками

Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ):
=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;
ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0)
);»»)

Решение без формул массива

Для отбора уникальных значений можно обойтись без использования формул массива. Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист «Без CSE» в файле примера ).

СОВЕТ: Список уникальных значений можно создать разными способами, например, с использованием Расширенного фильтра (см. статью Отбор уникальных строк с помощью Расширенного фильтра), Сводных таблиц или через меню Данные/ Работа с данными/ Удалить дубликаты . У каждого способа есть свои преимущества и недостатки. Преимущество использования формул состоит в том, чтобы при добавлении новых значений в исходный список, список уникальных значений автоматически обновлялся.

СОВЕТ2: Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список, необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст «», который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами). Например, см. статью Динамический выпадающий список в MS EXCEL.

Примечание: В статье Восстанавливаем последовательности из списка без повторов в MS 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 и копируем затем вниз до упора следующую формулу:

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

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

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

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

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

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

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

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

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

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

12 ответов

Totero является правильным. Ссылка Также очень полезна.

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

нажмите клавишу ctrl + shift + enter (или он не будет работать с помощью формулы массива).

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

есть хорошее руководство как это сделать здесь.

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

в моем случае Excel был заморожен при использовании формулы

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

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

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

скопировано из Microsoft Office Сайт:

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

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

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

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

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

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

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

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

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

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

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

Edit:

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

чтобы удалить дубликаты из столбца

  1. сортировка значений в столбце A A — >Z
  2. выберите столбец B

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

пока столбец B все еще выбран, выберите Edit — > Fill — > Down (в более новых версиях просто выберите ячейку B1 и потяните вниз внешнее поле, чтобы развернуть весь путь вниз в колонка)

Примечание: если столбец b находится на другом листе, вы можете сделать Лист1!A1 и Sheet1!A2.

в модуле рабочего листа для листа, содержащего список:

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

выполните следующие действия:

  • выберите ячейку B2; напишите формулу, чтобы получить уникальные значения из списка.
  • =IF(COUNTIF(A:A2,A2)=1,A2,””)
  • пресс Enter на клавиатуре.
  • функция вернет имя первого цвета.
  • чтобы вернуть значение для остальных ячеек, скопируйте та же формула. Чтобы скопировать формулу в диапазоне B3: B8, скопируйте формулу в ячейку B2, нажав клавишу CTRL+C на клавиатуре и вставить в диапазоне B3: B8, нажав клавишу CTRL+V .
  • здесь вы можете увидеть выход, где у нас есть уникальный список имен цветов.

поэтому для этой задачи сначала отсортируйте данные по порядку от A до Z или Z до A, затем вы можете просто использовать одну простую формулу, как указано ниже:

в приведенной выше формуле указано, что если данные столбца A2 ( a-столбец и 2-номер строки) аналогичны A3 (A-столбец и 3-Номер строки), то он будет печатать дубликат, иначе будет печатать не дублировать.

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

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

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

Edit: вот краткий обзор того, как это сделать в TextFX:

Плагины -> Диспетчер -> Плагин Показать Плагин Менеджер — > вкладка «доступно» — > TextFX — > установить

после установки TextFX в NotePad++ вы выбираете весь текст, из которого хотите удалить дубликаты, а затем обязательно проверяете: TextFX — > TextFX Tools — > Sort выводит только уникальные строки

затем нажмите «Сортировать строки с учетом регистра» или «сортировать строки без учета регистра», и он выполнит уникальную сортировку.

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

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

все, что вам нужно сделать, это : Перейти на вкладку Данные Выберите дополнительно в сортировке и фильтре В разделе действия выберите: Копировать в другое место, если требуется новый список — Копировать в любое место В списке range выберите список, который вы хотите получить записи . И самое главное-проверить : Только уникальные записи .

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

Ссылка на основную публикацию
Adblock
detector