Подсчет значений с множественными критериями (часть 1. условие и) в excel. примеры и описание

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

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

В таблице указаны позиции, их количества, а также принадлежность к той или иной группе товаров (первый столбец). Рассмотрим пока упрощенное использование СУММЕСЛИ, когда нам нужно посчитать сумму только по тем позициям, значения по которым соответствуют некоторому условию. Например, мы хотим узнать, сколько было продано топовых позиций, т.е. тех, значение которых превышает 70 ед. Искать такие товары глазами, а потом суммировать вручную не очень удобно, поэтому функция СУММЕСЛИ здесь очень уместна.

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

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

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования

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

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

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

Суммирование в Excel, используя функции СУММ и СУММЕСЛИ

​ Excel» тут.​ в функции СУММЕСЛИ —​ аргументом​Артишоки​ (обязательный аргумент)​припасла третий необязательный​может принимать до​ не будем рассматривать,​ например от 5​​критерию, т.е. слову​​ текстовым «Фрукты» и​​ значений? Если да-то​​ проверяемый текст (например,​ в различные страны​ качестве аргументов функции,​ и при пошаговом​ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая​

Функция СУММ – простое суммирование ячеек в Excel

​Второй пример.​​ третьим. Этот момент​​Диапазон_суммирования​Ольга​Диапазон ячеек для суммирования.​ аргумент, который отвечает​ 255 аргументов и​ как посчитать сумму​ до 20 (см.​ персики. Массив можно​ числовым «Количество на​ как это сделать?​

​ «Продавец_1», «Товар_1») или​ на протяжении трех​​ и возвращает полученное​​ вычислении на листе​ версия Excel​

​Сумма выборочных ячеек​​ часто является источником​​(A2:A9). Результат — 30.​22​Диапазон_условия1​ за диапазон, который​ суммировать сразу несколько​ в Excel при​

​ файл примера Лист​​ увидеть, выделив в​​ складе» (См. файл​ Заранее спасибо за​ числовое значение (Например,​ месяцев. Определить суммарный​ суммарное значение.​ массивы (выделены красным).​​Способ 2:​​Excel.​ проблем при использовании​Проблема​Бананы​ (обязательный аргумент)​

​ необходимо просуммировать. Т.е.​​ несмежных диапазонов или​​ помощи оператора сложения,​ 2Числа).​ Строке формул A2:A13=D2,​

​ примера).​ ответ.​ 20), определяющие ячейки​​ доход от отгрузок​​В отличие от СУММЕСЛИ,​Думаю всё понятно​Code =СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)​Нам нужно посчитать​ данных функций.​Описание​

​Артем​​ Диапазон, в котором проверяется​​ по первому аргументу​ ячеек:​ автосуммы и прочих​Формулы строятся аналогично задаче​ а затем нажав​

​Найдем количество ящиков товара​Юрий М​ в диапазоне условия1,​​ товара №2 в​​ в рассматриваемой функции​ :)​В английской версии:​

​ сумму всех Ашанов​При копировании и изменении​Вместо ожидаемого результата отображается​12​

​Условие1​​ функция проверяет условие,​​Если среди суммируемых значений​ инструментов. Сегодня мы​ 1: =СУММЕСЛИМН(B2:B13;B2:B13;»>=»&D2;B2:B13;»​F9​ с определенным Фруктом​: А СУММЕСЛИ() +​ для которых будут​ Казахстан за июнь​ предусмотрена возможность внесения​

​МИНУСЫ: СУММПРОИЗВ -​Code =SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)​ с номерами, у​ этих похожих формул​

СУММЕСЛИ — условная сумма в Excel

​ 0 (нуль).​​Бананы​​.​ а третий подлежит​ содержится текст, то​ разберем всего две​Примечание​​;​​И​ СУММЕСЛИ() не подойдёт?​

​ отобраны и просуммированы​ и август.​ до 127 критериев​ «тяжёлая» формула массива.​КАК ЭТО РАБОТАЕТ:​ которых нет слов​

​ нужно следить за​Если выполняется поиск текстовых​Ольга​Диапазон_условия1​ суммированию.​

​: для удобства, строки,​Результатом вычисления B2:B13 является​, у которых Остаток​vikttur​ данные из диапазона​Вид таблицы данных:​

​ отбора данных для​ При вычислениях на​Функция СУММПРОИЗВ формирует​ перед словом «Ашан».​ правильным порядком аргументов.​ значений, например имени​10​и​В следующем примере мы​СУММ​

