• Преподавателю
  • Информатика
  • Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Раздел Информатика
Класс -
Тип Другие методич. материалы
Автор
Дата
Формат docx
Изображения Есть
For-Teacher.ru - все для учителя
Поделитесь с коллегами:

Лабораторная работа №19

Информационная технология подбора параметра

Цель: Научиться применять в Excel технологию подбора параметра для анализа данных.

Теоретическая часть

Команда Подбор параметра (кнопка Анализ «что-если» вкладка Данные) обеспечивает вычисление значения аргумента (параметра) для заданного значения функции методом последовательных итераций.

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

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

Практическая часть

Задание 1. Используя режим подбора параметра определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250000р. (на основании файла «Ведомость ЗП», созданного в лабораторной работе №15).

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

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

  1. Создайте рабочую книгу с именем с именем «Лабораторные № 20-23» в своей рабочей папке и откройте созданный в Лабораторной работе №15 файл «Ведомость ЗП».

  2. Скопируйте содержимое листа «Ведомость ЗП» на Лист1 электронной книги «Лабораторные № 20-23» и присвойте ему имя «Подбор параметра».

  3. Осуществите подбор параметра кнопкой Анализ «что-если» Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности на вкладке «Данные».

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Рис. 1. Задание параметров подбора параметра

В диалоговом окне Подбор параметра в первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G12), во второй строке наберите заданное значение 250000, в третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку OK (рис. 2).

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Рис. 2. Подтверждение результатов подбора параметра

Произойдёт обратный пересчёт % Премии. Результат подбора (рис. 3): если сумма к выдаче равна 250000р., то % Премии должен быть 562%.

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Рис. 3 Подбор значения % Премии для заданной общей суммы

заработной платы, равной 250000р.


Задание 2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рис. 4.

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Рис. 4. Исходные данные для Задания 2

Краткая справка. Известно, что в штате состоит:

  • 6 курьеров;

  • 8 младших менеджеров;

  • 10 менеджеров;

  • 3 заведующих отделами;

  • 1 главный бухгалтер;

  • 1 программист;

  • 1 системный аналитик;

  • 1 генеральный директор фирмы.

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

Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai*x+Bi, где x - оклад курьера; Ai и Bi - коэффициенты, показывающие:

Ai - во сколько раз превышается значение х;

Bi - во сколько превышается значение х.

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

  1. Создайте таблицу штатного расписания фирмы по приведённому образцу (рис. 4). Введите исходные данные в рабочий лист электронной книги.

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

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

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

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

  1. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100000р. Для этого активизируйте команду Подбор параметра с помощью кнопки Анализ «что-если» на вкладке «Данные».

  • В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчёта фонда заработной платы;

  • В поле Значение наберите искомый результат 100000;

  • В поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке OK. Произойдёт обратный расчёт зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000р.

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

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

заработной платы, равной 100000р.


  1. Присвойте рабочему листу имя «Штатное расписание».

Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 1 и 2 показывают, что поиск значения параметров формулы - это не что иное, как численное решение уравнений. Другими словами, используя возможности MS Excel, можно решать любые уравнения с одной переменной.

Задание 3. Самостоятельно решите следующую задачу:

Вычислить сумму накопления на депозитном вкладе, если размещены 1000р. Под 18% годовых на срок 3 года с обязательством дополнительных вложений в сумме 100р. В конце каждого месяца.

Краткая справка. В финансовых функциях по расчёту денежных потоков важно направление потока: «к нам» - со знаком плюс, «от нас» - со знаком минус.

Рекомендации по решению задачи

Лабораторная работа ИТ подбора параметра по дисциплине Информационные технологии в профессиональной деятельности

Рис. 6. Параметры для вычисления функции БС

  1. Свободный лист переименуйте на БС.

  2. Начиная с ячейки А1 разместите параметры для вычисления функции БС (рис. 6).

  3. Введите в ячейки В1:В5 исходные данные (см. условие задачи).

  4. Вычислите функцию БС по формуле =БС(В1/12;В2*12;В3;В4;В5).

Результат - 6436,74р.

Задание 4

Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы.

Контрольные вопросы:

  1. При решении, каких задач применяют технологию подбора параметра?

  2. Каким образом вызывается команда Подбор параметра?

  3. Из каких информационных полей состоит диалоговое окно Подбора параметра?

  4. Что должна содержать ячейка, для которой вызывается команда Подбор параметра?

  5. Почему при выполнении Задания 2 ячейка D3 задаётся в виде абсолютной адресации?

5


© 2010-2022