Базовый (1 балл) Время: 4-6 мин Excel / Calc ВПР СУММЕСЛИМН Автофильтр Все задачи №3 в каталоге

Задание №3. Базы данных: три таблицы и один ответ

Тема: Связанные таблицы в Excel, ВПР, СУММЕСЛИМН и фильтры

Файл с тремя связанными таблицами: движение товаров, справочник товаров и справочник магазинов. Надо найти суммарное количество, выручку или изменение остатка по товару и району. Делается в Excel за пять минут, если не метаться между листами.

Что от тебя хотят

В файле три листа. На главном («Движение товаров») записаны операции: дата, номер магазина, артикул, тип операции (поступление или продажа), количество, цена. На втором («Товары») по артикулу можно узнать отдел, название и вес упаковки. На третьем («Магазины») по номеру магазина узнаёшь район. Вопрос обычно такой: сколько чего-то в каком-то районе за какие-то дни.

Главная проблема: нужных сведений нет на главном листе. Название товара и район лежат на соседних листах.

Идея: подтяни всё на один лист

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

Район. В новый столбец главного листа вставь =ВПР(B2; Магазины!A:C; 3; 0). Здесь B2 — номер магазина, 3 — номер столбца с районом в справочнике, 0 — точный поиск.
Название товара и вес упаковки. Так же, по артикулу: =ВПР(C2; Товары!A:F; 3; 0) и =ВПР(C2; Товары!A:F; 5; 0). Номера столбцов подставь свои.
Растяни формулы вниз двойным щелчком по уголку ячейки.
Посчитай ответ. Для суммы по нескольким условиям используй СУММЕСЛИМН:

=СУММЕСЛИМН(F:F; J:J; "Заречный"; K:K; "Молоко"; D:D; "Продажа")

Первый аргумент — что суммируем (количество), дальше пары «диапазон; условие». Даты, тоже парами: E:E; ">="&ДАТА(2021;6;1); E:E; "<="&ДАТА(2021;6;10).

Если спросили «на сколько изменилось»

Добавь столбец со знаком: =ЕСЛИ(D2="Поступление"; F2; -F2). Теперь поступление идёт плюсом, продажа минусом, и ответ — просто сумма этого столбца с теми же условиями района и товара.

Запасной способ без формул

Включи фильтр (Ctrl+Shift+L) и отбери нужное в справочниках: район на листе «Магазины», товар на листе «Товары». Выпиши номера магазинов и артикулы и отфильтруй ими главный лист. Результат считай через ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; диапазон).

Обычная СУММ суммирует и скрытые строки. Если ты работаешь через фильтр, нужна только ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; ...), иначе ответ получится завышенным.

Если оба условия «район» и «товар» перечислены списком (несколько артикулов или магазинов), проще достроить столбцы и использовать СУММЕСЛИМН, чем фильтровать руками.

Разновидности и прототипы задания на экзамене

Тип 1: Сколько продано или поступило

Суммируем количество упаковок по товару, району, типу операции и датам.

Тип 2: На сколько изменился остаток

Поступление считаем со знаком плюс, продажу со знаком минус.

Тип 3: Вес или выручка

Количество упаковок умножаем на вес упаковки или на цену, смотря что спросили.

Типичные ошибки

✕ Не так

Искать товар по названию без отдела

У «Чая» бывает несколько артикулов в разных отделах.

✓ Так

Сначала отфильтровать отдел, потом выбирать артикулы внутри него.

✕ Не так

Забыть перевести граммы в килограммы

Вес упаковки в справочнике в граммах, а просят килограммы.

✓ Так

Перечитать вопрос: если нужны килограммы, делим итог на 1000.

✕ Не так

Перепутать поступление и продажу

Выручка получается только от продаж.

✓ Так

Выручка считается по строкам с типом операции «Продажа».

ГРОБ

ГРОБ №3: Одинаковые названия в разных отделах

В справочнике есть «Чай» в отделе «Бакалея» и «Чай» в отделе «Напитки», а в вопросе речь только об одном из них. Если искать по названию, посчитаешь оба.

Как обойти

Не ищи по названию. Сначала отфильтруй отдел, потом выпиши артикулы только из него и работай с артикулами.

Лайфхаки на экзамене

Двойной щелчок по уголку ячейки с формулой растягивает её вниз на всю таблицу.
Выдели диапазон чисел, и в правом нижнем углу окна Excel сразу покажется сумма и количество.
В ВПР последний аргумент почти всегда ноль: он включает точный поиск. Без него Excel может подтянуть соседнюю строку.
Формулу в первой строке проверь руками: найди значение на справочном листе и убедись, что ВПР принёс то же самое.

Банк реальных задач №3 Открыть в тренажере СмартКИМ