ЕГЭ по информатике. Задание №3. Базы данных в Excel

Что нужно знать и уметь

Для решения задач по данной теме, нам понадобится умение работать с MS Excel:

1. Таблицы в Excel
2. Сортировка данных
3. Фильтрация данных
4. Умение строить формулы
5. Умение работать с агрегатными функциями
6. Владение функцией ВПР
7. Умение строить сводные таблицы
8. Понимание организации хранения данных в БД:
   1. Таблицы, записи, поля
   2. Ключи
   3. Взаимосвязи между таблицами

Внимание! Начиная с учебного года 2025-2026, задания выполняются в LibreOffice

Что проверяет задание №3

Задание моделирует работу с базой данных: есть несколько связанных таблиц (справочники и факты), нужно объединить данные, посчитать показатели, отфильтровать, отсортировать и найти требуемый результат.

Коротко о необходимых инструментах

• Таблицы Excel («умные таблицы»): именованный объект с заголовками, автофильтром и структурой; удобен для формул и сводных.
• Сортировка и фильтр: упорядочиваем и оставляем только нужные строки перед расчётами (ускоряет поиск ответа)
• Формулы и ссылки: относительные/абсолютные, протяжка вниз, «Процентный формат», автосумма.
• ВПР (VLOOKUP): подтягиваем поля из справочников по ключу — «вертикальный просмотр».
• Сводные таблицы: быстрые агрегаты по полям (Сумма/Количество/Среднее), группировки по датам/категориям; must-have для «сложных» версий.

Шаблон аргументов ВПР

=ВПР(искомое_значение; таблица; номер_столбца; ЛОЖЬ)
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Важно: фиксируйте диапазон «таблица» абсолютными ссылками (F4 → $A$2:$E$1000), чтобы он не «съезжал» при копировании. :contentReference[oaicite:4]{index=4}

«Простое» задание №3: решаем через ВПР

Условие: см. kompege.ru/task?id=1956. Используем входной файл из задания (лист «Движение товаров» + справочники «Товар», «Магазин»).

Структура данных

• Движение товаров: дата, магазин, артикул, количество упаковок, тип операции (Поступление/Продажа), цена за единицу.
• Товар: артикул, отдел, наименование, ед. изм., количество в упаковке, поставщик.
• Магазин: ID магазина, район, адрес.

Цель урока-примера

1. Подтянуть из справочника «Товар» поле Название товара по артикулу (через ВПР).
2. Из справочника магазинов подтянуть район по ID магазина. (ВПР)
3. Отфильтровать по датам, району магазина и названию товара
4. Посчитать фактическое число проданных и полученных упаковок товара. (ИСПОЛЬЗОВАТЬ ФУНКЦИЮ СУММЕСЛИМН)
5. Вычесть из количества полученных, количество проданных
6. Результат и будет ответом на вопрос задачи

Пошагово

1. На листе «Движение товаров» добавьте столбец Товар
2. На листе «Движение товаров» добавьте столбец Район
3. На листе «Движение товаров» в столбец Товар и введите формулу с функцией ВПР:

   =ВПР(D2;$Товар.$A:$F;3;0)

   Закрепите диапазон F4 (если не закрепился автоматом, то есть на таблице Товар появились значки доллара), протяните вниз.

4. На листе «Движение товаров» в столбец Район и введите формулу с функцией ВПР:

   =ВПР(C2;$Магазин.$A:$C;2;0)

   Закрепите диапазон F4,(если не закрепился автоматом, то есть на таблице Магазин появились значки доллара), протяните вниз.

5. Добавьте ячейку  Упаковок поступило формулу:

   =СУММЕСЛИМН(E2:E2273;H2:H2273;"Яйцо диетическое";I2:I2273;"Заречный";F2:F2273;"Поступление") Примечание. Условие по датам можно не делать, так как все даты на листе "Движение товара" укладываются в диапазон условия

6. Добавьте ячейку Упаковок продано формулу:

   =СУММЕСЛИМН(E2:E2273;H2:H2273;"Яйцо диетическое";I2:I2273;"Заречный";F2:F2273;"Продажа")

7. Добавьте в ячейку Остаток формулу.
   =L2-L3

«Сложное» задание №3: ВПР + сводные таблицы

Условие: sdamgia.ru/problem?id=75240. Вложение: 03.ods. Классический кейс, где мало просто «свести» поле — нужно сначала дотянуть атрибуты из справочников (ВПР), а затем построить грамотные сводные.

Алгоритм решения (универсальный чек-лист)

1. Обогащение фактов через ВПР:
   • Из «Товар» подтянуть: отдел
   • Из «Магазин» подтянуть: район.

   =ВПР(D2;$Товар.$A:$F;2;0)
   =ВПР(C2;$Магазин.$A:$B;2;0)

2. Вычислимые поля:
   • Выручка строки

     =F2*G2

3. Сводные таблицы:
   • Поля строк: в зависимости от вопроса (Дата, или для других заданий Магазин).
   • Значения: Сумма Выручки (или для других заданий Количество/Среднее по условию).
   • Фильтры: Тип операции = Продажа, Район = Заречный, Категория товара = Молоко.
4. Ответ: делаем сортировку по сумме выручки по убыванию. Находим дату наибольшей выручки

Это слайд-шоу требует JavaScript.

Типичные формулировки и как их взять из сводной

• «Определите магазин/район с наибольшей выручкой в июне…» — сводная: Строки=Магазин (или Район), Значения=Сумма(Выручка), Фильтр=Дата (июнь), Тип=Продажа; сортировка по убыванию.
• «Суммарная выручка отдела X за период…» — добавьте Отдел в Фильтры/Столбцы и возьмите сумму.
• «Название товара с максимальной выручкой в магазине M5…» — Фильтры: Магазин=M5; Строки: Товар; Значения: Сумма(Выручка), сортировка по убыванию Top-1.

Частые ошибки и лайфхаки

• Неверный тип данных: дата как текст, цена как текст. Лекарство — «Текст по столбцам» или умножение на 1, смена формата ячеек.
• «Прыгающий» диапазон ВПР: всегда фиксируйте ссылку на таблицу F4 — $A$2:$E$1000. :contentReference[oaicite:6]{index=6}
• Смешение упаковок и штук: выручку считаем только по единицам товара (шт/л/кг и т. п.), значит обязательно умножаем на «Кол-во в упаковке» из справочника.
• «Продажа» vs «Поступление»: не путайте знаки. Для выручки берём только строки с типом «Продажа».
• Сводные и фильтры: после изменения данных обновляйте сводную (ПКМ → «Обновить»), следите за активными фильтрами.

Мини-практика для ученика

1. В вашем файле добавьте столбец «Кол-во в упаковке» на «Движении» через ВПР.
2. Посчитайте «Единиц» и «Сумма» (только «Продажа»).
3. Постройте сводную: «Сумма по магазинам», выполните сортировку по убыванию.
4. Найдите товар-лидер по выручке в магазине M5 и долю этого товара в выручке M5, используя «Показать значения как → % от итога по строке».