Задание №3. Поиск и расчёты в связанных таблицах

Сколько килограммов товара поступило в магазины района? Какую сумму принесли продажи? В задании №3 ответ складывается из данных нескольких таблиц. В одной записаны операции, в другой — названия и характеристики товаров, в третьей — адреса магазинов. Решение состоит из трёх действий: связать эти сведения, отобрать подходящие операции и выполнить расчёт в нужных единицах.

Как связаны таблицы

В строке таблицы «Движение товаров» указано, когда, в каком магазине и с каким товаром произошла операция. Для товара используется артикул вместо названия, а для магазина — ID вместо адреса. Эти коды позволяют найти подробности в двух других таблицах.

Строки разных листов не обязаны стоять в одинаковом порядке. Связь устанавливается по коду. Например, магазин M15 ищется по значению M15 в столбце ID, а не в пятнадцатой строке листа.

Разбор: масса поступившего товара

В задании EGE-03-0001 ↗ требуется найти общий вес картофельного крахмала, поступившего в магазины Октябрьского района с 1 по 8 июня 2025 года включительно. Ответ нужен в килограммах. Ниже используются данные приложенного файла 614BFD.ods.

1. Условия отбора

Каждая часть вопроса задаёт отдельное условие. Все они должны выполняться одновременно.

Вопрос о поступлении, поэтому продажи не входят в сумму. Для веса понадобится ещё масса одной упаковки из таблицы «Товар».

2. Коды товара и магазинов

В таблице «Товар» строка «Крахмал картофельный» содержит артикул 42, единицу измерения «кг» и количество в упаковке 0,5. Значит, одна упаковка весит 0,5 кг. В таблице «Магазин» Октябрьскому району соответствуют пять ID: M1, M5, M6, M10, M15.

Теперь названия из вопроса переведены в значения, которые есть в таблице операций: товар 42 и любой из этих пяти магазинов.

3. Отбор операций в электронной таблице

На листе «Движение_товаров» данные занимают диапазон A1:G2273. Для этого диапазона включается фильтр. Заголовки входят в диапазон, чтобы каждое меню относилось к своему столбцу.

1. В столбце D «Артикул» остаётся значение 42.
2. В столбце C «ID магазина» остаются M1, M5, M6, M10 и M15.
3. В столбце B «Дата» задаётся период от 01.06.2025 до 08.06.2025, с обеими границами.
4. В столбце E «Тип операции» остаётся «Поступление».

Несколько выбранных магазинов означают «M1 или M5 или …». А фильтры разных столбцов соединяются условием «и»: нужный магазин и товар 42, и нужная дата, и поступление. Порядок применения этих фильтров результат не меняет.

Остаются пять операций. В таблице ниже показаны все пять; столбцы с одинаковыми значениями сокращены: везде артикул 42 и тип «Поступление».

Все поставки оказались 3 июня. Это результат отбора по полному периоду, а не причина заранее ограничить поиск одним днём.

4. Сумма упаковок и переход к килограммам

Сумма значений последнего столбца равна 180 + 180 + 170 + 180 + 180 = 890 упаковок. Пять — это число операций, а 890 — количество товара в упаковках.

В исходном листе количество находится в столбце F. После фильтрации сумму можно получить формулой в свободной ячейке вне диапазона данных:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;F2:F2273)

Число 9 означает суммирование. Функция пропускает строки, скрытые фильтром, и возвращает 890. В программе с английскими именами функций её название — SUBTOTAL. Обычная СУММ(F2:F2273) учла бы и отфильтрованные строки.

Из справочника известно: в упаковке 0,5 кг. Поэтому 890 \cdot 0{,}5 = 445 кг. Единица уже указана в килограммах, дополнительное деление на 1000 не требуется. Ответ: 445.

Как меняется расчёт

Связывание таблиц и отбор операций остаются теми же. Дальнейшее действие определяется вопросом. Ниже — несколько встречающихся вариантов.

Количество упаковок

Если нужны упаковки, складываются значения «Количество упаковок». Размер упаковки может участвовать только в отборе. Например, в EGE-03-0089 ↗ речь о шоколаде в упаковках по 100 г, но ответ нужен в упаковках. Подходят артикулы 30, 32 и 33; у шоколада с изюмом, артикул 31, упаковка 80 г. После отбора получается 10 511 упаковок. Умножение на 100 изменило бы единицу ответа.

