Реляционные базы данных
Реляционная база данных хранит сведения в нескольких таблицах, которые связаны через ключевые поля. В задании 3 нужные записи сначала ищут в справочных таблицах, затем отбирают фильтром в главной таблице по их ключам, датам и типу операции. Итог считают по отобранным строкам: сумму, массу через количество в упаковке или разность поступлений и продаж.
Как искать данные в связанных таблицах
Таблица состоит из записей (строк) и полей (столбцов). Ключ таблицы однозначно определяет запись, а ключ другой таблицы в поле записи связывает две таблицы.
- Прочитайте вопрос и выпишите условия: что считать, за какие даты, для каких объектов, какой тип операции.
- В справочных таблицах (магазины, товары) найдите записи, которые подходят под условия, и выпишите их ключи.
- В главной таблице включите автофильтр и оставьте строки с этими ключами, нужными датами и типом операции.
- Недостающие сведения о товаре перенесите в главную таблицу функцией
ВПРпо ключу. - Посчитайте итог по видимым строкам:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; …)в Excel,ИТОГ(9; …)в LibreOffice Calc.
Таблица, запись и поле
Реляционная база данных — набор таблиц, каждая из которых хранит сведения об однотипных объектах: магазинах, товарах, операциях. Запись — строка таблицы, сведения об одном объекте. Поле — столбец таблицы, одно свойство всех объектов. У поля есть имя (заголовок столбца) и тип: число, текст, дата.
В задании 3 база дана файлом электронной таблицы: каждая таблица базы лежит на своём листе, первая строка листа — заголовки полей. Открыть его можно в Excel или в LibreOffice Calc.
Пример
Таблица «Магазин» имеет поля «ID магазина», «Район», «Адрес». В ней записи: M1 — Центральный, просп. Мира, 45; M2 — Заречный, ул. Лесная, 3; M3 — Центральный, ул. Садовая, 8. Сколько в таблице записей и полей? Какие магазины стоят в Центральном районе?
- 1.Каждый магазин — одна запись: записей 3.
- 2.Каждое свойство — одно поле: «ID магазина», «Район», «Адрес», полей 3.
- 3.В поле «Район» значение «Центральный» стоит в записях M1 и M3.
Ответ: 3 записи, 3 поля; магазины M1 и M3
Ключ таблицы
Ключ таблицы (первичный ключ) — поле, значение которого не повторяется ни в одной записи и поэтому однозначно указывает на запись. Обычно это код: «ID магазина», «Артикул». Название ключом быть не может, если два объекта могут называться одинаково.
Ключ нужен, чтобы другие таблицы ссылались на запись коротким кодом, а не повторяли все её сведения.
Пример
Таблица «Товар» имеет поля «Артикул», «Отдел», «Наименование товара», «Количество в упаковке». Какое поле — ключ таблицы?
- 1.В одном отделе много товаров: значение «Отдел» повторяется.
- 2.Одинаковое количество в упаковке бывает у разных товаров.
- 3.Артикул у каждого товара свой и в таблице не повторяется.
Ответ: «Артикул»
Внешний ключ и связь таблиц
Внешний ключ — поле одной таблицы, значения которого — ключи записей другой таблицы. В таблице «Движение товаров» поле «ID магазина» хранит код из таблицы «Магазин», а поле «Артикул» — код из таблицы «Товар». По этому коду находят все сведения о магазине или товаре. Целостность базы данных означает, что каждое значение внешнего ключа есть среди ключей связанной таблицы.
Один магазин встречается во многих операциях, а у каждой операции один магазин: это связь один-ко-многим. Связь один-к-одному соединяет одну запись с одной записью. Связь многие-ко-многим (одна ткань идёт на много моделей, одна модель шьётся из многих тканей) задают через третью таблицу, в которой есть оба внешних ключа.
Пример
Таблица «Магазин»: M1 — Центральный, просп. Мира, 45; M2 — Заречный, ул. Лесная, 3. Таблица «Движение товаров»: операция 1 — магазин M2, артикул 5, 40 упаковок, «Поступление»; операция 2 — магазин M1, артикул 5, 12 упаковок, «Продажа». В каком районе получили товар в операции 1?
- 1.В записи операции 1 поле «ID магазина» равно M2: это внешний ключ.
- 2.Ищем M2 в ключевом поле таблицы «Магазин»: Заречный район, ул. Лесная, 3.
Ответ: Заречный
Отбор записей фильтром
Фильтр скрывает строки таблицы, которые не подходят под условие, и оставляет видимыми нужные. Включите его: «Данные» → «Фильтр» в Excel, «Данные» → «Автофильтр» в LibreOffice Calc (в обеих программах Ctrl+Shift+L). В заголовке каждого столбца появится стрелка со списком значений: отметьте нужные. Условия в разных столбцах действуют вместе, как логическое И. Несколько отмеченных значений одного столбца — это ИЛИ.
Условие «адрес содержит слово», «дата между» задают в «Текстовых фильтрах» и «Фильтрах по дате» в Excel, в «Стандартном фильтре» в LibreOffice. Слово «включительно» значит, что обе границы входят в период. Тот же список под стрелкой сортирует столбец по возрастанию или убыванию.
Пример
Операции магазина M1 в июне: 3-го — поступление 20 упаковок; 4-го — продажа 5; 6-го — поступление 30; 9-го — поступление 10; 10-го — продажа 8. Сколько упаковок поступило с 4 по 9 июня включительно?
- 1.Фильтр по типу операции: «Поступление». Остаются операции 3, 6 и 9 июня.
- 2.Фильтр по дате с 4 по 9 июня включительно: 3 июня не входит, 6 и 9 июня входят.
- 3.Складываем видимые строки: .
Ответ: 40
Сведения из связанной таблицы: функция ВПР
Если условие или расчёт требует поля другой таблицы, перенесите это поле в главную таблицу. Функция ВПР(Критерий; Массив; Индекс; 0) ищет значение критерия в первом столбце массива и возвращает значение из столбца с номером «Индекс» той же строки. Последний аргумент 0 требует точного совпадения. Массив закрепляют знаками доллара, чтобы он не сдвигался при копировании: абсолютные ссылки.
Ссылку на другой лист программа вставит сама, если выделить диапазон мышью. Затем клавиша F4 добавит знаки доллара: Товар!$A$2:$F$61 в Excel, $Товар.$A$2:$F$61 в LibreOffice. Столбец, где значение считают формулой по другим полям, называют вычисляемым полем: масса партии =E2*G2 — упаковки, умноженные на граммы в упаковке.
Пример
На листе «Товар» в столбцах A–F лежат поля «Артикул», «Отдел», «Наименование товара», «Ед_изм», «Количество в упаковке», «Цена за упаковку»; у артикула 5 количество в упаковке 800 г. В строке 2 листа «Движение товаров» артикул 5 (ячейка D2) и 15 упаковок (E2). Найдите массу партии.
- 1.«Количество в упаковке» — пятый столбец массива, индекс 5.
- 2.В G2 пишем
=ВПР(D2;Товар!$A$2:$F$61;5;0): формула находит артикул 5 и возвращает 800. - 3.В H2 пишем
=E2*G2: г. Формулы копируют вниз на все строки. - 4. г — это 12 кг.
Ответ: 12 кг
Итог по отобранным записям
Когда фильтр оставил нужные строки, сложите числа вычисляемого столбца только в видимых строках. Функция СУММ складывает весь диапазон, вместе со строками, которые фильтр скрыл. Сумму видимых строк даёт ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;H2:H6481) в Excel и ИТОГ(9;H2:H6481) в LibreOffice Calc: код 9 означает сумму. Если ответ просят в килограммах, а масса в граммах, разделите итог на 1000.
Пример задания
Открытый банк ФИПИ
В файле приведён фрагмент базы данных «Кондитерские изделия» о поставках конфет и печенья в магазины районов города. База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поступлении товаров со склада в магазины в течение июня 2023 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид:
| ID операции | Дата | ID магазина | Артикул | Количество упаковок, шт. | Тип операции |
|---|
Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид:
| Артикул | Отдел | Наименование товара | Ед. изм | Количество в упаковке | Цена за упаковку |
|---|
Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид:
| ID магазина | Район | Адрес |
|---|
На рисунке приведена схема указанной базы данных:

