
Навигация по странице
Постановка задачи
В прошлых статьях мы уже научились работать с реляционными базами данных и с функцией поиска информации ВПР(). Теперь же применим полученные навыки для решения задания 3 ЕГЭ по информатике.
Формулировка таких заданий стандартная: нам дан файл Excel с тремя таблицами: «Товар», «Магазин» и сводная таблица — «Движение товаров».

ER-диаграмму таких таблиц мы уже рассматривали ранее. Общими ключами в этих таблицах являются «ID магазина» и «Артикул».
Больше никаких общих атрибутов у наших таблиц не имеется, следовательно переместить данные из таблицы «Магазин» в «Движение товаров» можно только по «ID магазина», а из таблицы «Товар» только по атрибуту «Артикул».
В данной задаче требуется определить общую стоимость (в рублях) всех товаров, проданных в магазинах центрального района, цена за одну упаковку которых больше 100 рублей.
Алгоритм решения
Шаг 1
Открываем файл Excel и видим следующие три таблицы. Для удобства здесь обозначим границы каждой таблицы и цветом выделим строку заголовков.
Таблица «Движение товаров»:

Таблица «Товар»:

Таблица «Магазин»:

Первым делом, переместим с помощью функции ВПР()
информацию о стоимости одной единицы товара из таблицы «Товар» в сводную таблицу «Движение товаров».
Для этого в ячейке G2
запишем следующую формулу:
=ВПР(D2;Товар!A:F;6;0)
В качестве искомого значения указываем «Артикул», далее указываем всю таблицу «Товар» от столбца A
до F
, нужный нам столбец из этой таблицы — шестой — «Цена за упаковку», интервальный просмотр всегда используем в точном режиме (значение «0»).
Двойным щелчком по правому нижнему углу ячейки G2
растягиваем формулу до конца таблицы.

Шаг 2
Аналогичным образом, что и в прошлом шаге, переносим данные о районе каждого магазина из таблицы «Магазин».
Формула в ячейке H2
будет следующая:
=ВПР(C2;Магазин!A:F;2;0)

Шаг 3
Вычислим теперь стоимость каждой закупки целиком и запишем эти значения в столбец I
. Для этого умножим цену за упаковку (столбец G
) на количество упаковок (столбец E
).

Шаг 4
Сформируем из наших значений таблицу. Для этого во вкладке «Главная» выберем пункт «Форматировать как таблицу». В открывшемся окне оставляем выбранным пункт «Таблица с заголовками» и подтверждаем.

Теперь мы сможем использовать фильтры для столбцов таблицы.
Шаг 5
Сначала оставим только те строки, в которых цена за одну упаковку товаров больше 100 рублей.
Для этого открываем меню фильтров нажатием на кнопку со стрелкой вниз в ячейке «Цена за упаковку» (G1
). Выбираем «Числовые фильтры» и «Больше…». В открывшемся окне вписываем значение 100 и подтверждаем.

Шаг 6
Теперь оставим в таблице только те строки, у которых в столбце «Тип операции» указано «Продажа».

Шаг 7
Следующим шагом оставим в нашей таблицы только магазины центрального района.

Шаг 8
Все наши данные готовы, теперь может перейти к вычислению ответа.
Нам нужно посчитать сумму всех не скрытых ячеек в столбце I
. Для этого воспользуемся функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
.
В качестве аргументов этой функции передадим сначала номер применяемой функции (здесь применяем функцию СУММ()
, у которой номер 9) и вторым аргументом передадим диапазон таблицы со стоимостью товаров.
Полная формула выглядит так:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица2[[#Данные];[Стоимость товара]])

В ответе получаем значение 12434130. Задание 3 ЕГЭ по информатике решено!