Методические основы
Если хотите превратить Excel из «таблички для подсчёта чисел» в мощный инструмент анализа данных, автоматизации и принятия решений – вы на правильном пути! В нашем задачнике собраны интересные кейсы, разработанные специально для тех, кто хочет не просто зазубрить функции, а научиться применять их в реальных жизненных сценариях: от финансов до маркетинга, от личного бюджета до корпоративных отчётов.
Почему именно задачи?
Чтобы по-настоящему освоить Microsoft Excel, недостаточно прочесть теорию – нужно «пощупать» инструменты на практике. Тесты с вопросами вроде «Что делает функция ВПР?» – это как проверять повара по его знанию названий ножей. Теория важна, но бизнесу нужен человек, который
решает реальные задачи: анализирует продажи, строит прогнозы, автоматизирует
отчёты. Работая над заданиями, вы:
- увидите Excel «изнутри»;
- сможете мыслить как аналитик;
- получите опыт разбора реальных кейсов;
- получите объективную оценку своих знаний;
- интересно проведёте время с пользой.
Кому полезен практикум по решению кейсов Excel?
Microsoft Excel – это универсальный софт, который открывает двери в мир эффективности, карьерного роста и личного развития. Но чтобы освоить его по-настоящему, недостаточно теории. Волшебный ключ, сокращающий путь от «я где-то слышал про ВПР» до «я решаю любую задачу за 5 минут» – это практика. И вот кому он особенно необходим:
Студентам – готовьтесь к зачёту без стресса
Когда вы сталкиваетесь с необходимостью сдать зачёт или экзамен, уверенность в своих знаниях становится ключом к успеху. Разбирая кейсы в Excel, вы не только закрепляете навыки работы с формулами, диаграммами и сводными таблицами, но и учитесь применять их в реальных ситуациях. Это помогает не просто «зубрить» материал, а понимать, как анализировать данные, автоматизировать процессы и находить решения, которые оценят преподаватели. Практикум превращает страх перед экзаменом в уверенность!
Соискателям – проявите себя на собеседовании
Работодатели ищут не красивые слова в резюме, а реальные навыки. Когда вы решаете практические кейсы в Excel, вы тренируете умение справляться с заданиями, которые часто дают на интервью. Это может быть анализ продаж, построение отчётов или автоматизация рутинных операций. Чем больше вы практикуетесь, тем увереннее чувствуете себя на собеседовании. А это значит, что вы выделяетесь среди конкурентов, которые застревают на простых задачках.
Сотрудникам – не теряйте квалификацию и двигайтесь вверх
Даже если вы уже давно в профессии, Excel не прощает забвения. Постоянный практикум помогает не только сохранять навыки, но и осваивать новые инструменты, такие как динамические массивы или макросы. Решая задачи, вы остаётесь в тонусе: не просто повторяете привычные действия, а учитесь применять современные решения, экономящие время и повышающие эффективность. Для руководства это сигнал – вы не просто сотрудник, а активный участник развития компании!
Всем остальным – тренируйте мозг, как мышцы
Электронные таблицы – это не только про цифры, а тренажёр для логики, памяти и креативности. Каждая задача заставляет вас думать системно: находить закономерности, строить алгоритмы, анализировать данные. Это как шахматы для аналитиков: чем больше вы играете, тем острее ваш ум. Практика в Excel развивает навыки, которые пригодятся в любой сфере: от планирования бюджета до принятия решений. И даже если вы не связаны с цифрами напрямую, такие кейсы улучшают концентрацию, учат мыслить стратегически и находить нестандартные подходы!
Почему наш задачник – ваш реальный помощник?
Знания без практики – как ключ без замка: они вроде как есть, но открыть ими ничего нельзя. Мы предлагаем не просто набор заданий, «сухо» снабжая его готовым правильным ответом. Мы учим мыслить, предлагая пошаговую инструкцию к решению, а также интересный видеоурок, где опытный специалист даёт детальный разбор практического примера. Наш задачник – далеко не банальный набор кейсов, а ваш личный преподаватель, ведущий тернистой тропой к знаниям MS Excel.
Готовы к вызову? Выберите первую задачу, откройте программу Excel на своём компьютере и начните практиковаться! Не бойтесь ошибок – именно через них вы прокачаете навыки. Каждый кейс на сайте – это мини-проект:
- Условия задачи: описание ситуации, близкой к реальности;
- Исходные данные: скачиваемый файл Excel, содержащий готовые таблицы с данными;
- Цель задания: какой практический результат требуется получить;
- Правильный ответ: позволит сверить своё решение;
- Пошаговая инструкция: детальный разбор примера с картинками;
- Видеоурок: короткий и интересный ролик для тех, кто предпочитает визуальный ответ.
Не ждите вдохновения – создавайте его сами! Каждая успешно решённая задача – это новый уровень в Вашей карьере. Начните практиковаться прямо сейчас и Excel станет не проблемой, а главным инструментом успеха.
Как работать с заданиями?
- Скачайте файл и изучите задание – скачайте и откройте Excel-файл с данными, внимательно прочитайте условие задачи.
- Решите задачу самостоятельно – примените формулы, функции и инструменты программы. Не подглядывайте в ответы – всегда интереснее узнать собственные силы!
- Проверьте результат – сверяйтесь с эталонным решением: пошаговая инструкция и видеоурок помогут разобрать ошибки и найти новые подходы.
- Анализируйте и улучшайте – даже при правильном ответе изучите альтернативные варианты решения, так вы освоите Excel глубже.
1. Кейс на тему формул с процентами
Итоговую стоимость закупки нужно принять за 100%. Суммы по каждому товару нужно пересчитать в процентах от итоговой суммы.
Что получилось в ячейке Е5?
Смотреть решение
1. Итоговую сумму 272650 принимаем за 100%.
2. Вводим формулу в ячейке Е2: =D2*100%/$D$10 и копируем ее ниже.
3. В ячейке Е5 получим 0,22006235099945 и переводим формат ячейки в процентный, используя кнопку «Процентный» в группе «Число» на ленте вкладки «Главная».
Правильный ответ: 22%
2. Задание на расширенный фильтр
Ниже исходной таблицы получить таблицу, где будут данные по товару «кресло» или по любому товару со стоимость больше 100 000.
Сколько строк с такими данными получилось?
Смотреть решение
1. Для расширенного фильтра нужно создать отдельно от таблицы условия для выборки данных. Данные связаны союзом «или» поэтому условия для выборки располагаем в ячейках разных строк.
2. На вкладке «Данные» выбирают команду «Дополнительно» в разделе «Сортировка и фильтр».
3. Получим:
4. Количество строк можно посчитать по количеству числовых значений. Для этого можно использовать функцию: =СЧЁТ(B37:B59)
Правильный ответ: 23
3. Задача на трёхмерные ссылки и консолидацию
Найдите среднее значение по расходам на канцелярские товары за 6 месяцев двумя способами. 1 способ – трёхмерные ссылки. Второй способ – консолидация. Результаты округлите до целого.
Какое значение получилось по товару папки?
Смотреть решение
Первый способ решения
1. На листе «Итог» в ячейке В2 вводим =СРЗНАЧ().
2. Щелкаем по названия листа «январь», нажимаем Shift и щелкаем по названию последнего листа. Щелкаем по В2 и нажимаем Enter. Получим формулу: =СРЗНАЧ(январь:июнь!B2).
3. Копируем формулу вниз на остальные ячейки.
4. Округляем до целого значения с помощью кнопки «Уменьшить разрядность» в группе «Число» на ленте вкладки «Главная».
Второй способ решения
1. Выделяем пустую ячейку, начиная с которой будет создана таблицы по консолидации.
2. На вкладке «Данные» выбираем команду «Консолидация». В поле «Функция» нужно выбрать «Среднее». Выделяем данные по первой таблице вместе с заголовками на листе «январь» и нажимаем кнопку «Добавить», выделяем данные второй таблице на листе «февраль» и опять нажимаем кнопку «Добавить» и т.д. Таким образом, собираем данные со всех таблиц. Внизу нужно поставить 2 галочки.
3. Результаты нужно округлить до целого значения. Для этого используем кнопку «Уменьшить разрядность» на ленте вкладки «Главная».
4. Результат работы:
Правильный ответ: 12
4. Задание на формулы массивов
Найдите среднее значение по премии менеджера Смирнова. Округлите результат до целого.
Какое значение получилось?
Смотреть решение
{=СРЗНАЧ(ЕСЛИ(A2:A50="Смирнов";ЕСЛИОШИБКА(B2:B50;"");""))} или
{=СРЗНАЧ(ЕСЛИ(A:A="Смирнов";ЕСЛИОШИБКА(B2:B50;"");""))}
1. Функция СРЗНАЧ посчитает среднее значение по премиям.
2. Функция ЕСЛИОШИБКА заменит ошибочные значения на пробел. Пробел можно показать двумя двойными кавычками. Мы используем эту функция из-за значений #Н/Д в премиях. Эти ошибочные значения заменяются на пробел. Если в столбце премий оставить ошибочные значения вперемешку с числовыми значениями, то нельзя посчитать среднее значение.
3. Функция ЕСЛИ забирает премии только по Смирнову. Она содержит 3 аргумента.
=ЕСЛИ(условие;значение в случае выполнения условия;значение в случае невыполнения условия).
Если фамилия Смирнов, то забирается премия в массив данных, а иначе – пробел.
4. Поскольку мы работаем с массивами данных, то завершаем формулу нажатием Ctrl+Shift+Enter и тогда получим фигурные скобки.
Правильный ответ: 2295
5. Задача на функции Excel
Используйте функции из категорий: логические, математические, дата и время.
1. Заполните колонку «Премия». За продажи в летний период мы назначаем премию 1% от продажи, в зимние месяцы – 3% от продажи. В осенний период премия составит 2% от продажи, а весной – 1,5% от суммы продажи.
2. По каждому менеджеру получите итоговую сумму по премиям за год. Результат округлите до двух цифр после запятой.
Какой значение получилось в ячейке Е2 и I2?
Смотреть решение
1. Для получения премии нужно использовать вложенную функцию ЕСЛИ().
2. Функция МЕСЯЦ() позволит понять к какому сезону относится дата. Она относится к категории ДАТА и ВРЕМЯ. Функцию будем применять к дате. Например, если значением функции будет 1 или 2 или 12, то это зимний период.
3. Для получения премии используем формулу:
=ЕСЛИ(ИЛИ(МЕСЯЦ(B2)=1;МЕСЯЦ(B2)=2;МЕСЯЦ(B2)=12);C2*3%;ЕСЛИ(ИЛИ(МЕСЯЦ(B2)=6;МЕСЯЦ(B2)=7;МЕСЯЦ(B2)=8);C2*1%;ЕСЛИ(ИЛИ(МЕСЯЦ(B2)=3;МЕСЯЦ(B2)=4;МЕСЯЦ(B2)=5);C2*1,5%;С2*2%)))
4. Для получения итоговой суммы по премиям по каждому менеджеру будем использовать функцию =СУММЕСЛИ() Она относится к категории математических функций.
=СУММЕСЛИ(D:D;H2;E:E) или =СУММЕСЛИ($D$2:$D$127;H2;$E$2:$E$127)
Правильный ответ: 573,6 и 32441,13
6. Кейс на условное форматирование
1. Выделите голубым цветом строчки, относящиеся к городу Клин, где суммы больше 9000.
2. Выделите зеленым цветом строчки, относящиеся к городу Дмитров, где суммы меньше 10000.
3. Выполните сортировку таким образом, чтобы строки с голубым фоном расположились вверху таблицы, а строки с зеленым фоном ниже строк с голубым фоном.
4. Посчитайте количество строк с голубым фоном и с зеленым фоном, используя фильтры.
Сколько получилось строк с голубым и зеленым фоном?
Смотреть решение
1. Выделите всю таблицу без заголовков.
2. На вкладке «Главная» нужно выбрать «Условное форматирование» → «Создать правило».
3. Нажать кнопку «Формат» и на вкладке «Заливка» выбрать голубой цвет.
4. Таким же образом создаем зеленый фон.
5. Для сортировки достаточно выделить любую ячейку таблицы и на вкладке «Данные» выбрать «Сортировка».
6. Теперь будем фильтровать по цвету. Щелкнуть по любой ячейки таблицы и на вкладке «Данные» выбрать «Фильтр». Запускаем фильтр по цвету.
7. В строке состояния можно увидеть количество строк.
8. Таким же образом смотрим количество зеленых строк.
Правильный ответ: 6 и 3
7. Задача по теме «Промежуточные итоги»
Получите по каждому городу итоговые суммы по категории, по наименованию и итоговую сумму по конкретному городу.
Какие итоговые суммы по Калининграду, по молоку в Екатеринбурге и крупам в Ростове?
Смотреть решение
1. Выполнить сортировку по 3 критериям сразу. Для этого нужно щелкнуть мышкой по любой ячейке таблицы и на вкладке «Данные» выбрать команду сортировка.
2. На вкладке «Данные» нужно выбрать команду «Промежуточный итог».
3. Второй раз зайти в окно итогов и по категориям получить итоговые суммы.
4. Третий раз зайти в окно промежуточных итогов и получить суммы по наименованию товаров.
5. Выбрать предпоследний уровень в верхнем левом углу таблицы.
6. Получим результат:
Правильный ответ: 846088, 134615 и 317964 соответственно
8. Задание на построение диаграммы
Постройте по исходным данным комбинированную диаграмму следующего вида:
Смотреть решение
1. Выделяем всю таблицу с заголовками и на вкладке «Вставка» выбираем гистограмму.
2. Щелкаем правой клавишей мыши в области диаграмм и в контекстном меню выбираем команду «Изменить тип диаграммы». Выбираем комбинированная.
3. Выбираем график с маркерами для колонок с изменением курсов, для остальных данных оставляем гистограммы. Вспомогательную ось включаем для курсов доллара и евро.
4. Вызываем числовые показатели на диаграмму. Для этого щелкаем по значку плюс в верхнем правом углу диаграммы и выбираем «Метки данных».
5. Для изменения расположения числовых данных нужно щелкнуть по любому числу правой клавишей мыши и выбрать «Формат подписей данных». В появившемся окне выбрать вариант расположения числовых показателей.
6. Для быстрого понимания, к какой диаграмме относится число, используем изменение цвета шрифта или заливку за числовым показателем. Цвет фигуры диаграммы совпадает с цветом шрифта или с заливкой под числовым показателем.
7. Для создания названия осей нужно щелкнуть по значку плюс в верхнем правом углу диаграммы и выбираем «Название осей». Выбираем основные вертикальные и промежуточные вертикальные. Набираем названия осей.
8. Чтобы даты переместить вниз ось дат нужно щелкнуть правой клавишей мыши по оси с датами и выбрать «Формат оси» и положение подписи выбираем «внизу».
9. Легенду помещаем вверху.
10. Для изменения цвета фигур диаграмм нужно щелкнуть правой клавишей мыши по диаграмме и выбрать «формат ряда данных». Переключаемся на заливку и границы и меняем цвет для линии изменения курса Евро.
11. Для создания названия диаграммы нужно щелкнуть по плюсу в верхнем левом углу бланка диаграммы и выбираем «Название диаграммы». В строке формул нужно ввести знак = и щелкнуть по ячейке А1.
9. Кейс на подбор параметра
Какой должна быть сумма проекта, если хотим получить премию 15000? Округлите до 2 цифр после запятой.
Смотреть решение
На вкладке «Данные» выбираем «Анализ что-если» → «Подбор параметра».
Правильный ответ: 405405,41
10. Задача по теме «Анализ в сводных таблицах»
Выполните анализ данных с помощью сводной таблицы таким образом, чтобы можно было понять какая категория дает наибольшую прибыль в конкретный месяц и в рамках каждой категории нужно понять какой товар продается успешнее всего в конкретный месяц.
Сумма процентов по каждой категории должна дать 100%. Если сложить проценты по конкретным наименованиям в рамках одной категории, то так же должно получиться 100%.
- Какой процент составляют лакокрасочное материалы в январе от итоговой суммы по январю?
- Сколько процентов составляет сумма по лаку от суммы по категории «лакокрасочные материалы» за январь?
Смотреть решение
1. Создаем сводную таблицу на новом листе на основе исходной таблицы. Для этого щелкаем по любой ячейке таблицы и на вкладке «Вставка» → «Сводная таблица». В раздел «значения» перемещаем сумму, в раздел «Строки» перемещаем категорию и наименование. Месяц помещаем в раздел «Столбцы».
Получим:
2. Щелкнуть правой клавишей мыши по любому числовому значению и выбрать команду «Дополнительные вычисления» → «Процент от суммы по родительской строке». Итоговая сумма по каждому месяцу берется за 100%. Если сложить проценты по каждой категории, то получим 100%. Если сложить проценты по каждой категории, то так же получим 100%.
В январе наиболее успешна шла продажа по категории «Крепёж», и по болтам, лаку и шпатлевке в рамках категорий. Время работы 10 ч 35 мин. Округлим до 10 часов.
Правильный ответ: 21,93% и 62,30% соответственно
