Маржа и наценка в Excel
Выберите, что считать, и укажите буквы своих столбцов — генератор соберёт формулу, которую остаётся вставить в ячейку и протянуть вниз. Или скачайте готовый шаблон с маржой, наценкой и ценой под нужный процент.
Три листа: маржа и доля в прибыли по товарам со средневзвешенным итогом, цена по наценке с округлением, цена под нужную маржу с учётом комиссий. Белые ячейки — ввод, остальное считается само.
Основные формулы
| Задача | Формула (закупка в A, цена в B, процент в C) | Формат ячейки |
|---|---|---|
| Маржа, % | =(B2-A2)/B2 | процентный |
| Наценка, % | =(B2-A2)/A2 | процентный |
| Маржа, ₽ | =B2-A2 | денежный |
| Цена по наценке | =A2*(1+C2) | денежный |
| Цена по марже | =A2/(1-C2) | денежный |
| Закупка из цены | =B2/(1+C2) | денежный |
Откуда берутся эти формулы и как считать наценку в задачах посложнее — с НДС, доставкой и скидкой — разобрали в статье «Как посчитать наценку на товар».
Почему формула показывает не то
Посчитать маржу в экселе умеют почти все, а ошибаются чаще не в математике, а в формате ячеек. Вот что мы встречаем, когда разбираем прайсы клиентов:
- Вместо 30% получается 3 000%. В ячейке с процентом стоит число 30, а не 30%. Excel хранит 30% как 0,3. Либо введите значок процента, либо делите на 100 прямо в формуле: =A2*(1+C2/100).
- Ошибка #ДЕЛ/0! В строке нет закупки или цены. Оберните формулу: =ЕСЛИОШИБКА((B2-A2)/B2;"") — пустые строки останутся пустыми.
- Маржа 0,25 вместо 25%. Формула верная, у ячейки общий формат. Выберите «Процентный» на вкладке «Главная».
- После протягивания всё съехало. Если процент один на весь прайс и лежит в одной ячейке, например F1, закрепите её: =A2*(1+$F$1). Без долларов при протягивании Excel начнёт брать F2, F3 и дальше — пустые.
- Средняя маржа завышена. Функция СРЗНАЧ по столбцу процентов игнорирует, что товары продаются в разных количествах и по разным ценам. Нужна взвешенная: суммарная маржа в рублях, делённая на суммарную выручку. В генераторе это пункт «Средняя маржа по списку».
Как округлить цену с наценкой
Цены вида 5 872,50 ₽ на витрине выглядят странно. Округлять нужно вверх, иначе наценка просядет ниже расчётной. Формула =ОКРВВЕРХ(A2*(1+C2)+1;100)-1 даёт окончание …99: 5 872,50 превращается в 5 899. Для окончания на 9 возьмите шаг 10, для …990 — шаг 1000 и поправку 10 вместо 1.
В английской версии и в Google Таблицах функция называется CEILING, а аргументы разделяются запятой. Генератор учитывает это сам — переключите язык в поле справа.
Когда Excel уже не справляется
Таблица хороша, пока товаров сотни, а цены поставщиков приходят раз в месяц. Дальше начинается ручная работа: выгрузить остатки, подтянуть новые закупочные цены, пересчитать, загрузить обратно на сайт и в маркетплейсы. Каждый шаг — место для ошибки. Мы автоматизируем этот цикл: прайсы поставщиков загружаются сами, правила наценки и округления живут в 1С или МойСклад, цены на сайте и маркетплейсах обновляются по расписанию. А маржу по каждому заказу видно в отчёте, без СУММПРОИЗВ.
Вопросы
Введите формулу =(B2-A2)/A2 в первую ячейку, задайте процентный формат и дважды щёлкните по маркеру в правом нижнем углу ячейки — Excel протянет формулу до конца таблицы.
Да. Выберите в генераторе английский язык: Google Таблицы понимают английские имена функций и запятую между аргументами. Шаблон .xlsx тоже открывается в Google Таблицах без потерь.
Нет, только обычные формулы — файл .xlsx не может содержать макросов. Его можно спокойно открыть, Excel не попросит включать содержимое.