​СУММ​​ участвующие в суммировании,​​ массив , т.е.​ ящиков на складе​: =ИЛИ(СУММЕСЛИ(. ;<.>;. ))​ суммирования.​Для расчета итоговой суммы​ суммирования. СУММЕСЛИМН удобна​ больших диапазонах данных​ массивы из значений​

​В ячейке D4​Одинаковое количество строк и​ человека, убедитесь в​Морковь​Условие1​

​ просуммируем общую стоимость​​их игнорирует, т.е.​​и​

​ выделены Условным форматированием​ просто значения из​ не менее минимального.​Makes​​Последующие аргументы являются необязательными​​ используем формулу:​ для работы с​ заметно увеличивается время​ ИСТИНА и ЛОЖЬ,​

​ напишем такую формулу.​ столбцов для аргументов,​ том, что значения​Артем​составляют пару, определяющую,​​ всех проданных фруктов.​​ не включает в​​СУММЕСЛИ​​ с правилом =И($B2>=$D$2;$B2​ столбца​ Например, количество ящиков​: Слишком громоздкая формула​ для заполнения, а​Результат расчетов:​ объемными таблицами, содержащими​ пересчёта.ПРИМЕЧАНИЯ: Количество обрабатываемых​ согласно выбранным критериям,​=СУММЕСЛИ(A2:A8;D3&»*»;B2:B8)​ задающих диапазоны ячеек​ аргументов​33​ к какому диапазону​

​ Для этого воспользуемся​

СЧЕТЕСЛИ по двум критериям.

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

​ извращаться — см.​ которых фамилия начинается​​ задаче было только​

​(т.е. вначале проверка​​Текстовые строки сравниваются​Так и не​ и наступит результат!​ критерию, когда ее​ критерию >=5, поэтому​ минимального (Условие И​

​ пишем такую формулу.​​ результатов голосования или​ с многими вложенными​ повторов каждого значения​

​ Подсчет ТЕКСТовых значений​​ может быть выражен​ надо. Легко сообразить,​ следующие способы.​

​ одно условие (все​​ первого символа, потом​ по значению, без​​ разобралась в чем​​r_kot​

​ поле удовлетворяет обоим​​ второе значение в​ — условие при​

​ =СЧЁТЕСЛИ(B1:E1;»4″)*2+СЧЁТЕСЛИ(B1:E1;»7″)*6​​ ответов в анкете,​ функциями, смотрите в​ в списке.​ с единственным критерием​ следующим образом: 32,​ что этот способ​

​Добавим к нашей таблице​ а заканчивается на​​ заказы Петрова или​ проверка строки без​ учёта регистра​ тогда была проблема,​

​: Поправил формулу. Вместо​​ критериям одновременно). ​ массиве =1 и​ котором строка считается​Получилось 16 часов.​ других опросов.​ статье «Как составлять​Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел ,​ в MS EXCEL).​

​ «32», «>32», «яблоки»​ (как и предыдущий)​

​ еще один столбец,​​ «В» — критерий​

​ все заказы в​​ учёта регистра, и​Serge 007​ но когда заново​ enter жми ctrl+shift+enter​Решение стоится аналогично предыдущей​ т.д.​ удовлетворяющей критерию, когда​Как посчитать количество​Есть таблица с​ формулы в Excel​​ который говорит о​

​Так как любой дате​​ или​​ легко масштабируется на​ который будет служить​П*В​ «Копейку», например), то​ затем проверка рейтинга)​: да, это стандартный​

Функция СУММПРОИЗВ — подробное описание

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

Сами способы следующие:

1. Определение суммы произведений массива.
2. Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
3. Использование в качестве замены формулы массива.

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

Синтаксис функции СУММПРОИЗВ

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

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

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

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

1

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

Видим, что результат использования функции СУММПРОИЗВ получился идентичным. 

2

Функция СУММПРОИЗВ с условием

В чистом виде функция СУММПРОИЗВ используется довольно редко. Особенно в бухгалтерском учете. Тяжело представить ситуацию, что она оказывается непосредственно полезной каждый день для того, чтобы рассчитывать сумму произведений. Впрочем, такие ситуации тоже бывают. Например, если поставлена задача перемножить между собой курс и сумму в долларах, а потом получить общий финансовый показатель, основанный на стоимости каждой продукции в национальной валюте. Но такая задача все равно случается довольно редко. 

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

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

Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!

3

Давайте рассмотрим аргументы более подробно:

1. Первое условие записывается в первых скобках.
2. Второе условие записывается во вторых скобках.
3. Третий аргумент – это массив, сумму из которого необходимо извлечь. 

