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

>          шаг h=0,25

      необходимо:

1. Разработать алгоритм нахождения значений заданной кусочно-ломаной функции. Оформить алгоритм на рабочем листе с помощью встроенных средств Microsoft Excel.
2. На основании алгоритма построить электронную таблицу для вычисления значений кусочно-ломаной функции в диапазоне двух периодов, с заданным шагом.

       При построении формулы для вычисления значений функции использовать встроенные функции Microsoft Excel ЕСЛИ и ОСТАТ.

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

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. Постановка  задачи

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

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

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

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

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

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

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

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

 

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

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

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

4. Задание 4

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

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

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, равна соответственно 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. Разработка  макета электронной таблицы

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