971615
столько раз учителя, ученики и родители
посетили сайт «Инфоурок»
за прошедшие 24 часа
+Добавить материал
и получить бесплатное
свидетельство о публикации
в СМИ №ФС77-60625 от 20.01.2015
Дистанционные курсы профессиональной переподготовки и повышения квалификации для педагогов

Дистанционные курсы для педагогов - курсы профессиональной переподготовки от 5 480 руб.;
- курсы повышения квалификации от 1 400 руб.
Московские документы для аттестации

ВЫБРАТЬ КУРС СО СКИДКОЙ 60%

ВНИМАНИЕ: Скидка действует ТОЛЬКО до 28 февраля!

(Лицензия на осуществление образовательной деятельности №038767 выдана ООО "Столичный учебный центр", г.Москва)

Инфоурок / Информатика / Другие методич. материалы / Практическое занятие по теме: «Подбор параметра и оптимизация (поиск решений) в Excel»

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


Напоминаем, что в соответствии с профстандартом педагога (утверждён Приказом Минтруда России), если у Вас нет соответствующего преподаваемому предмету образования, то Вам необходимо пройти профессиональную переподготовку по профилю педагогической деятельности. Сделать это Вы можете дистанционно на сайте проекта "Инфоурок" и получить диплом с присвоением квалификации уже через 2 месяца!

Только сейчас действует СКИДКА 50% для всех педагогов на все 184 курса профессиональной переподготовки! Доступна рассрочка с первым взносом всего 10%, при этом цена курса не увеличивается из-за использования рассрочки!

ВЫБРАТЬ КУРС И ПОДАТЬ ЗАЯВКУ
библиотека
материалов
Скачать материал целиком можно бесплатно по ссылке внизу страницы.

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



Цель практического занятия: Изучение технологии «Подбора параметра и оптимизация (поиск решений) » для решения задач, имеющих точное целевое значение, зависящее от одного неизвестного параметра

Задачи:

  1. Познакомится с основными ключевыми понятиями «Подбора параметра»

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

  3. Решение задач по теме «Подбор параметра» по образцу и по алгоритму.

  4. Решение задач по указанной теме самостоятельно

План занятия:

  1. Ознакомление студентов с ключевыми понятиями.

  2. Рассмотрение процесса нахождения исходных данных

  3. Знакомство с понятием Подбор параметра и Поиск решения

  4. Решение задач по теме «Подбор параметра» по образцу и по алгоритму

  5. Решение задач по указанной теме самостоятельно



Ход занятия

Для эффективного использования формул необходимо ознакомиться с тремя ключевыми понятиями.

Вычисление   — это процесс расчета по формулам и последующего отображения значений результатов в ячейках, содержащих формулы. Во избежание ненужных вычислений Microsoft Office Excel автоматически пересчитывает формулы только при изменении влияющих на формулу ячеек. Это является стандартной процедурой при первом открытии книги и ее редактировании. Однако тем, как и когда Excel будет пересчитывать формулы, можно управлять.

Итерация   — это многократный пересчет листа до удовлетворения определенного числового условия. Excel не может автоматически рассчитать значение по формуле, которая ссылается (прямо или косвенно) на ячейку, содержащую формулу (это называется циклической ссылкой). Если формула содержит обратную ссылку на одну из своих собственных ячеек, необходимо определить, сколько раз следует пересчитывать формулу. Циклические ссылки могут пересчитываться до бесконечности. Однако существует возможность управления максимальным числом итераций и количеством допустимых изменений.

Точность   — это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение.

Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул в Excel называется анализом «что-если».

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

Другими словами, вы можете задать вопрос типа: Какой рост продаж необходим для получения дохода в $1 200 000? В Excel для этого предусмотрены два подходящих средства.

  • Подбор параметра – определяет значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата).

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

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







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

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

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

Задание:

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

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

hello_html_60de95b8.png

Рис.1

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

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

hello_html_m12009130.png

Рис.2

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

hello_html_2b934889.png

Рис.3

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

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

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

hello_html_m621cb2a8.png

Рис.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. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска

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

hello_html_4add158b.png

Параметры:

hello_html_m15dec6d8.png

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

Сырье

Норма расхода сырья

Запас сырья

Расход сырья

 

А

В

С

 

 

Сырье 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


Количество

?

?

?


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

?

?

?

?


















Краткое описание документа:
Изучение технологии «Подбора параметра и оптимизация (поиск решений) » для решения задач, имеющих точное целевое значение, зависящее от одного неизвестного параметра Задачи: Познакомится с основными ключевыми понятиями «Подбора параметра» рассматривается процесс нахождения исходных данных, которые при подстановке в формулы, дают необходимое значение в ячейке результатаРешение задач по теме «Подбор параметра» по образцу и по алгоритму.Решение задач по указанной теме самостоятельноПлан занятия: Ознакомление студентов с ключевыми понятиями.Рассмотрение процесса нахождения исходных данныхЗнакомство с понятием Подбор параметра и Поиск решенияРешение задач по теме «Подбор параметра» по образцу и по алгоритму Решение задач по указанной теме самостоятельно
Общая информация

Номер материала: 41770032806



Очень низкие цены на курсы переподготовки от Московского учебного центра для педагогов

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

После окончания обучения выдаётся диплом о профессиональной переподготовке установленного образца с присвоением квалификации (признаётся при прохождении аттестации по всей России).

Подайте заявку на интересующий Вас курс сейчас: KURSY.ORG

Только до конца зимы! Скидка 60% для педагогов на ДИПЛОМЫ от Столичного учебного центра!

Курсы профессиональной переподготовки и повышения квалификации от 1 400 руб.
Для выбора курса воспользуйтесь удобным поиском на сайте KURSY.ORG


Вы получите официальный Диплом или Удостоверение установленного образца в соответствии с требованиями государства (образовательная Лицензия № 038767 выдана ООО "Столичный учебный центр" Департаментом образования города МОСКВЫ).

Московские документы для аттестации: KURSY.ORG


Вам будут интересны эти курсы:

Курс повышения квалификации «Информационные технологии в деятельности учителя физики»
Курс повышения квалификации «Современные информационные технологии и их использование в работе преподавателей. Системы автоматизированного проектирования одежды и организация технологического процесса»
Курс повышения квалификации «Основы создания интерактивного урока: от презентации до видеоурока»
Курс повышения квалификации «Внедрение системы компьютерной математики в процесс обучения математике в старших классах в рамках реализации ФГОС»
Курс профессиональной переподготовки «Информатика: теория и методика преподавания в образовательной организации»
Курс «1С: Предприятие 7.7»
Курс «WEB-ВЕРСТКА (HTML, CSS)»
Курс повышения квалификации «Сетевые и дистанционные (электронные) формы обучения в условиях реализации ФГОС по ТОП-50»
Курс профессиональной переподготовки «Информационные технологии в профессиональной деятельности: теория и методика преподавания в образовательной организации»
Курс повышения квалификации «Специфика преподавания информатики в начальных классах с учетом ФГОС НОО»
Курс повышения квалификации «Введение в программирование на языке С (СИ)»
Курс профессиональной переподготовки «Теория и методика обучения информатике в начальной школе»
Курс профессиональной переподготовки «Математика и информатика: теория и методика преподавания в образовательной организации»
Курс повышения квалификации «Современные тенденции цифровизации образования»
Курс повышения квалификации «Специфика преподавания дисциплины «Информационные технологии» в условиях реализации ФГОС СПО по ТОП-50»
Включите уведомления прямо сейчас и мы сразу сообщим Вам о важных новостях. Не волнуйтесь, мы будем отправлять только самое главное.