30 задач в Excel: от рабочей таблицы до понятного отчёта

Марк Системов

30 задач в Excel: от рабочей таблицы до понятного отчёта

Как работать с книгой

Перед вами 30 задач из пяти обычных рабочих ситуаций: заказы, склад, бюджет, проекты и обращения в поддержку. Каждая задача начинается с результата, который нужно получить. Сначала попробуйте решить её сами, затем сравните действия и контрольное значение с разбором. Учебные данные вымышлены. Все таблицы напечатаны в книге, поэтому для выполнения задач достаточно набрать их в новом файле Excel.

Примеры рассчитаны на настольный Excel 2021, Excel 2024 и Microsoft 365 с русскими названиями функций. В другом языке интерфейса названия функций могут отличаться. Разделитель аргументов в русской локали обычно ;. Если ваша установка предлагает запятую, используйте её. Для задач с датами вводите именно даты, а не строки текста. Перед изменением исходных значений сохраняйте рабочую копию файла.

Здесь важен не набор команд на память, а проверяемый результат. Если получилась другая цифра, проверьте типы данных, диапазоны, условия и включённые фильтры. Не подменяйте ошибку нулём без выяснения причины.

Часть 1. Заказы: получить точную выручку

Создайте лист Заказы. Заголовки A1:H1: №, Дата, Клиент, Товар, Количество, Цена, Статус, Канал. Введите строки:

2. №: 1001; Дата: 01.10.2026; Клиент: Альфа; Товар: Кабель; Количество: 4; Цена, ₽: 250; Статус: Оплачен; Канал: Онлайн.

3. №: 1002; Дата: 02.10.2026; Клиент: Бета; Товар: Мышь; Количество: 3; Цена, ₽: 800; Статус: Оплачен; Канал: Магазин.

4. №: 1003; Дата: 03.10.2026; Клиент: Альфа; Товар: Клавиатура; Количество: 2; Цена, ₽: 1500; Статус: Ожидает; Канал: Онлайн.

5. №: 1004; Дата: 04.10.2026; Клиент: Гамма; Товар: Кабель; Количество: 10; Цена, ₽: 250; Статус: Оплачен; Канал: Магазин.

6. №: 1005; Дата: 05.10.2026; Клиент: Бета; Товар: Мышь; Количество: 1; Цена, ₽: 800; Статус: Возврат; Канал: Онлайн.

7. №: 1006; Дата: 06.10.2026; Клиент: Дельта; Товар: Монитор; Количество: 2; Цена, ₽: 9000; Статус: Оплачен; Канал: Онлайн.

8. №: 1007; Дата: 07.10.2026; Клиент: Альфа; Товар: Кабель; Количество: 6; Цена, ₽: 250; Статус: Оплачен; Канал: Онлайн.

9. №: 1008; Дата: 08.10.2026; Клиент: Гамма; Товар: Клавиатура; Количество: 1; Цена, ₽: 1500; Статус: Ожидает; Канал: Магазин.

1. Посчитать сумму каждой строки

Результат. В столбце I должна появиться стоимость каждого заказа, а не общий итог.

Сделайте. В I1 напишите Сумма, ₽. В I2 введите =E2*F2 и протяните формулу до I9. Для денежных значений задайте числовой формат с двумя знаками после запятой.

Проверьте. I2 = 1 000 ₽, I7 = 18 000 ₽. Если во всех строках одно число, проверьте, не закрепили ли вы номер строки знаком $.

2. Узнать сумму всех заказов и не спутать её с выручкой

Результат. Получить контрольную сумму всех строк, включая неоплаченные и возврат.

Сделайте. В I11 напишите Все заказы, в J11 введите =СУММ(I2:I9). Формула складывает все восемь строк, не оценивая статус.

Проверьте. J11 = 30 700 ₽. Эту цифру нельзя назвать выручкой: среди строк есть два ожидающих заказа и один возврат.

3. Посчитать только оплаченные заказы

Результат. Выручка без ожидающих и возврата.

Сделайте. В J12 введите =СУММЕСЛИМН(I2:I9;G2:G9;"Оплачен"). Суммируемый диапазон и диапазон условия должны иметь одинаковую длину.

Проверьте. J12 = 25 400 ₽. Если вышло 30 700 ₽, условие не применилось. Если ноль, проверьте написание статуса и то, что суммы в I являются числами.

4. Разделить выручку по каналам

Результат. Сумма оплаченных онлайн-заказов.

Сделайте. В J13 введите =СУММЕСЛИМН(I2:I9;G2:G9;"Оплачен";H2:H9;"Онлайн"). Условия действуют одновременно: каждая учитываемая строка должна быть и оплаченной, и онлайн.

Проверьте. J13 = 20 500 ₽. Не прибавляйте онлайн-возврат из строки 6: статус у него другой.

5. Проверить число заказов каждого статуса

Результат. Сводка, которая помогает проверить полноту списка.

Сделайте. В K2:K4 запишите Оплачен, Ожидает, Возврат. В L2 введите =СЧЁТЕСЛИМН($G$2:$G$9;K2) и протяните до L4. Закреплённый диапазон не сдвинется при копировании, ссылка K2 сменится на K3 и K4.

Проверьте. Должно быть 5, 2 и 1; сумма этих чисел равна восьми строкам данных. Если сумма не равна восьми, найдите пустой или иначе написанный статус.

6. Сделать отчёт устойчивым к новым заказам

Результат. Новая строка войдёт в расчёт без ручной замены I2:I9 на более длинный диапазон.

Сделайте. Выделите A1:I9, нажмите Ctrl+T, подтвердите наличие заголовков и назовите таблицу Заказы. Добавьте снизу заказ 1009: 09.10.2026, Бета, Кабель, 2, 250, Оплачен, Магазин. Убедитесь, что формула в столбце Сумма, ₽ продолжилась. Для оплаченной выручки используйте структурированные ссылки: =СУММЕСЛИМН(Заказы[Сумма, ₽];Заказы[Статус];"Оплачен").