Как использовать счетчики Excel

Обновлено: 21.11.2024

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

Разница между СЧЁТЕСЛИ и СЧЁТЕСЛИМН в том, что СЧЁТЕСЛИ предназначен для подсчёта ячеек с одним условием в одном диапазоне, тогда как СЧЁТЕСЛИМН может оценивать разные критерии в одном и том же или разных диапазонах.

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

Формула

=COUNTIFS(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерий2]…)

Функция СЧЁТЕСЛИМН использует следующие аргументы:

  1. Criteria_range1 (обязательный аргумент) – это первый диапазон, который будет оцениваться по связанным критериям.
  2. Criteria1 (обязательный аргумент) – условия, которые необходимо проверить на соответствие значениям. Критерии могут быть в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки должны быть подсчитаны. Например, критерии могут быть выражены как 2, ">2", A4, "Манго" или "32".
  3. Диапазон_критериев2, критерии2, … (необязательный аргумент) — дополнительные диапазоны и связанные с ними критерии. Функция поддерживает до 127 пар диапазон/критерий.

Заданные критерии могут быть любыми из следующих:

  1. Это числовое значение. Это может быть целое, десятичное число, дата, время или логическое значение.
  2. Текстовая строка. Он может включать подстановочные знаки. Подстановочные знаки могут быть ? (вопросительный знак) или * (звездочка). А? соответствует любому одиночному символу, тогда как * соответствует любой последовательности символов. Если мы действительно хотим найти ? (вопросительный знак) или * (звездочка), нам нужно ввести символ ~ перед этим символом в нашем поиске.

Как использовать функцию СЧЁТЕСЛИМН?

Как функцию рабочего листа, функцию СЧЁТЕСЛИМН можно ввести как часть формулы в ячейку рабочего листа.

Чтобы понять использование функции, рассмотрим несколько примеров:

Пример 1

Предположим, что у нас есть следующие данные:

Мы хотим получить количество товаров, которые есть в наличии (значение в столбце C больше 0), но остаются непроданными (значение в столбце D равно 0).

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

Пример 2

Давайте создадим подсчет по месяцам. Для этого нам нужно использовать две функции — функцию СЧЁТЕСЛИМН и функцию ДАТАМЕС с двумя критериями.

Предположим, что нам даны следующие данные:

Список проблем находится в столбце B. У каждого вопроса есть дата (столбец C) и приоритет (столбец D).

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

Первый столбец сводной таблицы — это дата. Имея фактические даты в столбце C, мы можем легко построить необходимые критерии, используя саму дату и вторую дату, созданную с помощью функции ДАТАМЕС.

Здесь используется следующая формула:

Формула генерирует правильный счет для каждой даты. Для приоритетов мы использовали следующую формулу:

Формула возвращает количество по дате, но с разбивкой по приоритету, который берется непосредственно из строки 6.

Конечным результатом является количество задач, которые необходимо выполнить с точки зрения приоритета:

Некоторые примечания о функции СЧЁТЕСЛИМН:

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

  • Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
  • Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
  • Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс 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 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

В своей простейшей форме СЧЁТЕСЛИ говорит:

=COUNTIF(Где вы хотите искать?, Что вы хотите искать?)

СЧЁТЕСЛИ(диапазон, критерии)

Имя аргумента

Описание

диапазон (обязательно)

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

критерии (обязательно)

Число, выражение, ссылка на ячейку или текстовая строка, определяющая, какие ячейки будут учитываться.

Например, вы можете использовать число, например 32, сравнение, например ">32", ячейку, например B4, или слово, например "яблоки".

COUNTIF использует только один критерий. Используйте СЧЁТЕСЛИМН, если вы хотите использовать несколько критериев.

Примеры

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

Описание

Подсчитывает количество ячеек с яблоками в ячейках с A2 по A5. Результат равен 2.

Подсчитывает количество ячеек с персиками (значение в A4) в ячейках с A2 по A5. Результат равен 1.

Подсчитывает количество яблок (значение в A2) и апельсинов (значение в A3) в ячейках с A2 по A5. Результат равен 3. В этой формуле дважды используется СЧЁТЕСЛИ, чтобы указать несколько критериев, по одному критерию на выражение. Вы также можете использовать функцию СЧЁТЕСЛИМН.

Подсчитывает количество ячеек со значением больше 55 в ячейках с B2 по B5. Результат равен 2.

Подсчитывает количество ячеек со значением, отличным от 75, в ячейках с B2 по B5. Амперсанд (&) объединяет оператор сравнения для не равно (<>) и значение в B4 для чтения =СЧЁТЕСЛИ(B2:B5,"<>75"). Результат равен 3.

Подсчитывает количество ячеек со значением больше (>) или равным (=) 32 и меньше (

Распространенные проблемы

Что пошло не так

Для длинных строк возвращается неверное значение.

Функция СЧЁТЕСЛИ возвращает неверные результаты, если вы используете её для сопоставления строк длиннее 255 символов.

Чтобы сопоставить строки длиннее 255 символов, используйте функцию СЦЕПИТЬ или оператор объединения &. Например, =СЧЁТЕСЛИ(A2:A5,"длинная строка"&"еще одна длинная строка").

Значение не возвращается, когда вы ожидаете значение.

Не забудьте заключить аргумент criteria в кавычки.

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

Рекомендации

Учтите, что СЧЁТЕСЛИ игнорирует прописные и строчные буквы в текстовых строках.

Критерии не чувствительны к регистру. Другими словами, строка "яблоки" и строка "ЯБЛОКИ" будут соответствовать одним и тем же ячейкам.

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

Подстановочные знаки — вопросительный знак (?) и звездочка (*) — можно использовать в критериях. Знак вопроса соответствует любому одиночному символу. Звездочка соответствует любой последовательности символов. Если вы хотите найти фактический вопросительный знак или звездочку, введите тильду (~) перед символом.

Например, =СЧЁТЕСЛИ(A2:A5,"яблоко?") будут подсчитаны все экземпляры слова "яблоко", последняя буква которого может меняться.

Убедитесь, что ваши данные не содержат ошибочных символов.

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

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

СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =СЧЁТЕСЛИ(фрукты,">=32")-СЧЁТЕСЛИ(фрукты,">85"). Именованный диапазон может находиться на текущем листе, другой лист на том же книгу или из другой книги. Чтобы ссылаться на другую книгу, эта книга также должна быть открыта.

Примечание. Функция СЧЁТЕСЛИ не будет подсчитывать ячейки на основе фона ячейки или цвета шрифта. Однако Excel поддерживает определяемые пользователем функции (UDF) с использованием операций Microsoft Visual Basic для приложений (VBA) над ячейками на основе цвета фона или шрифта. Вот пример того, как вы можете подсчитать количество ячеек с определенным цветом ячейки с помощью VBA.

Нужна дополнительная помощь?

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

В этом учебном пособии по Excel объясняется, как использовать функцию Excel СЧЁТЕСЛИМН с синтаксисом и примерами.

Описание

Функция СЧЁТЕСЛИМН в Microsoft Excel подсчитывает количество ячеек в диапазоне, соответствующем одному или нескольким критериям.

Функция СЧЁТЕСЛИМН — это встроенная в Excel функция, которая относится к категории статистических функций. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию СЧЁТЕСЛИМН можно ввести как часть формулы в ячейку рабочего листа.

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

Синтаксис

Синтаксис функции СЧЁТЕСЛИМН в Microsoft Excel:

Параметры или аргументы

диапазон1 Диапазон ячеек, к которым вы хотите применить критерий1. критерии1 Критерии, используемые для определения ячеек для подсчета. criteria1 применяется к range1. диапазон2, . диапазон_n Необязательный. Это диапазон ячеек, к которым вы хотите применить критерии2, . критерии_n против. Может быть до 127 диапазонов. критерии2, . критерии_n Необязательный. Он используется для определения того, какие клетки следует подсчитывать. критерий2 применяется к диапазону2, критерий3 применяется к диапазон3 и т. д. Может быть до 127 критериев.

Возврат

Функция СЧЁТЕСЛИМН возвращает числовое значение.

Применимо к

  • Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007

Тип функции

Пример (как функция рабочего листа)

Давайте рассмотрим несколько примеров функции СЧЁТЕСЛИМН в Excel и узнаем, как использовать функцию СЧЁТЕСЛИМН в качестве функции рабочего листа в Microsoft Excel:

Исходя из приведенной выше электронной таблицы Excel, будут возвращены следующие примеры СЧЁТЕСЛИМН:

Использование именованных диапазонов

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

Например, мы создали именованный диапазон под названием products, который ссылается на диапазон B2:B9 на листе 1.

Затем мы ввели в Excel следующие данные:

На основе приведенной выше таблицы Excel:

Чтобы просмотреть именованные диапазоны, выберите вкладку "Формулы" на панели инструментов в верхней части экрана. Затем в группе "Определенные имена" щелкните раскрывающийся список "Определенные имена" и выберите Диспетчер имен.

Функция Excel СЧЁТЕСЛИМН возвращает количество ячеек, соответствующих одному или нескольким критериям. СЧЁТЕСЛИМН можно использовать с критериями, основанными на датах, числах, тексте и других условиях. СЧЁТЕСЛИМН поддерживает логические операторы (>, ,=) и подстановочные знаки (*,?) для частичного совпадения.

  • range1 — первый диапазон для оценки.
  • criteria1 — критерии для диапазона 1.
  • range2 – [необязательно] второй диапазон для оценки.
  • criteria2 – [необязательно] критерии для диапазона2.

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

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

СЧЁТЕСЛИМН входит в группу из восьми функций Excel, которые разбивают логические критерии на две части (диапазон + критерии). В результате синтаксис, используемый для построения критериев, отличается, и СЧЁТЕСЛИ требуется диапазон ячеек для аргументов диапазона, вы не можете использовать массив.

Базовый пример

В приведенном примере можно использовать COUNTIFS для подсчета записей с использованием двух критериев следующим образом:

Обратите внимание, что функция СЧЁТЕСЛИМН не учитывает регистр.

Двойные кавычки ("") в критериях

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

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

Значение из другой ячейки

При использовании значения из другой ячейки в условии ссылка на ячейку должна быть объединена с оператором при использовании. В приведенном ниже примере СЧЁТЕСЛИМН подсчитает значения в ячейке A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текст) заключен в кавычки, а ссылка на ячейку — нет:

Примечание. СЧЁТЕСЛИМН — это одна из нескольких функций, которые разбивают условия на две части: диапазон + критерии. Это вызывает некоторые несоответствия в отношении других формул и функций.

Не равно

Чтобы создать критерий "не равно", используйте оператор "<>", заключенный в двойные кавычки (""). Например, приведенная ниже формула будет подсчитывать ячейки не равные «красному» в диапазоне A1:A10:

Пустые ячейки

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

Даты

Самый простой способ использовать СЧЁТЕСЛИМН с датами – указать действительную дату в другой ячейке с помощью ссылки на ячейку. Например, для подсчета ячеек в A1:A10, содержащих дату больше даты в B1, можно использовать следующую формулу:

Обратите внимание, что мы присоединяем оператор ">" к дате в ячейке B1, но и не заключаем в кавычки ссылку на ячейку.

Самый безопасный способ жестко закодировать дату в СЧЁТЕСЛИМН – использовать функцию ДАТА. Это гарантирует, что Excel поймет дату. Для подсчета ячеек в A1:A10, содержащих дату менее 1 сентября 2020 г., вы можете использовать:

Подстановочные знаки

В критериях можно использовать знак вопроса (?), звездочку (*) или тильду (~). Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для подсчета ячеек в формате A1:A5, содержащих текст «яблоко», можно использовать следующую формулу:

Тильда (~) — это управляющий символ, позволяющий находить буквальные подстановочные знаки. Например, чтобы подсчитать буквальный вопросительный знак (?), звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (например, ~?, ~*, ~~).

логика ИЛИ

Функция СЧЁТЕСЛИМН предназначена для применения нескольких критериев, но условия применяются с логикой И. Это означает, что если вы попытаетесь подсчитать ячейки, содержащие «красный» или «синий» в одном диапазоне, результат будет равен нулю (0). Однако для подсчета ячеек по логике ИЛИ вы можете использовать константу массива и функцию СУММ следующим образом:

Приведенная выше формула подсчитывает ячейки в диапазоне, которые содержат "красный" или "синий".Вкратце, СЧЁТЕСЛИМН возвращает два числа в массиве (одно для «красного» и одно для «синего»), а функция СУММ возвращает сумму в качестве конечного результата. Для получения дополнительной информации см. этот пример.

Читайте также: