Как сделать сумму ячеек в excel. Подсчет суммы в столбце в программе Microsoft Excel

Функция СУММ относится к категории: «Математические». Нажмите комбинацию горячих клавиш SHIFT+F3 для вызова мастера функций, и вы быстро ее там найдете.

Использование этой функции существенно расширяет возможности процесса суммирования значений ячеек в программе Excel. На практике ее рассмотрим возможности и настройки при суммировании нескольких диапазонов.

Как в таблице Excel посчитать сумму столбца?

Просуммируем значение ячеек A1, A2 и A3 с помощью функции суммирования. За одно и узнаем, для чего используется функция сумм.

В результате в ячейке A4 отображается результат вычисления. Саму функцию и ее параметры можно увидеть в строке формул.

Примечание. Вместо использования инструмента «Сумма» на главной панели, можно в ячейку A4 сразу ввести функцию с параметрами вручную. Результат будет одинаковый.

Поправка автоматического распознания диапазонов

При введении функции с помощью кнопки на панели инструментов или при использовании мастера функций (SHIFT+F3). функция СУММ() относится к группе формул «Математические». Автоматически распознанные диапазоны не всегда являются подходящими для пользователя. Их можно при необходимости быстро и легко поправить.

Допустим, нам нужно просуммировать несколько диапазонов ячеек, как показано на рисунке:

1. Перейдите в ячейку D1 и выберите инструмент «Сумма».
2. Удерживая клавишу CTRL дополнительно выделите мышкой диапазон A2:B2 и ячейку A3.
3. После выделения диапазонов нажмите 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 можно одновременно просуммировать несколько смежных и несмежных столбцов.

Заполняете столбцы, так как показано на рисунке:

1. Выделите диапазон A1:B3 и удерживая клавишу CTRL так же выделите столбец D1:D3.
2. На панели «Главная» щелкните по инструменту «Сумма» (или нажмите ALT+=).

Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.

Ранее я описал, как с помощью пользовательской функции найти . К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) . На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате , примеры в формате

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

Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).

Рис. 2. Суммирование ячеек, отвечающих одному условию

Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

