В этой статье разберём, как работать с базами данных в электронных таблицах и решать задачи с помощью функции ВПР. На примерах из задания № 3 ЕГЭ по информатике научимся находить нужные данные, связывать таблицы и делать расчёты по условию задачи.
Термины, с которыми будем работать: база данных, ВПР.
Что такое база данных
База данных — это способ хранения и обработки информации в удобном и структурированном виде. В задании № 3 ЕГЭ база данных представлена в виде электронной таблицы с несколькими листами. Каждый лист содержит свою информацию, а специальные ключи связывают таблицы между собой.
Вот так выглядит описание такой таблицы:
В файле приведён фрагмент базы данных «Продукты» о поставках товаров в магазины районов города. База данных состоит из трёх таблиц. Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой декады июня 2021 г., а также информацию о проданных товарах. Поле «Тип операции» содержит значение «Поступление» или «Продажа», а в соответствующее поле «Количество упаковок» внесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Заголовок таблицы имеет следующий вид:
| ID операции | Дата | ID магазина | Артикул | Тип операции | Количество упаковок, шт. | Цена |
|---|
Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Заголовок таблицы имеет следующий вид:
| Артикул | Отдел | Наименование | Ед. изм. | Количество в упаковке | Поставщик |
|---|
Таблица «Магазин» содержит информацию о местонахождении магазинов. Заголовок таблицы имеет следующий вид:
| ID магазина | Район | Адрес |
|---|
На рисунке приведена схема указанной базы данных:
Обрати внимание, что листы «Магазин» и «Товар» связаны с листом «Движение товаров» ключами «ID магазина» и «Артикул» соответственно. Это значит, что, зная артикул, на листе «Движение товаров» мы можем получить (по этому артикулу) любую информацию, связанную с ним с листа «Товар». Например, у товара есть параметры: цена и вес — они станут нам доступны, если мы знаем артикул.
Формула ВПР
=ВПР(искомое значение; в какой таблице ищем; из какого столбца вернуть значение; всегда 0)
Например, нужно найти наименование товара, зная только его артикул. В этом случае артикул будет искомым значением в формуле ВПР. Затем нужно определить, на каком листе находится информация о товарах, и выделить таблицу целиком. После этого посчитай, каким по счёту идёт столбец с нужной информацией, и укажи этот номер третьим аргументом формулы. Четвёртый аргумент всегда ставь 0 — он отвечает за точный поиск.
Чтобы узнать, какой товар соответствует артикулу 4, воспользуемся формулой =ВПР:
В качестве искомого значения указываем артикул 4. Искать информацию будем на листе «Товар» в диапазоне столбцов A:F:
Наименование товара находится в 3-м столбце таблицы, а последний аргумент формулы — 0, так как нам нужен точный поиск.
Формула показывает, что товар с артикулом 4 — «Кефир 3,2%». Проверим это в таблице:
Важно: искомое значение для =ВПР должно быть в первом столбце той таблицы, в которой происходит поиск.
Как видно из примера выше, на листе «Товар» в первом столбце стоят артикулы, именно поэтому поиск по артикулу был успешным.
Практикум
Задание 1
Используя информацию из приведённой базы данных, определите, на сколько увеличилось количество упаковок всех видов макарон производителя «Макаронная фабрика», имеющихся в наличии в магазинах Первомайского района, за период с 1 по 8 июня включительно. В ответе запишите только число.
Сначала добавим на лист «Движение товаров» недостающие поля, которые понадобятся для решения задачи: «Район» и «Товар». Для этого используем функцию =ВПР:
Чтобы определить район, выполняем поиск по ID магазина на листе «Магазин». После этого включаем фильтр: «Данные» → «Фильтр».
Настраиваем условия:
- дата — с 1 по 8 июня включительно;
- район — Первомайский;
- товар — все виды макарон.
Теперь нужно определить, на сколько увеличилось количество товара в наличии. Для этого считаем отдельно:
- сколько упаковок поступило;
- сколько упаковок было продано.
Сначала ставим фильтр на тип операции «Поступление» и считаем общее количество упаковок:
Меняем на «Продажи»:
Получаем: 4970 − 3360 = 1610.
Ответ: 1610.
Задание 2
Используя информацию из приведённой базы данных, определите, какую выручку (в рублях) от продажи конфет «Клюква в сахаре» получили магазины Промышленного района, за период с 1 по 15 июня включительно. В ответе запишите только число.
Для начала перенесём на лист «Движение товаров» те поля, которые на нём отсутствуют, но которые нужны для решения задачи: «Район», «Товар», «Цена». Воспользуемся формулой =ВПР:
Отфильтруем дату с 1 по 15 июня включительно, район — Промышленный, товар — конфеты «Клюква в сахаре», тип операции — продажа.
Так как нужно найти общую выручку, умножаем количество проданных товаров на цену товара, а затем складываем все полученные значения. Получаем 777700.
Ответ: 777700.
Задание 3
Используя информацию из приведённой базы данных, определите общий вес (в кг) всех видов карамели, полученных магазинами на улице Металлургов за период с 10 по 20 августа включительно. В ответе запишите только число.
Сначала добавим на лист «Движение товаров» недостающие данные, которые нужны для решения: «Улица», «Товар» и «Вес». Для этого используем функцию =ВПР:
Обрати внимание, что вес карамели измеряется в граммах.
Затем настроим фильтры:
- дата — с 10 по 20 августа включительно;
- улица — Металлургов;
- товар — все виды карамели;
- тип операции — «Поступление».
Теперь нужно найти общий вес полученной карамели. Для этого умножаем количество упаковок на вес одной упаковки и складываем все полученные значения:
Так как вес указан в граммах, переводим результат в килограммы: делим сумму на 1000. Получится 1800.
Ответ: 1800.
Заключение
Теперь ты знаешь, как работать с реляционными базами данных в электронных таблицах и использовать функцию ВПР для поиска нужной информации. После изучения темы ты сможешь связывать таблицы, применять фильтры, выполнять расчёты и решать задания № 3 ЕГЭ по информатике. Главное — внимательно определять, какие данные нужны для решения, и правильно настраивать поиск и фильтрацию.
Автор статьи
