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

Как выбрать уникальные значения из списка excel

  • автор:

Как выбрать уникальные значения из списка excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Функция УНИК

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше

Функция УНИК возвращает список уникальных значений в списке или диапазоне.

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

Пример использования =УНИК(B2:B11) для возврата уникального списка чисел

Возвращение уникальных имен из списка имен

Применение функции УНИК для сортировки списка имен

=УНИК(массив,[by_col],[exactly_once])

Функция УНИК имеет следующие аргументы:

Диапазон или массив, из которого возвращаются уникальные строки или столбцы

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

Значение ИСТИНА сравнивает столбцы друг с другом и возвращает уникальные столбцы

Значение ЛОЖЬ (или отсутствующее значение) сравнивает строки друг с другом и возвращает уникальные строки

[exactly_once]

Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, встречающиеся в диапазоне или массиве только один раз. Это концепция базы данных УНИК.

Значение ИСТИНА возвращает из диапазона или массива все отдельные строки или столбцы, которые встречаются только один раз

Значение ЛОЖЬ (или отсутствующее значение) возвращает из диапазона или массива все отдельные строки или столбцы

  • Массив может рассматриваться как строка или столбец со значениями либо комбинация строк и столбцов со значениями. В примерах выше массивы для наших формул УНИК являются диапазонами D2:D11 и D2:D17 соответственно.
  • Функция УНИК возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.
  • Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Примеры

В этом примере СОРТ и УНИК используются совместно для возврата уникального списка имен в порядке возрастания.

Использование УНИК с СОРТ для возврата списка имен по возрастанию

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

Использование УНИК с аргументом occurs_once, для которого задано значение true, для возврата списка имен, которые встречаются только один раз.

В этом примере используется амперсанд (&) для сцепления фамилии и имени в полное имя. Обратите внимание, что формула ссылается на весь диапазон имен в массивах A2:A12 и B2:B12. Это позволяет Excel вернуть массив всех имен.

Использование УНИК с несколькими диапазонами для объединения столбцов имени и фамилии в столбец полного имени.

  • Если указать диапазон имен в формате таблицы Excel, формула автоматически обновляется при добавлении или удалении имен.
  • Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИК(B2:B12&» «&A2:A12))

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

Использование УНИК для возврата списка продавцов.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

Фильтрация уникальных значений и удаление повторяющихся — это две тесно связанные друг с другом задачи, поскольку в результате их выполнения отображается список уникальных значений. Однако между этими двумя задачами существует важное различие. При фильтрации уникальных значений повторяющиеся значения временно скрываются, тогда как в ходе удаления повторяющихся значений они удаляются без возможности восстановления. Значение считается повторяющимся, если все значения в одной строке полностью совпадают со значениями в другой. Повторяющиеся значения определяются значением, которое отображается в ячейке, а не тем, которое в ней хранится. Например, если в различных ячейках содержатся одинаковые значения даты в разных форматах («08.12.2010» и «8 дек 2010»), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование, чтобы перед удалением повторяющихся значений убедиться в том, что будет получен ожидаемый результат.

Примечание: Если формулы в ячейках разные, а значения одинаковые, такие значения считаются повторяющимися. Например, если в ячейке A1 содержится формула =2-1, а в ячейке A2 — формула =3-2 и к ячейкам применено одинаковое форматирование, такие значения считаются повторяющимися. Одинаковые значения, к которым применены различные числовые форматы, не считаются повторяющимися. Например, если значение в ячейке A1 имеет формат 1,00, а в ячейке A2 — формат 1, эти значения не являются повторяющимися.

Фильтрация уникальных значений

Кнопка

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

Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.

Дополнительные параметры

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

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

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

Кнопка

  1. Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
  2. На вкладке Данные в разделе Работа с данными нажмите кнопку Удалить дубликаты.

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

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

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

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в группе Стили щелкните Условное форматирование, наведите указатель на пункт Правила выделения ячеек и выберите Повторяющиеся значения.
  3. В диалоговом окне Создать правило форматирования выберите нужные параметры и нажмите кнопку ОК.

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

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

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Создать правило.
  3. В списке Стиль выберите пункт Классический, а затем в списке Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
  4. В списке значения в выбранном диапазоне выберите пункт уникальные или повторяющиеся.
  5. В списке Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.

Изменение правил расширенного условного форматирования

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

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Управление правилами.
  3. Убедитесь, что в списке Показать правила форматирования для выбран соответствующий лист или таблица.
  4. Выберите правило и нажмите кнопку Изменить правило.
  5. Выберите нужные параметры и нажмите кнопку ОК.

Фильтрация уникальных значений

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

Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.

Дополнительные параметры

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

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

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

  1. Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
  2. На вкладке Данные в разделе Сервис нажмите кнопку Удалить дубликаты.

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

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

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

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в разделе Формат щелкните стрелку рядом с элементом Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт Повторяющиеся значения.

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

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

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Создать правило.

Изменение правил расширенного условного форматирования

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

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.

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

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

Фильтр

    Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.

Удаление дубликатов

Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).

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

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

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

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

Фильтрация уникальных значений

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

Фильтр

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

  • Щелкните Фильтровать список на месте.

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

Кнопка

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

Разверните

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

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

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

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

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

Удаление двойников

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. На вкладке Данные щелкните Удалить дубликаты (в группе Инструменты для работы с данными ).

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

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

Проблемы при удалении дубликатов из контурных или промежуточных данных

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

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

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

Быстрое форматирование

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в группе Стиль щелкните небольшую стрелку для параметра Условное форматирование, а затем выберите пункт Правила выделения ячеек и выберите Повторяющиеся значения.

Важно: Вставьте текст оповещения здесь.

Быстрое форматирование

Расширенное форматирование

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

Важно: Вставьте текст оповещения здесь.

Управление правилами

Кнопка

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

    Разверните

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

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

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

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

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

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

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

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

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *