В файле приведён фрагмент базы данных «Кондитерские изделия» о поставках конфет и печенья в магазины районов города.
База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой половины августа 2021 г., а также информацию о проданных товарах.
Поле Тип операции содержит значение Поступление или Продажа, а в поле Количество упаковок, шт внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня.
Заголовок таблицы имеет следующий вид.
| ID операции | Дата | ID магазина | Артикул | Количество упаковок, шт | Тип операции |
|---|
Таблица «Товар» содержит информацию об основных характеристиках каждого товара.
Заголовок таблицы имеет следующий вид.
| Артикул | Отдел | Наименование товара | Ед_изм | Количество в упаковке | Цена за упаковку |
|---|
Таблица «Магазин» содержит информацию о местонахождении магазинов.
Заголовок таблицы имеет следующий вид.
На рисунке приведена схема указанной базы данных.
Используя информацию из приведённой базы данных, определите, на сколько увеличилось количество упаковок галет для завтрака, имеющихся в наличии в магазинах Центрального района, за период с 2 по 14 августа включительно.
В ответе запишите только число.
Способ 1. Формулы ВПР и фильтр
Откроем файл, перейдём на лист «Движение товаров». Озаглавим столбец G «Район», столбец H — «Товар», а столбец I — «Изменение, шт.».
В ячейке G2 введём формулу =ВПР(C2;'Магазин'!A:C;2;0) и скопируем формулу до конца списка.
В ячейке H2 введём формулу =ВПР(D2;'Товар'!A:H;3;0) и скопируем формулу до конца списка.
В ячейке I2 введём формулу =ЕСЛИ(F2="Поступление";E2;-E2) и скопируем формулу до конца списка.
В результате получим расширенную таблицу «Движение товаров» с дополнительными столбцами.
Воспользуемся стандартными средствами редактора таблиц: требуется отфильтровать записи в таблице, оставив только операции с товаром «галет для завтрака», магазины Центральный района, даты с 2 по 14 августа включительно.
Для этого включим фильтр для столбцов от A до I, зададим условия и скопируем получившуюся таблицу на новый лист.
Окончательно, воспользовавшись формулой =СУММ(I2:I9999), определим, на сколько изменилось количество упаковок товара за указанный период.
Количество упаковок увеличилось на 736.
Способ 2. Фильтры по листам
Откроем файл, перейдём на лист «Магазин». Воспользуемся стандартными средствами редактора таблиц: требуется отфильтровать записи, оставив только магазины Центральный района. Для этого включим фильтр.
Получаем следующую таблицу:
| ID магазина | Район | Адрес |
|---|
| 1 | M1 | Центральный | просп. Мира, 45 |
| 2 | M5 | Центральный | ул. Гагарина, 17 |
| 3 | M6 | Центральный | просп. Мира, 10 |
| 4 | M10 | Центральный | пл. Революции, 1 |
| 5 | M15 | Центральный | Пушкинская, 8 |
| 6 | M18 | Центральный | Лермонтова, 9 |
Перейдём на лист «Товар». В этой таблице, воспользовавшись средствами поиска, найдём строку с товар «Галеты для завтрака». Артикул товара — 37.
| Артикул | Отдел | Наименование товара | Ед_изм | Количество в упаковке | Цена за упаковку |
|---|
| 1 | 37 | Печенье | Галеты для завтрака | грамм | 200 | 50 |
Теперь перейдём на лист «Движение товаров». Снова воспользуемся фильтром по столбцу «ID магазина», чтобы вывести только магазины Центральный района. В фильтре отметим ID магазинов из таблицы «Магазин» — M1, M5, M6, M10, M15 и M18. Также применим фильтр к столбцу «Артикул», чтобы оставить только записи по артикулу 37. В результате получим следующую таблицу:
| ID операции | Дата | ID магазина | Артикул | Количество упаковок, шт | Тип операции |
|---|
| 1 | 649 | 03.08.2021 | M1 | 37 | 300 | Поступление |
| 2 | 673 | 03.08.2021 | M5 | 37 | 300 | Поступление |
| 3 | 697 | 03.08.2021 | M6 | 37 | 300 | Поступление |
| 4 | 721 | 03.08.2021 | M10 | 37 | 300 | Поступление |
| 5 | 745 | 03.08.2021 | M15 | 37 | 300 | Поступление |
| 6 | 769 | 03.08.2021 | M18 | 37 | 300 | Поступление |
| 7 | 1729 | 09.08.2021 | M1 | 37 | 205 | Продажа |
| 8 | 1753 | 09.08.2021 | M5 | 37 | 199 | Продажа |
| 9 | 1777 | 09.08.2021 | M6 | 37 | 225 | Продажа |
| 10 | 1801 | 09.08.2021 | M10 | 37 | 237 | Продажа |
| 11 | 1825 | 09.08.2021 | M15 | 37 | 208 | Продажа |
| 12 | 1849 | 09.08.2021 | M18 | 37 | 205 | Продажа |
| 13 | 2809 | 10.08.2021 | M1 | 37 | 300 | Поступление |
| 14 | 2833 | 10.08.2021 | M5 | 37 | 300 | Поступление |
| 15 | 2857 | 10.08.2021 | M6 | 37 | 300 | Поступление |
| 16 | 2881 | 10.08.2021 | M10 | 37 | 300 | Поступление |
| 17 | 2905 | 10.08.2021 | M15 | 37 | 300 | Поступление |
| 18 | 2929 | 10.08.2021 | M18 | 37 | 300 | Поступление |
… и ещё 6 записей.
Далее необходимо посчитать изменение количества упаковок товара в магазинах Центральный района за указанный период. Скопируем полученную таблицу на отдельный лист и отсортируем записи по столбцу «Тип операции». В результате получаем следующую таблицу:
| ID операции | Дата | ID магазина | Артикул | Количество упаковок, шт | Тип операции |
|---|
| 2 | 649 | 03.08.2021 | M1 | 37 | 300 | Поступление |
| 3 | 673 | 03.08.2021 | M5 | 37 | 300 | Поступление |
| 4 | 697 | 03.08.2021 | M6 | 37 | 300 | Поступление |
| 5 | 721 | 03.08.2021 | M10 | 37 | 300 | Поступление |
| 6 | 745 | 03.08.2021 | M15 | 37 | 300 | Поступление |
| 7 | 769 | 03.08.2021 | M18 | 37 | 300 | Поступление |
| 8 | 2809 | 10.08.2021 | M1 | 37 | 300 | Поступление |
| 9 | 2833 | 10.08.2021 | M5 | 37 | 300 | Поступление |
| 10 | 2857 | 10.08.2021 | M6 | 37 | 300 | Поступление |
| 11 | 2881 | 10.08.2021 | M10 | 37 | 300 | Поступление |
| 12 | 2905 | 10.08.2021 | M15 | 37 | 300 | Поступление |
| 13 | 2929 | 10.08.2021 | M18 | 37 | 300 | Поступление |
| 14 | 1729 | 09.08.2021 | M1 | 37 | 205 | Продажа |
| 15 | 1753 | 09.08.2021 | M5 | 37 | 199 | Продажа |
| 16 | 1777 | 09.08.2021 | M6 | 37 | 225 | Продажа |
| 17 | 1801 | 09.08.2021 | M10 | 37 | 237 | Продажа |
| 18 | 1825 | 09.08.2021 | M15 | 37 | 208 | Продажа |
| 19 | 1849 | 09.08.2021 | M18 | 37 | 205 | Продажа |
| 20 | 3889 | 14.08.2021 | M1 | 37 | 367 | Продажа |
| 21 | 3913 | 14.08.2021 | M5 | 37 | 258 | Продажа |
… и ещё 4 записи.
Окончательно, воспользовавшись формулой =СУММ(E2:E13)-СУММ(E14:E25), получаем ответ — 736.
