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

  • Решим следующую задачу

  • Подбор параметра. Задание

  • Задание для самостоятельной работы

  • Подбор параметра

  • Сырье Нормы расхода сырья Запас сырья

    		•

    Поиск решений. Пр. работа_Поиск решений. Занятие по теме Подбор параметра и оптимизация (поиск решений) в Excel


    Скачать 73.26 Kb.
    НазваниеЗанятие по теме Подбор параметра и оптимизация (поиск решений) в Excel
    АнкорПоиск решений
    Дата12.10.2021
    Размер73.26 Kb.
    Формат файлаdocx
    Имя файлаПр. работа_Поиск решений.docx
    ТипЗанятие
    #245769

    Практическое занятие по теме: «Подбор параметра и оптимизация (поиск решений) в Excel»



    1. Подбор параметра


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

    Решим следующую задачу:

    Вы хотите положить деньги в банк под 4,5% и получить ровно 1000 руб. по истечении года. Необходимо определить сумму вклада. Для решения данной задачи используем средство Подбор параметра.

    Задание:

    1. Создайте новый файл под именем Лаб.раб. Лист 1 переименуйте в Подбор.

    2. Оформите таблицу (рис. 1). Введите указанную формулу.



    Рис.1

    1. Активизируйте ячейку В3. Выполните команду Данные - -Анализ «что- если» Подбор параметра.

    2. В открывшемся диалоговом окне укажите значение необходимой конечной суммы и ссылку на ячейку с искомым значением вклада (рис.2).



    Рис.2

    1. Нажмите кнопку ОК. Средство подбор параметра найдет решение и сообщит об этом (рис. 3). Нажмите кнопку ОК и убедитесь, что искомое значение помещено в ячейке В2.



    Рис.3

    Задание для самостоятельной работы:

    1. Решите задачу: Для покупки автомобиля Вам необходима сумма 200 000 руб. У Вас есть возможность взять ипотечную ссуду, при этом нужно сделать первый взнос 20%. Определите, какую сумму нужно взять в банке, чтобы на руки вы получили требуемую сумму.

    2. Для решения задачи оформите таблицу (рис.4). Введите формулы.



    Рис.4

    1. С помощью средства Подбор параметра определите размер ссуды.


    Задачи оптимизации (поиск решения)

    Цель: Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).

    Задание 1. Составление плана выгодного производства.

    Фирма производит несколько видов продукции из одного и того же сырья – А, В, С. Реализация продукции А дает прибыль 10р., В – 15р., С-20р. На единицу изделия. Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.

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

    Сырье
    Нормы расхода сырья
    Запас сырья

    А
    В
    С

    Сырье 1
    18
    15
    12
    350

    Сырье 2
    6
    4
    8
    200

    Сырье 3
    5
    3
    3
    100

    Прибыль
    10
    15
    20



    Решение:

    1. Создать расчетную таблицу

    А В С D E F

    План выгодного производства









    E Fа приведены в таблице.стно, что сбыт обеспечен, но ограничены запасы с















    Сырье
    Норма расхода сырья
    Запас сырья
    Расход сырья

     
    А
    В
    С
     
     

    Сырье 1
    18
    15
    12
    350
    =$B$9*B5+$C$9*C5+$D$9*D5

    Сырье 2
    6
    4
    8
    200
    =$B$9*B6+$C$9*C6+$D$9*D6

    Сырье 3

    5
    3
    3
    100
    =$B$9*B7+$C$9*C7+$D$9*D7

    Прибыль на ед. изделия
    10
    15
    20
     
     

    Количество
     
     
     
     
     

    Общая прибыль
    =B8*B9
    =C8*C9
    =D8*D9
    =СУММ(B10:D10)
     

    1. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска

    Сервис - настройки – поиск решения



    Параметры:



    В результате должно получиться следующее:

    Сырье
    Норма расхода сырья
    Запас сырья
    Расход сырья

     
    А
    В
    С
     
     

    Сырье 1
    18
    15
    12
    350
    350

    Сырье 2
    6
    4
    8
    200
    200

    Сырье 3

    5
    3
    3
    100
    83,33333333

    Прибыль на ед. изделия
    10
    15
    20
     
     

    Количество
    0
    5,5555556
    22,222
     
     

    Общая прибыль
    0
    83,333333
    444,44
    527,77778
     

    Сохранить под именем «План производства» и сделать выводы.

    Задание№2. Выполнить аналогичные расчеты

    Вариант 1

    Сырье
    Норма расхода сырья
    Запас сырья

     
    А
    В
    С



    Сырье 1
    25
    17
    11
    350

    Сырье 2
    9
    7
    10
    400

    Сырье 3

    15
    8
    5
    300

    Прибыль на ед. изделия
    5
    10
    12



    Количество
    ?
    ?
    ?



    Общая прибыль
    ?
    ?
    ?
    ?

    Вариант 2

    Сырье
    Норма расхода сырья
    Запас сырья

     
    А
    В
    С



    Сырье 1
    12
    11
    8
    3500

    Сырье 2
    14
    15
    2
    280

    Сырье 3

    8
    9
    10
    711

    Прибыль на ед. изделия
    10
    9
    8



    Количество
    ?
    ?
    ?



    Общая прибыль
    ?
    ?
    ?
    ?

    Вариант 3

    Сырье
    Норма расхода сырья
    Запас сырья

     
    А
    В
    С



    Сырье 1
    10
    20
    15
    2700

    Сырье 2
    16
    25
    13
    3800

    Сырье 3

    8
    9
    10
    1200

    Прибыль на ед. изделия
    7
    8
    6



    Количество
    ?
    ?
    ?



    Общая прибыль
    ?
    ?
    ?
    ?

    Вариант 4

    Сырье
    Норма расхода сырья
    Запас сырья

     
    А
    В
    С



    Сырье 1
    14
    15
    19
    460

    Сырье 2
    7
    8
    12
    820

    Сырье 3

    17
    24
    6
    214

    Прибыль на ед. изделия
    15
    10
    25



    Количество
    ?
    ?
    ?



    Общая прибыль
    ?
    ?
    ?
    ?

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