В прошлых статьях мы познакомились с реляционным подходом в базах данных, научились собирать данные из нескольких таблиц в одну с помощью функции ВПР(), а также попробовали свои силы в использовании автофильтров и некоторых функций LibreOffice Calc.
Теперь настало время применить все полученные навыки на практике и освоить решение заданий 3 ЕГЭ по информатике!
Что же, в этих заданиях вам даётся файл с фрагментом базы данных. В нём содержатся три таблицы:
В первой обычно представляется движение товаров: продажа или поступление, дата операции, артикул товара, идентификатор магазина и количество проданного или поступившего товара
Во второй таблице даются цены каждого товара, его краткое описание и самое важное для связи между таблицами – артикул этого товара
Третья же таблица несёт в себе информацию о самом магазине, его расположении и, конечно, идентификаторе этого магазина.
Типовая схема такого фрагмента базы данных представлена на рисунке ниже.
Мы не зря здесь акцентируем внимание на артикуле товара и идентификаторе магазина – именно через эти ключи и осуществляется связь между таблицами базы данных. Следовательно, для перемещения каких-либо данных между таблицами мы будем использовать только эти два ключа!
То есть перемещать данные о цене товара, об объёме упаковки и так далее в таблицу «Движение товаров», с которой и будем работать большую часть времени, мы можем только по ключу «Артикул».
Аналогично данные о районе можно перенести из таблицы «Магазин» в «Движение товаров» по ключу «ID магазина».
Но вернёмся к условию задания. После описания таблиц в прикреплённом файле даётся информация, по которой мы будем приходить к ответу на задание. Чаще всего нам даётся какой-либо товар или группа товаров, обозначается диапазон дат, в которые с этим товаром что-то происходило, и группа магазинов, в которые эти товары поступили или были проданы.
А по тому числу, которое необходимо дать в ответ, мы можем разделить задания 3 на два типа:
В заданиях первого типа от нас требуется подсчитать общееколичество поступившего или проданного товара: это может быть сумма в рублях или вес в килограммах, граммах.
В заданиях второго типа требуется вычислить изменениеколичества этого товара. Например, на сколько единиц увеличилось его количество в заданный период.
Для решения заданий первого типа нам необходимо перенести все нужные данные в одну таблицу, у нас это будет «Движение товаров», отфильтровать все строки по заданным значениям и подсчитать нужную величину.
Именно решению заданий 3 этого типа и посвящена эта статья. Задания второго типа мы рассмотрим в отдельной статье.
Алгоритм решения
Итак, в общих чертах мы уже рассмотрели алгоритм решения заданий 3 первого типа. Давайте приступим к его реализации. Для начала рассмотрим задание с такой формулировкой:
В файле приведён фрагмент базы данных «Молочные продукты» о поставках товаров в магазины районов города. База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой половины августа 2023 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид.
ID операции
Дата
ID магазина
Артикул
Количество упаковок, шт
Тип организации
Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид.
Артикул
Отдел
Наименование товара
Единица измерения
Количество в упаковке
Цена за упаковку
Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид.
ID магазина
Район
Адрес
На рисунке приведена схема указанной базы данных.
Используя информацию из приведённой базы данных, определите, на какую сумму (в рублях) было продано варенца термостатного в магазинах Нагорного района за период с 5 по 14 октября включительно.
В ответе запишите только число.
Первым делом откроем прикреплённый к заданию файл в LibreOffice Calc и ознакомимся с данными таблицами.
Таблица «Движение товаров»
Таблица «Товар»
Таблица «Магазин»
Нас интересует товар «Варенец термостатный» и его цена из таблицы «Товар» и несколько магазинов Нагорного района из таблицы «Магазин».
Дальше есть два пути решения: вы можете запомнить нужный артикул товара и ID магазинов и отфильтровать таблицу «Движение товаров» по этим значениям, датам и типу операции; или же перенести все данные в одну таблицу и в качестве параметров для фильтрации выбирать не абстрактные цифры артикула и идентификаторов магазинов, а понятные названия товара и районов магазинов.
Второй путь более надёжный, и мы крайне рекомендуем вам поступать именно так!
Первым делом приведём таблицу «Движение товаров» в порядок, сузим столбцы, расставим границы и выделим заголовки, чтобы нам было проще ориентироваться в ней. Теперь добавим справа столбец «Товар», в который с помощью функции ВПР() перенесём названия каждого товара в соответствии с его артикулом.
Для этого во вторую ячейку столбца «G» напишем формулу:
=ВПР(D2;Товар.$A$2:$F$61;3;0)
В качестве критерия поиска выбираем столбец «D» (Артикул), массивом у нас служит вся таблица «Товар», из неё нужен только третий столбец, и нам требуется именно точное совпадение, поэтому последним аргументом пишем 0 (ЛОЖЬ).
Растягиваем формулу до конца таблицы и получаем столбец с названием каждого товара.
Аналогично перенесём все районы из таблицы «Магазин». Формула в ячейке «H2» будет такая:
=ВПР(C2;Магазин.$A$2:$C$19;2;0)
Растягивая формулу, вниз получим столбец с районом каждого магазина.
Осталось дело за малым – перенести в эту таблицу цену за упаковку каждого товара и вычислить общую стоимость товара, перемножив цену на количество упаковок.
Для переноса цены воспользуемся всё той же формулой, что была в столбце «G», только поменяем в ней значение столбца (третий аргумент функции) с 3 на 6, в котором и записаны цены.
=ВПР(D2;Товар.$A$2:$F$61;6;0)
Теперь у нас есть столбец с ценой каждого товара.
И последим столбцом в нашей таблице будет «Стоимость», в котором перемножим значения столбцов «E» и «I».
Осталось лишь применить нужные фильтры к таблице. Начнём с дат. В условии дан диапазон от 5 до 14 октября включительно. В автофильтре выбираем даты, входящие в этот диапазон: 07, 09, 10 и 14.
В типе операции выбираем только продажу.
В столбец «Товар» выбираем интересующий нас Варенец термостатный.
Магазины только Нагорногорайона.
В итоге получаем вот такую небольшую таблицу.
Осталось лишь сложить все числа в столбце «Стоимость». Для этого можно выделить их и в правом нижнем посмотреть сумму. Либо можно использовать функцию АГРЕГАТ().
Функция АГРЕГАТ() объединяет в себе возможности множества стандартных функций (таких как СУММ(), СРЗНАЧ(), СЧЁТ(), МАКС(), МИН() и др.), но при этом умеет игнорировать скрытые строки, ошибки и другие элементы, которые могут мешать корректным вычислениям.
Если же мы к столбцу «J» применим обычную функцию СУММ(), то она включит в сумму и видимые, и скрытые строки. Именно поэтому нужна функция АГРЕГАТ(), которая вычислит сумму только видимых строк.
функция – номер функции, которую мы хотим использовать внутри АГРЕГАТ(). Например: 9 – сумма (СУММ), 1 – среднее значение (СРЗНАЧ), 2 – количество (СЧЁТ), 4 – максимум (МАКС), 5 – минимум (МИН) и так далее.
параметры – задают, какие данные нужно игнорировать. В нашей задаче значение 1 позволяет не учитывать скрытые строки.
диапазон – диапазон ячеек, по которому выполняется расчёт.
(необязательный) дополнительный аргумент – нужен некоторым функциям. Например, для функций НАИБОЛЬШИЙ() и НАИМЕНЬШИЙ() указывают номер искомого элемента k. Для суммы он не нужен.
В нашем же случае, для подсчёта суммы первым аргументом передадим число 9 (функция СУММ()), вторым – единицу (чтобы игнорировать значения в скрытых строках), а третьим – диапазон ячеек таблицы в столбце «J». Формула будет такая:
=АГРЕГАТ(9;1;J1092:J3720)
В результате получаем ответ на это задание – 133228.
Пример
И разберём еще один пример первого типа задания 3. В этот раз уже не будем так подробно останавливаться на каждом шаге решения – во многом оно будет идентично рассмотренному выше.
Формулировка у нас будет следующей:
В файле приведён фрагмент базы данных «Хозтовары» о поставках товаров для ухода, уборки и дома. База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой половины августа 2023 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид.
ID операции
Дата
ID магазина
Артикул
Количество упаковок, шт
Тип операции
Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид.
Артикул
Отдел
Наименование товара
Единица измерения
Количество в упаковке
Цена за упаковку
Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид.
ID магазина
Район
Адрес
На рисунке приведена схема указанной базы данных.
Используя информацию из приведённой базы данных, определите общий объём (в литрах) всех видов шампуня для волос, проданных магазинами, расположенными на Тургеневской улице, за период с 3 по 22 сентября включительно.
В ответе запишите целую часть числа.
Откроем прикреплённый к заданию файл и сразу добавим в таблицу «Движение товаров» следующие столбцы:
Товар
Улица
Ед_изм (единица измерения)
Количество в упаковке
Объём (л)
Если честно, столбец с единицами измерения тут скорее для самопроверки. В дальнейшем мы увидим, что шампуни измеряются только в миллилитрах, но лучше всегда добавлять единицы измерения, чтобы точно быть уверенными в корректности своих вычислений. Вдруг автор захочет добавить какой-то товар не в миллилитрах, а в литрах?
Столбцы готовы, время перенести все данные в одну таблицу с помощью ВПР().
Формулы для каждого из столбцов у нас будут следующие:
Товар: =ВПР(D2;Товар.$A$2:$F$61;3;0)
Улица: =ВПР(C2;Магазин.$A$2:$C$19;3;0)
Ед_изм: =ВПР(D2;Товар.$A$2:$F$61;4;0)
Количество в упаковке: =ВПР(D2;Товар.$A$2:$F$61;5;0)
В итоге получаем такую таблицу.
Столбец «Объём (л)» пока не спешим заполнять, давайте сначала отфильтруем значения в таблице.
Для дат выберем значения: 07, 09, 14, 17 и 22. Для столбца «Улица» выберем две Тургеневские улицы: «Тургеневская, 2» и «Тургеневская, 36». Из всех товаров выберем только 3 шампуня: «Шампунь для жирных волос», «Шампунь для нормальных волос» и «Шампунь для сухих волос». Тип операции – «Продажа».
Получаем такую отфильтрованную таблицу.
Убеждаемся, что везде объём измеряется в миллилитрах, и высчитываем для каждой строки объем в литрах, умножая столбец «Количество в упаковке» на «Количество упаковок, шт», и делим полученное значение на 1000 чтобы получить из миллилитров литры.
Осталось лишь просуммировать все числа столбца «K» с помощью функции АГРЕГАТ():
=АГРЕГАТ(9;1;K1971:K6317)
Получаем число 1501,8.
В ответ же записываем только целую часть числа, а именно – 1501.
На этом мы закончим разбирать решение 3 заданий ЕГЭ первого типа. В следующей статье уже перейдём к более сложному – второму типу, где необходимо будет подсчитать разницу в количестве нужного товара по определённым критериям.
Разберём эту тему на бесплатном пробном занятии
Преподаватель покажет, как решать такие задачи, и подскажет, с чего начать подготовку. Группы до 8 человек, очно в Калининграде и онлайн.
Мы используем файлы cookie. Необходимые обеспечивают работу сайта: корзину и вход в личный кабинет. С вашего согласия мы также используем сервис Яндекс Метрика, чтобы анализировать посещаемость и улучшать сайт.
Подробнее — в политике конфиденциальности.
