Разбор задания 3 ЕГЭ

ЕГЭ по информатике задание 3

База данных (БД)  – это организованное хранилище данных, позволяющее эффективно их сохранять, управлять и извлекать. Основные признаки БД: структурированность, связность и доступность для обработки.

Система управления базами данных (СУБД)  – программное обеспечение, используемое для создания, управления и работы с БД.

Модели данных  описывают способ организации информации. Наиболее распространённая –  реляционная модель , где данные хранятся в таблицах, связанных между собой.

Таблица БД   состоит из  полей  (столбцов) и  записей  (строк). Поле определяет атрибут данных с указанным  типом поля  (число, текст, дата и др.), а запись хранит конкретные значения.

Ключевое поле  – уникальный идентификатор записи.  Первичный ключ  однозначно определяет строку в таблице, а  внешний ключ  связывает записи разных таблиц, указывая на первичный ключ другой таблицы.

Связи между таблицами в реляционной модели бывают разных типов

Один к одному (1:1) : В этой связи каждая запись в одной таблице может быть связана только с одной записью в другой таблице, и наоборот. Пример: таблица с информацией о сотрудниках и таблица с их паспортными данными. Каждый сотрудник имеет только один паспорт, и каждому паспорту соответствует только один сотрудник.

Один ко многим (1:N) : Одна запись в первой таблице может быть связана с несколькими записями во второй таблице, но каждая запись во второй таблице может быть связана только с одной записью в первой таблице. Пример: таблица клиентов и таблица заказов. Один клиент может сделать много заказов, но каждый заказ связан только с одним клиентом.

Многие ко многим (M:N) : Записи в обеих таблицах могут быть связаны с несколькими записями в другой таблице. Чтобы реализовать такую связь, часто используется промежуточная таблица, которая хранит соответствие между записями обеих таблиц. Пример: таблица студентов и таблица курсов. Каждый студент может посещать несколько курсов, и каждый курс может включать несколько студентов.

Эти связи позволяют организовать данные в БД таким образом, чтобы минимизировать дублирование информации и обеспечивать её целостность.

Задание №3  из ЕГЭ по информатике проверяет умение работать с реляционными базами данных в Excel. В этом задании требуется находить и анализировать информацию с помощью встроенных функций и формул. Оно относится к  базовому уровню сложности

Предоставлен фрагмент базы данных, содержащей сведения о поставках и продажах кондитерских изделий (конфет и печенья) в магазины города за июнь 2023 года.  База данных  представлена в формате Excel и состоит из  трёх таблиц :

1. Таблица «Движение товаров».  Эта таблица содержит данные о поступлении товаров со склада в магазины и о продажах товаров. Структура таблицы:

• ID операции  (первичный ключ) – уникальный идентификатор операции;
• Дата  – дата операции;
• ID магазина  (внешний ключ) – это поле, которое связывает операцию с конкретным магазином. Значение в этом поле соответствует уникальному номеру магазина (первичному ключу) из таблицы «Магазин»;
• Артикул  (внешний ключ) – это поле, которое связывает операцию с конкретным товаром. Значение в этом поле соответствует уникальному номеру товара (первичному ключу) из таблицы «Товар»;
• Количество упаковок, шт.  – количество упаковок товара, поступивших в магазин или проданных;
• Тип операции  – тип операции (поступление или продажа).

2. Таблица «Товар».  Эта таблица содержит данные о свойствах и характеристиках каждого товара. Структура таблицы:

• Артикул  – уникальный код, идентифицирующий товар;
• Отдел  – категория, к которой относится товар (например, конфеты, печенье);
• Наименование товара  – полное название товара (например, «Зефир в шоколаде»);
• Ед_изм  – единица измерения товара (например, штуки или граммы);
• Количество в упаковке  – число единиц товара или масса в одной упаковке;
• Цена за упаковку  – стоимость одной упаковки товара.

3. Таблица «Магазин».  Она содержит данные о местоположении магазинов. Структура таблицы:

ID магазина  – уникальный номер, идентифицирующий каждый магазин;

Район  – район города, где находится магазин;

Адрес  – точный адрес магазина.

На рисунке показана схема базы данных, включающая три таблицы и связи между ними. Связи реализованы по принципу «один ко многим». Это означает, что одна запись в таблице A может быть связана с несколькими записями в таблице B, тогда как каждая запись в таблице B связана только с одной записью в таблице A.

Задание:  Используя данные из базы в Excel, определите общую массу (в кг) всех видов зефира, поступившего в магазины, расположенные на проспекте Мира, за период с 5 по 19 июня 2023 года включительно. В ответе укажите только число (массу в килограммах).

Функция ВПР  (или VLOOKUP) – важный инструмент электронных таблиц, который помогает находить данные в одном столбце и извлекать связанные значения из другого. Она особенно полезна при анализе баз данных, объединении таблиц и автоматизации работы с большими наборами информации.

Что делает ВПР?  ВПР (от англ. Vertical Lookup – вертикальный поиск):

• Находит заданное значение в  первом столбце  диапазона данных.

