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