Как подсчитать количество положительных чисел в Excel
СЧЁТЕСЛИ – это функция Excel, которая подсчитывает ячейки, соответствующие одному условию. функция используется для подсчета ячеек, содержащих текст, числа или даты. Мы также можем использовать такие операторы, как >, Итак, в сценарии для сегодняшнего блога мне нужно подсчитать, сколько положительных (продажи) и отрицательных (возвраты) чисел есть в моем наборе данных. Вы можете увидеть список значений продаж в моем примере набора данных ниже.
Краткий обзор функции СЧЁТЕСЛИ в Excel.
Синтаксис функции СЧЁТЕСЛИ очень прост.
диапазон – это диапазон ячеек, которые вы хотите подсчитать
критерии это относится к критериям, которые определяют, какие ячейки учитывать
Пример 1. ПОДСЧЁТ ОТРИЦАТЕЛЬНЫХ ЧИСЕЛ
В первом сценарии мне нужно знать, сколько из моих продаж являются отрицательными или возвратами. Поэтому критерий, который мне нужно использовать, — найти те ячейки, которые меньше нуля («Вы можете увидеть обе формулы ниже.
СЧЁТЕСЛИ с типизированным критерием
COUNITF с другой ячейкой, содержащей критерии
Пример 2. ПОДСЧЕТ ПОЛОЖИТЕЛЬНЫХ ЧИСЕЛ
Во втором сценарии подсчитывается количество продаж или положительных чисел в наборе данных. Критерии, как и в первом примере, можно ввести непосредственно в формулу или сохранить в другой ячейке. В этом примере критерии ссылаются конкатенацией. В этом случае мне нужно подсчитать ячейки со значениями больше спасибо («>0»).
Оба варианта формулы приведены ниже.
СЧЁТЕСЛИ положительных чисел
Функция СЧЁТЕСЛИ для положительных чисел
На что следует обратить внимание при использовании функции СЧЁТЕСЛИ в Excel.
Примечания, правила или рекомендации по использованию функции СЧЁТЕСЛИ перечислены ниже.
1, СЧЁТЕСЛИ регистр не учитывается.
2. СЧЁТЕСЛИ поддерживает только одно условие.
3. Текстовые строки в критериях должны быть заключены в двойные кавычки («»), например, «apple», «>32», «ja*»
4. Ссылки на ячейки в критериях не заключаются в кавычки, т. е. “
Итак, вот как подсчитать, сколько ячеек содержит положительные или отрицательные числа. В этом сообщении в блоге я продемонстрировал, как использовать функцию Excel countif для подсчета положительных и отрицательных чисел. Я надеюсь, что это было полезно, и вы сможете применить полученные знания на практике. Наконец, если у вас есть какие-либо вопросы, пожалуйста, не стесняйтесь обращаться за помощью. Спасибо за прочтение!
Еще больше возможностей для чтения в Excel.
Чтобы получить дополнительные советы по Excel и VBA, подпишитесь на мой ежемесячный информационный бюллетень. Я делюсь 3 советы каждый месяц. Получите мою бесплатную электронную книгу, 50 советов по Excel и ознакомьтесь со всеми моими постами в блоге Formula Friday ниже.
Вам нужна помощь с проблемой Excel?.
Наконец, я рад сообщить, что я объединился с Excel Rescue, где вы можете получить помощь с Excel FAST. Почему бы не проверить это?.
Функция СЧЕТЕСЛИ используется для подсчета количества ячеек, соответствующих определенному критерию.
Для подсчета количества отрицательных чисел в диапазоне ячеек можно использовать функцию СЧЁТЕСЛИ.
Как работает эта формула
В приведенном выше примере функция СЧЁТЕСЛИ ищет отрицательное число в диапазоне, заданном именем Testdate, и подсчитывает совпадения.
В этом примере использовалась переменная критерия.
Подсчитать количество ячеек, содержащих положительные числа
Для подсчета количества положительных чисел в диапазоне ячеек можно использовать функцию СЧЁТЕСЛИ.
Как работает эта формула
В приведенном выше примере функция СЧЁТЕСЛИ ищет положительное число в диапазоне, заданном именем Testdate, и подсчитывает совпадения.
В этом примере использовалась критериальная переменная D8.
Подсчитать количество ячеек, содержащих числа больше -60
Как работает эта формула
В приведенном выше примере функция СЧЁТЕСЛИ ищет числа больше -60 в пределах диапазона и подсчитывает совпадения.
Здесь D8 — это переменная критерия, содержащая критерий >-60.
Подсчитать количество ячеек, содержащих положительные числа и ноль
Как работает эта формула
В приведенном выше примере функция СЧЁТЕСЛИ ищет числа, большие или равные 0 в пределах диапазона, и подсчитывает совпадения. Здесь D8 — переменная критерия, содержащая критерий >=0.
Excel предоставляет различные функции, с помощью которых мы можем подсчитывать ячейки на основе различных условий и критериев. Эта статья покажет вам те функции, в которых мы подсчитываем только ячейки, содержащие числа, и их правильное использование.
Загружаемая рабочая тетрадь
5 способов подсчета ячеек с числами в Excel
1. Как подсчитать ячейки с числами с помощью функции COUNT
Давайте предположим, что у вас есть набор данных о некоторых продавцах с их ежемесячным объемом продаж. Там могут быть пустые ячейки, где у продавца нет продаж в этот день. Теперь посчитаем количество продаж в ноябре с помощью функции СЧЁТ.
Синтаксис этой функции такой:
Здесь в параметре функции COUNT мы могли бы передать диапазон ячеек, которые нам нужно вычислить.
За использование таких параметров функции-формулы в нашей книге.
В первом разделе мы выберем номер первой ячейки или место, с которого мы хотим начать подсчет, а другой раздел будет содержать последний номер ячейки или место, где мы хотим остановить подсчет.
Шаг 1. Введите приведенную ниже формулу в ячейку D4 и нажмите клавишу ВВОД.
Шаг 2. Затем в этой ячейке будут показаны все подсчитанные числа.
2. Как подсчитать ячейки, содержащие положительные числа
Иногда нам может понадобиться подсчитать ячейки с положительными числами в Excel. В этом случае мы можем использовать функцию СЧЁТЕСЛИ, и, используя условие положительного числа, мы можем легко узнать положительное число из любого набора данных. Предположим, у нас есть набор данных с некоторыми числами. У нас могут быть как положительные, так и отрицательные числа. Теперь мы вычислим ячейки с положительными числами с помощью функции СЧЁТЕСЛИ.
Синтаксис функции такой:
Таким образом, первая часть параметра нам нужна для выбора диапазона наших ячеек, а вторая часть параметра будет содержать критерий или условие. В нашей книге формула будет такой:
Поскольку мы выбираем ячейки от B4 до ячеек B10, диапазон будет B4:B10, а для условия я использовал «> 0», потому что нам нужно подсчитать все положительные числа.
Шаг 1. Введите приведенную ниже формулу в ячейку C4 и нажмите клавишу ВВОД.
Шаг 2. После нажатия Enter будет подсчитано общее положительное количество ячеек.
3. Как подсчитать ячейки, содержащие отрицательные числа
Для подсчета ячеек с отрицательными числами мы рассмотрим тот же сценарий, что и в методе 2. Вместо подсчета ячеек с положительными числами здесь мы найдем ячейки с отрицательными числами. Для этого формула будет такой:
Теперь давайте рассмотрим способ подсчета ячеек, содержащих числа, в зависимости от условия.Для этого метода мы рассмотрим тот же пример, который использовался в методе 1. Здесь я покажу общее количество продаж, где стоимость продаж превышает 2000 долларов США, с помощью функции СЧЁТЕСЛИ.
Шаг 1. Введите формулу в ячейку D4 и нажмите клавишу ВВОД.
Шаг 2. Общее количество продаж на сумму более 2000 долларов США будет подсчитано в ячейке D4.
5. Как проверять числа и считать числа
В этом методе я сначала покажу, как проверять числа, а затем подсчитывать ячейки, содержащие числа. Для этого давайте представим, что у нас есть список некоторых имен и номеров в столбце. С помощью двух функций СУММПРОИЗВ и ЕЧИСЛО мы проверим числа и посчитаем их.
Для этого мы будем использовать такие функции:
Здесь я использовал 2 функции в Excel. Все их объяснения приведены ниже:
Это логическая функция, которая выдает логический вывод о том, равны ли значения друг другу.
С помощью этой функции мы можем легко выполнять умножение, сложение, вычитание и деление. Просто мы можем добавить диапазоны или массивы с помощью этой функции, передав диапазоны или массивы в параметр.
Шаг 1. Введите приведенную ниже формулу в ячейку D4 и нажмите клавишу ВВОД.
Шаг 2. Затем будут учтены все значения.
Заключение
Это способы подсчета ячеек с числами в Excel. Я показал все методы с соответствующими примерами. Если у вас есть другой способ добиться этого, поделитесь им с нами.
Дополнительная литература
Мд. Абдулла Аль Мурад
Здравствуйте! Добро пожаловать в мой профиль. В настоящее время я работаю и изучаю Microsoft Excel, и здесь я буду публиковать статьи, связанные с этим. Моя последняя образовательная степень — бакалавр наук, а моей программой были компьютерные науки и инженерия Американского международного университета в Бангладеш. Я выпускник компьютерных наук с большим интересом к исследованиям и разработкам. Всегда старайтесь собирать знания из разных источников и старайтесь находить инновационные решения.
Мы можем подсчитывать ячейки, содержащие отрицательное число в указанном диапазоне ячеек, с помощью функции СЧЁТЕСЛИ. Мы пройдем процедуру, выполнив простые шаги, описанные ниже.
Рисунок 1. Функция СЧЕТЕСЛИ для подсчета отрицательных и положительных чисел
Синтаксис
Формула
Настройка данных
- Мы настроим наши данные, введя ПОЛОЖИТЕЛЬНЫЕ и ОТРИЦАТЕЛЬНЫЕ числа в столбец A , озаглавленный RANGE .
- Мы назовем ячейку C3 диапазоном, ячейку D3 — критерием, а ячейку E3 — числом.
- Диапазон – A4:A9, а критерии формулы – подсчет ячеек, в которых числа больше или меньше нуля.
Рисунок 2. Функция СЧЕТЕСЛИ для подсчета отрицательных чисел
Количество ячеек, содержащих отрицательные числа
Мы введем приведенную ниже формулу в ячейку E4.
Рисунок 3. Функция COUNTIF для подсчета отрицательных чисел
Мы нажмем Enter, чтобы получить результат.
Рисунок 4. Функция СЧЕТЕСЛИ для подсчета отрицательных чисел
Количество ячеек, содержащих положительные числа
Это можно сделать, изменив знак меньше на знак больше. Мы введем приведенную ниже формулу в ячейку E5 и нажмем Enter.
Рисунок 5. Функция СЧЕТЕСЛИ для подсчета положительных чисел
Пояснение
Функция СЧЁТЕСЛИ подсчитывает числа, присутствующие в диапазоне, соответствующем заданным критериям. Если указанный критерий меньше нуля, функция СЧЁТЕСЛИ возвращает количество ячеек со значениями меньше нуля.
Мгновенное подключение к эксперту через нашу службу Excelchat
В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.
Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.
Условное форматирование для окрашивания ячеек в красный цвет для отрицательных чисел не работает. Он не считывает число как отрицательное.
нужно подсчитывать совпадения между двумя столбцами, например, если A1 = B1, то считать 1, иначе 0, считать для всего столбца. Мой столбец содержит числа
В рамках функции промежуточного итога можно подсчитывать только уникальные ячейки в пределах этого уровня промежуточного итога. Например, строки содержат как номер заказа, так и номер строки. Я хочу подсчитать количество номеров заказов и количество строк (которые будут отличаться, поскольку некоторые номера заказов будут иметь более одной строки).
Читайте также:
- Межстрочный интервал в фотошопе
- Выберите одно подходящее слово-связку для ответа на вариант теста 2
- Как сделать таблицу в Excel
- Как сделать мультфильм в фотошопе
- Как ухудшить качество слоя в фотошопе
Методы выполнения подсчетов на листе
Подсчет является неотъемлемой частью анализа данных, будь то определение численности сотрудников отдела в организации или количества единиц, проданных поквартально. В Excel есть несколько методов для подсчета ячеек, строк или столбцов данных. Чтобы помочь вам сделать оптимальный выбор, в этой статье приводится исчерпывающая сводка методов, скачиваемая книга с интерактивными примерами и ссылки на связанные темы для дальнейшего понимания.
Примечание: Подсчет не следует путать с суммированием. Дополнительные сведения о суммирования значений в ячейках, столбцах или строках см. в разделе Суммирование способов добавления и подсчета данных Excel.
Скачивание примеров
Вы можете скачать пример книги, которая содержит примеры, дополняющие сведения, приведенные в этой статье. В большинстве разделов этой статьи будет содержаться ссылка на соответствующий лист в примере книги, который содержит примеры и дополнительные сведения.
В этой статье
- Простой подсчет
- Использование автосуммирования
- Добавление строки промежуточных итогов
- Подсчет ячеек в списке или столбце таблицы Excel с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
- Видео: использование функций СЧЁТ, СЧЁТЕСЛИ и СЧЁТЗ
- Подсчет ячеек в диапазоне с помощью функции СЧЁТ
- Подсчет ячеек в диапазоне на основе одного условия с помощью функции СЧЁТЕСЛИ
- Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции БСЧЁТ
- Подсчет ячеек в диапазоне на основе нескольких условий с помощью функции COUNTIFS
- Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
- Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
- Подсчет ячеек в столбце или строке в сводной таблице
- Подсчет непустых ячеек в диапазоне с помощью функции СЧЁТ
- Подсчет непустых ячеек в списке с определенными условиями с помощью функции DCOUNTA
- Подсчет пустых ячеек в смежном диапазоне с помощью функции СЧИТАТЬПУСТОТЫ
- Подсчет пустых ячеек в несмежном диапазоне с помощью сочетания функций СУММ и ЕСЛИ
- Подсчет количества уникальных значений в столбце списка с помощью расширенного фильтра
- Подсчитайте количество уникальных значений в диапазоне, удовлетворяющих одному или нескольким условиям, с помощью функций IF, SUM, FREQUENCY, MATCH и LEN
- Подсчет общего количества ячеек в диапазоне с помощью функций ЧСТРОК и ЧИСЛСТОЛБ
- Подсчет слов в диапазоне с помощью сочетания функций SUM, IF, LEN, TRIM и SUBSTITUTE
Простой подсчет
Подсчитать количество значений в диапазоне можно с помощью простой формулы, кнопки или функции.
Excel также может отображать количество выбранных ячеек в excel строка состояния. Ознакомьтесь с видеодемограммой ниже, чтобы быстро ознакомиться с использованием строки состояния. Кроме того, дополнительные сведения см. в разделе Отображение вычислений и подсчетов в строке состояния . Вы можете ссылаться на значения, отображаемые в строке состояния, если вы хотите быстро взглянуть на данные и у вас нет времени для ввода формул.
Видео: подсчет ячеек с помощью строки состояния Excel
Просмотрите следующее видео, чтобы узнать, как просмотреть счетчик в строке состояния.
Использование автосуммирования
Используйте автосумму , выбрав диапазон ячеек, содержащий по крайней мере одно числовое значение. Затем на вкладке Формулы щелкните Автосумма > Число чисел.
Excel возвращает число числовых значений в диапазоне в ячейке, примыкающей к выбранному диапазону. Как правило, этот результат отображается в ячейке справа для горизонтального диапазона или в ячейке ниже для вертикального диапазона.
Добавление строки промежуточных итогов
Вы можете добавить строку промежуточных итогов в данные Excel. Щелкните в любом месте данных, а затем выберите Данные > Промежуточный итог.
Примечание: Параметр Промежуточные итоги будет работать только с обычными данными Excel, но не для таблиц Excel, сводных таблиц или сводных диаграмм.
Кроме того, ознакомьтесь со следующими статьями:
- Структурирование (группировка) данных на листе
- Вставка промежуточных итогов в списке данных на листе
Подсчет ячеек в списке или столбце таблицы Excel с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ используется для подсчета количества значений в таблице Excel или диапазоне ячеек. Если таблица или диапазон содержит скрытые ячейки, можно использовать промежуточные итоги, чтобы включить или исключить эти скрытые ячейки, и это самая большая разница между функциями СУММ и ПРОМЕЖУТОЧНЫХ ИТОГОВ.
Синтаксис промежуточных итогов выглядит следующим образом:
Чтобы включить скрытые значения в диапазон, присвойте аргументу function_num значение 2.
Чтобы исключить скрытые значения в диапазоне, задайте для аргумента function_num значение 102.
Подсчет на основе одного или нескольких условий
С помощью ряда функций можно подсчитать количество ячеек в диапазоне, удовлетворяющих заданным условиям (критериям).
Видео: использование функций СЧЁТ, СЧЁТЕСЛИ и СЧЁТЗ
В видеоролике ниже показано, как использовать функцию СЧЁТ, а также функции СЧЁТЕСЛИ и СЧЁТЗ для подсчета только тех ячеек, которые удовлетворяют заданным условиям.
Подсчет ячеек в диапазоне с помощью функции СЧЁТ
Чтобы подсчитать количество числовых значений в диапазоне, используйте в формуле функцию СЧЁТ.
В приведенном выше примере A2, A3 и A6 являются единственными ячейками, содержащими числовые значения в диапазоне, поэтому выходные данные — 3.
Примечание: A7 является значением времени, но он содержит текст (a.m.), поэтому COUNT не считает его числовым значением. Если бы вы должны были удалить a.m. из ячейки COUNT будет рассматривать A7 как числовое значение и изменять выходные данные на 4.
Подсчет ячеек в диапазоне на основе одного условия с помощью функции СЧЁТЕСЛИ
Используйте функцию СЧЁТЕСЛИ , чтобы подсчитать, сколько раз определенное значение отображается в диапазоне ячеек.
Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции БСЧЁТ
Функция DCOUNT подсчитывает ячейки, содержащие числа в поле (столбце) записей в списке или базе данных, которые соответствуют указанным условиям.
В следующем примере вы хотите найти количество месяцев, включая или позже марта 2016 года, в которых было продано более 400 единиц. Первая таблица на листе от A1 до B7 содержит данные о продажах.
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 используйте
Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4.
Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.
Приведенная выше функция говорит, что если D2:D7 имеет счета для Бьюкенен менее чем за 9000 долл. США, сумма должна отобразить сумму записей, в которой выполняется условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Важно: Формулы в этом примере должны быть введены как формулы массива. Это означает, что сначала нужно нажать клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД. В более ранних версиях Excel для Mac используйте клавиши
Дополнительные советы см. в следующих статьях базы знаний:
- Использование функции СУММ(ЕСЛИ()) в качестве функции массива вместо функции СЧЁТЕСЛИ() с критериями И и ИЛИ в Excel
- Использование частоты вхождения числа или текста в диапазоне в Excel
Подсчет ячеек в столбце или строке в сводной таблице
Сводная таблица содержит сводку данных и помогает анализировать и детализировать данные, позволяя выбирать категории, по которым требуется просматривать данные.
Чтобы быстро создать сводную таблицу, выделите любую ячейку в диапазоне данных или таблице Excel, а затем на вкладке Вставка в группе Таблицы щелкните Сводная таблица.
Рассмотрим пример электронной таблицы «Продажи», в которой можно подсчитать количество значений продаж для разделов «Гольф» и «Теннис» за конкретные кварталы.
Примечание: Для интерактивного взаимодействия можно выполнить эти действия для примера данных, представленных на листе сводной таблицы в загружаемой книге.
- Введите данные в электронную таблицу Excel.
- Выделите диапазон A2:C8
- Выберите Вставка >Сводная таблица.
- В диалоговом окне «Создание сводной таблицы» установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК. Пустая сводная таблица будет создана на новом листе.
- В области «Поля сводной таблицы» выполните одно из указанных ниже действий.
- Перетащите элемент Спорт в область Строки.
- Перетащите элемент Квартал в область Столбцы.
- Перетащите элемент Продажи в область Значения.
- Повторите третье действие. Имя поля Сумма_продаж_2 отобразится и в области «Сводная таблица», и в области «Значения». На этом этапе область «Поля сводной таблицы» будет выглядеть так:
- В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
- В диалоговом окне Параметры поля значений выполните указанные ниже действия.
- На вкладке Операция выберите пункт Количество.
- В поле Пользовательское имя измените имя на Количество.
Сводная таблица отобразит количество записей для разделов «Гольф» и «Теннис» за кварталы 3 и 4, а также показатели продаж.
Подсчет, если данные содержат пустые значения
С помощью функций можно подсчитать количество ячеек, содержащих данные или являющихся пустыми.
Подсчет непустых ячеек в диапазоне с помощью функции СЧЁТ
Функция COUNTA используется для подсчета только ячеек в диапазоне, содержащих значения.
Иногда при подсчете ячеек удобнее пропускать пустые ячейки, поскольку смысловую нагрузку несут только ячейки со значениями. Например, необходимо подсчитать общее число продавцов, которые совершили продажу (столбец D).
ФУНКЦИЯ COUNTA игнорирует пустые значения в D3, D4, D8 и D11 и подсчитывает только ячейки, содержащие значения в столбце D. Функция находит шесть ячеек в столбце D, содержащих значения, и отображает 6 в качестве выходных данных.
Подсчет непустых ячеек в списке с определенными условиями с помощью функции DCOUNTA
С помощью функции БСЧЁТА можно подсчитать количество непустых ячеек, которые удовлетворяют заданным условиям, в столбце записей в списке или базе данных.
В следующем примере функция DCOUNTA используется для подсчета количества записей в базе данных, содержащейся в диапазоне A1:B7, которые соответствуют условиям, указанным в диапазоне условий A9:B10. Эти условия: значение идентификатора продукта должно быть больше или равно 2000, а значение Ratings должно быть больше или равно 50.
DCOUNTA находит две строки, соответствующие условиям, — строки 2 и 4, и отображает значение 2 в качестве выходных данных.
Подсчет пустых ячеек в смежном диапазоне с помощью функции СЧИТАТЬПУСТОТЫ
Функция COUNTBLANK используется для возврата количества пустых ячеек в непрерывном диапазоне (ячейки являются смежными, если все они соединены в непрерывной последовательности). Если ячейка содержит формулу, которая возвращает пустой текст («»), эта ячейка включается в подсчет.
Иногда требуется включить в подсчет и пустые ячейки. В следующем примере электронной таблицы продуктовых продаж. Предположим, вы хотите узнать, сколько ячеек не имеет упомянутых показателей продаж.
Примечание: Функция листа COUNTBLANK предоставляет наиболее удобный метод для определения количества пустых ячеек в диапазоне, но она не очень хорошо работает, когда интересующие ячейки находятся в закрытой книге или когда они не образуют смежный диапазон. В статье базы знаний XL: когда следует использовать SUM(IF()) вместо CountBlank() показано, как использовать формулу массива SUM(IF()) в этих случаях.
Подсчет пустых ячеек в несмежном диапазоне с помощью сочетания функций СУММ и ЕСЛИ
Используйте сочетание функции СУММ и функции IF . Как правило, это можно сделать с помощью функции IF в формуле массива, чтобы определить, содержит ли каждая ячейка, на которую указывает ссылка, значение, а затем суммировать количество значений FALSE, возвращаемых формулой.
См. несколько примеров сочетаний функций SUM и IF в предыдущем разделе Подсчет частоты возникновения нескольких текстовых или числовых значений с помощью функций SUM и IF в этом разделе.
Подсчет частоты вхождения уникальных значений
Вы можете подсчитать уникальные значения в диапазоне с помощью сводной таблицы, функции СЧЁТЕСЛИ, СУММ и ЕСЛИ или диалогового окна Расширенный фильтр .
Подсчет количества уникальных значений в столбце списка с помощью расширенного фильтра
С помощью диалогового окна Расширенный фильтр можно найти уникальные значения в столбце данных. Эти значения можно отфильтровать на месте или извлечь их и вставить в другое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.
Чтобы использовать расширенный фильтр, перейдите на вкладку Данные и в группе Сортировка & фильтр нажмите кнопку Дополнительно.
На рисунке ниже показано, как с помощью расширенного фильтра скопировать только уникальные записи в другое место на листе.
На следующем рисунке столбец E содержит значения, скопированные из диапазона в столбце D.
- При фильтрации значений на месте они не удаляются с листа, просто одна или несколько строк могут быть скрыты. Чтобы снова отобразить эти значения, на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Очистить.
- Если вам нужно только быстро узнать количество уникальных значений, выделите данные после применения расширенного фильтра (фильтрованные или скопированные данные) и взгляните на строку состояния. Значение Количество, показанное в строке состояния, должно совпадать с количеством уникальных значений.
Подсчитайте количество уникальных значений в диапазоне, удовлетворяющих одному или нескольким условиям, с помощью функций IF, SUM, FREQUENCY, MATCH и LEN
Используйте функции ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР в разных сочетаниях.
Дополнительные сведения и примеры см. в разделе Подсчет количества уникальных значений с помощью функций в статье Подсчет уникальных значений среди дубликатов.
Особые случаи (подсчет всех ячеек, подсчет слов)
Используя разные сочетания функций, можно подсчитать количество ячеек или количество слов в диапазоне.
Подсчет общего количества ячеек в диапазоне с помощью функций ЧСТРОК и ЧИСЛСТОЛБ
Предположим, вам нужно определить размер большого листа, чтобы решить, как выполнять вычисления в книге: автоматически или вручную. Чтобы подсчитать все ячейки в диапазоне, используйте формулу, которая умножает возвращаемые значения с помощью функций 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 как свои пять пальцев на курсе по таблицам от Skillbox
В такой ситуации, формула примет такой вид:
=СУММЕСЛИ(B2:B8;">0";B2:B8)
Мы используем три аргумента:
- Диапазон, который будет проверяться на критерий;
- Критерий;
- Диапазон, который мы суммируем.
Естественно, первый и третий критерии, могут различаться.
Складываем положительные числа больше 100
В прошлом разделе статьи мы сложили только положительные числа, а теперь сложим числа больше 100.
Допустим, у нас есть такая табличка:
Для выполнения такой задачи, формула примет такой вид:
=СУММЕСЛИ(B2:B8;">100";B2:B8)
Проверяем на критерий отличный от суммирования диапазон
Ранее я уже говорил, что диапазон, который мы проверяем на критерий, может отличаться от того, который будем суммировать..
Допустим, у нас есть такая табличка.
Наша задача — сложить только те значения из В столбика, для которых значение из С столбика положительно.
Формула принимает такой вид:
=СУММЕСЛИ(C2:C6;">0";B2:B6)
Формула все та же, только мы указали разные диапазоны в 1 и 3 критерии.
Складываем только отрицательные числа
Тут все точно также, как и в первом разделе статьи, только с одним изменением.
Допустим, у нас есть такая табличка:
Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.
Наша задача — сложить только отрицательные значения.
Формула принимает такой вид:
=СУММЕСЛИ(B2:B8;"<0";B2:B8)
Критерий отделяет только отрицательные числа, а далее они складываются.
Вы можете изменять критерий, непосредственно, под вашу ситуацию без всяких проблем.
Вот и все! Мы рассмотрели как складывать положительные или отрицательные числа с помощью функции СУММЕСЛИ.
Надеюсь, эта статья оказалась полезной для вас!
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Как посчитать количество ячеек содержащих определенное значение в Exel
Для подсчета количества ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, TXT представляет собой текст, который должны содержать ячейки, и «*» является подстановочным символом, соответствующим любому количеству символов.
В примере, активная ячейка содержит следующую формулу:
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «*a*», который поставляется в качестве критериев. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов», так что эта модель будет считать любую ячейку, которая содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.
Вы можете легко настроить эту формулу, чтобы использовать содержимое другой ячейки для критериев. Например, если A1 содержит текст, который соответствует тому, что вы хотите, используйте следующую формулу:
X или Y
Для подсчета ячеек, содержащих либо одно значение, либо другое, можно либо использовать вспомогательный столбец решений, или более сложные формулы.
Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».
Решение одной формулой
Для решения одной формулойц, вы можете использовать комбинацию СУММПРОИЗВ с ЕЧИСЛО + НАЙТИ. Формула в ячейке E4 будет:
Эта формула основана на формуле, которая находит текст внутри ячейки:
При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.
Далее мы складываем эти массивы вместе (+), сложение создаст новый единый массив чисел. Каждое число в этом массиве является результатом сложения истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит следующим образом:
Нам нужно сложить эти цифры, но мы не хотим, чтобы удвоился счет. Таким образом, мы должны убедиться, что любое значение больше нуля. Чтобы сделать это, мы вернем все значения, которые больше 0, в Истина или Ложь, а затем с помощью двойного отрицания (—) переведем массив в формат 1 и 0.
И, наконец, СУММПРОИЗВ суммирует полученные числа.
Вспомогательный столбец решений
Со вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложная. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (при условии, как «бесконечное множество»). Формула:
СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).
Итоговый результат равен 1 или 0 для каждой ячейки. Чтобы получить в общей сложности для всех ячеек в диапазоне, вам нужно просуммировать вспомогательный столбец.
Ошибки
Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ, завернутую в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, в которых вы хотели бы рассчитывать ошибки.
В примере, активная ячейка содержит следующую формулу:
СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив , он просто суммирует элементы в массиве.
Функция ЕОШ вычисляется для каждой ячейки в rng. Результатом является массив со значениями истина / ложь:
(—) — Оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:
СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общую сумму, которая, в данном примере, это число 2.
Примечание: ЕОШ подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы также рассчитывалось # N / A, используйте функцию ЕОШИБКА вместо ЕОШ.
Вы можете также использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). После ввода формула будет выглядеть следующим образом:
Пять символов
Для подсчета количества ячеек, содержащих определенное количество символов текста, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, а «?» соответствует любому одному символу.
В примере, активная ячейка содержит следующую формулу:
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять символов путем сопоставления содержимого каждой ячейки с шаблоном «. », который поставляется в качестве критерия для СЧЁТЕСЛИ. «?» символ является подстановочным в Excel, что означает «любой одиночный символ», так что эта модель будет считать ячейки, которые содержат любые пять символов. Подсчет ячеек, которые соответствуют этому шаблону возвращает число, в данном примере, это число 3.
Положительные числа
Для подсчета положительных чисел в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, содержащих числа.
В примере, активная ячейка содержит следующую формулу:
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям. В этом случае критерии поставляются в виде «> 0», которые оцениваются как «значения больше нуля». Общее количество всех ячеек в диапазоне, которые удовлетворяют этому критерию рассчитывается функцией.
Вы можете легко настроить эту формулу для подсчета ячеек на основе других критериев. Например, для подсчета всех ячеек со значением, большим или равным 100, использовать эту формулу:
Отрицательные числа
Для подсчета количества ячеек, содержащих отрицательные числа в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, содержащих числа.
В примере, активная ячейка содержит следующую формулу:
Вы можете легко настроить эту формулу для подсчета клеток на основе других критериев. Например, для подсчета всех ячеек со значением менее -10, используйте следующую формулу:
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
Если в ячейке А1 находится значение «-5», критерии будут «
Цифры
Для подсчета количества ячеек, которые содержат числа, используйте функцию СЧЁТ. Однако в общем виде формулы (выше) rng представляет собой диапазон ячеек.
В примере, активная ячейка содержит следующую формулу:
Функция СЧЁТ является полностью автоматической. Она подсчитывает количество ячеек в диапазоне, содержащих числа и рассчитывает результат.
Нечетные числа
Для подсчета ячеек, которые содержат только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.
В примере, формула в ячейке E4 является:
Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван «rng» в формуле).
Функция СУММПРОИЗВ непосредственно работает с массивами.
Одна вещь, которую вы можете сделать довольно легко с СУММПРОИЗВ это выполнить тест на массив, используя один или несколько критериев, затем подсчитать результаты.
В этом случае, мы проводим тест на нечетное число, который использует функцию ОСТАТ:
ОСТАТ рассчитывает остаток от деления. В этом случае делитель равен 2, поэтому ОСТАТ рассчитывает остаток 1 для любого нечетного числа, а остаток 0 для четных чисел.
В функции СУММПРОИЗВ, этот тест выполняется в каждой ячейке B5: B10, результат представляет собой массив значений истина / ложь:
После того, как мы присвоили значения истина / ложь числам с помощью двойного отрицания, мы получили:
СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.
Текст
Для подсчета количества ячеек, содержащих текст (т.е. не цифры, не ошибки и не пустые), можете использовать функцию СЧЁТЕСЛИ и подстановочные знаки. В общей форме формулы (выше), rng является диапазон ячеек, а «*» является подстановочным знаком, который соответствует любому количеству символов.
В примере, активная ячейка содержит следующую формулу:
СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа «*», который совпадает с любым количеством символов текста.
- Логические значения истина и ложь не учитываются, как текст
- Числа не подсчитываются «*», если они не будут введены в виде текста
- Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.
Вы можете также использовать СУММПРОИЗВ для подсчета текстовых значений наряду с функцией ЕТЕКСТ так:
Двойной дефис принуждает результат ЕТЕКСТ от логического значения ИСТИНА или ЛОЖЬ перейти к 1 и 0. СУММПРОИЗВ затем суммирует эти значения вместе, чтобы получить результат.
Чувствительная к регистру версия
Если вам нужна чувствительная к регистру версия, вы не можете использовать СЧЁТЕСЛИ. Вместо этого вы можете проверить каждую ячейку в диапазоне, используя формулу, основанную на функции НАЙТИ и функции ЕЧИСЛО.
НАЙТИ чувствительна к регистру, и вы должны дать ему диапазон ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:
Там, где текст является текстом, который вы ищете, и rng диапазон ячеек, которые вы хотите подсчитать. Там нет необходимости использовать групповые символы, так как НАЙТИ возвратит число, если текст найден в любом месте в ячейке.