Excel суммирование по несколько я

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

Простое сложение в Excel

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

Суммирование ячеек по условию

Excel также предусматривает суммирование с учетом условия. Функция применяется, когда необходимо просуммировать только те значения, которые соответствуют заданному условию. Для нее возможно задать три аргумента:

• Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
• Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как ">5". Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие "Иванов";
• Диапазон суммирования – массив значений, которые нужно сложить.

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

Функция «СУММЕСЛИ»

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

1. По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
2. Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
3. Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
4. Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
5. В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
6. В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
7. Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
8. Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.

В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».

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

Функция «СУММЕСЛИМН»

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

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

1. Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
2. В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».
3. В появившемся окне в строке «Диапазон суммирования» введите ячейки, который находятся в столбце «Количество».
4. В «Диапазон условия» выделите все ячейки в столбце «Товар».
5. В качестве первого условия пропишите значение «Яблоки».
6. После этого необходимо задать второе условие и диапазон для него. В данной таблице столбец «Покупатели» является значением для диапазона. Выделите его в строку, затем в втором условии пропишите фамилию Евдокимов.
7. Нажмите на кнопку «ОК», чтобы программа посчитала, сколько яблок купил Евдокимов.

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

Итог

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

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

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

Ниже на рисунке находится часть списка стран с показателями ВВП за период начиная от 2000-х и до 2005-х годов. Допустим нам необходимо суммировать показатель ВВП Бразилии за 2001-2004 год. Для суммирования значений по двум или нескольким условиям удобно использовать функцию СУММЕСЛИМН. В данном случае нам нужно учитывать:

1. Первое условие – только страна Бразилия.
2. Второе условие – суммировать ВВП только за 2001 и 2004 год.

Формула вычисления суммы показателей ВВП для указанной страны за определенный период лет:

Первым аргументом функции СУММЕСЛИНМ является диапазон ячеек, содержащие суммированные значения. Следующие аргументы объединены в пары по схеме Диапазон_условия1 вместе c Услвоие1. Количество аргументов функции – это всегда непарное число учитывая их последовательность. Первая пара критериев (второй и третий аргумент) являются обязательными для заполнения. Без них данная функция ничем не отличалась бы от обычной функции СУММ. Дополнительные последующие пары критериев не обязательны для заполнения. А их максимальное количество пар аргументов может быть достигнуто до 127-ми. То есть максимальное количество всех аргументов в функции СУММЕСЛИМН 127*2+1= 255.

В выше приведенном примере суммируются значения столбца C только тех ячеек, которые соответствуют значениям в столбцах A и B при выполнении определенных условий отбора. Условием для отбора ячеек в столбце A является совпадение значений с ячейкой F1, где указывается название страны для отбора. Год проверяется по двум условиям определяющих начальный год и конечный период времени. Начальный год записан в ячейке F2, а конечный – F3. Ссылки на эти ячейки через символ амперсант (&) объединены с оператором сравнения «больше или равно» (>=) и «меньше или равно» (<=) – соответственно. Значение ячейки столбца D будет учитываться в итоговой сумме только тогда, когда будут исполнены все три условия.



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

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

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

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

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

Принцип работы функции СУММПРОИЗВ основан на табличных массивах. Это такие таблицы, которые создаются в памяти и представляют сбой ничто иное как списки значений. В функции СУММПРОИЗВ каждый элемент таблицы сравнивается с соответственным ему значением. Например, в выше описанном примере сначала проверяется значение каждой ячейки в диапазоне A2:A20 на соответствие значению, записанному в F1. То есть функция ищет адреса ячеек, которые содержать значение «Бразилия». Результатом выполненных сравнений является очередная таблица, которая содержит список значений ИСТИНА и ЛОЖЬ, в соответствии с каждой ячейкой исходной таблицы. Эти результирующие логические значения в памяти называются элементами. Большая часть из них это естественно ложные элементы (в данном примере), но 6 элементов соответствуют ячейкам содержащих строку «Бразилия» и получают результативное значение ИСТИНА.

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

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

ЛОЖЬ*ИСТИНА*ИСТИНА*394,48

Другими словами:

(название страны это не Бразилия)*(год больше чем 2001)*(год меньше чем 2004)*(текущее значение ВВП)

Или так:

0*1*1*394,48=0

Первый ноль – это результат проверки первого табличного массива, который вернул логическое значение ЛОЖЬ – не равно Бразилия! Так как одно из умножаемых числе является нулем в результате простых арифметических операций также получаем 0.

В случае с 15-ой строкой листа складывается совсем другая ситуация. Все результаты проверки значений возвращают ИСТИНА, а значит =1. На этот раз при проверки первой ячейки выявлено, что она содержит текст «Бразилия». При проверке года на «больше или равно» >=2001 снова получаем ИСТИНА. Эта же ячейка с годом теперь проверяется по третьему условию «меньше или равно 2005» и в результате ИСТИНА. Таким образом выполняется следующая операция:

1*1*1*559,37=559,37

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

Это итоговый результат вычислений для 15-ой строки листа 14-ой строки таблицы в памяти. По такому же принципу происходят операции и для остальных строк таблицы. По каждой строке выводится свой итоговый результат (0 или показатель ВВП) и суммируется функцией СУММПРОИЗВ. Данная функция весьма сложная и работает медленнее чем СУММЕСЛИМН, но ее все еще можно использовать в новых версиях Excel так как опытные пользователи привыкли к ней. Так же это дает возможность открывать старые версии файлов без ошибок в формулах. А при некоторых редких случаях она может быть даже более удобной других функций, поэтому стоит знать ее принципы работы.

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

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

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

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

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

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

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

Описание аргументов:

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

Результат расчетов:

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



Выборочное суммирование по условию в Excel

Пример 2. В таблице указаны данные об отгрузках трех товаров в различные страны на протяжении трех месяцев. Определить суммарный доход от отгрузок товара №2 в Казахстан за июнь и август.

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

Для расчета итоговой суммы используем формулу:

Результат расчетов:

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

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

Пример 3. Используя таблицу из второго примера определить суммарной прибыли от поставок товара №1 в Китай и Грузию на протяжении всего периода (трех месяцев).

Для нахождения искомого значения используем формулу массива (для ввода жмем CTRL+SHIFT+Enter):

Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.

Примечание: если критерии переданы в качестве константы массива для двух и более пар диапазон_условияN; условиеN, результат выполнения формулы будет некорректным.

Полученное значение:

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

Функция имеет следующую синтаксическую запись:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

Описание аргументов:

• диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
• диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
• условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «<>0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.

Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.

Примечания:

1. Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
2. В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
3. Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
4. Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
5. Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
6. Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
7. Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.