📋 Условие
Формулировка таких заданий стандартная: дан файл Excel с тремя таблицами: «Товар», «Магазин», «Поставки». Нужно определить общую стоимость всех товаров, проданных в магазинах центрального района, цена за упаковку которых больше 100 рублей.
🔍 Подробное решение
Формулировка таких заданий стандартная: дан файл Excel с тремя таблицами: «Товар», «Магазин» и сводная таблица — «Движение товаров». В данной задаче требуется определить общую стоимость (в рублях) всех товаров, проданных в магазинах центрального района, цена за одну упаковку которых больше 100 рублей.
Решение:
Шаг 1: Открываем файл Excel и видим три таблицы. Для удобства обозначим границы каждой таблицы и цветом выделим строку заголовков.
Шаг 2: Переносим с помощью функции ВПР() информацию о стоимости одной единицы товара из таблицы «Товар» в сводную таблицу. В ячейке G2 запишем формулу: =ВПР(D2;Товар!A:F;6;0). В качестве искомого значения указываем «Артикул», далее указываем всю таблицу «Товар» от столбца A до F, нужный столбец — шестой («Цена за упаковку»), интервальный просмотр в точном режиме (значение «0»). Двойным щелчком по правому нижнему углу ячейки G2 растягиваем формулу до конца таблицы.
Шаг 3: Аналогичным образом переносим данные о районе каждого магазина из таблицы «Магазин». Формула: =ВПР(C2;Магазин!A:F;2;0).
Шаг 4: Вычисляем стоимость каждой закупки — умножаем цену за упаковку (столбец G) на количество упаковок (столбец E).
Шаг 5: Формируем из значений таблицу. Во вкладке «Главная» выбираем «Форматировать как таблицу». Оставляем выбранным пункт «Таблица с заголовками» и подтверждаем.
Шаг 6: Оставляем только те строки, в которых цена за одну упаковку товаров больше 100 рублей. Открываем меню фильтров нажатием на кнопку со стрелкой вниз в ячейке «Цена за упаковку». Выбираем «Числовые фильтры» — «Больше…». Вписываем значение 100.
Шаг 7: Оставляем только те строки, у которых в столбце «Тип операции» указано «Продажа».
Шаг 8: Оставляем только магазины центрального района.
Шаг 9: Вычисляем сумму всех не скрытых ячеек в столбце стоимости с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(): =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица2[[#Данные];[Стоимость товара]])
Ответ: 12434130
Решение:
Шаг 1: Открываем файл Excel и видим три таблицы. Для удобства обозначим границы каждой таблицы и цветом выделим строку заголовков.
Шаг 2: Переносим с помощью функции ВПР() информацию о стоимости одной единицы товара из таблицы «Товар» в сводную таблицу. В ячейке G2 запишем формулу: =ВПР(D2;Товар!A:F;6;0). В качестве искомого значения указываем «Артикул», далее указываем всю таблицу «Товар» от столбца A до F, нужный столбец — шестой («Цена за упаковку»), интервальный просмотр в точном режиме (значение «0»). Двойным щелчком по правому нижнему углу ячейки G2 растягиваем формулу до конца таблицы.
Шаг 3: Аналогичным образом переносим данные о районе каждого магазина из таблицы «Магазин». Формула: =ВПР(C2;Магазин!A:F;2;0).
Шаг 4: Вычисляем стоимость каждой закупки — умножаем цену за упаковку (столбец G) на количество упаковок (столбец E).
Шаг 5: Формируем из значений таблицу. Во вкладке «Главная» выбираем «Форматировать как таблицу». Оставляем выбранным пункт «Таблица с заголовками» и подтверждаем.
Шаг 6: Оставляем только те строки, в которых цена за одну упаковку товаров больше 100 рублей. Открываем меню фильтров нажатием на кнопку со стрелкой вниз в ячейке «Цена за упаковку». Выбираем «Числовые фильтры» — «Больше…». Вписываем значение 100.
Шаг 7: Оставляем только те строки, у которых в столбце «Тип операции» указано «Продажа».
Шаг 8: Оставляем только магазины центрального района.
Шаг 9: Вычисляем сумму всех не скрытых ячеек в столбце стоимости с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(): =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица2[[#Данные];[Стоимость товара]])
Ответ: 12434130
📚 Теория
Реляционные базы данных. Используем Excel для фильтрации и связей между таблицами.