Функция счетесли в excel

Содержание:

Эксперимент

Для иллюстрации приведем пример того как рассматривают ячейку с Пустым текстом Условное форматирование и функция ЕПУСТО() (см. Файл примера ).

Рассмотрим диапазон A1:D4 , содержащий числа, текст, пустые ячейки и Пустой текст “” (наиболее общий случай).

Ячейка С4 содержит значение Пустой текст (введено как результат вычисления формулы =ЕСЛИ(1>2;1;””) ) и выделена жирной границей. Условное форматирование с правилом « Форматировать только те ячейки, которые пустые » выделит действительно пустые ячейки и ячейку со значением Пустой текст !

Функция ЕПУСТО() не разделяет такого подхода и говорит, что в С4 , что-то есть (формула =ЕПУСТО(C4) введенная в ячейку С5 возвращает ЛОЖЬ).

Функция СЧИТАТЬПУСТОТЫ() также как и Условное форматирование , учитывает при подсчете ячейки со значением Пустой текст вместе с пустыми ячейками.

Чтобы не запутаться с подсчетом пустых ячеек в диапазоне С2:С11 при наличии значений Пустой текст, приведем обобщающую таблицу:

Формула или средство EXCEL	Различает ли пустую ячейку и ячейку со значением Пустой текст ?	Комментарий
Условное форматирование	нет	пустая ячейка и ячейка со значением Пустой текст считаются тождественными
= СЧИТАТЬПУСТОТЫ(С2:С11)	нет	подсчитает все пустые ячейки и ячейки, содержащие Пустой текст
= СУММПРОИЗВ( –(C2:C11=””))	нет	–//–
= СЧЁТЕСЛИ(C2:C11;””)	нет	–//–
= СУММПРОИЗВ(–ЕПУСТО(C2:C11))	да	подсчет только пустых ячеек

Поиск по нескольким критериям

Функция если в excel

Зачастую пользователям требуется провести поиск по нескольким названиям или условиям сразу. В этом случае вы можете объединить несколько формул в одну с помощью знака «+». Посчитаем, сколько позиций столов в количестве более 140 встречается в таблице:

Вводим синтаксис в необходимую клетку и выделяем диапазон. Вписываем «столы» и закрываем оператор.

Далее ставим знак «+» и пишем новую формулу, в которой используем те же значения списка, но вместо критерия пишем «>140».

Смотрим результат и сверяем.

Несколько полезных советов:

если ваша формула отсылается на ячейки из другой базы данных, то в момент вычисления она должна быть открыта в Excel;
при поиске текста не учитывается регистр символов;
чтобы программа искала сами знаки >,

Функция РАНГ

Функция расцепить в excel примеры

Возвращает позицию элемента в списке по его значению, относительно значений других элементов. Результатом функции будет не индекс (фактическое расположение) элемента, а число, указывающее, какую позицию занимал бы элемент, если список был отсортирован либо по возрастанию либо по убыванию.По сути, функция РАНГ выполняет обратное действие функциям НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, т.к. первая находит ранг по значению, а последние находят значение по рангу.Текстовые и логические значения игнорируются.

Синтаксис: =РАНГ(число; ссылка; ), где

число – обязательный аргумент. Числовое значение элемента, позицию которого необходимо найти.
ссылка – обязательный аргумент, являющийся ссылкой на диапазон со списком элементов, содержащих числовые значения.
порядок – необязательный аргумент. Логическое значение, отвечающее за тип сортировки:
- ЛОЖЬ – значение по умолчанию. Функция проверяет значения по убыванию.
- ИСТИНА – функция проверяет значения по возрастанию.

Если в списке отсутствует элемент с указанным значением, то функцией возвращается ошибка #Н/Д.Если два элемента имеют одинаковое значение, то возвращается ранг первого обнаруженного.Функция РАНГ присутствует в версиях Excel, начиная с 2010, только для совместимости с более ранними версиями. Вместо нее внедрены новые функции, обладающие тем же синтаксисом:

РАНГ.РВ – полная идентичность функции РАНГ. Добавленное окончание «.РВ», сообщает о том, что, в случае обнаружения элементов с равными значениями, возвращается высший ранг, т.е. самого первого обнаруженного;
РАНГ.СР – окончание «.СР», сообщает о том, что, в случае обнаружения элементов с равными значениями, возвращается их средний ранг.

Пример использования:

В данном случае используется возврат ранга при проверке диапазона значений по возрастанию.

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

Синтаксис и особенности функции

Поиск позиции функция в excel

