35375

РЕЖИМ ПОДБОРА ПАРАМЕТРОВ

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

Информатика, кибернетика и программирование

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

Русский

2014-03-24

882.57 KB

52 чел.

ТЕМА 4. РЕЖИМ ПОДБОРА ПАРАМЕТРОВ

Цель работы: освоить методы решения линейных уравнений с одной переменой с использованием режима подбора параметров2 и применять полученные знания при решении экономических задач.

Примеры.

Задание 1. Используя режим Подбора параметра, определите, при каком значении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 250 000 руб.

Исходные данные этого примера приведены на рис. 1.22, где знаком «?» отмечены ячейки с расчетными данными.

Решение. Создайте таблицу расчета заработной платы по образцу (рис. 1.22).

Произведите расчёты во всех столбцах таблицы.

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

Рис. 1.22. Ведомость начисления заработной платы

Формула для расчёта столбца «Всего начислено»: Всего начислено = Оклад + Премия.

Формула для расчёта столбца «Удержания»: Удержания = Всего начислено * % Удержаний, в ячейке F6 наберите =$F$5*Е6. Далее эта формула копируется вниз по столбцу F автозаполнением.

Формула для расчёта столбца «К выдаче»: К выдаче = Всего начислено — Удержания.

          По столбцу К выдаче рассчитайте среднее значение, найдите максимальное значение и минимальное значение. Для этого воспользуйтесь функциями «СРЗНАЧ», «МАКС», «МИН» соответственно. Откройте окно Мастер функций, выберите категорию – СТАТИСТИЧЕСКИЕ, функцию СРЗНАЧ. В строку 1 введите диапазон - столбец К выдаче (без итоговой суммы). Аналогично работают функции «МАКС», «МИН».

Результаты работы и итоговый вид таблицы для расчёта заработной платы представлены на рис. 1.23.

Из расчетов видно, что общая сумма к выдаче при указанных окладах и премии в размере 27 % составляет 104 799,77 руб. Теперь осуществите подбор параметра командой, для чего установите курсор в ячейке общей суммы К выдаче и воспользуйтесь меню СЕРВИС - командой Подбор параметра. Откроется окно, как на рис. 1.24.

Рис.1.23. Ведомость начисления заработной платы

                

Рис. 1.24.  Подбор параметра      Рис. 1.25. Подбор параметра

В диалоговом окне «Подбор параметра» на первой строке Установить в ячейке в качестве подбираемого параметра должен находиться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячейка G20, на которой установлен курсор). На второй строке у поля «Значение» диалогового окна «Подбор параметра» наберите желаемое значение параметра, в данном примере это число равно 250 000, на третьей строке в поле «Изменяя значение ячейки» укажите адрес подбираемого значения «% Премии» (ячейка D5), после чего нажмите кнопку ОК (рис. 1.25).

Произойдёт почти моментальный пересчёт всей таблицы, и откроется окно «Результат подбора параметра» (рис. 1.26), в котором дайте подтверждение подобранному параметру нажатием ОК.

Итак, произошёл обратный пересчёт «% Премии». Результаты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%.

Рис 1.26. Таблица расчета заработной платы

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

Известно, что в штате фирмы состоят:

6 курьеров;

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

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

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

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

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

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

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

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

Решение. Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата равна А * Z + В,

где Z оклад курьера;

А и В — коэффициенты, показывающие соответственно, во сколько раз превышается значение Z и на сколько превышается значение Z.

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

Рис. 1.27. Штатное расписание фирмы (таблица исходных данных)

В столбце Коэф. В указаны надбавки к окладам.

Выделите отдельную ячейку D17 для зарплаты курьера (переменная Z) и с учётом этого задайте все расчёты. В ячейку D17 временно введите произвольное число, например 500.

В столбце Зарплата сотрудника введите формулу для расчёта заработной платы по каждой должности. Далее скопируйте формулу из ячейки E4 вниз по столбцу автозаполнением.

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

Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы в размере 100 000 руб. Для этого в строке МЕНЮ выберите СЕРВИС - ПОДБОР ПАРАМЕТРА.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку G12, содержащую формулу расчёта фонда заработной платы; в поле Значение наберите искомый результат — 100000; в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D17, в которой находится значение зарплаты курьера, и щелкните на кнопке ОК. Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб. (рис. 1.28).

Рис. 1.28. Штатное расписание фирмы

Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.

Следует заметить, что если возникает необходимость копировать результаты, полученные подбором параметра, то следует производить копирование полученных данных в виде значений с использованием СПЕЦИАЛЬНОЙ ВСТАВКИ.

Для этого необходимо выделить копируемые данные, произвести запись в буфер памяти (ПРАВКА > КОПИРОВАТЬ), установить курсор в соответствующую ячейку, задать режим специальной вставки (ПРАВКА > СПЕЦИАЛЬНАЯ ВСТАВКА), отметив в качестве объекта вставки "значения" (ПРАВКА — СПЕЦИАЛЬНАЯ ВСТАВКА — ЗНАЧЕНИЯ) (рис. 1.29).

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

Рис. 1.29. Специальная вставка

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

Для решения каких задач используется режим ПОДБОРА ПАРАМЕТРОВ?

Адрес какой ячейки должен находиться в диалоговом окне «Подбор параметра» в первой строке?

В каких случаях следует производить копирование данных с использованием специальной вставки?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 4

