Практикум по Excel: 20 задач для отработки навыков с решениями

Освоить Excel можно только через практику: решая реальные кейсы от подсчета сумм до автоматизации отчетов. В этой статье собраны 20 заданий разного уровня сложности с пошаговым разбором формул и логики действий. Вы научитесь использовать функции ВПР (VLOOKUP), ЕСЛИ (IF), строить сводные таблицы и настраивать условное форматирование. Начните с базовых упражнений, чтобы закрепить фундамент, и постепенно переходите к продвинутым инструментам аналитики.

Базовый уровень: работа с данными и простыми формулами

Первые шаги направлены на понимание структуры таблицы и арифметических операций.

Задание 1. Подведение итогов продаж

Цель: Научиться использовать функцию СУММ (SUM) и автозаполнение.

1. В столбце A (ячейки A1:A4) введите названия регионов: Москва, СПб, Екатеринбург, Казань.
2. В столбце B (B1:B4) внесите суммы продаж: 15000, 23000, 18000, 12000.
3. В ячейке B5 введите формулу: =СУММ(B1:B4). Результат должен составить 68000.

Разбор: Если вместо числа отображается ошибка или текст, проверьте формат ячеек (должен быть «Числовой»). Для быстрого копирования формулы вниз выделите ячейку с результатом и потяните за маркер заполнения в правом нижнем углу.

Задание 2. Расчет среднего чека и максимального значения

Цель: Освоить функции СРЗНАЧ (AVERAGE) и МАКС (MAX). Добавьте в файл из предыдущего задания:

• В ячейку C1 напишите «Средний чек», в C2 введите: =СРЗНАЧ(B1:B4).
• В ячейку D1 напишите «Максимум», в D2 введите: =МАКС(B1:B4).

Разбор: Функция СРЗНАЧ автоматически игнорирует пустые ячейки. Если вы видите ошибку #ДЕЛ/0!, значит, диапазон полностью пуст или не содержит чисел.

Задание 3. Визуализация отклонений

Цель: Применить условное форматирование.

1. Выделите диапазон продаж (B1:B4).
2. Перейдите на вкладку Главная > Условное форматирование > Правила выделения ячеек > Меньше чем.
3. Введите значение 15000 и выберите красный цвет заливки.

Разбор: Это позволяет мгновенно видеть регионы с низкими показателями. Форматирование динамическое: при изменении цифры цвет обновится автоматически.

Средний уровень: логика и поиск данных

На этом этапе мы подключаем логические функции и работу со справочниками.

Задание 4. Расчет скидки в зависимости от оборота

Цель: Использовать функцию ЕСЛИ (IF). Данные: Столбец A — клиент, столбец B — оборот (например, 50000, 30000, 80000). В столбце C рассчитайте итоговую сумму со скидкой 10%, если оборот больше 50000: =ЕСЛИ(B1>50000; B1*0,9; B1)

Разбор: Синтаксис функции: ЕСЛИ(условие; значение_если_истина; значение_если_ложь). Обратите внимание на разделитель — в русской версии Excel это точка с запятой (;), а десятичный разделитель — запятая (,).

Задание 5. Поиск цены товара по названию

Цель: Освоить функцию ВПР (VLOOKUP). Справочник: Создайте таблицу A1:B5, где столбец A — Товар (Яблоки, Бананы...), столбец B — Цена. В отдельной ячейке (например, D1) введите название товара «Бананы», а в E1 получите цену: =ВПР(D1; A1:B5; 2; 0)

Разбор: Последний аргумент 0 (или ЛОЖЬ) обязателен для точного совпадения. Ошибка #Н/Д означает, что искомое значение не найдено (проверьте лишние пробелы или регистр букв).

Задания 6–8. Статистика и даты

• Задание 6: Посчитать количество заказов свыше 1000 руб: =СЧЁТЕСЛИ(B:B; ">1000").
• Задание 7: Суммировать продажи только по Москве: =СУММЕСЛИ(A:A; "Москва"; B:B).
• Задание 8: Рассчитать возраст клиента по дате рождения (ячейка C1): =РАЗНДАТ(C1; СЕГОДНЯ(); "Г").

