Задание 3. Реляционные базы данных

Задание 3 демонстрационного варианта ЕГЭ-2027 (ФИПИ).

Файл к заданию: demo_3.ods – скачайте его и повторяйте шаги разбора в LibreOffice.

В файле приведён фрагмент базы данных «Кондитерские изделия» о поставках конфет и печенья в магазины районов города. База данных состоит из трёх таблиц.

Таблица «Движение товаров» содержит записи о поступлении товаров со склада в магазины в течение июня 2025 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид.

Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид.

Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид.

На рисунке приведена схема указанной базы данных.

Используя информацию из приведённой базы данных, определите общую массу (в кг) всех видов зефира, полученного магазинами, расположенными на Прибрежной улице, за период с 5 по 19 июня включительно. В ответе запишите только число.

Дальше – восемь коротких записей: что нажимать и что при этом происходит на экране. Смотреть их подряд не обязательно, заголовки над кадром работают как оглавление – можно открыть тот приём, который нужен сейчас.

Формулы на записях выбраны ради механики, а не ради смысла: там складывают артикулы и умножают их на количество упаковок. Таких величин в задаче нет, и считать их незачем – важно, как формула вводится, как закрепляется ссылка и как она расходится по столбцу. Осмысленные формулы этого разбора – ниже, в блоке «Решение».

Вопрос задания 3 всегда составлен из нескольких независимых условий, и лежат они на разных листах. Поэтому первое действие – разобрать вопрос на условия и для каждого найти лист: выписать их столбиком и подписать, откуда брать, быстрее, чем перечитывать условие по ходу работы.

Из таблицы следуют четыре решения.

1. Собирать всё на листе журнала. Одна строка «Движение_товаров» – одна операция, а именно операции и отбираются. Справочники ничего не отбирают, они только поставляют недостающие поля. Поэтому не фильтровать три листа по отдельности, а подтянуть адрес, наименование и вес упаковки в журнал через ВПР по ключам: ID магазина ведёт на «Магазин», Артикул – на «Товар». Связи видны на схеме.
2. Массы в базе нет – её считаем. Одна упаковка – это «Количество в упаковке» в единицах «Ед_изм». У всех четырёх видов зефира это граммы (250, 800, 500 и 1000 г), значит масса строки = упаковки × граммы, а ответ – сумма, делённая на 1000. Проверять столбец «Ед_изм» надо всегда: бывают штуки, литры, миллилитры.
3. «Всех видов» – это не одно наименование. Зефира в справочнике четыре: «Зефир в шоколаде», «Зефир ванильный», «Зефир воздушный», «Зефир лимонный». Отбирать по началу названия, а не по одному товару.
4. Прибрежная улица – это два магазина: M9 («Прибрежная, 7») и M11 («Прибрежная, 21»), оба в Заречном районе. Район в этом вопросе не нужен – спрашивают улицу.

Дальше всё решение – два действия: собрать таблицу, отфильтровать и сложить.

На листе «Движение_товаров» справа от данных четыре новых столбца; заголовки в строку 1: Адрес, Товар, В упаковке, Масса, г. Формулы для строки 2:

G2: =ВПР(C2;$Магазин.$A$2:$C$19;3;0)
H2: =ВПР(D2;$Товар.$A$2:$F$61;3;0)
I2: =ВПР(D2;$Товар.$A$2:$F$61;5;0)
J2: =E2*I2

Аргументы ВПР по порядку:

• что ищем – C2, ID магазина из текущей строки;
• где ищем – $Магазин.$A$2:$C$19. Искомое значение обязано стоять в первом столбце диапазона, а знаки $ нужны, чтобы диапазон не уехал при протягивании;
• номер столбца диапазона, откуда взять ответ: 3 – «Адрес», столбцы нумеруются с единицы;
• последний аргумент 0 – точное совпадение. Без него ВПР ищет приблизительно и ошибается (подробно – в «Типичных ошибках»).

Затем выделить G2:J2 и двойным щелчком по маркеру заполнения протянуть до строки 6481. Проверка первой строки: G2 = «просп. Мира, 45», H2 = «Батончик соевый», I2 = 250, J2 = 50000. Если внизу столбцов появилось #Н/Д – в диапазоне ВПР забыты $.

Выделить любую ячейку таблицы → Данные → Автофильтр (Ctrl+Shift+L). Отбор по четырём столбцам:

1. «Тип операции» – оставить только «Поступление».
2. «Дата» – оставить с 05.06.2025 по 19.06.2025, обе границы включительно.
3. «Адрес» – в поле поиска выпадающего списка набрать «Прибреж», останутся два адреса, отметить оба.
4. «Товар» – в поле поиска набрать «Зефир», отметить все четыре.

Осталось 17 строк. Складывать их формулой не нужно: выделите мышью ячейки столбца «Масса, г» от первой отобранной строки до последней, и Calc напишет сумму сам – внизу окна, в строке состояния. Скрытые фильтром строки в неё не попадают, хотя выделение и проходит сквозь них: рядом стоит «Выбрано: 17 строк», и это первая проверка, что сложилось именно отобранное.

Сумма – 1630000 граммов, то есть 1630 кг. Ответ: 1630.

В банке задания 3 база та же по устройству, меняется вопрос – и каждая перемена стоит одной-двух правок.

• Не отсеяли продажи. «Полученного» – это только «Поступление».
• Границы дат взяли строго. «С 5 по 19 включительно» – это >= и <=. Без 5 и 19 июня выходит 1430.
• Ответили не в тех единицах. Сумма упаковок – 2500, масса в граммах – 1630000. Спрашивают килограммы.
• ВПР без последнего аргумента. Последний аргумент – режим поиска, и без него включается приблизительный, рассчитанный на отсортированный справочник: функция идёт по первому столбцу и отдаёт последнее значение, которое не больше искомого. ID магазинов – текст, а текст сравнивается по алфавиту, где M1 < M10 < … < M18 < M2, тогда как в справочнике строки идут по-человечески: M1, M2, M3… Ища «M11», функция доходит до M1, видит, что следующий M2 уже больше, и возвращает адрес первого магазина. Так все магазины с M10 по M18 получают «просп. Мира, 45», «Прибрежная, 21» из выборки исчезает, и ответ выходит 765. Ячеек с ошибкой при этом нет ни одной – потому и опасно.
• Забыли $ в диапазоне ВПР. Диапазон без $ едет вниз вместе с формулой: во второй строке журнала это A2:C19, в третьей уже A3:C20, а в двадцатой – A20:C37, где справочника нет вовсе. Поэтому с третьей строки из диапазона выпадает M1 и все его операции дают #Н/Д, а ниже двадцатой строки #Н/Д идёт сплошняком.
• Сложили до фильтра или после его снятия. Сумма в строке состояния считает то, что выделено сейчас: со снятым фильтром это все 6 480 строк журнала, а не 17 отобранных. Рядом с суммой стоит число выделенных строк – сверяйте его.