ДЕПАРТАМЕНТ ОБРАЗОВАНИЯ И НАУКИ ГОРОДА СЕВАСТОПОЛЯ

ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ ГОРОДА СЕВАСТОПОЛЯ  «СЕВАСТОПОЛЬСКИЙ ТОРГОВО-ЭКОНОМИЧЕСКИЙ ТЕХНИКУМ»

Цикловая комиссия информационных дисциплин 

Специальность:

Дисциплина: "Информационные технологии в профессиональной деятельности "

МЕТОДИЧЕСКАЯ РАЗРАБОТКА

ПРАКТИЧЕСКОГО ЗАНЯТИЯ ПО ТЕМЕ:

«Подбор параметра в Excel»

Преподаватель: Карась Ю.А.

Севастополь, 2025

I.      Организационный момент - 5 мин.

 Приветствие: Добрый день! Рад вас видеть! Отметить присутствующих в группе. Мотивирование на учебную деятельность: У нас сегодня уникальное занятие, потому как сегодняшняя информация пригодится вам в будущем при работе с данными и выполнении экономических расчетов. Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Озвучить тему занятия, сформулировав проблему, огласить цель и задачи урока.

II.   Повторение пройденного материала - 10 мин.

На прошлом занятии мы изучили тему: "Связанные таблицы. Расчет промежуточных итогов в таблицах MS Excel".

Давайте проведем краткое тестирование и проверим, как вы освоили материал.

Раздать контрольные листы самооценки, на которых обучающиеся будут проставлять баллы, полученные за ответы на вопросы теста (0,5 балла за один правильный ответ).

Вопросы теста, представлены ниже.

1. Что такое связанные таблицы в Excel?

A)  Таблицы, которые содержат одинаковые данные.

B)  Таблицы, которые связаны между собой через формулы или функции.

C)  Таблицы, которые расположены на разных листах.

D)  Таблицы, которые имеют одинаковую структуру.

2. Какая функция используется для связывания данных между таблицами?

A)  СУММ

B)  ЕСЛИ

C)  ВПР

D)  СЧЁТ

3. Что означает функция ВПР?

A)  Вертикальный поиск по диапазону.

B)  Горизонтальный поиск по диапазону.

C)  Поиск по вертикали.

D)  Поиск по горизонтали.

4. Какая из следующих функций используется для поиска данных по горизонтали? A) ВПР

B)  ГПР

C)  СУММ

D)  ЕСЛИ

5. Какая функция используется для расчёта промежуточных итогов в Excel?

A)  ПРОМЕЖУТОЧНЫЙ.ИТОГ

B)  СУММ

C)  СЧЁТ

D)  СРЗНАЧ

6. Какие типы промежуточных итогов можно рассчитать с помощью функции

ПРОМЕЖУТОЧНЫЙ.ИТОГ?

A)  Сумма, среднее, количество.

B)  Максимум, минимум, количество.

C)  Сумма, среднее, количество, максимум, минимум.

D)  Все вышеперечисленные.

7. Как выполнить копирование рабочего листа быстрым способом в EXCEL? A) С помощью ALT.

B)  С помощью CTRL

C)  С помощью SHIFT

8. Как можно выполнить подведение итогов данных рабочего листа? A) С помощью Данные/Промежуточный Итог.

B)  С помощью Вставка/Промежуточный Итог.

C)  С помощью Формулы/Промежуточный Итог.

9. В сводной таблице неправильно получены Итоговые значения. Как исправить ошибки?

A) Исправить значения в исходной таблице и обновить данные (команда «Обновить») в сводной таблице.

B)  Исправить значения в сводной таблице.

C)  Это невозможно исправить

10. Для проведения сортировки данных в таблице Excel необходимо: A) Воспользоваться командой Данные/Сортировка.

B)  Воспользоваться командой Вставка/Сортировка. C) Воспользоваться командой Данные/Фильтр.

                                                 III.    Объяснение нового материала - 20 мин.

Объяснение нового материала для выполнения практического задания сопровождается наглядной демонстрацией видео с примерами решения задач из открытого источника RuTube. Перейти на канал автора видеоконтента.

