Бесплатный курс Excel: формулы для денег, продаж и отчётов

Вот продажи «Чайника» за июль — восемь позиций магазина. Скопируйте эту таблицу к себе в Excel: она станет тренажёром, на котором вы повторите каждую формулу курса и получите те же цифры, что и в примерах.

Столбцы простые: закупочная цена, цена продажи, сколько продали за месяц и выручка (цена × количество). Расходы лежат на втором листе, там 10 строк, и он понадобится в уроке 5. Всего 18 строк. Этого хватает, чтобы отработать весь курс.

Любая формула начинается со знака равно: напишете в ячейке 2+2 — Excel покажет текст, напишете =2+2 — посчитает. Функция СУММ (по-английски SUM) складывает целый диапазон ячеек сразу, без плюсиков между каждой.

В тренажёре восемь значений выручки лежат в столбце E, строки со 2-й по 9-ю. Формула =СУММ(E2:E9) даёт 252 400 ₽ — продажи «Чайника» за июль. Те же 10 строк расходов (их распишем в уроке 5) складываются в 239 000 ₽. Кнопка «Автосумма» на вкладке «Главная» вставляет СУММ сама: встаньте под столбцом и нажмите её.

Задание: выделите столбец «Выручка» и получите 252 400 ₽ автосуммой; затем сложите столбец «Продано» — должно выйти 485 штук.

Прибыль — это выручка минус расходы, и в Excel это обычное вычитание через знак минус. Если итог продаж стоит в одной ячейке, а итог расходов в другой, формула =B1-B2 покажет разницу и сама пересчитает её, стоит поменять любую цифру.

У «Чайника»: 252 400 − 239 000 = 13 400 ₽. Это то, что осталось после всех трат за месяц. Прибыль 13 400 ₽ на выручку 252 400 ₽ — это 5,3%, нормальный уровень для розницы (обычно 2-7%), но запас тонкий: один слабый месяц, и уйдёт в ноль.

Задание: поставьте под таблицей две ячейки — «Выручка» и «Расходы» — и третьей формулой посчитайте прибыль. Увеличьте любой расход на 20 000 и посмотрите, как прибыль сама упадёт до −6 600.

Проценты в Excel — это скидки, комиссия банка, наценка и маржа. Наценка и маржа считаются от разных чисел, и их часто путают. Наценка =(Цена−Закуп)/Закуп — насколько подняли цену над закупкой. Маржа =(Цена−Закуп)/Цена — какую долю цены вы оставляете себе. По-английски формулы те же, только разделитель — запятая.

Ассам: закуп 200, цена 350. Наценка (350−200)/200 = 75%, а маржа (350−200)/350 = 42,9% — числа разные, хотя товар один. Поставьте ячейке формат «Процент», иначе Excel покажет 0,429. В тренажёре сразу видно: травяной сбор держит маржу 60%, а подарочный набор — всего 13,3%.

Задание: добавьте столбец «Маржа» с формулой =(C2−B2)/C2 для всех восьми товаров; средняя по столбцу выйдет около 38,8%.

ЕСЛИ (по-английски IF) выбирает ответ по условию: если условие верное — одно, если нет — другое. Текст внутри пишут в кавычках. Для «Чайника» удобно, чтобы файл сам помечал позиции, которые надо проверить по цене.

Формула =ЕСЛИ(F2<25%;"Проверить";"ОК") смотрит на маржу в столбце F: где ниже 25% — ставит «Проверить», где выше — «ОК». Из восьми товаров загорается один — подарочный набор с маржой 13,3%. Значит, набор либо переоценить, либо пересобрать состав: продаётся много, а зарабатывает мало.

Задание: сделайте столбец «Статус» с этой формулой и убедитесь, что «Проверить» стоит только у набора.

СУММЕСЛИ (по-английски SUMIF) складывает только строки, подходящие под условие, — незаменима для расходов по статьям. В тренажёре есть второй лист: 10 строк расходов «Чайника» за июль, у каждой дата, статья и сумма. Скопируйте и его — дальше работаем с ним.

