О задании
В прошлых статьях мы познакомились с реляционным подходом в базах данных, научились собирать данные из нескольких таблиц в одну с помощью функции ВПР(), а также попробовали свои силы в использовании автофильтров и некоторых функций LibreOffice Calc.
Теперь настало время применить все полученные навыки на практике и освоить решение заданий 3 ЕГЭ по информатике!
Что же, в этих заданиях вам даётся файл с фрагментом базы данных. В нём содержатся три таблицы:
- В первой обычно представляется движение товаров: продажа или поступление, дата операции, артикул товара, идентификатор магазина и количество проданного или поступившего товара
- Во второй таблице даются цены каждого товара, его краткое описание и самое важное для связи между таблицами – артикул этого товара
- Третья же таблица несёт в себе информацию о самом магазине, его расположении и, конечно, идентификаторе этого магазина.
Типовая схема такого фрагмента базы данных представлена на рисунке ниже.
Мы не зря здесь акцентируем внимание на артикуле товара и идентификаторе магазина – именно через эти ключи и осуществляется связь между таблицами базы данных. Следовательно, для перемещения каких-либо данных между таблицами мы будем использовать только эти два ключа!
То есть перемещать данные о цене товара, об объёме упаковки и так далее в таблицу «Движение товаров», с которой и будем работать большую часть времени, мы можем только по ключу «Артикул».
Аналогично и со второй таблицей, переместить данные из таблицы «Товар» в «Движение товаров» можно только по ключу «ID магазина».
Но вернёмся к условию задания. После описания таблиц в прикреплённом файле даётся информация, по которой мы будем приходить к ответу на задание. Чаще всего нам даётся какой-либо товар или группа товаров, обозначается диапазон дат, в которые с этим товаром что-то происходило, и группа магазинов, в которые эти товары поступили или были проданы.
А по тому числу, которое необходимо дать в ответ, мы можем разделить задания 3 на два типа:
- В заданиях первого типа от нас требуется подсчитать общее количество поступившего или проданного товара: это может быть сумма в рублях или вес в килограммах, граммах.
- В заданиях второго типа требуется вычислить изменение количества этого товара. Например, на сколько единиц увеличилось его количество в заданный период.
Для решения заданий первого типа нам необходимо перенести все нужные данные в одну таблицу, у нас это будет «Движение товаров», отфильтровать все строки по заданным значениям и подсчитать нужную величину.
Именно решению 3 заданий этого типа и посвящена эта статья. Задания второго типа мы рассмотрим в отдельной статье.
Алгоритм решения
Итак, в общих чертах мы уже рассмотрели алгоритм решения заданий 3 первого типа. Давайте приступим к его реализации. Для начала рассмотрим задание с такой формулировкой:
«В файле приведён фрагмент базы данных «Молочные продукты» о поставках товаров в магазины районов города. База данных состоит из трёх таблиц.
…
Используя информацию из приведённой базы данных, определите, на какую сумму (в рублях) было продано варенца термостатного в магазинах Нагорного района за период с 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 – минимум (МИН) и так далее.
- параметры – задают, какие данные нужно игнорировать: 0 – не игнорировать ничего, 1 – игнорировать скрытые строки, 2 – игнорировать ошибки и так далее.
- диапазон – диапазон ячеек, по которому выполняется расчёт.
- (необязательный) дополнительный аргумент – используется, если выбранная функция требует больше одного диапазона (например, для функций типа КОРРЕЛ, МЕДИАНА и др.). Для суммы обычно не нужен.
В нашем же случае, для подсчёта суммы первым аргументом передадим число 9 (функция СУММ()), вторым – единицу (чтобы игнорировать значения в скрытых строках), а третьим – диапазон ячеек таблицы в столбце «J». Формула будет такая:
=АГРЕГАТ(9;1;J1092:J3720)
В результате получаем ответ на это задание – 133228.
Пример
И разберём еще один пример первого типа задания 3. В этот раз уже не будем так подробно останавливаться на каждом шаге решения – во многом оно будет идентично рассмотренному выше.
Формулировка задания будет следующей:
«В файле приведён фрагмент базы данных «Хозтовары» о поставках товаров для ухода, уборки и дома. База данных состоит из трёх таблиц.
…
Используя информацию из приведённой базы данных, определите общий объём (в литрах) всех видов шампуня для волос, проданных магазинами, расположенными на Тургеневской улице, за период с 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 заданий ЕГЭ первого типа. В следующей статье уже перейдём к более сложному – второму типу, где необходимо будет подсчитать разницу в количестве нужного товара по определённым критериям.
