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

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

Скачать материал

Лабораторная работа №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 автосуммированием вычислите суммарный фонд заработной платы фирмы.

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

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

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

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

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

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

 

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

Анализ задач показывает, что с помощью 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 задаётся в виде абсолютной адресации?

 

Просмотрено: 0%
Просмотрено: 0%
Скачать материал
Скачать материал "Лабораторная работа "ИТ подбора параметра" по дисциплине "Информационные технологии в профессиональной деятельности""

Методические разработки к Вашему уроку:

Получите новую специальность за 2 месяца

Директор школы

Получите профессию

Технолог-калькулятор общественного питания

за 6 месяцев

Пройти курс

Рабочие листы
к вашим урокам

Скачать

Скачать материал

Найдите материал к любому уроку, указав свой предмет (категорию), класс, учебник и тему:

6 663 990 материалов в базе

Скачать материал

Другие материалы

Вам будут интересны эти курсы:

Оставьте свой комментарий

Авторизуйтесь, чтобы задавать вопросы.

  • Скачать материал
    • 29.01.2016 2972
    • DOCX 123.7 кбайт
    • 20 скачиваний
    • Рейтинг: 5 из 5
    • Оцените материал:
  • Настоящий материал опубликован пользователем Глухова Людмила Алексеевна. Инфоурок является информационным посредником и предоставляет пользователям возможность размещать на сайте методические материалы. Всю ответственность за опубликованные материалы, содержащиеся в них сведения, а также за соблюдение авторских прав несут пользователи, загрузившие материал на сайт

    Если Вы считаете, что материал нарушает авторские права либо по каким-то другим причинам должен быть удален с сайта, Вы можете оставить жалобу на материал.

    Удалить материал
  • Автор материала

    Глухова Людмила Алексеевна
    Глухова Людмила Алексеевна
    • На сайте: 8 лет и 3 месяца
    • Подписчики: 1
    • Всего просмотров: 22208
    • Всего материалов: 10

Ваша скидка на курсы

40%
Скидка для нового слушателя. Войдите на сайт, чтобы применить скидку к любому курсу
Курсы со скидкой

Курс профессиональной переподготовки

Няня

Няня

500/1000 ч.

Подать заявку О курсе

Курс профессиональной переподготовки

Математика и информатика: теория и методика преподавания в профессиональном образовании

Преподаватель математики и информатики

500/1000 ч.

от 8900 руб. от 4150 руб.
Подать заявку О курсе
  • Сейчас обучается 41 человек из 23 регионов
  • Этот курс уже прошли 53 человека

Курс профессиональной переподготовки

Информационные системы и технологии: теория и методика преподавания в профессиональном образовании

Преподаватель информационных систем и технологий

300/600 ч.

от 7900 руб. от 3650 руб.
Подать заявку О курсе
  • Этот курс уже прошли 13 человек

Курс профессиональной переподготовки

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

Специалист по формированию электронного архива

600 ч.

9840 руб. 5600 руб.
Подать заявку О курсе
  • Сейчас обучается 30 человек из 22 регионов
  • Этот курс уже прошли 36 человек

Мини-курс

Психологические концепции и практики

6 ч.

780 руб. 390 руб.
Подать заявку О курсе
  • Сейчас обучается 24 человека из 16 регионов

Мини-курс

Основы дизайна в Figma

4 ч.

780 руб. 390 руб.
Подать заявку О курсе
  • Сейчас обучается 83 человека из 27 регионов
  • Этот курс уже прошли 11 человек

Мини-курс

Психология и профессиональное развитие

6 ч.

780 руб. 390 руб.
Подать заявку О курсе
  • Сейчас обучается 21 человек из 15 регионов