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