Масса или объём нескольких товаров

Если размеры упаковок различаются, количество каждого товара умножается на его размер упаковки. Затем результаты складываются и переводятся в нужную единицу.

В EGE-03-0011 ↗ продаются три вида шампуня. После отбора магазинов на проспекте Мира, продаж и дат с 7 по 22 сентября получаются такие суммы по артикулам:

Эти суммы можно получить по очереди, оставляя в фильтре один артикул и сохраняя остальные условия. Общий объём: (438900 + 427750 + 551950) / 1000 = 1418{,}6 л. В условии требуется целая часть, поэтому ответ 1418.

Выручка или стоимость поставки

Для каждой подходящей операции количество упаковок умножается на цену упаковки. Произведения складываются. Для выручки отбираются продажи, для стоимости поступившего товара — поступления.

Цена может находиться в «Товаре» или в «Движении товаров» — это видно по заголовкам конкретного файла. Если цена дана для операции, используется цена этой строки. При разных ценах нельзя умножать общее количество на цену одного произвольно выбранного товара.

Например, в EGE-03-0050 ↗ у шести видов карамели разные цены. Для карамели «Барбарис» отобрано 2017 проданных упаковок по 60 рублей: 2017 \cdot 60 = 121020 рублей. Аналогичный расчёт нужен для остальных пяти видов; общая выручка составляет 1 958 420 рублей. Пример расчёта стоимости поступлений — EGE-03-0003 ↗.

Изменение остатка

Фраза «на сколько увеличилось количество» требует разности: поступило минус продано за один и тот же период, для тех же товаров и магазинов. В EGE-03-0035 ↗ выбраны макаронные изделия производителя «Макаронная фабрика» и Октябрьский район. Подходят артикулы 24–27. Поступило 3540 упаковок, продано 3180, поэтому увеличение равно 3540 - 3180 = 360 упаковок.

Для такого расчёта сумма определяется дважды: с фильтром «Поступление», затем с фильтром «Продажа». Первую сумму нужно сохранить, поскольку формула промежуточных итогов пересчитается при смене фильтра. Начальный запас для разности не нужен. Чтобы узнать запас на конец периода, потребовалось бы прибавить эту разность к начальному запасу.

Возможные ловушки

Потеря части товаров или магазинов. «Все виды» могут означать несколько артикулов, а одна улица — несколько магазинов. В EGE-03-0007 ↗ нужны пять видов сметаны и оба магазина на Некрасовском бульваре: M9 и M17. Выбор одной строки справочника уменьшит сумму. Сначала определяется весь подходящий список кодов, затем он переносится в фильтр операций.

Слишком широкий поиск по названию. В EGE-03-0008 ↗ нужны питьевые йогурты с черникой, малиной или клубникой жирностью 1,5%. Поиск только слова «йогурт» захватит другие товары. Здесь одновременно важны вид продукта и жирность, а вкусы являются альтернативами. Подходят артикулы 8, 9 и 10.

Число строк принято за количество товара. В основном примере ↗ пять операций содержат 890 упаковок. Подсчёт строк ответит на другой вопрос. Для количества товара нужна сумма соответствующего столбца; для массы — ещё умножение на массу упаковки.

Фильтр установлен, но сумма включает скрытые строки. В том же примере СУММ по всему столбцу F продолжит учитывать неподходящие операции. Для суммы после фильтрации подходит ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;F2:F2273). Условия задаются фильтром, а не ручным скрытием строк: режим 9 учитывает строки, скрытые вручную.

Поступление спутано с изменением остатка. В примере с макаронами ↗ число 3540 описывает поступившие упаковки. Оно не учитывает 3180 проданных. Искомое увеличение — 360. Оба итога считаются с одинаковыми условиями по товару, месту и датам.

Целая часть заменена округлением. В EGE-03-0167 ↗ получается 3185,5 кг карамели. Целая часть — 3185, а округление дало бы 3186. Дробная часть отбрасывается только у окончательной суммы: усечение каждого промежуточного результата может потерять целые килограммы. Это несколько возможных ошибок, а не исчерпывающий список.