Ссылка на источник: https://rutube.ru/video/193b8e660c28043f5e5ea264069e58fd/?playlist=1112713

В данном видео рассматриваются следующие аспекты:

Подбор параметра (вводная часть):

Это часть блока задач инструмента "Анализ что-если".

Для использования инструмента необходимо зайти в ленту "Данные" и выбрать "Анализ что-если" → "Подбор параметра".

Упрощённое назначение: найти значение, которое нужно ввести в одиночную формулу, чтобы получить желаемый результат.

Подбор параметра работает только при наличии формул.

Знакомство с ключевыми понятиями по теме:

Для эффективного использования механизма подбора параметра

(оптимизации) в Excel,  необходимо ознакомиться с некоторыми ключевыми понятиями:

Итерация – это многократный пересчет листа до удовлетворения определенного числового условия. Excel не может автоматически рассчитать значение по формуле, которая ссылается (прямо или косвенно) на ячейку, содержащую формулу (это называется циклической ссылкой). Если формула содержит обратную ссылку на одну из своих собственных ячеек, необходимо определить, сколько раз следует пересчитывать формулу. Циклические ссылки могут пересчитываться до бесконечности. Однако существует возможность управления максимальным числом итераций и количеством допустимых изменений. 

Точность – это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение. 

Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул в Excel называется анализом «чтоесли». 

Подбор параметра – определяет значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата). 

Поиск решения - определяет значения в нескольких входных ячейках, которые требуются для получения желаемого результата. Более того, можно накладывать ограничения на входные данные, поэтому здесь можно получить решение (если оно существует) многих практических задач.

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

Подбор параметра (практическая часть):

Пример расчёта ежемесячного платежа по кредиту:

Рассчитывается ежемесячный платёж для кредита на покупку квартиры.

Используются следующие данные:

Сумма кредита: 6,5 млн рублей.

Срок кредита: 120 месяцев (10 лет).

Процентная ставка: 21%.

Для расчёта используется финансовая функция ПЛТ.

Функция ПЛТ возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянной процентной ставки.

Рассматриваются аргументы функции ПЛТ: ставка, количество периодов и сумма кредита.

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

Решается задача по определению суммы кредита при условии, что ежемесячный платёж не должен превышать 25 000 рублей.

Устанавливается значение ежемесячного платежа в ячейке и подбирается сумма кредита.

Результат: сумма кредита составляет 1 619 893 рубля.

Расчёт процентной ставки:

Решается задача по определению процентной ставки при условии, что ежемесячный платёж составляет 25 000 рублей.

Устанавливается значение ежемесячного платежа и подбирается процентная ставка.

Результат: процентная ставка должна быть 4%.

Пример расчёта проходного балла для абитуриента:

Рассчитывается, какой балл по физике должен получить абитуриент, чтобы пройти по проходному баллу 85.

Используется функция СРЗНАЧ для определения среднего значения баллов.

Результат: балл по физике должен быть 100.

Пример расчёта фонда заработной платы:

Рассчитывается фонд заработной платы для пяти сотрудников.

Включаются оклады, премии, доплаты и налоги.

Используется условный оператор ЕСЛИ для определения доплаты.

Рассчитывается сумма начислений и налога.

Определяется сумма к выдаче.

Рассчитывается фонд заработной платы.

С помощью подбора параметра определяется процент премии, чтобы фонд заработной платы составил 250 000 рублей.

Результат: премия должна быть 90%.

Выполнение практической работы - 45 мин ⁽Задания взяты из открытых источников⁾

Практическая работа - 45 мин. Работа выполняется индивидуально. За каждое правильно выполненное задание выставляется 1 балл.

Инструкционная карта для выполнения практической работы, представлена ниже:

Произвести обратный пересчет данных методом подбора параметра в таблице, в которой данные связаны формулами. 

Задание 1. Используя операцию подбор параметра, определить, при каком значении % премии общая сумма заработной палаты за октябрь будет равна 250000 руб. (на основании таблицы прошлой практической работы).

