Как посчитать количество уникальных значений в столбце excel
Перейти к содержимому

Как посчитать количество уникальных значений в столбце excel

  • автор:

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

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 2007 Excel для Mac 2011 Еще. Меньше

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

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

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

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

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

  1. Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице. Убедитесь в том, что диапазон ячеек содержит заголовок столбца.
  2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно. Появится диалоговое окно Расширенный фильтр.
  3. Установите переключатель скопировать результат в другое место.
  4. В поле Копировать введите ссылку на ячейку. В противном случае нажмите Свернуть диалоговое окно

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

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

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

  • Назначьте значение 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

Про подсчет уникальных текстовых и числовых значений (без условий) можно прочитать в статье Подсчет Уникальных ТЕКСТовых значений в MS EXCEL и Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL . В этой статье рассмотрим более сложные варианты с условиями.

Задача1

Пусть имеется таблица с перечнем продаж по продавцам.

Требуется подсчитать сколько различных товаров продал конкретный продавец. Например, Вася продал 1 товар1, 2 Товара2 и 1 Товар4 (выделено зеленым). Всего 3 разных товара.

Это можно подсчитать формулой =СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21)) , которая будет работать только с версии MS EXCEL 2007 из-за функции СЧЁТЕСЛИМН() .

Изменив в ячейке А7 имя продавца (в файле примера для удобства сделан выпадающий список ), формула пересчитает количество уникальных.

Задача2

Аналогичным образом можно решить задачу с двумя условиями.

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

В файле примера на листе Два критерия приведено решение этой задачи с помощью Сводной таблицы . В этом случае выводится не количество уникальных, а список уникальных товаров.

Задача3

Теперь рассмотрим другую таблицу (столбцы А:С на рисунке ниже).

Необходимо вывести количество заключенных договоров в каждом месяце.

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

Создадим дополнительный столбец для определения месяца заключения договора (см. статью Название месяца прописью в MS EXCEL ). Выведем из этого столбца только уникальные месяцы (см. статью Отбор уникальных значений (убираем повторы из списка) в MS EXCEL ) и поместим их в столбец F.

И, наконец, помощью формулы =СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26)) подсчитаем количество уникальных договоров в соответствующем месяце.

Решение также возможно с помощью Сводной таблицы.

Методы выполнения подсчетов на листе

Подсчет является неотъемлемой частью анализа данных, будь то определение численности сотрудников отдела в организации или количества единиц, проданных поквартально. В Excel есть несколько методов для подсчета ячеек, строк или столбцов данных. Чтобы помочь вам сделать оптимальный выбор, в этой статье приводится исчерпывающая сводка методов, скачиваемая книга с интерактивными примерами и ссылки на связанные темы для дальнейшего понимания.

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

Скачивание примеров

Вы можете скачать пример книги, которая содержит примеры, дополняющие сведения, приведенные в этой статье. В большинстве разделов этой статьи будет содержаться ссылка на соответствующий лист в примере книги, который содержит примеры и дополнительные сведения.

