Задание №3 — Базы данных
В файле приведён фрагмент базы данных «Кондитерские изделия» о поставках конфет и печенья в магазины районов города. База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой половины июня 2023 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид.
| ID операции | Дата | ID магазина | Артикул | Количество упаковок, шт. | Тип операции |
Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид.
| Артикул | Отдел | Наименование товара | Ед_изм | Количество в упаковке | Цена за упаковку |
Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид.
| ID магазина |
Район | Адрес |
На рисунке приведена схема указанной базы данных.
Используя информацию из приведённой базы данных, определите общую массу (в кг) всех видов зефира, полученных магазинами на улице Металлургов за период с 4 по 13 июня включительно.
В ответе запишите только число.
Правильный ответ
3570
Пояснение
Решение:
Нужна масса зефира, который был получен магазинами, то есть учитываем только операции с типом «Поступление», а «Продажу» игнорируем. Данные разнесены по трём таблицам: даты, количество упаковок и тип операции лежат в «Движении товаров», масса упаковки — в «Товаре» (связь по полю «Артикул»), адрес — в «Магазине» (связь по полю «ID магазина»).
Сначала по таблице «Магазин» находим магазины на улице Металлургов. Их два: M2 по адресу «ул. Металлургов, 12» и M16 по адресу «ул. Металлургов. 29». Во втором адресе после слова «Металлургов» стоит точка, а не запятая, поэтому поиск по строке «Металлургов,» этот магазин теряет — это главная ловушка задания: без M16 ответ получается ровно вдвое меньше, 1785.
Затем в таблице «Товар» отбираем зефир — это артикулы с 4 по 7: «Зефир в шоколаде» (250 граммов в упаковке), «Зефир ванильный» (800), «Зефир воздушный» (500) и «Зефир лимонный» (1000). Все четыре товара измеряются в граммах, поэтому масса одной операции равна количеству упаковок, умноженному на количество граммов в упаковке.
В таблице «Движение товаров» отводим свободный столбец G, записываем в ячейку G2 формулу и копируем её до конца таблицы (до строки 4321), а затем суммируем столбец и делим результат на 1000, чтобы перевести граммы в килограммы:
=ЕСЛИ(И(F2="Поступление";
B2>=ДАТА(2023;6;4); B2<=ДАТА(2023;6;13);
ИЛИ(C2="M2"; C2="M16");
D2>=4; D2<=7);
E2*ВПР(D2; Товар!A:E; 5; 0);
0)
=СУММ(G2:G4321)/1000
Сумма равна 3 570 000 граммов, то есть 3570 кг (по 1785 кг на каждый из двух магазинов на улице Металлургов).
Ответ: 3570