«Анализ продаж магазина»
В финальном задании вам предстоит самостоятельно поработать с таблицей продаж небольшого магазина. В файле есть основная таблица с заказами и отдельный справочник товаров. Ваша задача — привести данные в порядок, выполнить расчеты, использовать формулы, применить логические функции, подтянуть данные из справочника через ВПР, создать сводную таблицу и построить график.
Задание
1. Оформить таблицу
На листе Продажи оформите таблицу:
Сделайте заголовки жирным шрифтом Добавьте заливку для строки заголовков Добавьте границы таблицы Выровняйте данные так, чтобы таблицу было удобно читать При необходимости измените ширину столбцов
2. Очистить и подготовить данные
Проверьте таблицу и выполните подготовку данных:
Удалите повторяющиеся строки, если они есть Проверьте, нет ли пустых строк внутри таблицы Отсортируйте данные по дате заказа от старых к новым Включите фильтры для всей таблицы
3. Рассчитать итоговую сумму заказа
В основной таблице создайте новый столбец:
Итоговая сумма
Рассчитайте итоговую сумму по формуле:
Количество х Цена за единицу
Формулу нужно ввести только в первой строке с данными, а затем протянуть вниз на остальные строки.
4. Определить размер заказа через ЕСЛИ
Создайте новый столбец:
Тип заказа
С помощью функции ЕСЛИ определите тип заказа:
Если итоговая сумма больше или равна 5000 — написать Крупный заказ Если итоговая сумма меньше 5000 — написать Обычный заказ
5. Определить уровень скидки через ЕСЛИМН
Создайте новый столбец:
Уровень скидки
С помощью функции ЕСЛИМН распределите заказы по уровню скидки:
Если итоговая сумма меньше 3000 — Без скидки Если итоговая сумма от 3000 до 6999 — Скидка 5% Если итоговая сумма 7000 и больше — Скидка 10%
6. Подтянуть данные из справочника через ВПР
На листе Справочник товаров есть таблица, где каждому товару соответствует поставщик.
В основной таблице на листе Продажи создайте новый столбец:
Поставщик
С помощью функции ВПР подтяните поставщика для каждого товара из справочника.
7. Посчитать общий итог
Под таблицей рассчитайте:
Общую сумму всех заказов с помощью функции СУММ Общее количество проданных товаров с помощью функции СУММ Количество крупных заказов с помощью фильтра или дополнительной проверки
8. Создать сводную таблицу
На новом листе создайте сводную таблицу и назовите лист:
Сводная таблица
В сводной таблице покажите
Сумму продаж по категориям Сумму продаж по менеджерам Количество заказов по категориям
Сводная таблица должна быть построена на основе основной таблицы с заказами.
9. Построить график
На основе сводной таблицы постройте график:
Продажи по категориям
Можно использовать столбчатую диаграмму.
Требования к сдаче
Таблица аккуратно оформлена Данные очищены от дубликатов Итоговая сумма рассчитана через формулу Функции ЕСЛИ и ЕСЛИМН использованы корректно Поставщик подтянут через ВПР Сводная таблица построена правильно График создан на основе данных В работе нет ручных расчетов там, где должны использоваться формулы Excel
Пример итоговой работы
Ниже представлен пример, как может быть оформлена работа в таблицах Excel, визуальные правила и применение формул.
