Как посчитать сумму остатка в excel

Как в Excel посчитать сумму

MS Excel – табличный процессор, способный обрабатывать и систематизировать большие объемы информации.

MS Excel – табличный процессор, способный обрабатывать и систематизировать большие объемы информации, проводить расчеты посредством встроенного инструментария и ввода формул. У пользователя может возникнуть вопрос: «Как в Экселе посчитать сумму и применить формулы?». Чтобы не производить расчеты чисел вручную, опытные пользователи пользуются несколькими вариантами.

Функция «Автосуммирование»

В вопросе: «Как посчитать сумму в Excel в столбце или строке?» MS Excel предлагает задействуют встроенный инструментарий. Для этого юзеру необходимо:

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

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

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

выбирает ячейку, находящуюся в том же столбце что и числа, но несколько ниже;
кликает на пиктограмму суммы на панели инструментов;
MS Excel предлагает подсчитать все, что располагается выше обозначенной ячейки, но диапазон захватывает и пустые ячейки;
изменяет диапазон через выделение мышкой или ручной ввод диапазона;
для вступления изменений в силу нажимает «Enter».

Строка состояния

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

вводит числовую информацию в столбце;
выделяет диапазон.

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

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

Использование функций

Табличный процессор обладает мощным функционалом, поэтому пользователей интересует опрос, как считать в Excel. Приложение предлагает воспользоваться формулами. Распространенная формула: «=СУММ» – находит сумму введенных чисел.

Для нахождения результата пользователь:

выбирает ячейку отображения результата;
устанавливает курсор в строке формул;
вводит функцию =сумм(диапазон ячеек);
Нажимает «Enter».

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

Суммирование

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

Сумма с одним условием

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

Текстовое значение

Для решения задачи с одним условием в виде текстового значения юзер:

создает таблицу;
выбирает ячейку, где будет подсчитана сумма, и устанавливает курсор в строку формул;
вводит команду: =суммесли (условия выполнения).
последовательно указывает диапазон значений, условие отбора, диапазон суммирования.
для получения результата нажимает «Enter». В выбранной ячейке указывает сумму тех значений, которые удовлетворяют поставленному условию.

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

Второй параметр – критерий, указывающий, что нужно искать.

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

Критерий отбора указывается в кавычках.

Между параметрами функции обязательно должен быть разделитель «;» (точка с запятой). В противном случае приложение выдаст ошибку о неверно написанной функции.

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

Численное значение

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

формирует таблицу;
выбирает ячейку, где будет отображаться итог решения задачи;
переходит в строку формул;
вписывает функцию: =суммесли (условия выполнения);
действует согласно предыдущему алгоритму, указывая условия отбора и диапазоны значений;
для получения результата нажимает «Enter». В выбранной ячейке указывается сумма тех значений, которые удовлетворяют поставленному условию.

Сумма с несколькими условиями

Иногда юзеру нужно решить задачу, где указаны 2 и более условий, и нужно найти сумму значений, удовлетворяющих заданным критериям. Для этого используется функция «=СУММЕСЛИМН». Поэтому важно знать, как в Экселе сделать сумму с несколькими условиями. Для этого пользователь:

формирует таблицу по заданному образцу;
выбирает ячейку, где будет отображаться итог решения задачи;
переходит в строку формул;
вписывает функцию: =суммеслимн (условия выполнения).
последовательно указывает диапазон сложения, диапазон условия1, условие1 и т.д.;
для получения результата нажимает «Enter». В выбранной ячейке указывает сумма тех значений, которые удовлетворяют поставленному условию.

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

Второй параметр – диапазон условия 1, указывающий столбец, в котором проходит фильтрация по первому условию.

Третий параметр – условие1, которое указывается в кавычках и задает фильтрацию.

Четвертый и последующий параметры аналогичны второму и третьему, т.е. диапазон условия2, условие2; диапазон условия3, условие3 и т.д.

Условий отбора может множество.

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

Произведение двух столбцов

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

вводит значения в 2 столбца;
выбирает ячейку, где отображается результат;
устанавливает курсор в строку формул и вводит текст:=сумм (диапазон ячеек одного столбца ) *сумм(диапазон ячеек другого столбца);
для получения результата нажимает «Enter».

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

