Эксель формула суммы столбца. Как посчитать сумму в Экселе

8 обычных методов как посчитать в Excel сумму столбца

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

Как суммировать весь столбец или строку.

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

Если поставить формулу суммы в G2

=СУММ(B:B)

то получим общую выручку по первому отделу.

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

Важное замечание! Ежели посреди чисел случаем окажется дата, то это окажет суровое влияние на корректность расчетов. Дело в том, что даты хранятся в Excel в виде чисел, и отсчет их начинается с 1900 года раз в день. Потому будьте внимательны, рассчитывая сумму столбца в Excel и используя его в формуле целиком.

Все произнесенное выше в полной мере относится и к работе со строками.

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

Суммируем спектр ячеек.

Важно научиться верно указать спектр данных. Вот как это сделать, ежели суммировать реализации за 1-й квартал:

Формула расчета смотрится так:

=СУММ(B2:D4)

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

В формуле поочередно перечисляем несколько диапазонов:

=СУММ(B2:D4;B8:D10)

Естественно, их может быть не два, а еще больше: до 255 штук.

Как вычислить сумму каждой N-ой строки.

В таблице размещены повторяющиеся с определенной периодичностью характеристики – реализации по отделам. Нужно рассчитать общую выручку по каждому из их. Сложность в том, что интересующие нас характеристики находятся не рядом, а чередуются. Представим, мы анализируем сведения о продажах 3-х отделов помесячно. Нужно найти реализации по каждому отделу.

Иначе говоря, необходимо, двигаясь вниз, взять каждую третью строчку.

Это можно сделать 2-мя способами.

Первый – самый обычной, «в лоб». Складываем все числа подходящего отдела обыкновенной математической операцией сложения. Смотрится просто, но представьте, ежели у вас статистика, представим, за 3 года? Придется обработать 36 чисел…

Второй метод – для наиболее «продвинутых», но зато универсальный.

Записываем

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C16)+1;3)=0;C2:C16))

И потом жмем комбинацию кнопок CTRL+SHIFT+ENTER, так как употребляется формула массива. Excel сам добавит к фигурные скобки слева и справа.

Как это работает? Нам нужна 1-я, 3-я, 6-я и т.д. позиции. При помощи функции СТРОКА() мы вычисляем номер текущей позиции. И ежели остаток от деления на 3 будет равен нулю, то значение будет учтено в расчете. В неприятном случае – нет.

Читайте также  Как в ворде составить формулу. Как сделать формулу в Word

Для такового счетчика мы будем применять номера строк. Но наше 1-ое число находится во 2-ой строке рабочего листа Эксель. Так как нужно начинать с первой позиции и позже брать каждую третью, а начинается спектр со 2-й строки, то к порядковому номеру её добавляем 1. Тогда у нас счетчик начнет считать с числа 3. Для этого и служит выражение СТРОКА(C2:C16)+1. Получим 2+1=3, остаток от деления на 3 равен нулю. Так мы возьмем 1-ю, 3-ю, 6-ю и т.д. позиции.

Формула массива значит, что Excel должен поочередно перебрать все ячейки спектра – начиная с C2 до C16, и с каждой из их произвести описанные выше операции.

Когда будем отыскивать реализации по Отделу 2, то изменим выражение:

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C16);3)=0;C2:C16))

Ничего не добавляем, так как 1-ое подходящее значение как раз и находится в 3-й позиции.

Аналогично для Отдела 3

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C16)-1;3)=0;C2:C16))

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

Ну и, естественно, не забываем жать CTRL+SHIFT+ENTER.

Примечание. Точно таковым же образом можно суммировать и каждый N-й столбец в таблице. Лишь заместо функции СТРОКА() необходимо будет применять СТОЛБЕЦ().

Сумма каждых N строк.

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

Используем то, что СУММ() может ложить значения не лишь в спектре данных, но и в массиве. Таковой массив значений ей может предоставить функция СМЕЩ.

Напомним, что тут необходимо указать несколько аргументов:

1.       Исходную точку. Обратите внимание, что С2 мы ввели как абсолютную ссылку.

2.       Сколько шагов вниз сделать

3.       Сколько шагов на право сделать. Опосля этого попадаем в исходную (левую верхнюю) точку массива.

4.       Сколько значений взять, вновь двигаясь вниз.

5. Сколько колонок будет в массиве. Попадаем в конечную (правую нижнюю) точку массива значений.

Итак, формула для 1-й недели:

=СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1))

В данном случае СТРОКА() – это как бы наш счетчик недель. Отсчет необходимо начинать с 0, чтоб деяния начать прямо с ячейки C2, никуда вниз не перемещаясь. Для этого используем СТРОКА()-2. Так как сама формула находится в ячейке F2, получаем в итоге 0. Началом отсчета будет С2, а конец его – на 5 значений ниже в той же колонке.

СУММ просто сложит предложенные ей 5 значений.

Для 2-й недельки в F3 формулу просто копируем. СТРОКА()-2 даст тут итог 1, потому начало массива будет 1*5=5, то есть на 5 значений вниз в ячейке C7 и до С11. И так далее.

Как отыскать сумму больших (наименьших) значений. 

Задача: Суммировать 3 наибольших либо 3 малых значения.

Функция Больший возвращает самое огромное значение из списка данных. Хитрость в том, что 2-ой ее аргумент указывает, какое конкретно значение необходимо вернуть: 1- самое огромное, 2 – 2-ое по величине и т.д. А ежели указать {1;2;3} – означает, необходимы три самых огромных. Но при этом не запамятовывайте использовать формулу массива и завершать композицией клавиш CTRL+SHIFT+ENTER.

=СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3}))

Аналогично обстоит дело и с самыми малеханькими значениями:

Читайте также  Как удалить аккаунт из гугл. Как удалить аккаунт Google за 8 шагов?

=СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3}))

3-D сумма, либо работаем с несколькими листами рабочей книжки Excel.

Чтобы подсчитать числа из схожей формы спектра на пары листах, вы сможете записывать координаты данных особым синтаксисом, именуемым «3d-ссылка».

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

Посмотрите на этом маленьком видео, как используются 3-D формулы.

Как видите, у нас имеется 4 схожих таблицы. Обычный способ нахождения выручки за месяц –

=СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8)

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

А вот 3D-метод:

=СУММ(неделя1:неделя4!B2:B8)

Мы указываем програмке, что необходимо произвести вычисления с ячейками B2:B8, находящимися на листах неделя1, неделя2, неделя3, неделя4. Тут номер листа поочередно возрастает на 1.

Важное замечание. Вы сможете воспользоваться 3D-ссылками и в остальных вариантах. К примеру, выражение

=СРЗНАЧ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8)

найдет среднее значение однодневной выручки за месяц.

Поиск подходящего столбца и расчет его суммы.

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

Для поиска и выбора определенного продукта используем очень популярную комбинацию функций ИНДЕКС+ПОИСКПОЗ.

Расчет суммы в G3 выполним так:

=СУММ(ИНДЕКС(B2:D21;0;ПОИСКПОЗ(F3;B1:D1;0)))

Итак, композиция ИНДЕКС+ПОИСКПОЗ должны возвратить для дальнейших расчетов набор чисел в виде вертикального массива, который и будет позже просуммирован.

Опишем это подробнее.

ПОИСКПОЗ находит в шапке наименований таблицы B1:D1 подходящий продукт (бананы) и возвращает его порядковый номер (иначе говоря, 2).

Затем ИНДЕКС выбирает из массива значений B2:D21 соответственный номер столбца (второй). Будет возвращен весь столбик данных с подходящим номером, так как номер строчки (первый параметр функции) указан равным 0. На нашем рисунке это будет С2:С21. Остается лишь подсчитать все значения в данной колонке.

В данном случае, чтоб избежать ошибок при записи наименования продукта, мы рекомендовали бы употреблять выпадающий перечень в F3, а значения для заполнения его брать из B1:D1.

5 методов сделать выпадающий перечень в Excel – советуем ознакомиться.

Сумма столбцов из пары таблиц.

Как в Экселе посчитать сумму столбца, ежели таковых столбцов несколько, да и сами они находятся в пары различных таблицах?

Для получения итогов сходу по нескольким таблицам также используем функцию СУММ и структурированные ссылки. Такие ссылки возникают при разработке в Excel «умной» таблицы.

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

В нашем случае это смотрится так:

Для сотворения «умной» таблицы выделим спектр A1:B21 и на ленте «Главная» избираем «Форматировать как таблицу».

Приятным бонусом тут является то, что «умная» таблица сама изменяет свои размеры при добавлении в нее данных (или же их удалении), ссылки на нее корректировать не нужно.

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

Помимо этого, ежели используемые вами таблицы содержат строку итогов, то нашу формулу перепишем так:

Читайте также  Можно ли очистить папку темп. Папка TEMP на компьютере занимает много места – что делать?

=СУММ(Таблица2[#Итоги];Таблица3[#Итоги])

И ежели будут внесены какие-то конфигурации либо добавлены числа, то все пересчитается автоматически.

Примечание: итоговая строка в таблице обязана быть включена. Ежели вы отключите её, то выражение вернет ошибку #ССЫЛКА.

Еще одно принципиальное замечание. Чуток выше мы с вами говорили, что функция СУММ обязана сложить сумму всех значений в строке либо столбце – даже ежели они укрыты либо же фильтр значений не дозволяет их увидеть.

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

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

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

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

Также рекомендуем:

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

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

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

Способ 1 – Автосуммирование

Данное действие дозволяет быстро выяснить результат по столбцу без наличия способностей сотворения формул. Метод действий:

  1. Курсор установить в месте, в котором нужно вывести значение подсчетов.
  2. На вкладке «Главная» надавить значок Автосуммы либо пользоваться сочетанием кнопок Alt и =.

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

Если требуется просуммировать сходу несколько столбцов либо строк, то:

  1. Выделить ячейки, в которых будет отражена сумма по каждому из столбцов.
  2. Нажать знак Автосуммы.
  3. Enter. Итоги будут посчитаны сходу по двум столбцам.

Если требуется отыскать сумму вкупе с несколькими доп ячейками:

  1. Выделить область для вывода итогов.
  2. Нажать на иконку Автосуммы.
  3. Диапазон автоматом определится в составе всех вышерасположенных ячеек.
  4. Зажать Ctrl и выделить доп области и надавить Enter.

Способ 2. Ручной ввод формулы

Удобен при необходимости подсчитать результат сложения значений, хаотично расположенных по отношению друг к другу. Создаем формулу пошагово:

  1. Курсор поставить на ячейку с будущим итогом.
  2. Ввести =. С равенства начинается неважно какая формула в Excel.
  3. Левой клавишей мыши щелкать по необходимым для суммирования ячейкам, ставя меж ними + и Enter.

Способ 3. Приятное суммирование

Особенность этого метода в том, что результат не выводится в отдельную ячейку, нет необходимости употреблять формулы. Конечный итог будет виден в нижнем правом углу листа документа при выделенном спектре ячеек. Как лишь выделение снимается, сумма исчезает. Поэтапные действия:

  1. Мышкой выделить нужный столбец либо строку.
  2. Посмотреть на результат.

Примеры были рассмотрены на столбцах, подобная последовательность действий при суммировании значений в строчках. Отличие – спектр выделяется не по вертикали, а по горизонтали.

Оставьте комментарий