Бесплатный курс Excel: формулы для денег, продаж и отчётов
Практический курс по формулам Excel: сумма, проценты, ЕСЛИ, СУММЕСЛИ, ВПР, ошибки, даты и простые отчёты на примерах бизнеса.
Коротко
- Это курс по формулам Excel: от простого сложения до поиска цены и отчёта по статьям.
- В каждом уроке есть пример формулы, русское и английское название функции и задание.
- После курса можно собрать таблицу продаж, расходов, платежей и прибыли без ручного пересчёта.
Бесплатный курс Excel должен учить не общим словам про таблицы, а конкретным формулам. Собственник или менеджер открывает файл и хочет быстро посчитать: сколько пришло денег, какие расходы съели прибыль, сколько клиент должен оплатить и где в таблице ошибка.
В этом курсе берём рабочие ситуации из бизнеса: продажи, закупки, зарплата, налоги, платежи поставщикам, скидки и долги клиентов. На каждой задаче показываем формулу, объясняем её простыми словами и даём упражнение.
В русской версии Excel функции называются по-русски: СУММ, ЕСЛИ, СУММЕСЛИ. В английской версии — SUM, IF, SUMIF. Ниже будут оба варианта, чтобы вы могли повторить урок в своей программе.
Урок 1. Как устроена формула
Любая формула в Excel начинается со знака равно. Если в ячейке написать 2+2, Excel увидит обычный текст или число. Если написать =2+2, он посчитает результат.
Формула может брать числа прямо из ячеек. Например, в B2 стоит выручка 120000, в C2 расход 45000. Формула =B2-C2 покажет прибыль 75000. Если поменять расход, результат обновится сам.
Главная привычка: не забивать итог руками. Если число должно зависеть от других ячеек, ставьте формулу. Так таблица продаж, расходов и платежей не развалится после первой правки.
- Пример: =B2-C2 считает разницу между выручкой и расходом.
- Знак плюс складывает: =B2+C2.
- Знак минус вычитает: =B2-C2.
- Звёздочка умножает: =B2*C2.
- Косая черта делит: =B2/C2.
- Задание: сделайте три колонки «Цена», «Количество», «Сумма» и посчитайте сумму продаж формулой =B2*C2.
Урок 2. СУММ: как сложить продажи и расходы
СУММ складывает диапазон ячеек. Диапазон — это несколько ячеек подряд, например B2:B20. В английской версии Excel эта функция называется SUM.
Если в B2:B20 лежат продажи за день, формула =СУММ(B2:B20) покажет общую выручку. В английской версии: =SUM(B2:B20).
Та же формула работает для расходов, оплат клиентов, зарплаты или закупок. Собственник сразу видит итог, а не складывает строки калькулятором.
- Русская формула: =СУММ(B2:B20).
- Английская формула: =SUM(B2:B20).
- Для итога по двум колонкам можно написать: =СУММ(B2:B20;C2:C20).
- Для быстрой вставки суммы используйте кнопку «Автосумма».
- Задание: внесите 10 продаж и 10 расходов, посчитайте общую выручку, общий расход и остаток денег.
Урок 3. Проценты, скидка и наценка
Проценты в Excel часто нужны для скидок, комиссии банка, налога, маржи и наценки. Если цена товара 5000 рублей, а скидка 10%, сумма скидки считается формулой =B2*10%.
Чтобы получить цену после скидки, используйте =B2*(1-C2), где B2 — цена, C2 — скидка. Если в C2 стоит 10%, результат будет 4500 рублей.
Для наценки формула похожая: =B2*(1+C2). Если закупочная цена 3000 рублей, а наценка 40%, итоговая цена будет 4200 рублей.
- Скидка в рублях: =B2*C2.
- Цена после скидки: =B2*(1-C2).
- Цена с наценкой: =B2*(1+C2).
- Доля расхода в выручке: =C2/B2, потом включите формат «Процент».
- Задание: посчитайте цену после скидки 5%, 10% и 15% для трёх товаров.
Урок 4. ЕСЛИ: как поставить условие
ЕСЛИ помогает Excel выбирать ответ по условию. В английской версии функция называется IF. Логика простая: если условие верное — показать одно, если нет — другое.
Например, клиент должен оплатить 120000 рублей. Если оплата просрочена, в таблице нужно увидеть «Напомнить». Формула может смотреть на дату оплаты и сама ставить статус.
Простой пример: =ЕСЛИ(B2>0;"Оплачено";"Ждём оплату"). Если в B2 есть сумма оплаты, Excel покажет «Оплачено». Если там ноль, покажет «Ждём оплату».
- Русская формула: =ЕСЛИ(B2>0;"Оплачено";"Ждём оплату").
- Английская формула: =IF(B2>0,"Paid","Waiting").
- Для проверки расходов: =ЕСЛИ(C2>100000;"Проверить";"Ок").
- Текст внутри формулы пишется в кавычках.
- Задание: сделайте колонку «Статус» и отметьте платежи больше 100000 рублей словом «Проверить».
Урок 5. СУММЕСЛИ: итог по одной статье
СУММЕСЛИ складывает только те строки, которые подходят под условие. В английской версии это SUMIF. Это одна из самых полезных формул для расходов.
Например, в колонке A стоят статьи расходов, а в колонке B суммы. Нужно узнать, сколько ушло на рекламу. Формула: =СУММЕСЛИ(A:A;"Реклама";B:B).
Так можно быстро собрать отчёт: зарплата, аренда, закупка, налоги, реклама. Не нужно фильтровать таблицу руками и копировать суммы.
- Русская формула: =СУММЕСЛИ(A:A;"Реклама";B:B).
- Английская формула: =SUMIF(A:A,"Ads",B:B).
- Первый диапазон — где искать статью.
- Условие — что именно искать.
- Последний диапазон — какие суммы складывать.
- Задание: посчитайте отдельно расходы на рекламу, аренду и зарплату.
Урок 6. СУММЕСЛИМН: итог по статье и месяцу
СУММЕСЛИМН нужна, когда условий несколько. В английской версии это SUMIFS. Например, нужно посчитать расходы на рекламу только за июль или продажи конкретного менеджера за неделю.
Представим таблицу: A — дата, B — статья, C — сумма. Чтобы посчитать рекламу за июль, можно поставить даты начала и конца месяца в E1 и F1, а статью в E2.
Формула будет такой: =СУММЕСЛИМН(C:C;B:B;E2;A:A;">="&E1;A:A;"<="&F1). Она складывает суммы из C, где статья равна E2, а дата попадает в нужный период.
- Русская формула: =СУММЕСЛИМН(C:C;B:B;E2;A:A;">="&E1;A:A;"<="&F1).
- Английская формула: =SUMIFS(C:C,B:B,E2,A:A,">="&E1,A:A,"<="&F1).
- Эта формула удобна для отчёта по месяцам.
- Её можно использовать для клиентов, менеджеров, проектов и статей расходов.
- Задание: посчитайте рекламу за один месяц и зарплату за тот же месяц.
Урок 7. ВПР и ПРОСМОТРX: как подтянуть цену или статью
ВПР ищет значение в справочнике и возвращает данные из соседней колонки. В английской версии это VLOOKUP. Например, в заказе написан артикул товара, а цену нужно подтянуть из прайса.
Если в A2 указан артикул, а на листе «Прайс» в колонках A:B лежат артикулы и цены, формула будет: =ВПР(A2;Прайс!A:B;2;ЛОЖЬ). Английский вариант: =VLOOKUP(A2,Price!A:B,2,FALSE).
В новых версиях Excel удобнее ПРОСМОТРX, английское название XLOOKUP. Он ищет аккуратнее и не ломается так легко, когда в справочник добавляют колонки.
- ВПР: =ВПР(A2;Прайс!A:B;2;ЛОЖЬ).
- VLOOKUP: =VLOOKUP(A2,Price!A:B,2,FALSE).
- ПРОСМОТРX: =ПРОСМОТРX(A2;Прайс!A:A;Прайс!B:B).
- XLOOKUP: =XLOOKUP(A2,Price!A:A,Price!B:B).
- Задание: сделайте маленький прайс из 5 товаров и подтяните цену в таблицу заказов по артикулу.
Урок 8. ЕСЛИОШИБКА: как убрать страшные ошибки
Когда Excel не может посчитать формулу, он показывает ошибку. Например, деление на ноль даёт #ДЕЛ/0!, а ВПР без найденного товара может дать #Н/Д. Для пользователя это выглядит пугающе и портит отчёт.
ЕСЛИОШИБКА позволяет заменить ошибку понятным текстом или пустой ячейкой. В английской версии функция называется IFERROR.
Например, если формула маржи =B2/C2 иногда делит на ноль, напишите =ЕСЛИОШИБКА(B2/C2;0). Тогда вместо ошибки будет 0.
- Русская формула: =ЕСЛИОШИБКА(B2/C2;0).
- Английская формула: =IFERROR(B2/C2,0).
- Для ВПР можно поставить текст: =ЕСЛИОШИБКА(ВПР(A2;Прайс!A:B;2;ЛОЖЬ);"Нет в прайсе").
- Не прячьте важные ошибки молча: если ошибка требует действия, лучше показать понятный текст.
- Задание: сделайте формулу поиска цены и выведите «Нет в прайсе», если артикул не найден.
Урок 9. Даты: просрочка, срок оплаты и ближайшие платежи
Даты в Excel можно считать как числа. Это удобно для платежей клиентов, сроков поставщикам и налогов. Если дата оплаты в B2, а сегодня уже позже этой даты, таблица может сама показать просрочку.
Функция СЕГОДНЯ показывает текущую дату. В английской версии это TODAY. Например: =ЕСЛИ(B2<СЕГОДНЯ();"Просрочено";"В срок").
Если нужно посчитать, сколько дней осталось до платежа, используйте =B2-СЕГОДНЯ(). Если результат отрицательный, платёж уже просрочен.
- Сегодняшняя дата: =СЕГОДНЯ() или =TODAY().
- Дней до оплаты: =B2-СЕГОДНЯ().
- Статус платежа: =ЕСЛИ(B2<СЕГОДНЯ();"Просрочено";"В срок").
- Для просроченных клиентов можно добавить условное выделение красным.
- Задание: сделайте список из 10 счетов клиентов и отметьте, какие оплаты уже просрочены.
Урок 10. Соберите маленький отчёт из формул
Теперь соберите один лист «Отчёт». На нём должны быть выручка, расходы, прибыль, расходы по статьям, просроченные оплаты клиентов и ближайшие платежи. Это уже не просто таблица, а рабочий инструмент.
Например, выручку посчитайте через СУММ, расходы на рекламу через СУММЕСЛИ, расходы за месяц через СУММЕСЛИМН, статус оплат через ЕСЛИ, цены товаров через ВПР или ПРОСМОТРX.
Раз в неделю открывайте отчёт и проверяйте три вещи: денег хватает, расходы не убежали, клиенты не просрочили оплату. Если формулы настроены правильно, на это уйдёт несколько минут.
- Выручка: =СУММ(Продажи!D:D).
- Расходы на рекламу: =СУММЕСЛИ(Операции!B:B;"Реклама";Операции!C:C).
- Прибыль: выручка минус расходы.
- Просрочка клиента: =ЕСЛИ(B2<СЕГОДНЯ();"Просрочено";"В срок").
- Цена товара: =ВПР(A2;Прайс!A:B;2;ЛОЖЬ).
- Задание: соберите отчёт на одном листе и проверьте его на 20 строках продаж и расходов.
Итог
Теперь это бесплатный курс Excel именно по формулам: вы складываете суммы, считаете проценты, ставите условия, собираете итоги по статьям, ищете цены в справочнике и проверяете просрочки.
Этого набора хватает, чтобы собрать первый нормальный файл для продаж, расходов, платежей и прибыли. Дальше можно добавлять сводные таблицы, диаграммы и автоматизацию, но базу лучше начинать с этих формул.