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

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

Источник:
http://topexcel.ru/6-sposobov-sozdat-spisok-unikalnyx-znachenij-v-excel/

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

22 апреля 2013 г.

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

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

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

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

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

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

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите: Исходный_список ;
  • в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$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 EXCE L.

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

Источник:
http://excel2.ru/articles/otbor-unikalnyh-znacheniy-ubiraem-povtory-iz-spiska-v-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 и выводит все позиции списка с номерами в отдельную таблицу:

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

Как в Экселе найти уникальные значения

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

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

Опишу пример из жизни: есть таблица имён для ребёнка, куда записали предложения все члены семьи. Нам нужно убрать повторы из этого перечня:

Алгоритм поиска таков:

  1. Первым уникальным значением будем считать заголовок второй таблицы. Это необходимо, чтобы дальнейшая логика срабатывала
  2. Посчитаем, сколько раз уже найденные уникальные значения встречаются в общем перечне. Получим для каждой строчки массивы с количеством элементов, равным размеру исходного списка. В нашем примере это 9 элементов. Для первой строки результат должен выглядеть так: <0;0;0;0;0;0;0;0;0>. То есть, ни одно из уникальных имён не найден в исходнике
  3. В этом массиве нулями будут помечены те имена, которые еще не были добавлены в список. Добавляем к уникальным то имя, которому соответствует ближайший к началу нолик.
  4. Переходим к следующей строке уникального списка и проделываем всё заново, начиная со второго пункта. При этом, массив уже будет выглядеть так:
  5. Останавливаемся, когда все уникальные имена будут выведены