Краткая справка. К исходным данным этой таблицы относятся значения оклада и %Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в Microsoft Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра и по этому значению подбирается некоторое удовлетворяющее заданным условиям значение исходного параметра расчета.

Ход и порядок выполнения:

1. Оформить таблицу по образцу для этого необходимо выполнить следующие расчеты:

•       Премия= Оклад * % Премии (для в ячейке D5 введите формулу =$D$4*C5, так как ячейка D4 используется в виде абсолютной адресации). Скопируйте набранную формулу вниз по столбцу Автозаполнением.

•       Всего начислено= оклад + премия

•       Удержания = всего начислено *% удержаний (для этого в ячейке F5 введите формулу =$F$4*E5)

•       К выдаче= всего начислено- удержания

2.   Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доход по данным колонки «К выдаче».

3.   Проверьте сортировку по фамилиям в алфавитном порядке по возрастанию.

4.   Переименуйте Лист 1, присвоив ему имя Зарплата за октябрь.

5.   Осуществите подбор параметра командой Данные/Анализ, что если/ Подбор параметра. Откроется окно подбор параметра. В диалоговом окне подбор параметра на первой строе в качестве подбираемого параметра укажите адрес общей итоговой суммы заработной платы (G19), на второй строке наберите значение 250000, на третей строке укажите адрес подбираемого значения- % премии ($D$4) и нажмите кнопку ОК.

6.   Произойдет обратный расчет % Премии.

Задание 2. Используя режим «Подбор параметра», определите штатное расписание фирмы. Общий месячный фонд заработной платы составляет 100000 рублей. Необходимо определить, каким должны бить оклады сотрудников фирмы.

Ход и порядок выполнения работы:

1.   Оформите таблицу по образцу:

2.   Выделите отдельную ячейку D3 для заработной платы курьера и все расчеты задайте с учетом этого. Например, введите в ячейку D3 число 100.

3.   В столбце D введите формулу для расчета заработной платы по каждой должности. Для ячейки D6 формула имеет следующий вид: =B6*$D$3+C6. Далее используйте автозаполнение для интервала D6:D13.

4.   В столбце F задайте формулу расчета заработной платы для всех работающий в данной должности. Для ячейки F6 формула: =F6*E6. Далее используйте автозаполнение для интервала F6:F13.

5.   В ячейке F14 функцией «Автосумма» вычислите суммарный фонд заработной платы фирмы. Полученный результат в ячейке F14 запомните.

6.   Произведите подбор заработных плат сотрудников фирмы для суммарной заработной платы в сумме 100000 рублей. (Данные/Анализ, что если/ Подбор параметра.)

В поле «Установить в ячейке» появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы; в поле «Значение» наберите искомый результат 100000; в поле «Изменяя значение ячейки» введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера. Щелкните ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб.

7.   Зафиксируйте какая стала зарплата курьера.  

8.   Назовите Лист как: Штатное расписание1

Далее, используя таблицу расчета штатного расписания (см. задание выше), определить величину заработной платы сотрудников фирмы для ряда заданных значений фонда заработной платы 

Порядок работы 

1.     Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».

2.     Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 руб. Результаты подбора значений зарплат скопируйте в Сводную таблицу (см. ниже) (создана на отдельном листе) в виде специальной вставки. 

Краткая справка. Для копирования результатов расчетов специальной вставкой в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать), установить курсор в первую ячейку таблицы ответов соответствующего столбца, задать режим специальной вставки (Правка/ Специальная вставка), отметив в качестве объекта вставки значения (Правка/Специальная вставка/вставить — значения)  

Сводная таблица

Дополнительно (самостоятельное задание)*

(выполнить один вариант, выбрав его по списку журнала):

Решите функциональное уравнение, заданное вашим вариантом в таблице вариантов:

Проверить работы обучающихся.

Домашнее задание - 2 мин.

Дать ссылку на краткий конспект ключевых понятий по данной теме занятия.

Обучающимся законспектировать ключевые понятия в конспект.

Подведение итогов занятия - 3 мин.

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