Как в экселе создать формулу с процентами. Как вычислить процент в экселе

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

Формула расчета процентов. Как посчитать проценты в Экселе?

Проценты (латин. pro centum ) – являются неотъемлемой частью финансовой математики и используются в банковском секторе, финансах, бухгалтерии, страховании, налогообложении и т.д. Так в виде процентов выражают доходность и прибыльность предприятия, ставку по банковским кредитам и займам, налоговые ставки и т.д. Можно привести в подтверждение важности процентов в финансовом мире высказывание А. Энштейна: «Сложные проценты – самая мощная сила в природе». Перед тем как рассмотреть формулы расчета процентов введем основные термины и понятия.

• Капитал (англ. Capital, Principal ) – является базой относительно которого вычисляют процент.
• Частота начисления процентов – период выплат процентов на капитал.
• Процентная ставка (англ. Rate ) – размер процента или доля капитала, который будет выплачен.
• Период вложения (англ. Period ) – временной интервал передачи капитала банку или другому финансовому институту.

Итак, рассмотрим различные эконометрические задачи с процентами.

Формула расчета доли

Расчет доли часто необходим в бухгалтерском и финансовом учете, где необходимо определить долю тех или иных видов активов по отношению к суммарным. На рисунке ниже приведен пример и бухгалтерские данные по предприятию ОАО «АЛРОСА».

Доля запасов в Активах =B6/B7

Расчет доли в процентах

Для того чтобы в ячейке полученные доли имею процентный вид можно воспользоваться сочетанием клавиш «Ctrl» + «Shift» + «%».

Формула расчета размера процента от капитала

Рассмотрим вторую постоянно встречающуюся экономическую задачу: расчет абсолютного значения процента по капиталу.

Пример задачи . Необходимо определить размер выплаты банка ОАО «Альфа-банк» по вкладу в размере 100000 руб. с процентной ставкой 15%. Формула расчета размера выплат будет иметь следующий вид:

Выплаты по вкладу в банке =B6*B7

Расчет выплат по вкладу в банк в Excel

Формула расчета размера капитала с процентами

Пример задачи . Требуется рассчитать итоговую стоимость товара с НДС (налог на добавленную стоимость), тогда как стоимость товара без НДС составляет 10000 руб., процентная ставка налога равна 18%. Формула расчета стоимости товара с учетом НДС рассчитывается по формуле:

Стоимость товара с учетом НДС =B6*(1+B7)

Расчет стоимости с учетом НДС в Excel

Формула уменьшения размера капитала на процент

Остаток капитала =B6-B6*B7

Расчет стоимости капитала после вычета процентов

Формула расчета очищенной стоимости капитала (продукции)

Стоимость продукции без НДС =B6/(1+B7)

Пример расчета очищенной стоимости продукции без НДС в Excel

Формула расчета простых процентов по банковскому вкладу

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

Пример задачи. Требуется рассчитать размер выплат по банковскому вкладу, на который начисляется простые проценты. Размер вклада составляет 150000 руб., годовая процентная ставка по вкладу равна 12% (за 365 дней), период размещения вклада составляет 300 дней. Формула расчета в Excel следующая:

Размер банковского вклада на конец периода размещения =(1+B8*300/365)*B6

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

Формула расчета сложных процентов по банковскому вкладу

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

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

Формула расчета сложных процентов:

Размер вклада со сложными процентами на конец года =B6*(1+B8*B9/B7)^4

Пример расчета сложных процентов в Excel

Видео-урок: “Примеры расчета простых и сложных процентов”

Резюме

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

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

Кликните по кнопке ниже для загрузки Excel файла с примерами расчетов процентов в Excel:

Что такое процент?

Проце́нт (лат. per cent - на сотню) - одна сотая часть. Отмечается знаком «%». Обозначает долю чего-либо по отношению к целому. Например, 25 % от 500 руб. означает 25 частей по 5 руб. каждая, то есть 125 руб.

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

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

Как посчитать проценты от суммы в Excel

Видео урок:

