Проценты и округление

Месячный отчёт по продажам почти целиком состоит из процентов. В нём нужно ответить на три вопроса:

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

Ответ на каждый даёт короткая формула. А когда в ценах появляются копейки, понадобится ещё и округление.

Доля от итога

Доля равна части, делённой на целое. Если менеджер принёс 120 000 ₽ из 400 000 ₽, его доля 120 000 ÷ 400 000 = 0,3, то есть 30%.

В таблице долю считают формулой. Ниже её написали для Анны, =B2/$B$6, и протянули вниз. Ссылку на итог закрепили знаками доллара, иначе при протягивании он съехал бы на пустые ячейки B7, B8 и дальше.

Формула даёт дробь 0,3, а 30% в ячейке видно благодаря процентному формату. Его ставят так: выделите ячейки с долями и на вкладке «Главная» в списке «Числовой формат» выберите «Процентный». Формат умножает число на 100 только на экране, в ячейке по-прежнему хранится 0,3.

Прирост

Прирост показывает, на сколько процентов изменилось число по сравнению с прежним. Выручка выросла с 200 000 ₽ до 230 000 ₽, то есть на 30 000 ₽. Проценты считают от того, с чего начинали: 30 000 ÷ 200 000 = 0,15, или 15%.

В формуле из нового значения вычитают старое и делят на старое:

=(B3-B2)/B2

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

Прибавить или вычесть процент

Цена с наценкой 20% — это вся цена и ещё 20% от неё, то есть 120% цены. Для Excel 100% это 1, а 20% это 0,2. Значит, цену нужно умножить на 1 + 0,2. Так же считают и цену с НДС: при ставке 22% цену умножают на 1 + 0,22.

Сам процент в формулу не вписывают, а держат в отдельной ячейке, например в F1. Если наценка поменяется, достаточно исправить одну ячейку, и все цены пересчитаются. Цену с наценкой считает такая формула:

=B2*(1+$F$1)

Единица здесь означает всю цену, а к ней прибавляется процент из F1. Скобки нужны, чтобы Excel сначала сложил 1 и процент и только потом умножил. Знаки доллара держат F1 на месте, когда формулу протягивают вниз: цены у товаров разные, а наценка одна.

Скидку считают так же, только процент вычитают: =B2*(1-$F$1). Если в F1 скидка 15%, цена умножится на 0,85.

Процент к цене не прибавляют напрямую. Формула =B2+$F$1 прибавит к цене не 20%, а число 0,2: для Excel 20% это просто 0,2.

Наценка и скидка любого размера сводятся к одному множителю:

=B2*(1+$F$1)

Округление

Формат с двумя знаками после запятой не округляет число, а только прячет лишние знаки. Считает Excel по настоящим значениям. Поэтому итог иногда расходится на копейку с суммой цен, которые видно на экране.

Чтобы округлить само значение, есть функция ОКРУГЛ. В скобках ей передают два аргумента через точку с запятой: сначала число или формулу, которую нужно округлить, потом сколько знаков после запятой оставить. Два знака оставляют копейки, ноль округляет до целых рублей.

Бывает, что округлять нужно только вверх: на 41 кружку по 12 штук в коробке нужно 4 коробки, хотя 41/12 даёт 3,4. Для этого есть ОКРУГЛВВЕРХ с теми же двумя аргументами: =ОКРУГЛВВЕРХ(41/12;0) вернёт 4.

Если ставка НДС лежит в F1, цену с НДС до копеек считает такая формула:

=ОКРУГЛ(B2*(1+$F$1);2)

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

Главное

• Доля равна части, делённой на целое: =B2/$B$6. Итог закрепляют знаками $, а процентный формат показывает 0,3 как 30%.
• Прирост равен разности нового и старого значения, делённой на старое. Скобки обязательны.
• Процент держат в отдельной ячейке. Цену с наценкой считают как цену, умноженную на 1 плюс процент, со скидкой как цену, умноженную на 1 минус процент.
• Формат только прячет знаки. Функция ОКРУГЛ меняет само значение, и итог сходится с экраном.