Функция сумм в 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).
При любом варианте мы получаем один и тот же результат вычисления – число 11. Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.
Одновременное суммирование столбцов
В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.
Заполняете столбцы, так как показано на рисунке:
- Выделите диапазон A1:B3 и удерживая клавишу CTRL так же выделите столбец D1:D3.
- На панели «Главная» щелкните по инструменту «Сумма» (или нажмите ALT+=).
Под каждым столбцом автоматически добавилась функция СУММ(). Теперь в ячейках A4; B4 и D4 отображается результат суммирования каждого столбца. Это самый быстрый и удобный метод.
Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.
Функция в экселе суммирования переменного числа слагаемых. Суммирование столбцов в Microsoft Excel
Многие работая с электронными таблицами EXCEL в определённый момент сталкиваются с вопросом «как в EXCEL сложить числа в ячейках по определённому условию». В один из солнечных дней я также столкнулся с данным вопросом, но на просторах интернета не нашёл ответ на данный вопрос, однако я настойчивый и решил изучить справку программы, которая меня и натолкнула на мысль, а далее методом проб и ошибок я составил формулу, и как оказалось формула элементарная.
Используемый формат валюты - это формат, установленный по умолчанию на вашем компьютере. Если аргументы функции следующие. Например, вам может потребоваться узнать, сколько раз любое значение происходит один, два, три, четыре раза и т.д. другими словами, вам нужно условное подсчет простого счета.
К счастью, проблема только звучит запутанно. Вы можете работать с любым набором данных или. Но в этом случае мы условно подсчитываем счетчики, а не значения, показывает простой набор данных. Первым шагом является подсчет количества раз, когда значение возникает в одном наборе данных.
Всё началось с того, что я решил учитывать свои ежемесячные расходы и для этого создал таблицу, которую приложил к данной статье, ведь и вам она может пригодиться.
Теперь постараюсь подробно расписать принцип создания формулы. У меня есть в отчёте детальная статистика и сводный отчёт. В детальной статистике я вписываю свои ежедневные расходы, а в сводном отчёте считается сумма расходов по определённым категориям и общая сумма расходов.
Где столбец ссылается на столбец, который содержит значения, которые вы пересчитываете, а значение - ячейка в том же столбце.
- У Майка и Сьюзан есть только одна запись в наборе данных.
- У Джона и Джорджа есть две записи в наборе данных.
Условно подсчет количества
Другими словами, вам нужно знать, сколько записей происходит один, два, три раза и т.д.
- У двух человек, Майка и Сьюзан, есть по одной записи.
- Два человека, Джон и Джордж, имеют две записи.
- Нет ни одного из трех или четырех записей.
- Один человек, «Рассвет», имеет пять записей.
- В нем нет более пяти записей.
Для примера возьмём категорию расходов «Покупки в магазинах». Нам надо, чтобы EXCEL находил все затраты по данной категории в детальной статистике, суммировал расходы по данной категории и записывал полученную сумму в ячейку D10 .
Сначала запишем готовую формулу, которую вставляем в ячейку D10 , а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):
Однако матрица не дает полной картины. Мы можем ясно видеть, что хотя бы одно значение имеет пять записей, но матрица не отражает этого. Матрица так же хороша, как и строка заголовка. Мы можем расширить эту строку, но насколько этого будет достаточно?
Матрица могла бы это сделать, если бы вы заранее знали, как далеко ее расширить. Это решение поможет вам собрать информацию, которая может иметь смысл, если она представлена в другом формате. Этот столбец был вдохновлен читателем. У вас есть другое решение? Не отправляйте файлы, если они не запрашиваются, первоначальные запросы на получение справки, которые поступают с прикрепленными файлами, будут удалены непрочитанные. Вы можете отправить скриншоты своих данных, чтобы помочь уточнить ваш вопрос.
=СУММЕСЛИ ($G$5:$G$300 ;("Покупки в магазинах ");$H$5:$H$300 )
Цветом выделены различные условия, чтобы было наглядней. Разберём по порядку. В процессе описания смотрите на картинку выше, чтобы было понятней. Делая снимок специально были захвачены буквы столбцов и цифры строк. Итак, приступаем.
- СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
- $G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300 ;
- («Покупки в магазинах ») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
- $H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.
Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5: H300 , для которых соответствующие значения из диапазона G5: G300 равны «Покупки в магазинах» и записывает результат в ячейку D10 .
При обращении ко мне, будьте как можно более конкретными. Например, Пожалуйста, устранить мою книгу и исправить то, что «неправильно», вероятно, не получило ответа, но «Можете ли вы сказать мне, почему эта формула не возвращает ожидаемые результаты?» мог бы.
Пожалуйста, укажите приложение и версию, которые вы используете. Щелкните правой кнопкой мыши и выберите «Формат» в списке. В меню формата выберите «Выравнивание» и установите текст под углом 45 градусов. Нажмите на значок, который говорит «Стоп-листы».
Это гарантирует, что ваш первый столбец и строка остаются видимыми. Введите следующие теги в строке 1. Введите информацию о продуктах, из которых вы будете следовать, начните с номера строки. Введите код, имя, количество купленных и проданных продуктов.
Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию .
Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $ . К примеру, в ячейке D10 у нас вписана формула:
=СУММЕСЛИ(G5:G300;("Покупки в магазинах");H5:H300)
Эта формула вычитает количество, продаваемое каждым продуктом, из купленной суммы. Это покажет вам оставшийся инвентарь для каждого элемента. В предыдущих версиях вы должны нажать «Формат» и перейти в «Условный формат». Выберите «Вставить специальный» в появившемся меню. Выберите «Форматы» в появившемся диалоговом окне. Первая функция, которую каждый должен знать, - выполнить базовую арифметику. В следующий момент вы увидите сумму двух ячеек. Вы можете использовать эту функцию и большинство функций, перечисленных здесь, чтобы захватить как можно больше ячеек - просто выберите все нужные вам ячейки.
Далее мы хотим выводить сумму обедов в ячейке D11 . Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11 . Благодаря этому формула будет вставлена в D11 , но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:
=СУММЕСЛИ(G6:G301;("Покупки в магазинах");H6:H301)
Произошло смещение. Если мы скопируем формулу в D12 , то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $ . Такие особенности EXCEL.
Опять же, вы можете использовать указатель мыши, чтобы щелкнуть и перетащить выбранную группу ячеек, или использовать элемент управления для выбора отдельных ячеек. Это текстовая функция, используя ее, вы присоедините две или более текстовых строк в строке.
Вы хотите узнать количество дней между двумя датами в электронной таблице? Реверсирование заказа ячейки даст вам положительное число. Вы хотите видеть текущую дату и время, когда вы открываете рабочий лист? Эта функция позволяет вам округлить числа. Если вам нужно больше прямого контроля над округлением вверх или вниз, для этого есть функция.
Таблица «Учёт ежемесячных расходов» -
Иногда перед пользователями Экселя становится вопрос, как сложить общую сумму значений нескольких столбцов? Задача ещё больше усложняется, если данные колонки расположены не единым массивом, а разрознены. Давайте выясним, как просуммировать их различными способами.
Суммирование колонок в Экселе происходит по общим принципам сложения данных в этой программе. Конечно, указанная процедура имеет некоторые особенности, но они являются всего лишь частностью общей закономерности. Как и любое другое суммирование в данном табличном процессоре, сложение столбцов можно произвести, применив простую арифметическую формулу, использовав встроенную функцию Excel СУММ или автосумму.
Способ 1: использование автосуммы
Прежде всего, давайте рассмотрим, как произвести суммирование колонок в Экселе при помощи такого инструмента, как автосумма.
Для примера возьмем таблицу, в которой представлена ежедневная выручка пяти магазинов за семь дней. Данные по каждому магазину расположены в отдельном столбце. Нашей задачей будет узнать общий доход этих торговых точек за указанный выше период. Для этой цели как раз и нужно будет сложить колонки.
Способ 2: использование простой математической формулы
Теперь давайте посмотрим, как можно произвести суммирование столбцов таблицы, применив для этих целей только простую математическую формулу. Для примера будем использовать ту же таблицу, которую применяли для описания первого способа.
Нельзя не заметить, что данный способ занимает большее время и требует больших усилий, чем предыдущий, так как предполагает для вывода общей величины дохода ручное перещелкивание каждой ячейки, которую нужно сложить. Если в таблице очень много строк, то указанная процедура может быть утомительной. В то же время у данного способа имеется одно неоспоримое преимущество: результат можно выводить в любую пустую ячейку на листе, которую выберет пользователь. При использовании автосуммы подобной возможности нет.
На практике два этих способа можно совмещать. Например, подбивать итоги в каждой колонке в отдельности с помощью автосуммы, а общую величину выводить путем применения арифметической формулы в той ячейке на листе, которую выберет пользователь.
Способ 3: применение функции СУММ
Недостатки двух предыдущих способов можно исключить при использовании встроенной функции Excel под названием СУММ . Предназначение данного оператора состоит как раз в суммировании чисел. Он относится к категории математических функций и имеет следующий простой синтаксис:
СУММ(число1;число2;…)
В качестве аргументов, количество которых может достигать 255, выступают суммируемые числа или адреса ячеек, где они расположены.
Посмотрим, как применяется данная функция Эксель на практике на примере все той же таблицы выручки по пяти торговым точкам за 7 дней.
Но иногда бывают случаи, когда нужно вывести общий результат по всем торговым точкам без подведения промежуточных итогов для отдельных магазинов. Как оказывается, оператор СУММ и это может, причем решение данной задачи даже легче, чем применение предыдущего варианта данного способа.
Если рассматривать данный способ с чисто технической стороны, то мы сложили не столбцы, а весь массив. Но результат оказался тем же, как если бы складывали каждый столбец в отдельности.
Но существуют ситуации, когда нужно сложить не все колонки таблицы, а только определенные. Ещё больше задача усложняется, если они не граничат друг с другом. Давайте рассмотрим, как производится данный вид сложения при помощи оператора СУММ на примере все той же таблицы. Предположим, что нам нужно сложить только значения столбцов «Магазин 1» , «Магазин 3» и «Магазин 5» . При этом требуется, чтобы результат подсчитывался без выведения промежуточных итогов по колонкам.
Как видим, для сложения столбцов в Экселе существует три основных способа: использование автосуммы, математическая формула и функция СУММ . Наиболее простой и быстрый вариант – это использование автосуммы. Но он наименее гибкий и подойдет далеко не во всех случаях. Наиболее гибкий вариант – это применение математических формул, но он наименее автоматизированный и в некоторых случаях, при большом объеме данных, его воплощение на практике может занять значительное время. Использование функции СУММ можно назвать «золотой» серединой между этими двумя способами. Данный вариант относительно гибкий и быстрый.
Давайте решим все проблемы на Вашем компьютере?
Суммирование в Excel, используя функции СУММ и СУММЕСЛИ
В этом уроке мы не будем рассматривать, как посчитать сумму в Excel при помощи оператора сложения, автосуммы и прочих инструментов. Сегодня мы разберем всего две функции: СУММ и СУММЕСЛИ. Спешу Вас обрадовать, их функционала достаточно, чтобы решать практически все возможные вопросы суммирования в Excel.
Функция СУММ – простое суммирование ячеек в Excel
Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:
Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:
Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:
Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.
В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:
Следующая формула подсчитывает сумму всех ячеек, содержащихся на рабочем листе Лист1. Чтобы данная формула не вызвала циклической ошибки, ее необходимо использовать на другом рабочем листе Excel (отличном от Лист1).
Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:
Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:
Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:
Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:
Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.
СУММЕСЛИ – условная сумма в Excel
Функция СУММЕСЛИ позволяет подсчитать условную сумму в Excel, т.е. сумму ячеек, которые удовлетворяют определенному критерию. Функция СУММЕСЛИ может содержать только один критерий.
Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.
В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:
Меняем условие, меняется и результат:
Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.
Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?
На этот случай функция СУММЕСЛИ припасла третий необязательный аргумент, который отвечает за диапазон, который необходимо просуммировать. Т.е. по первому аргументу функция проверяет условие, а третий подлежит суммированию.
В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:
Нажав Enter получим результат:
Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.
Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!
.