Лабораторная работа № 1
Решение уравнений средствами программы Microsoft Excel: «подбор параметра» и «поиск решения»
Цель работы: приобрести навыки решения уравнений средствами программы Microsoft Excel: «Подбор параметра» и «Поиск решения»
Сведения из теории
Программа Microsoft Excel относится к классу программ, называемых электронными таблицами. Электронные таблицы ориентированы, прежде всего, на решение экономических и инженерных задач, позволяют систематизировать данные из любой сферы деятельности.
Программа Microsoft Excel позволяет:
а) сформировать данные в виде таблиц;
б) рассчитать содержимое ячеек по формулам, при этом возможно использование более 300 встроенных функций;
в) представить данные из таблиц в графическом виде;
г) организовать данные в конструкции, близкие по возможностям, к базе данных.
Решение уравнений с одной переменной
Задачи, в которых используются уравнения с одной переменной можно решать методом перебора, однако в лучшем случае на это уходит много времени.
Можно предложить другие способы решения. В Excel они реализованы как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.
 |
Эту процедуру используют для поиска такого значения ячейки, при котором значение другой ячейки, вычисляемое по формуле, заранее задано. В формуле должна быть ссылка на ячейку, значения которой ищут. Ограничения на искомое значение ячейки не налагают.
Познакомимся с этой процедурой на примере составления штатного расписания.
Пусть известно, что в штате отдела ДПС состоит 15 постовых, 3 старших наряда, 1 ком. взвода, начальник отделения, зам. нач. отделения, бухгалтер, зам.нач ДПС, начальник ДПС. Общий месячный фонд зарплаты составляет 70000 руб.. Необходимо определить, какими должны быть оклады сотрудников отдела.
Построим модель решения этой задачи. За основу возьмем оклад постового, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: Ai*C+Bb где С - оклад постового; А, и В; - коэффициенты, которые для каждой должности определяют следующим образом:
• Старший наряда получает в 1,5 раза больше постовой (А2=1,5: В2=0);
• Ком. взвода - в 3 раза больше постового (В3=0; А3=3);
• Нач. отделения - на 300 руб. больше, чем ком. взвода (А4=3; В4=300);
• Зам. нач. отделения - в 2 раза больше постового (A5=2; B5=0);
• бухгалтер - на 400 руб. больше старшего наряда (А6,= 1,5: В6,=400);
• зам. нач. ДПС - в 4 раза больше постового (А7-4; В7=0);
• начальник ДПС - на 200 руб. больше зам. нач. ДПС (A8=4; B8=200);
Зная количество человек на каждой должности, нашу модель можно записать как уравнение:
N1*A1*C+N2*(A2*C+B2)+...+N8*(A8*C+B8)= 70000 руб,
где N1 - число постовых. N2 – старших наряда и т.д.
В этом уравнении нам известны A1..A8, В1...В8 и N1... N8. а С неизвестно.
Анализ уравнения показывает, что задача составления расписания свелась к решению линейного уравнения относительно С. Решим его.
Введём исходные данные в рабочий лист электронной таблицы, как показано на рисунке 1.
Рисунок 1
В столбце D вычислим заработную плату для каждой должности. Например, дляячейки D3 формула расчета имеет вид =В3*$G$6+С3.
В столбце F вычислим заработную плату всех рабочих данной должности. Например, для ячейки F3 формула расчета имеет вид =D3*E3.
В ячейке F11 вычислите суммарный фонд заработной платы отдела ДП
С. Рабочий лист электронной таблицы будет выглядеть, как показано на рисунке 2.
Рисунок 2
Определим оклад постового так, чтобы расчетный фонд был равен заданному:
а) активизируем команду Подбор параметра из меню Сервис,
б) в поле "Установить в ячейке" появившегося окна введём ссылку на ячейку F11. содержащую формулу;
в) в поле "Значение" наберём искомый результат 70000;
г) в поле "изменяя значение ячейки" введём ссылку на изменяемую ячейку G6 и щелкните на кнопке ОК.
Сохраним таблицу. Анализ задачи показывает, что с помощью Excel можно решать линейные уравнения. Благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, - это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.
Рисунок 3
Задачи оптимизации
В предыдущем подразделе, мы рассмотрели задачу поиска значения параметра, позволяющего достичь конкретной цели.
Решаемые задачи могут быть более сложными. Например, поиск нескольких параметров, обеспечивающих некоторый наперед заданный результат.
Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как минимизировать затраты на содержание персонала.
Такие задачи в Excel решают с помощью Поиска решения.