(Часть/Целое) * 100 = Процент (%)

Но если использовать формат ячейки “Процентный”, то для вычисления процента от числа достаточно поделить одно число на другое. Например, у нас есть яблоки, которые мы купили по 100 руб. на оптовом складе, а розничную цену выставим 150 руб. Для того чтобы высчитать процент того, сколько составляет закупочная цена от розничной, нам потребуется:

• Составить таблицу с ценами и добавить колонку для вычисления величины процента:

• В ячейку D2 внести формулу, вычисляющую процент цены закупки от цены продажи:

• Применить формат ячейки D2 “Процентный”:

Как посчитать процент от суммы значений таблицы Excel

Видеоурок:

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

Для этого нам нужно:

• Добавить колонку к таблице с расчетом процента;
• В ячейку C2 вставить формулу:

Значки $ фиксируют колонку “B” и ячейку “9” для того, чтобы при протягивании формулы на все строки таблицы, Excel автоматически подставлял объем продаж каждого продавца и высчитывал % от общего объема продаж. Если не поставить значки “$”, то при протягивании формулы, система будет смещать ячейку B9 на столько ячеек вниз, на сколько вы протяните формулу.

• Протянуть формулу на все ячейки таблицы, соответствующие строкам с фамилиями продавцов:

• На примере выше мы протянули формулу и получили значения в виде дробных чисел. Для того чтобы перевести полученные данные в проценты выделите данные левой клавишей мыши и смените формат ячеек на “Процентный”:

Как вычислить процент нескольких значений от суммы таблицы

Видеоурок:

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

Из списка товаров выше мы хотим вычислить какой объем продаж составляют помидоры (они записаны в нескольких строках таблицы). Для этого:

• Справа от таблицы укажем товар (Помидоры), по которым хотим рассчитать долю в продажах:

• Сменим формат ячейки E2 на “Процентный”;
• В ячейку E2 вставим формулу с функцией , которая поможет вычислить из списка товаров Помидоры и суммировать их объем продаж, а затем поделить его на общий объем продаж товаров:

Как работает эта формула?

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

Синтаксис функции СУММЕСЛИ:

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

• диапазон – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
• условие – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
• диапазон_суммирования – суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент диапазон в качестве диапазон_суммирования .

Таким образом, в формуле =СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9 мы указали “$A$2:$A$8 ” как диапазон товаров, среди которых функция будет искать нужный нам критерий (Помидоры). Ячейка “$E$1 ” указана в качестве критерия и указывает что мы ищем “Помидоры”. Диапазон ячеек “$B$2:$B$8 ” обозначает какие ячейки нужно суммировать, в случае если искомый критерий был найден.

Как посчитать разницу в процентах в Excel

Например, для расчета разницы в процентах между двумя числами А и В используется формула:

(B-A)/A = Разница между двумя числами в процентах

На практике, при использовании этой формулы важно определить какое из нужных вам чисел является “А”, а какое “В”. Например, представим что вчера у вас было в наличии 8 яблок, а сегодня стало 10 яблок. Таким образом количество яблок относительно вчерашнего у вас изменилось на 25% в большую сторону. В том случае, если у вас вчера было 10 яблок, а сегодня стало 8 яблок, то количество яблок, относительно вчерашнего дня сократилось на 20%.

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

(Новое значение – Старое значение) / Старое значение = Разница в процентах между двумя числами

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

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

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