Продвинутый уровень: аналитика и сводные таблицы

Инструменты для обработки больших массивов данных и построения отчетов.

Задание 11. Создание сводной таблицы

Цель: Быстро агрегировать данные. Имея таблицу из 100+ строк (Дата, Продукт, Сумма):

1. Выделите весь диапазон данных.
2. Вкладка Вставка > Сводная таблица.
3. В поле «Строки» перетащите «Продукт», в поле «Значения» — «Сумма».
4. Для группировки по времени кликните правой кнопкой по дате в таблице > Группировать > выберите «Месяцы».

Разбор: Если группировка не работает, убедитесь, что в исходном столбце с датами нет текстовых значений или пустых ячеек.

Задание 13. Комбинация ИНДЕКС и ПОИСКПОЗ

Цель: Заменить ВПР более гибкой связкой функций. Формула для поиска значения в столбце B по ключу из D1 (ключи в столбце A): =ИНДЕКС(B:B; ПОИСКПОЗ(D1; A:A; 0))

Разбор: Это сочетание позволяет искать данные слева от ключевого столбца, чего не умеет классический ВПР. Ошибки часто возникают при несовпадении размеров диапазонов в обеих функциях.

Экспертный уровень: автоматизация и очистка данных

Задачи для оптимизации рутинных процессов.

Задание 16. Простой макрос для очистки

Цель: Автоматизировать удаление данных.

1. Нажмите Alt + F11 для открытия редактора VBA.
2. Вставьте модуль и код:

Sub ClearData()
    Range("B:B").ClearContents
End Sub

3. Запустите макрос через Alt + F8.

Разбор: Макросы могут быть заблокированы настройками безопасности. Разрешите их выполнение в разделе «Центр управления безопасностью».

Задание 17. Объединение текста

Используйте функцию ТЕКСТОБЪЕДИНИТЬ (TEXTJOIN) для создания списка через запятую, игнорируя пустые ячейки: =ТЕКСТОБЪЕДИНИТЬ("; "; ИСТИНА; A1:A10)

Задание 20. Использование Power Query

Цель: Профессиональная очистка данных без формул. Перейдите во вкладку Данные > Получить данные. Загрузите таблицу, удалите дубликаты, отфильтруйте лишние столбцы и нажмите «Закрыть и загрузить». При обновлении исходных файлов достаточно нажать кнопку «Обновить все», чтобы отчет перестроился автоматически.

Частые ошибки при выполнении заданий

1. #ЗНАЧ! вместо числа: Часто возникает, когда в диапазоне для суммирования есть текст (например, слово "нет данных" вместо 0).
2. Неверный результат ВПР: Искомое значение и ключ в справочнике имеют разные форматы (одно — число, другое — текст). Приведите их к одному виду через «Текст по столбцам».
3. Сбитые ссылки: При протягивании формулы ссылка на справочник смещается. Всегда фиксируйте диапазон справочника ($A$1:$B$10).

FAQ

Как быстро научиться решать задачи в Excel? Регулярность важнее объема. Решайте по 2–3 задачи ежедневно, постепенно усложняя условия. Старайтесь применять новые функции в своих рабочих таблицах сразу после изучения.

Где брать данные для тренировки? Вы можете создать вымышленные данные самостоятельно (как в примерах выше) или использовать открытые датасеты. Главное — структура: заголовки столбцов должны быть в первой строке, а данные не должны содержать объединенных ячеек.

Что делать, если формула не работает в моей версии Excel? Некоторые функции (ПРОСМОТРХ, ТЕКСТОБЪЕДИНИТЬ) доступны только в подписке Microsoft 365 и версиях начиная с 2019 года. В старых версиях используйте аналоги: связку ИНДЕКС+ПОИСКПОЗ вместо ПРОСМОТРХ и СЦЕПИТЬ для объединения текста.