Формула =СУММЕСЛИ(B:B;"Закупка товара";C:C) берёт столбец статей B, ищет «Закупка товара» и складывает суммы из C. Две строки закупки (70 000 и 46 000) дают 116 000 ₽. Так же «Реклама» из двух строк — 15 000 ₽. Фильтровать вручную не нужно: поменяли слово в условии — получили другой итог.

Задание: посчитайте СУММЕСЛИ по статьям «Аренда», «Зарплата» и «Реклама» — выйдет 45 000, 40 000 и 15 000 ₽.

ВПР (по-английски VLOOKUP) ищет значение в справочнике и подтягивает данные из соседнего столбца — например, цену по названию товара. Сделайте лист «Прайс»: в столбце A названия, в B цены. Тогда в накладной достаточно выбрать товар, а цена подставится сама.

Формула =ВПР("Улун";Прайс!A:B;2;ЛОЖЬ) находит «Улун» в прайсе и возвращает цену из второго столбца — 480 ₽. В новых версиях удобнее ПРОСМОТРX (по-английски XLOOKUP): =ПРОСМОТРX("Улун";Прайс!A:A;Прайс!B:B) — он не ломается, когда в прайс добавляют столбцы.

Задание: соберите прайс из восьми товаров «Чайника» и подтяните цену набора — должно вернуться 1 500 ₽.

СРЗНАЧ (по-английски AVERAGE) считает среднее по диапазону. Среднее полезно как линейка: что выше — тянет вверх, что ниже — отстаёт.

=СРЗНАЧ(E2:E9) по выручке восьми позиций даёт 31 550 ₽ — столько в среднем приносит одна позиция за месяц. Набор (45 000) и Ассам (42 000) выше среднего, кружка (22 000) ниже. Средняя маржа по товарам — 38,8%, и на этом фоне 13,3% у набора видно сразу.

Задание: посчитайте среднюю цену продажи по столбцу «Цена» — выйдет 675 ₽.

Условное форматирование (по-английски Conditional Formatting) — это правило подсветки: клетка сама меняет цвет, когда цифра пересекает порог. Находится на вкладке «Главная», рядом с обычными форматами. Проблему видно по цвету, читать все строки не нужно.

Поставьте правило «залить красным, если меньше 25%» на столбец «Маржа» — загорится одна ячейка, набор. Тот же приём ловит кассовый минус (остаток вечером ниже нуля) и просрочку клиентов (дата оплаты меньше сегодняшней): столбец с датами сам подсветит, кто должен и тянет.

Задание: подсветите красным все позиции с маржой ниже 25% и убедитесь, что горит только набор.

Сводная таблица (по-английски PivotTable) собирает итоги по группам одним движением: «Вставка» → «Сводная таблица», в строки — «Статья», в значения — «Сумма». Excel сам соберёт восемь статей из десяти строк и посчитает каждую.

Для «Чайника» сводная показывает всю структуру расходов сразу: закупка товара — 116 000 ₽ (почти половина всех трат), аренда — 45 000, зарплата — 40 000. Итог — те же 239 000 ₽, что мы вычитали в уроке 2, но теперь видно, из чего он складывается.

Задание: постройте сводную по расходам и проверьте, что «Итого» равно 239 000 ₽.

Доля позиции — это её выручка, делённая на общий итог, в процентах. Чтобы формулу можно было протянуть вниз, итог закрепляют знаком доллара: =E2/$E$10. Доллары говорят Excel «эту ячейку не сдвигай», пока номер строки меняется.

Для «Чайника» картина такая: набор — 45 000 из 252 400, то есть 17,8% всей выручки, следом Ассам с 16,6%. Неприятно другое: самый крупный по обороту товар даёт худшую маржу, 13,3%. Доля и маржа стоят рядом, и сразу ясно, за какую позицию браться первой.

Задание: добавьте столбец «Доля» и проверьте, что сумма всех долей равна 100%.

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

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