В результате у нас должна получится, например, такая формула (число -(число * процент, который нужно отнять) . Нажимаем Ентер.

1. Итак сначала необходимо выбрать ячейку, куда будет посчитан процент и будет оформлен результат. В этой ячейке ставим знак = (равно)…
2. Потом находим ту ячейку с числом, от которого нам надо вычислить процент и вводим е адрес в выбранную ячейку…
3. За адресом ячейки ставим знак * (умножение)…
4. За знаком умножение цифрами прописываем тот процент, который нам надо вычислить, либо выбираем ячейку, в которой этот процент уже прописан и вводим е адрес…
5. Следом за числом или адресной строкой ставим знак % (проценты)…
6. Далее просто надо нажать Enter (энтер)…

У вас появится цифра, которая и будет заданным числом процента…

Выглядит это примерно так:

=G9*H15%

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

• для начала становимся на пустую ячейку, где мы хотели бы видеть конечный результат;
• начинаем формулу со знака =;
• указывает число, от которого необходимо отнять проценты. Если данное число уже прописано в файле, то мышкой кликаем на ячейку, где оно значится;
• ставим знак вычитания -;
• открываем скобку (;
• снова кликаем на число от которого отнимаем процент;
• ставим знак умножения *;
• пишем количество процентов, после которых надо указать символ %;
• закрываем круглую скобку;
• нажимаем Enter.

• Я постараюсь изложить вкратце ваш вопрос. Алгоритм действий такой:

  • в пустой ячейке ставим знак =.
  • пишем адрес той ячейки, откуда отнимаем процент.
  • пишем знак *.
  • пишем количество процентов и знак %.

• Уравнение для вычитание следующее:amount/total=percentage(десятичной формат процентной доли)

  Допустим запишись формулу =A2/C2 в ячейке D2 и еще скопировать ее вниз на столько строчек сколько нужно,для этого можно воспользоваться маркером автозаполнения.далее нажимайте команду Percent Style на вкладке Home в группе команд Number.да и еще по необходимости нужно будет настроить количество знаков справа от запятой.

  В этой программе можно узнать процент от общей суммы двумя способами;

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

  Второй вариант это прописать его в строке и в последствии произвести вычисление.

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

  В ячейке G6 вбита формула =E6-(E6*F6). В ячейке F6 проставлен процентный формат. Если вам, допустим (как на примере) надо от 100 отнять 10%, то вы в ячейку Е6 вводите число 100, в ячейку F6 нужный процент и в итоговой ячейке G6 получаете результат.

  Выбираете функции, потом математические, а там %. Хотя мне удобнее самому формулу написать. % — это часть от 100. Поэтому если надо вычесть, например, 27%, то я пропишу как формулу: (графа, где нужное число)*0,73.

  Например, надо от 100 отнять 10%. Вводим формулу =100-(100*0,1) Получается 90. Ну а дальше зависит от фантазии, можно намного облегчить этот процесс (если надо часто складывать и отнимать проценты) разбив данные действия по ячейкам и по процентам.

  

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

  Чтобы вычесть процент из числа в данной программе, нужно сделать следующее:

  • ставим курсор в пустую ячейку, в которой у нас будет результат,
  • начинаем составлять формулу, сначала пишем знак =;
  • далее пишем число, от которого мы отнимаем проценты, можно также кликнуть по нему, если число уже стоит в ячейке,
  • затем пишем знак -;
  • далее ставим знак (;
  • кликаем по числу, от которого нужно отнять проценты,
  • далее пишем знак умножения * и указываем количество процентов,
  • затем пишем знак %, а затем );

  теперь осталось нажать Enter.

Процент – это одна сотая часть чего-либо, которая обозначается знаком «%». Например, если у вас есть 50 000 рублей, то двадцать процентов от этой суммы будут составлять 10 000 рублей (то есть двадцать сотых частей по 500 рублей). Соответственно, сто процентов будут составлять целое значение, то есть 50 000 рублей. Необходимость вычислять проценты испытывает работник практически каждой сферы, особенно экономической.

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

• налог на добавленную стоимость (НДС);
• подоходный налог (НДФЛ);
• наценки;
• маржи;
• проценты по кредиту;
• рентабельность инвестиций.

И это лишь малая часть из того, где вам может пригодиться умение рассчитывать проценты. Программа Microsoft Excel, которая предназначена для работы с таблицами, содержит в себе широкий инструментарий для расчёта процентов разных видов. Теперь, когда мы кратко прошлись по теории, настало время перейти к практике и разобраться в том, на что способен Эксель. Чтобы вам было легче понять суть расчётов, мы рассмотрим всё на конкретных примерах.

Самый простой процентов – это найти процент от какого-то значения. Например, вам нужно посчитать, сколько будет составлять 20% от числа 400. Чтобы провести такие расчёты в Экселе, проделайте следующее:

Обратите внимание, что в ячейке «B2» вы указывали знак процента. Делается это для того, чтобы перевести значение ячейки в процентный формат. Можно посчитать процент от числа без форматирования ячейки, пользуясь при этом стандартной формулой расчёта процентов. Для этого уберите соответствующий значок с ячейки «B2» и введите формулу «=A2/100*B2» .

Процент от суммы

Если вам нужно узнать, какой процент от одного числа составляет другое, то это также можно легко реализовать в Microsoft Excel. Рассмотрим это на конкретном примере. Предположим, вы купили на оптовом рынке яблоки по цене 100 рублей за штуку, а розничную цену вы указали в размере 150 рублей. Чтобы узнать процент закупочной цены от розничной, проделайте следующие шаги:

1. Внесите значения в свободные ячейки таблицы.

   

2. Выберите свободную ячейку, в какую будет выведен результат.

   

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

   

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

   

5. Укажите для неё формулу «=C2/B2» в поле ввода для формул, жмите клавишу «Enter».

   

Вы наверняка заметили, что в ячейках «B2» и «C2» указан знак российского рубля. Он не был добавлен туда вручную (если бы он был добавлен, то элемент ячейки преобразовался бы из числового элемента в строчный, из-за чего было бы невозможно провести с ними любые математические операции, включая подсчёт процента). Для этой ячейки был указан денежный формат, который устанавливается так же, как и процентный. По умолчанию в ячейках такого формата указывается рубль в виде валюты.

Процент от суммы значений

Рассмотрим ещё один пример. У вас есть список сотрудников вашей фирмы и показатель объёма продаж каждого из этих сотрудников. Вы хотите проанализировать эффективность работы каждого сотрудника, и для этого вам нужно узнать, какой процент из общего объёма продаж составляет каждый отдельный показатель. Чтобы провести такую операцию в Microsoft Excel, делайте всё так, как описано в инструкции:

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

   

2. Задайте справа от первого значения (в данном случае в ячейке «C2» ) формулу «=B2/$B$9» .

   

3. Растяните эту формулу на все ячейки таблицы, которые соответствуют фамилиям сотрудников.

   

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

   

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

Ячейка «B9» , в которой содержится значение итоговой суммы всех показателей объёма продаж, в данном примере в формуле была выделена символом «$» . Сделано это было для того, чтобы «закрепить» значение и производить деление показателей на итоговую сумму, а не на ячейку, расположенную после него. Если бы этого сделано не было, то показатели Петрова делились бы на ячейку «B10» , в которой ничего не прописано, Сидорова – на «B11» , и так далее.

Процент более одного значения

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

Теперь рассмотрим подробнее эту на первый взгляд сложную формулу функция «СУММЕСЛИ» рассчитывает сумму значений, которые соответствуют заданным критериям. Её синтаксис состоит из указания диапазона значений (в данном случае текстовых), условия (в данном случае это ячейка «E1» со значением «Помидоры» ) и диапазона суммирования. Все эти параметры разделены точкой с запятой, а диапазон задаётся двоеточием. Сначала эта функция проверяет значения ячеек от «A2» до «A8» и ищет в них соответствие значению «E1» , то есть ищет в названиях помидоры, затем суммирует объёмы продаж «помидоров» и делит их на общую сумму (ячейка «B9 »).

Разница в процентах

Формула вычисления разницы в процентах следующая: «(Текущее значение — Предыдущее значение)/Предыдущее значение = Разница между этими значениями в процентах» . Перейдём сразу к примеру.

У вас есть показатели продаж за год, и вы хотите выяснить, как менялись эти показатели в каждом месяце относительно предыдущего. И всё это в процентах. Microsoft Excel поможет вам решить эту задачу, если вы будете следовать приведённой ниже инструкции:

1. Как вы уже догадались, нужно сперва заполнить ячейки.

   

2. В ячейку «C3» вбейте формулу «=(B3-B2)/B2» и растяните её до «C13» . Ячейке «C3» соответствует значение результата за февраль, поэтому начинать следует с него, а не с января. Ведь январь – это первый отчётный месяц, который не с чем сравнивать.

   

   

   Важно! В ячейках с результатом выставьте процентный формат.

3. Если вам нужно зафиксировать какое-то значение, то выделите его знаком «$» . Например, вы хотите узнать, как менялись показатели продаж относительно января, а не каждого предыдущего месяца. Тогда делайте то же самое, что и в предыдущей инструкции, только «закрепите» значение января («B2» ), поменяв формулу на «=(B3-B2)/$B$2» .

   

   

Добавление и вычитание процента к числу

Представим, что вы собираетесь ехать на неделю в отпуск и планируете свои расходы. Вы собираетесь во время отпуска тратить на 30% больше средств, чем в обычную неделю, а в следующую неделю после отпуска – на 30% меньше. В обычную неделю вы тратите 20 000 рублей.

Справка! В первой формуле к обычным затратам прибавляется значение, составляющее 30% от них, во втором оно отнимается.

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

Видео — Как посчитать проценты в Excel

Создание диаграммы

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

1. Выделите мышкой всю таблицу, в том числе и названия столбцов (то есть весь диапазон от «A1» до «B7» ), затем во вкладке «Вставка» нажмите на «Диаграмма» и выберите вид диаграммы. В этом примере мы выберем круговую.

   

   

2. Сразу же после того, как вы указали тип диаграммы, она отобразится на вашей странице в Excel.

   

3. Чтобы добавить в диаграмму подписи, кликните по ней правой клавишей и нажмите на «Добавить подписи данных» . После этого на ней появятся значения всех экономических показателей.

   

4. Чтобы отобразить значения в процентах, не используя при этом дополнительных формул, вам нужно будет совершить ещё несколько операций. Сначала кликните правой клавишей мыши по диаграмме и выберите «Формат подписей данных…» .

   

   Кликаем правой клавишей мыши по диаграмме, выбираем «Формат подписей данных…»

5. Снимите галочку со «Значения» и установите её возле «Доли» .

   

6. В подгруппе «Число» измените числовой формат на процентный и уберите десятичные знаки.

   

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

Видео — Создание круговой диаграммы в Excel 2010

В этом уроке Вы увидите, как при помощи Excel быстро вычислить проценты, познакомитесь с основной формулой расчёта процентов и узнаете несколько хитростей, которые облегчат Вашу работу с процентами. Например, формула расчёта процентного прироста, вычисление процента от общей суммы и кое-что ещё.

Умение работать с процентами может оказаться полезным в самых разных сферах жизни. Это поможет Вам, прикинуть сумму чаевых в ресторане, рассчитать комиссионные, вычислить доходность какого-либо предприятия и степень лично Вашего интереса в этом предприятии. Скажите честно, Вы обрадуетесь, если Вам дадут промокод на скидку 25% для покупки новой плазмы? Звучит заманчиво, правда?! А сколько на самом деле Вам придётся заплатить, посчитать сможете?

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

Базовые знания о процентах

Термин Процент (per cent) пришёл из Латыни (per centum) и переводился изначально как ИЗ СОТНИ . В школе Вы изучали, что процент – это какая-то часть из 100 долей целого. Процент рассчитывается путём деления, где в числителе дроби находится искомая часть, а в знаменателе – целое, и далее результат умножается на 100.

Основная формула для расчёта процентов выглядит так:

(Часть/Целое)*100=Проценты

Пример: У Вас было 20 яблок, из них 5 Вы раздали своим друзьям. Какую часть своих яблок в процентах Вы отдали? Совершив несложные вычисления, получим ответ:

(5/20)*100 = 25%

Именно так Вас научили считать проценты в школе, и Вы пользуетесь этой формулой в повседневной жизни. Вычисление процентов в Microsoft Excel – задача ещё более простая, так как многие математические операции производятся автоматически.

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

Я хочу показать Вам некоторые интересные формулы для работы с данными, представленными в виде процентов. Это, например, формула вычисления процентного прироста, формула для вычисления процента от общей суммы и ещё некоторые формулы, на которые стоит обратить внимание.

Основная формула расчёта процента в Excel

Основная формула расчёта процента в Excel выглядит так:

Часть/Целое = Процент

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

А теперь посмотрим, как расчёт процентов в Excel может помочь в реальной работе с данными. Допустим, в столбец В у Вас записано некоторое количество заказанных изделий (Ordered), а в столбец С внесены данные о количестве доставленных изделий (Delivered). Чтобы вычислить, какая доля заказов уже доставлена, проделаем следующие действия:

• Запишите формулу =C2/B2 в ячейке D2 и скопируйте её вниз на столько строк, сколько это необходимо, воспользовавшись маркером автозаполнения.
• Нажмите команду Percent Style (Процентный формат), чтобы отображать результаты деления в формате процентов. Она находится на вкладке Home (Главная) в группе команд Number (Число).
• При необходимости настройте количество отображаемых знаков справа от запятой.
• Готово!

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

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

Расчёт процента от общей суммы в Excel

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

Пример 1. Общая сумма посчитана внизу таблицы в конкретной ячейке

Очень часто в конце большой таблицы с данными есть ячейка с подписью Итог, в которой вычисляется общая сумма. При этом перед нами стоит задача посчитать долю каждой части относительно общей суммы. В таком случае формула расчёта процента будет выглядеть так же, как и в предыдущем примере, с одним отличием – ссылка на ячейку в знаменателе дроби будет абсолютной (со знаками $ перед именем строки и именем столбца).

Например, если у Вас записаны какие-то значения в столбце B, а их итог в ячейке B10, то формула вычисления процентов будет следующая:

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

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

Пример 2. Части общей суммы находятся в нескольких строках

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

В этом случае используем функцию SUMIF (СУММЕСЛИ). Эта функция позволяет суммировать только те значения, которые отвечают какому-то определенному критерию, в нашем случае – это заданный продукт. Полученный результат используем для вычисления процента от общей суммы.

SUMIF(range,criteria,sum_range)/total
=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)/общая сумма

В нашем примере столбец A содержит названия продуктов (Product) – это диапазон . Столбец B содержит данные о количестве (Ordered) – это диапазон_суммирования . В ячейку E1 вводим наш критерий – название продукта, по которому необходимо рассчитать процент. Общая сумма по всем продуктам посчитана в ячейке B10. Рабочая формула будет выглядеть так:

SUMIF(A2:A9,E1,B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10

Кстати, название продукта можно вписать прямо в формулу:

SUMIF(A2:A9,"cherries",B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;"cherries";B2:B9)/$B$10

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

=(SUMIF(A2:A9,"cherries",B2:B9)+SUMIF(A2:A9,"apples",B2:B9))/$B$10
=(СУММЕСЛИ(A2:A9;"cherries";B2:B9)+СУММЕСЛИ(A2:A9;"apples";B2:B9))/$B$10

Как рассчитать изменение в процентах в Excel

Одна из самых популярных задач, которую можно выполнить с помощью Excel, это расчёт изменения данных в процентах.

Формула Excel, вычисляющая изменение в процентах (прирост/уменьшение)

(B-A)/A = Изменение в процентах

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

Пример: Вчера у Вас было 80 яблок, а сегодня у Вас есть 100 яблок. Это значит, что сегодня у Вас на 20 яблок больше, чем было вчера, то есть Ваш результат – прирост на 25%. Если же вчера яблок было 100, а сегодня 80 – то это уменьшение на 20%.

Итак, наша формула в Excel будет работать по следующей схеме:

(Новое значение – Старое значение) / Старое значение = Изменение в процентах

А теперь давайте посмотрим, как эта формула работает в Excel на практике.

Пример 1. Расчёт изменения в процентах между двумя столбцами

Предположим, что в столбце B записаны цены прошлого месяца (Last month), а в столбце C – цены актуальные в этом месяце (This month). В столбец D внесём следующую формулу, чтобы вычислить изменение цены от прошлого месяца к текущему в процентах.

Эта формула вычисляет процентное изменение (прирост или уменьшение) цены в этом месяце (столбец C) по сравнению с предыдущим (столбец B).

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

Пример 2. Расчёт изменения в процентах между строками

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

Здесь C2 это первое значение, а C3 это следующее по порядку значение.

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

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

Например, вот так будет выглядеть формула для расчёта процентного изменения для каждого месяца в сравнении с показателем Января (January):

Когда Вы будете копировать свою формулу из одной ячейки во все остальные, абсолютная ссылка останется неизменной, в то время как относительная ссылка (C3) будет изменяться на C4, C5, C6 и так далее.

Расчёт значения и общей суммы по известному проценту

Как Вы могли убедиться, расчёт процентов в Excel – это просто! Так же просто делается расчёт значения и общей суммы по известному проценту.

Пример 1. Расчёт значения по известному проценту и общей сумме

Предположим, Вы покупаете новый компьютер за $950, но к этой цене нужно прибавить ещё НДС в размере 11%. Вопрос – сколько Вам нужно доплатить? Другими словами, 11% от указанной стоимости – это сколько в валюте?

Нам поможет такая формула:

Total * Percentage = Amount
Общая сумма * Проценты = Значение

Предположим, что Общая сумма (Total) записана в ячейке A2, а Проценты (Percent) – в ячейке B2. В этом случае наша формула будет выглядеть довольно просто =A2*B2 и даст результат $104.50 :

Важно запомнить: Когда Вы вручную вводите числовое значение в ячейку таблицы и после него знак %, Excel понимает это как сотые доли от введённого числа. То есть, если с клавиатуры ввести 11%, то фактически в ячейке будет храниться значение 0,11 – именно это значение Excel будет использовать, совершая вычисления.

Другими словами, формула =A2*11% эквивалентна формуле =A2*0,11 . Т.е. в формулах Вы можете использовать либо десятичные значения, либо значения со знаком процента – как Вам удобнее.

Пример 2. Расчёт общей суммы по известному проценту и значению

Предположим, Ваш друг предложил купить его старый компьютер за $400 и сказал, что это на 30% дешевле его полной стоимости. Вы хотите узнать, сколько же стоил этот компьютер изначально?

Так как 30% – это уменьшение цены, то первым делом отнимем это значение от 100%, чтобы вычислить какую долю от первоначальной цены Вам нужно заплатить:

Теперь нам нужна формула, которая вычислит первоначальную цену, то есть найдёт то число, 70% от которого равны $400. Формула будет выглядеть так:

Amount/Percentage = Total
Значение/Процент = Общая сумма

Для решения нашей задачи мы получим следующую форму:

A2/B2 или =A2/0,7 или =A2/70%

Как увеличить/уменьшить значение на процент

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

Чтобы увеличить значение на процент, используйте такую формулу:

Значение*(1+%)

Например, формула =A1*(1+20%) берёт значение, содержащееся в ячейке A1, и увеличивает его на 20%.

Чтобы уменьшить значение на процент, используйте такую формулу:

Значение*(1-%)

Например, формула =A1*(1-20%) берёт значение, содержащееся в ячейке A1, и уменьшает его на 20%.

В нашем примере, если A2 это Ваши текущие расходы, а B2 это процент, на который Вы хотите увеличить или уменьшить их значение, то в ячейку C2 нужно записать такую формулу:

Увеличить на процент: =A2*(1+B2)
Уменьшить на процент: =A2*(1-B2)

Как увеличить/уменьшить на процент все значения в столбце

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

Нам потребуется всего 5 шагов для решения этой задачи:

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

В результате значения в столбце B увеличатся на 20%.

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

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

На сегодня всё, благодарю за внимание!

← Вернуться