Как составить таблицу долей выручки от продажи в Excel
Как составить таблицу долей выручки от продажи: пример магазина велозапчастей, формулы Excel и проверка итогов. Различаем долю продаж, наценку и прибыль.
Ольга
Основатель малого бизнеса, партнёр
8 мин чтения
На листе потерялась половина выручки. В кассе её никто не терял. Я готовила к обсуждению ассортимента четыре строки продаж магазина велозапчастей, посчитала проценты и получила в сумме 50%. Поэтому вопрос, как составить таблицу долей выручки от продажи, для меня начинается с проверки общего итога. Цвета столбцов подождут.
Мне нужна была понятная картина за неделю: какие группы дали выручку и сколько приходится на каждую. Я собиралась показать лист механику и обсудить ассортимент. Числа выглядели аккуратно. Именно это и мешало заметить ошибку: ни одной красной ячейки, никаких предупреждений, только убедительные проценты.
Как составить таблицу долей выручки от продажи: исходные строки
Я начала с подписи: «Продажи четырёх групп велозапчастей за выбранную неделю, рубли». Взяла суммы продаж после скидок, с уже учтёнными возвратами этого отчёта. Это условие нашего рабочего расчёта. Не стоит смешивать в соседних строках выручку до скидки и сумму после неё: одинаковый денежный знак не делает показатели сопоставимыми.
Группы я распределила по товарным кодам. Каждая позиция попала только в одну группу. Если одни и те же тросы учесть отдельно и ещё раз внутри тормозных деталей, проценты будут считать повторную запись. Сам Excel не знает, как устроен мой ассортимент. Это я должна определить до формул.
Для повторения примера размести данные на пустом листе так. Заголовки занимают строку 1; последняя строка с товарной группой — строка 5. Пробелы в суммах ниже нужны для чтения, а в ячейки B2:B5 можно ввести числа 48000, 36000, 24000 и 12000.
Строка листа · A: товарная группа · B: выручка, ₽ · C: доля выручки
Пока смотри только на денежный столбец. Проценты справа — ориентир для проверки, их мы получим формулой. Сумма четырёх групп составляет 120 000 рублей. Доля цепей равна 48 000 / 120 000 = 0,4, или 40%. Каждую следующую группу делим на тот же общий итог.
Общий здесь означает итог именно выбранных групп. Если за их пределами остались другие продажи магазина, надпись «доля всей выручки магазина» будет неверной. Можно расширить набор или честно назвать выборку. У ста процентов тоже должен быть подписан состав.
Откуда взялись 50% и как закрепить общий итог
В моей исходной выгрузке строка «Итого» уже стояла под товарами. Я посчитала знаменатель по пяти денежным строкам, захватив её вместе с четырьмя группами. Получилось 240 000 рублей. Каждую настоящую выручку я поделила на удвоенную сумму и получила 20%, 15%, 10% и 5%.
Вот и вся пропажа. Строка «Итого» не продаёт пятую товарную группу. Я исправила диапазон и проверила 4 строки заново: 40 + 30 + 20 + 10 = 100. Для этого не понадобилось менять продажи.
На чистом листе порядок заполнения такой:
В A1 напиши «Группа», в B1 — «Выручка, ₽», в C1 — «Доля выручки». Заполни названия и суммы в строках 2–5.
В B6 введи `=СУММ(B2:B5)`. В английской версии имя функции — `SUM`. Ожидаемый результат равен 120000.
В C2 введи `=B2/$B$6`. Получится 0,4. Знаки `$` удерживают адрес общего итога при копировании.
Скопируй C2 в C3:C5. В C3 формула должна стать `=B3/$B$6`: меняется только выручка конкретной группы.
Выдели C2:C6 и на вкладке «Главная» выбери процентный формат. В C6 добавь `=СУММ(C2:C5)` и проверь 100%.
Я отдельно смотрю на последнюю формулу доли. Если в C5 осталось деление на B6, всё хорошо. Если там B9, ссылка уехала при копировании. Поэтому запись `=B2/B6` для первой строки здесь неудобна: вниз переместится и числитель, и знаменатель. Полная фиксация `$B$6` сохраняет нужный итог.
Ещё одна ловушка выглядит громче: вместо 40% получается 4000%. Обычно это значит, что ты уже умножил результат деления на 100, а затем применил процентный формат. Для нашего столбца достаточно `=B2/$B$6`. Excel покажет десятичную дробь 0,4 как 40%. Дополнительное умножение не нужно.
Если C6 показывает 100%, я всё равно возвращаюсь к исходным записям. Четыре неверные суммы тоже могут безупречно разделить собственный итог. Подробный разбор проверки исходных сведений есть в материале про анализ данных с нейросетью. Здесь я сверяю каждую группу с её продажами, а общий итог — с тем же набором записей за ту же неделю.
Что означают 40% цепей: выручка, наценка и прибыль
Когда лист наконец сошёлся, я показала его механику. «Значит, на цепях у нас самые выгодные продажи?» — спросил он. Я уже хотела согласиться. Цепи ведь наверху. Но в таблице нет ни закупочной стоимости, ни расходов магазина. Я подготовила ответ на другой вопрос: какую часть выбранной выручки дали цепи.
Сорок процентов отвечают на вопрос «от чего?», прежде чем на вопрос «много ли?». В нашей таблице основание — 120 000 рублей продаж четырёх групп. Для наценки основанием будет закупочная стоимость, и это уже другой расчёт.
Возьмём одну цепь: купили за 1000 рублей, продали за 1600. Разница составляет 600 рублей. Наценка к закупочной стоимости равна 600 / 1000 × 100% = 60%. Эти 60% не заменяют долю цепей в выручке, которая остаётся 40% по недельной таблице. У показателей разные числители и разные основания.
И 600 рублей ещё нельзя назвать чистой прибылью магазина от этой продажи. Мы не учли прочие расходы. Я записала вывод: «Цепи дали 40% выручки четырёх выбранных групп за неделю». Ни слова о превосходстве в прибыли. Так вывод стал соответствовать данным, которые у меня действительно были.
По этой таблице можно обсуждать вклад групп в сумму продаж. Нельзя только по её верхней строке решить, сколько закупать на следующую неделю или какая группа принесла больше прибыли. Для таких вопросов нужны дополнительные сведения. Я оставила рядом отдельную заметку о расходах, чтобы не выдать сегодняшнюю арифметику за готовое решение об ассортименте.
Как разобрать свою таблицу с LibraChat
Для текстовой проверки я бы дала чату не весь учёт магазина, а названия групп, суммы и описание того, что они включают. Имена покупателей и номера заказов здесь ничего не объясняют. Если планируешь повторять такой разбор регулярно, условия своего доступа можно посмотреть в тарифах LibraChat.
Вот предлагаемый запрос для этого упражнения:
Помоги разобрать таблицу долей выручки магазина велозапчастей за одну неделю. Группы: цепи — 48000 рублей, тормозные колодки — 36000, камеры — 24000, тросы и рубашки — 12000. Суммы после скидок, возвраты этого отчёта учтены. Рассчитай общий итог и долю каждой группы, затем предложи формулы для Excel: группы в A2:A5, выручка в B2:B5, итог в B6, доли в C2:C5. Не включай итог повторно в сумму групп. Объясни, какие выводы о структуре продаж допустимы, а для каких не хватает данных. Не подменяй долю выручки наценкой или прибылью. Если состав данных неясен, задай вопрос.
С этим заданием открой чат LibraChat и попробуй разобрать свой небольшой набор. Чат может объяснить формулы и представить таблицу в ответе. Полученные числа всё равно сверяй на листе: описание ошибки не гарантирует, что в ответе её не будет.
Перед обсуждением результата у меня остаётся короткий контроль:
Каждая продажа входит в одну группу, а период и способ учёта сумм совпадают.
В знаменателе стоят все выбранные группы один раз; готовый итог не прибавлен повторно.
Пустая исходная сумма выяснена, а не молча заменена нулём. При общем итоге ноль делить нельзя.
Проценты рассчитаны из исходных сумм. Три равные доли могут отображаться как 33,3% каждая. Сумма этих подписей равна 99,9%, хотя итог по неокруглённым значениям составляет 100%.
Заголовок листа говорит о выручке выбранного набора, а вывод не обещает показать прибыль.
Мои пропавшие 50% нашлись в диапазоне формулы. После исправления я принесла на обсуждение лист с понятными 100% и точной подписью. Мы могли говорить о структуре продаж, не споря о том, куда исчезли деньги. Прежде чем делать собственный вывод по самой большой доле, проверь две вещи: что входит в общий итог и на какой вопрос отвечает твоя таблица.
