Счетесли эксель как сделать
Функция СЧЕТЕСЛИ в Excel и примеры ее использования
Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
Функция СЧЕТЕСЛИ в Excel: примеры
Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.
У нас есть такая таблица:
Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:
Если условие подсчета внести в отдельную ячейку, можно в качестве критерия использовать ссылку:
Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.
Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:
Во втором случае в качестве критерия использовали ссылку на ячейку.
Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).
Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:
Формула посчитала «кровати» и «банкетки».
Используем в функции СЧЕТЕСЛИ условие поиска «не равно».
Формула: =СЧЁТЕСЛИ(A1:A11;»<>«&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».
При применении ссылки формула будет выглядеть так:
Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.
СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ
Посчитаем количество реализованных товаров по группам.
Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Функция СЧЁТЕСЛИ с использованием двух критериев поиска. Описание и примеры.
Функция СЧЁТЕСЛИ с использованием двух критериев поиска. Описание и примеры.
В данной статье рассмотрим варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска. Более подробная информация с описанием функции СЧЁТЕСЛИ, ее возможностями и примерами использования, находиться по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры
Предположим, что у нас есть простая таблица, с текстовыми данными и числами.
СЧЁТЕСЛИ с двумя критериями. Числовое значение.
Найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. Сначала, используя функцию СЧЁТЕСЛИ, мы находим, количество ячеек, значения в которых больше числа 50. Можно сначала найти количество ячеек, с значением меньше числа 50. Это не имеет значение. Как это сделать описано в статье: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Теперь в строке формулы добавляем плюс «+», и еще раз используем функцию СЧЁТЕСЛИ. Диапазон тот же самый, но в критерии поиска задаем поиск значений меньше числа 50. Получаем общие количество ячеек, значение в которых больше и меньше числа 50.
Можно использовать и другие варианты: больше или равно, меньше или ровно, равно, не равно. В зависимости от задачи.
Так же, вместо суммирования результатов поиска по нескольким критериям, можно провести вычитание этих результатов. Или любое другое действие.
СЧЁТЕСЛИ с двумя критериями. Ссылка на ячейку.
В качестве критерия поиска, можем использовать значения, содержащиеся в ячейке, указывая ссылку на эту ячейку в поле Критерии, диалогового окна Аргументы функции. Вернемся к нашей таблице и найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. При этом используем в качестве критериев поиска ссылки на ячейки. Используем ячейку С7, как одну из ячеек, в которой находиться число 50.
Получили результат 12 ячеек.
Обращаю внимание, что знаки « » (или другие знаки), в таком варианте использования функции, необходимо заключать в кавычки. И ставить между знаками и ссылкой на ячейку символ «&».
СЧЁТЕСЛИ с двумя критериями. Текстовые критерии.
В качестве критерия поиска, в данном варианте, можно использовать текстовое значение (слово) или часть слова. Как работает функции при использование таких критериев поиска, можно узнать по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Объединение нескольких критериев поиска в данном варианте происходит по такому же принципу, как и в вариантах, описанных выше. Возьмём для примера столбец, в котором содержаться название мебели. Найдем количество ячеек, в которых содержаться слово Стол и слово Шкаф. При этом, поиск будем осуществлять используя буквы находящиеся в этих словах. «Ст» и «ф» соответственно. К этим буквам необходимо добавить символ «*». В случае с «Ст», после, так как это первые буквы в слове. В случае с «ф», перед, так как это конец слова. Кавычки появятся автоматически.
Количество критериев не ограниченно двумя. Может быть больше, в зависимости от задачи.
Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel
Количества чисел в таблице Excel можно быстро выяснить, используя функцию «Счёт». Игнорируя текстовый формат, она учитывает только числовые значения.
Где используется и как работает функция СЧЁТ?
Часто, для анализа табличных данных не нужно производить никаких математических операций с числами. Достаточно уточнить количество показателей. Именно для такого случая пригодится эта простая функция СЧЁТ.
Аргументом функции может быть:
Синтаксис функции выглядит следующим образом:
Все версии Excel, начиная с 2007, могут учитывать до 255 аргументов при расчёте результата этой формулы. Обязательным является только первый из них. При этом есть разница в том, как учитывается значение, если оно хранится в ячейке или введено в числе аргументов.
Для понимания этой особенности нужно рассмотреть наглядно на конкретных примерах.
Пример использования функции СЧЁТ в Excel
Пример 1. В таблицу введены некоторые значения. Среди них есть текст, числовые выражения и число, заключённое в кавычки «22». Именно его и считает программа Excel текстовым значением в ячейке.
Пропишем формулу, она будет учитывать содержимое массива ячеек, и будет выглядеть таким образом:
После введения формулы нажатием клавиши Enter, появится число 4. Именно столько чисел введено в ячейки при помощи цифр, которые не заключены в кавычки.
Теперь введём в ячейку ниже D2, другую функцию. В ней все табличные элементы будут прописаны в виде аргументов. Она будет выглядеть так:
=СЧЁТ(12; мост; кг; крыло; 33; 45678; «22»; сила; ампер; 16.02.1999; 14.07.1975; стена; потолок; 21)
Введя формулу, мы получим значение 5.
Разные результаты функции объясняются тем, что одно число («22») воспринимается программой, как текст, если оно находится в ячейке, и как число, если оно прописано в числе аргументов функции.
Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel
Вы сможете быстро определить количество заполненных или пустых клеток в заданной области таблицы. Для выполнения этих операций используются функции с именами «СЧЁТЗ» и «СЧИТАТЬПУСТОТЫ». Для наглядности этой возможности необходимо рассмотреть пример.
Пример 2. В таблицу введены разные значения. Среди них нет никаких закономерностей. Есть пустые и заполненные ячейки.
В любом месте таблицы можно ввести функцию. Для определения заполненных ячеек в диапазоне А1:С5 нужно прописать такую формулу:
Введение её через Enter даст результат 8. Именно столько заполненных ячеек есть в обозначенной области.
Чтобы узнать количество пустых клеток, нужно ввести функцию СЧИТАТЬПУСТОТЫ:
Введение формулы покажет результат 7. Это количество пустых ячеек в таблице.
Пример использования функции СЧЁТЕСЛИ с условием
Очень часто используется такая разновидность функции «СЧЁТ». С помощью заданной формулы можно узнать количество ячеек с заданными параметрами. Функция имеет имя «СЧЁТЕСЛИ». В ней могут учитываться такие аргументы.
Синтаксис выглядит так:
Функция может показать количество ячеек с заданным текстом. Для этого аргумент заключается в кавычки. При этом не учитывается текстовый регистр. В синтаксисе формулы не может быть пробелов.
Оба аргумента являются обязательными для указания. Для наглядности стоит рассмотреть следующий пример.
Пример 3. Есть ведомость с фамилиями студентов и оценками за экзамен. В таблице 2 столбца и 10 ячеек. Нужно определить, какое количество студентов получили отличную оценку 5 (по пятибалльной системе оценивания), а какое 4, потом 3 и 2.
Для определения количества отличников нужно провести анализ содержимого ячеек второго столбика. В отдельной табличке нужно использовать простую функцию подсчета количества числовых значений с условием СЧЁТЕСЛИ:
После нажатия на клавиатуре Enter будет получен результат:
Так, всего за несколько секунд, можно получить данные по обширным и сложным таблицам.
Функция СЧЁТЕСЛИ() в MS Excel
Подсчет числовых значений с одним критерием
Данные будем брать из диапазона A15:A25 (см. файл примера ).
Критерий
Формула
Результат
Примечание
Подсчитывает количество ячеек, содержащих числа равных или более 10. Критерий указан в формуле
Подсчитывает количество ячеек, содержащих числа равных или меньших 10. Критерий указан через ссылку
>= (ячейка С4)11(ячейка С5)
Подсчитывает количество ячеек, содержащих числа равных или более 11. Критерий указан через ссылку и параметр
Подсчет дат с одним критерием
Если необходимо подсчитать количество дат, принадлежащих определенному месяцу, то нужно создать дополнительный столбец для вычисления месяца, затем записать формулу = СЧЁТЕСЛИ(B20:B30;2)
Подсчет с несколькими условиями
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
Функция Счётесли
Счётесли (диапазон; критерий)
Диапазон – группа ячеек, для которых нужно выполнить подсчет.
Диапазон может содержать числа, массивы или ссылки на числа.
Обязательный аргумент.
Критерий – число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Обязательный аргумент.
Критерий проверки необходимо заключать в кавычки.
Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».
Примеры использования функции Счётесли.
Вопросительный знак соответствует одному любому символу,
звездочка— любому количеству произвольных символов.
Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак
Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой:
Если необходимо подсчитать количество ячеек, которые содержат ровно четыре символа, можно использовать формулу:
В функции Счётесли используется только один критерий.
Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.
Функция Счётеслимн
Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).
Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.
Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и диапазон условия 1. Эти диапазоны могут не находиться рядом друг с другом.
Пример использования:
Счётеслимн(C1:C8;”>15.01.2015″;C1:C8;” 15.01.2015” и “ 
Замечание. Критерий не чувствителен к регистру букв, поэтому можно вводить как прописные, так и строчные буквы.
Если ваши данные содержат несколько вариантов слов, которые вы хотите сосчитать, то вы можете использовать подстановочные знаки для подсчета всех ячеек, содержащих определенное слово, фразу или буквы, как часть их содержимого.
К примеру, в нашей таблице есть несколько заказчиков «Корона» из разных городов. Нам необходимо подсчитать общее количество заказов «Корона» независимо от города.
Мы подсчитали количество заказов, где в наименовании заказчика встречается «коро» в любом регистре. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в приведенном выше примере. Если вам нужно заменить какой-либо один символ, введите вместо него знак вопроса (?).
Кроме того, указывать условие прямо в формуле не совсем рационально, так как при необходимости подсчитать какие-то другие значения вам придется корректировать её. А это не слишком удобно.
Рекомендуется условие записывать в какую-либо ячейку и затем ссылаться на нее. Так мы сделали в H9. Также можно употребить подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать «* Коро *» непосредственно в формуле, вы можете записать его куда-нибудь, и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:
Подсчет ячеек, начинающихся или заканчивающихся определенными символами
Вы можете употребить подстановочный знак звездочку (*) или знак вопроса (?) в зависимости от того, какого именно результата вы хотите достичь.
Если вы хотите узнать количество ячеек, которые начинаются или заканчиваются определенным текстом, независимо от того, сколько имеется других символов, используйте:
=СЧЁТЕСЛИ(A2:A22;”*р”) – считать заканчивающиеся буквой «р».
Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, то поставьте вопросительный знак (?):
=СЧЁТЕСЛИ(С2:С22;”. д”) – находит количество буквой «д» в конце и текст в которых состоит из 5 букв, включая пробелы.
= СЧЁТЕСЛИ(С2:С22,”??”) – считает количество состоящих из 2 символов, включая пробелы.
Примечание. Чтобы узнать количество клеток, содержащих в тексте знак вопроса или звездочку, введите тильду (
?*”) будут подсчитаны все позиции, содержащие знак вопроса в диапазоне С2:С22.
Подсчет чисел по условию.
В отношении чисел редко случается, что нужно подсчитать количество их, равных какому-то определённому числу. Тем не менее, укажем, что записать нужно примерно следующее:
Гораздо чаще нужно высчитать количество значений, больших либо меньших определенной величины.
Чтобы подсчитать значения, которые больше, меньше или равны указанному вами числу, вы просто добавляете соответствующий критерий, как показано в таблице ниже.
критерии
Описание
Подсчитайте, где значение больше 5.
Подсчет со числами менее 5.
Определите, сколько раз значение равно 5.
Подсчитайте, сколько раз не равно 5.
Если больше или равно
Подсчет, когда больше или равно 5.
Если меньше или равно
Считаем количество крупных заказов на сумму более 10 000. Обратите внимание, что условие подсчета мы записываем здесь в виде текстовой строки и поэтому заключаем его в двойные кавычки.
Вы также можете использовать все вышеприведенные варианты для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить число ссылкой.
Замечание. В случае использования ссылки, вы должны заключить математический оператор в кавычки и добавить амперсанд (&) перед ним. Например, чтобы подсчитать числа в диапазоне D2: D9, превышающие D3, используйте =СЧЕТЕСЛИ(D2:D9,”>”&D3)
Если вы хотите сосчитать записи, которые содержат математический оператор, как часть их содержимого, то есть символ «>», « 5*”) будет подсчитывать все позиции в диапазоне D2: D9 с таким содержимым, как «Доставка >5 дней» или «>5 единиц в наличии».
Примеры с датами.
Если вы хотите сосчитать клетки с датами, которые больше, меньше или равны указанной вами дате, вы можете воспользоваться уже знакомым способом, используя формулы, аналогичные тем, которые мы обсуждали чуть выше. Все вышеприведенное работает как для дат, так и для чисел.
Позвольте привести несколько примеров:
критерии
Описание
Даты, равные указанной дате.
Подсчитывает количество ячеек в диапазоне E2:E22 с датой 1 июня 2014 года.
Даты больше или равные другой дате.
Сосчитайте количество ячеек в диапазоне E2:E22 с датой, большей или равной 01.06.2014.
Даты, которые больше или равны дате в другой ячейке, минус X дней.
Определите количество ячеек в диапазоне E2:E22 с датой, большей или равной дате в H2, минус 7 дней.
Помимо этих стандартных способов, вы можете употребить функцию СЧЕТЕСЛИ в сочетании с функциями даты и времени, например, СЕГОДНЯ(), для подсчета ячеек на основе текущей даты.
критерии
Равные текущей дате.
До текущей даты, то есть меньше, чем сегодня.
После текущей даты, т.е. больше, чем сегодня.
Даты, которые должны наступить через неделю.
В определенном диапазоне времени.
Синтаксис и использование функции СЧЕТЕСЛИ в Excel
Функция СЧЕТЕСЛИ в Excel используется для подсчета ячеек в пределах заданного диапазона, которые соответствуют определенному критерию или условию.
Например, вы можете использовать функцию СЧЕТЕСЛИ, чтобы узнать, сколько ячеек на вашем листе содержит число больше или меньше указанного вами числа. Другое типичное использование функции СЧЕТЕСЛИ в Excel – подсчет ячеек с определенным словом или началом с конкретной буквы (букв).
Синтаксис функции СЧЕТЕСЛИ очень прост:
Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:
Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;”Иванов Иван”) подсчитывает, сколько заявок поступало от Иванова Ивана:
Функция СЧЕТЕСЛИ в Excel для текста и чисел (точное совпадение)
Выше мы рассмотрели пример функции СЧЕТЕСЛИ, которая подсчитывает текстовые значения, соответствующие определенному критерию.
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.
Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 
В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку
Дополнительная информация
Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL
=SUMIF(B1:B5,”>9″) в файле примера): в других случаях. Для подсчета результата и просто установить курсорМастера функций
Считаем с учетом всех критериев (логика И).
Этот вариант является самым простым, поскольку функция СЧЕТЕСЛИМН предназначена для подсчета только тех ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что логическая функция И работает таким же образом.
Для каждого диапазона – свой критерий.
Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).
Задача может быть выполнена таким образом:
Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.
Одинаковый критерий для всех диапазонов.
Если вы хотите посчитать элементы с одинаковыми критериями, вам все равно нужно указывать каждую пару диапазон/условие отдельно.
Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:
Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.
Использование упрощенного варианта с одним ограничением выбора, например =СЧЁТЕСЛИМН(B2:D11;0), даст другой результат – общее количество ячеек в B2: D11, содержащих ноль (в данном примере это 5).