Professional Excel Engineering: упражнения
Эти упражнения о книгах, которым должны доверять и которые должны поддерживать другие люди. Они охватывают разбиение модели на входные данные, расчёты и результаты, защиту листов и ячеек, аудит формул с помощью трассировки, совместную работу над общим файлом и ускорение медленных книг. Каждое задание это небольшая починка или проверка существующего файла. Лучше всего подойдёт тем, кто строит отчёты и модели, которые живут дольше одного вечера.
24 упражнения · уровень: Эксперт
Начать с первого задания Изучить курс «Professional Excel Engineering» →
Показано 24 из 24
Архитектура и аудит
Дайте ячейке H1 имя «НДС», а ячейке H2 — имя «Скидка».
В D2:D7 посчитайте сумму со скидкой, используя имя «Скидка».
В E2:E7 посчитайте НДС от суммы со скидкой через имя «НДС».
В H4 выведите итог с НДС по всем товарам.
Проверьте модель: поменяйте скидку в H2 на 10% — все суммы должны пересчитаться сами.
В C4 цена сохранена как текст. Введите её заново числом 1500.
В D2:D7 должна быть одна формула =Кол-во×Цена во всех строках. Найдите строку, где вписано число, и исправьте.
Исправьте итог количества в B8: он должен охватывать все месяцы.
В F2 посчитайте выручку другим путём — без столбца D.
В F3 сделайте контрольную ячейку: «ОК», если D8 и F2 отличаются меньше чем на 0,01, иначе «Ошибка». Добейтесь «ОК».
Производительность и интеграция
В D2:D9 замените СЕГОДНЯ() ссылкой на дату отчёта H1.
В H2 посчитайте сумму всех счетов один раз.
В E2:E9 посчитайте долю каждого счёта, ссылаясь на готовый итог H2, а не пересчитывая СУММ в каждой строке.
В H3 найдите сумму просроченных счетов (дней просрочки больше 0).
В E2:E11 сделайте JOIN: подтяните категорию товара по коду из справочника K2:L4.
В G2:G4 посчитайте SUM(amount) по регионам с условием WHERE year = значение из G7.
В H2:H4 посчитайте COUNT(*) — число заказов региона за тот же год.
В I2:I4 отметьте HAVING: «да», если сумма региона больше порога из G8, иначе «нет».
Проект
Перепишите себестоимость E2:E7 так, чтобы доля бралась из J1 (и исправьте строку, где вписано число).
Перепишите комиссию F2:F7 через параметр J2.
Исправьте строку «Итого» D8:G8: каждый итог — формула СУММ по строкам 2–7.
Добавьте налог и чистую прибыль: G9 — налог по ставке из J3, G10 — прибыль после налога.
В J5 сделайте контроль: «ОК», если итог прибыли G8 совпадает с D8−E8−F8 (допуск 0,01), иначе «Ошибка».
Стресс-тест: комиссия маркетплейса выросла. Введите в J2 значение 10% — модель должна пересчитаться целиком.
По этим фильтрам упражнений нет.