Подсчет количества уникальных значений среди повторяющихся
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Предположим, что требуется определить количество уникальных значений в диапазоне, содержащем повторяющиеся значения. Например, если столбец содержит:
- числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;
- строки «Руслан», «Сергей», «Сергей», «Сергей», будут найдены два уникальных значения — «Руслан» и «Сергей».
Существует несколько способов подсчета количества уникальных значений среди повторяющихся.
Подсчет количества уникальных значений с помощью фильтра
С помощью диалогового окна Расширенный фильтр можно извлечь уникальные значения из столбца данных и вставить их в новое местоположение. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.
- Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице. Убедитесь в том, что диапазон ячеек содержит заголовок столбца.
- На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно. Появится диалоговое окно Расширенный фильтр.
- Установите переключатель скопировать результат в другое место.
- В поле Копировать введите ссылку на ячейку. В противном случае нажмите Свернуть диалоговое окно для временного скрытия диалогового окна, выберите ячейку на листе, а затем нажмите Развернуть диалоговое окно .
- Установите флажок Только уникальные записи и нажмите ОК. Уникальные значения из выделенного диапазона будут скопированы в новое место, начиная с ячейки, указанной в поле Копировать.
- В пустой ячейке под последней ячейкой диапазона введите функцию ЧСТРОК. Используйте диапазон скопированных уникальных значений в качестве аргумента, исключив заголовок столбца. Например, если уникальные значения содержатся в диапазоне B2:B45, введите =ЧСТРОК(B2:B45).
Подсчет количества уникальных значений с помощью функций
Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.
- Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
- Вычислите сумму, используя функцию СУММ.
- Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.
- Узнайте позицию текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.
- Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.
- Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
- Чтобы просмотреть процесс вычисления функции по шагам, выделите ячейку с формулой, а затем на вкладке Формулы в группе Зависимости формул нажмите Вычислить формулу.
Описание функций
- Функция ЧАСТОТА вычисляет частоту появления значений в диапазоне и возвращает вертикальный массив чисел. С помощью функции ЧАСТОТА можно, например, подсчитать количество результатов тестирования, попадающих в определенные интервалы. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
- Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, так как элемент 25 является вторым в диапазоне.
- Функция ДЛСТР возвращает число символов в текстовой строке.
- Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.
- Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как из повторяющихся значений оставить одно в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Фильтрация или удаление повторяющихся значений
Фильтрация уникальных значений и удаление повторяющихся — это две тесно связанные друг с другом задачи, поскольку в результате их выполнения отображается список уникальных значений. Однако между этими двумя задачами существует важное различие. При фильтрации уникальных значений повторяющиеся значения временно скрываются, тогда как в ходе удаления повторяющихся значений они удаляются без возможности восстановления. Значение считается повторяющимся, если все значения в одной строке полностью совпадают со значениями в другой. Повторяющиеся значения определяются значением, которое отображается в ячейке, а не тем, которое в ней хранится. Например, если в различных ячейках содержатся одинаковые значения даты в разных форматах («08.12.2010» и «8 дек 2010»), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование, чтобы перед удалением повторяющихся значений убедиться в том, что будет получен ожидаемый результат.
Примечание: Если формулы в ячейках разные, а значения одинаковые, такие значения считаются повторяющимися. Например, если в ячейке A1 содержится формула =2-1, а в ячейке A2 — формула =3-2 и к ячейкам применено одинаковое форматирование, такие значения считаются повторяющимися. Одинаковые значения, к которым применены различные числовые форматы, не считаются повторяющимися. Например, если значение в ячейке A1 имеет формат 1,00, а в ячейке A2 — формат 1, эти значения не являются повторяющимися.
Фильтрация уникальных значений
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
- Выполните одно из указанных ниже действий.
| Задача | Необходимые действия |
|---|---|
| Фильтрация диапазона ячеек или таблицы на месте | Выделите диапазон ячеек и щелкните Фильтровать список на месте. |
| Копирование результатов фильтрации в другое место | Выделите диапазон ячеек, щелкните Скопировать результат в другое место, а затем в поле Копировать в введите ссылку на ячейку. |
Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.
Дополнительные параметры
Удаление повторяющихся значений
При удалении повторяющихся значений данные удаляются только из выделенного диапазона ячеек или таблицы. Любые другие значения, которые находятся за пределами этого диапазона ячеек или таблицы, не изменяются и не перемещаются. Так как данные удаляются без возможности восстановления, перед удалением повторяющихся записей рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или в другую книгу.
Примечание: Нельзя удалить повторяющиеся значения, если выделенные фрагмент содержит структурированные данные или промежуточные итоги. Перед удалением повторяющихся значений нужно удалить структуру и промежуточные итоги.
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Работа с данными нажмите кнопку Удалить дубликаты.
- Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Удалить дубликаты.
Совет: Если в диапазоне ячеек или таблице содержится много столбцов, а нужно выбрать только несколько из них, снимите флажок Выделить все и выделите только нужные столбцы.
Применение условного форматирования к уникальным или повторяющимся значениям
Для наглядного отображения уникальных или повторяющихся значений к ним можно применить условное форматирование. Например, выделение повторяющихся данных определенным цветом помогает найти и (при необходимости) удалить их.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование, наведите указатель на пункт Правила выделения ячеек и выберите Повторяющиеся значения.
- В диалоговом окне Создать правило форматирования выберите нужные параметры и нажмите кнопку ОК.
Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям
Вы можете создать правило для выделения уникальных или повторяющихся значений на листе определенным цветом. Это особенно полезно, когда в данных содержится несколько наборов повторяющихся значений.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Создать правило.
- В списке Стиль выберите пункт Классический, а затем в списке Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
- В списке значения в выбранном диапазоне выберите пункт уникальные или повторяющиеся.
- В списке Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.
Изменение правил расширенного условного форматирования
Вы можете отредактировать существующее правило, чтобы изменить условное форматирование, применяемое к уникальным или повторяющимся данным.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Управление правилами.
- Убедитесь, что в списке Показать правила форматирования для выбран соответствующий лист или таблица.
- Выберите правило и нажмите кнопку Изменить правило.
- Выберите нужные параметры и нажмите кнопку ОК.
Фильтрация уникальных значений
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Сортировка и фильтр щелкните стрелку рядом с элементом Фильтр и выберите пункт Расширенный фильтр.
- Выполните одно из указанных ниже действий.
| Задача | Необходимые действия |
|---|---|
| Фильтрация диапазона ячеек или таблицы на месте | Выделите диапазон ячеек и щелкните Фильтровать список на месте. |
| Копирование результатов фильтрации в другое место | Выделите диапазон ячеек, щелкните Скопировать результат в другое место, а затем в поле Копировать в введите ссылку на ячейку. |
Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.
Дополнительные параметры
Удаление повторяющихся значений
При удалении повторяющихся значений данные удаляются только из выделенного диапазона ячеек или таблицы. Любые другие значения, которые находятся за пределами этого диапазона ячеек или таблицы, не изменяются и не перемещаются. Так как данные удаляются без возможности восстановления, перед удалением повторяющихся записей рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или в другую книгу.
Примечание: Нельзя удалить повторяющиеся значения, если выделенные фрагмент содержит структурированные данные или промежуточные итоги. Перед удалением повторяющихся значений нужно удалить структуру и промежуточные итоги.
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Сервис нажмите кнопку Удалить дубликаты.
- Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Удалить дубликаты. Появится либо сообщение о том, сколько повторяющихся значений было удалено и сколько уникальных осталось, либо сообщение о том, что ни одного повторяющегося значения не было удалено.
Совет: Если в диапазоне ячеек или таблице содержится много столбцов, а нужно выбрать только несколько из них, снимите флажок Выделить все и выделите только нужные столбцы.
Применение условного форматирования к уникальным или повторяющимся значениям
Для наглядного отображения уникальных или повторяющихся значений к ним можно применить условное форматирование. Например, выделение повторяющихся данных определенным цветом помогает найти и (при необходимости) удалить их.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с элементом Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт Повторяющиеся значения.
- Выберите нужные параметры и нажмите кнопку ОК.
Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям
Вы можете создать правило для выделения уникальных или повторяющихся значений на листе определенным цветом. Это особенно полезно, когда в данных содержится несколько наборов повторяющихся значений.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Создать правило.
- Во всплывающем меню Стиль выберите пункт Классический, а затем во всплывающем меню Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
- В меню значения в выбранном диапазоне выберите уникальные или повторяющиеся.
- В меню Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.
Изменение правил расширенного условного форматирования
Вы можете отредактировать существующее правило, чтобы изменить условное форматирование, применяемое к уникальным или повторяющимся данным.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.
- Убедитесь, что в меню Показать правила форматирования для выбран соответствующий лист или таблица.
- Выберите правило и нажмите кнопку Изменить правило.
- Выберите нужные параметры и нажмите кнопку ОК.
Clear duplicated values in selection (leave one original)
Range › 20. Clear duplicated values in selection (leave one original)
This utility will remove all duplicated values within the selected cells and keep one original.
The first cell containing a duplicated value will keep its value, all other cells with this same value will have their contents cleared.
This way only one of each value will remain.
You can specify if you want this utility to be case sensitive.
If your selection contains formulas then this utility compares the calculated values. This means that this utility considers a cell with the value 12 and a cell with the formula =3*4 as a duplicate.
Screenshots
Example screenshot: 1
Example screenshot: 2
Starting this tool
- Click ASAP Utilities › Range › 20. Clear duplicated values in selection (leave one original)
- Specify a Keyboard Shortcut: ASAP Utilities › Favorites & Shortcut keys › Edit your favorite tools and shortcut keys.
Additional keywords for this tool:
Double, duplicate data, duplicate fields, duplicate cells, undouble, dedupe, deduplicate, undouble list, delete, del, duplication, double entries, the same value, Empty duplicates in selection, clear, remove, doubles, dedupe, deduplicate, undouble