Функция суммирования в excel
Сумма в Excel: от простого к эффективному
В прошлом посте мы рассмотрели подсчет значений в Эксель. А сегодня мы узнаем, как в Excel посчитать сумму значений. Эта задача может поставить в тупик, если структура таблицы сложная, или нужно сделать выборочное суммирование. Дочитайте этот до конца, вас ждёт описание новой уникальной функции с отличным функционалом.
Как считают сумму значений новички? Пишут формулу, где через «+» перечисляют все слагаемые и остаются довольными результатом. Но это работает, если слагаемых 5-10. А как быть, если их больше?
Самый простой способ – выделить мышью все слагаемые и посмотреть результат в строке состояния.
Сумма в строке состояния
Такой способ хорош, если нужно узнать сумму, без использования в других формулах и таблицах. А если результат сложения нужно отобразить на листе – можно пользоваться функциями суммирования.
Функции суммирования в Эксель
Простейшая функция суммирования выглядит так: =СУММ(аргумент1 ; аргумент2;…) . Её можно использовать, когда есть перечень ячеек, массив, несколько массивов для сложения. Аргументами могут быть числа, ссылки на ячейки с числами, диапазоны. В следующем примере я просуммировал оклады работников отделов Экономики и Продаж в некой организации:
Функция СУММ
Если нужно посчитать сумму произведений неких чисел, пользуйтесь функцией =СУММПРОИЗВ(массив1; [массив2];…) . Функция перемножит соответствующие элементы массивов, а результаты умножения просуммирует. Понятно, что все массивы, заданные в функции, должны иметь одинаковое количество строк и столбцов.
В примере ниже есть оклады работников в у.е. и курсы валюты. Нужно посчитать суммарный оклад всех работников в валюте их страны. Т.е. формула будет иметь вид: =СУММПРОИЗВ(Оклады ; Курсы) . Функция умножит каждый оклад на курс и сложит все произведения друг с другом:
Сумма произведений в Эксель
Суммирование с условием в Excel
Часто нужно просуммировать только те значения, которые отвечают какому-то условию. Для этого используем функцию =СУММЕСЛИ(Диапазон, Критерий, [Диапазон суммирования]) . Как видим, для нее можно задать 3 аргумента:
- Диапазон – массив, в котором происходит проверка условия. Это обязательный аргумент;
- Критерий – условие для отбора значений (обязательный аргумент). Ячейки из «Диапазона» будут проверены на соответствие этому критерию. Если вы ищете равенство какому-то числу – просто запишите это число в качестве аргумента. Остальные критерии заключаются в кавычки. Например:
- 12 – значение равно двенадцати
- «>12» — значение больше двенадцати
- «Обувь» — значение равно «Обувь»
- Диапазон суммирования – массив значений, которые будут просуммированы. Он должен иметь столько же строк и столбцов, сколько в «Диапазоне». Это необязательный аргумент. Если он не задан, просуммированы будут значения из массива «Диапазон».
В этом примере я сложил оклады всех сотрудников отдела логистики:
Сумма с условием
А что, если нужно задать несколько условий? Эксель прекрасно справится с такой задачей, используем функцию:
СУММЕСЛИМН(диапазон суммирования ; диапазон условия1; условие1; диапазон условия2 ; условие2 ; …) .
Конечно, диапазоны суммирования и условий должны иметь одинаковое количество столбцов и строк, иначе формула выдаст ошибку. В примере я просуммировал зарплату только работников отдела экономики с окладом выше 100 у.е.:
Сумма с несколькими условиями
Мощная и гибкая функция АГРЕГАТ
А теперь представляю вам обещанную новинку. Функция АГРЕГАТ впервые появилась в Excel 2010 и сразу решила многие мои проблемы. Что если нужно посчитать сумму только видимых ячеек? Функция СУММ сложит все ячейки диапазона, даже если они скрыты. Что делать?
А если массив содержит ошибки расчетов, которые нужно игнорировать? Эти и многие другие задачи решает функция: =АГРЕГАТ(номер функции ; параметр ; массив;…) . Функция имеет аргументы:
- Номер функции – укажите что именно нужно подсчитать. Например, количество значений, сумму, максимум и т.д. При вводе формулы появится подсказка:
Функция Агрегат, первая подсказка
- Параметр – даёт возможность выбрать данные, которые функция будет игнорировать. Например, скрытые ячейки, ошибки и др. При вводе так же будет подсказка:
Функция Агрегат, вторая подсказка
- Массив – та группа ячеек, для которой производится расчет
- Прочие параметры – в зависимости от значения первого аргумента, функция может попросить у вас дополнительные аргументы
Описывать функцию агрегат можно долго, но главное – вовремя узнать о её существовании, и начать свои эксперименты. Она вас не раз удивит и сэкономит множество времени, пользуйтесь!
Прямо сейчас оставлю вас наедине со всеми описанными функциями. Попрактикуйтесь немного, чтобы они остались в вашей памяти.
А следующие посты снова очень важные – это функции поиска и выбора данных. Если вы читаете этот пост – обязательно прочтите и следующий, это будет очень полезно!
Не забыли подписаться на блог? Сделайте это прямо сейчас, чтобы всегда первыми читать новые статьи!
Функция СУММ
В этом курсе:
Функция сумм добавляет значения. Вы можете складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трех видов.
=СУММ(A2:A10) Суммирует значения в ячейках A2:10.
=СУММ(A2:A10;C2:C10) Суммирует значения в ячейках A2:10, а также ячейки C2: C10.
Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8.
Это второе число для сложения. Можно указать до 255 чисел.
Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.
Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.
Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:
А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.
Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел
Допустим, вы используете формулу такого вида:
Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.
Ошибка #ССЫЛКА! при удалении строк или столбцов
При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.
Формулы не обновляют ссылки при вставке строк или столбцов
При вставке строки или столбца формула не обновляется — в нее не включается добавленная строка, тогда как функция СУММ будет автоматически обновляться (пока вы не вышли за пределы диапазона, на который ссылается формула). Это особенно важно, когда вы рассчитываете, что формула обновится, но этого не происходит. В итоге ваши результаты остаются неполными, и этого можно не заметить.
Функция СУММ — отдельные ячейки или диапазоны.
Используя формулу такого вида:
вы изначально закладываете в нее вероятность появления ошибок при вставке или удалении строк в указанном диапазоне по тем же причинам. Гораздо лучше использовать отдельные диапазоны, например:
Такая формула будет обновляться при добавлении и удалении строк.
Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.
Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.
Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).
Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).
Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.
Другие способы работы с датами описаны в статье Вычисление разности двух дат.
Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка «Итог», автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Формула суммы в Excel
Любое обучение всегда идет от простого к сложному. Когда-то и я не знал, как в Эксель посчитать сумму столбца. Примерно с этого начинали и другие пользователи. Однако все можно изучить, если приложить усилия и выделить время.
В этой статье рассмотрим самые азы, а именно, как в Экселе сплюсовать ячейки , и как автоматически посчитать сумму столбца.
«+» – оператор суммирования. Чтобы сплюсовать числа в двух или более ячейках, нужно вначале выделить ячейку для записи суммы, нажать «=», левой кнопкой мыши указать первую ячейку, нажать «+», указать вторую ячейку и т.д., после выделения последней ячейки нажимать Enter. Данная функция является наследием микрокалькуляторов, когда нужно было набирать и плюсовать каждое значение в отдельности.
На мониторе суммирование плюсиком выглядит примерно так:
Если бы мы имели дело с небольшим количеством данных, то на этом обзор можно было бы закончить. Однако на практике обычно имеют дело с большими массивами данных. Складывать плюсиком будет очень затруднительно. Поэтому существует формула суммы в Эксель, позволяющие несколькими движениями подбить итог у таблицы с сотней строк.
Функция СУММ (SUM) в Excel
СУММ – самая простая и часто используемая функция. Синтаксис очень простой.
число1;число2;… – это ссылки на ячейки или на целый диапазон.
Если диапазон суммирования имеет разрывы, то нужные участки следует выделять, удерживая клавишу Ctrl.
Так как функция суммирования используется очень часто, то есть несколько способов ее вызвать. Стандартный вариант – через Мастер функций. Для вызова Мастера функций нужно стать в ячейку (то есть сделать ее активной), где будет прописана формула, затем нажать кнопку fx (слева от строки формул) и выбрать нужную функцию. На рисунке видно, где что нажимать:
Кому такой способ не нравится, в панели инструментов на закладках Главная и Формулы предусмотрена специальная кнопочка — Автосумма.
Использовать автосумму лучше тогда, когда диапазон суммирования является неразрывным, и ячейка для суммирования находится снизу или справа от диапазона суммирования. Иначе придется вручную поправлять диапазон, что не есть большая проблема.
Однако истинные мастера Excel выбирают горячие клавиши. Автосумму можно вызвать комбинацией клавиш Alt + = (Альт и равно). Ниже показано, что происходит на экране при использовании автосуммы: активировали ячейку B21, нажали Alt + = (или кнопку на панели).
Диапазон автоматически выделился – это видно по контуру моргающей рамочки. Формула суммирования появилась как в ячейке, так и в строке формул. Осталось нажать Enter и все – сумма готова. Теперь вы знаете, как в Экселе суммировать столбец или отдельные ячейки.
В видео ниже показан трюк применения автосуммы с помощью горячих клавиш.
Функция сумм в Excel и примеры ее использования
Функция СУММ относится к категории: «Математические». Нажмите комбинацию горячих клавиш SHIFT+F3 для вызова мастера функций, и вы быстро ее там найдете.
Использование этой функции существенно расширяет возможности процесса суммирования значений ячеек в программе Excel. На практике ее рассмотрим возможности и настройки при суммировании нескольких диапазонов.
Как в таблице Excel посчитать сумму столбца?
Просуммируем значение ячеек A1, A2 и A3 с помощью функции суммирования. За одно и узнаем, для чего используется функция сумм.
- После ввода чисел перейдите на ячейку A4. На закладке инструментов «Главная» выберите инструмент «Сумма» в разделе «Редактирование» (или нажмите комбинацию горячих клавиш ALT+=).
- Диапазон ячеек распознан автоматически. Адреса ссылок уже введенные в параметры (A1:A3). Пользователю остается только нажать Enter.
В результате в ячейке A4 отображается результат вычисления. Саму функцию и ее параметры можно увидеть в строке формул.
Примечание. Вместо использования инструмента «Сумма» на главной панели, можно в ячейку A4 сразу ввести функцию с параметрами вручную. Результат будет одинаковый.
Поправка автоматического распознания диапазонов
При введении функции с помощью кнопки на панели инструментов или при использовании мастера функций (SHIFT+F3). функция СУММ() относится к группе формул «Математические». Автоматически распознанные диапазоны не всегда являются подходящими для пользователя. Их можно при необходимости быстро и легко поправить.
Допустим, нам нужно просуммировать несколько диапазонов ячеек, как показано на рисунке:
- Перейдите в ячейку D1 и выберите инструмент «Сумма».
- Удерживая клавишу CTRL дополнительно выделите мышкой диапазон A2:B2 и ячейку A3.
- После выделения диапазонов нажмите Enter и в ячейке D4 сразу отобразиться результат суммирования значений ячеек всех диапазонов.
Обратите внимание на синтаксис в параметрах функции при выделении нескольких диапазонов. Они разделены между собой (;).
В параметрах функции СУММ могут содержаться:
- ссылки на отдельные ячейки;
- ссылки на диапазоны ячеек как смежные, так и несмежные;
- целые и дробные числа.
В параметрах функций все аргументы должны быть разделены точкой с запятой.
Для наглядного примера рассмотрим разные варианты суммирования значений ячеек, которые дают один и тот же результат. Для этого заполните ячейки A1, A2 и A3 числами 1, 2 и 3 соответственно. А диапазон ячеек B1:B5 заполните следующими формулами и функциями:
При любом варианте мы получаем один и тот же результат вычисления – число 11. Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.
Одновременное суммирование столбцов
В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.
Заполняете столбцы, так как показано на рисунке:
- Выделите диапазон A1:B3 и удерживая клавишу CTRL так же выделите столбец D1:D3.
- На панели «Главная» щелкните по инструменту «Сумма» (или нажмите ALT+=).
Под каждым столбцом автоматически добавилась функция СУММ(). Теперь в ячейках A4; B4 и D4 отображается результат суммирования каждого столбца. Это самый быстрый и удобный метод.
Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.
