Суммеслимн в excel примеры с датами

Сумма за месяц игнорируя год

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

В показанном примере, формула в Н5:

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

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

Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот:

Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так:

Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом:

Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю. СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше

СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.

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

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

Динамический диапазон суммирования по условию в Excel

Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).

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

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

• C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
• C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
• “>5” – первое условие отбора значений из указанного выше диапазона;
• B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
• “А*” – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).

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

Сумма, если Дата больше, чем

В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.

В примере показано, ячейка H4 содержит формулу:

Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.

Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).

Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&)

Функция СУММЕСЛИ при условии соответствия тексту

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

1. В этот раз помимо диапазона суммируемых ячеек определите и те, где присутствуют надписи, попадающие в условие.

Начните запись функции с ее обозначения точно так же, как это уже было показано выше.

В первую очередь введите диапазон надписей, поставьте ; и задайте условие. Тогда это выражение в синтаксическом формате обретет примерно такой вид: A2:A25;«Сентябрь»;.

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

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

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

Почему функция СУММЕСЛИ может не работать?

Существует несколько распространенных ошибок, по которым функция СУММЕСЛИ перестает работать. 

1. Диапазоны должны указываться в виде ссылок на диапазон, а не массива. Эта ошибка бывает довольно редко, но это возможно, поскольку многие новички не полностью понимают разницу между диапазоном и массивом.
2. Если суммируются значения из других листов или книг, которые закрыты на момент использования формулы.
3. Диапазон данных и поиска отличаются по размеру.

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

Структура функции

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

Значения аргументов следующие:

1. В диапазон суммирования записываете столбец или строку, по которой будет вычисляться сумма значений.
2. Диапазон условия 1 нужен для задания массива, в котором будут применены параметры отбора данных.
3. Условие 1 является дополнением к предыдущему аргументу. На этой позиции указывается критерий отбора в диапазоне условия 1.
4. Затем идут дополнительные аргументы, с помощью которых можно уточнить параметры расчета суммы значений.

Функция Excel ЕСЛИМН

Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

Как видно, запись формулы выглядит гораздо проще и понятнее.

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

Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция СУММЕСЛИМН с несколькими условиями

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

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

Начните запись со стандартного ее объявления =СУММЕСЛИМН, откройте скобки и напишите, какой диапазон ячеек будет суммироваться. Не забудьте закрыть объявление аргумента знаком ;.

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

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

Полностью строка этой функции имеет вид =СУММЕСЛИМН(C2:C25;A2:A25;«Сентябрь»;B2:B25;«>29»), а вам остается лишь подстроить ее под себя и получить правильный результат. Может показаться, что объявлять каждый аргумент и не запутаться в символах сложно, но при раздельном подходе никаких проблем возникнуть не должно.

Подробнее: 10 популярных математических функций Microsoft Excel

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”>=”&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9 >;
• Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B >;
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Задача3 (2 критерия Дата)

Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”

Чтобы вывести условия отбора в текстовой строке используейте формулу =”Объем продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)

В последней формуле использован Пользовательский формат .

Функция СУММЕСЛИМН в Excel

Добрый день друзья!

Я вот решил что уделяю мало внимания функциям, которые используются в Excel и поэтому решил не откладывать это дело в долгий ящик, а написать цикл статей о различных нужных функциях. Первой «синичкой» станет статья, функция СУММЕСЛИМН в Excel. Я уже описывал и рассказывал в деталях о функциях СУММ, ЕСЛИ и СУММЕСЛИ, а вот пришло время розширить знания о возможностях суммирования еще и функцией СУММЕСЛИМН. Что можно о ней сказать, чем же она отличается от других функций суммирования и чем она может оказаться вам полезной. Все ранее рассматриваемые функции (за исключением, функции ЕСЛИ) поддерживают поиск по 1 аргументу, а в функции ЕСЛИ, аж целых 7, тогда как функция СУММЕСЛИМН в Excel поддерживает поиск по 127 критериям, а это согласитесь веский аргумент в поиске. Сразу замечу, что эта функция была введена в работу с версии Excel 2007, поэтому для пользователей более ранних версий, данная статья будет только ознакомительная. Я в принципе смутно себе представляю себе задачу, где нужно использовать такую массу критериев, но всё же это говорит о том, что функции достойна того, что бы ее знали и умели пользоваться. А для этого надо знать, как минимум, ее орфографию, рассмотрим подробнее:

=СУММЕСЛИМН( диапазон для суммирования; диапазон где условия; наше условие; ; и т.д. до 127 раз), где,

диапазон для суммирования – это тот диапазон, где находятся данные, которые нужно суммировать, когда наши условия будут выполнены;

диапазон где условие – это диапазон откуда выбирается данные согласно нашему критерию для дальнейшего суммирования;

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