Сначала рассмотрим аргументы функции:

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

В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
Функция не учитывает регистр текстовых значений.
При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (

Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.

Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel

Количества чисел в таблице Excel можно быстро выяснить, используя функцию «Счёт». Игнорируя текстовый формат, она учитывает только числовые значения.

Где используется и как работает функция СЧЁТ?

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

Аргументом функции может быть:

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

Синтаксис функции выглядит следующим образом:

=СЧЁТ(аргумент1; аргумент2; …)

Все версии 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 будет получен результат:

5 отличников;
3 студента с оценкой 4 балла;
2 троечника;
ни одного двоечника.

Так, всего за несколько секунд, можно получить данные по обширным и сложным таблицам.

Часто задаваемые вопросы

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

=СЧЁТЕСЛИ(A2;A5;F6;G9;»>0″)

1	=СЧЁТЕСЛИ(A2;A5;F6;G9;»>0″)

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

=СУММ(СЧЁТЕСЛИ(A2;»>0″);СЧЁТЕСЛИ(A5;»>0″);СЧЁТЕСЛИ(F6;»>0″);СЧЁТЕСЛИ(G9;»>0″))

1	=СУММ(СЧЁТЕСЛИ(A2;»>0″);СЧЁТЕСЛИ(A5;»>0″);СЧЁТЕСЛИ(F6;»>0″);СЧЁТЕСЛИ(G9;»>0″))

ИЛИ

=СЧЁТЕСЛИ(A2;»>0″) + СЧЁТЕСЛИ(A5;»>0″) + СЧЁТЕСЛИ(F6;»>0″) + СЧЁТЕСЛИ(G9;»>0″)

1	=СЧЁТЕСЛИ(A2;»>0″)+СЧЁТЕСЛИ(A5;»>0″)+СЧЁТЕСЛИ(F6;»>0″)+СЧЁТЕСЛИ(G9;»>0″)

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

=СЧЁТЕСЛИ(C4:C19;»>=26.02.2020″)

1	=СЧЁТЕСЛИ(C4C19;»>=26.02.2020″)

Я хочу заменить 26.02.2020 ячейкой A1. Как это сделать?Ответ: Чтобы использовать ссылку на ячейку в критериях, вы можете сделать следующее:

=СЧЁТЕСЛИ(C4:C19;»>=»&A1)

1	=СЧЁТЕСЛИ(C4C19;»>=»&A1)

Вопрос: Я бы хотел сделать следующее:

=СЧЁТЕСЛИ(ABS(A1:A10);»>0″)

1	=СЧЁТЕСЛИ(ABS(A1A10);»>0″)

т.е. подсчитайте количество значений в диапазоне A1:A10, которые имеют ненулевую величину. Синтаксис, который я пробовал, не работает. Не могли бы вы помочь?Ответ: Поскольку вы не можете применить функцию ABS к диапазону A1:A10, вам придется вместо этого разбить формулу на две функции СЧЁТЕСЛИ следующим образом:

=СЧЁТЕСЛИ(A1:A10;»>0″) + СЧЁТЕСЛИ(A1:A10;»<0″)

1	=СЧЁТЕСЛИ(A1A10;»>0″)+СЧЁТЕСЛИ(A1A10;»<0″)

Это будет подсчитывать количество значений, которые либо больше 0, либо меньше 0.

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

Функция СЧЕТЕСЛИ в Excel и примеры ее использования

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

Рекомендации для правильной работы функции:

Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
Функция не учитывает регистр текстовых значений.
При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.

Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

Если условие подсчета внести в отдельную ячейку, можно в качестве критерия использовать ссылку:

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

Во втором случае в качестве критерия использовали ссылку на ячейку.

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»»&»стулья»). Оператор «» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.

Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Скачать примеры функции СЧЕТЕСЛИ в Excel

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

Пример использования функции СЧЕТЕСЛИ в excel

В диапазоне ячеек A1:A8 введем значения:

Что бы найти значения больше 10, в ячейке A10 вставим формулу:

=СЧЁТЕСЛИ(A1:A8;»>10″)

Результатом будет значение 5

Если же нам нужно из этих ячеек найти количество больше 10 но меньше 50 то тут формула будет выглядеть иначе:

=СЧЁТЕСЛИ(A1:A8;»>10″)-СЧЁТЕСЛИ(A1:A8;»>50″)

