Предположим, вы хотите узнать, сколько уникальных значений существует в диапазоне, который содержит повторяющиеся значения. Например, если столбец содержит:
- числа 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 или получить поддержку в сообществах.
См. также
Фильтр уникальных значений или удаление повторяющихся значений