Внимание! Функция СУММЕСЛИМН в Excel умеет и может работать со знаками подстановки такими как «*» — для замены любого количества символов и знаком «?» — для замены любого одного символа, а также функция успешно использует операторы отношения, такие как «=», «>», «

Выборочные вычисления по одному или нескольким критериям

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

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

• Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
• Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

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

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

Математические функции в Excel

В данной категории содержится порядка 80 функций. Здесь можно найти операторы, способные просчитать значения электронной таблицы любого формата. В Excel «суммесли» часто встречается, а набор тригонометрических функций подходит для определенного круга пользователей. В чем же выражается суть арифметического оператора, у которого есть аналог в последних версиях программного продукта «суммеслимн»? Его задача суммировать значения, которые попадают под определенные критерии.

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

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

Пример 4

Даны 3 столбца, в которых указана дата, тип расходов, сумма на конкретный вид траты. Необходимо сделать выборку по двум условиям:

1. Критерий не включает слово «Прочие».
2. Условие содержит слово «Расходы*». Знак «*» говорит о том, что после введенной информации идет продолжение в ячейке столбца В.

В качестве самостоятельного задания пользователь может ввести условие по дате за конкретный период: за месяц или неделю. Чтобы это сделать, необходимо в кавычках указать период, к примеру, «>01.01.2017». Вторым условием выступает: «<31.01.2017».

Функции Excel всегда подробно описаны в справке Microsoft. Если возникают вопросы, даже опытный пользователь не брезгует применить встроенную помощь программы.

Просмотры:
13

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

Функция СУММЕСЛИМН появилась начиная с Excel 2007 и выше. Само название функции говорит о том, что данная функция позволяет суммировать значения если совпадает множество значений.

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

У нас есть список сотрудников, выбирая город нам необходимо посчитать сумму подключенных услуг по сотрудникам и видам услуг. То есть нам необходимо заполнить вот такую таблицу (то что выделено желтым).

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

Для наглядности я перенес данную таблицу на один лист с исходными данными.

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН( диапазон_суммирования ; диапазон_условий1 ; условия1 ;;…) диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646

Далее указываются условия по которым необходимо просуммировать услуги. У нас три условия:

1. должна совпадать фамилия сотрудника;
2. должна совпадать услуга;
3. должен совпадать город.

диапазон_условий1 — первое условие у нас сотрудники и диапазон условий это столбец с именами ФИО сотрудников A2:A646

условия1 — это сам сотрудник, так как мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3

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

Продолжим, следующая условие это услуга

диапазон_условий2 — это столбец с услугами D2:D646

условия2 — это ссылка на услугу 1, то есть H2

Вот как должна выглядеть наша формула на текущий момент:

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2

Добавляем третье условие по городам

диапазон_условий3 — диапазон условий по городам это столбец «Город клиента» и диапазон B2:B646

условия3 — это ссылка на город в раскрывающемся списке G1

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

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2; B2:B646; G1 )

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

Во-первых все диапазоны условий у нас не двигаются и постоянны поэтому закрепим их с помощью знака доллара (выделить данный диапазон в формуле и нажать клавишу F4):

Диапазон суммирования у нас так же постоянный E2:E646 → $E$2:$E$646

Так же условия3 по городу G1 y нас всегда находится только в ячейке G1 и не должен смещаться при протягивании, поэтому так же закрепляем данную ячейку

G1 → $G$1

Услуги (условия2) при протягивании вправо должны меняться по столбцам, а вот строка при протягивании вниз не должна меняться, поэтому закрепляем только строку

H2 → H$2

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

G3 → $G3

Итоговая формула будет выглядеть следующим образом

=СУММЕСЛИМН( $E$2:$E$646 ; $A$2:$A$646 ; $G3 ; $D$2:$D$646 ; H$2; $B$2:$B$646 ;$ G$1 )

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

Функция СУММЕСЛИ при условии неравенства

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

1. Определитесь с диапазоном ячеек, попадающих под рассмотрение формулой, у нас это будет прибыль за месяц.

Начните запись с ее указания в поле ввода, написав СУММЕСЛИ.

Создайте открывающую и закрывающую скобку, где введите диапазон выбранных ячеек, например C2:C25. После этого обязательно поставьте знак ;, который означает конец аргумента.

Откройте кавычки и в них укажите условие, что в нашем случае будет >300000.

Как только произойдет нажатие по клавише Enter, функция активируется. На скриншоте ниже видно, что условию >300000 соответствуют лишь две ячейки, следовательно, формула суммирует их числа и отображает в отдельном блоке.

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

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

• диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
• диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
• , условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

Похожие записи:

Как полностью удалить программу с компьютера в windows 10 Оптимизация windows 10 Как подключиться к удаленному рабочему столу в windows 10 через rdp Как узнать, какая операционная система на компьютере? Как открыть файл xls онлайн Internet explorer для windows