Как посчитать количество ячеек больше какого то числа эксель
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).
Самая простая функция СЧЁТЕСЛИ означает следующее:
=СЧЁТЕСЛИ(где нужно искать;что нужно найти)
СЧЁТЕСЛИ(диапазон;критерий)
Имя аргумента
диапазон (обязательный)
Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.
критерий (обязательный)
Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".
В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.
Примеры
Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.
Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.
Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.
Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.
Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.
Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.
Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.
Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.
Распространенные неполадки
Возможная причина
Для длинных строк возвращается неправильное значение.
Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.
Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").
Функция должна вернуть значение, но ничего не возвращает.
Аргумент критерий должен быть заключен в кавычки.
Эта ошибка возникает при вычислении ячеек, когда в формуле содержится функция, которая ссылается на ячейки или диапазон в закрытой книге. Для работы этой функции необходимо, чтобы другая книга была открыта.
Рекомендации
Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.
Критерий не чувствителен к регистру. Например, строкам "яблоки" и "ЯБЛОКИ" будут соответствовать одни и те же ячейки.
Использование подстановочных знаков
Подстановочные знаки — вопросительный знак (?) и звездочка (*) — можно использовать в критериях. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.
Например, =СЧЁТЕСЛИ(A2:A5;"яблок?") возвращает все вхождения слова "яблок" с любой буквой в конце.
Убедитесь, что данные не содержат ошибочных символов.
При подсчете текстовых значений убедитесь в том, что данные не содержат начальных или конечных пробелов, недопустимых прямых и изогнутых кавычек или непечатаемых символов. В этих случаях функция СЧЁТЕСЛИ может вернуть непредвиденное значение.
Для удобства используйте именованные диапазоны.
ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =СЧЁТЕСЛИ(> =32")-СЧЁТЕСЛИ(> 85). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.
Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В этом учебном материале по Excel мы рассмотрим примеры того, как подсчитать ячейки между двумя числами.
Основная формула
Описание
Для того, чтобы подсчитать количество ячеек, содержащих значения между двумя числами находящимися в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИМН. В основном виде формулы (выше) диапазон представляет собой диапазон ячеек, содержащих числа. Ячейка A1 представляет нижнюю границу, а B1 представляет верхнюю границу чисел, которые вы хотите подсчитать. В показанном примере формула в G5, ниже, имеет следующий вид:
Пояснение
Цель этого примера - подсчитать числа, попадающие в определенные диапазоны. Нижнее значение берется из столбца "Начало", а верхнее значение - из столбца "Конец". Для каждого диапазона мы хотим включить нижнее значение, но исключить верхнее значение. Для удобства подсчитываемые числа находятся в данных именованного диапазона.
Формула, используемая для решения этой проблемы, основана на функции СЧЁТЕСЛИМН, которая предназначена для подсчета ячеек, удовлетворяющих нескольким условиям.
СЧЁТЕСЛИМН принимает условия как пара диапазон/условие. Первая пара диапазон/условие проверяет значения в данных, которые больше или равны (>=) значению "Начало" в столбце E:
Поскольку мы предоставляем одинаковый диапазон для обоих условий, каждая ячейка в данных должна соответствовать обоим условиям, чтобы быть включенной в окончательный подсчет. Обратите внимание, что в обоих случаях нам нужно объединить ссылку на ячейку с логическим оператором. Это особенность статистических функций в Excel. Поскольку формула копируется в столбец G, она возвращает количество чисел, попадающих в диапазон, определенный столбцами E и F.
Альтернатива СЧЁТЕСЛИ
Если у вас более старая версия Excel, в которой нет функции СЧЁТЕСЛИМН, вы можете использовать функцию СЧЁТЕСЛИ следующим образом:
Первый СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые больше или равны "Начало" (который по определению также включает значения, превышающие значение "Конец", поскольку "Конец" больше, чем "Начало"). Второй СЧЁТЕСЛИ подсчитывает количество ячеек со значениями, большими или равными значению "Конец". Это второе число вычитается из первого числа, что дает окончательный результат - количество ячеек, содержащих значения между "Начало" и "Конец".
Для подсчета количества ячеек, содержащих значения между двумя числами в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИМН. В общей форме формулы диапазон представляет собой диапазон ячеек, содержащих числа, X представляет собой нижнюю границу, а Y представляет собой верхнюю границу цифры, которые вы хотите посчитать.
В примере, активная ячейка содержит следующую формулу:
Функция СЧЁТЕСЛИМН построена для подсчета клеток, которые отвечают нескольким критериям. В этом случае, так как мы подставляем один и тот же диапазон для двух критериев, каждая ячейка в диапазоне должна соответствовать обоим критериям для того, чтобы быть посчитанной.
Если у вас есть старая версия Excel, которая не имеет функцию СЧЁТЕСЛИМН, вы можете использовать функцию СЧЁТЕСЛИ вместо нее, чтобы формула выглядела так:
= СЧЁТЕСЛИ (диапазон; «> = X») — СЧЁТЕСЛИ (диапазон «> Y»)
Первая функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, который больше или равен X (который, по определению, также включает в себя значения больше Y, так как Y больше, чем Х). Вторая функция СЧЁТЕСЛИ подсчитывает количество ячеек со значениями больше, чем Y. Это второе число затем вычитается из первого номера, что дает конечный результат — количество ячеек, содержащих значения между X и Y.
Создание переменной критериев
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
Если значение в ячейке A1 является «5», критерии будут «> 5» после конкатенации.
Между датами
Для подсчета количества ячеек, содержащих даты между двумя датами, вы можете использовать функцию СЧЁТЕСЛИМН. В примере, в ячейке F5, содержит следующую формулу:
Эта формула подсчитывает дни рождения в 2000 году, которые появляются в диапазоне С5: С11.
Функция СЧЁТЕСЛИМН построена для подсчета ячеек, которые отвечают нескольким критериям. В этом случае мы должны создать два критерия: один критерий для более ранней даты и один для более позднего срока. Мы поставляем названные даты диапазона (C5: C11) для обоих критериев.
Для построения каждой даты, мы используем функцию ДАТА:
ДАТА (E5; 1; 1) // формула для первого дня года
ДАТА (E5; 12; 31) // формула для последнего дня года
Функция ДАТА позволяет легко создать даты, задавая аргументы год, месяц и день, которые либо прописываем руками или используем ссылки на ячейки. В примере, месяц и день мы прописали сами, а год получаем год из ячеек столбца E.
Для подсчета количества ячеек, содержащих значения не равных определенному значению, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, а Х представляет собой значение, которое вы не хотите рассчитывать. Все остальные значения будут учитываться.
В примере, активная ячейка содержит следующую формулу:
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям.
В примере, мы используем «<>» (логический оператор «не равно») для подсчета ячеек в диапазоне D5:D11, которые не равны «Готово». СЧЁТЕСЛИ возвращает число в качестве результата.
СЧЁТЕСЛИ не чувствительна к регистру. В этом примере слово «готово» может появиться в любой комбинации прописных / строчных букв.
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
Если значение в ячейке A1 равно «100», критерии будут «<> 100» после конъюнкции, и СЧЁТЕСЛИ будет считать ячейки не равные 100.
Количество ячеек, не равных нескольким критериям
= СЧЁТЗ (диапазон) — СУММПРОИЗВ (СЧЁТЕСЛИ (диапазон; значения))
Для подсчета ячеек, не равных многим критериям (т.е. не равны х, у, z, и т.д.), вы можете использовать формулу, основанную на СЧЁТЕСЛИ, СУММПРОИЗВ и СЧЁТЗ.
В показанном примере, формула в Н5:
Если у вас есть всего лишь пару значений, которые вы не хотите рассчитывать, вы можете использовать функцию СЧЁТЕСЛИМН следующим образом:
= СЧЁТЕСЛИМН (диапазон; «<> яблоко»; диапазон; «<> оранжевый»)
Но это не удобно, когда у вас есть список многих значений, потому что вам придется добавить еще пару диапазонов / критериев, чтобы для каждого значения не рассчитывалось, что вы хотите. Было бы намного проще, создать список и передать ссылку на этот список в качестве части критериев. Это именно то, что делает формула на этой странице.
Эта формула использует именованный диапазон «Критерии» (E5: E7) для хранения значений, которые мы не хотим рассчитывать.
Мы начинаем путем подсчета всех значений в диапазоне с функцией СЧЁТЗ:
Далее, мы генерируем подсчет всех значений, которые мы не хотим считать с помощью СЧЁТЕСЛИ, так:
Поскольку диапазон содержит несколько значений, СЧЁТЕСЛИ будет возвращать несколько результатов. В примере, мы получаем обратно массив значений, как этот:
и мы используем СУММПРОИЗВ, чтобы суммировать все элементы в массиве, получаем 5. Это число затем вычитается из первоначальной общей суммы с получением конечного результата.
Использование СУММПРОИЗВ вместо СУММ избавляет от необходимости использовать формулу массива.
Количество ячеек, не равных х или у
Для подсчета клеток не равных тому или иному, вы можете использовать функцию СЧЁТЕСЛИМН с несколькими критериями.
В приведенном примере есть простой список цветов в столбце B. Есть всего 6 клеток с цветом, некоторые из них являются дубликатами.
Для того, чтобы подсчитать количество ячеек, которые не равны «красный» или «синий», нужна формула в Е5:
В этом примере «rng» именованный диапазон, который равен B5:B10.
Функция СЧЁТЕСЛИМН подсчитывает клетки, которые удовлетворяют одному или нескольким условиям. Все условия должны быть удовлетворены, чтобы подойти для подсчета ячеек.
Ключевым в данном случае является использование оператора «не равно», который пишется <>.
Чтобы добавить еще критерии, нужно просто добавить другой диапазон / критерии пару аргументов.
Альтернатива с СУММПРОИЗВ
Функция СУММПРОИЗВ может также рассчитывать клетки, которые отвечают нескольким условиям.
Как подсчитать количество ячеек больше или меньше 0 (нуля) в Excel?
Если вы хотите подсчитать количество ячеек со значениями больше или меньше нуля в диапазоне, что вы можете сделать? В этой статье мы покажем вам методы подсчета количества ячеек больше или меньше нуля в Excel.
Подсчитайте количество ячеек больше или меньше нуля с формулой
Вы можете подсчитать количество ячеек больше или меньше нуля с помощью следующих формул.
Подсчет ячеек больше нуля
1. Выберите пустую ячейку, введите формулу. = СЧЁТЕСЛИ (A1: C7; "> 0") в панель формул, затем нажмите Enter ключ.
Тогда выбранная ячейка будет заполнена количеством ячеек больше нуля.
Подсчитывать ячейки меньше нуля
Как и в описанной выше операции, для подсчета ячеек меньше нуля используйте эту формулу = СЧЁТЕСЛИ (A1: C7; «<0»)
Внимание: Измените ссылку на ячейку в формуле по своему усмотрению.
Подсчитайте и выберите ячейки больше или меньше нуля с помощью Kutools for Excel
Помимо формул, здесь представлена утилита Select Specific Cells из Kutools for Excel. С помощью этой утилиты вы можете не только подсчитывать количество ячеек, которые больше или меньше нуля, но и выбирать их после подсчета. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон, в котором вы хотите подсчитать количество ячеек больше или меньше нуля. Затем нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. В диалоговом окне «Выбор конкретных ячеек» выберите Ячейка вариант в Тип выбора раздел. в Конкретный тип раздел, выберите Больше or Менее в раскрывающемся списке введите число 0 в следующее поле, а затем щелкните значок OK кнопку.
3. Затем еще один Выбрать определенные ячейки диалоговое окно всплывает, чтобы показать вам, сколько ячеек было найдено больше или меньше нуля. Щелкните значок OK кнопка, все эти ячейки выбираются немедленно.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: