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

8 простых способов вычислить сумму столбца в Excel

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

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

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

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

= СУММ (B: B)

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

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

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

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

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

Суммируем диапазон ячеек.

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

Формула расчета выглядит так:

= СУММ (B2: D4)

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

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

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

Конечно может не два, а гораздо больше: до 255 штук.

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

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

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

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

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

Второй способ — более «продвинутый», но универсальный.

Мы пишем

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

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

Как это работает? Нам нужен первый, третий, шестой и так далее. С помощью функции LINE () вычисляем номер текущей позиции. И если остаток от деления на 3 равен нулю, значение будет учтено при вычислении. В противном случае нет.

Для этого счетчика мы будем использовать номера строк. Но наша первая цифра находится во второй строке листа Excel. Так как нам нужно начинать с первой позиции, а затем брать каждую третью, а диапазон начинается со 2-й строки, поэтому мы добавляем 1 к его порядковому номеру, чтобы наш счетчик начал отсчет с числа 3. Для этого выражение LINE (C2: C16) +1 используется. Получаем 2 + 1 = 3, остаток от деления на 3 равен нулю. Итак, возьмем первый, третий, шестой и так далее.

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

Когда мы найдем продажи для отдела 2, мы изменим выражение:

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

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

Аналогично для Дивизиона 3

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

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

И, конечно же, не забудьте нажать CTRL + SHIFT + ENTER.

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

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

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

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

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

1. Отправная точка. Обратите внимание, что мы ввели C2 как абсолютную ссылку.

2. Сколько ступенек вниз нужно сделать

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

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

5. Сколько столбцов будет в массиве. Мы приходим к конечной точке (внизу справа) массива значений.

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

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

В этом случае ROW () похож на наш недельный счетчик. Обратный отсчет должен начинаться с 0, чтобы действия начинались непосредственно с ячейки C2, без движения вниз. Для этого мы используем STRING () — 2. Поскольку сама формула находится в ячейке F2, результатом будет 0. Исходной точкой будет C2, а ее конец будет иметь 5 меньших значений в том же столбце.

СУММ просто добавит пять предложенных значений.

Для 2-й недели в F3 просто скопируйте формулу. ROW () — 2 даст здесь результат 1, поэтому начало массива будет 1 * 5 = 5, что составляет 5 значений в ячейках от C7 до C11. И т.п.

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

Цель: сложить 3 максимальных или 3 минимальных значения.

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

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

Аналогичная ситуация и с меньшими значениями:

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

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

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

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

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

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

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

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

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

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

Мы говорим программе выполнять вычисления с ячейками B2: B8, найденными на рабочих листах week1, week2, week3, week4. Здесь номер листа последовательно увеличивается на 1.

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

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

найдет средний дневной доход за месяц.

Поиск нужного столбца и расчет его суммы.

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

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

Рассчитываем сумму в G3 следующим образом:

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

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

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

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

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

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

Сумма столбцов из нескольких таблиц.

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

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

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

В нашем случае это выглядит так:

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

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

Кроме того, в нашем случае не имеет значения, где именно эти данные находятся в вашем файле Excel. Даже не важно, что они на разных листах: программа все равно найдет их по имени.

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

= СУММ (Таблица2 [#Totals]; Table3 [#Totals])

А если будут внесены изменения или добавлены числа, все будет автоматически пересчитано.

Примечание. Должна быть включена итоговая строка в таблице. Если вы отключите его, выражение вернет ошибку #REF.

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

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

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

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

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