Автоматизация решения задач пользователя

       По  табличным данным с помощью Мастера  диаграмм построить график функции (тип диаграммы – точечная).

2. Разработка  алгоритма

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

         

Рисунок 20 - Алгоритм нахождения значений функции

 

3. Разработка  макета электронной таблицы

         Для решения задачи на основе ее данных необходимо построить  электронную таблицу. Таблица включает следующие данные:

• столбец значений аргумента Х от 0 до 4 с шагом 0,25
• столбец предполагаемых значений функции

 

       Макет электронной таблицы представлен  на рисунке 21

Рисунок 21 – Макет электронной таблицы 

       Формулы, записанные в ячейках, представлены на рисунке 22.

Рисунок 22 – Электронная таблица с  формулами 

4. Вычисления  в электронной таблице

       Вычисленные значения функции Y(X) представлены на рисунке 23 

Рисунок 23 – Таблица с численными результатами

5. Построение  графика функции

       С помощью Мастера диаграмм Microsoft Excel на основе табличных данных строим точечную диаграмму кусочно-ломаной функции, представленную на рисунке 24

Рисунок 24 – Точечная диаграмма заданной функции

3. Задание 3

1. Постановка  задачи

      Используя команду Подбор параметра из меню Сервис, найти значение Х, при котором функция возвратит заданное значение.

      Результат отобразить в виде таблицы:

Переменная			Функция	Результат
x	y	z
	0,5	3,75	=y+ez X	27,7

2. Разработка  макета электронной таблицы

       Для решения задачи на основе ее данных необходимо построить следующую электронную таблицу, представленная на рисунке 25(ячейка D3 содержит формулу):

Рисунок 25 – Электронная таблица с данными

3. Вычисления  в электронной таблице.

      Используя команду Подбор параметра из меню Сервис, в ячейке A3 находим значение Х, при котором функция возвратит заданное значение.

      Порядок заполнения диалогового окна Подбор параметра указан на рисунке 26

 

Рисунок 26 – Порядок заполнения диалогового  окна Подбор параметра 

       Результат вычислений указан на рисунке 27

Рисунок 27 – Электронная таблица с  численными результатами 

4. Задание 4

1. Постановка  задачи

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

Число Имеют Число Число Мин. Средний балл Студентов детей зачетов экзаменов стипендия, руб. 3,5-4,0 4,1-4,5 4,6-5,0           повышающий  коэффициент 12 1 3 3 30 100 1,0 1,4 1,75

2. Анализ  предметной области

       Имеются 12 студентов, у которых в зависимости  от набранных баллов по трем зачетам  и трем экзаменам выплачивается  стипендия. По итогам сессии определяется средний балл, от которого зависит повышающий коэффициент, а, следовательно, и конечная сумма стипендии. Она определяется произведением минимальной стипендии, составляющая 30100 рублей на повышающий коэффициент. У студента, имеющего ребенка выплачивается на него минимальная стипендия.

3. Разработка  макета электронной таблицы

       Для решения задачи на основе ее данных необходимо построить электронную таблицу, представленную на рисунке 28. Таблица включает следующие данные:

Рисунок – 28 Макет электронной таблицы 

       Формулы, записанные в ячейках, представлены ниже:

       I3=СРЗНАЧ(F3:H3), данная формула скопирована в ячейки I4:I14

K3=ЕСЛИ(И(C3="зачтено";D3="зачтено";E3="зачтено");ЕСЛИ(И(I3>=3,5;I3<=4);$B$19*J3+$B$19+$D$19;ЕСЛИ(И(I3>=4,1;I3<=4,5);$B$19*$D$19+$B$19*J3;ЕСЛИ(И(I3>=4,6;I3<=5);$B$19*$E$19+$B$19*J3)))). данная формула скопирована в ячейки K4:K14

 

4. Вычисления  в электронной таблице, представлены на рисунке 29

 

 

Рисунок 29 – Таблица с численными результатами

5. Графическое отображение результатов

       С помощью Мастера диаграмм Microsoft Excel на основе табличных данных строим диаграмму, представленную на рисунке 30 (тип диаграммы - гистограмма): 

