Формулы и первые KPI: выручка, средний чек, себестоимость и прибыль
📈Формулы и первые KPI: выручка, средний чек, себестоимость и прибыль
Связь с уроком 1: используем тот же датасет из 60 операций интернет-магазина за сентябрь и октябрь 2026 года.
ЕСЛИ, рассчитаете валовую прибыль 68 590 ₽, средний чек 2 275,51 ₽ и валовую маржу 61,52% на листе отчёта.Курс: Excel для аналитики: от таблицы до отчёта
Формат: бесплатный мини-курс CopyPastWorld
Модуль 1. Урок 2 из 5
Тема: Формулы и первые KPI: выручка, средний чек, себестоимость и прибыль
Уровень: начинающий · Ориентировочное время: 35–50 минут с практикой
Связь с предыдущим уроком: используем те же 60 операций за сентябрь–октябрь 2026 года.
На прошлом занятии вы импортировали CSV, проверили структуру таблицы и научились фильтровать записи. Теперь мы превратим исходную выгрузку в рабочий расчётный инструмент. После этого урока вы сможете самостоятельно рассчитать стоимость заказа, учебную выручку по выкупленным операциям, себестоимость реализованных товаров, валовую прибыль, средний чек и валовую маржу. Всё это сделаем в Excel — с формулами, которые можно протянуть на десятки строк и затем проверить контрольными значениями.
В качестве примера используем небольшой интернет-магазин одежды на Ozon и Wildberries. В выгрузке 60 операций; часть из них выкуплена, часть отменена, часть помечена как возврат. Если просто перемножить количество на цену во всех строках и сложить результаты, получится не выручка от состоявшихся продаж, а сумма потенциальных стоимостей всех операций. Поэтому урок начинается не с кнопок и формул, а с методики: какие строки включаются в расчёт и почему.
Что мы будем считать и какие данные уже есть
Откройте файл sales_start.xlsx из первого урока или новый sales_formulas_practice.xlsx. На листе «Продажи» находятся данные в столбцах A–K; заголовки в строке 1, операции — в строках 2–61. Для вычислений особенно важны четыре столбца: H — «Количество», I — «Цена за единицу, ₽», J — «Себестоимость за единицу, ₽», K — «Статус». Остальные поля — дата, площадка, категория, артикул и номер заказа — пригодятся для группировок на следующем занятии.
Учебные файлы для выполнения урока 2
Для работы во втором уроке используйте обновлённую книгу sales_formulas_practice.xlsx с подготовленными расчётными столбцами и листом «Отчёт». Также доступен исходный CSV-файл для повторения импорта.
sales_formulas_practice.xlsx
Содержит три листа: «Начните здесь», «Продажи» (с новыми столбцами L–Q) и динамический лист «Отчёт». В строках 2–3 уже введены образцы формул — вам предстоит протянуть их на все 60 строк и получить полные KPI.
sales_training.csv
Тот же текстовый датасет из 60 операций (UTF-8-BOM, разделитель точка с запятой). Пригодится, если вы хотите повторить построение таблицы с нуля или сравнить чистый источник данных с расчётной книгой.
Запись со статусом «Выкуплен» будем считать состоявшейся продажей. Записи «Отменён» и «Возврат» в данном учебном наборе не признаём отдельными продажами и присваиваем им нулевую учебную выручку. Это упрощённая модель, а не универсальное правило бухгалтерского учёта возвратов: в реальном бизнесе возврат может относиться к ранее учтённой продаже и уменьшать выручку через отдельную корректировку. В нашем наборе для возвратов нет ссылок на исходные продажи, дат первоначального признания и сумм возмещений. Поэтому нельзя автоматически вычитать каждую строку «Возврат» из суммы «Выкуплен» — можно дважды учесть эффект или привязать корректировку не к тому периоду.
Мы также пока не учитываем комиссии маркетплейсов, рекламу, логистику, упаковку, налоги и другие расходы, которых в выгрузке нет. Следовательно, полученный показатель называется валовой прибылью в рамках принятой учебной методики, а не чистой прибылью магазина.
Как устроена формула Excel
Формула начинается со знака =. Например, =2+3 вернёт 5, а =H2*I2 умножит количество из H2 на цену из I2. Благодаря ссылкам на ячейки Excel пересчитывает результат, если исходные значения изменяются. Для аналитика это принципиально: одна проверенная формула часто заменяет десятки ручных вычислений.
Основные арифметические операторы: + — сложение, - — вычитание, * — умножение, / — деление, ^ — возведение в степень. Приоритет операций такой же, как в математике: сначала скобки, затем степени, умножение и деление, потом сложение и вычитание. Поэтому =(I2-J2)*H2 означает «разница между ценой и себестоимостью одной единицы, умноженная на количество». Без скобок другая последовательность может дать иной результат.
Вы можете ввести формулу непосредственно в ячейку либо в строку формул. После нажатия Enter ячейка показывает вычисленное значение, а при выделении ячейки формула отображается в строке формул. Именно поэтому проверка только видимого числа не всегда достаточна: значение может быть введено вручную и не обновляться вместе с данными.
Попробуйте: в свободной ячейке введите =1200*2, затем =H4*I4. Для записи CPW-09-003 оба варианта должны дать 2 400 ₽. После проверки удалите пробные вычисления, чтобы не смешивать их с рабочей таблицей.
Относительные и абсолютные ссылки
По умолчанию Excel использует относительные ссылки. Если вы ввели в L2 формулу =H2*I2 и протянули её на одну строку вниз, Excel подставит =H3*I3. Это удобно: нам нужна одна логика для всех операций, но разные исходные ячейки. Если скопировать формулу ещё ниже, она станет =H4*I4 и так далее.
Абсолютная ссылка записывается со знаком $, например $B$2. При копировании ссылка остаётся привязанной к одной ячейке. Допустим, на листе «Отчёт» в B2 хранится налоговая ставка или коэффициент (пока не будем применять его к учебной прибыли). Тогда =L2*$B$2 использует L2, L3, L4 при протягивании, но постоянно берёт коэффициент из B2. Существуют и смешанные ссылки $B2 и B$2, фиксирующие только столбец или строку.
Для наших основных расчётов столбцов L–Q относительных ссылок достаточно. Но уже сейчас важно научиться отличать H2 от $H$2: случайно зафиксированная ссылка может заставить все 60 строк использовать количество первой операции, и итог окажется неверным.
Создаём первый вычисляемый столбец: стоимость операции
Добавьте справа от исходного набора столбец L — «Стоимость операции, ₽». Это произведение количества на цену единицы товара, без проверки статуса:
=H2*I2
Для строки 2 (один худи по 2 800 ₽) результат равен 2 800 ₽. Для строки 4 (две футболки по 1 200 ₽) результат равен 2 400 ₽. Важно: стоимость операции не равна автоматически признанной выручке. Даже если заказ отменён, значения «Количество» и «Цена» в записи могут оставаться заполненными.
В файле для практики первые две строки расчётных столбцов уже содержат пример формул. Протяните формулу L2 до L61, либо используйте маркер автозаполнения. Если на листе уже создана «Таблица Excel», формула может автоматически заполнить вычисляемый столбец. Проверьте L4 и L61 — они должны ссылаться на собственные строки, а не на H2 и I2.
Сохраните рабочую книгу. Полезная привычка — выполнять одно логическое изменение за раз, проверять 2–3 контрольные строки и только затем переходить к следующему столбцу.
Себестоимость операции: не забываем количество
В столбце M — «Себестоимость операции, ₽» рассчитайте, сколько стоят товары из данной операции по указанной себестоимости за единицу:
=H2*J2
Для записи CPW-09-003 себестоимость двух футболок составляет 2 × 420 = 840 ₽. Если ошибочно взять только J4, вы занизите себестоимость операции вдвое. Эта ошибка особенно опасна в наборах, где большинство строк содержит единицу товара: формула будет выглядеть правильной, пока не встретится количество 2 или 3.
Протяните формулу M2:M61 и проверьте одну строку с количеством больше единицы. Сумма M2:M61 пока не является себестоимостью состоявшихся продаж, потому что включает значения по отменённым и возвращённым операциям. Мы специально разделяем «первичную стоимость записи» и «участвующую в учебной отчётности себестоимость».
Логическая функция ЕСЛИ: отделяем продажи от отмен
Чтобы включать в выручку только выкупленные операции, используем функцию ЕСЛИ (IF в английском интерфейсе Excel). Её логика проста: если условие выполняется — вернуть одно значение, иначе другое.
Синтаксис русской локализации:
=ЕСЛИ(условие;значение_если_истина;значение_если_ложь)
В английском Excel с разделителем аргументов «запятая» эквивалент записывается как =IF(condition,value_if_true,value_if_false). Названия функций и разделители зависят от версии и региональных настроек. Формулы внутри .xlsx хранятся в совместимом формате и при открытии могут отображаться на языке вашей установки Excel.
Создайте столбец N — «Выручка, ₽». Для русскоязычного Excel:
=ЕСЛИ(K2="Выкуплен";L2;0)
Для англоязычного Excel:
=IF(K2="Выкуплен",L2,0)
Вычисление читается так: «Если статус K2 равен тексту “Выкуплен”, взять стоимость из L2, иначе поставить ноль». Для CPW-09-007 со статусом «Возврат» учебная выручка по этой строке должна быть 0 ₽, хотя исходная стоимость операции равна 2 800 ₽. Ноль здесь — часть конкретной учебной методики; он не означает, что в реальной системе возврат не имеет финансовых последствий.
Проверьте текст статуса. Если кто-то записал Выкуплен с лишним пробелом или изменил значение на Оплачен, формула вернёт ноль. Это не ошибка Excel, а сигнал к проверке качества данных и словаря статусов. Не заменяйте слепо любые неизвестные статусы на «Выкуплен»: тогда аналитика станет недостоверной.
Рассчитываем себестоимость продаж и валовую прибыль
В столбце O — «Себестоимость продаж, ₽» используйте то же правило статуса:
=ЕСЛИ(K2="Выкуплен";M2;0)
Английский вариант: =IF(K2="Выкуплен",M2,0). Столбец O показывает ту часть себестоимости, которая относится к выкупленным операциям в рамках нашей модели. Теперь создайте P — «Валовая прибыль, ₽» и вычислите:
=N2-O2
Валовая прибыль конкретной выкупленной операции равна её учебной выручке за вычетом соответствующей себестоимости. Для отменённой или возвращённой записи оба показателя равны нулю, значит и P равен нулю. Для CPW-09-003: выручка 2 400 ₽, себестоимость продаж 840 ₽, валовая прибыль 1 560 ₽.
Не называйте P «чистой прибылью». Чистая прибыль требует учитывать как минимум все соответствующие расходы и налоги; нужных полей в датасете нет. Если в учебном отчёте написано «маржинальная прибыль», нужно заранее согласовать, какие расходы считаются переменными. В этом курсе мы используем точный термин «валовая прибыль» — это понятнее для новичка и корректнее при имеющихся данных.
Дополнительный показатель: количество выкупленных единиц
Номера заказов в нашем наборе уникальны, но количество товара в строке может быть больше единицы. Поэтому мы заранее создадим столбец Q — «Выкуплено, шт.». Его формула:
=ЕСЛИ(K2="Выкуплен";H2;0)
Для английской локализации: =IF(K2="Выкуплен",H2,0). Этот столбец позволит различать 49 выкупленных операций и 55 выкупленных единиц товара. Если вы случайно назовёте число строк «количеством товаров», итоговый отчёт будет содержать ошибку, даже если все формулы Excel работают правильно.
Заполните столбцы L–Q до строки 61. Если всё сделано верно, каждая из 60 строк должна иметь значения во всех шести вычисляемых столбцах. Проверьте, что скрытые фильтрами строки тоже заполнены: если протягивать формулы при активном фильтре, результат может зависеть от способа копирования. Перед массовым заполнением удобнее снять фильтры и проверить границы диапазона.
Интерактивный тренажёр: «Построй расчёт операции»
Выберите учебную запись и посмотрите, как формулы Excel столбцов L–Q работают в разных ситуациях
=H2*I2=H2*J2=ЕСЛИ(K2="Выкуплен";L2;0)=ЕСЛИ(K2="Выкуплен";M2;0)=N2-O2=ЕСЛИ(K2="Выкуплен";H2;0)CPW-09-007) первичная стоимость операции в столбце L сохраняется (2 800 ₽), но признанная выручка N и себестоимость продаж O равны нулю. Это позволяет сохранить факт попытки заказа, не искажая объём реальных продаж магазина.Функция СУММ: считаем показатели всего магазина
Откройте лист «Отчёт» в шаблоне или создайте новый лист самостоятельно. Формулы ссылаются на лист «Продажи»; если в имени листа есть пробелы, заключайте его в одинарные кавычки. Русский вариант суммы учебной выручки:
=СУММ(Продажи!N2:N61)
В английской локализации: =SUM(Продажи!N2:N61). Аналогично вычислите себестоимость продаж =СУММ(Продажи!O2:O61), валовую прибыль =СУММ(Продажи!P2:P61) и число выкупленных единиц =СУММ(Продажи!Q2:Q61).
Здесь важно суммировать правильный столбец. Если взять L вместо N, в общую сумму войдут значения отменённых и возвращённых записей. Если сложить цену из I без учёта H, операции с несколькими единицами будут посчитаны неправильно. Обе ошибки могут давать привлекательные, но недостоверные итоги.
Эти числа рассчитаны по приложенному синтетическому набору данных. Проверять ответ нужно не по одному совпавшему числу, а по последовательности: количество выкупленных операций, количество единиц, сумма выручки, себестоимость и их разница.
СЧЁТЕСЛИ: считаем только нужные записи
Для расчёта среднего чека сначала нужно определить количество выкупленных заказов. В нашем учебном файле каждая операция имеет уникальный номер заказа, поэтому количество строк со статусом «Выкуплен» совпадает с количеством выкупленных заказов. В реальной выгрузке один заказ может занимать несколько строк — тогда число строк использовать нельзя, понадобятся уникальные идентификаторы заказов.
В Excel используем функцию СЧЁТЕСЛИ (COUNTIF):
=СЧЁТЕСЛИ(Продажи!K2:K61;"Выкуплен")
Английский вариант:
=COUNTIF(Продажи!K2:K61,"Выкуплен")
Результат — 49. Для проверки можно сравнить его с фильтром по статусу и с количеством записей из первого урока. В следующем уроке научимся сочетать несколько критериев и отдельно анализировать маркетплейсы, категории и месяцы через СУММЕСЛИМН / SUMIFS и СЧЁТЕСЛИМН / COUNTIFS.
Средний чек и средняя цена: это разные показатели
Средний чек отвечает на вопрос: сколько выручки приходится на один выкупленный заказ. В рамках нашего набора его формула:
[ \text{Средний чек}=\frac{\text{Выручка по выкупленным операциям}}{\text{Число выкупленных заказов}} ]
Поскольку выручка равна 111 500 ₽, а выкупленных заказов 49, средний чек составит примерно 2 275,51 ₽.
В Excel, если на листе «Отчёт» B5 хранит выручку, а B3 — число выкупленных заказов, можно написать =ЕСЛИ(B3=0;0;B5/B3) или в англоязычной версии =IF(B3=0,0,B5/B3). Проверка на ноль нужна, чтобы не получить #ДЕЛ/0!, когда нет ни одного выкупленного заказа. В полноценном управленческом отчёте при нулевой базе вместо нуля лучше показывать «не рассчитывается», но для простого учебного шаблона можно использовать ноль как техническое значение при обязательном пояснении.
Средняя цена реализованной единицы — другой показатель:
[ \text{Средняя цена единицы}=\frac{\text{Выручка}}{\text{Выкуплено единиц}} ]
Для наших данных это примерно 2 027,27 ₽. Почему значения отличаются? Некоторые заказы содержат две единицы товара. Средний чек относится к заказу, средняя цена — к одной реализованной единице. Разница в определении важнее, чем сами числа: нельзя сравнивать два показателя с разными знаменателями как один и тот же KPI.
Валовая маржа: прибыль в процентах от выручки
Абсолютная валовая прибыль показывает сумму в рублях. Валовая маржа позволяет оценить её долю в выручке:
[ \text{Валовая маржа}=\frac{\text{Валовая прибыль}}{\text{Выручка}}\times100% ]
По нашей выгрузке: 68 590 / 111 500 ≈ 0,6152, то есть 61,52%. Для отображения результата в Excel удобнее записать простую дробь =B7/B5 и задать ячейке процентный формат с двумя знаками после запятой. Не умножайте дополнительно на 100, если ячейка уже отформатирована как процент: Excel сам показывает дробь 0,6152 как 61,52%.
Такая маржа выглядит высокой, потому что в учебной модели вычтена только себестоимость товаров. Из неё ещё не оплачены комиссии, доставка, реклама, аренда, зарплаты, налоги и прочие расходы. Поэтому недопустимо утверждать: «Магазин заработал 61,52% чистой прибыли». Правильная формулировка: «Валовая маржа по выбранной учебной методике составила около 61,52%; другие расходы не включены».
Как проверить расчёты, чтобы не отправить руководителю ошибку
Первое правило — проверяйте исходную модель. Убедитесь, что цена и себестоимость относятся к единице товара, а статус «Выкуплен» обозначает состоявшуюся продажу. Второе — проверяйте заполнение диапазона: формулы есть во всех строках 2–61; никакие записи не пропущены после фильтра. Третье — используйте контрольное равенство: валовая прибыль должна быть точно равна выручке за вычетом себестоимости продаж.
Четвёртое — проверяйте разумность результата. Выручка по выкупленным операциям не должна быть больше суммы первичной стоимости всех операций при неотрицательных ценах и количестве. У нас сумма L2:L61 по всем статусам составляет 138 450 ₽, а выручка по выкупленным — 111 500 ₽. Разница 26 950 ₽ приходится на стоимость записей, которые не признаются продажами в учебной модели. Это не «сумма возвратов» в бухгалтерском смысле — туда входят также отмены, и отдельный возврат может относиться к другой первоначальной продаже.
Пятое — проверяйте необычные значения и единицы измерения. Если цена в файле записана в копейках, а формула считает её рублями, ошибка будет кратной ста. Если код артикула превратился в число, вычисления выручки могут сохраниться, но группировка по товару сломается. Хороший аналитик проверяет не только вычисления, но и то, что они означают.
Быстрый контроль
Если одно из условий не выполняется, не пытайтесь подогнать финальную цифру. Найдите неверную строку или ссылку. Например, формула =ЕСЛИ(K2="Выкуплен";I2;0) ошибочно игнорирует количество H2. А формула =H2*J2 в столбце N берёт себестоимость вместо цены. Правильный итог получается только из правильных промежуточных шагов.
Практическая работа: создаём лист с первыми KPI
Этап А. Подготовка. Откройте свою рабочую книгу из первого урока или sales_formulas_practice.xlsx. Убедитесь, что исходный набор содержит 60 строк и 11 исходных столбцов. Сохраните новую копию как Excel_для_аналитики_Урок_2.xlsx. Не удаляйте исходные записи и не меняйте статусы ради совпадения с контрольными значениями.
Этап Б. Формулы на уровне операции. Создайте столбцы L «Стоимость операции, ₽», M «Себестоимость операции, ₽», N «Выручка, ₽», O «Себестоимость продаж, ₽», P «Валовая прибыль, ₽», Q «Выкуплено, шт.». Напишите формулы для второй строки и протяните их до строки 61. Проверьте минимум одну запись с количеством 2, одну отменённую и одну со статусом «Возврат». Объясните, почему L может быть больше нуля при N=0.
Этап В. Лист «Отчёт». Рассчитайте общее число выкупленных операций, число выкупленных единиц, выручку, себестоимость реализованных товаров, валовую прибыль, средний чек, среднюю цену единицы и валовую маржу. Для большинства показателей достаточно функций СУММ, СЧЁТЕСЛИ и обычного деления. Оформите рублёвые значения с разделителями тысяч, маржу — в процентах, количество — целыми числами.
Этап Г. Краткий вывод. Напишите для условного руководителя 5–7 предложений. Укажите период и размер выборки; объясните, почему 60 операций не равны 60 продажам; сообщите выручку, валовую прибыль и средний чек; перечислите отсутствующие расходы; предложите следующий шаг: анализ по площадкам, категориям и месяцам. Не утверждайте, что одна площадка выгоднее другой, пока не рассчитали показатели по каждой площадке.
Этап Д. Проверка на устойчивость. Сохраните копию и временно измените в одной выкупленной операции количество с 1 на 2. Убедитесь, что все зависимые ячейки и итоги пересчитались. Затем отмените изменение. Если отчёт остался прежним, проверьте, не вписано ли итоговое число вручную вместо формулы.
Пять задач для проверки по данным
- Сколько операций со статусом «Выкуплен» содержится в исходном наборе?
- Сколько единиц товара содержится в выкупленных операциях?
- Какова общая выручка по выкупленным операциям за два месяца?
- Какова общая валовая прибыль по выбранной методике?
- Каков средний чек по выкупленным заказам? Округлите до двух знаков.
Контрольные показатели отчёта (5 задач)
Проверьте результаты расчёта формул в своей рабочей книге Excel
111500, 111 500 ₽, 2275,51В интерфейсе CopyPastWorld ученик должен ввести число, а не выбрать случайный вариант. При проверке допускайте пробелы-разделители тысяч и десятичную запятую; для среднего чека — отклонение ±0,01 ₽ из-за округления. Контрольные ответы храните в служебной конфигурации, а не в видимой части страницы до проверки.
Частые ошибки начинающего аналитика
Ошибка 1: суммировать все строки без статуса. Показатель L — стоимость всех зарегистрированных операций, N — учебная выручка от выкупленных. Это разные величины. Ошибка 2: забыть умножить на количество. В строках с двумя единицами товара формула по цене одной единицы занизит выручку и себестоимость. Ошибка 3: путать валовую и чистую прибыль. Без маркетинговых, логистических, административных и других расходов чистую прибыль рассчитать нельзя.
Ошибка 4: делить выручку на количество всех 60 записей. Средний чек рассчитывается по состоявшимся заказам. В нашей модели это 49 выкупленных операций, потому что номера заказов уникальны. Ошибка 5: считать среднюю цену и средний чек одним показателем. Они используют разные знаменатели. Ошибка 6: использовать вручную введённые итоги. Когда источник данных обновится, такой «отчёт» останется со старыми значениями.
Ошибка 7: ошибиться с форматом процентов. Если формула возвращает 0,6152, формат «Процентный» покажет 61,52%. Если умножить значение на 100 и затем снова применить процентный формат, получится 6152% — это ошибка представления, а не бизнеса. Ошибка 8: использовать неизвестные статусы как продажи. Если встречается нестандартный статус, его значение нужно уточнить, а не молча присвоить категории «Выкуплен».
Итоги и следующий шаг
Сегодня вы освоили главное преимущество Excel: связали исходные данные с автоматическими вычислениями и получили воспроизводимый финансовый мини-отчёт. Теперь вы умеете рассчитывать суммы по строкам, использовать относительные ссылки, отбирать выкупленные операции через ЕСЛИ, получать итоги с помощью СУММ, считать события через СЧЁТЕСЛИ и корректно различать выручку, себестоимость, валовую прибыль, средний чек и среднюю цену единицы.
Главное — не сами функции, а способность объяснить, какие данные участвуют в расчёте и чего показатель не учитывает. Цифра 68 590 ₽ полезна лишь тогда, когда вы можете назвать её валовой прибылью по выкупленным операциям за два месяца и объяснить, почему она не равна чистой прибыли.
На следующем уроке научимся отвечать на более точные вопросы: какая площадка даёт больше выручки, какая категория формирует основную прибыль и как сравнить периоды без ручной фильтрации. Для этого изучим СУММЕСЛИМН (SUMIFS), СЧЁТЕСЛИМН (COUNTIFS) и поиск по справочнику. Мы будем работать с тем же набором данных, поэтому ваши расчёты из текущего урока станут основой следующего занятия.
Вы завершили 2-й из 5 уроков бесплатного мини-курса. Впереди ещё 3 урока.
Что произойдёт с формулой =H2*I2 при копировании из L2 в L3?
Для чего мы используем ЕСЛИ(K2="Выкуплен";L2;0)?
Что нужно проверить перед отправкой отчёта? (выберите все верные варианты)
Почему средний чек и средняя цена единицы товара могут различаться?
Можно ли по имеющимся полям точно рассчитать чистую прибыль магазина?
Ваш прогресс в уроке
Выполняйте задания, чтобы заработать все баллы