Глаза вас не обманули: в формуле СЧЁТЕСЛИ(A1:A8;»>50″) действительно написано больше 50… Но если подсчитать число ячеек, значение которых больше 10 и вычесть из него количество ячеек значение которых больше 50, в результате и получится количество ячеек, числа в которых лежат в переделах больше 10 и меньше 50… Такая вот загогулина…

В этом видео показано как применяют функцию СЧЕТЕСЛИ в excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Новые статьи

Как разбить ячейку в эксель, как сделать нормальную таблицу в Excel — 12/06/2021 18:30
Исправляем ошибку VBA № 5854 слишком длинный строковый параметр в шаблоне word из таблицы excel 255 символов — 21/02/2021 08:54
База данных из JavaScript для веб страницы из Excel на VBA модуле — 30/11/2019 09:15
Листы в Excel из списка по шаблону — 02/06/2019 15:42
Печать верхней строки на каждой странице в Excel — 04/06/2017 17:05
Создание диаграммы, гистограммы в Excel — 04/06/2017 15:12
Функция СИМВОЛ в Excel или как верстать HTML в Excel — 03/06/2017 17:32
Функция ЕСЛИОШИБКА в excel, пример использования — 20/05/2017 11:39
Как использовать функцию МИН в excel — 20/05/2017 11:36
Как использовать функцию МАКС в excel — 20/05/2017 11:33
Как использовать функцию ПРОПИСН в excel — 20/05/2017 11:31
Как использовать функцию СТРОЧН в excel — 20/05/2017 11:29

Предыдущие статьи

Как использовать функцию Функция СЧЁТ в excel — 20/05/2017 11:09
Как использовать функцию ПОИСК в эксель — 10/03/2017 21:28
Как использовать функцию СЦЕПИТЬ в эксель — 10/03/2017 20:41
Как использовать функцию ПРАВСИМВ в excel — 10/03/2017 20:35
Как использовать функцию ЛЕВСИМВ в excel — 06/03/2017 16:04
Как использовать функцию ЗАМЕНИТЬ в excel — 28/02/2017 18:44
Как использовать функцию ДЛСТР в эксель — 25/02/2017 15:07
Как использовать функцию ЕСЛИ в эксель — 24/02/2017 19:37
Как использовать функцию СУММЕСЛИ в Excel — 22/02/2017 19:08
Как использовать функцию СУММ в эксель — 20/02/2017 19:54
Печать документа в Excel и настройка печати — 16/02/2017 19:15
Условное форматирование в ячейках таблицы Excel — 16/06/2016 17:38
Объединить строку и дату в Excel в одной ячейке — 16/06/2016 17:33
Горячие клавиши в Microsoft Office Excel — 04/06/2016 14:57
Как использовать эксель в качестве фотошопа — 04/06/2016 09:01
Как разделить текст по столбцам, как разделить ячейки в Excel — 14/04/2016 16:19
Как применить функцию ВПР в Excel для поиска данных на листе — 08/01/2016 23:40
Как создать таблицу в Excel, оформление таблицы — 06/01/2016 20:29
Работа в эксель, как начать пользоваться Excel — 26/12/2015 15:48

Функции Excel. Функция СУММЕСЛИ(), СЧЁТЕСЛИ()

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

Синтаксис функции СУММЕСЛИ()

Функция СУММЕСЛИ возвращает сумму значений из указанного диапазона ячеек согласно условия. Функция СУММЕСЛИ имеет следующие аргументы:

СУММЕСЛИ ( диапазон ; критерий ; диапазон_суммирования)

Диапазон – указывается диапазон ячеек для проверки;
Критерий – в критерии может быть указано число, текст или выражение. Критерий указывается следующим образом: 45, “45”, “>45″,” 45″, “текст”.
Диапазон_суммирования – необязательный аргумент. Если аргумент не указан, то суммирование производится по аргументу “Диапазон”, если указан, то суммирование значений будет производиться по ячейкам указанным в этом аргументе.

Пример 1.

Разберем пример на подсчет суммы. Итак, дана матрица 4х4

Необходимо подсчитать сумму значений, которые меньше 6.

Щелкаем по свободной ячейке, добавляем функцию. Функция СУММЕСЛИ находится в категории «Математические», вносим следующие аргументы:

Жмем «ОК» и смотрим. Результат 25.

Пример 2.

Во втором примере я продемонстрирую еще один вариант подсчета.

Необходимо подсчитать, сколько всего было продано молока.

Вставляем функцию и пишем следующее

В данном варианте я поленился писать критерий и сделал ссылку на ячейку G4, если Вам не лень, то пропишите критерий в кавычках “Молоко”. Результат 79.