Рисунок 30 – Гистограмма табличных данных

 

5. Задание 5

1. Постановка  задачи

       Используя надстройку Поиск решения решить задачу оптимизации:

       Для изготовления двух видов продукции  Р1 и Р2 используют четыре вида ресурсов S1, S2, S3, S4. Запасы ресурсов, число единиц ресурсов, затрачиваемых на изготовление единицы продукции, представлены в таблице.

Вид ресурса	Запас ресурса	Число единиц ресурсов, затрачиваемых на изготовление единицы продукции
		Р1	Р2
S1	18	1	3
S2	16	2	1
S3	5	-	1
S4	21	3	-

       Прибыль, получаемая от реализации единицы продукции  Р1 и Р2, равна соответственно 2 и 3 у.е. Необходимо составить такой план производства продукции, при котором прибыль от ее реализации будет максимальной.

2. Разработка  математической модели задачи

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

• целевая функция: F(X_i), i = 1, n → min, max, const
• ограничение: g(X_i), i = 1, n (<=>) b
• граничные условия для искомых переменных: 
  X_i, i = 1, n → цел; X_i, i = 1, n > 0.

       Ц.Ф.:  F(X_i), i = 1, n → min, max, const

       ОГР:  g(X_i), i = 1, n (<=>) b

       ГРУ:  X_i, i = 1, n → цел

       X_i, i = 1, n > 0

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

       Обозначим искомые величины (количество единиц продукции Р1 и Р2 в оптимальном плане) как: Х₁, Х₂.

       Тогда целевая функция (прибыль от реализации продукции) примет вид:

       F(Х₁, Х₂)=2Х₁+3Х₂→max

       По  условию задачи известны запасы ресурсов и число единиц ресурсов, затрачиваемых на изготовление единицы продукции каждого вида.

       С учетом этого запишем ограничения:

   для ресурса S1:  Х₁+3Х₂<=18

   для ресурса S2: 2Х₁+ Х₂<=16

   для ресурса S3:          Х₂<=18

   для ресурса S4: 3Х₁       <=18

 

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

       По  условию задачи: X_1, X₂ - целые

                        X_1, X₂ >0

       В соответствии с вышесказанным математическая модель задачи примет вид

        Ц.ф.: F(Х₁, Х₂)=2Х₁+3Х₂→max

       ОГР:   Х₁+3Х₂<=18

              2Х₁+ Х₂<=16

                       Х₂<=18

              3Х₁       <=18

       ГРУ: X_1, X₂ - целые

             X_1, X₂ >0

3. Разработка  макета электронной таблицы

       Для решения задачи на основе ее данных необходимо построить электронную таблицу. Таблица включает следующие данные:

• условие задачи
• строку предполагаемого количества единиц продукции Р1 и Р2 (искомые значения Х₁, Х₂)
• колонку с формулами ограничения
• ячейку с целевой функцией (D4)

      Макет электронной таблицы представлен на рисунке 31 

Рисунок 31 - Макет электронной таблицы 

Формулы, записанные в ячейках, представлены ниже:

D4 =СУММПРОИЗВ($B$3:$C$3;B4:C4)    прибыль от реализации продукции

D7 =СУММПРОИЗВ($B$3:$C$3;B7:C7)

D8 =СУММПРОИЗВ($B$3:$C$3;B8:C8)

D9 =СУММПРОИЗВ($B$3:$C$3;B9:C9)

D10=СУММПРОИЗВ($B$3:$C$3;B10:C10)

4. Расчет  в электронной таблице

       Таблица со значениями после ввода формул показана на рисунке 32

Рисунок 32 – Исходные данные для расчета 

       Для проведения расчетов выделяем ячейку D4, содержащую целевую функцию, и вызываем Поиск решения c помощью команды меню Сервис. В диалоговое окно, представленное на рисунке 33 вводим данные с учетом максимизации целевой функции. В поле Ограничения записывается следующее:

• по условию задачи величина использования каждого вида ресурса не может превышать его запаса

Рисунок 33 – Ввод данных в диалоговое окно Поиска решения 

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