Базовый (1 балл)
Время: 3-6 мин
Excel / Calc
ВПР (VLOOKUP)
ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Автофильтр Ctrl+Shift+L
Все задачи №{ topic_num } в каталоге
Задание №3. Поиск информации в реляционных базах данных
Тема: Связанные таблицы Excel, автофильтры, ВПР и ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Работа с базой данных из 3 связанных таблиц (Движение товаров, Товары, Магазины). Требуется вычислить суммарную выручку, количество упаковок или суммарный вес товаров конкретной группы в магазинах определенного района за заданный период. Решается строго в Excel за 3 минуты.
1. Структура реляционной базы данных ЕГЭ №3:
- Таблица 1: «Движение товаров» (Основная транзакционная таблица) — содержит дату, ID магазина, Артикул товара, Количество упаковок, Цену за штуку и Тип операции (Поступление / Продажа).
- Таблица 2: «Товары» (Справочник товаров) — содержит Артикул (первичный ключ), Отдел, Наименование, Вес упаковки, Единицу измерения.
- Таблица 3: «Магазины» (Справочник магазинов) — содержит ID магазина (первичный ключ), Район и Адрес.
2. Пошаговый алгоритм решения через Автофильтр (Самый надежный способ):
- Шаг 1. Таблица «Магазины»:
Включаем фильтр (Ctrl + Shift + L) $\to$ в столбце «Район» выбираем нужный (например, Октябрьский). Выписываем все ID магазинов (например:M1, M5, M6, M12, M15). - Шаг 2. Таблица «Товары»:
В столбце «Отдел» / «Наименование» фильтруем нужный товар (например, все виды Сгущенного молока). Выписываем список Артикулов (например:45, 46, 47, 48). - Шаг 3. Таблица «Движение товаров»:
• Включаем автофильтр (Ctrl + Shift + L).
• В столбце «ID магазина» ставим галочки на найденных магазинах.
• В столбце «Артикул» выбираем найденные артикулы.
• В столбце «Тип операции» выбираемПродажа(илиПоступление). - Шаг 4. Расчёт итоговой суммы (Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ):
В свободную ячейку справа вставляем формулу стоимости строки:=Количество * Ценаи растягиваем.
Вверху считаем итоговую сумму формулой:=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; G2:G10000).
(Код 9 означает СУММ, но строго по видимым строкам, скрытые строки фильтра игнорируются!)
Разновидности и прототипы задания на экзамене
Тип 1: Фильтрация по району и категории товаров (Выручка)
Найти общую выручку (руб) от продажи товаров категории (например, 'Зефир') в магазинах Заречного района.
Тип 2: Подсчет остатка товаров на складах
Вычисление разницы между 'Поступлением' и 'Продажей' (в упаковках или кг) на заданную дату.
Тип 3: Подсчет суммарного веса с переводом граммов в килограммы
Количество упаковок умножается на вес 1 упаковки из таблицы 'Товары' и делится на 1000.
Анти-примеры (Типичные ошибки vs Как делать правильно)
Как делать НЕ надо:
Ошибка: Использовать функцию =СУММ() на отфильтрованной таблице
Обычная функция СУММ() суммирует ВСЕ строки листа (включая скрытые фильтром) и выдаст ответ в 10 раз больше!Как делать ПРАВИЛЬНО:
Правильно: Использовать только =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; диапазон) — она суммирует строго видимые отфильтрованные ячейки!Как делать НЕ надо:
Ошибка: Забыть перевести граммы в килограммы при вопросе о весе в кг
Умножить количество упаковок на 250 г и не разделить на 1000.Как делать ПРАВИЛЬНО:
Правильно: Внимательно перечитать вопрос задачи: если вес в граммах, а просят кг — разделить итог на 1000.Как делать НЕ надо:
Ошибка: Перепутать операцию 'Поступление' и 'Продажа'
Посчитать выручку по операциям поступления товара на склад магазина.Как делать ПРАВИЛЬНО:
Правильно: Выручка от покупателей — это строго операция 'Продажа'.
ГРОБ
ГРОБ №3: Товары с одинаковым названием в разных отделах
Товар 'Чай черный' есть в отделе 'Бакалея' и в отделе 'Напитки', а в вопросе требуется учесть только отдел 'Бакалея'.
Как обойти ловушку: Никогда не ищите товары простым поиском названия: сначала отфильтруйте столбец 'Отдел', а затем внутри него выбирайте артикулы.
Лайфхаки и подводные камни на экзамене:
- Комбинация клавиш `Ctrl + Shift + L` мгновенно включает и выключает автофильтр в Excel и LibreOffice.
- С помощью формулы `=ВПР(A2; Магазины!$A$2:$B$20; 2; 0)` можно за 5 секунд подтянуть район прямо в основную таблицу 'Движение товаров'!
- Для быстрого просмотра суммы выделите диапазон нужных чисел и посмотрите в правый нижний угол окна Excel (строка состояния показывает сумму и количество).