Главная страница
Навигация по странице:

  • Задание 2.

  • Тип заготовки Количество заготовок Способ 1 раскроя

  • Задание 4.

  • R=

  • Задание 1. Поиск решения

  • $I$8 Изменять надо ячейки, содержащие количество ставок

  • Ячейка

  • Методичка_Excel_(03.10.2014). Методичка_Excel_(03.10. Лабораторная работа 4 4 Лабораторная работа 4 Задание Задание (10 баллов) Поиск решения


    Скачать 0.83 Mb.
    НазваниеЛабораторная работа 4 4 Лабораторная работа 4 Задание Задание (10 баллов) Поиск решения
    АнкорМетодичка_Excel_(03.10.2014).doc
    Дата15.05.2018
    Размер0.83 Mb.
    Формат файлаdoc
    Имя файлаМетодичка_Excel_(03.10.2014).doc
    ТипЛабораторная работа
    #17315

    Содержание


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


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




    Задание



    Задание 1. (10 баллов) Поиск решения

    На фирме могут работать директор, главный бухгалтер, ведущие инженеры и специалисты.

    Известно, что директора и главного бухгалтера может быть только по 1 человеку, ведущих инженеров может быть не более 5, но один – обязательно, специалистов – не более 10 и не менее 2 человек.

    Зарплата каждого сотрудника зависит от разряда. Таблица разрядов и окладов задана. Известно, что директор тарифицируется по 9 разряду, главный бухгалтер – по 8 разряду, ведущий инженер – по 7 разряду, специалист – по 5 разряду.

    Требуется определить количество сотрудников фирмы так, чтобы суммарная заработная плата не превысила 13000 рублей."
    Задание 2. (10 баллов) Моделирование задачи линейного программирования

    В ходе производственного процесса из листов материала получают заготовки деталей двух типов А и Б тремя различными способами, при этом количество получаемых заготовок при каждом методе различается (данные в таблице):

    Тип заготовки

    Количество заготовок

    Способ 1 раскроя

    Способ 2 раскроя

    Способ 3 раскроя

    А

    6

    4

    10

    Б

    2

    5

    3

    Необходимо выбрать оптимальное сочетание способов раскроя, для того чтобы получить 600 заготовок первого типа и 400 заготовок второго типа при расходовании наименьшего количества листов материала.

    Задание 3 . Моделирование задач линейного программирования


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

    Представить решение каждого пункта на отдельном листе.

    Пункт А) (3 балла)

    Тип заготовки

    Количество заготовок

    Способ 1 раскроя

    Способ 2 раскроя

    Способ 3 раскроя

    А=500

    8

    2

    6

    Б=362

    7

    1

    4

    Пункт Б) (3 балла)

    Тип заготовки

    Количество заготовок

    Способ 1 раскроя

    Способ 2 раскроя

    Способ 3 раскроя

    А=645

    4

    3

    2

    Б=715

    2

    4

    5

    В=590

    5

    2

    1

    Пункт В) (3 балла)

    Тип заготовки

    Количество заготовок

    Способ 1 раскроя

    Способ 2 раскроя

    Способ 3 раскроя

    Способ 4 раскроя

    А=1085

    7

    6

    4

    5

    Б=1105

    6

    3

    7

    4

    В=1250

    8

    5

    4

    7


    Пункт Г) (3 балла) Самостоятельно подобрать исходные данные так, чтобы было 4 типа заготовки и 3 способа раскроя.

    Задание 4. (15 баллов) Расчет стипендии учебной группы

    Необходимо составить расчетную ведомость на выдачу стипендии группе студентов, условно состоящей из 5 человек. Исходными данными являются:

    1. Общий стипендиальный фонд, выделенный на группу (рубли);

    2. Минимальная стипендия M (рубли),

    3. Студенческий коэффициент K, на который умножается минимальная стипендия для вычисления значения стипендии каждого студента;

    4. Успеваемость каждого студента, выраженная в виде его среднего рейтинга R за семестр;

    5. Количество двоек Д каждого студента за сессию.

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

    Для решения задачи используются следующие формулы:

    Стипендия = Общая стипендия + Добавочная стипендия










    Ф(1)

    Общая стипендия =









    Ф(2)

    Добавочная стипендии =

    Баллы, превышающие R=35



    Добавка за 1 балл


    Ф(3)

    Баллы, превышающие R=35

    =






    Ф(4)

    Найдем добавку за 1 балл:













    Общий фонд = Сумма общих стипендий










    Ф(5)

    Добавочный фонд = Стипендиальный фонд - Общий фонд










    Ф(6)

    Добавка за

    1 балл

    =

    Добавочный фонд

    Сумма баллов, превышающих R=35




    Ф(7)


    Задание 5. (5 баллов) Расчет стипендии учебной группы на 10 человек

    Провести аналогичный расчет стипендии для группы минимум из 10 студентов и при условии, что стипендиальный фонд на группу равен 650000 руб., минимальная стипендия составляет 42500 руб., студенческий коэффициент равен 1,52.

    Методические указания к выполнению 4 лабораторной работы



    Задание 1. Поиск решения

    Для решения создать таблицу с начальными данными:

    Количество ставок по должностям вначале принимает значение 0.

    Зарплата каждого сотрудника зависит от разряда и количества ставок. Поэтому в ячейку I3 надо занести формулу:
    =ПРОСМОТР(H3;$B$3:$B$11;$C$3:$C$11)*G3
    В остальные ячейки столбца "Зар.плата" скопировать эту формулу.
    В ячейку G7 занести формулу суммирования: =СУММ(G3:G6)
    В ячейку I8 занести формулу: =СУММ(I3:I6)
    Используя специальную возможность Excel "Поиск решения" (Если такая функция отсутствует в меню "Параметры Excel", следует выбрать "Надстройки", далее установить галочку напротив позиции "Поиск решения"), найти решение задачи.






    Окно запроса "Поиск решения" предлагает установить целевую ячейку, задать изменяемые ячейки и ограничения.

    Для данной задачи надо задать целевую ячейку I8 – общая сумма зарплат. В нашей задаче общая сумма зарплат не должна превышать 13000, поэтому выбрать надо переключатель "максимальное значение" и задать условие отбора ("Ограничения")
    $I$8<=13000
    Изменять надо ячейки, содержащие количество ставок, - G3:G6.

    Задать ограничения можно, используя кнопку "Добавить"

    Все значения ячеек G3:G6 – должны быть целочисленными:


    В ячейках G3 и G4 должно быть число 1.

    Значение в ячейкеG5 должно быть больше и равным 1 и меньшим или равным 5.

    Значение в ячейке G6 должно быть больше и равным 2 и меньшим или равным 10.

    На следующем рисунке приведено окно запроса "Поиск решения" с установленными параметрами задачи.


    Параметры задачи
    Чтобы получить результат, достаточно нажать кнопку "Выполнить". После чего Excel, используя алгоритм перебора, подбирает значения в изменяемых ячейках так, чтобы в целевой ячейке было максимальное значение. Если решение будет найдено, то надо сохранить найденное решение и Excel выдаст результат поиска.



    Решение найдено


    Результат решения задачи

    Задание 2. (10 баллов) Моделирование задачи линейного программирования
    Математическая модель задачи

    Параметрами, значения которых требуется определить, являются количества листов материала, которые будут раскроены различными способами:

    X1 – количество листов, раскроенное способом 1;

    X2 – количество листов, раскроенное способом 2;

    X3 – количество листов, раскроенное способом 3;
    Тогда целевая функция, значением которой является количество листов материала, примет вид:

    F=X1 + X2 + X3
    Ограничения определяются значениями требуемых количеств заготовок типа А и Б, тогда с учетом количеств заготовок, получаемых различными способами, должны выполняться два равенства:

    6X1 + 4X2 + 10X3 = 600

    2X1 + 5X2 + 3X3 = 400
    Кроме того, количества листов не могут быть отрицательными!
    Таким образом, необходимо найти удовлетворяющие ограничениям значения параметров, при которых целевая функция принимает минимальное значение.

    Получили задачу линейного программирования.
    Построим проект таблицы:


    Проект таблицы
    В ячейки B2, C2, D2 занести число 0, т.к. количество листов не может быть отрицательным, но может отсутствовать в заготовке. В ячейки B3, B4 занести ограничения на количество заготовок типа А (600) и типа В (400), заданные по условию задачи.

    Записать формулы для расчета.


    Ячейка

    Вид формулы в Excel

    Примечание

    B5

    =6*B2+4*C2+10*D2

    ограничение на заготовки типа А

    B6

    =2*B2+5*C2+3*D2

    ограничение на заготовки типа В

    E2

    =B2+C2+D2

    целевая функция


    Задать условия решения задачи с помощью инструмента Поиск решения:

      • Установить целевую ячейку E2, определить ее значение как минимальное.

      • Изменяться должны ячейки B2, C2, D2.

      • Ограничения:

      • изменяемые ячейки должны быть целыми и положительными,

      • значение ячейки B5 должно быть равным количеству заготовок типа А, т.е. равным значению ячейки B3,

      • значение ячейки B6 должно быть равным количеству заготовок типа В, т.е. равным значению ячейки B4.

    Получить решение, дав команду "Выполнить".


    Задание условий для поиска решения
    Дав команду "Выполнить", сохранив решение и построив диаграмму, получаем решение задачи:



    Решение задачи

    Задание 3 . Моделирование задач линейного программирования



    Далее можно проводить эксперименты. Например, если задать значения А=700 и В=300, то решение задачи будет следующим:

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

    Следующее действие – поставить галочку в поле "Создавать копию".

    В результате появится новый лист – полная копия исходного листа, в котором можно менять значения и получать новые данные для экспериментов.

    Задание 4. (15 баллов) Расчет стипендии учебной группы

    По материалам лекции
    Задание 5. (5 баллов) Расчет стипендии учебной группы на 10 человек

    По материалам лекции. Самостоятельно.




    написать администратору сайта