Для получения произведения двух столбцов пользователь:

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

Читать еще: Защита формулы excel от изменения

Ручной ввод формулы

Следующий вариант – как посчитать общую сумму в Excel посредством ручного ввода функции. Для решения задачи по заданному методу пользователь:

устанавливает курсор в выбранной ячейке (дважды щелкнуть по ячейке);
прописывает формулу, начиная со знака «=»;
мышкой кликает по тем ячейкам, которые нужно поочередно сложить;
после окончания формулы нажимает «Enter».

Функция ОСТАТ в Excel для работы с остатками товаров на складе

При выполнении самых разных иногда анализов возникает потребность в поиске и определении с выполнением вычислительных операций для непарных чисел в Excel. Чтобы не искать непарные числа вручную рационально автоматизировать этот процесс с помощью формулы где главную роль играет функция ЕСЛИ.

Пример работы функции ОСТАТ в Excel

Для примера возьмем магазин с летними и зимними автомобильными шинами. Клиент покупает 4 или 2 шины для своего автомобиля. Если на остатках в складе магазина встречаются непарные числа, то скорее всего на складе пересорт товара.

Следует сбалансировать все остатки до переучета. Для этого нужно определить все позиции с непарным количеством товара на остатках. Если склад большой, то список наименований шин с различными маркировками и параметрами (размеры, диаметр, производитель и т.д.) может содержать тысячи позиций. Поэтому вручную искать все «грехи» складовщиков – это трудозатратный процесс, требующий много времени. Рекомендуем быстрое решение:

Чтобы найти все непарные числа:

В ячейку C2 введите следующую формулу: 0;»Пересорт»;»»)’ >
Скопируйте эту формулу на против всех ячеек столбца «Остаток».

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

Принцип работы формулы для поиска непарных чисел:

В первом аргументе главной функции ЕСЛИ применяется функция ОСТАТ. В данном примере она возвращает нам остаток от деления на число 2. Если остаток от деления не равно нулю (так происходит в случае деления непарных чисел на 2), формулой сразу же возвращается текстовое значение «Пересорт». В противные случаи ячейка остается пустой.

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

Если остаток от деления = 0, то число парное.

Использование функции ОСТАТ в условном форматировании

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

Выделите диапазон B2:B11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
В появившемся диалоговом окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматируемых ячеек» и в поле ввода введите формулу: 0′ >
Нажмите на кнопку «Формат», чтобы задать оформление для ячеек с неправильными остатками.
На всех открытых диалоговых окнах нажмите на кнопку ОК.

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

Аннуитет. Расчёт в EXCEL погашение основной суммы долга

Рассчитаем в MS EXCEL остаток основной суммы долга, который требуется погасить после заданного количества периодов. Выплата кредита производится равными ежемесячными платежами (аннуитетная схема). Процентная ставка и величина платежа — известны, начисление процентов за пользование кредитом – также ежемесячное.

Аннуитетная схема предусматривает погашение кредита периодическими равновеликими платежами (как правило, ежемесячными), которые включают как выплату основного долга, так и процентный платеж за пользование кредитом. Такой равновеликий платеж называется аннуитет. В аннуитетной схеме погашения предполагается неизменность процентной ставки по кредиту в течение всего периода выплат. В статье Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа) показано как рассчитать величину регулярной суммы для погашения кредита или ссуды. В данной статье научимся вычислять остаток основной суммы долга, который требуется погасить после заданного количества периодов (а также сумму основного долга, которая была выплачена в промежутке между двумя периодами).

Вычисление остатка суммы основного долга (при БС=0, тип=0)

Пусть был взят кредит в размере 100 000руб. на 10 лет под ставку 9%. Кредит должен гаситься ежемесячными равными платежами (в конце периода). Требуется вычислить сумму основного долга, которая будет выплачена в первом месяце третьего года выплат. Решение простое – используйте функцию ОСПЛТ(): =ОСПЛТ(9%/12;25;10*12;100000) Ставка за период (ставка): 9%/12 Номер периода (первый месяц третьего года выплат): 25=2*12+1 Всего периодов (кпер): 10*12 Кредит: 100000 Ответ: Сумма основного долга, которая будет выплачена в первом месяце третьего года выплат: -618,26руб.

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