Итоговый результат – 3700 рублей. Если распространить эту формулу на остальные строки и заменить условия в каждой из них, можно получить значения, характерные для другого отчетного периода или иной статьи расходов. К сожалению, лучше автоматизировать этот процесс не получится. Придется некоторые показатели править вручную. Но такова жизнь. Правда, если научиться писать макросы, можно добиться абсолютно любых уровней автоматизации при работе с электронными таблицами. 

4

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

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

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

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

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

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

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

Как посчитать количество ячеек по нескольким условиям в Excel?

Пример 3. В таблице приведены данные о количестве отработанных часов сотрудником на протяжении некоторого периода. Определить, сколько раз сотрудник работал сверх нормы (более 8 часов) в период с 03.08.2018 по 14.08.2018.

Вид таблицы данных:

Для вычислений используем следующую формулу:

В качестве первых двух условий проверки указаны даты, которые автоматически преобразовываются в код времени Excel (числовое значение), а затем выполняется операция проверки. Последний (третий) критерий – количество рабочих часов больше 8.

В результате расчетов получим следующее значение:

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?

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

Хотя общая часть понятна — схожее назначение и параметры — различия не так заметны, хотя и весьма существенны.

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон_суммирования — это первый параметр в СУММЕСЛИ, но это третий параметр в СУММЕСЛИ.

На первый взгляд может показаться, что Microsoft намеренно усложняет обучение своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон не является обязательным в СУММЕСЛИ. Если вы его опустите, нет проблем, ваша формула будет добавлена ​​в диапазон поиска (первый параметр).

Однако в SUMIFN это очень важно и обязательно, поэтому оно стоит на первом месте. Ребята из Microsoft, наверное, подумали, что после добавления десятого или сотого интервала / пары критериев кто-то может забыть указать интервал для добавления:)

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

В функции СУММЕСЛИ эти аргументы не обязательно должны быть одинакового размера. Просто укажите начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.

Выражение = СУММЕСЛИ (E2: E21; C2: C21; I2; F2: F22; I3) вернет #VALUE! Сообщение об ошибке, потому что второй параметр поиска (F2: F22) не того же размера, что и остаток (E2: E21) и (C2: C21).

Хорошо, хватит стратегии (т.е теории), перейдем к тактике (примерам).

Метод 2: применение функции СЧЕТЗ

Данная функция позволяет сделать то же самое, но с одной существенной разницей – полученный результат будет зафиксирован. Остальные минусы предыдущего метода сохраняются, т.е. СЧЕТ3 не работает с условиями.

Функция СЧЕТ3 выполняет задачу по подсчету всех заполненных ячеек в заданном диапазоне (пустые не учитываются). Формула функции может выглядет по-разному:

• =СЧЕТЗ(ячейка1;ячейка2;…ячейкаN)
• =СЧЕТЗ(ячейка1:ячейкаN)

В первом случае функция выполнит подсчет всех перечисленных ячеек. Во втором – определит количество непустых ячеек в диапазоне от ячейки 1 до ячейки N

Обратите внимание, что количество аргументов функции ограничено на отметке 255

Давайте попробуем применить функцию СЧЕТ3 на примере:

1. Выбираем ячейку, где по итогу будет выведен результат подсчета.
2. Переходим во вкладку “Формулы” и нажимаем кнопку “Вставить функцию”.Также можно кликнуть по значку «Вставить функцию» рядом со строкой формул.
3. В открывшемся меню (Мастер функций) выбираем категорию «Статистические», далее ищем в перечне нужную функцию СЧЕТ3, выбираем ее и нажимаем OK, чтобы приступить к ее настройке.
4. В окне «Аргументы функции» задаем нужные ячейки (перечисляя их или задав диапазон) и щелкаем по кнопке OK. Задать диапазон можно как с заголовком, так и без него.
5. Результат подсчет будет отображен в выбранной нами ячейке, что изначально и  требовалось. Учтены все ячейки с любыми данными (за исключением пустых).

Метод 3: использование функции СЧЕТ

Функция СЧЕТ подойдет, если вы работаете исключительно с числами. Ячейки, заполненные текстовыми значениями, этой функцией учитываться не будут. В остальном СЧЕТ почти идентичен СЧЕТЗ из ранее рассмотренного метода.

Так выглядит формула функции СЧЕТ:

• =СЧЕТ(ячейка1;ячейка2;…ячейкаN)
• =СЧЕТ(ячейка1:ячейкаN)

Алгоритм действий также похож на тот, что мы рассмотрели выше:

