Задание 3 · Реляционные базы данных
Практика и итог
Вся тема на одной странице: как решилась задача темы, главное из подтем, порядок решения и частые ошибки.
Одна задача — вся тема
Задача темы — масса товаров фабрики А, проданных в Северном районе с 1 по 10 июня, — прошла через все подтемы, и каждая добавляла к решению новый шаг. Пролистай черновик: точками отмечены строки, которых не было после предыдущей подтемы.
- Подтема 1, «База из трёх листов»: три листа, связь по кодам. Точками отмечены новые строки.
- Подтема 2, «ВПР: связь листов»: к операции — район, поставщик, кг.
- Подтема 3, «Отбор записей»: фильтр по всем требованиям.
- Подтема 4, «Расчёт ответа»: масса строки, сумма видимых. Ответ задачи темы — 14,5 кг.
Задача не менялась — менялся лист: сначала три справочника, потом сведения к каждой операции, отбор фильтром и, наконец, сумма видимых строк. Так решается любое задание 3.
Карта темы
Каждая подтема дала один инструмент, и любое решение собирается из них. Сверху — путь от файла к ответу, ниже — по блоку на подтему. Заголовок блока ведёт на подтему.
- Файлдвижение и два справочника
- Отборстроки, подходящие под вопрос
- Ответсумма нужной величины
1База из трёх листов
- строкаодна операция, один магазин или один товар
- ID, артикулкоды для связи, а не количества
- связьпо совпадению кода в двух листах
2ВПР: связь листов
=ВПР(код;N-й столбец строки справочника с этим кодомсправочник; N; 0) - последний 0точное совпадение; без него поиск приблизительный
$A$2:$C$17справочник закреплён и не сползает
3Отбор записей: фильтр
- требованиесвой столбец и своё значение
- «включительно»обе граничные даты входят
- «все виды»поиск по части названия
СУММскладывает и скрытые строки
4Расчёт ответа
- массаупаковки × кг в упаковке
- стоимостьупаковки × цена строки
- изменение запасапоступления минус продажи
- «какой магазин»сначала итог каждого, потом сравнение
Как решать
Любое задание 3 решается одним порядком из пяти шагов. Каждому шагу ты уже учился — под ним номера подтем:
Оценка — грубая прикидка до ответа: в задаче темы 22 упаковки по 0,5–1 кг весят от 11 до 22 кг, поэтому 14,5 правдоподобно, а 1450 — нет.
Частые ошибки
Восемь ошибок встречаются чаще других, и каждую ловит один вопрос к себе. Выбери ошибку — увидишь, как её исправить, и вопрос, который её ловит:
- ВПР без 0
=ВПР(C2;→…; 2) =ВПР(C2;. Спроси себя: поиск точный? Проверь одну строку вручную.подтема 2…; 2; 0) - диапазон без $
'Магазин'!A2:C4→'Магазин'!$A$2:$C$4. Спроси себя: что стоит в формуле последней строки?подтема 2- граница периода
- даты «с 1 по 10» взяты без 10-го → «включительно» — обе даты входят. Спроси себя: есть ли в отборе операции первого и последнего дня?подтема 3
- все виды
- отобран один вид товара → поиск по части названия. Спроси себя: все ли названия с этим словом попали в список?подтема 3
- СУММ после фильтра
=СУММ(F2:F13)→ сумма видимых в строке состояния. Спроси себя: не сложились ли скрытые строки?подтема 3- упаковки вместо кг
- сложены упаковки → упаковки × кг в упаковке. Спроси себя: в каких единицах спрашивают ответ?подтема 4
- только продажи
- для запаса оставлены только продажи → поступления со знаком плюс, продажи — минус. Спроси себя: не выбран ли тип операции в фильтре?подтема 4
- самая большая запись
- «какой магазин больше» — по самой большой продаже → сначала итог каждого магазина. Спроси себя: я сравниваю записи или суммы магазинов?подтема 4
Итог
Тема пройдена: задача темы решена от трёх листов до ответа 14,5 кг. Вот что взять с собой.
- ПорядокВопрос, ВПР, фильтр, расчёт, оценка.
- ВПРКод, закреплённый справочник, номер столбца, 0.
- ФильтрВсе требования сразу, обе граничные даты, все виды.
- РасчётВеличина в строке, затем сумма видимых, а не СУММ.
- Задача темыПять продаж, 22 упаковки, 14,5 кг.
Проверьте себя
Ответь сначала сам, потом открой ответ.
В листе «Движение» нет района, а в вопросе он есть. Как его получить?
ОтветСкрыть ответ
Дописать к каждой операции через ВПР по ID магазина: район лежит в справочнике «Магазин».
После фильтра выделили столбец и написали =СУММ(F2:F13), получилось 86. Почему не ответ?
ОтветСкрыть ответ
СУММ складывает и скрытые строки. Сумму видимых показывает строка состояния — или СУММЕСЛИМН с теми же условиями.
Спрашивают, на сколько изменился запас. Какие операции оставить в фильтре?
ОтветСкрыть ответ
Обе: поступления со знаком плюс, продажи со знаком минус. Начальный запас не нужен.
«Какой магазин района продал больше всего?» Что сравнивать?
ОтветСкрыть ответ
Итоги магазинов: сначала сложить подходящие записи каждого магазина, потом выбрать наибольшую сумму.
Практика
Для проверки ответов нужен JavaScript. Условия, подсказки и решения доступны ниже.
Стоимость ультрапастеризованного молока
Определи общую стоимость (в руб.) упаковок ультрапастеризованного молока всех видов, полученных магазинами Первомайского района за период с 3 по 24 августа включительно. Файл тот же — 3-3.xlsx.
Введите число.
Подсказка
Стоимость строки — упаковки × цена поступления этой строки.
Решение
Допиши наименование и район через ВПР; стоимость строки — упаковки × цена. Фильтры: наименование — поиск «ультрапастеризованное», район — «Первомайский», тип — «Поступление», дата — с 03.08 по 24.08. Сумма видимых стоимостей — 27 328 рублей.
Рядом с операциями — характеристики через ВПР:H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районI2: =ВПР(C2;'Магазин'!$A:$C;3;0) — адресJ2: =ВПР(D2;'Товар'!$A:$F;3;0) — наименованиеK2: =ВПР(D2;'Товар'!$A:$F;5;0) — в упаковкеL2: =F2*G2 — стоимость строкиОтвет: 27328
Изменение запаса муки
На сколько упаковок изменился запас всех видов муки в магазинах Заречного района за период с 3 по 9 июня включительно? В ответе запиши число, равное изменению запаса: росту соответствуют положительные числа, уменьшению — отрицательные. Файл тот же — 3-4.xlsx.
Введите число.
Подсказка
Два вида муки — поиск «мука»; уменьшение запаса — отрицательное число.
Решение
Допиши наименование и район, количество со знаком — в отдельном столбце. Фильтры: наименование — поиск «мука» (пшеничная и ржаная), район — «Заречный», дата — с 03.06 по 09.06; тип не фильтруют. Сумма видимых −26: продано больше, чем поступило.
Рядом с операциями — характеристики через ВПР:H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районI2: =ВПР(C2;'Магазин'!$A:$C;3;0) — адресJ2: =ВПР(D2;'Товар'!$A:$F;3;0) — наименованиеK2: =ВПР(D2;'Товар'!$A:$F;5;0) — в упаковкеL2: =ЕСЛИ(E2="Поступление";F2;-F2)Ответ: -26
Магазин с наибольшей выручкой
В файле 3-5.xlsx три листа: «Движение товаров», «Товар» и «Магазин». Какой магазин Центрального района получил наибольшую выручку от продажи товаров отдела «Молоко» за период с 10 по 20 июня включительно? В ответе запиши ID магазина так, как он указан в базе.
Введите ID магазина так, как он указан в базе.
Подсказка
Сравни суммы выручки магазинов района.
Решение
Допиши отдел и район, стоимость строки — упаковки × цена. Фильтры: отдел — «Молоко», район — «Центральный», тип — «Продажа», дата — с 10.06 по 20.06. Затем оставляй в фильтре по одному магазину района (M5–M8) и сравни суммы в строке состояния — или построй сводную таблицу. Наибольшая выручка — у M6.
Рядом с операциями — характеристики через ВПР:H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районI2: =ВПР(C2;'Магазин'!$A:$C;3;0) — адресJ2: =ВПР(D2;'Товар'!$A:$F;3;0) — наименованиеK2: =ВПР(D2;'Товар'!$A:$F;5;0) — в упаковкеL2: =ВПР(D2;'Товар'!$A:$F;2;0) — отделM2: =F2*G2 — выручка строкиОтвет: M6
День наибольшей выручки «Бакалеи»
В какой день июня в магазинах Первомайского района выручка от продажи товаров отдела «Бакалея» была наибольшей? В ответе запиши целое число от 1 до 30 — число искомой даты. Файл тот же — 3-5.xlsx.
Введите число.
Подсказка
Итог выручки каждого дня удобно получить сводной таблицей.
Решение
Допиши отдел и район, стоимость строки — упаковки × цена. Фильтры: отдел — «Бакалея», район — «Первомайский», тип — «Продажа». Сводная таблица с датами в строках и суммой стоимости в значениях показывает наибольший итог 9 июня.
L2: =ВПР(D2;'Товар'!$A:$F;2;0) — отделH2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районM2: =F2*G2 — выручка строкиОтвет: 9
Масса пряников на Луговой улице
По файлу 3-7.xlsx определи общую массу (в кг) всех видов пряников, проданных магазинами, расположенными на Луговой улице, за период с 8 по 21 сентября включительно. В ответе запиши только целую часть числа.
Введите целое число.
Подсказка
Масса строки — упаковки × количество в упаковке; целая часть.
Решение
Допиши наименование, адрес и количество в упаковке; масса строки — упаковки × количество в упаковке. Фильтры: наименование — поиск «пряник», адрес — поиск «Луговая», тип — «Продажа», дата — с 08.09 по 21.09. Сумма видимых масс — 90,5 кг, в ответе целая часть.
Рядом с операциями — характеристики через ВПР:H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районI2: =ВПР(C2;'Магазин'!$A:$C;3;0) — адресJ2: =ВПР(D2;'Товар'!$A:$F;3;0) — наименованиеK2: =ВПР(D2;'Товар'!$A:$F;5;0) — в упаковкеL2: =F2*K2Ответ: 90
Выручка от сметаны
По тому же файлу 3-7.xlsx определи, на какую сумму (в руб.) было продано сметаны всех видов в магазинах Заречного района за период с 10 по 25 сентября включительно.
Введите число.
Подсказка
Выручка строки — упаковки × цена этой строки.
Решение
Допиши наименование и район, стоимость строки — упаковки × цена. Фильтры: наименование — поиск «сметана», район — «Заречный», тип — «Продажа», дата — с 10.09 по 25.09. Сумма видимых — 15 536 рублей.
Рядом с операциями — характеристики через ВПР:H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районI2: =ВПР(C2;'Магазин'!$A:$C;3;0) — адресJ2: =ВПР(D2;'Товар'!$A:$F;3;0) — наименованиеK2: =ВПР(D2;'Товар'!$A:$F;5;0) — в упаковкеL2: =F2*G2Ответ: 15536
Район с наибольшей поставкой
По файлу 3-8.xlsx определи, в магазины какого района Молокозавод № 2 поставил с 5 по 18 октября включительно товаров на наибольшую сумму. В ответе запиши число — найденное значение наибольшей суммы в рублях.
Введите число.
Подсказка
Сравни суммы поставок четырёх районов.
Решение
Допиши поставщика и район, стоимость строки — упаковки × цена. Фильтры: поставщик — «Молокозавод № 2», тип — «Поступление», дата — с 05.10 по 18.10. Сравни суммы четырёх районов — по очереди фильтром района или сводной таблицей. Наибольшая — 97 746 рублей.
H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районL2: =ВПР(D2;'Товар'!$A:$F;6;0) — поставщикM2: =F2*G2 — стоимость строкиОтвет: 97746
Литры молока на проспекте Мира
По тому же файлу 3-8.xlsx определи, сколько литров молока всех видов получили магазины, расположенные на проспекте Мира, за период с 1 по 20 октября включительно. В ответе запиши только целую часть числа.
Введите целое число.
Подсказка
Ищи в адресе «пр. Мира»: поиск «Мир» захватит и ул. Мирная.
Решение
Допиши наименование, адрес и количество в упаковке; объём строки — упаковки × количество в упаковке. Фильтры: наименование — поиск «Молоко» (четыре вида), адрес — поиск «пр. Мира» (поиск «Мир» добавил бы магазины ул. Мирная), тип — «Поступление», дата — с 01.10 по 20.10. Сумма видимых — 733,6 л, в ответе целая часть.
Рядом с операциями — характеристики через ВПР:H2: =ВПР(C2;'Магазин'!$A:$C;2;0) — районI2: =ВПР(C2;'Магазин'!$A:$C;3;0) — адресJ2: =ВПР(D2;'Товар'!$A:$F;3;0) — наименованиеK2: =ВПР(D2;'Товар'!$A:$F;5;0) — в упаковкеL2: =F2*K2 — литры строкиОтвет: 733