Visits: 5396

Суммируем в Excel.

Операция суммирования на самом деле является, пожалуй, наиболее часто встречающейся операцией, выполняемой в Excel. Очень часто приходится находить общую сумму. Суммирование может проходить в самых разных вариантах.

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

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

Обычное суммирование.

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

Ее синтаксис несложен.

=СУММ(параметр1; параметр 2…)

При работе с функцией СУММ учитывайте следующие тонкости.

1. Параметрами могут выступать как отдельные числа и иные значения, так и отдельные ячейки, и диапазоны с ними.
2. В качестве ЗНАЧЕНИЙ параметров могут быть любые данные – числа, текст, даты, – но суммировать Excel будет только числа.
3. Если хотя бы одно значение в параметрах содержит ошибку, то и результат всей функции СУММ выдаст ошибку.
4. Всего можно указать до 255 параметров. Этим сама функция СУММ выгодно отличается от ее разновидности АВТОСУММА, часто применяемой новичками. АВТОСУММА находит только сумму значений в ячейках, расположенных рядом. При этом, если в диапазоне есть пустые ячейки или ячейки с текстом, то АВТОСУММА уже не складывает значения, находящиеся за ними.

 

Рисунок 1

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

Рисунок 2

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

Рисунок 3

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

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

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

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

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

=СУММЕСЛИ(B3:B70;”молоток”;C3:C70).

Обратите внимание, так как слово «Молоток» это текст, то мы взяли его в кавычки.

Данную формулу можно прочитать следующим образом: «Необходимо сложить значения всех ячеек, расположенных в диапазоне C3:C70 на позициях ячеек в диапазоне B3:B70, содержащих слово МОЛОТОК»

Или – так: «Если в ячейках диапазона B3:B70 находится слово МОЛОТОК, то соответствующие им по расположению ячейки диапазона C3:C70 просуммировать».

Что произойдет после того, как мы запустим на выполнение эту формулу, нажав Enter?

Excel начнет просматривать каждую ячейку в диапазоне B3:B70. Если он находит в очередной проверяемой ячейке слово «Молоток», то соответствующая ей по порядку ячейка из диапазона C3:C70 будет просуммирована. Если в очередной проверяемой ячейке в диапазоне B3:B70 слово «Молоток» не найдено, то ячейка из С3:С70 будет пропущена.

 

Рисунок 4

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

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

Рисунок 5

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

=СУММЕСЛИ(C3:C82;”>0″)

3. При указании критерия допускается применение подстановочных знаков. Так, знак звездочки «*» соответствует наличию или отсутствию любого количества произвольных символов. Если указать сочетание *ст, то Excel поймет, что нас интересуют любой текст, который заканчивается на сочетание «СТ», например – пост, помост, тост и прочие. Если указать «СТ*», то Excel будет искать слова, которые начинаются с СТ – студент, стол, стена. НУ а если ввести сочетание «*ст*», то будут найдены все ячейки, где вообще сочетание «СТ» есть, неважно – в начале, в середине или в конце. Вопросительный же знак говорит о возможном наличии или отсутствии одного произвольного символа. В частности, если указать такой вариант «СТ??», то из списка стол, стул, стена, студент, стекло, стеллаж программа отберет только слова Стол и Стул (так как было указано, что первые два знака должны быть СТ, но после них может быть НЕ БОЛЕЕ двух символов). Это удобно, если вы не знаете точное значение критерия.

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

=СУММЕСЛИ(B3:B82;”*внесение наличных*”;C3:C82)

Рисунок 6

 4. Если требуется вывести итоговую сумму на основе значений ячеек, где непосредственно используются подстановочные знаки, то указывая эти знак в качестве критерия, перед ними уазываем знак тильды (~)

=СУММЕСЛИ(I3:I18;”~*”;J3:J18)

Рисунок 7

5. Ну и наконец – не забываем, что если в качестве критерия указываем значение из списка в диапазоне, то диапазон проверки и диапазон суммирования в формуле закрепляют, используя абсолютную или смешанную адресацию. Изначально отсутствие закрепления на расчет не повлияет, но в дальнейшем при сортировке или ином изменении в порядке исходных данных будут проблемы с искажением результатов. Для закрепления достаточно выделит нужные диапазоны в формуле и нажать клавишу F4 в верхнем ряду клавиатуры. В примере ниже найден объем реализации в литрах для каждой номенклатурной группе ГСМ.

Рисунок 8

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

 

Рисунок 9

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

В следующем примере выполнялось суммирование по значениям из внешнего файла traning.xlsx

=СУММЕСЛИ([traning.xlsx]склад!$A$2:$A$1119;”*”&$A3&”*”;[traning.xlsx]склад!D$2:D$1119)

Рисунок 10

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

Рисунок 11

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

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

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

=СУММЕСЛИМН( диапазон суммирования; диапазон проверки на соответствие первому критерию ( то есть первому условию); первый критерий ( условие, которому должна соответствовать ячейка в диапазоне проверки первого критерия); диапазон проверки на соответствие второму критерию ( второму условию); второй критерий ( второе условие)… и так до 127 диапазонов проверки критериев и самих критериев).

Приведем пример работы СУММЕСЛИМН. Предположим следующее. Наименования товаров заданы в диапазоне B3:B50, количество упаковок каждого товара находится в графе С3:С50, а в диапазоне D3:D50 указаны соответствующие заданным позициям товара расценки. Наша задача – найти общее количество упаковок рубашек с ценой ниже 3000. Получаем следующую задачу:

«Найти общую сумму в диапазоне С3:С50, но при этом в диапазоне B3:B50 должно содержаться слово «рубашка», а в диапазоне D3:D50 значение должно быть меньше 3000». Итоговая формула будет выглядеть следующим образом.

=СУММЕСЛИМН(C3:C50;B3:B50;”рубашка”;D3:D50;”<3000″)

 

Рисунок 12

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

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

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

Подведем итоги.

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

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

В таких случаях используются формулы массива, а также функции БДСУММ, СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ. Функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ подробно рассмотрены на прошлых занятиях, а вот о работе с функциями БДСУММ, СУММПРОИЗВ и применении формул массива для суммирования в соответствии с условиями   поговорим в наших следующих материалах.

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

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