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

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

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

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

  • числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;

  • строки "Руслан", "Сергей", "Сергей", "Сергей", будут найдены два уникальных значения — "Руслан" и "Сергей".

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

В этой статье

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

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

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

С помощью диалогового окна Расширенный фильтр можно извлечь уникальные значения из столбца данных и вставить их в новое местоположение. С помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

    Убедитесь в том, что диапазон ячеек содержит заголовок столбца.

  2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

    Появится диалоговое окно Расширенный фильтр.

  3. Установите переключатель скопировать результат в другое место.

  4. В поле Копировать введите ссылку на ячейку.

    Кроме того нажмите кнопку Свернуть диалоговое окно Изображение кнопки временно скрыть диалоговое окно, выберите ячейку на листе и затем нажмите кнопку Развернуть диалоговое окно Изображение кнопки .

  5. Установите флажок Только уникальные записи и нажмите кнопку ОК.

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

  6. В пустую ячейку под последней ячейки в диапазоне введите функцию СТРОК . Используйте диапазон уникальных значений, которые были скопированы в качестве аргумента, за исключением заголовок столбца. Например если диапазон уникальных значений B2:B45, введите =ROWS(B2:B45).

К началу страницы

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

Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.

  • Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

  • Вычислите сумму, используя функцию СУММ.

  • Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.

  • Узнайте номер позиции текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.

  • Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

  1. Создайте пустую книгу или лист.

  2. Выделите пример в разделе справки.

    Примечание: Не выделяйте заголовки строк или столбцов.

    Выделение примера в справке

    Выделение примера в справке

  3. Нажмите клавиши CTRL+C.

  4. Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

  5. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

1

2

3

4

5

6

7

8

9

10

A

B

Данные

Данные

986

Руслан

Дмитрий

563

67

789

235

Руслан

Дмитрий

689

789

Дмитрий

143

56

237

67

235

Формула

Описание (результат)

=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1))

Подсчет количества уникальных числовых значений в диапазоне A2:A10 без учета пустых ячеек и текстовых значений (4)

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1))

Подсчет количества уникальных текстовых и числовых значений в диапазоне B2:B10, который не должен содержать пустые ячейки (7)

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),""),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),""))>0,1))

Подсчет количества уникальных текстовых и числовых значений в диапазоне A2:A10 без учета пустых ячеек и текстовых значений (6)

Примечания    

  • Формулы в этом примере должны быть введены как формулы массива. Выделите все ячейки, содержащие формулу, нажмите клавишу F2, а затем — клавиши CTRL+SHIFT+ВВОД.

  • Чтобы просмотреть процесс вычисления функции по шагам, выделите ячейку с формулой, а затем на вкладке Формулы в группе Зависимости формул нажмите кнопку Вычисление формулы.

Описание функций

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

  • Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25,A1:A3,0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

  • Функция ДЛСТР возвращает число символов в текстовой строке.

  • Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.

  • Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.

К началу страницы

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×