logo

Блог Александра Томма

О том, как заставить Microsoft Office работать на Вас

Сумма в Excel: от простого к эффективному

Суммирование в Excel Дата: 5 марта 2016 Категория: Excel
Поделиться, добавить в закладки или распечатать статью

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

Как считают сумму значений новички? Пишут формулу, где через «+» перечисляют все слагаемые и остаются довольными результатом. Но это работает, если слагаемых 5-10. А как быть, если их больше?

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

Сумма в строке состояния Excel
Сумма в строке состояния

Такой способ хорош, если нужно узнать сумму, без использования в других формулах и таблицах. А если результат сложения нужно отобразить на листе – можно пользоваться функциями суммирования.

Функции суммирования в Эксель

Простейшая функция суммирования выглядит так: =СУММ(аргумент1 ; аргумент2;…). Её можно использовать, когда есть перечень ячеек, массив, несколько массивов для сложения. Аргументами могут быть числа, ссылки на ячейки с числами, диапазоны. В следующем примере я просуммировал оклады работников отделов Экономики и Продаж в некой организации:

Функция СУММ в Эксель
Функция СУММ

Если нужно посчитать сумму произведений неких чисел, пользуйтесь функцией =СУММПРОИЗВ(массив1; [массив2];…). Функция перемножит соответствующие элементы массивов, а результаты умножения просуммирует. Понятно, что все массивы, заданные в функции, должны иметь одинаковое количество строк и столбцов.

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

Сумма произведений в Excel
Сумма произведений в Эксель

Суммирование с условием в Excel

Часто нужно просуммировать только те значения, которые отвечают какому-то условию. Для этого используем функцию =СУММЕСЛИ(Диапазон, Критерий, [Диапазон суммирования]). Как видим, для нее можно задать 3 аргумента:

  • Диапазон – массив, в котором происходит проверка условия. Это обязательный аргумент;
  • Критерий – условие для отбора значений (обязательный аргумент). Ячейки из «Диапазона» будут проверены на соответствие этому критерию. Если вы ищете равенство какому-то числу – просто запишите это число в качестве аргумента. Остальные критерии заключаются в кавычки. Например:
    • 12 – значение равно двенадцати
    • «>12» — значение больше двенадцати
    • «Обувь» — значение равно «Обувь»
  • Диапазон суммирования – массив значений, которые будут просуммированы. Он должен иметь столько же строк и столбцов, сколько в «Диапазоне». Это необязательный аргумент. Если он не задан, просуммированы будут значения из массива «Диапазон».

В этом примере я сложил оклады всех сотрудников отдела логистики:

Сумма с условием в Эксель
Сумма с условием

А что, если нужно задать несколько условий? Эксель прекрасно справится с такой задачей, используем функцию:

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

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

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

Мощная и гибкая функция АГРЕГАТ

А теперь представляю вам обещанную новинку. Функция АГРЕГАТ впервые появилась в Excel 2010 и сразу решила многие мои проблемы. Что если нужно посчитать сумму только видимых ячеек? Функция СУММ сложит все ячейки диапазона, даже если они скрыты. Что делать?

А если массив содержит ошибки расчетов, которые нужно игнорировать? Эти и многие другие задачи решает функция: =АГРЕГАТ(номер функции ; параметр ; массив;…). Функция имеет аргументы:

  1. Номер функции – укажите что именно нужно подсчитать. Например, количество значений, сумму, максимум и т.д. При вводе формулы появится подсказка:
Функция Агрегат, первая подсказка
Функция Агрегат, первая подсказка
  1. Параметр – даёт возможность выбрать данные, которые функция будет игнорировать. Например, скрытые ячейки, ошибки и др. При вводе так же будет подсказка:
Функция Агрегат, второй аргумент
Функция Агрегат, вторая подсказка
  1. Массив – та группа ячеек, для которой производится расчет
  2. Прочие параметры – в зависимости от значения первого аргумента, функция может попросить у вас дополнительные аргументы

Описывать функцию агрегат можно долго, но главное – вовремя узнать о её существовании, и начать свои эксперименты. Она вас не раз удивит и сэкономит множество времени, пользуйтесь!

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

А следующие посты снова очень важные – это функции поиска и выбора данных. Если вы читаете этот пост – обязательно прочтите и следующий, это будет очень полезно!

Не забыли подписаться на блог? Сделайте это прямо сейчас, чтобы всегда первыми читать новые статьи!

Поделиться, добавить в закладки или распечатать статью

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

8 комментариев

  1. Спасибо, информация очень интересная и полезная

    Ответить
    1. Здравствуйте, Надежда. Спасибо за Ваш положительный отзыв о моей работе! Заходите на блог еще, задавайте вопросы, если что-то осталось непонятным!

      Ответить
  2. Здравствуйте Александр. Есть к Вам интересный вопрос по поводу функции СУММЕСЛИМН: как написать функцию так чтобы она работала для одного и того же диапазона только из разных листов той же книге? Пробовал так: =СУММЕСЛИМН(‘Лист1:Лист2’Диапазон суммирование; и так далее..). Для одного листа работает прекрасно, а вот уже для 2 листов выдаёт ошибку. Помогите!

    Ответить
    1. Пробовал так: =СУММЕСЛИМН(‘Лист1:Лист2’!Диапазон суммирование; и так далее..)

      Ответить
      1. Здравствуйте, Михаил. Формула возвращала ошибку, потому что Вы не соблюли ее синтаксис. Напомню: =СУММЕСЛИМН(диапазон суммирования ; диапазон условия1; условие1; диапазон условия2 ; условие2 ; …). То есть, в первом аргументе Вы указываете в каком массиве выполнять суммирование, во втором — массив для условия №1, в третьем — условие №1, в четвертом — массив для условия №2, в пятом — условие №2 и т.д. Массивы для условий могут быть на разных листах, но должны иметь одинаковые размеры. Например, следующая формула дала у меня верный результат:
        =СУММЕСЛИМН($B$2:$B$11;A2:A11;1;Лист2!A6:A15;2)

        Ответить
        1. Здравствуйте Александр.=СУММЕСЛИМН(‘Лист1:Лист2’!Диапазон суммирование… Тут я имел ввиду что диапазон суммирование должен быть и для Листа 1 и для Листа 2. Отдельно всё получается но вместе нет.

          Ответить
          1. =СУММЕСЛИМН(‘366:BOBCAT’!$K$17:$K$700;’366:BOBCAT’!$B$17:$B$700;B7). Пробовал так. ‘366:BOBCAT’! — это листы с 366 до BOBCAT (примерно 70 листов). Можно конечно писать формулу для каждого листа отдельно, но это нерационально. Помогите!

  3. Разобрался. Создал список всех листов и с его помощью нужные мне диапазоны. Решил что хватает просто функции СУММЕСЛИ + СУММПРОИЗВ, получилось так: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(«‘»&$W$4:$W$65&»‘!$B$17:$B$200»);B4;ДВССЫЛ(«‘»&$W$4:$W$65&»‘!$K$17:$K$200»)))

    Ответить