В этом учебном материале по Excel мы рассмотрим примеры того, как подсчитать ячейки, которые содержат числа больше нуля.
Основная формула
Описание
Чтобы подсчитать положительные числа в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В основной формуле (выше) диапазон
представляет собой диапазон ячеек, содержащих числа. В этом примере активная ячейка содержит эту формулу:
Пояснение
Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, соответствующих заданным условиям. В этом случае условие предоставляется как >0
, что оценивается как «значения больше нуля». Функция возвращает общее количество всех ячеек в диапазоне, соответствующих этому условию. Вы можете легко настроить эту формулу для подсчета ячеек на основе других условий. Например, чтобы подсчитать все ячейки со значением больше или равным 100, используйте эту формулу:
=СЧЁТЕСЛИ(диапазон;«>=100») |
Количество отрицательных и положительных значений в столбце |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Чтобы подсчитать количество ячеек, содержащих положительные или отрицательные числа из диапазона ячеек в Excel, обычно простая функция СЧЁТЕСЛИ может оказать вам услугу.
- Подсчитайте количество ячеек, содержащих положительные значения, с помощью функции СЧЁТЕСЛИ
- Подсчитайте количество ячеек, содержащих отрицательные значения, с помощью функции СЧЁТЕСЛИ
Подсчитайте количество ячеек, содержащих положительные значения, с помощью функции СЧЁТЕСЛИ
Вот простая формула для подсчета положительных чисел в диапазоне ячеек, общий синтаксис:
=COUNTIF(range,»>0″)
- range: Диапазон ячеек, содержащих числа, которые нужно подсчитать;
- “>0”: Указывает критерий больше 0.
Введите или скопируйте приведенную ниже формулу в пустую ячейку и нажмите Enter ключ для получения результата, см. снимок экрана:
=COUNTIF(A2:A13,»>0″)
Tips: Если вам нужно подсчитать, что ячейки содержат как положительные, так и нулевые значения, примените следующую формулу:
=COUNTIF(A2:A13,»>=0″)
Подсчитайте количество ячеек, содержащих отрицательные значения, с помощью функции СЧЁТЕСЛИ
Если вы хотите подсчитать количество отрицательных значений в диапазоне, функция СЧЁТЕСЛИ также может вам помочь, общий синтаксис:
=COUNTIF(range,»<0″)
- range: Диапазон ячеек, содержащих числа, которые нужно подсчитать;
- “<0”: Указывает критерий меньше 0.
Введите или скопируйте следующую формулу в пустую ячейку и нажмите Enter ключ, чтобы получить результат, как показано ниже:
=COUNTIF(A2:A13,»<0″)
Используемая относительная функция:
- COUNTIF:
- Функция СЧЁТЕСЛИ — это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.
Другие статьи:
- Подсчитайте количество ячеек, не равное множеству значений в Excel
- В Excel вы можете легко получить количество ячеек, не равное определенному значению, используя функцию СЧЁТЕСЛИ, но пробовали ли вы когда-нибудь подсчитать количество ячеек, которые не равны множеству значений? Например, я хочу получить общее количество продуктов в столбце A, но исключить конкретные элементы в C4: C6, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.
- Подсчитайте количество ячеек, содержащих нечетные или четные числа
- Как все мы знаем, остаток нечетных чисел равен 1 при делении на 2, а остаток четных чисел равен 0 при делении на 2. В этом уроке я расскажу о том, как получить количество ячеек, содержащих нечетные или четные. числа в Excel.
- Количество ячеек, содержащих числовые или нечисловые значения
- Если у вас есть диапазон данных, который содержит как числовые, так и нечисловые значения, и теперь вы можете подсчитать количество числовых или нечисловых ячеек, как показано на скриншоте ниже. В этой статье я расскажу о некоторых формулах решения этой задачи в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Комментарии (2)
Оценок пока нет. Оцените первым!
Нужно получить следующие значения (всего на листе около 1000 ячеек): 3. Количество ячеек с положительными числами. Как это сделать, не пойму… справку читал, не помогло. |
|
vikttur Пользователь Сообщений: 47199 |
=СЧЁТЕСЛИ(L3:L11;»<0″) |
{quote}{login=vikttur}{date=24.09.2008 09:07}{thema=}{post}=СЧЁТЕСЛИ(L3:L11;»<0″) |
|
Ни разу не встречал такую ошибку. Если можно выложите файл. |
|
Haken Пользователь Сообщений: 495 |
{quote}{login=4орт}{date=24.09.2008 10:08}{thema=Re: }{post}{quote}{login=vikttur}{date=24.09.2008 09:07}{thema=}{post}=СЧЁТЕСЛИ(L3:L11;»<0″) Если нет возможности выделить все значения меньшим количеством блоков, то разбейте их на 2 (или больше при необходимости) одинаковые функции: |
Raptor Пользователь Сообщений: 182 |
Имхо, намного проще создать именованый диапазон — и для формул гут, и глаз радует:) |
{quote}{login=Haken}{date=24.09.2008 11:29}{thema=Re: Re: } У меня все блоки (это то, что разделено «:» ?) состоят из 2х ячеек только, как в приведенном примере. Или если я чего-то не понял, прошу заранее простить, с экселем почти не знаком… |
|
{quote}{login=Raptor}{date=24.09.2008 11:52}{thema=}{post}Имхо, намного проще создать именованый диапазон — и для формул гут, и глаз радует:){/post}{/quote} |
|
Sh_Alex Пользователь Сообщений: 732 |
Функция =счетесли(…) принимает только два аргумента: непрерывный диапазон и условие. Даже если именованным диапазоном сделать несколько отдельно стоящих ячеек, а имя ввести аргументом в функцию, работать не будет. |
{quote}{login=Sh_Alex}{date=24.09.2008 12:11}{thema=Как посчитать все отрицательные и положительные числа в ячейках?}{post}Функция =счетесли(…) принимает только два аргумента: непрерывный диапазон и условие. Даже если именованным диапазоном сделать несколько отдельно стоящих ячеек, а имя ввести аргументом в функцию, работать не будет. О! спасибо, это работает (правда только для первой части задачи). Но нереально вручную сотни значений вписывать в формулу :(… |
|
А что мешает пробежаться макросом по всем ячейкам листа? Или задача ТОЛЬКО формулами? |
|
{quote}{login=Юрий М}{date=24.09.2008 01:53}{thema=}{post}А что мешает пробежаться макросом по всем ячейкам листа? Или задача ТОЛЬКО формулами?{/post}{/quote} |
|
Посмотрите пример. Так подойдёт? |
|
Нашёл у себя ошибку — если попадается пустая строка или столбец — считает неправильно. Как исправить — не знаю. Прошу помощи. |
|
Sh_Alex Пользователь Сообщений: 732 |
{quote}{login=Юрий М}{date=24.09.2008 05:56}{thema=}{post}Нашёл у себя ошибку — если попадается пустая строка или столбец — считает неправильно. Как исправить — не знаю. Прошу помощи.{/post}{/quote} |
Александр, спасибо! |
|
Raptor Пользователь Сообщений: 182 |
Предлагается решение без макросов:) |
Спасибо всем! |
|
vikttur Пользователь Сообщений: 47199 |
{quote}{login=4орт}{date=25.09.2008 08:56}{thema=}{post} без макросов для меня всё-же намного проще…{/post}{/quote} |
{quote}{login=vikttur}{date=25.09.2008 09:11}{thema=Re: }{post}{quote}{login=4орт}{date=25.09.2008 08:56}{thema=}{post} без макросов для меня всё-же намного проще…{/post}{/quote}…Может быть, есть еще закономерности, по которым можно будет соорудить менее трудоемкую формулу?{/post}{/quote} |
|
Raptor Пользователь Сообщений: 182 |
#21 25.09.2008 12:16:56 Если один раз внимательно повыделять мышей нужные диапазоны, удерживая Ctrl, а затем всей этой красоте присвоить имя через менюшку, никаких закономерностей искать не придется:) |
На чтение 9 мин. Просмотров 61.2k.
Содержание
- Определенный текст
- X или Y
- Ошибки
- Пять символов
- Положительные числа
- Отрицательные числа
- Цифры
- Нечетные числа
- Текст
Определенный текст
=СЧЁТЕСЛИ(rng;»*txt*»)
Для подсчета количества ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, TXT представляет собой текст, который должны содержать ячейки, и «*» является подстановочным символом, соответствующим любому количеству символов.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B12;»*a*»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «*a*», который поставляется в качестве критериев. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов», так что эта модель будет считать любую ячейку, которая содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.
Вы можете легко настроить эту формулу, чтобы использовать содержимое другой ячейки для критериев. Например, если A1 содержит текст, который соответствует тому, что вы хотите, используйте следующую формулу:
=СЧЁТЕСЛИ(rng;»*»&a1&»*»)
X или Y
=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«abc»;B5:B12))+ЕЧИСЛО(НАЙТИ(«def»;B5:B12)))>0))
Для подсчета ячеек, содержащих либо одно значение, либо другое, можно либо использовать вспомогательный столбец решений, или более сложные формулы.
Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».
Решение одной формулой
Для решения одной формулойц, вы можете использовать комбинацию СУММПРОИЗВ с ЕЧИСЛО + НАЙТИ. Формула в ячейке E4 будет:
=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«abc»;B5:B12)) + ЕЧИСЛО(НАЙТИ(«def»;B5:B12)))>0))
Эта формула основана на формуле, которая находит текст внутри ячейки:
ЕЧИСЛО(НАЙТИ(«abc»;B5:B12)
При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.
Далее мы складываем эти массивы вместе (+), сложение создаст новый единый массив чисел. Каждое число в этом массиве является результатом сложения истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит следующим образом:
{2; 0; 2; 0; 1; 0; 2}
Нам нужно сложить эти цифры, но мы не хотим, чтобы удвоился счет. Таким образом, мы должны убедиться, что любое значение больше нуля. Чтобы сделать это, мы вернем все значения, которые больше 0, в Истина или Ложь, а затем с помощью двойного отрицания (—) переведем массив в формат 1 и 0.
И, наконец, СУММПРОИЗВ суммирует полученные числа.
Вспомогательный столбец решений
Со вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложная. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (при условии, как «бесконечное множество»). Формула:
=—(СУММ(СЧЁТЕСЛИ(B4;{«*abc*»;»*def*»}))>0)
СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).
Итоговый результат равен 1 или 0 для каждой ячейки. Чтобы получить в общей сложности для всех ячеек в диапазоне, вам нужно просуммировать вспомогательный столбец.
Ошибки
=СУММПРОИЗВ(—ЕОШ(rng))
Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ, завернутую в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, в которых вы хотели бы рассчитывать ошибки.
В примере, активная ячейка содержит следующую формулу:
=СУММПРОИЗВ(—ЕОШ(B5:B9))
СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив , он просто суммирует элементы в массиве.
Функция ЕОШ вычисляется для каждой ячейки в rng. Результатом является массив со значениями истина / ложь:
{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
(—) — Оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:
{1; 0; 1; 0; 0}
СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общую сумму, которая, в данном примере, это число 2.
Примечание: ЕОШ подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы также рассчитывалось # N / A, используйте функцию ЕОШИБКА вместо ЕОШ.
Вы можете также использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). После ввода формула будет выглядеть следующим образом:
{=СУММ(—ЕОШ(B5:B9))}
Пять символов
=СЧЁТЕСЛИ(rng;»?????»)
Для подсчета количества ячеек, содержащих определенное количество символов текста, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, а «?» соответствует любому одному символу.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B10;»?????»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять символов путем сопоставления содержимого каждой ячейки с шаблоном «?????», который поставляется в качестве критерия для СЧЁТЕСЛИ. «?» символ является подстановочным в Excel, что означает «любой одиночный символ», так что эта модель будет считать ячейки, которые содержат любые пять символов. Подсчет ячеек, которые соответствуют этому шаблону возвращает число, в данном примере, это число 3.
Положительные числа
=СЧЁТЕСЛИ(rng;»>0″)
Для подсчета положительных чисел в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, содержащих числа.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B10; «>0»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям. В этом случае критерии поставляются в виде «> 0», которые оцениваются как «значения больше нуля». Общее количество всех ячеек в диапазоне, которые удовлетворяют этому критерию рассчитывается функцией.
Вы можете легко настроить эту формулу для подсчета ячеек на основе других критериев. Например, для подсчета всех ячеек со значением, большим или равным 100, использовать эту формулу:
=СЧЁТЕСЛИ(rng;»>=100″)
Отрицательные числа
=СЧЁТЕСЛИ(rng;»<0″)
Для подсчета количества ячеек, содержащих отрицательные числа в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, содержащих числа.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B10;»<0″)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям. В этом случае критерии поставляются в виде «<0», который оценивается как «значения меньше нуля». Общее количество всех ячеек в диапазоне, которые удовлетворяют этому критерию рассчитывается функцией.
Вы можете легко настроить эту формулу для подсчета клеток на основе других критериев. Например, для подсчета всех ячеек со значением менее -10, используйте следующую формулу:
=СЧЁТЕСЛИ(rng;»<-10″)
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
=СЧЁТЕСЛИ(rng;»<«&А1)
Если в ячейке А1 находится значение «-5», критерии будут «<-5» после конъюнкции.
Цифры
=СЧЁТ(rng)
Для подсчета количества ячеек, которые содержат числа, используйте функцию СЧЁТ. Однако в общем виде формулы (выше) rng представляет собой диапазон ячеек.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТ(B5:B8)
Функция СЧЁТ является полностью автоматической. Она подсчитывает количество ячеек в диапазоне, содержащих числа и рассчитывает результат.
Нечетные числа
=СУММПРОИЗВ(—(ОСТАТ(rng;2)=1))
Для подсчета ячеек, которые содержат только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.
В примере, формула в ячейке E4 является:
=СУММПРОИЗВ(—(ОСТАТ(B5:B10;2)=1))
Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван «rng» в формуле).
Функция СУММПРОИЗВ непосредственно работает с массивами.
Одна вещь, которую вы можете сделать довольно легко с СУММПРОИЗВ это выполнить тест на массив, используя один или несколько критериев, затем подсчитать результаты.
В этом случае, мы проводим тест на нечетное число, который использует функцию ОСТАТ:
ОСТАТ(rng;2)=1
ОСТАТ рассчитывает остаток от деления. В этом случае делитель равен 2, поэтому ОСТАТ рассчитывает остаток 1 для любого нечетного числа, а остаток 0 для четных чисел.
В функции СУММПРОИЗВ, этот тест выполняется в каждой ячейке B5: B10, результат представляет собой массив значений истина / ложь:
{ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА}
После того, как мы присвоили значения истина / ложь числам с помощью двойного отрицания, мы получили:
{0; 1; 1; 1; 0; 1}
СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.
Текст
=СЧЁТЕСЛИ(rng;»*»)
Для подсчета количества ячеек, содержащих текст (т.е. не цифры, не ошибки и не пустые), можете использовать функцию СЧЁТЕСЛИ и подстановочные знаки. В общей форме формулы (выше), rng является диапазон ячеек, а «*» является подстановочным знаком, который соответствует любому количеству символов.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B9;»*»)
СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа «*», который совпадает с любым количеством символов текста.
Несколько замечаний:
- Логические значения истина и ложь не учитываются, как текст
- Числа не подсчитываются «*», если они не будут введены в виде текста
- Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.
Вы можете также использовать СУММПРОИЗВ для подсчета текстовых значений наряду с функцией ЕТЕКСТ так:
=СУММПРОИЗВ(—ЕТЕКСТ(rng))
Двойной дефис принуждает результат ЕТЕКСТ от логического значения ИСТИНА или ЛОЖЬ перейти к 1 и 0. СУММПРОИЗВ затем суммирует эти значения вместе, чтобы получить результат.
Чувствительная к регистру версия
Если вам нужна чувствительная к регистру версия, вы не можете использовать СЧЁТЕСЛИ. Вместо этого вы можете проверить каждую ячейку в диапазоне, используя формулу, основанную на функции НАЙТИ и функции ЕЧИСЛО.
НАЙТИ чувствительна к регистру, и вы должны дать ему диапазон ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:
=СУММПРОИЗВ(—(ЕЧИСЛО(НАЙТИ(text;rng))))
Там, где текст является текстом, который вы ищете, и rng диапазон ячеек, которые вы хотите подсчитать. Там нет необходимости использовать групповые символы, так как НАЙТИ возвратит число, если текст найден в любом месте в ячейке.