Назначение математической функции СУММ в Excel скачать пример

СУММ в Excel – это математическая функция, используется для суммирования числовых значений. Аргументами могут быть отдельные значения, ссылки на ячейки, диапазон или несколько отдельных диапазонов.

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

Как работает функция СУММ? Схематически её синтаксис имеет следующий вид:

Рассмотрим самый простой пример работы функции СУММ. У нас есть список с прибылью по нескольким точкам продаж и первое задание – посчитать сумму прибыли по всем точкам. В ячейке Е10 пишем формулу. Для этого в качестве аргументов выбираем весь диапазон со значениями (С3:С8):

Суммировать можно так же и несколько диапазонов, например, нам нужна сумма диапазонов С3:С5 и С6:С8:

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

Как использовать функцию СУММ с массивами

Теперь рассмотрим, как ещё мы можем упростить себе выполнение задач с помощью СУММ. У нас есть форма с информацией о строительных материалах, их цене, количестве и стоимости доставки. Нам нужно посчитать полную сумму покупки товаров. Имея определённые знания в EXCEL пользователь может выполнить эту задачу следующим образом – в ячейке G17 написать формулу расчета «Цена*Количество+Доставка» и скопировать до последней позиции:

Затем в ячейке G22 написали функцию СУММ, выбрали весь диапазон G17:G21 и у нас готова общая стоимость покупки товаров с учетом цены за товар, количества и доставки:

Чтобы выполнить расчет покупки не прибегая к дополнительному столбцу, в котором мы считали стоимость по отдельному товару, используем функцию СУММ и формулу массива:

в ячейке С24 выбираем функцию СУММ;
затем выбираем массив D17:D21, ставим символ умножения;
выбираем массив Е17:Е21;
добавляем (+) массив F17:F21, закрываем скобку.

Обязательный момент! После того как мы заполнили формулу, её выполнение производим не через Enter, а последовательным нажатием кнопок Ctrl+Shift+Enter. Если вы нажмете Enter – возвратится ошибка. В этот раз у нас появились фигурные скобки, которые означают, что наша формула выполнила суммирование с помощью правил массива:

Можем сравнить результаты двух примеров (ячейки G22 и С24), чтобы убедиться, что формулы работают одинаково.

Как рассчитать нарастающий итог с помощью функции СУММ

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

Начало диапазона (С31) у нас будет неизменным, поскольку мы указали для этой ячейки абсолютные ссылки. Сейчас конец диапазона у нас та же ячейка С31, поскольку пока что нам нужна сумма продаж только за январь. После этого нам нужно скопировать формулу до конца года. При копировании по столбцу будет меняться только конец диапазона. Таким образом, будет происходить расчет прибыли за месяцы с начала года и мы получим накопительные значения:

Суммирование по выбранным критериям

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

выбираем массив В48:В59. В этом массиве будет производиться поиск по указанному условию;
ссылаемся на ячейку с условием («Англия», В62);
ставим символ умножения, выбираем массив Е48:Е59, в котором находятся числовые значения;
последовательно нажимаем клавиши ctrl + shift + enter для корректного отображения результата. Таким образом мы «говорим» программе, используется формула массива.

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

Можно использовать дополнительные условия. Кроме критерия для поиска по странам, мы можем указать, что хотим найти стоимость товаров определенного поставщика, например, «Baugut». Для удобства в отдельной ячейке (B63) укажем поставщика, чтобы потом ссылаться на эту ячейку. Алгоритм действий для построения формулы точно такой же, как в предыдущем примере, только добавим еще один массив для поиска С48:С59 и искомый критерий «Baugut» в ячейке В63:

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

Как использовать функцию СУММ с другими функциями

Комбинирование функций EXCEL позволяет расширить их функционал. В следующем примере мы рассмотрим простое использование функции СУММ вместе с функцией СМЕЩ для того чтобы понимать логику действия этих функций вместе. На этот раз нам понадобится таблица, в которой находится информация об отделах и их доходах в период с 2014 по 2022 года. Наша задача – найти сумму доходов отдела А и отдела В в 2014-м году. Если вы знакомы с функцией СМЕЩ, тогда знаете, что её аргументы выделяют область необходимых нам значений: от ячейки В71 делаем шаг на две строки вверх и три столбца вправо (первая пара цифр -2;3). Сейчас мы «стали» на ячейку Е69. Затем создаем границы для того массива, данные которых нам нужно суммировать: 1 ряд и 2 столбца (вторая пара цифр). Таким образом мы выбрали диапазон Е69:F69. Теперь полученную формулу вложим в СУММ:

Мы получили результат 100 (отдел А имеет за 2014 год доход в размере 65, а отдел В - 35). Теперь рассчитаем общий доход, например, для отделов В, С, D за 2017-2021 года. Смещаем нашу функцию таким же образом:

аргумент 1 – ячейка, от которой будет происходить смещение;
аргумент 2 – смещение на 1 ряд вниз;
аргумент 3 – смещение на 4 столбца вправо;
аргумент 4 – количество рядов в массиве (5);
аргумент 5 – количество столбцов в массиве (3).

Проверка вводимых значений с помощью СУММ

Для выполнения этого примера нам понадобится форма о затратах и инструмент «Проверка данных». Нам нужно установить ограничение на объем совокупных затрат – сумма при добавлении новой позиции (ячейка С100) не должна превышать 25 000:

На вкладке «Данные» - группа «Работа с данными» выбираем инструмент «Проверка данных». У нас откроется окно «Проверка вводимых значений». В появившемся окне в условиях проверки выбираем из списка Тип данных – Другой. Пишем нашу формулу:

Затем переходим на вкладку «Сообщение об ошибке» и указываем текст, который хотим видеть при несоблюдении условия, например, «Лимит превышен!»:

Когда мы введем значение в ячейку С100 или изменим его, запустится проверка данных. Пока сумма по массиву С95:С100 будет меньше 25 000, проверка будет успешной. Вводим число 1000 и видим, что ничего не происходит:

Теперь введем значение 4000 и у нас появится окно с ошибкой, которую мы указывали во вкладке «Сообщение об ошибке»:

download file Скачать практические примеры использования функции СУММ в Excel

Произошло это, потому что инструмент заново посчитал сумму значений по всем статьям затрат и она превысила порог 25 000.