Вычислим ежемесячный платеж, используя формулу Приведенной стоимости . Обозначим сумму кредита как ПС, ежемесячный платеж как ПЛТ: ПС=ПМТ*(1-(1+ставка)^-кпер)/ставка. Отсюда, ПМТ=ПС* ставка /(1-(1+ставка)^-кпер)=1266,76 (правильность расчета можно проверить с помощью ПЛТ() – см. файл примера, лист Задача ). ПЛТ() вернет -1266,76. Знак минус указывает на различные направления денежных потоков + (из банка сумма кредита), — (в банк ежемесячные платежи). Формула приведенной стоимости является следствием того, что сумма долей ежемесячных платежей, идущих на погашение основной суммы долга, должна быть равна сумме кредита.

Доля платежа, которая идет на погашение основной суммы долга в 1-й период =ПМТ-ПС*ставка, а с учетом знаков =-ПМТ-ПС*ставка (чтобы сумма долей была того же знака, что и ПС). Обозначим эту долю как ПС1. ПС*ставка – это сумма процентов, уплаченная за пользование кредитом в первый период.
Доля платежа, которая идет на погашение основной суммы долга в 2-й период =-ПМТ-(ПС-ПС1)*ставка=-ПМТ-(ПС +ПМТ+ПС*ставка) *ставка=(-ПМТ-ПС*ставка)*(1+ставка)=ПС1*(1+ставка). Обозначим эту долю как ПС2. ПС-ПС1 – это остаток суммы долга в конце второго периода.
Доля платежа, которая идет на погашение основной суммы долга в 3-й период =-ПМТ-(ПС-ПС1-ПС2)*ставка=-ПМТ-(ПС-ПС1)*ставка+ПС2*ставка =ПС2+ПС2*ставка= ПС2*(1+ставка) =ПС1*(1+ставка)^2
Очевидно, что доля платежа, которая идет на погашение основной суммы долга в последний период (кпер)= ПС1*(1+ставка)^ кпер =-(ПМТ+ПС*ставка) *(1+ставка)^ кпер
Чтобы погасить кредит полностью, необходимо, чтобы сумма долей, идущих на погашение кредита, была равна сумме кредита, т.е. =-(ПМТ+ПС*ставка)*(1-(1+ставка)^ кпер)/ставка=ПС. Эта формула получена как сумма членов геометрической прогрессии: первый член =-(ПМТ+ПС*ставка), знаменатель =(1+ставка).
Решая нехитрое уравнение, полученное на предыдущем шаге, получим, что ПС=ПМТ*(1-(1+ставка)^-кпер)/ставка. Это и есть формула приведенной стоимости (при БС=0 и платежах, осуществляемых в конце периода (тип=0)).

Вычислим сумму основного долга, которую нужно будет выплатить начиная с 25-го месяца (т.е. в начиная с 25 и заканчивая 120 периодом). Сделаем это используя формулу приведенной стоимости ПС=ПМТ*(1-(1+ставка)^-кпер)/ставка. Теперь ПМТ нам известно, ПС – это искомая сумма основного долга, которую нужно будет выплатить, начиная с 25-го месяца, т.е. за 96 периодов (120-24=кпер). ПС=86466,91 Правильность расчета можно проверить с помощью ОБЩДОХОД() .
Вычислим сумму процентов, которые будут выплачены в 25-й месяц: 86466,91*ставка=648,50 Правильность расчета можно проверить с помощью ПРПЛТ() .
Наконец, т.к. каждый платеж содержит сумму, идущую в оплату основной суммы долга и начисленные за период проценты, то Сумму основного долга, которая будет выплачена в первом месяце третьего года выплат получим как: ПМТ-648,50=618,26

Читать еще: Как сопоставить столбцы в excel

Как видим, сумма совпадает результатом ОСПЛТ() , вычисленную ранее (с точностью до знака).

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

Данные расчеты можно сделать несколькими разными способами (см. файл примера ).

Способ 1. Функция ОБЩДОХОД() Функция ОБЩДОХОД(ставка; кол_пер; нз; нач_период; кон_период; тип) возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами ( нач_период и кон_период ).

