Общая сумма в excel. Суммирование в Excel: простые методы для сложных вычислений
Электронные таблицы Excel предназначены для хранения данных и проведения в них вычислений, а еще для анализа информации и сбора статистики. Это означает, что пользователю нужны способы расчета итоговых значений и статистических данных, удовлетворяющие его требованиям. Для этого программа предоставляет различные возможности.
Использование функций
Одна из наиболее востребованных задач пользователей Excel - вычисление суммы данных, хранящихся в одной колонке. Изучим, как суммировать столбец в Excel различными методами. Самый простой способ найти сумму данных, расположенных на листе, — применить формулу. И простейшая из них - функция СУММ(). Покажем ее работу на примере.
Туристическое агентство ведет учет туристов, приехавших в отель в отчетный период. Гостиница предоставляет компании номера, которые фирма предлагает гостям для размещения. Гости оплачивают отдых компании, а сама фирма оплачивает номера в отель. Стоит задача - подсчитать, сколько денег компания должна заплатить в гостиницу, сколько агентству должны туристы, сколько людей приедет в указанный период и сколько в сумме ночей гости проведут в отеле.
Чтобы суммировать столбец в Excel как набор ячеек, используем формулу. Для ее вставки добавляем в результирующей ячейке знак равенства и выбираем функцию СУММ(). В открывшемся окне ввода аргументов указываем столбец, данные из которого будут добавляться. В итоговом поле получим сумму значений колонки.
Автосумма
Еще один способ получения итоговых результатов - использование промежуточных итогов. В нашем случае речь идет об автосумме данных. Этот способ работает, только если диапазон ячеек отформатирован как таблица. Если же это неотформатированный набор данных, функция не действует.
Чтобы суммировать столбец в Excel как промежуточный итог, используют команду «Автосумма» на панели инструментов «Редактирование» вкладки «Главная». В меню указанной кнопки расположены пункты выбора и других итогов: вычисления среднего, минимального и максимального значения и другие функции.
Для вставки автосуммы выделяем нужную ячейку. Для примера суммируем числа в столбце в Excel «Количество ночей». Установим курсор на поле H10 и нажмем кнопку «Автосумма». В нужной ячейке мгновенно появляется результат. При вставке строк в таблицу или их удалении диапазон итогов автоматически расширяется или сужается, поддерживая актуальность вычисления.
Суммирование по условию
Иногда стоит задача добавить к итогу не все значения в колонке таблице, а только те, которые отвечают заданным требованиям. Для такого случая предусмотрена функция СУММЕСЛИ(). Перед тем как суммировать столбцы таблицы Excel, мы определяем условия добавления ячеек к сумме и указываем их в аргументах функции. Синтаксис команды:
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).
Диапазоном мы определяем колонку, данные в которой нам необходимо просуммировать. Затем прописываем требования к этим данным. Посчитаем в таблице, какова итоговая сумма по заявкам туристов, каждая из которых превысила 100 тыс. руб.
Для этого вызываем функцию СУММЕСЛИ и указываем атрибуты:
В программе существует команда, которая позволяет указывать несколько условий добавления числа к сумме. Это СУММЕСЛИМН. Ее синтаксис:
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …).
Посмотрим, как суммировать столбцы таблицы Excel с несколькими условиями. Для примера посчитаем итоговую цену заявок туристов, заказавших завтраки и проживающих в отелей больше двух ночей.
Строка функции выглядит так:
СУММЕСЛИМН([Полная цена];[Питание];»Завтраки»;[Количество ночей];»>2″).
Сводные таблицы
Для еще более сложных итогов существует возможность создания сводных таблиц. Они позволяют производить вычисления итогов по категориям. В нашем случае добавим сводную таблицу, которая покажет общее количество туристов, ночей проживания, суммы в отель и от гостей по категориям номеров.
Для создания сводной таблицы выбираем команду «Сводная таблица» панели инструментов «Таблицы» вкладки «Вставка». В мастере создания задаем таблицу или диапазон, откуда будут браться данные. Возможность использования диапазона, а не всей таблицы позволяет Excel суммировать столбец, начиная с определенной строки, а не все значения колонки.
В статье мы рассмотрели способы, как суммировать столбец в Excel. Каждый из них имеет свои преимущества и в разных случаях может оказаться удобнее других. Осталось выбрать тот, что подходит под ваши задачи и требования и дает вам нужный результат при минимальных усилиях.
– Игорь (Администратор)В рамках данной заметки, я расскажу вам как в Excel посчитать сумму столбца несколькими методами.
Электронные таблицы это один из самых часто используемых инструментов. И это не удивительно, ведь в них можно как составить список товаров для похода, так и нечто более сложное (к примеру, сложные и огромные сводные отчеты). Но, первое с чем сталкивается практически каждый пользователь - это то, как посчитать сумму в столбце.
Примечание : Материал рассчитан для начинающих пользователей, но может быть полезен и опытным, так как в Excel многие часто используемые вещи можно сделать несколькими разными методами.
Примечание : Данные методы актуальны для Excel 2007, 2010, 2013, 2016 и выше.
Вычисление суммы при выделении
Excel учитывает массу различных пожеланий пользователей, поэтому самые частые из них обычно делают легкодоступными. К этому относится и автоматическое суммирование, которое применяется к выделенной области. Все что для этого необходимо сделать - это выделить ячейки, а затем посмотреть в нижнюю правую часть окна программы (рядом с панелькой "масштаба" и "разметки"). Обычно там отображается "среднее", "количество ячеек" и "сумма". Последнее именно то, что и нужно.
Если же суммы там не присутствует, то необходимо сделать следующее:
1. Правой кнопкой щелкнуть в любом месте самой нижней панели (полосочки).
2. В появившемся контекстном меню выбрать "Сумма".
3. Снова выделить ячейки и увидеть, что число там отображается.
Автоматическое вычисление суммы столбца в ячейке
Этот метод позволяет быстро вставить формулу и получить сумму столбца в одной из более нижних ячеек. Вот, что нужно сделать:
1. Выберите ячейку, которая расположена ниже всех ячеек в столбце (тех, для которых необходимо вычислить сумму).
2. В верхней ленте перейдите во вкладку "Главная".
3. В блоке "Редактирование" нажмите кнопку "Автосумма" (если главное окно программы сжато по ширине, то ориентируйтесь по иконке суммирования ряда из математики - чем-то похоже на букву Е).
4. Нажмите "Ввод" и в ячейку вставится формула подсчета суммы (вы так же можете скорректировать область до нажатия кнопки "Ввод").
Примечание : Если нужно вычислить суммы для нескольких столбцов, то можно в шаге 1 выбрать не одну, а сразу несколько ячеек, и аналогичным образом проделать остальные шаги.
Вычисляем сумму столбца в Excel с помощью формулы
Иногда необходимо вывести сумму в какую-то произвольную ячейку, поэтому стоит уметь пользоваться формулами. И вот как это делается:
1. Выберите нужную ячейку, где будет выводится сумма.
2. Затем нажмите символ "=" (равно). С этого символа начинается любая формула. Учтите, что он должен быть в самом начале.
3. Введите слово "СУММ", затем открывающую скобочку "(" и либо мышкой выделите нужный диапазон, либо укажите диапазон в формате "A1:A22" (первая ячейка, затем двоеточие, затем последняя ячейка).
4. Затем поставьте закрывающую скобочку ")" (в принципе, можно и не выполнять данный шаг, но для приобретения практических навыков составления формул крайне советуется).
5. Нажмите кнопку "Ввод" и в выделенной ячейке отобразится значение суммы столбца.
Если вы знаете еще методы, то смело делитесь ими в комментариях.
Многие работая с электронными таблицами 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 .
Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию .
Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $ . К примеру, в ячейке D10 у нас вписана формула:
=СУММЕСЛИ(G5:G300;("Покупки в магазинах");H5:H300)
Далее мы хотим выводить сумму обедов в ячейке D11 . Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11 . Благодаря этому формула будет вставлена в D11 , но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:
=СУММЕСЛИ(G6:G301;("Покупки в магазинах");H6:H301)
Произошло смещение. Если мы скопируем формулу в D12 , то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $ . Такие особенности EXCEL.
Таблица «Учёт ежемесячных расходов» -
Как посчитать в
Excel
ячейки выборочно
, не все подряд, а по условию? Если вводить формулу вручную в большой таблице, то это очень долго. Нас выручит функция Excel «СУММЕСЛИ».
У нас такая таблица.
Складываем ячейки с суммой в строках «план» - это первая, четвертая и восьмая строки.
В ячейке F1 пишем формулу. На закладке «Главная» в разделе «формулы» - «Математические» выбираем функцию «СУММЕСЛИ».
В появившемся диалоговом окне в строке «Диапазон» указываем диапазон (столбец), в котором написано слово «план». У нас – это столбец В.
В строке «Критерий» пишем то, что ищем. Мы написали слово «план», п.ч. считаем сумму плана. Если бы считали сумму «выпушено», то написали бы критерий – «выпущено».
В разделе "Критерий" можно писать некоторые символы.
* (звездочка) означает любое количество символов в слове. Например, "мор*" - будет искать слова на "мор" и любое окончание (мор, море, моряк, т.д.). Чтобы найти все фамилии на букву "Р" и заканчивающиеся на букву "в", нужно написать критерий так - "Р*в" (или "Р*В"). Найдет фамилии - Рублев, Рылеев, т.д.
? (знак вопроса) обозначает один символ. Например, критерий "ст?л" - будет искать стул, стол, т.д. Можно написать несколько вопросительных знаков. Например, "Иван?????" (Иванов, Иванченко, т.д.)
О других символах, которые можно вставить в формулу, читайте в статье "Символы в формулах Excel ".
В строке «Диапазон_суммирования» указываем диапазон (столбец), в котором находятся числа, то, что нам нужно сложить. У нас - это столбец С. Диалоговое окно заполнили так. Нажимаем «ОК».
Получилась такая формула.
=СУММЕСЛИ(B2:B10;"план";C2:C10)
Формула посчитала.
В ячейке F2 пишем такую формулу.
=СУММЕСЛИ(B2:B10;"выпущено";C2:C10)
Ещё примеры счета с условием смотрите в статье "Функция "СУММЕСЛИ" в Excel ".
Теперь нам нужно посчитать проценты в Excel
.
В ячейку F3 напишем формулу, которая посчитает средний процент выполнения плана по фирме.
Здесь пригодится функция Excel «СРЗНАЧЕСЛИ».
В ячейке устанавливаем формат «процентный».
На закладке «Формулы» выбираем «Другие функции» - «Статистические» и, выбираем функцию «СРЗНАЧЕСЛИ».
Диалоговое окно заполняем, как в предыдущей формуле, только «критерий» пишем «%» - это в нашей таблице так написано. Получилась такая формула.
=СРЗНАЧЕСЛИ(B2:B10;"%";C2:C10)
Таблица посчитала так.
Как посчитать наценку, скидку, умножив столбец на число, смотрите в статье "
Допустим, вам нужно суммировать значения с более чем одного условия, например сумму продаж товара в определенной области. Это хороший обращения для использования функции СУММЕСЛИМН в формуле.
Просмотрите в этом примере, в котором у нас есть двумя условиями: необходимо узнать сумму продаж мяса в южном районе (из столбца A) (из столбца C).
Вот формулу можно использовать для acomplish это:
Результатом является значение 14,719.
Рассмотрим изучить все части формулы.
СУММЕСЛИМН является формулой арифметического. Он вычисляет числа, которые находятся в этом случае в столбце D. Первым делом нужно указать расположение номера:
=СУММЕСЛИМН(D2:D11;
Другими словами нужно ввести формулу для суммирования чисел в столбце, если они удовлетворяют условиям. Что диапазон ячеек - это первый аргумент в этой формуле - первая часть данных, функция требует в качестве входных данных.
После этого необходимо найти данные, которые соответствуют двух условий, поэтому введите свой первый условие, указав для функции расположение данных (a2: a11), а также какие условием является - наилучшим образом «Южный». Обратите внимание, запятые между отдельные аргументы:
= SUMIFS(D2:D11,A2:A11,"South",
Кавычки вокруг слова «Южный» укажите, что это текстовые данные.
Наконец, введите аргументы для второго условия – диапазон ячеек (C2: C11), содержащий слово «Мясо», плюс само слово (заключено в кавычки) таким образом, Excel может соответствовать его. Завершить формулу с закрывающую скобку ) и нажмите клавишу ВВОД. Результат, снова нажмите - 14,719.
=SUMIFS(D2:D11,a2:a11,"South",C2:C11,"Meat")
По мере ввода функции СУММЕСЛИМН в Microsoft Excel, если вы не помните аргументы справки готов под рукой. После ввода = СУММЕСЛИМН (, автозавершение формул появится под формулы, в списке аргументов в их правильном порядке.
Если посмотреть на изображение использования функции автозавершения формул и списка аргументов, то в нашем примере диапазон_суммирования - это диапазон D2:D11, представляющий собой столбец с числами, которые вы хотите просуммировать; диапазон_условия1 - это диапазон A2.A11, представляющий собой столбец с данными, в котором находится условие1 - “Южный”.
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 - это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 - “Мясо”.
Если вы нажмете СУММЕСЛИМН в автозавершении формулы, откроется статья, справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСЛИМН, то некоторые образцы данных и формулы, использующей функцию.
Вы можете работать с демонстрационными данными непосредственно в этой книге веб-приложения Excel Online. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
Регион |
Продавец |
Что следует ввести |
Продажи |
Западный |
Молочные продукты |
||
Восточный |
Песоцкий |
||
Северный |
Песоцкий |
Молочные продукты |
|
Маринова |
Сельхозпродукты |
||
Восточный |
Песоцкий |
Сельхозпродукты |
|
Северный |
Сельхозпродукты |
||
Маринова |
|||
Формула |
Описание |
Результат |
|
" = СУММЕСЛИМН (a2: a11; D2: D11 |
Суммирует продаж мяса в |
SUMIFS(D2:D11,A2:A11, |