• Возвращает соответствующее значение из  другого столбца той же строки .

Функция работает только по вертикали (строкам), что и отражено в её названии.

Синтаксис функции ВПР:

ВПР(Искомое_значение; Диапазон_таблицы; Номер_столбца; [Тип_совпадения])

Пошаговое решение задачи

1. Сбор данных в одну таблицу.  На первом этапе нужно объединить данные из разных таблиц в одну, необходимых для решения задачи. В рамках данной задачи – это оптимально сделать это на основе таблицы «Движение товаров». Чтобы добавить дополнительные столбцы используем функцию ВПР (или XLOOKUP в более новых версиях Excel). В результате таблица «Движение товаров» должна дополниться следующими столбцами:

• Адрес магазина – данные из таблицы «Магазин»;
• Наименование товара – данные из таблицы «Товар»;
• Единица измерения (Ед_изм) – добавляется для наглядности, поскольку все интересующие нас товары (все виды зефира) измеряются в граммах;
• Количество в упаковке – масса одной упаковки товара (в граммах) или количество единиц в упаковке (данные из таблицы «Товар»).

Адрес магазина.  В столбец справа от данных (G1) таблицы введём заголовок: Адрес магазина. В ячейку G2 введём следующую формулу для подстановки адреса магазина:

= ВПР(C2; Магазин!A:C; 3; ЛОЖЬ)

Объяснение формулы:

C2 – ID магазина из текущей строки таблицы «Движение товаров».

Магазин!A:C – диапазон данных таблицы «Магазин», содержащий столбцы ID магазина, Район и Адрес.

3 – номер столбца в указанном диапазоне, в котором находятся адреса магазинов.

ЛОЖЬ – точное совпадение значения ID магазина.

Затем растяните формулу на весь столбец, выделив ячейку G2, наведя курсор на её правый нижний угол и двойным щелчком мыши.

После выполнения этих шагов столбец «Адрес магазина» будет автоматически заполнен для всех строк таблицы.

Наименование товара.  Выполним аналогичные действия, а именно в H1 – Наименование товара, в H2 – формулу =ВПР(D2; Товар!A:F; 3; ЛОЖЬ). Растянем формулу аналогичным образом.

Добавим оставшиеся два столбца:

в ячейку I1 – Единица измерения, в I2 – формулу =ВПР(D2; Товар!A:F; 4; ЛОЖЬ), растянем формулу вниз до конца таблицы;

в ячейку J1 – Количество в упаковке, а J2 – формулу =ВПР(D2; Товар!A:F; 5; ЛОЖЬ), применем формулу ко всему столбцу.

2. Расчёт массы упаковок для каждой операции.  Для каждой записи таблицы рассчитаем массу зефира:

Если единица измерения указана в граммах, то умножим количество упаковок на массу одной упаковки и переведём результат в килограммы (разделив на 1000).

Если товар измеряется в штуках или иной единице, результат равен – 0.

Шаги выполнения:

В K1 введём заголовок: Масса.

В K2 – формулу: =ЕСЛИ(I2 = "ГРАММ"; J2 / 1000 * E2; 0).

Распространим формулу на весь столбец K.

Формула =ЕСЛИ(I2 = "ГРАММ"; J2 / 1000 * E2; 0). выполняет расчёт массы следующим образом:

• Проверка условия. I2 = "ГРАММ" – проверяет, указана ли единица измерения как «граммы».
• Если условие истинно (единица измерения – граммы), то J2 / 1000 * E2 – переводит массу одной упаковки из граммов в килограммы и умножает результат на количество упаковок, чтобы найти общую массу.
• Если условие ложно (единица измерения не граммы), то возвращается 0 (так как расчёт массы для других единиц измерения не требуется).

После выполнения этих шагов столбец Масса будет содержать рассчитанную массу зефира (в килограммах) для каждой записи.

3. Фильтрация данных.  После выполнения расчётов отфильтруем таблицу согласно условиям задания:

• Дата – выберем записи с 5 по 19 июня 2023 года.
• Тип операции – оставим только строки со значением «Поступление».
• Адрес магазина – выберем магазины, расположенные на проспекте Мира.
• Наименование товара – отфильтруем все виды зефира.

Для включения фильтра выделим заголовки таблицы (диапазон A1:K1) и кликнем на пункт  Фильтр , расположенный в меню  Главная  →  Сортировка и фильтр .

Настроим фильтры через стрелочки в заголовках столбцов и начнём со столбца «Дата». Для этого отметим галочками только даты с 5 по 19 июня 2023 года.

Фильтр по столбцу «Тип операции»: оставим только «Поступление».

Фильтр по столбцу «Адрес магазина»: выберем адреса на проспекте Мира.

Фильтр по столбцу «Наименование товара»: отметим все виды зефира.

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

4. Подсчёт общей массы.  После применения фильтров для таблицы найдём сумму всех значений в столбце K:

Выделим столбец K, кликнув по его заголовку левой кнопкой мыши.

В строке состояния Excel автоматически отобразится сумма значений.