1. Выбираем ячейку, где будет сохранен и отображен результат подсчета значений.
2. Заходим в Мастер функций любым удобным способом, выбираем в категории “Статистические” необходимую строку СЧЕТ и щелкаем OK.
3. В «Аргументах функции» задаем диапазон ячеек или перечисляем их. Далее жмем OK.
4. В выбранной ячейке будет выведен результат. Функция СЧЕТ проигнорирует все ячейки с пустым содержанием или с текстовыми значениями. Таким образом, будет произведен подсчет исключительно тех ячеек, которые содержат числовые данные.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция СУММПРОИЗВ — подробное описание

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

Сами методы следующие:

1. Определение суммы произведений массива.
2. Проверка стоимости на соответствие определенным критериям, получение их произведений и суммирование.
3. Используйте вместо формулы массива.

На самом деле существует гораздо больше возможных вариантов методов использования функции СУММПРОИЗВ. Опытный пользователь Excel может легко комбинировать несколько функций, чтобы таблица работала так, как он хочет.

Синтаксис функции СУММПРОИЗВ

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

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

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

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

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

Мы видим, что результат использования функции СУММПРОИЗВ идентичен.

 

Функция СУММПРОИЗВ с условием

В чистом виде функция СУММПРОИЗВ используется редко. Особенно в бухгалтерском учете. Трудно представить себе ситуацию, которая бы использовалась непосредственно каждый день для подсчета суммы произведений. Однако бывают и такие ситуации. Например, если задана задача умножить обменный курс на сумму в долларах, то получить общий финансовый показатель, основанный на стоимости каждого продукта в национальной валюте. Но такая задача по-прежнему возникает довольно редко.

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

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

Чтобы сделать это с помощью этой функции, вам нужно использовать ее и вначале отметить два условия. Каждое из условий заключено в круглые скобки. Поскольку в нашем случае должны выполняться одновременно два критерия, мы использовали символ звездочки (*). Синтаксис можно увидеть на этом скриншоте. Вам просто нужно подставить соответствующие значения для вашего случая в нужные места. И готово!

Давайте подробнее рассмотрим аргументы:

1. Первое условие написано в первых скобках.
2. Второе условие записано между вторыми скобками.
3. Третий аргумент — это массив, из которого нужно добавить.

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

Функция СЧЕТЕСЛИ в Excel

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

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

Синтаксис и использование функции СЧЕТЕСЛИ в Excel

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

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

Синтаксис функции СЧЕТЕСЛИ очень прост:

Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:

• диапазон – определяет одну или несколько ячеек для подсчета. Вы помещаете диапазон в формулу, как обычно, в Excel, например. A1:A20.
• критерии – определяет условие, которое сообщает функции, которую подсчитывают ячейки. Это может быть число, текстовая строка, ссылка на ячейку или выражение (например, «10», A2, «>=10»).

Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;»Иванов Иван») подсчитывает, сколько заявок поступало от Иванова Ивана:

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

Примечание : Критерий нечувствителен к регистру, что означает, что если вы наберете «иванов иван» в качестве критерия в приведенной выше формуле СЧЕТЕСЛИ, это приведет к такому же результату.

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

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

Функция СЧЕТЕСЛИ в Excel для текста и чисел (точное совпадение)

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

Вместо ввода текста вы можете использовать ссылку на любую ячейку , содержащую это слово или слова, и получить абсолютно одинаковые результаты, например: =СЧЕТЕСЛИ(С1:С7; С2).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку

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

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел

На изображении выше формула =СЧЁТЕСЛИ(B2:B7;10) учитывает ячейки с количеством 10 в столбце D.

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

Обратите внимание, что в функции СЧЕТЕСЛИ оператор с числом всегда заключен в кавычки, например, =СЧЕТЕСЛИ(B2:B7; «>=10»)

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел с логическим оператором

Функция СЧЕТЕСЛИ с подстановочными знаками (частичное совпадение)

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

Предположим, у вас есть список цветов, и вы хотите узнать количество цветов, в названии которых содержится слово «синий». Поскольку эти цвета можно написать несколькими разными способами, мы вводим «*синий*» в качестве критериев поиска =СЧЕТЕСЛИ(B2:B8;»*синий*»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с частичным совпадением

Звездочка (*) используется в функции СЧЕТЕСЛИ для поиска ячеек с любой последовательностью ведущих и конечных символов, как показано в приведенном выше примере. Если вам нужно сопоставить какой-либо один символ, введите вместо него знак вопроса (?) , например, =СЧЁТЕСЛИ(A2:A7;»ст?л»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с подстановочным знаком

В данном случае функция СЧЕТЕСЛИ вернет значение 2, так как найдет «стол» и «стул».