Mir Excel — Обучение работе в программе Microsoft Excel. Сайт Ольги Кулешовой. » Архив блога » Сумма с накоплением

Расчет суммы — это одна из самых распространенных задач в Excel. Как может быть использована формула СУММ и можно ли обойтись без нее? Полезные советы с примерами.

Сумма с накоплением

Автор: Ольга Кулешова | Категория: Приемы и советы, Формулы и функции | Опубликовано 05-04-2013

4

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

runningsum1.png
Вычисление сумм с накоплением (сумм с нарастающим итогом) можно осуществлять в несколько действий, однако можно написать и одну простую формулу, используя функцию СУММ (SUM) и различный вид ссылок на адреса ячеек.
Формула в первой записи будет иметь вид: =СУММ($B$2:B2) или =SUM($B$2:B2), где ссылка на первую ячейку диапазона $B$2 является абсолютной (расчет любого итогового значения должен всегда идти от первой записи), а ссылка на последнюю ячейку диапазона B2 – относительной (диапазон должен автоматически увеличиваться при копировании формулы вниз).

runningsum2.png

1. Процентная структура продаж

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

Шаг 1. Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм — Доходы (если вы не знаете, как создать сводную таблицу, посмотрите статью «Как построить сводную таблицу в Excel»).

Сводная таблица

Шаг 2. Щелкаем правой кнопкой мыши по любому числу в сводной таблице и выбираем раздел:
Дополнительные вычисления → % от общей суммы. В появившемся меню доступно несколько способов вычисления процентов:

а) % от общей суммы – рассчитывается к итоговой сумме, от «угла».

вычисления в сводных таблицах

Если переместить данные по Городам в область строк, а Товары в столбцы, мы увидим, что общий процент считается как по строкам, так и по колонкам, и сумма процентов равна 100%.

б) % от суммы по столбцу или по строке.
Если требуется рассчитать структуру продаж, например, только по Городам, выбираем % от суммы по столбцу. Если только по товарам, соответственно – по строке.

в) А если нужно видеть структуру продаж и по товарам, и по городам? Не проблема! Нужно выбрать % от суммы по родительской строке.
Тогда процент рассчитается от суммы группы, а не от общего итога. А сумма процентов внутри группы будет равна 100%.

вычисления в сводной таблице

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

несколько одинаковых столбцов в сводной

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

 Как посчитать сумму одним кликом.

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

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

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

График с накоплением в Excel.

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

График с накоплением в Excel

Выбираем нужный нам тип диаграммы — График с накоплением.

График с накоплением в Excel

Нажимаем и получаем График с накоплением.

График с накоплением в Excel

На графике отразились две кривые линии. Оранжевая кривая, это продажи Магазина №2. Синяя кривая, это продажи магазина №1. Для наглядности добавим Метки данных. Вызовем настройки диаграммы нажав на  нее. В настройках выберем Элементы диаграммы. Поставим галочку напротив пункта Метки данных. На нашем графики отразиться количество проданных стульев соответственно месяцу года и магазину.

Пример

Вертикальная ось диаграммы показывает количество проданных стульев. При этом оранжевая кривая на графике, которая соответствует продажам Магазина №2, берет свое начало не на отметке 260 (шт.). Это количество проданных стульев данным магазинов в январе. Оранжевая кривая берет сове начало на отметке 510 (шт.). Это суммарное  количество проданных стульев двумя магазинами в январе (250 шт. +.260 шт.). То есть, для оранжевой кривой, при определении координаты по вертикальной оси, начало отсчета происходит не от нулевой отметки, а от соответствующей (нижележащей) точки синей кривой.

Построим на основе исходной таблицы простой График.

Обычный

Как видно, кривые продаж Магазина №1 и Магазина №2 наложились одна на одну. Проводить анализ или презентацию используя такой график не целесообразно. График с накоплением позволяет избежать данной проблемы.

Статьи посвященные диаграммам в Excel:

Как построить график в Excel. Описание и примеры.

Круговая диаграмма (вторичная круговая и вторичная линейчатая диаграммы), круговая объемная и кольцевая диаграммы. Описание и примеры.

Диаграммы MS Excel. Гистограмма, линейчатая диаграмма. Виды этих диаграмм, описание и примеры.

Соединить текст из разных ячеек

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

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

Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

Подобрать значения для нужного результата

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

Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

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

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

Что такое автосумма в таблице Excel?

Вторым по простоте способом является автосуммирование. Установите курсор в то место, где вы хотите увидеть расчет, а затем используйте кнопку «Автосумма» на вкладке Главная, или комбинацию клавиш  ALT + =.

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

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

=СУММ()

Этот метод быстрый и позволяет автоматически получать и сохранять результат.

Кроме того, ALT + = можно просто нажать, чтобы быстро поставить формулу в ячейку Excel и не вводить ее руками. Согласитесь, этот небольшой хак в Экселе может сильно ускорить работу.

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

Под видео на всякий случай дано короткое пояснение.

Итак, выберите диапазон ячеек и дополнительно ряд пустых ячеек снизу и справа (то есть, В2:Е14 в приведенном примере).

Нажмите кнопку «Автосумма» на вкладке «Главная» ленты. Формула сложения будет сразу введена.

Обновить курс валют

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

Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

Ввод функции СУММ вручную.

Самый традиционный способ создать формулу в MS Excel – ввести функцию с клавиатуры. Как обычно, выбираем нужную ячейку и вводим знак =. Затем начинаем набирать СУММ. По первой же букве “С” раскроется список доступных функций, из которых можно сразу выбрать нужную.

Теперь определите диапазон с числами, которые вы хотите сложить, при помощи мыши или же просто введите его с клавиатуры. Если у вас большая область данных для расчетов, то, конечно, руками указать её будет гораздо проще (например, B2:B300). Нажмите Enter.

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

Правила использования формулы суммы в Excel (синтаксис):

СУММ(число1; [число2]; …)

У нее имеется один обязательный аргумент: число1.

Есть еще и необязательные аргументы (заключенные в квадратные скобки): [число2], ..

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

К примеру, в выражении =СУММ(D2:D13) только один аргумент – ссылка на ячейки D2:D13.

Кстати, в этом случае также имеется возможность воспользоваться автосуммированием.

Использование для проверки введенных значений.

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

Посмотрите это видео, в котором используется инструмент «Проверка данных».

В условии проверки используем формулу

=СУММ(B2:B7)<3500

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

В этом случае нам нужно условие, которое возвращает ЛОЖЬ до тех пор, пока затраты в смете меньше 3500. Мы используем функцию сложения для обработки фиксированного диапазона, а затем просто сравниваем результат с 3500.

Каждый раз, когда записывается число, запускается проверка. Пока результат остается меньше 3500, проверка успешна. Если какое-то действие приводит к тому, что он превышает 3500, проверка завершается неудачно и выводится сообщение об ошибке.

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

25 способов условного форматирования в Excel

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

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

Выделить цветом нужные данные

Когда таблицы большие и многое происходит автоматически, легко пропустить что-то важное. От этого спасает функция выделения цветом. Для ее активации на главной вкладке выберите «Условное форматирование» и задайте условия и цвет выделения.

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

Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

Суммировать только нужное

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

Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

Расставить по порядку

В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

При помощи четырех формул мы узнали минимальную, максимальную и среднюю зарплату промоутеров, а также расположили всех сотрудников по возрастанию оклада

Уверены, теперь вы сможете прокачать наши таблицы до максимального уровня:

  1. Экселька, которая ведет семейный бюджет.
  2. Помогает выбрать что угодно.
  3. И считает доходность по вкладам.
Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...