Используя информацию из приведённой базы данных, определите общую массу (в кг) всех видов зефира, полученных магазинами, расположенными на проспекте Мира, за период с 5 по 19 июня включительно. В ответе запишите только число.
Изменение количества: поступления минус продажи
Вопрос «на сколько увеличилось количество товара в магазинах» спрашивает разность: сколько поступило минус сколько продали за период. Отберите строки товара и магазинов фильтром, затем найдите сумму видимых строк дважды: с типом «Поступление» и с типом «Продажа». Можно и одной формулой: вычисляемый столбец =ЕСЛИ(F2="Поступление";E2;-E2), затем сумма видимых строк. Если остаток на начало периода не дан, он не нужен: разность от него не зависит.
Пример задания
Открытый банк ФИПИ
В файле приведён фрагмент базы данных «Продукты» о поставках товаров в магазины районов города. База данных состоит из трёх таблиц.
Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой декады июня 2021 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. занесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид.
| ID операции | Дата | ID магазина | Артикул | Тип операции | Количество упаковок, шт. | Цена руб./шт. |
|---|
Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид.
| Артикул | Отдел | Наименование товара | Ед. изм. | Количество в упаковке | Поставщик |
|---|
Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид.
| ID магазина | Район | Адрес |
|---|
На рисунке приведена схема указанной базы данных.

Используя информацию из приведённой базы данных, определите на сколько увеличилось количество упаковок яиц диетических, имеющихся в наличии в магазинах Заречного района за период с 1 по 10 июня.
В ответе запишите только число.