БДСУММ($А$1:$А$101,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Читая книгу Джона Уокенбаха я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы ; в окне Макрос , выделите строку СумЦветУсл , и нажмите Выполнить . Запуститься макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Код процедуры

Sub СумЦветУсл() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Запрос диапазона Set UserRange = Application.InputBox(_ Prompt:="Выберите диапазон суммирования", _ Title:="Выбор диапазона", _ Default:=ActiveCell.Address, _ Type:=8) " Запрос критерия Set CriterionRange = Application.InputBox(_ Prompt:="Выберите критерий суммирования", _ Title:="Выбор критерия", _ Default:=ActiveCell.Address, _ Type:=8) " Суммирование "правильных" ячеек For Each i In UserRange If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color Then SumColor = SumColor + i End If Next MsgBox SumColor End Sub

Рассмотрим, как проводить суммирование в Excel по условиям . Складывать в Excel можно, не только, по столбцам и строкам, но и выбирать для сложения данные из таблицы по разным условиям.
Как установить простое условие для подсчета данных, смотрите в статье "Функция "ЕСЛИ" в Excel ".
Здесь рассмотрим функцию «СУММЕСЛИМН» в Excel , которая суммирует числа, удовлетворяющие нескольким условиям, выбирает определенные данные для сложения по нескольким условиям. Можно указать до 127 условий.
Есть еще функция "СУММЕСЛИ", но в ней можно указать одно условие. Смотрите статью "Функция "СУММЕСЛИ" в Excel" .
Итак, у нас такая таблица.
Нам нужно посчитать сумму проданных яблок, которые продал менеджер Васечкин покупателю Иванову.
В ячейку F5 устанавливаем формулу.
На закладке «Формулы» в разделе «Библиотека функций» выбираем «Математические». В появившемся списке нажимаем на функцию «СУММЕСЛИМН». Появится такое окно.
В этом окне строки для одного диапазона и одного условия. Но, можно написать до 127 условий.
Итак, заполняем диалоговое окно так.
В строке «Диапазон суммирования» пишем диапазон D2-D8 – это столбец с суммами покупок.
В строке «Диапазон условия1» пишем диапазон столбца с менеджерами. У нас - B2-B8.
Обратите внимание .
Когда мы поставили курсор в эту строку, автоматически появилась новая строка для условий. Если условий много, то появляется полоса прокрутки, с помощью которой, переходим в следующие окна условий.
В строке «Условие1» пишем фамилию менеджера. Мы написали – Васечкин.
В строке «Диапазон условия2» пишем диапазон столбца покупателей – А2:А8.
В строке «Условие2» написали фамилию покупателя – Иванов.
Двигаем поле условий полосой прокрутки, пишем следующее условие.
В строке «Диапазон условий3» пишем диапазон С2:С8.
В строке «Условие3» пишем – «яблоки».
Нажимаем «ОК». Получился такой результат.

Формула в ячейке А5 такая.
=СУММЕСЛИМН(D2:D8;B2:B8;"Васечкин";A2:A8;"Иванов";C2:C8;"яблоки")
Если нужно посчитать на какую сумму продал менеджер Васечкин всего разных фруктов Иванову, то напишем такую формулу с двумя условиями.
=СУММЕСЛИМН(D2:D8;B2:B8;"Васечкин";A2:A8;"Иванов")
Если нужно посчитать сколько продали яблок Иванову менеджеры Васечкин и Сергеева, то формулу напишем так.
=СУММЕСЛИМН(D2:D8;B2:B8;"Васечкин";A2:A8;"Иванов";C2:C8;"яблоки")+
СУММЕСЛИМН(D2:D8;B2:B8;"Сергеева";A2:A8;"Иванов";C2:C8;"яблоки")
Пишем эту формулу так. Сначала вставляем функцию «СУММЕСЛИМН» по Васечкину. Нажимаем «ОК». В строке формул в конце формулы ставим знак «+» и вставляем функцию «СУММЕСЛИМН» по Сергеевой. Получается, мы сложили функции отдельно по каждому менеджеру.
Внимание!
В условиях этой функции «СУММЕСЛИМН» количество ячеек диапазона суммирования и количество ячеек диапазона условий должно быть одинаковое. Например, все диапазоны с 3 по 8 ячейки. Если будет диапазон суммирования с 3 по 10 ячейки. А диапазон условия с 3 по 8, то формула посчитает не верно. Это нужно учитывать при установлении функции «СУММЕСЛИМН».
Есть еще одна функция в Excel, которая считает выборочно по условию. В ней можно указывать разной длины диапазоны, но условие можно указать только одно. Подробнее о применении функции «СУММЕСЛИ», о символах, которые можно вставить в критерий функции, смотрите в статье «

Самый быстрый и, безусловно, самый простой способ вывести сумму в Excel – это сервис «Автосумма». Для его использования необходимо выделить ячейки, в которых содержатся значения, которые нужно сложить, а также пустую ячейку непосредственно под ними. Во вкладке «Формулы» необходимо нажать на знак «Автосумма», после чего в пустой ячейке появляется результат сложения.

Этот же способ доступен и в обратной последовательности. В пустой ячейке, где необходимо вывести результат сложения, необходимо поставить курсор и кликнуть на знак «Автосумма». После нажатия в ячейке формируется формула со свободным диапазоном суммирования. С помощью курсора необходимо выделить диапазон ячеек, значения которых нужно сложить, после чего нажать Enter.

Ячейки для сложения также можно определять и без выделения курсором. Формула суммы имеет вид: «=СУММ(B2:B14)», где в скобках приводится первая и последняя ячейка диапазона. Меняя значения ячеек в Excel-таблице, можно корректировать диапазон суммирования.

Сумма значений для сведения

Excel позволяет не только суммировать значения для каких-то конкретных целей, но и автоматически считает выделенные ячейки. Такие сведения могут быть полезны в тех случаях, когда задача не связана с суммированием чисел, а заключается в необходимости посмотреть значение суммы на промежуточном этапе. Чтобы сделать это, не прибегая к написанию формулы, достаточно выделить нужные ячейки и в командной строке программы в самой нижней части кликнуть правой клавишей мыши на слово «Готово». В открывшемся контекстном меню напротив графы «Сумма» будет выведено значение всех ячеек.

Формула простого сложения в Excel

В случаях, когда значения для сложения рассредоточены по всей таблице можно использовать простую формулу сложения в Excel-таблице. Формула имеет следующую структуру:

Для создания формулы в свободной ячейке, где должна быть выведена сумма, ставится знак «равно». Excel автоматически реагирует на это действие, как на формулу. Далее при помощи мыши необходимо выделить ячейку с первым значением, после которого ставится знак «плюс». Далее все остальные значения также вводятся в формулу через знак сложения. После того, как цепочка значений набрана, нажимается кнопка Enter. Использование этого метода оправдано при сложении небольшого количества чисел.

Формула СУММ в Excel

Несмотря на практически автоматизированный процесс использования формул сложения в Excel, порой проще самостоятельно. В любом случае, их структуру необходимо знать в тех случаях, когда работа ведется в документе, созданном другим человеком.

Формула СУММ в Excel имеет следующую структуру:

СУММ(A1;A7;C3)

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

Знак «;» служит для определения ячейки, в свою очередь знак «:» устанавливает диапазон ячеек.

Важный нюанс – при подсчете значений всего столбца не нужно устанавливать диапазон, пролистывая до конца документа, который имеет границу в свыше 1 миллиона значений. Достаточно ввести формулу с следующими значениями: =СУММ(B:B), где буквы обозначают тот столбец, который необходимо посчитать.

Excel также позволяет вывести сумму всех без исключения ячеек документа. Для этого формулу необходимо вводить в новой вкладке. Допустим, что все ячейки, суммы которых нужно посчитать, расположены на Листе1, в таком случае формула должна писаться на Листе2. Сама структура имеет следующий вид:

СУММ(Лист1!1:1048576), где последнее значение является цифровым значением самой последней ячейки Листа1.

Эксель, Excel, программа электронных, таблиц уже давно стала классикой жанра. Имеется много аналогов, для разных операционных систем, с разной степенью платности/бесплатности и различного типа открытости кода. То есть, можно вообще взять и наваять самому программу электронных таблиц на основе какого-то открытого для всех исходника и благополучно ею пользоваться. Но достижения в этой области уже установились, и волей-неволей придётся снова обращаться к продвунувшемуся дальше других пакету Microsoft Office, частью которого и является сейчас программа Microsoft Excell.

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

Возможности Эксел

Размеры таблицы Excel последних версий просто гигантские. Столбцов в ней 16384, а строк 1048576, то есть общее количество ячеек 17 179 869 184 штук (2 34). Понятно, что для реальных вычислений не всегда нужно такое необозримое самовычисляющееся полотно (в Экселе оно называется «Рабочий лист»), но ничего. Зато программа вместит все мыслимые размеры и формы полей данных и легко произведёт все необходимые вычисления во всём их количестве. И, на удивление, Excel всё посчитает молниеносно быстро.

Умение работать с офисом уже давно перешло в разряд обычной грамотности, но здесь вспомним, чем отличаются в таблице простые данные от вычисляемых (exeсutable). Ячейка может содержать всё, что угодно: число, буквы, двоичные и шестнадцатиричные данные, даты, и всё это программа распознаёт как исходные данные. За одним исключением. Если поставить в начале значения знак равенства, то программа поймёт, что дальше следует формула , и будет прочитывать её по правилам специального языка формул. Если формула написана без ошибок, то после окончания её ввода — нажатия клавиши ENTER, или просто выхода из данной ячейки и перехода в другую — будет немедленно выполнено её вычисление, и на месте формулы будет показан результат.

Внешне же, посмотрев на таблицу, содержащую большое количество как данных, так и формул, мы можем и не отличить одни ячейки от других. Везде будут данные, только одни окажутся исходными, введёнными именно так — цифрами, буквами, и т.д., а другие вычисленными.

Распознать всё-таки можно, если «ходить» по таблице Excel , кликая мышкой то там, то тут.

Когда мы оказываемся в ячейке с формулой, то формулу можно увидеть выше таблицы в специальной строчке , которая так и называется, «строка формул» — такая широкая строчка, слева от которой стоит обозначение f x . Если мы «стоим» в ячейке с формулой, то в этой широкой строке и окажется эта формула, начинающаяся знаком =. Если пользователь захочет в какую-то ячейку вставить формулу, первым делом введёт знак равенства, а потом уже может вводить числа, функции, адреса ячеек, и т.д.

Ещё два способа подсчёта сумм

Помощь программы в выборе диапазона суммирования

Кроме того, перед вводом формулы (и выполнением сразу вслед за этим вычисления по ней) программа сначала предложит выбрать, какие ячейки суммировать. Программа посмотрит на данные в таблице вокруг ячейки, «где мы стоим» и, если увидит, что в ближайшей ячейке слева находятся числовые данные, она предложит просуммировать их все до начала — то есть всё количество слева от нашей формулы до первого нечислового данного. Что в формулу сразу и введёт.

Но нас интересует сейчас получение значения в ячейке Excel суммированием значений в столбцах. Если сверху от нашей ячейки есть данные, то суммировать программа предложит вверх. Причём может заметить не только число, рядом стоящее, но и расположенные гораздо дальше

В любом случае Excel сначала «предлагает» формулу для вычисления, и мы можем её изменить так, как хочется нам. Например, поменять диапазон вычисляемых данных .

Это делается просто:

А можно через «;» перечислить и диапазоны — если их несколько.

Как сделать сумму видимой постоянно

И данные не обязаны находиться слева или сверху от нашей формульной ячейки. Нажав на знак суммы, а потом протянув мышь вниз по нужной нам табличке с данными, мы получим сумму, расположенную сверху от исходных данных. Это ценно, когда разбивают таблицу на области. Это делается во вкладке «Вид/Окно», например, кнопкой «Закрепить области» . В результате таблица будет разбита на две или четыре области, левые и верхние из которых неподвижны, а часть полотна таблицы, уходящую вправо и вниз, можно будет листать. Таким образом легко увидеть все данные, если таблица большая — не влазит в экран программы — а суммарные цифры при этом непрерывно наблюдать, если заранее разместить их на этих самых неподвижных областях.

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

Самым изящным способам просуммировать весь столбец таблицы — от начала (ячейка i1) до конца (ячейка i1048576), где i — номер суммируемого столбца, будет формула =СУММА(i:i)

Когда вводим диапазон с помощью программы, надо просто кликнуть мышкой на адресную кнопку столбца — столбец весь будет выделен, а его диапазон i:i и станет аргументом функции СУММА в нашей формуле. Единственное условие при этом — надо сделать так, чтобы формула в нашей ячейке не содержала адреса самой этой нашей ячейке . Нигде, ни в перечислении отдельных ячеек, ни в каком либо из диапазонов, указанных в скобках после названия функции СУММА. То есть, когда мы суммируем в Excel весь столбец по формуле =СУММА(i:i), то наша сумма не должна располагаться в этом же столбце .

Потому что тогда формуле пришлось бы автоматически суммировать со значениями столбца ещё и своё собственное значение. А так считать невозможно: прибавив к общей сумме значение в этой же ячейке, программа изменит это значение и захочет опять к сумме прибавить это новое значение. И так до бесконечности, это называется «рекурсивное вычисление» . Поэтому, лучше вывести ячейку с суммой за пределы столбца, это будет примерно вот так.