Задача 1. Имея исходные данные, рассчитайте сумму премии и сумму к выдаче. При каком проценте премии общая сумма к выдаче будет равна 100000 руб. Рассчитайте средний доход за месяц.

Рис. 1.30. Ведомость заработной платы

Задача 2. Рассчитайте сумму оклада (МРОТ*коэф*разряд), сумму премии (50% от оклада), удержаний в пенсионный фонд (28% от оклада + премия) и сумму к выдаче. Определите, каким должен быть коэффициент, чтобы итоговая сумма к выдаче составила 200000 руб. Исходные данные приведены в таблице на рис. 1.31.

Рис. 1.31. Штат сотрудников фирмы

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

Процесс решения с помощью данного метода распадается на два этапа:

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

2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра и получение ответа (или сообщения о его отсутствии/Невозможности найти).

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


 

А также другие работы, которые могут Вас заинтересовать

25956. Основные конструктивные элементы здания – горизонтальные (перекрытия, покрытия), вертикальные (стены, колонны) и фундаменты, взятые вместе, составляют единую пространственную систему – несущий остов здания 12.15 KB
  Основное назначение несущего остова конструктивной основы здания состоит в восприятии нагрузок действующих на здание работе на усилия от этих нагрузок с обеспечением конструкциям необходимых эксплуатационных качеств в течение всего срока их службы. Конструктивная система представляет собой взаимосвязанную совокупность вертикальных и горизонтальных несущих конструкций здания которые совместно обеспечивают его прочность жёсткость и устойчивость. Горизонтальные конструкции перекрытия и покрытия здания воспринимают приходящиеся на них...
25957. Реконструкция объектов капитального строительства 12.01 KB
  Реконструкция стен здания: Уменьшение несущей способности стен дома происходит изза влияния факторов влияющих на фундамент. Реконструкция фасадов Усиление каменной кирпичной кладки стен Реконструкция стропильной системы и кровельного покрытия Собственно крыша и ее верхний слой кровля подвержены постоянному влиянию большого количества агрессивных факторов. При покрытии кровли мягким материалом при небольших дефектах выполняются заплатки а при износе демонтируется все покрытиеи после этого выполняется полная реконструкция крыши.
25958. Крупноблочные конструкции 27.5 KB
  Из крупных блоков могут быть смонтированы различные части здания: фундаменты наружные и внутренние стены перегородки и т. ленточных фундаментов и стен подвалов могут применяться не только в крупноблочных домах но и в зданиях с кирпичными и крупнопанельными конструкциями См. наружных стен зданий из блоков изготовленных на основе лёгких и ячеистых бетонов шлакобетон керамзитобетон газобетон и др. Толщина крупноблочных стен назначается от 30 до 60 см в зависимости от теплотехнических и прочностных свойств материала блока и от...
25959. Стены из крупных легкобетонных блоков 27.5 KB
  В наружных стенах из крупных легкобетонных блоков показанных на чертежах типоразмеры основных элементов кладки назначены исходя из двухрядной разрезки в пределах этажа высотой 28 м. Блоки подразделяются на наружные простеночные рядовые и угловые поясные и перемычные подоконные. Внутренние стены возводятся из крупных бетонных блоков однорядной разрезки. Блоки подразделяются на внутренние стеновые перемычные вентиляционные специальные.
25960. Детали сопряжений крупноблочных стен 23 KB
  Для этого в углы стеновых панелей и в элементы каркаса при изготовлении закладывают стальные пластинки закладные детали к которым приваривают связывающие их стержни. Поэтому при использовании сварки для соединения панелей и связи панелей с каркасом необходимо очень тщательно выполнять требования по антикоррозийной защите сварных узлов.
25961. Детали стыков стен из легкобетонных блоков 23 KB
  Такие стыки обеспечивают наибольшую прочность и жесткость сопряжения а также надежную защиту от коррозии. Вертикальные и горизонтальные стыки стеновых панелей необходимо тщательно защищать от проникновения влаги и продувания. С этой целью при монтаже крупнопанельных зданий стыки герметизируют: всю линию вертикального стыка с внутренней стороны оклеивают рулонным материалом и защищают утепляющим вкладышем из пенополистирола или из пакета минераловатных плит обернутых пергамином. С наружной стороны в горизонтальные и вертикальные стыки вводят...
25962. Крупнопанельные конструкции 28 KB
  Пространственная жесткость и устойчивость этих зданий обеспечивается взаимной связью между панелями наружных и внутренних стен и панелями перекрытий. Бескаркасные панельные здания могут иметь четыре конструктивных варианта: с тремя продольными несущими стенами двумя наружными и одной внутренней с опиранием перекрытий по двум коротким сторонам; с несущими наружными стенами и внутренними продольными и поперечными с опиранием панелей перекрытий по контуру ; с несущими наружными степами и внутренними поперечными с опиранием перекрытий по трем...
25963. Основные конструкций крупнопанельного здания 28 KB
  Панели двух и трехслойные виброкирпичные панели с применением пластических масс являются разновидностями двух указанных основных групп. Однослойные панели в сравнении с многослойными требуют меньше металла менее трудоемки в изготовлении обеспечивают теплотехнический режим в помещении в таких стенах меньше мостиков холода достаточно прочны. В двухслойной панели одна скорлупа и слой утеплителя опасность накопления влаги в утеплителе не изолированном железобетонной плитой больше чем в трехслойной. Однослойные панели могут быть...