Суммирование в Excel, используя функции СУММ и СУММЕСЛИ
В этом уроке мы не будем рассматривать, как посчитать сумму в Excel при помощи оператора сложения, автосуммы и прочих инструментов. Сегодня мы разберем всего две функции: СУММ и СУММЕСЛИ. Спешу Вас обрадовать, их функционала достаточно, чтобы решать практически все возможные вопросы суммирования в Excel.
Функция СУММ – простое суммирование ячеек в Excel
Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:
Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:
Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:
Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.
В следующем примере функция СУММ складывает весь столбец А, а это значений:
Следующая формула подсчитывает сумму всех ячеек, содержащихся на рабочем листе Лист1. Чтобы данная формула не вызвала циклической ошибки, ее необходимо использовать на другом рабочем листе Excel (отличном от Лист1).
Функция СУММ может принимать до аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:
Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:
Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:
Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:
Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.
СУММЕСЛИ &#; условная сумма в Excel
Функция СУММЕСЛИ позволяет подсчитать условную сумму в Excel, т.е. сумму ячеек, которые удовлетворяют определенному критерию. Функция СУММЕСЛИ может содержать только один критерий.
Например, следующая формула суммируем только положительные числа диапазона A1:A Обратите внимание, что условие заключено в двойные кавычки.
В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:
Меняем условие, меняется и результат:
Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.
Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?
На этот случай функция СУММЕСЛИ припасла третий необязательный аргумент, который отвечает за диапазон, который необходимо просуммировать. Т.е. по первому аргументу функция проверяет условие, а третий подлежит суммированию.
В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:
Нажав Enter получим результат:
Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.
Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!
Функция сумм в 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 заполните следующими формулами и функциями:
- =A1+A2+A3+5;
- =СУММ(A1:A3)+5;
- =СУММ(A1;A2;A3)+5;
- =СУММ(A1:A3;5);
- =СУММ(A1;A2;A3;5).
При любом варианте мы получаем один и тот же результат вычисления – число Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.
Одновременное суммирование столбцов
В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.
Заполняете столбцы, так как показано на рисунке:
- Выделите диапазон A1:B3 и удерживая клавишу CTRL так же выделите столбец D1:D3.
- На панели «Главная» щелкните по инструменту «Сумма» (или нажмите ALT+=).
Под каждым столбцом автоматически добавилась функция СУММ(). Теперь в ячейках A4; B4 и D4 отображается результат суммирования каждого столбца. Это самый быстрый и удобный метод.
Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.
Функция СУММ
Функция СУММ суммирует значения. Вы можете складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трех видов.
Пример
=СУММ(A2:A10) Суммы значений в ячейках A
=СУММ(A2:A10;C2:C10) Суммы значений в ячейках A, а также в ячейках C2:C
СУММ(число1;[число2];…)
Имя аргумента | Описание |
|---|---|
number1 Обязательно | Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8. |
число2– Необязательно | Это второе число для сложения. Можно указать до чисел. |
Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.
Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.
Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:
=,93+,90+,23
А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.
Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел
Допустим, вы используете формулу такого вида:
Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.
Ошибка #ССЫЛКА! при удалении строк или столбцов
При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.
Формулы не обновляют ссылки при вставке строк или столбцов
При вставке строки или столбца формула не обновляется — в нее не включается добавленная строка, тогда как функция СУММ будет автоматически обновляться (пока вы не вышли за пределы диапазона, на который ссылается формула). Это особенно важно, когда вы рассчитываете, что формула обновится, но этого не происходит. В итоге ваши результаты остаются неполными, и этого можно не заметить.
Функция СУММ — отдельные ячейки или диапазоны.
Используя формулу такого вида:
вы изначально закладываете в нее вероятность появления ошибок при вставке или удалении строк в указанном диапазоне по тем же причинам. Гораздо лучше использовать отдельные диапазоны, например:
Такая формула будет обновляться при добавлении и удалении строк.
Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.
Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.
Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между и для вычисления заработной платы, можно воспользоваться формулой =(""-"")*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).
Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (, т. е. 5 часов 36 минут).
Дополнительные сведения см. в статье Сложение и вычитание значений времени
Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.
Другие способы работы с датами описаны в статье Вычисление разности двух дат.
Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией profexcel.ru Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка "Итог", автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Дополнительные сведения о функции СУММ
Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию
Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким условиям
Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию
Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Математические и тригонометрические функции
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Использование функции СУММ для суммирования чисел в диапазоне
Для суммы чисел в диапазоне (группе ячеек) можно использовать простую формулу, но функцию СУММ проще использовать при работе с несколькими числами. Например, =СУММ(A2:A6) реже имеет ошибки при вводе, чем =A2+A3+A4+A5+A6.
Вот формула, использующая два диапазонаячеев: =СУММ(A2:A4;C2:C3) суммирует числа в диапазонах A2:A4 и C2:C3. Чтобы получить итоговую сумму в , нажмите ввод.
Чтобы создать формулу:
Введите в ячейку =СУММ и открываю скобки (.
Чтобы ввести первый диапазон формул, который называется аргументом (частью данных, которую нужно выполнить), введите A2:A4 (или выберите ячейку A2 и перетащите ее через ячейку A6).
Введите запятую (,), чтобы отделить первый аргумент от следующего.
Введите второй аргумент C2:C3 (или перетащите его, чтобы выбрать ячейки).
Введите закрываюю скобки )и нажмите ввод.
Каждый аргумент может быть диапазоном, числом или ссылками на отдельные ячейки, разделенными запятой.
=СУММ(A2:A4;;)
=СУММ(;A3:A4;C2:C3)
=СУММ(;;;C2:C3)
=СУММ(A2;A3;A4;;)
Совет: Если вам нужно свести столбцы или строки чисел рядом друг с другом, используйте авто сумму чисел.
Попробуйте попрактиковаться
Если вы хотите поиграть с образцом данных, воспользуйтесь некоторыми данными.
Чтобы узнать, как работает функция СУММ, скопируйте таблицу ниже в таблицу и вкопируйте ее в ячейку A1.
Данные | ||
|---|---|---|
-5 | ||
15 | ||
30 | ||
'5 | ||
ИСТИНА | ||
Формула | Описание | Результат |
=СУММ(3;2) | Прибавляет 3 и 2. | 5 |
=СУММ("5"; 15; ИСТИНА) | Прибавляет 5, 15 и 1. Текстовое значение "5" сначала переводится в число, а логическое значение ИСТИНА — в число 1. | 21 |
=СУММ(A2:A4) | Суммы значений в ячейках A2-A4. | 40 |
=СУММ(A2:A4; 15) | С суммой значений в ячейках A2–A4, а затем к результату прибавляет | 55 |
=СУММ(A5;A6;2) | Суммы значений в ячейках A5 и A6, а затем 2 к результату. Так как не числовые значения в ссылках не переводятся, значение в ячейке A5 ('5) и значение в ячейке A6 (ИСТИНА) будут рассматриваться как текст, поэтому значения в этих ячейках игнорируются. | 2 |
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция СУММ() и операция сложения (+) в EXCEL
Есть ли разница между сложением значений с помощью функции СУММ( ) , английская версия SUM(), и операцией сложения (+)? Оказывается разница есть.
Не смотря на неудобство применения операции сложения для диапазонов (приходится вручную вводить много слагаемых, например =А1+А2+А3+…+А10 ), у нее есть одно существенное преимущество перед функцией СУММ() – перед сложением эта операция пытается преобразовать слагаемое в число. А функция СУММ() просто проигнорирует нечисловые значения, но что еще хуже - проигнорирует числовые значения в текстовом формате.
Проведем эксперимент. В ячейку А1 введем число 10, а в А2 введем значение ’20 (перед значением введем апостроф, который говорит EXCEL, что введено текстовое значение, подробнее в статье Автоматическое преобразование формата ячейки при вводе текстовых данных (часть 1) .
Формула = СУММ(A1:A2) вернет результат 10, а =A1+A2 вернет 30!
Другой вариант сложения - замечательная функция СУММПРОИЗВ() . Эта функция сочетает преимущества обеих формул: удобство и подсчет чисел в текстовой формате. Записав формулу =СУММПРОИЗВ(1*A1:A2) , получим результат 30, т.к. из-за операции умножения на 1, EXCEL преобразует (там где это возможно) все текстовые значения в числа (с функцией СУММ() в прямую такой фокус не пройдет - ее нужно будет вводить как формулу массива ).
Если преобразование в число невозможно (например, если вместо ’20 введено слово Яблоки ), то формулы =СУММПРОИЗВ(1*A1:A2) и =А1+А2 вернут ошибку #ЗНАЧ!, а СУММ() – правильное в данном случае значение 10.