В этой статье

  • Простой подсчет
    • Использование автосуммирования
    • Добавление строки промежуточных итогов
    • Подсчет ячеек в списке или столбце таблицы Excel с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
    • Видео: использование функций СЧЁТ, СЧЁТЕСЛИ и СЧЁТЗ
    • Подсчет ячеек в диапазоне с помощью функции СЧЁТ
    • Подсчет ячеек в диапазоне на основе одного условия с помощью функции СЧЁТЕСЛИ
    • Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции БСЧЁТ
    • Подсчет ячеек в диапазоне на основе нескольких условий с помощью функции COUNTIFS
    • Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
    • Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
    • Подсчет ячеек в столбце или строке в сводной таблице
    • Подсчет непустых ячеек в диапазоне с помощью функции СЧЁТ
    • Подсчет непустых ячеек в списке с определенными условиями с помощью функции DCOUNTA
    • Подсчет пустых ячеек в смежном диапазоне с помощью функции СЧИТАТЬПУСТОТЫ
    • Подсчет пустых ячеек в несмежном диапазоне с помощью сочетания функций СУММ и ЕСЛИ
    • Подсчет количества уникальных значений в столбце списка с помощью расширенного фильтра
    • Подсчитайте количество уникальных значений в диапазоне, удовлетворяющих одному или нескольким условиям, с помощью функций IF, SUM, FREQUENCY, MATCH и LEN
    • Подсчет общего количества ячеек в диапазоне с помощью функций ЧСТРОК и ЧИСЛСТОЛБ
    • Подсчет слов в диапазоне с помощью сочетания функций SUM, IF, LEN, TRIM и SUBSTITUTE

    Простой подсчет

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

    Excel также может отображать количество выбранных ячеек в excel строка состояния. Ознакомьтесь с видеодемограммой ниже, чтобы быстро ознакомиться с использованием строки состояния. Кроме того, дополнительные сведения см. в разделе Отображение вычислений и подсчетов в строке состояния . Вы можете ссылаться на значения, отображаемые в строке состояния, если вы хотите быстро взглянуть на данные и у вас нет времени для ввода формул.

    Видео: подсчет ячеек с помощью строки состояния Excel

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

    Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

    Использование автосуммирования

    Используйте автосумму , выбрав диапазон ячеек, содержащий по крайней мере одно числовое значение. Затем на вкладке Формулы щелкните Автосумма > Число чисел.

    Количество чисел

    Excel возвращает число числовых значений в диапазоне в ячейке, примыкающей к выбранному диапазону. Как правило, этот результат отображается в ячейке справа для горизонтального диапазона или в ячейке ниже для вертикального диапазона.

    Добавление строки промежуточных итогов

    Вы можете добавить строку промежуточных итогов в данные Excel. Щелкните в любом месте данных, а затем выберите Данные > Промежуточный итог.

    Примечание: Параметр Промежуточные итоги будет работать только с обычными данными Excel, но не для таблиц Excel, сводных таблиц или сводных диаграмм.

    Щелкните Промежуточный итог на вкладке Данные, чтобы добавить строку промежуточных итогов в данные Excel.

    Кроме того, ознакомьтесь со следующими статьями:

    • Структурирование (группировка) данных на листе
    • Вставка промежуточных итогов в списке данных на листе

    Подсчет ячеек в списке или столбце таблицы Excel с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ

    Функция ПРОМЕЖУТОЧНЫЙ ИТОГ используется для подсчета количества значений в таблице Excel или диапазоне ячеек. Если таблица или диапазон содержит скрытые ячейки, можно использовать промежуточные итоги, чтобы включить или исключить эти скрытые ячейки, и это самая большая разница между функциями СУММ и ПРОМЕЖУТОЧНЫХ ИТОГОВ.

    Синтаксис промежуточных итогов выглядит следующим образом:

    Пример промежуточных итогов

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

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

    Подсчет на основе одного или нескольких условий

    С помощью ряда функций можно подсчитать количество ячеек в диапазоне, удовлетворяющих заданным условиям (критериям).

    Видео: использование функций СЧЁТ, СЧЁТЕСЛИ и СЧЁТЗ

    В видеоролике ниже показано, как использовать функцию СЧЁТ, а также функции СЧЁТЕСЛИ и СЧЁТЗ для подсчета только тех ячеек, которые удовлетворяют заданным условиям.

    Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

    Подсчет ячеек в диапазоне с помощью функции СЧЁТ

    Чтобы подсчитать количество числовых значений в диапазоне, используйте в формуле функцию СЧЁТ.

    Пример функции COUNT

    В приведенном выше примере A2, A3 и A6 являются единственными ячейками, содержащими числовые значения в диапазоне, поэтому выходные данные — 3.

    Примечание: A7 является значением времени, но он содержит текст (a.m.), поэтому COUNT не считает его числовым значением. Если бы вы должны были удалить a.m. из ячейки COUNT будет рассматривать A7 как числовое значение и изменять выходные данные на 4.

    Подсчет ячеек в диапазоне на основе одного условия с помощью функции СЧЁТЕСЛИ

    Используйте функцию СЧЁТЕСЛИ , чтобы подсчитать, сколько раз определенное значение отображается в диапазоне ячеек.

    Примеры с функцией СЧЁТЕСЛИ

    Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции БСЧЁТ

    Функция DCOUNT подсчитывает ячейки, содержащие числа в поле (столбце) записей в списке или базе данных, которые соответствуют указанным условиям.

    В следующем примере вы хотите найти количество месяцев, включая или позже марта 2016 года, в которых было продано более 400 единиц. Первая таблица на листе от A1 до B7 содержит данные о продажах.

    Пример данных для DCOUNT

    DCOUNT использует условия, чтобы определить, откуда должны возвращаться значения. Условия обычно вводятся в ячейки на самом листе, а затем вы ссылаетесь на эти ячейки в аргументе условия . В этом примере ячейки A10 и B10 содержат два условия: одно из которых указывает, что возвращаемое значение должно быть больше 400, а другое, указывающее, что конечный месяц должен быть равен или больше 31 марта 2016 г.

    Следует использовать следующий синтаксис:

    =DCOUNT(A1:B7;»Окончание месяца»,A9:B10)

    DCOUNT проверяет данные в диапазоне от A1 до B7, применяет условия, указанные в A10 и B10, и возвращает 2, общее количество строк, удовлетворяющих обоим условиям (строки 5 и 7).

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

    Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.

    Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:

    СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

    Пример с функцией СЧЁТЕСЛИМН

    Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ

    Предположим, что вам нужно определить, сколько продавцов продало определенный товар в определенном регионе, или вы хотите узнать, сколько продаж за определенное значение было выполнено определенным продавцом. Функции IF и COUNT можно использовать вместе; то есть сначала используется функция IF для проверки условия, а затем, только если результат функции IF имеет значение True, для подсчета ячеек используется функция СЧЁТ.

      Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если эта книга открыта в приложении Excel для Windows или Excel 2016 для Mac и вы хотите изменить формулу или создать похожую, нажмите клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте

    Примеры вложенных функций СЧЁТ и ЕСЛИ

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

    В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.

    Пример 1. Функции СУММ и ЕСЛИ, вложенные в формулу

    Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4.

    Пример 2. Функции СУММ и ЕСЛИ, вложенные в формулу

    Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.

    Пример 3. Функции СУММ и ЕСЛИ, вложенные в формулу

    Приведенная выше функция говорит, что если D2:D7 имеет счета для Бьюкенен менее чем за 9000 долл. США, сумма должна отобразить сумму записей, в которой выполняется условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.

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

    Дополнительные советы см. в следующих статьях базы знаний:

    • Использование функции СУММ(ЕСЛИ()) в качестве функции массива вместо функции СЧЁТЕСЛИ() с критериями И и ИЛИ в Excel
    • Использование частоты вхождения числа или текста в диапазоне в Excel

    Подсчет ячеек в столбце или строке в сводной таблице

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

    Чтобы быстро создать сводную таблицу, выделите любую ячейку в диапазоне данных или таблице Excel, а затем на вкладке Вставка в группе Таблицы щелкните Сводная таблица.

    Пример сводной таблицы, к котором показано, как поля связаны со списком полей.

    Рассмотрим пример электронной таблицы «Продажи», в которой можно подсчитать количество значений продаж для разделов «Гольф» и «Теннис» за конкретные кварталы.

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

    Пример данных для сводной таблицы

    1. Введите данные в электронную таблицу Excel.

    Поля сводной таблицы

  • Выделите диапазон A2:C8
  • Выберите Вставка >Сводная таблица.
  • В диалоговом окне «Создание сводной таблицы» установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК. Пустая сводная таблица будет создана на новом листе.
  • В области «Поля сводной таблицы» выполните одно из указанных ниже действий.
    1. Перетащите элемент Спорт в область Строки.
    2. Перетащите элемент Квартал в область Столбцы.
    3. Перетащите элемент Продажи в область Значения.
    4. Повторите третье действие. Имя поля Сумма_продаж_2 отобразится и в области «Сводная таблица», и в области «Значения». На этом этапе область «Поля сводной таблицы» будет выглядеть так:

    Диалоговое окно

  • В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
  • В диалоговом окне Параметры поля значений выполните указанные ниже действия.
    1. На вкладке Операция выберите пункт Количество.
    2. В поле Пользовательское имя измените имя на Количество.

    Сводная таблица отобразит количество записей для разделов «Гольф» и «Теннис» за кварталы 3 и 4, а также показатели продаж.

    Сводная таблица

    Подсчет, если данные содержат пустые значения

    С помощью функций можно подсчитать количество ячеек, содержащих данные или являющихся пустыми.

    Подсчет непустых ячеек в диапазоне с помощью функции СЧЁТ

    Функция COUNTA используется для подсчета только ячеек в диапазоне, содержащих значения.

    Иногда при подсчете ячеек удобнее пропускать пустые ячейки, поскольку смысловую нагрузку несут только ячейки со значениями. Например, необходимо подсчитать общее число продавцов, которые совершили продажу (столбец D).

    Пример COUNTA

    ФУНКЦИЯ COUNTA игнорирует пустые значения в D3, D4, D8 и D11 и подсчитывает только ячейки, содержащие значения в столбце D. Функция находит шесть ячеек в столбце D, содержащих значения, и отображает 6 в качестве выходных данных.

    Подсчет непустых ячеек в списке с определенными условиями с помощью функции DCOUNTA

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

    В следующем примере функция DCOUNTA используется для подсчета количества записей в базе данных, содержащейся в диапазоне A1:B7, которые соответствуют условиям, указанным в диапазоне условий A9:B10. Эти условия: значение идентификатора продукта должно быть больше или равно 2000, а значение Ratings должно быть больше или равно 50.

    Пример функции DCOUNTA

    DCOUNTA находит две строки, соответствующие условиям, — строки 2 и 4, и отображает значение 2 в качестве выходных данных.

    Подсчет пустых ячеек в смежном диапазоне с помощью функции СЧИТАТЬПУСТОТЫ

    Функция COUNTBLANK используется для возврата количества пустых ячеек в непрерывном диапазоне (ячейки являются смежными, если все они соединены в непрерывной последовательности). Если ячейка содержит формулу, которая возвращает пустой текст («»), эта ячейка включается в подсчет.

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

    Пример COUNTBLANK

    Примечание: Функция листа COUNTBLANK предоставляет наиболее удобный метод для определения количества пустых ячеек в диапазоне, но она не очень хорошо работает, когда интересующие ячейки находятся в закрытой книге или когда они не образуют смежный диапазон. В статье базы знаний XL: когда следует использовать SUM(IF()) вместо CountBlank() показано, как использовать формулу массива SUM(IF()) в этих случаях.

    Подсчет пустых ячеек в несмежном диапазоне с помощью сочетания функций СУММ и ЕСЛИ

    Используйте сочетание функции СУММ и функции IF . Как правило, это можно сделать с помощью функции IF в формуле массива, чтобы определить, содержит ли каждая ячейка, на которую указывает ссылка, значение, а затем суммировать количество значений FALSE, возвращаемых формулой.

    См. несколько примеров сочетаний функций SUM и IF в предыдущем разделе Подсчет частоты возникновения нескольких текстовых или числовых значений с помощью функций SUM и IF в этом разделе.

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

    Вы можете подсчитать уникальные значения в диапазоне с помощью сводной таблицы, функции СЧЁТЕСЛИ, СУММ и ЕСЛИ или диалогового окна Расширенный фильтр .

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

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

    Чтобы использовать расширенный фильтр, перейдите на вкладку Данные и в группе Сортировка & фильтр нажмите кнопку Дополнительно.

    На рисунке ниже показано, как с помощью расширенного фильтра скопировать только уникальные записи в другое место на листе.

    Расширенный фильтр

    На следующем рисунке столбец E содержит значения, скопированные из диапазона в столбце D.

    Столбец, скопированный из другого расположения

    • При фильтрации значений на месте они не удаляются с листа, просто одна или несколько строк могут быть скрыты. Чтобы снова отобразить эти значения, на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Очистить.
    • Если вам нужно только быстро узнать количество уникальных значений, выделите данные после применения расширенного фильтра (фильтрованные или скопированные данные) и взгляните на строку состояния. Значение Количество, показанное в строке состояния, должно совпадать с количеством уникальных значений.

    Подсчитайте количество уникальных значений в диапазоне, удовлетворяющих одному или нескольким условиям, с помощью функций IF, SUM, FREQUENCY, MATCH и LEN

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

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

    Особые случаи (подсчет всех ячеек, подсчет слов)

    Используя разные сочетания функций, можно подсчитать количество ячеек или количество слов в диапазоне.

    Подсчет общего количества ячеек в диапазоне с помощью функций ЧСТРОК и ЧИСЛСТОЛБ

    Предположим, вам нужно определить размер большого листа, чтобы решить, как выполнять вычисления в книге: автоматически или вручную. Чтобы подсчитать все ячейки в диапазоне, используйте формулу, которая умножает возвращаемые значения с помощью функций ROWS и COLUMNS . Пример см. на следующем рисунке:

    Пример функции ROWS и COLUMNS для подсчета количества ячеек в диапазоне

    Подсчет слов в диапазоне с помощью сочетания функций SUM, IF, LEN, TRIM и SUBSTITUTE

    В формуле массива можно использовать сочетание функций SUM, IF, LEN, TRIM и SUBSTITUTE . В следующем примере показан результат использования вложенной формулы для поиска количества слов в диапазоне из 7 ячеек (3 из которых пусты). Некоторые ячейки содержат начальные или конечные пробелы. Функции TRIM и SUBSTITUTE удаляют эти лишние пробелы перед началом подсчета. См. пример ниже.

    Пример вложенной формулы для подсчета слов

    Теперь для правильной работы приведенной выше формулы необходимо сделать ее формулой массива, в противном случае формула возвращает #VALUE! ошибку #ЗНАЧ!. Для этого щелкните ячейку с формулой, а затем в строке формул нажмите клавиши CTRL+SHIFT+ВВОД. Excel добавляет фигурную скобку в начале и конце формулы, что делает ее формулой массива.

    Отображение вычислений и подсчетов в строке состояния

    При выделении одной или нескольких ячеек информация о данных в них отображается в строке состояния Excel. Например, если на листе выделены четыре ячейки, которые содержат значения 2, 3, текстовую строку (например, «облако») и 4, то в строке состояния могут одновременно отображаться следующие значения: среднее значение, количество выделенных ячеек, количество ячеек с числовыми значениями, минимальное значение, максимальное значение и сумма. Чтобы отобразить или скрыть все или любые из этих значений, щелкните строку состояния правой кнопкой мыши. Эти значения показаны на приведенном ниже рисунке.

    Строка состояния

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Как посчитать количество уникальных значений в колонке Excel?

    Иногда в работе нам нужно посчитать уникальные значения в определенной колонке, однако Excel имеет функции, которые суммируют только количество записей в заданном поле, например функцияCOUNT(). Проблема в том, что один и тот же код товара или клиента может повторяться несколько раз.

    Но есть выход, для решения нашей задачи мы можем совместить стандартные функции Excel. Давайте посмотрим как это сделать.

    Итак, давайте соединим функции SUM() — суммирует, IF() — проверка условия,FREQUENCY() — подсчитывает кол-во значений, попадающих в определенный интервал, LEN() — считает кол-во символов, MATCH() — ищет позицию элемента в массиве:

    1. Вычисление количества уникальных числовых значений

    =SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))

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

    2. Вычисление количества уникальных числовых и текстовых значений (не работает, если есть пустые ячейки)

    =SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))

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

    3. Вычисление количества уникальных значений (универсальная формула)

    =SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»);IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»))>0;1))

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

    Последнюю формулу нужно вводить как формулу массива, т.е. нажать не простоEnter, а Ctrl + Shift + Enter. После этого в строке формул мы увидим, что формула взята в фигурные скобки (<>), это признак того, что введенная формула массива.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *