-
Щелкните ячейку, в которой должен выводиться результат.
-
На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
-
СЧЁТЗ: подсчитывает количество непустых ячеек.
-
СЧЁТ: подсчитывает количество ячеек, содержащих числа.
-
СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
-
СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.
Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.
-
-
Выделите диапазон ячеек и нажмите клавишу RETURN.
-
Щелкните ячейку, в которой должен выводиться результат.
-
На вкладке Формулы щелкните Вставить, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
-
СЧЁТЗ: подсчитывает количество непустых ячеек.
-
СЧЁТ: подсчитывает количество ячеек, содержащих числа.
-
СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
-
СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.
Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.
-
-
Выделите диапазон ячеек и нажмите клавишу RETURN.
Содержание:
- Что такое диапазон?
- Как рассчитать диапазон в Excel?
- Вычислить условный диапазон в Excel
Обычно, когда я использую диапазон слов в своих руководствах по Excel, это ссылка на ячейку или набор ячеек на листе.
Но этот урок не об этом диапазоне.
«Диапазон» также является математическим термином, который относится к диапазону в наборе данных (т. Е. Диапазон между минимальным и максимальным значением в данном наборе данных).
В этом уроке я покажу вам действительно простые способы рассчитать диапазон в Exceл.
Что такое диапазон?
В данном наборе данных диапазон этого набора данных будет разбросом значений в этом наборе данных.
Чтобы дать вам простой пример, если у вас есть набор данных об успеваемости учащихся, где минимальный балл составляет 15, а максимальный балл — 98, то разброс этого набора данных (также называемый диапазоном этого набора данных) будет 73.
Диапазон = 98-15
«Диапазон» — это не что иное, как разница между максимальным и минимальным значением этого набора данных.
Как рассчитать диапазон в Excel?
Если у вас есть список отсортированных значений, вам просто нужно вычесть первое значение из последнего значения (при условии, что сортировка выполняется в порядке возрастания).
Но в большинстве случаев у вас будет случайный набор данных, который еще не отсортирован.
Найти диапазон в таком наборе данных также довольно просто.
В Excel есть функции для определения максимального и минимального значения из диапазона (функции MAX и MIN).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите вычислить диапазон для данных в столбце B.
Ниже приведена формула для расчета диапазона для этого набора данных:
= МАКС (B2: B11) -МИН (B2: B11)
Приведенная выше формула находит максимальное и минимальное значение и дает нам разницу.
Довольно просто … не правда ли?
Вычислить условный диапазон в Excel
В большинстве практических случаев найти диапазон не так просто, как просто вычесть минимальное значение из максимального значения.
В реальных сценариях вам также может потребоваться учесть некоторые условия или выбросы.
Например, у вас может быть набор данных, в котором все значения меньше 100, но есть одно значение выше 500.
Если вы рассчитываете порядок для этого набора данных, это приведет к неправильной интерпретации данных.
К счастью, в Excel есть множество условных формул, которые могут помочь вам разобраться в некоторых аномалиях.
Ниже у меня есть набор данных, в котором мне нужно найти диапазон значений продаж в столбце B.
Если вы внимательно посмотрите на эти данные, вы заметите, что есть два магазина, где значения довольно низкие (Магазин 1 и Магазин 3).
Это может быть связано с тем, что это новые магазины или какие-то внешние факторы повлияли на продажи в этих конкретных магазинах.
При вычислении диапазона для этого набора данных может иметь смысл исключить эти новые магазины и рассматривать только те магазины, где есть существенные продажи.
В этом примере, скажем, я хочу игнорировать все те магазины, где стоимость продажи меньше 20 000.
Ниже приведена формула, по которой можно найти диапазон с условием:
= MAX (B2: B11) -MINIFS (B2: B11, B2: B11, "> 20000")
В приведенной выше формуле вместо использования функции MIN я использовал функцию MINIFS (это новая функция в Excel2021-2022 и Microsoft 365).
Эта функция находит минимальное значение, если соблюдены указанные в нем критерии. В приведенной выше формуле в качестве критерия я указал любое значение, превышающее 20 000.
Таким образом, функция MINIFS просматривает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые больше 20 000.
Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, игнорируются выбросы).
Обратите внимание, что MINIFS — это новая функция в Excel. доступно только в Excel2021-2022 и подписке Microsoft 365. Если вы используете предыдущие версии, у вас не будет этой функции (и вы можете использовать формулу, описанную далее в этом руководстве)
Если в вашем Excel нет функции МИНИМУМ, воспользуйтесь приведенной ниже формулой, в которой для того же результата используется комбинация функций ЕСЛИ и МИНИМУМ:
= МАКС (B2: B11) -МИН (ЕСЛИ (B2: B11> 20000; B2: B11))
Так же, как я использовал условную функцию MINIFS, вы также можете использовать функцию MAXIFS, если вы хотите избежать точек данных, которые являются выбросами в другом направлении (т. Е. Пара больших точек данных, которые могут исказить данные)
Итак, вот как вы можете быстро найти диапазон в Excel используя пару простых формул.
Надеюсь, вы нашли этот урок полезным.
Skip to content
В этой статье мы сосредоточимся на функции Excel СЧЕТЕСЛИ (COUNTIF в английском варианте), которая предназначена для подсчета ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я приведу ряд примеров и предупрежу о возможных причудах при подсчете по нескольким критериям одновременно или же с определёнными типами данных.
По сути,они одинаковы во всех версиях, поэтому вы можете использовать примеры в MS Excel 2016, 2013, 2010 и 2007.
- Примеры работы функции СЧЕТЕСЛИ.
- Для подсчета текста.
- Подсчет ячеек, начинающихся или заканчивающихся определенными символами
- Подсчет чисел по условию.
- Примеры с датами.
- Как посчитать количество пустых и непустых ячеек?
- Нулевые строки.
- СЧЕТЕСЛИ с несколькими условиями.
- Количество чисел в диапазоне
- Количество ячеек с несколькими условиями ИЛИ.
- Использование СЧЕТЕСЛИ для подсчета дубликатов.
- 1. Ищем дубликаты в одном столбце
- 2. Сколько совпадений между двумя столбцами?
- 3. Сколько дубликатов и уникальных значений в строке?
- Часто задаваемые вопросы и проблемы.
Функция Excel СЧЕТЕСЛИ применяется для подсчета количества ячеек в указанном диапазоне, которые соответствуют определенному условию.
Например, вы можете воспользоваться ею, чтобы узнать, сколько ячеек в вашей рабочей таблице содержит число, больше или меньше указанной вами величины. Другое стандартное использование — для подсчета ячеек с определенным словом или с определенной буквой (буквами).
СЧЕТЕСЛИ(диапазон; критерий)
Как видите, здесь только 2 аргумента, оба из которых являются обязательными:
- диапазон — определяет одну или несколько клеток для подсчета. Вы помещаете диапазон в формулу, как обычно, например, A1: A20.
- критерий — определяет условие, которое определяет, что именно считать. Это может быть число, текстовая строка, ссылка или выражение. Например, вы можете употребить следующие критерии: «10», A2, «> = 10», «какой-то текст».
Что нужно обязательно запомнить?
- В аргументе «критерий» условие всегда нужно записывать в кавычках, кроме случая, когда используется ссылка либо какая-то функция.
- Любой из аргументов ссылается на диапазон из другой книги Excel, то эта книга должна быть открыта.
- Регистр букв не учитывается.
- Также можно применить знаки подстановки * и ? (о них далее – подробнее).
- Чтобы избежать ошибок, в тексте не должно быть непечатаемых знаков.
Как видите, синтаксис очень прост. Однако, он допускает множество возможных вариаций условий, в том числе символы подстановки, значения других ячеек и даже другие функции Excel. Это разнообразие делает функцию СЧЕТЕСЛИ действительно мощной и пригодной для многих задач, как вы увидите в следующих примерах.
Примеры работы функции СЧЕТЕСЛИ.
Для подсчета текста.
Давайте разбираться, как это работает. На рисунке ниже вы видите список заказов, выполненных менеджерами. Выражение =СЧЕТЕСЛИ(В2:В22,»Никитенко») подсчитывает, сколько раз этот работник присутствует в списке:
Замечание. Критерий не чувствителен к регистру букв, поэтому можно вводить как прописные, так и строчные буквы.
Если ваши данные содержат несколько вариантов слов, которые вы хотите сосчитать, то вы можете использовать подстановочные знаки для подсчета всех ячеек, содержащих определенное слово, фразу или буквы, как часть их содержимого.
К примеру, в нашей таблице есть несколько заказчиков «Корона» из разных городов. Нам необходимо подсчитать общее количество заказов «Корона» независимо от города.
=СЧЁТЕСЛИ(A2:A22;»*Коро*»)
Мы подсчитали количество заказов, где в наименовании заказчика встречается «коро» в любом регистре. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в приведенном выше примере. Если вам нужно заменить какой-либо один символ, введите вместо него знак вопроса (?).
Кроме того, указывать условие прямо в формуле не совсем рационально, так как при необходимости подсчитать какие-то другие значения вам придется корректировать её. А это не слишком удобно.
Рекомендуется условие записывать в какую-либо ячейку и затем ссылаться на нее. Так мы сделали в H9. Также можно употребить подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать «* Коро *» непосредственно в формуле, вы можете записать его куда-нибудь, и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:
=СЧЁТЕСЛИ(A2:A22;»*»&H8&»*»)
Подсчет ячеек, начинающихся или заканчивающихся определенными символами
Вы можете употребить подстановочный знак звездочку (*) или знак вопроса (?) в зависимости от того, какого именно результата вы хотите достичь.
Если вы хотите узнать количество ячеек, которые начинаются или заканчиваются определенным текстом, независимо от того, сколько имеется других символов, используйте:
=СЧЁТЕСЛИ(A2:A22;»К*») — считать значения, которые начинаются с « К» .
=СЧЁТЕСЛИ(A2:A22;»*р») — считать заканчивающиеся буквой «р».
Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, то поставьте вопросительный знак (?):
=СЧЁТЕСЛИ(С2:С22;»????д») — находит количество буквой «д» в конце и текст в которых состоит из 5 букв, включая пробелы.
= СЧЁТЕСЛИ(С2:С22,»??») — считает количество состоящих из 2 символов, включая пробелы.
Примечание. Чтобы узнать количество клеток, содержащих в тексте знак вопроса или звездочку, введите тильду (~) перед символом ? или *.
Например, = СЧЁТЕСЛИ(С2:С22,»*~?*») будут подсчитаны все позиции, содержащие знак вопроса в диапазоне С2:С22.
Подсчет чисел по условию.
В отношении чисел редко случается, что нужно подсчитать количество их, равных какому-то определённому числу. Тем не менее, укажем, что записать нужно примерно следующее:
= СЧЁТЕСЛИ(D2:D22,10000)
Гораздо чаще нужно высчитать количество значений, больших либо меньших определенной величины.
Чтобы подсчитать значения, которые больше, меньше или равны указанному вами числу, вы просто добавляете соответствующий критерий, как показано в таблице ниже.
Обратите внимание, что математический оператор вместе с числом всегда заключен в кавычки .
|
критерии |
Описание |
|
|
Если больше, чем |
=СЧЕТЕСЛИ(А2:А10;»>5″) |
Подсчитайте, где значение больше 5. |
|
Если меньше чем |
=СЧЕТЕСЛИ(А2:А10;»>5″) |
Подсчет со числами менее 5. |
|
Если равно |
=СЧЕТЕСЛИ(А2:А10;»=5″) |
Определите, сколько раз значение равно 5. |
|
Если не равно |
=СЧЕТЕСЛИ(А2:А10;»<>5″) |
Подсчитайте, сколько раз не равно 5. |
|
Если больше или равно |
=СЧЕТЕСЛИ(А2:А10;»>=5″) |
Подсчет, когда больше или равно 5. |
|
Если меньше или равно |
=СЧЕТЕСЛИ(А2:А10;»<=5″) |
Подсчет, где меньше или равно 5. |
В нашем примере
=СЧЁТЕСЛИ(D2:D22;»>10000″)
Считаем количество крупных заказов на сумму более 10 000. Обратите внимание, что условие подсчета мы записываем здесь в виде текстовой строки и поэтому заключаем его в двойные кавычки.
Вы также можете использовать все вышеприведенные варианты для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить число ссылкой.
Замечание. В случае использования ссылки, вы должны заключить математический оператор в кавычки и добавить амперсанд (&) перед ним. Например, чтобы подсчитать числа в диапазоне D2: D9, превышающие D3, используйте =СЧЕТЕСЛИ(D2:D9,»>»&D3)
Если вы хотите сосчитать записи, которые содержат математический оператор, как часть их содержимого, то есть символ «>», «<» или «=», то употребите в условиях подстановочный знак с оператором. Такие критерии будут рассматриваться как текстовая строка, а не числовое выражение.
Например, =СЧЕТЕСЛИ(D2:D9,»*>5*») будет подсчитывать все позиции в диапазоне D2: D9 с таким содержимым, как «Доставка >5 дней» или «>5 единиц в наличии».
Примеры с датами.
Если вы хотите сосчитать клетки с датами, которые больше, меньше или равны указанной вами дате, вы можете воспользоваться уже знакомым способом, используя формулы, аналогичные тем, которые мы обсуждали чуть выше. Все вышеприведенное работает как для дат, так и для чисел.
Позвольте привести несколько примеров:
|
критерии |
Описание |
|
|
Даты, равные указанной дате. |
=СЧЕТЕСЛИ(E2:E22;»01.02.2019″) |
Подсчитывает количество ячеек в диапазоне E2:E22 с датой 1 июня 2014 года. |
|
Даты больше или равные другой дате. |
=СЧЕТЕСЛИ(E2:E22,»>=01.02.2019″) |
Сосчитайте количество ячеек в диапазоне E2:E22 с датой, большей или равной 01.06.2014. |
|
Даты, которые больше или равны дате в другой ячейке, минус X дней. |
=СЧЕТЕСЛИ(E2:E22,»>=»&H2-7) |
Определите количество ячеек в диапазоне E2:E22 с датой, большей или равной дате в H2, минус 7 дней. |
Помимо этих стандартных способов, вы можете употребить функцию СЧЕТЕСЛИ в сочетании с функциями даты и времени, например, СЕГОДНЯ(), для подсчета ячеек на основе текущей даты.
|
критерии |
|
|
Равные текущей дате. |
=СЧЕТЕСЛИ(E2:E22;СЕГОДНЯ()) |
|
До текущей даты, то есть меньше, чем сегодня. |
=СЧЕТЕСЛИ(E2:E22;»<«&СЕГОДНЯ()) |
|
После текущей даты, т.е. больше, чем сегодня. |
=СЧЕТЕСЛИ(E2:E22;»>»& ЕГОДНЯ ()) |
|
Даты, которые должны наступить через неделю. |
= СЧЕТЕСЛИ(E2:E22,»=»&СЕГОДНЯ()+7) |
|
В определенном диапазоне времени. |
=СЧЁТЕСЛИ(E2:E22;»>=»&СЕГОДНЯ()+30)-СЧЁТЕСЛИ(E2:E22;»>»&СЕГОДНЯ()) |
Как посчитать количество пустых и непустых ячеек?
Посмотрим, как можно применить функцию СЧЕТЕСЛИ в Excel для подсчета количества пустых или непустых ячеек в указанном диапазоне.
Непустые.
В некоторых руководствах по работе с СЧЕТЕСЛИ вы можете встретить предложения для подсчета непустых ячеек, подобные этому:
СЧЕТЕСЛИ(диапазон;»*»)
Но дело в том, что приведенное выше выражение подсчитывает только клетки, содержащие любые текстовые значения. А это означает, что те из них, что включают даты и числа, будут обрабатываться как пустые (игнорироваться) и не войдут в общий итог!
Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, то введите:
СЧЕТЕСЛИ(диапазон;»<>» & «»)
Это корректно работает со всеми типами значений — текстом, датами и числами — как вы можете видеть на рисунке ниже.
Также непустые ячейки в диапазоне можно подсчитать:
=СЧЁТЗ(E2:E22).
Пустые.
Если вы хотите сосчитать пустые позиции в определенном диапазоне, вы должны придерживаться того же подхода — используйте в условиях символ подстановки для текстовых значений и параметр “” для подсчета всех пустых ячеек.
Считаем клетки, не содержащие текст:
СЧЕТЕСЛИ( диапазон; «<>» & «*»)
Поскольку звездочка (*) соответствует любой последовательности текстовых символов, в расчет принимаются клетки, не равные *, т.е. не содержащие текста в указанном диапазоне.
Для подсчета пустых клеток (все типы значений):
=СЧЁТЕСЛИ(E2:E22;»»)
Конечно, для таких случаев есть и специальная функция
=СЧИТАТЬПУСТОТЫ(E2:E22)
Но не все знают о ее существовании. Но вы теперь в курсе …
Нулевые строки.
Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.
Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие «пустышки» (F5 -Выделить — Пустые ячейки) — они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.
Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.
Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.
И самое интересное — если указать на нее мышкой и нажать Delete (или вкладка Главная — Редактирование — Очистить содержимое) — то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.
Если вы не хотите рассматривать их как пустые, используйте для подсчета реально пустых клеток следующее выражение:
=ЧСТРОК(E2:E22)*ЧИСЛСТОЛБ(E2:E22)-СЧЁТЕСЛИ(E2:E22;»<>»&»»)
Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:
- Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
- Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:
=ЕСЛИ(Е1=1;10;»»)
В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.
Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо «» ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно — Показывать нули в позициях, которые содержат нулевые значения.
СЧЕТЕСЛИ с несколькими условиями.
На самом деле функция Эксель СЧЕТЕСЛИ не предназначена для расчета количества ячеек по нескольким условиям. В большинстве случаев я рекомендую использовать его множественный аналог — функцию СЧЕТЕСЛИМН. Она как раз и предназначена для вычисления количества ячеек, которые соответствуют двум или более условиям (логика И). Однако, некоторые задачи могут быть решены путем объединения двух или более функций СЧЕТЕСЛИ в одно выражение.
Количество чисел в диапазоне
Одним из наиболее распространенных применений функции СЧЕТЕСЛИ с двумя критериями является определение количества чисел в определенном интервале, т.е. меньше X, но больше Y.
Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:
=СЧЁТЕСЛИ(B2:B11;»>5″)-СЧЁТЕСЛИ(B2:B11;»>15″)
Количество ячеек с несколькими условиями ИЛИ.
Когда вы хотите найти количество нескольких различных элементов в диапазоне, добавьте 2 или более функций СЧЕТЕСЛИ в выражение. Предположим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.
Сделаем это:
=СЧЁТЕСЛИ(A4:A13;»Лимонад»)+СЧЁТЕСЛИ(A2:A11;»*сок»)
Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для вычисления количества всех видов сока в списке.
Как вы понимаете, сюда можно добавить и больше условий.
Использование СЧЕТЕСЛИ для подсчета дубликатов.
Другое возможное использование функции СЧЕТЕСЛИ в Excel — для поиска дубликатов в одном столбце, между двумя столбцами или в строке.
1. Ищем дубликаты в одном столбце
Эта простое выражение СЧЁТЕСЛИ($A$2:$A$24;A2)>1 найдет все одинаковые записи в A2: A24.
А другая формула СЧЁТЕСЛИ(B2:B24;ИСТИНА) сообщит вам, сколько существует дубликатов:
Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.
2. Сколько совпадений между двумя столбцами?
Сравним список2 со списком1. В столбце Е берем последовательно каждое значение из списка2 и считаем, сколько раз оно встречается в списке1. Если совпадений ноль, значит это уникальное значение. На рисунке такие выделены цветом при помощи условного форматирования.
Выражение =СЧЁТЕСЛИ($A$2:$A$24;C2) копируем вниз по столбцу Е.
Аналогичный расчет можно сделать и наоборот – брать значения из первого списка и искать дубликаты во втором.
Для того, чтобы просто определить количество дубликатов, можно использовать комбинацию функций СУММПРОИЗВ и СЧЕТЕСЛИ.
=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)>0)*(C2:C24<>»»))
Подсчитаем количество уникальных значений в списке2:
=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)=0)*(C2:C24<>»»))
Получаем 7 уникальных записей и 16 дубликатов, что и видно на рисунке.
Полезное. Если вы хотите выделить дублирующиеся позиции или целые строки, содержащие повторяющиеся записи, вы можете создать правила условного форматирования на основе формул СЧЕТЕСЛИ, как показано в этом руководстве — правила условного форматирования Excel.
3. Сколько дубликатов и уникальных значений в строке?
Если нужно сосчитать дубликаты или уникальные значения в определенной строке, а не в столбце, используйте одну из следующих формул. Они могут быть полезны, например, для анализа истории розыгрыша лотереи.
Считаем количество дубликатов:
=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2;A2:K2)>1)*(A2:K2<>»»))
Видим, что 13 выпадало 2 раза.
Подсчитать уникальные значения:
=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2;A2:K2)=1)*(A2:K2<>»»))
Часто задаваемые вопросы и проблемы.
Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.
- Возможен ли подсчет в несмежном диапазоне клеток?
Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?
Ответ: Она не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций СЧЕТЕСЛИ:
Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)
Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)
Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:
=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))
Или же
=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)
- Амперсанд и кавычки в формулах СЧЕТЕСЛИ
Вопрос: когда мне нужно использовать амперсанд?
Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.
Итак, вы можете придерживаться этих правил:
- Если вы используете число или ссылку на ячейку в критериях точного соответствия, вам не нужны ни амперсанд, ни кавычки. Например:
= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)
- Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:
= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)
- Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:
= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())
Если вы сомневаетесь, нужен ли амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.
Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.
- Как сосчитать ячейки по цвету?
Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?
Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.
- Ошибка #ИМЯ?
Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?
Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.
- Формула не работает
Проблема: моя формула не работает! Что я сделал не так?
Ответ: Если вы написали формулу, которая на первый взгляд верна, но она не работает или дает неправильный результат, начните с проверки наиболее очевидных вещей, таких как диапазон, условия, ссылки, использование амперсанда и кавычек.
Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….
Если вы используете функцию с несколькими критериями, разделите формулу на несколько частей и проверьте каждую из них отдельно.
И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчитывания ячеек в Excel с несколькими условиями.
Ещё примеры расчета суммы:
Математически вы вычисляете диапазон, вычитая минимальное значение из максимального значения определенного набора данных. Он представляет собой разброс значений в наборе данных и полезен для измерения изменчивости — чем больше диапазон, тем более разбросаны и изменчивы ваши данные.
К счастью, найти диапазон набора данных легко при использовании функций Excel. Вот как это сделать.
Как найти и рассчитать диапазон за 3 шага
Самый простой способ найти диапазон в Microsoft Excel — использовать функции MIN и MAX. Функция MIN вычисляет наибольшее значение в наборе данных, а функция MIN вычисляет наименьшее значение.
Во-первых, убедитесь, что ваши данные аккуратно организованы на листе для простоты анализа. Затем вы можете использовать функции MIN и MAX следующим образом:
- Выберите ячейку за пределами вашего набора данных (в нашем примере D1). В этой ячейке введите = MAX и выберите = MAX в раскрывающемся меню.
- Выберите диапазон ячеек, щелкнув и перетащив. Кроме того, вы можете ввести диапазон ячеек в ячейку вручную (например, =MAX(B2:B15). Нажмите Enter для подтверждения.
- Выберите другую ячейку (в нашем примере D2) и введите =MIN. Повторите шаг 2 для этой ячейки.
- Теперь нужно найти разницу между этими значениями. Для этого выберите ячейку (в нашем примере D3) и используйте функцию вычитания, набрав =(ячейка, содержащая максимальное значение)-(ячейка, содержащая минимальное значение). Например, =D1-D2.
Можно вычислить диапазон значений за один быстрый шаг, объединив эти функции в одной ячейке. Для этого мы будем использовать наш пример, в котором набор данных содержится в ячейках с B2 по B15.
Используя эти ячейки, функция диапазона будет выглядеть следующим образом:
=МАКС(B2:B15)-МИН(B2-B15)
Все, что вам нужно сделать, это изменить значения ячеек, чтобы они соответствовали вашим данным.
Как найти условный диапазон в Excel
Если в вашем наборе данных есть несколько выбросов, можно рассчитать условный диапазон, игнорирующий выбросы. Например, взгляните на этот пример скриншота:
Почти все значения находятся в диапазоне от 40 до 80, но есть два значения около 1000, а два самых низких значения близки к 1. Если вы хотите рассчитать диапазон, но игнорируете эти значения, вам нужно будет игнорировать значения меньше 5 и больше 900. Здесь на помощь приходят функции MAXIFS и MINIFS.
MAXIFS добавляет условие, которое игнорирует значения выше определенного числа, а MINIFS игнорирует значения меньше определенного числа (с каждым условием, разделенным запятой).
Итак, для нашего примера функция MAX будет выглядеть так:
=МАКСЕСЛИ(B2:B15,B2:B15,”<900″)
И функция MIN станет:
=МИНИФС(B2:B15,B2:B15”>5”)
В этом случае итоговая формула Excel для расчета условного диапазона будет такой:
=МАКСИФН(B2:B15,B2:B15″,<900″)-МИНИФМН(B2:B15,B2:B15″,>5″)
Примечание. Функции МАКСИФС и МИНИМФС доступны только в Excel 2019 и Microsoft Office 365, поэтому, если вы используете более старую версию, вам нужно будет включить функцию ЕСЛИ отдельно. Например: =МАКС(В2:В15)-МИН(ЕСЛИ(В2:В15>5,В2:В15)
Анализ данных никогда не был проще
Microsoft Excel — мощный инструмент для анализа данных. С помощью этого руководства вы сможете легко рассчитать диапазон любого набора данных, даже если вам нужно удалить выбросы.
Функция СЧЁТЕСЛИ в Excel с разными условиями: мы объясняем это на примерах.
В этом руководстве объясняется, как использовать функцию СЧЁТЕСЛИ с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных: чисел, дат, текста, подстановочных знаков. Цель этого поста — продемонстрировать разные подходы и помочь выбрать наиболее эффективное решение для каждого конкретного бизнеса.
Начиная с выпуска Excel 2007 года, Microsoft добавила «старших сестер» Excel к функциям выборочного подсчета СУММЕСЛИ, СЧЁТЕСЛИ и СРЕДНЕЛИ — функциям СУММЕСЛИ, СЧЁТЕСЛИ и СРЕДНЕЛИ. На английском языке эти функции выглядят как СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЕДНИМЕСЛИМН, то есть в конце у них есть буква -S, которая в английском означает множественное число. В русской версии эту роль играет -MN.
Их часто путают, потому что они очень похожи друг на друга и рассчитаны на основе определенных критериев.
Разница в том, что функция СЧЁТЕСЛИ предназначена для подсчета ячеек с одним и тем же условием в одном диапазоне, а СЧЁТЕСЛИ может оценивать разные критерии в том же или разных диапазонах.
Как работает функция СЧЕТЕСЛИМН?
Вычисляет количество совпадений в нескольких диапазонах на основе одного или нескольких критериев.
Синтаксис функции следующий:
СЧЁТЕСЛИ (интервал1; условие1; [интервал2; условие2]…)
- диапазон1 (обязательный) — указывает первую область, к которой следует применить первое условие (условие1).
- условие1 (обязательно): устанавливает требование выбора в виде числа, ссылки на ячейку, текстовой строки, выражения или другой функции Excel. Определите, какие клетки нужно подсчитывать.
- [интервал2; условие2]… (необязательно) — необязательные поля и соответствующие критерии. Можно указать до 127 таких пар.
На самом деле нет необходимости запоминать этот синтаксис. Microsoft Excel отобразит аргументы функции, как только вы начнете вводить текст; тема, которую вы вводите, будет выделена жирным шрифтом.
Что нужно запомнить?
- Диапазон поиска может быть от 1 до 127. Каждый из них имеет свое условие. Рассматриваются только случаи, отвечающие всем требованиям.
- Каждый дополнительный диапазон должен иметь такое же количество строк и столбцов, что и первый. В противном случае вы получите # ЗНАЧЕНИЕ!
- Допускаются как смежные, так и несмежные диапазоны.
- Если аргумент содержит ссылку на пустую ячейку, функция рассматривает ее как нулевое (0) значение).
- в критериях можно использовать подстановочные знаки звездочки (*) и вопросительного знака (?). Подробнее об этом мы расскажем ниже.
Считаем с учетом всех критериев (логика И).
Этот вариант является самым простым, поскольку функция СЧЁТЕСЛИ предназначена для подсчета только ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что функция И работает точно так же.
а. Для каждого диапазона — свой критерий.
Предположим, у нас есть список продуктов, как показано на скриншоте ниже. Вы хотите знать количество продуктов, которые есть в наличии (у них значение в столбце B больше 0), но еще не проданных (значение в столбце D равно 0).
Задачу можно сделать так:
= СЧЁТЕСЛИ (B2: B11; G1; D2: D11; G2)
или
= СЧЁТЕСЛИ (B2: B11; «> 0»; D2: D11,0)
Видим, что 2 товара (крыжовник и ежевика) есть в наличии, но не в продаже.
б. Одинаковый критерий для всех диапазонов.
Если вы хотите подсчитать элементы по одним и тем же критериям, вам все равно нужно указать каждую пару диапазон / условие отдельно.
Например, вот правильный подход к подсчету элементов, у которых 0 как в столбце B, так и в столбце D:
= СЧЁТЕСЛИ (B2: B11,0; D2: D11,0)
Мы получаем 1, потому что только Plum имеет значение «0» в обоих столбцах.
Использование упрощенной версии с ограничением выбора, например = COUNTIFS (B2: D11; 0), даст другой результат: общее количество ячеек в B2: D11, содержащих ноль (в этом примере это 5).
Если достаточно выполнения хотя бы одного условия (логика ИЛИ).
Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем вышеперечисленным критериям, прост, потому что функция СЧЁТЕСЛИМН предназначена для выполнения этой работы.
Но что, если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий истинно, то есть использовать логику ИЛИ? В принципе, это можно сделать двумя способами: 1) путем добавления нескольких формул СЧЁТЕСЛИ или 2) с помощью комбинации СУММ + СЧЁТЕСЛИ с константой массива.
Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.
Считаем заказы со статусом «Отменено» и «Ожидание». Для этого вы можете просто написать 2 обычные формулы СЧЁТЕСЛИ и затем сложить результаты:
= СЧЁТЕСЛИ (E2: E11, «Отменено») + СЧЁТЕСЛИ (E2: E11, «Ожидание»)
Если вам нужно оценить более одного параметра выбора, используйте COUNTPIIF.
Чтобы узнать количество «отмененных» и «ожидающих» заказов на клубнику, используйте эту опцию:
= СЧЁТЕСЛИ (A2: A11, «клубника»; E2: E11, «Отменено») + СЧЁТЕСЛИ (LA2: A11, «клубника»; E2: E11, «Ожидание»)
Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.
В ситуациях, когда вам нужно оценить множество критериев, описанный выше подход — не лучший вариант, потому что ваша формула станет слишком громоздкой. Чтобы выполнить те же вычисления в более компактной форме, перечислите все критерии в константе массива и предоставьте этот массив в качестве аргумента функции COUNTPIIF.
Введите СЧЁТЕСЛИМН в функцию СУММ, например:
СУММ (СЧЁТЕСЛИМН (диапазон; {«условие1»; «условие2»; «условие3»;…}))
В нашей таблице с примерами подсчета заказов со статусом «Отменено» или «В ожидании» расчет будет выглядеть так:
= СУММ (СЧЁТЕСЛИМН (E2: E11; {«Отменено», «В ожидании»}))
Массив означает, что мы сначала ищем все отмененные ордера, затем отложенные ордера. В результате получается двузначная матрица итогов. А затем функция СУММ складывает их.
Точно так же вы можете использовать две или более пары диапазон / условие. Чтобы рассчитать количество отмененных или отложенных заказов на клубнику, используйте это выражение:
= СУММ (СЧЁТЕСЛИМН (A2: A11; «Клубника»; E2: E11; {«Отменено», «Ожидает рассмотрения»}))
Как сосчитать числа в интервале.
СЧЁТЕСЛИМН вычисляет 2 типа итогов: 1) на основе множества ограничений (объясненных в примерах выше) и 2) когда числа находятся между двумя указанными значениями. Последнее можно сделать двумя способами: с помощью функции СЧЁТЕСЛИ или вычитанием одного СЧЁТЕСЛИ из другого.
1. СЧЕТЕСЛИМН для подсчета ячеек между двумя числами
Чтобы узнать, сколько поступило заказов с количеством товаров от 10 до 20, сделаем так:
= СЧЁТЕСЛИ (D2: D11; «> 10»; D2: D11; «
2. СЧЕТЕСЛИ для подсчета в интервале
Тот же результат можно получить, вычитая одну формулу СЧЁТЕСЛИ из другой. Сначала мы вычисляем, сколько чисел больше значения нижней границы диапазона (10 в этом примере). Второй возвращает количество заказов, превышающих верхний предел (в данном случае 20). Разница между ними — это результат, который вы ищете.
= СЧЁТЕСЛИ (G2: D11; «> 10») — СЧЁТЕСЛИ (G2: D11; «> 20»)
Это выражение вернет ту же сумму, что и на изображении выше.
Как использовать ссылки в формулах СЧЕТЕСЛИМН.
При использовании логических операторов, таких как «>», « =» вместе со ссылками на ячейки, обязательно заключите оператор в «двойные кавычки» и добавьте амперсанд (&) перед ссылкой. Другими словами, требование выбора должно быть представлено в виде текста, заключенного в кавычки.
рис6
В этом примере мы будем считать заказы с количеством более 30 единиц, несмотря на то, что на складе было менее 50 единиц товара.
= СЧЁТЕСЛИ (B2: B11; « 30″)
или
= СЧЁТЕСЛИ (B2: B11; «» & G2)
если вы отметили значения ограничений в определенных ячейках, например в G1 и G2, и обратитесь к ним.
Как использовать СЧЕТЕСЛИМН со знаками подстановки.
Традиционно можно использовать следующие подстановочные знаки:
- Знак вопроса (?) — соответствует любому одиночному символу. Используйте его для подсчета ячеек, начинающихся и / или заканчивающихся четко определенными символами.
- Звездочка (*) — соответствует любой последовательности символов (включая ноль). Позволяет заменить часть контента.
Примечание. Если вы хотите подсчитывать ячейки, в которых есть вопросительный знак или звездочка, как буквы, поставьте тильду (~) перед звездочкой или вопросительным знаком в записи параметра поиска.
Теперь посмотрим, как можно использовать подстановочный знак.
Допустим, у нас есть список заказов, к которым менеджеры прикреплены лично. Вы хотите знать, сколько заказов уже было кому-то назначено и при этом для них установлен крайний срок. Другими словами, в столбцах B и E таблицы есть значения.
Нам нужно узнать количество заказов, по которым заполнены столбцы B и E:
= СЧЁТЕСЛИ (B2: B21; «*»; E2: E21;»»&»»)
Обратите внимание, что в первом критерии мы используем подстановочный знак *, поскольку мы рассматриваем текстовые значения (фамилии). По второму критерию мы разбираем даты, потом пишем по-другому: «» & «» (значит — не равно пустому значению).
Несколько условий в виде даты.
Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.
1.Подсчет дат в определенном интервале.
Вы также можете использовать СЧЁТЕСЛИ с двумя критериями или комбинацию двух функций СЧЁТЕСЛИ для подсчета дат, попадающих в определенный временной диапазон.
Следующие выражения подсчитывают в областях от D2 до D21 количество дат, которые попадают в период с 1 по 7 февраля 2020 года включительно:
= СЧЁТЕСЛИ (G2: G21; «> = 01.02.2020»; G2: G21; «
или
= СЧЁТЕСЛИ (G2: D21; «> =» & H3; D2: D21; «
2. Подсчет на основе нескольких дат.
Точно так же вы можете использовать СЧЁТЕСЛИМН, чтобы подсчитать количество дат в разных столбцах, которые соответствуют двум или более требованиям. Например, посчитаем, сколько заказов было принято до 1 февраля и доставлено после 5 февраля:
Как обычно, будем писать двумя способами: со ссылками и без них:
= СЧЁТЕСЛИ (D2: D21; «> =» & H3; E2: E21; «> =» & H4)
а также
= СЧЁТЕСЛИ (G2: D21; «> = 01.02.2020»; E2: E21; «> = 05.02.2020»)
3. Подсчет дат с различными критериями на основе текущей даты
вы можете использовать функцию СЕГОДНЯ () для подсчета дат относительно сегодняшнего дня.
Эта формула с двумя областями и двумя критериями покажет вам, сколько товаров уже было куплено, но еще не доставлено.
= СЧЁТЕСЛИ (G2: D21; «» & СЕГОДНЯ())
Он допускает множество возможных вариаций. В частности, вы можете настроить его так, чтобы он подсчитывал, сколько заказов было размещено более недели назад и еще не доставлено:
= СЧЁТЕСЛИ (G2: D21; «» & СЕГОДНЯ())
Таким образом, вы можете подсчитать клетки, соответствующие различным условиям.