Задание 3 ЕГЭ по информатике: поиск информации в реляционных базах данных

В задании 3 база данных почти всегда состоит из трёх таблиц двух типов:

• Справочники — таблицы вроде «Товар», «Магазин», «Клиенты», «Ткани». В них по одной строке на объект (один товар, один магазин, один клиент) и есть постоянные свойства: название, цена, район, ФИО, цвет.
• Факты (журнал операций) — таблица вроде «Движение товаров», «Аренда», «Готовый товар». В ней много строк — по одной на каждое событие (продажу, аренду, выпуск партии), и в каждой строке — ссылки на справочники через ID, а не сами значения.

Схема связей рисуется линиями между полями-ключами. Ниже — реальная схема из открытого банка ФИПИ: таблица «Движение товаров» ссылается на «Магазин» через ID магазина и на «Товар» через Артикул.

Первичный ключ — поле, которое однозначно определяет строку справочника (ID магазина в «Магазине», Артикул в «Товаре»). Внешний ключ — то же поле в таблице фактов, где оно уже не уникально: один и тот же магазин встречается во многих строках «Движения товаров».

Главное следствие для решения: условие вопроса почти всегда разбито между таблицами. «Магазины на проспекте Мира» — это условие на справочник «Магазин». «Поступления зефира» — это условие на справочник «Товар» и на поле «Тип операции» в таблице фактов. Чтобы применить оба условия сразу, нужно узнать в справочниках подходящие ID и найти по ним строки в таблице фактов — это и есть «поиск в реляционной базе данных».

ВПР (в англоязычной версии — VLOOKUP) ищет значение в первом столбце указанного диапазона и возвращает значение из другого столбца той же строки. Синтаксис в LibreOffice Calc:

=ВПР(искомое_значение; диапазон_поиска; номер_столбца; 0)

Последний параметр — 0 (или ЛОЖЬ) — означает «только точное совпадение». Его нельзя пропускать: без него формула может найти похожее, но не то же самое значение, и результат окажется неверным без каких-либо признаков ошибки.

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

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

Здесь C2 — ячейка с ID магазина в строке движения товара, Магазин.$A$2:$B$4 — справочник с абсолютными ссылками (чтобы формула не съехала при копировании вниз), а 2 — номер столбца «Район» внутри этого диапазона. Растянув формулу на все строки, получаем столбец с районом для каждой операции — и его уже можно использовать как обычное условие фильтра или СУММЕСЛИМН.

СУММЕСЛИМН (SUMIFS) складывает значения одного столбца, если для каждой строки одновременно выполняются несколько условий на другие столбцы. Это ровно то, что нужно в задании 3: условие на артикул, условие на тип операции, условие на район (уже подтянутый через ВПР в столбец G) и условие на диапазон дат. Синтаксис:

=СУММЕСЛИМН(диапазон_суммирования; диапазон1; условие1; диапазон2; условие2; ...)

Для нашей учебной базы (артикул 1, тип «Поступление», район «Северный», даты с 1 по 6 марта 2024 года) формула выглядит так:

=СУММЕСЛИМН(
  D2:D6;
  E2:E6; 1;
  F2:F6; "Поступление";
  G2:G6; "Северный";
  B2:B6; ">="&ДАТА(2024;3;1);
  B2:B6; "<="&ДАТА(2024;3;6)
)

Здесь D — столбец «Количество упаковок» (то, что суммируем), E — «Артикул», F — «Тип операции», G — район из ВПР, B — дата. Функция ДАТА(год;месяц;день) собирает настоящую дату из чисел, чтобы сравнение ">=" работало корректно, а не как сравнение текстовых строк. Результат — 14, ровно как при подсчёте вручную.

Если условий на текстовое поле несколько (например, «Hyundai» и диапазон дат сразу), а сами условия неудобно разносить по разным столбцам, тот же результат даёт сводная таблица: строки — клиент, столбцы — марка автомобиля, значения — сумма аренды, фильтр — диапазон дат. Способ длиннее в настройке, но нагляднее, когда нужно сравнить сразу несколько категорий, а не одно число.

На экзамене доступна среда программирования — и часть сдающих решает задание 3 не в таблицах, а кодом. Это законный способ: правильный ответ засчитывается независимо от того, как он получен. Обычный план — открыть файл в редакторе таблиц, сохранить нужные листы как *.csv (Файл → Сохранить как → CSV, отдельно для каждого листа), а дальше читать эти файлы модулем csv из стандартной библиотеки — устанавливать ничего не нужно.

Код ниже воспроизводит ту же учебную задачу (артикул 1, поступления, Северный район, 1–6 марта 2024) — здесь содержимое CSV-файлов записано прямо в строки для наглядности, на экзамене вместо них будет open("magazin.csv", encoding="utf-8") и аналогично для двух других файлов:

import csv, io, datetime

magazin_csv = """ID магазина,Район
M1,Северный
M2,Южный
M3,Северный
"""

dvizhenie_csv = """ID операции,Дата,ID магазина,Артикул,Количество упаковок,Тип операции
1,03.03.2024,M1,1,10,Поступление
2,04.03.2024,M1,2,5,Поступление
3,05.03.2024,M2,1,8,Поступление
4,06.03.2024,M3,1,4,Поступление
5,07.03.2024,M1,1,6,Продажа
"""

magazin = {r["ID магазина"]: r["Район"]
           for r in csv.DictReader(io.StringIO(magazin_csv))}

d1 = datetime.date(2024, 3, 1)
d2 = datetime.date(2024, 3, 6)

total = 0
for row in csv.DictReader(io.StringIO(dvizhenie_csv)):
    if row["Артикул"] != "1":
        continue
    if row["Тип операции"] != "Поступление":
        continue
    if magazin[row["ID магазина"]] != "Северный":
        continue
    day, month, year = row["Дата"].split(".")
    d = datetime.date(int(year), int(month), int(day))
    if not (d1 <= d <= d2):
        continue
    total += int(row["Количество упаковок"])

print(total)

Программа печатает 14 — тот же ответ, что и формулы. Логика один в один повторяет решение в таблицах: сначала строим словарь «ID магазина → район» (аналог ВПР), затем в цикле проверяем все условия сразу (аналог СУММЕСЛИМН) и складываем количество. Если в проверочной среде экзамена окажется библиотека pandas, тот же результат даёт df[(df.Артикул==1)&(df.Тип=="Поступление")] с последующим .merge() с таблицей магазинов — но рассчитывать на неё заранее не стоит: гарантированно доступен только модуль csv из стандартной библиотеки.

День 1–2: откройте несколько заданий 3 из открытого банка, изучите структуру каждой базы данных — сколько таблиц, какая из них справочник, какая журнал операций, через какие поля они связаны. Не решайте пока сами задания — только читайте схемы. День 3–5: отработайте формулу ВПР на маленьких учебных таблицах (5–10 строк), обязательно с последним параметром 0. День 6–7: добавьте СУММЕСЛИМН с двумя-тремя условиями сразу, включая условие на диапазон дат через ДАТА().

День 1–3: решайте реальные задания 3 из открытого банка ФИПИ целиком — от скачивания файла до записи ответа. После каждого сверяйтесь со вторым способом (автофильтром вместо формулы или наоборот). День 4–5: если на экзамене планируете использовать Python — перенесите на него уже отработанную логику: словарь для справочника и цикл с несколькими условиями по образцу примеров в этой статье. День 6–7: решайте задания на время — на задание 3 по спецификации ФИПИ отводится около 3 минут, и в это время должно уложиться и открытие файла, и сам поиск. Проверьте себя в тренажёре на заданиях из банка ФИПИ.