Примечание . Английская версия функции: CUMPRINC(rate, nper, pv, start_period, end_period, type) returns the CUMulative PRincipal paid for an investment period with a Constant interest rate.

Аргументы функции: Ставка — обязательный аргумент. Процентная ставка за период. Кол_пер — обязательный аргумент (кол_пер – это аргумент кпер в других функциях аннуитета, например в ПЛТ()). Общее количество периодов выплат. Нз — обязательный аргумент (нз – это аргумент пс в других функциях аннуитета, например в ПЛТ()). Начальное значение (чаще всего — сумма кредита). Нач_период — обязательный аргумент. Номер первого периода, включенного в вычисления. Нумерация периодов выплат начинается с 1. Кон_период — обязательный аргумент. Номер последнего периода, включенного в вычисления. Тип — обязательный аргумент, определяющий время платежа. Для аннуитета постнумерандо Тип=0, для пренумерандо Тип=1.

Примечание . Убедитесь, что аргумент «Ставка» соответствуют ставке за период (период не обязательно = году). Например, при ежемесячных выплатах по 4-х летнему займу из расчета 12 процентов годовых используйте значение 12%/12 для аргумента «Ставка». Аргумент «Кол_пер» будет равен 4*12. При ежегодных платежах по тому же займу используйте значение 12% для аргумента «ставка» и 4 — для аргумента «Кол_пер». При ежеквартальных платежах по тому же займу используйте значение 12%/4 для аргумента «ставка» и 4*4 — для аргумента «Кол_пер». Примечание . Функция ОБЩДОХОД() возвращает значение ошибки #ЧИСЛО! если «Ставка» ≤ 0, «кол_пер» ≤ 0 или «нз» ≤ 0, «нач_период» «кон_период» «тип» является любым числом, отличным от 0 и 1 Примечание . Функция ОБЩДОХОД() возвращает значение ошибки #ЗНАЧ! если для «тип» использованы значения ЛОЖЬ или ИСТИНА.

Альтернативная формула

Из анализа альтернативной формулы ясно, что функция ОБЩДОХОД() может использоваться, только если БС=0, т.е. когда предполагается, что по прошествии количества периодов «Кол_пер» займ полностью погашается. Это ограничение можно обойти, записав выражение =ОБЩДОХОД(ставка; кол_пер; нз+БС; нач_период; кон_период; тип)+(нач_период=1)*тип*БС* ставка /(1+ ставка)

Способ 2. Функция ОСПЛТ()

Функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) используется для вычисления регулярных сумм идущих на погашение основной суммы долга практически с теми же аргументами, что и ПЛТ() .

Примечание . Английский вариант функции: PPMT(rate, per, nper, pv, [fv], [type]), т.е. Principal Payment – платеж основной части долга.

Чтобы вычислить сумму основного долга, которая была выплачена в промежутке между двумя периодами, нужно использовать не одну, а несколько функций ОСПЛТ() . Например, вычислим сумму долга, выплаченную в 3-м и 4-м периоде: =ОСПЛТ(ставка; 3; кпер; пс; [бс]; [тип])+ОСПЛТ(ставка; 4; кпер; пс; [бс]; [тип])

Чтобы вычислить сумму основного долга, которая была выплачена в промежутке между двумя произвольными периодами нач_период и кон_период используйте формулу: =СУММПРОИЗВ(ОСПЛТ(ставка;СТРОКА(ДВССЫЛ(нач_период&»:»&кон_период)); кпер; пс; [бс]; [тип]))

Способ 3. Вычисление суммы основного долга через Будущую стоимость

Функция БС(ставка; кпер; плт; [пс]; [тип]) возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки. Подробнее см. статью Аннуитет. Определяем в MS EXCEL Будущую Стоимость . Чтобы вычислить сумму основного долга, которая была выплачена в промежутке между двумя произвольными периодами нач_период и кон_период используйте формулу:

=- БС(ставка; кон_период; плт; [пс]; [тип]) /(1+тип *ставка) + БС(ставка; нач_период-1; плт; [пс]; [тип]) /ЕСЛИ(нач_период =1;1; 1+тип *ставка)

