Базы данных в Excel

Что проверяет номер

В задании дан файл с несколькими таблицами: операции, товары, магазины. Таблицы связаны по номерам, например по артикулу и ID магазина. Нужно ответить на вопрос вроде такого: на какую сумму продано товаров отдела «Напитки» в районе «Южный» с 10 по 20 июня?

В таблице операций нет ни отдела, ни района, там только номера. Поэтому решение всегда состоит из трёх шагов:

1. Подтянуть в таблицу операций недостающие столбцы.
2. Отфильтровать строки по условиям.
3. Посчитать сумму или количество.

Вот наш учебный файл. Таблицы короткие только на схеме, в настоящем файле в операциях тысячи строк.

На станции КЕГЭ есть офисный пакет с таблицами. Какой именно, стоит посмотреть перед экзаменом на тренировочной станции: от этого зависят названия функций и разделитель аргументов. Ниже русские названия, аргументы разделяются точкой с запятой.

ВПР: подтягиваем данные

ВПР ищет значение в первом столбце другой таблицы и возвращает из найденной строки нужный столбец.

Четыре аргумента, по порядку:

• D2 это что ищем: артикул из текущей строки;
• Товар!A:F это где ищем: вся таблица, искомый столбец в ней должен быть первым;
• 2 это какой столбец вернуть, считаем с начала диапазона;
• 0 это точное совпадение, ставим всегда.

В нашем файле нужны три новых столбца:

Отдел (G):  =ВПР(D2; Товар!A:F; 2; 0)
Район (H):  =ВПР(C2; Магазин!A:C; 2; 0)
Сумма (I):  =F2*ВПР(D2; Товар!A:F; 5; 0)*ВПР(D2; Товар!A:F; 6; 0)

Сумма это упаковки × штук в упаковке × цена за штуку. Написали формулы в строке 2, потом выделяем их и двойным кликом по уголку ячейки протягиваем вниз на все строки.

Фильтр и сумма

Когда все столбцы на месте, включаем фильтр на строке заголовков: Данные → Фильтр. В каждом заголовке появится стрелка. Отбираем: тип операции, отдел, район, даты.

Выделяем столбец с суммами и смотрим вниз окна: в строке состояния написана сумма. Она считает только видимые строки, а это как раз то, что нужно после фильтра.

Одна формула вместо фильтра

То же самое можно получить без фильтра, функцией СУММЕСЛИМН. Сначала диапазон, который суммируем, потом пары «диапазон; условие»:

=СУММЕСЛИМН(I2:I301; E2:E301; "Продажа"; G2:G301; "Напитки"; H2:H301; "Южный";
            B2:B301; ">="&ДАТА(2026;6;10); B2:B301; "<="&ДАТА(2026;6;20))

На нашем учебном файле из 300 строк обе дороги дают 189750. Если границы дат не включить, получится 169422. Так что условие «с 10 по 20 включительно» читаем внимательно.

Типы задач

Без таблицы эти задачи не решаются, строк слишком много. Вся работа в том, чтобы правильно составить условие: какие таблицы нужны, по какому полю они связаны, что подтягиваем, что фильтруем и что считаем.

Перед тем как открывать файл, прочитай условие и выпиши себе:

• слово «получено» или «поступило» значит тип операции Поступление, а «продано» и «выручка» значат Продажа;
• какие таблицы нужны: район или адрес лежит в «Магазине», отдел, название и цена обычно в «Товаре»;
• в чём ответ: рубли, килограммы, литры, упаковки.

Ловушки перед экзаменом