Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать уникальные значения или числа в столбце в Excel?

Формула для подсчета количества ячеек в Excel по условию

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

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

Ниже приведена таблица показателей ВВП стран с 2000 по 2005 год. Мы хотим выяснить, сколько раз ВВП превышал 500 миллиардов долларов. Выборка данных основана на условии в ячейке F1. Поэтому формула выглядит следующим образом:

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

Использование READ работает более или менее так же, как и использование SUMMESLI. Даже его аргументы заполняются так же. Основное отличие заключается в названии и в значении возвращаемого результата вычисления — значения, удовлетворяющие условиям, подсчитываются количественно, а не суммируются.

Еще одним существенным отличием является отсутствие необязательного третьего аргумента, который имеется в SUMMESLY. Этот аргумент позволяет функции СУММЕСЛИ добавлять значения из другого диапазона ячеек, а не только из диапазона, охватываемого условием по его логическому выражению. Это не имеет смысла в случае функции СУММЕСЛИ, поскольку вычисление значений в другом диапазоне даст тот же результат. Обратите внимание, что функция TOTAL подсчитывает количество непустых ячеек.

Альтернативная формула для условного счета количества ячеек в Excel

Вы также можете использовать функцию SUMMproV для подсчета количества ячеек по условию. Обратите внимание на рисунок и формулу ниже:

формула для условного счета количества.

В результате был произведен аналогичный расчет по другой формуле.

Возможно, вы заметили двойной минус в аргументе функции SUMPROVE. В скобках содержится таблица булевых значений TRUE и FALSE. В Excel эти значения можно использовать для математических операций, поскольку TRUE=1 и FALSE=0. Поставив знак минус перед скобками, мы меняем значения и выполняем арифметические операции над таблицей массива в памяти. Например, -(1)=-1. Благодаря использованию двойного минуса положительные числа не становятся отрицательными, например, -(1)=1.

Читайте так же:
Как подсчитать общее количество кликов в указанной ячейке в Excel?

Каждое результирующее булево значение TRUE изменяется на единицу из-за первого знака минус, но отрицательное число -1 является истинным. В то же время значение FALSE также изменяется на 0. Используя второй знак минус, мы изменяем все отрицательные числа обратно на положительные, а на нулевые значения знак минус не влияет. Таким образом, сложив все положительные числа и нули, мы получим правильный результат вычисления, заданный формулой.

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

Подсчет уникальных текстовых и числовых значений в MS Excel (без условий) описан в статьях Подсчет уникальных текстовых значений в MS Excel и Подсчет уникальных числовых значений в MS Excel. В нашей следующей статье будут рассмотрены более сложные варианты с условиями.

Задача1

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

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

Его можно рассчитать по формуле =SUMPROV((A13:A21=A7)/Calculation(B13:B21;B13:B21;A13:A21;A13:A21)), которая будет работать только в MS EXCEL 2007 из-за наличия функции CONTROLLIMN().

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

Задача2

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

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

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

Задача3

Теперь рассмотрим еще одну таблицу (столбцы A:C на диаграмме ниже).

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

Читайте так же:
Как повторить метки строк для группы в сводной таблице?

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

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

Рассчитайте количество уникальных контрактов с помощью =SUMPROV($A$9:$A$26;$A$9:$A$26)/Count($D$9:$D$26).

Также в сводной таблице приведено решение.

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

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

Значения 5, 6, 7 и 6 встречаются как три уникальных значения: 5, 6 и 7.

Значения "Кирилл", "Сергей", "Сергей", "Сергей", результат — это два уникальных значения — "Кирилл" и "Сергей".

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

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

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

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

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

Читайте так же:
Как показать линию тренда на графике только в Excel?

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

Чтобы скопировать в другое место, щелкните команду Копировать в другое место.

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

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

Нажмите OK, чтобы проверить только уникальные записи.

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

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

Для выполнения этой задачи используйте функции If, Sums, Frequency, SEARCH, DLSTR.

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

Сумма итогов с использованием функции суммы .

SubCount все уникальные значения с помощью функции Frequency. Текстовые или нулевые значения не могут быть переданы в FREQUENCY. Эта функция возвращает количество вхождений определенного значения каждый раз, когда оно встречается в первый раз. Для каждого повторения того же значения, следующего за первым, эта функция возвращает ноль.

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

Используя Len, найдите пустые клетки. Длина пустых клеток равна 0.

В этом примере используются формулы массивов. В Office 365 просто введите формулу динамического массива в верхнюю левую ячейку и нажмите Enter для подтверждения ее использования. Если вы предпочитаете использовать предыдущую версию массива, выберите диапазон вывода, введите формулу в верхнюю левую ячейку и нажмите CTRL+SHIFT+ENTER для подтверждения. В начале и конце формул фигурные скобки автоматически вставляются Excel. Если вы хотите получить дополнительную информацию о формулах массивов, см. раздел Использование формул массивов: Советы и примеры.

Читайте так же:
Как предотвратить дублирование записей в столбце Excel?

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

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

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

В функции TLSTR возвращается количество символов текстовой строки.

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

В результате работы функции If возвращается значение, если заданное условие истинно, и значение, если оно ложно.

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

Вы всегда можете задать вопрос техническому сообществу Excel, попросить помощи в сообществе ответов или предложить новую функцию или усовершенствование на сайте Excel User Voice.

См. также

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

Подсчитать уникальные значения текста в диапазоне ячеек.

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений <"а","b","а","а","а">. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным. Соответственно, подсчитываются остальные значения, т.е. "а" и "b". Ответ очевиден: количество уникальных значений равно 2.

Читайте так же:
Как посчитать количество напечатанных страниц на листе?

Задача

Подсчитайте количество уникальных текстовых значений в диапазоне A7:A15 (см. пример файла). Диапазон может содержать пустые ячейки.

Решение

Запишем формулу =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если диапазон содержит числа в дополнение к текстовым значениям, формула будет считать и их. Числовые значения следует игнорировать, используя формулу =СУММПРОВ(ETEXT(A7:A15)/CALCULATION(A7:A15;A7:A15)).

Если вы постоянно добавляете значения в список, вы можете создать динамически сортируемый Source_List (Список Источников).

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

В файле примера уникальные значения выделены с помощью условного форматирования (см. статью Выделение уникальных значений в MS Excel).

Часто вместо формулы =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют более простую формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст (""), а первая их игнорирует.

Вот пример того, как это может быть важно.

Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;"") определяются товары, которые были проданы в январе. Если товар продан не в январе, то формула возвращает значение Пустой текст. Пользователь решает подсчитать количество уникальных товаров в январе (их всего 3: Товар1, Товар2 и Товар3).

Формула =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4, т.к. в "пустых" ячейках С31:С34 на самом деле содержатся 4 значения "", которые воспринимаются ей как некое текстовое значение, хотя и нулевой длины.

T IP: Как подсчитывать уникальные числовые значения показано в статье Подсчет уникальных цифровых значений.

В статье Counting Unique Numeric Values in MS Excel показано, как вычислять уникальные числовые значения с дополнительными условиями (критериями).

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector