Как быстро и легко посчитать сумму в Excel? Чаще всего нас интересует сумма по столбцу или строке. Попробуйте разные способы суммирования столбца, используйте функцию СУММ или преобразуйте диапазон в интеллектуальную таблицу для упрощения вычислений, добавляйте данные из нескольких столбцов или даже из разных таблиц. Все это мы увидим на примерах.
Содержание- Как суммировать весь столбец или строку.
- Суммарная площадь ячейки.
- Как вычислить сумму каждых N строк.
- Сумма каждых N строк.
- Как найти сумму наибольшего (наименьшего) значения.
- 3D-сумма или работа с несколькими листами в книге Excel.
- Найдите нужный столбец и посчитайте сумму.
- Сумма столбцов из нескольких таблиц.
Как суммировать весь столбец либо строку.
Если мы введем функцию вручную, в вашей электронной таблице Excel появятся различные возможности расчета. В нашей таблице записывается ежемесячный доход по отделам.
Если мы поместим формулу суммы в G2
=СУММ(В:В)
тогда мы получим общий доход для первого отдела.
Обратите внимание, что наличие текста, а не числа в ячейке B1 не повлияло на расчеты. Добавляются только числовые значения, а символьные игнорируются.
Важная заметка! Если среди чисел случайно окажется дата, это серьезно повлияет на точность расчетов. Дело в том, что даты хранятся в Excel в виде чисел, и их отсчет начинается с 19:00 ежедневно. Поэтому будьте осторожны, когда вычисляете сумму столбца в Excel и используете ее в формуле целиком.
Все сказанное в полной мере относится и к работе со строками.
А вот суммирование всего столбца происходит довольно редко. Гораздо чаще область, с которой мы собираемся работать, должна быть указана более тонко и точно.
Суммируем диапазон ячеек.
Важно научиться правильно указывать диапазон данных. Вот как это делается, если суммировать продажи за 1 квартал:
Формула расчета выглядит так:
=СУММ(B2:D4)
Вы также можете использовать его в нескольких непересекающихся областях, которые расположены в разных местах электронной таблицы.
В формуле мы последовательно показываем несколько областей:
=СУММ(B2:D4;B8:D10)
Естественно, их может быть не два, а гораздо больше: до 255 штук.
Как вычислить сумму каждой N-ой строки.
В таблице представлены повторяющиеся с определенной периодичностью показатели — продажи по отделам. Необходимо рассчитать общий доход по каждому из них. Сложность в том, что интересующие нас показатели не близкие, а чередующиеся. Предположим, мы ежемесячно анализируем данные о продажах трех отделов. Необходимо определить продажи для каждого отдела.
Другими словами, по мере продвижения вниз вы должны занимать каждую третью строчку.
Это можно сделать двумя способами.
Первый самый простой, «в лоб». Складываем все числа до нужного деления обычной математической операцией сложения. Выглядит просто, но представьте, если у вас есть статистика, скажем, за 3 года? Вам нужно обработать 36 номеров…
Второй способ слишком «продвинутый», но универсальный.
Мы записываем
=СУММ(ЕСЛИ(ОСТАТОК(СТРОКА(C2:C16)+1,3)=0,C2:C16))
А затем нажимаем комбинацию клавиш CTRL+SHIFT+ENTER, так как используется формула массива. Excel автоматически добавит фигурные скобки слева и справа.
Как это работает? Нам нужны 1-я, 3-я, 6-я и так далее позиции. С помощью функции STRING() вычисляем номер текущей позиции. И если остаток от деления на 3 равен нулю, значение будет учтено при расчете. В противном случае нет.
Для такого счетчика мы будем использовать номера строк. Но наше первое число находится во второй строке электронной таблицы Excel. Так как надо начинать с первой позиции и потом брать каждую третью, а диапазон начинается со 2-й строки, то к порядковому номеру прибавляем 1. Затем наш счетчик начинает считать с числа 3. Это выражение LINE (C2: C16) для +1. Получаем 2+1=3, остаток после деления на 3 равен нулю. Так мы занимаем 1-ю, 3-ю, 6-ю и так далее позиции.
Формула массива означает, что Excel должен последовательно пройтись по всем ячейкам в диапазоне — от С2 до С16, и произвести с каждой из них описанные выше операции.
Когда мы находим продажи для отдела 2, мы меняем выражение:
=СУММ(ЕСЛИ(ОСТАТОК(СТРОКА(C2:C16);3)=0;C2:C16))
Ничего не добавляем, так как первое подходящее значение находится ровно на третьей позиции.
То же самое для отдела 3
=СУММ(ЕСЛИ(ОСТАТОК(СТРОКА(C2:C16)-1,3)=0,C2:C16))
Вместо прибавления 1 мы теперь вычитаем 1, чтобы обратный отсчет возобновился с 3. Теперь мы берем каждую третью позицию, начиная с 4-й.
И, конечно же, не забудьте нажать CTRL+SHIFT+ENTER.
Примечание. Точно так же можно просуммировать каждый N-й столбец таблицы. Только вместо функции СТРОКА() нужно использовать СТОЛБЦ().
Сумма каждых N строк.
Электронная таблица Excel записывает ежедневный доход магазина за длительный период времени. Необходимо рассчитать недельный доход за каждую семидневку.
Мы используем тот факт, что SUM() может складывать значения не только в диапазон данных, но и в массив. Такой диапазон значений может быть задан функцией OFFSET.
Помните, что вы должны указать здесь несколько аргументов:
1. Отправная точка. Обратите внимание, что мы ввели C2 как абсолютную ссылку.
2. Сколько шагов вниз нужно пройти
3. Сколько шагов вправо нужно сделать. После этого приходим к начальной (верхней левой) точке матрицы.
4. Сколько значений нужно взять, снова сдвинемся вниз.
5. Сколько столбцов будет в матрице. Приходим к последней точке (справа внизу) диапазона значений.
Итак, формула для первой недели:
=СУММ(СМЕЩ($C$2,(СТРОКА()-2)*5,0,5,1))
В этом случае STRING() похож на наш счетчик недель. Отсчет должен начинаться с 0, чтобы действия начинались прямо с ячейки С2, никуда не опускаясь. Для этого мы используем STRING()-2. Так как сама формула находится в ячейке F2, то в результате получаем 0. Начальной точкой будет С2, а концом ее будет на 5 значений ниже в этом же столбце.
SUM просто добавит к нему пять предложенных значений.
Для 2-й недели в F3 мы просто копируем формулу. STRING()-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.
Чтобы подсчитать цифры одной и той же формы площади на нескольких листах, вы можете записать координаты данных, используя специальный синтаксис, называемый «3D-справка».
Предположим, что на каждом листе вашей рабочей книги есть таблица еженедельных данных. Вы должны собрать все это и получить сводку через месяц. Для этого мы будем обращаться к четырем листам.
Посмотрите это короткое видео, чтобы узнать, как используются 3D-формулы.
Как видите, у нас есть 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)
находит среднее значение однодневного дохода в месяц.
Поиск нужного столбца и расчет его суммы.
Есть информация о продаже товаров, для каждого из них выделена отдельная колонка. Необходимо рассчитать продажи для выбранного товара. Другими словами, в Excel нам нужно посчитать сумму столбца, но сначала нам нужно просто найти этот нужный нам столбец среди других подобных.
Для поиска и выбора конкретного товара мы используем очень популярную комбинацию функций ИНДЕКС + ПОИСКПОЗ.
Расчет суммы в G3 осуществляется следующим образом:
=СУММ(ИНДЕКС(B2:D21;0;ПОИСКПОЗ(F3;B1:D1;0)))
Так комбинация ИНДЕКС + ПОИСКПОЗ должна вернуть для дальнейших вычислений набор чисел в виде вертикальной матрицы, которые затем будут суммироваться.
Опишем его более подробно.
ПОИСКПОЗ находит нужный продукт (бананы) в заголовке таблицы B1:D1 и возвращает серийный номер (другими словами, 2).
Затем ИНДЕКС выбирает из массива значений B2:D21 соответствующий номер столбца (второй). Будет возвращен весь столбец данных с соответствующим номером, так как номер строки (первый параметр в функции) установлен равным 0. На нашем рисунке это будет C2:C21. Остается только посчитать все значения в этом столбце.
В этом случае, чтобы избежать ошибок при наборе названия товара, мы бы рекомендовали использовать выпадающий список в F3, а значения для его заполнения брать из B1:D1.
5 способов создать выпадающий список в Excel
Сумма столбцов из нескольких таблиц.
Как посчитать сумму столбца в Excel, если таких столбцов несколько, а сами они находятся в нескольких разных таблицах?
Чтобы получить итоги сразу по нескольким таблицам, мы также используем функцию СУММ и структурированные ссылки. Такие ссылки появляются при создании «умной» таблицы в Excel.
При ее создании Excel присваивает имя самой таблице и каждому заголовку столбца в ней. Затем эти имена можно использовать в выражениях: они могут появляться в качестве подсказок в строке ввода.
В нашем случае это выглядит так:
| Прямая связь | Структурированная ссылка (имена таблиц и столбцов) |
| Б2: Б21 | Таблица 2[Всего] |
Чтобы создать «умную» таблицу, выделите диапазон A1:B21 и выберите «Форматировать как таблицу» на ленте «Главная».
Приятным бонусом здесь является то, что «умная» таблица сама меняет размер при добавлении (или удалении) данных в нее, ссылки на нее не нужно настраивать.
Также в нашем случае не имеет значения, как именно эти данные размещены в вашем файле Excel. Неважно, что они находятся на разных листах — программа все равно найдет их по названию.
Кроме того, если таблицы, которые вы используете, содержат итоговую строку, мы перепишем нашу формулу следующим образом:
=СУММ(Таблица2[#Итоги];Таблица3[#Итоги])
А если будут внесены изменения или добавлены числа, то все будет пересчитано автоматически.
Примечание: общая строка в таблице должна быть включена. Если вы его отключите, выражение вернет ошибку #REF.
Еще одно важное замечание. Чуть ранее мы говорили, что функция СУММ должна складывать сумму всех значений в строке или столбце — даже если они скрыты или фильтр значений не позволяет их увидеть.
В нашем случае, если таблица содержит итоговую строку, вы будете использовать ее для получения суммы только видимых ячеек.
Как вы можете видеть на этом рисунке, если вы отфильтруете любое из значений, общий объем продаж, рассчитанный другим способом, изменится.
Тогда как если вы только добавите ячейки и не будете использовать последнюю строку «умной» таблицы, то фильтрация и скрытие отдельных позиций не изменит результат вычислений.
Мы надеемся, что теперь вам будет намного проще суммировать диапазоны данных или отдельные ячейки.
Мы также рекомендуем:
Функция СУММПРОИЗВ с примерами формул. В этой статье объясняются основные и дополнительные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по множеству условий, вычисления средневзвешенного значения…
Суммировать по цвету и считать по цвету в Excel. В этой статье вы узнаете, как считать ячейки по цвету и получить сумму по цвету ячейки в Excel. Эти решения работают как для раскрашенного вручную, так и для условного форматирования. Если…
Формула ПРОМЕЖУТОЧНОГО ИТОГО - основные функции с примерами. - В этой статье объясняются функции функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel и показано, как использовать формулы промежуточных итогов для суммирования данных в видимых ячейках. В предыдущей статье мы обсуждали автоматический способ вставки промежуточных…
Промежуточные итоги в Excel. В этом учебном пособии объясняется, как использовать инструмент «Промежуточный итог» Excel для автоматического суммирования, подсчета или среднего значения различных групп ячеек. Вы также узнаете, как отображать или скрывать промежуточные итоги, копировать только строки…
Как подсчитать количество пустых и непустых ячеек в Excel. Если ваша задача состоит в том, чтобы заставить Excel подсчитывать пустые ячейки на листе, прочитайте эту статью, чтобы найти 3 способа сделать это. Узнайте, как искать и выбирать среди них, используя стандартные…
Как сравнить два столбца на наличие совпадений и различий — прочтение этой статьи займет у вас около 10 минут, а в следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные…
Формула суммы в Excel - несколько полезных советов и примеров - Как быстро и легко посчитать сумму в таблице Excel? Попробуйте разные способы: посмотрите сумму выбранных ячеек в строке состояния, используйте автосумму, чтобы добавить все или только несколько отдельных ячеек,…
Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах. - В этом руководстве объясняется, как использовать функцию СЧЁТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для различных типов данных — чисел, дат, текста и т д,…
СЧЁТЕСЛИ в Excel — Примеры функций с одним и несколькими условиями. В этой статье мы сосредоточимся на функции СЧЁТЕСЛИ в Excel, которая предназначена для подсчёта ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и распространенное использование, а затем я…
Функция СУММЕСЛИМН - как суммировать ячейки в Excel, когда много условий? - В этом руководстве объясняется разница между функциями СУММЕСЛИ и СУММЕСЛИМН с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями... Александр Трифунтов2020-03-17T18:01:46+03 : 0016 Январь , 2020 Нет комментариев