В файле примера также приведена эквивалентная формула без использования функции БС() .

Способ 4. Вычисление суммы основного долга через Приведенную стоимость

Функция ПС(ставка; кпер; плт; [бс]; [тип]) возвращает приведенную (к текущему моменту) стоимость инвестиций. Подробнее см. статью Аннуитет. Определяем в MS EXCEL Приведенную (Текущую) стоимость . Чтобы вычислить сумму основного долга, которая была выплачена в промежутке между двумя произвольными периодами нач_период и кон_период используйте формулу:

=ПС(ставка; кпер-кон_период; плт; [бс]; [тип]) /(1+тип *ставка) — ПС(ставка; кпер-нач_период+1; плт; [пс]; [тип]) /ЕСЛИ(нач_период =1;1; 1+тип *ставка)

В файле примера также приведена эквивалентная формула без использования функции ПС() . Чтобы убедиться в идентичности вышеуказанных способов, в файле примера приведен график платежей, рассчитанный на основе функции ОСПЛТ() .

Как в Excel посчитать сумму

MS Excel – табличный процессор, способный обрабатывать и систематизировать большие объемы информации.

Функция «Автосуммирование»

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

Читать еще: Сравнить значения столбцов в excel

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

выбирает ячейку, находящуюся в том же столбце что и числа, но несколько ниже;
кликает на пиктограмму суммы на панели инструментов;
MS Excel предлагает подсчитать все, что располагается выше обозначенной ячейки, но диапазон захватывает и пустые ячейки;
изменяет диапазон через выделение мышкой или ручной ввод диапазона;
для вступления изменений в силу нажимает «Enter».

Строка состояния

вводит числовую информацию в столбце;
выделяет диапазон.

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

Использование функций

Для нахождения результата пользователь:

выбирает ячейку отображения результата;
устанавливает курсор в строке формул;
вводит функцию =сумм(диапазон ячеек);
Нажимает «Enter».

Суммирование

Сумма с одним условием

Текстовое значение

Для решения задачи с одним условием в виде текстового значения юзер:

создает таблицу;
выбирает ячейку, где будет подсчитана сумма, и устанавливает курсор в строку формул;
вводит команду: =суммесли (условия выполнения).
последовательно указывает диапазон значений, условие отбора, диапазон суммирования.
для получения результата нажимает «Enter». В выбранной ячейке указывает сумму тех значений, которые удовлетворяют поставленному условию.

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

Второй параметр – критерий, указывающий, что нужно искать.

Критерий отбора указывается в кавычках.

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

Численное значение

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

формирует таблицу;
выбирает ячейку, где будет отображаться итог решения задачи;
переходит в строку формул;
вписывает функцию: =суммесли (условия выполнения);
действует согласно предыдущему алгоритму, указывая условия отбора и диапазоны значений;
для получения результата нажимает «Enter». В выбранной ячейке указывается сумма тех значений, которые удовлетворяют поставленному условию.

Сумма с несколькими условиями

формирует таблицу по заданному образцу;
выбирает ячейку, где будет отображаться итог решения задачи;
переходит в строку формул;
вписывает функцию: =суммеслимн (условия выполнения).
последовательно указывает диапазон сложения, диапазон условия1, условие1 и т.д.;
для получения результата нажимает «Enter». В выбранной ячейке указывает сумма тех значений, которые удовлетворяют поставленному условию.

Второй параметр – диапазон условия 1, указывающий столбец, в котором проходит фильтрация по первому условию.

Третий параметр – условие1, которое указывается в кавычках и задает фильтрацию.

Условий отбора может множество.

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

Произведение двух столбцов

вводит значения в 2 столбца;
выбирает ячейку, где отображается результат;
устанавливает курсор в строку формул и вводит текст:=сумм (диапазон ячеек одного столбца ) *сумм(диапазон ячеек другого столбца);
для получения результата нажимает «Enter».

Для получения произведения двух столбцов пользователь:

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

Ручной ввод формулы

устанавливает курсор в выбранной ячейке (дважды щелкнуть по ячейке);
прописывает формулу, начиная со знака «=»;
мышкой кликает по тем ячейкам, которые нужно поочередно сложить;
после окончания формулы нажимает «Enter».