Второй вариант. Необходимо подсчитать кол-во товара без учета молока. В этом варианте продемонстрирую, как прописать неравенство и ссылку на ячейку. Добавляем в ячейку E16 следующую формулу

& (амперсанд) – позволяет «склеить» строки. В нашем случае, к знаку неравенства мы присоединяем строку G4. Результат работы функций:

Синтаксис функции СЧЁТЕСЛИ()

Функция СЧЁТЕСЛИ() возвращает количество ячеек(строк) удовлетворяющих указанному критерию.

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

Диапазоны и критерии задаются аналогично функции СУММЕСЛИ.

Примечание: Обратите внимание на написание функции, СЧЁТ пишется именно через Ё, а не через Е. Если написать иначе, функция не будет распознана

Пример 3

Все та же таблица с наименованием товаров. Необходимо подсчитать кол-во строк «Молоко» в массиве.

В ячейку B14 добавляем функцию СЧЁТЕСЛИ, которая находится в категории «Статистические».

Здесь я так же поленился с вводом критерия Результат следующий:

Вот и все. Функции просты и понятны. Примеры работы в приложенном файле.

Часто задаваемые вопросы и проблемы.

Я надеюсь, что эти примеры помогли вам почувствовать функцию 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 — примеры формул

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

Пример 1. Промежуточный итог с функцией 9 или 109?

Как вы уже знаете, ПРОМЕЖУТОЧНЫЕ.ИТОГИ использует 2 набора номеров функций: 1-11 и 101-111. Все они игнорируют отфильтрованные строки, но номера 1–11 учитывают скрытые вручную строки, а номера 101–111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.

Чтобы суммировать отфильтрованные строки, вы можете использовать формулу Промежуточный итог с функцией 9 или с 109, как показано на рисунке ниже:

Но если вы скрыли отдельные элементы вручную,

используя команду «Скрыть строки» на вкладке «Главная»> «Ячейки»> «Формат» > «Скрыть и отобразить»,
или щелкнув строки правой кнопкой мыши и выбрав «Скрыть»,

и теперь хотите суммировать значения только в видимых строках, использовать функцию 109 – это единственный вариант:

Остальные номера функций работают таким же образом. Например, для подсчета непустых отфильтрованных ячеек подойдет формула с функцией 3 или 103. Но только функция 103 может правильно подсчитывать видимые значения, если в диапазоне есть скрытые вручную строки.

Пример 2. Промежуточные итоги с условием.

Если вы создаете сводный отчет или информационную панель, где вам нужно отображать различные сводные данные, но у вас ограничено место, чтобы разместить множество вариантов итогов, то можно сделать так:

В одной ячейке создайте раскрывающийся список, содержащий названия функций, такие как СУММ, МАКС, МИН и др.
В ячейке рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточных итогов, соответствующими именам функций в раскрывающемся списке.

Например, если предположить, что значения для промежуточного итога находятся в ячейках C2: C16, а раскрывающийся список в A17 содержит элементы «Итого», «Среднее», «Максимум» и «Минимум», «динамическая» формула промежуточного итога будет выглядеть следующим образом:

И теперь, в зависимости от того, какую функцию ваш пользователь выбирает из раскрывающегося списка, соответствующая функция будет вычислять значения в отфильтрованных строках:

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

Обратите внимание также на то, что если бы мы использовали для расчёта суммы продаж функцию СУММ, то в этой таблице мы не смогли бы подсчитать продажи по какому-то определённому покупателю или группе покупателей. Пришлось бы для каждого из них использовать функцию СУММЕСЛИ. Вы можете достаточно просто посчитать промежуточные итоги с условием, если отфильтровать нужные строки по товарам, по покупателям либо по другим критериям

Результат будет показан только по значениям, оставшимся после фильтрации. Соответственно и расчет будет сделан только по ним

Вы можете достаточно просто посчитать промежуточные итоги с условием, если отфильтровать нужные строки по товарам, по покупателям либо по другим критериям. Результат будет показан только по значениям, оставшимся после фильтрации. Соответственно и расчет будет сделан только по ним.

К примеру, вот как можно подсчитать сумму продаж черного шоколада, исключив молочный при помощи фильтра:

Как видите, при помощи СУММ это сделать невозможно. А использовать СУММЕСЛИ — это каждый раз корректировать условие в формуле либо делать несколько формул «на все случаи жизни». А здесь мы просто отбираем нужное при помощи фильтра. Согласитесь, это гораздо проще и удобнее, нежели писать и затем корректировать громоздкие формулы.