Надеюсь, принцип ясен. Реализовываем:

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

    Обратите внимание на 2 момента здесь:

    • Интервал $D$1:D1 означает, что первая ячейка $D$1 будет оставаться неизменной, а последняя (D1) – спускаться ниже, когда вы копируете формулу вниз. Таким образом, заданный диапазон всегда будет охватывать все найденные уникальные имена
    • Формула массива СЧЁТЕСЛИ принимает в качестве критерия диапазон, а значит, будет считать количество совпадений уникальных имён с каждой из ячеек интервала-критерия. Таким образом, мы получим массив, о котором говорили выше
  1. Теперь в полученном массиве ищем первое нулевое значение с помощью функции ПОИСКПОЗ:

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

    Скопируем эту формулу в оставшиеся ячейки и получим все уникальные значения. Но видим в последних двух строчках ошибку #Н/Д. Это из-за того, что все уникальные значения найдены, новые уже не находятся.

    Используем функцию перехвата ошибки ЕСНД, чтобы вывести пустую строку («») вместо текста ошибки:

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

    Источник:
    http://officelegko.com/2019/12/24/kak-v-eksele-najti-unikalnye-znacheniya/

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

    В этом курсе:

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

    Чтобы отфильтровать уникальные значения, нажмите кнопку данные > отсортировать & фильтр > Дополнительно.

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

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

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

    Повторяющееся значение — это одно из значений, для которого все значения по крайней мере одной строки идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не на основе значения, хранящегося в ячейке. Например, если у вас есть одинаковое значение даты в разных ячейках, например «3/8/2006», а другое — «Мар 8, 2006», значения будут уникальными.

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

    Выполните указанные ниже действия.

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

    Нажмите кнопку данные > дополнительно (в группе Фильтр сортировки & ).

    В всплывающем окне Расширенный фильтр выполните одно из следующих действий:

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

    Нажмите кнопку Фильтровать список на месте.

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

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

    В поле Копировать в введите ссылку на ячейку.

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

    Установите флажок только уникальные записии нажмите кнопку ОК.

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

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

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

    Выполните указанные ниже действия.

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

    На вкладке данные нажмите кнопку Удалить дубликаты (в группе Работа с данными ).

    Выполните одно или несколько из указанных ниже действий:

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

    Чтобы быстро выделить все столбцы, нажмите кнопку выделить все.

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

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то «Key», использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2. Если в этих столбцах обнаружена повторяющаяся копия, вся строка удаляется, в том числе другие столбцы в таблице или диапазоне.

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

    Чтобы изменить изменения, нажмите кнопку Отменить (или нажмите клавиши CTRL + Z на клавиатуре).

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

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

    Выполните указанные ниже действия.

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

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

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

    Выполните указанные ниже действия.

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

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

    Выполните одно из следующих действий.

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

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

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

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

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

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

    В Excel в Интернете вы можете удалить повторяющиеся значения.

    Удаление повторяющихся значений

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

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

    Выполните указанные ниже действия.

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

    На вкладке данные нажмите кнопку Удалить дубликаты .

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то «Key», использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2. При обнаружении дубликата в Столбец1 и Столбец2 вся строка будет удалена, включая данные из Столбец3.

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

    Примечание: Если вы хотите вернуться к данным, просто нажмите кнопку отменить (или нажмите клавиши CTRL + Z на клавиатуре).

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

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

    Источник:
    http://support.microsoft.com/ru-ru/office/%D1%84%D0%B8%D0%BB%D1%8C%D1%82%D1%80-%D1%83%D0%BD%D0%B8%D0%BA%D0%B0%D0%BB%D1%8C%D0%BD%D1%8B%D1%85-%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D0%B9-%D0%B8%D0%BB%D0%B8-%D1%83%D0%B4%D0%B0%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5-%D0%BF%D0%BE%D0%B2%D1%82%D0%BE%D1%80%D1%8F%D1%8E%D1%89%D0%B8%D1%85%D1%81%D1%8F-%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D0%B9-ccf664b0-81d6-449b-bbe1-8daaec1e83c2

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

    знаете ли вы Способ в Excel «вычислить» по формуле список уникальных значений ?

    например: диапазон, содержащий значения «red» , «blue» , «red» , «green» , «blue» , «black»
    и я хочу иметь в результате «red , «blue» , «green» , «black» + в итоге 2 других пустая ячейка.

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

    20 ответов

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

    я назвал список цветов Colors (A2:A7), и формула массива в камеру C2 это (основные):

    использовать Ctrl+Shift+Enter ввести формулу в C2, и скопируйте C2 вниз к C3: C7.

    1. COUNTIF(Colors,» возвращает массив (#1) с учетом значений, которые меньше, чем каждый элемент в данных <4;1;4;3;1;0>(черный=0 пунктов меньше, синий=1 шт, Красный=4 элемента). Это можно перевести на значение для каждого элемента.
    2. COUNTIF(C:C. Colors) возвращает массив (#2) с 1 для каждого элемента данных, который уже в отсортированном результат. В C2 он возвращается <0;0;0;0;0;0>и в C3 <0;0;0;0;0;1>потому что «черный» является первым в сортировке и последним в данных. В C4 <0;1;0;0;1;1>он указывает на «черный», и все вхождения» синего » уже присутствуют.
    3. на SUM возвращает k-th сортировать значение, подсчитывая все вхождения меньших значений, которые уже присутствуют (сумма массива #2).
    4. MATCH находит первый индекс k-го значения сортировки (индекс в массиве #1).
    5. на IFERROR только скрыть #N/A ошибка в нижних ячейках, когда сортированный уникальный список завершен.

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

    Ок, у меня есть две идеи для вас. Надеюсь, один из них доставит вас туда, куда вам нужно. Обратите внимание, что первый игнорирует просьбу сделать это как формулу, так как это решение не очень. Я решил, что удостоверюсь, что простой способ действительно не сработает для вас;^).

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

    1. выберите список (или поместите свой выбор в любом месте списка и нажмите ok, если появится диалоговое окно с жалобой на то, что Excel не знает, если ваш список содержит заголовки или нет)
    2. Выберите Данные / Расширенный Фильтр
    3. выберите «фильтровать список, на месте» или » копировать в другое место»
    4. выберите «только уникальные записи»
    5. нажмите ok
    6. вы сделали. Уникальный список создается либо на месте, либо в новом месте. Обратите внимание, что вы можете записать это действие, чтобы создать однострочный скрипт VBA для этого, который затем может быть обобщен для работы в других ситуациях для вас (например без ручных шагов, перечисленных выше).

    использование формул (обратите внимание, что я строю на решении Locksfree, чтобы получить список без отверстий)

    Это решение будет работать со следующими оговорками:

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

    вот краткое изложение решения:

    1. для каждого элемента в списке, подсчитать количество повторений выше.
    2. для каждого места в уникальном списке вычислите индекс следующего уникального элемента.
    3. наконец, используйте индексы для создания нового списка только с уникальными предметами.

    и вот пошаговый пример:

    1. открыть новую таблицу
    2. В a1: a6 введите пример, приведенный в исходном вопросе («красный», «синий», «красный», «зеленый», «синий», » черный»)
    3. сортировка списка: поместите выделение в список и выберите команду сортировки.
    4. в столбце B вычислите дубликаты:
      1. В B1 введите » =IF (COUNTIF ($A$1: A1,A1) = 1,0,COUNTIF(A1:$A$6,A1))». Обратите внимание, что » $ » в ссылках на ячейки очень важны, так как это сделает следующий шаг (заполнение остальной части столбца) намного проще. «$»Указывает на абсолютную ссылку, чтобы при копировании/вставке содержимого ячейки ссылка не обновлялась (в отличие от относительной ссылки, которая будет обновляться).
      2. используйте smart copy для заполнения остальной части столбца B: выберите B1. Наведите курсор мыши на черный квадрат в правом нижнем углу выделения. Щелкните и перетащите вниз в нижней части списка (B6). После выпуска формула будет скопирована в B2: B6 с обновленными относительными ссылками.
      3. значение B1: B6 теперь должно быть «0,0,1,0,0,1». Обратите внимание, что записи» 1 » указывают на дубликаты.
    5. в столбце C создайте индекс уникальных элементов:
      1. в C1 введите » =Row ()». Вы действительно просто хотите C1 = 1, но использование Row () означает, что это решение будет работать, даже если список не запускается в строке 1.
      2. В C2 введите «=IF (C1+1
      3. используйте smart copy для заполнения C3: C6.
      4. значение C1:C6 должно быть » 1,2,4,5,7,8″
    6. в столбце D, создайте новый уникальный список:
      1. в D1 введите » =IF (C1
      2. используйте smart copy для заполнения D2: D6.
      3. значения D1: D6 теперь должны быть «черный»,»синий»,»зеленый»,»красный»,»»,»».

    Источник:
    http://askdev.ru/q/poluchenie-unikalnyh-znacheniy-v-excel-s-pomoschyu-formul-tolko-35797/