Подбор параметра
Цель: показать учащимся эффективный способ решения линейных уравнений в электронных
таблицах с помощью метода «подбор параметра».
Тип урока: комбинированный (объяснение материала+практикум)
Задачи:
образовательные
научить строить информационную модель, адекватную поставленной задаче;
развивать информационное видение явлений и процессов окружающего мира при создании
моделей;
развивающие
формировать познавательный интерес школьников к предмету;
развитие критического мышление (умение оценивать, сопоставлять полученные данные)
воспитательные:
воспитание целеустремленности, трудолюбия, ответственности;
научить оценивать свои способности, навыки самоанализа выполненной работы
Приборы и материалы: ПК, презентация для демонстрации, программа MS EXCEL + заранее
подготовленные задания, раздаточные материалы технология работы с задачей 1.
Ход урока:
I Организационный момент.
приветствие учащихся учителем; фиксация отсутствующих;
сообщение темы урока; раскрытие общей задачи урока и плана его проведения.
II Постановка цели и задач урока.
Сегодня на уроке мы рассмотрим эффективный способ решения линейных уравнений в
электронных таблицах с помощью метода подбора параметров.
III Актуализация знаний
Как ввести формулу в среде электронной таблицы?
Что может входить в состав формулы?
Какие адреса ячеек называют относительными ? абсолютными?
С какими сервисами (функциями) Excel вы знакомы?
IV Формирование знаний
На этом уроке вы убедитесь, что Excel позволяет не только производить расчёты, но и
решать сложные задачи в различных сферах деятельности, такие как решение уравнений, задачи
оптимизации.
Формулировка таких задач может представлять собой систему уравнений с несколькими
неизвестными и набор ограничений на решения. Поэтому решение задачи необходимо начинать с
построения соответствующей модели.
Специфика таких задач состоит в том, что в вашем распоряжении есть математическая
модель исследуемого процесса, но вы не знаете, при каком значении входящего в неё параметра
можно достичь поставленной цели.
Решение таких задач можно искать методом перебора, однако на это уходит много времени.
Здесь уместно применить более эффективный способ, который в Excel реализован как поиск
значения параметра формулы, удовлетворяющего ее конкретному значению.
Эту процедуру используют для поиска такого значения ячейки, при котором значение
другой ячейки, вычисляемое по формуле, заранее задано. В формуле должна быть ссылка на
ячейку, значение которой ищут. Ограничение на искомое значение ячейки не налагают.
Познакомимся с этой процедурой на примере составления штатного расписания.
1Задача 1:
Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 3
заведующих отделениями, главный врач, зав. Аптекой, заведующий хозяйством и заведующий
больницей. Общий фонд зарплаты составляет 800 000 ден. ед. Необходимо определить, какими
должны быть оклады сотрудников больницы.
Построим модель решения этой задачи
За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него:
во сколькото раз или на сколькото больше. Говоря языком математики, каждый оклад является
линейной функцией от оклада санитарки:
Ai*C + Bi , где С оклад, Ai и Bi коэффициенты, которые для каждой должности определяются
следующим образом:
медсестра получает в 1,5 раза больше санитарки ( А2 = 1,5; В2 = 0);
врач в 3 раза больше санитарки ( А3 =3; В3 = 0);
заведующий отделением на 30 у.е. больше чем врач ( А4 = 3; В4 = 30);
заведующий аптекой в 2 раза больше санитарки ( А5 = 2; В5 = 0);
заведующий хозяйством на 40 у.е. больше медсестры ( А6 = 1,5; В4 = 40);
главный врач в 4 раза больше санитарки (А7 = 4; В7 = 0);
заведующий больницей на 20 у.е. больше главного врача ( А8 = 4; В8 = 20).
Зная количество человек на каждой должности, нашу модель можно записать как уравнение:
N1*A1*C +N2(A2*C+B2) +…..+N8(A8*C + B8) = 10 000
где N1 число санитарок; N2 число медсестер и т. д.
В этом уравнении нам известны A1…A.8; B1 …B8 и N1…. N8 , а С неизвестно.
Анализ уравнения показывает, что задача составления расписания свелась к решению линейного
уравнения относительно С.
Решим его.
V Обобщение и первичное закрепление знаний
Технология работы:
1. Заполните таблицу: В столбец В введите коэффициенты А (санитарка 1; медсестра 1,5;
врач 3; Зав.отделением 3; зав.аптекой 2; завхоз 1,5; глав.врач 4; зав. Больницей 4)
2. В столбец С введите коэффициенты В (санитарка 0; медсестра 0; врач 0;
Зав.отделением 30; зав.аптекой 0; завхоз 40; глав.врач 0; зав. Больницей 20)
3. В столбец Е введите количество сотрудников (санитарка 6; медсестра 8; врач 10;
Зав.отделением 1; зав.аптекой 1; завхоз 1; глав.врач 1; зав. Больницей 1)
4. В столбце D вычислите заработную плату для каждой должности (например для ячейки D10
формула примет вид: = $G$10*B10+C10 ) и скопируйте вниз
5. В столбце F вычислите заработную плату c учётом количества сотрудников (например для
ячейки F10 формула примет вид: = D10*E10) и скопируйте вниз, затем вычислите в ячейке
F19 суммарный фонд заработной платы.
Коэф.А
Должность
Санитарка
Медсестра
Врач
Зав. отдел.
Зав. аптекой
Завхоз
Глав.врач
Коэф.
В
Зарплата
сотр.
Колво
сотр.
Сумм.
зарплата
Зарплата
санитарки
2Зав. Больницей
Итого
Решение:
1. Сервис Подбор параметров диалоговое окно Подбор параметра
2. В поле Установить в ячейке указать координаты ячейки, где нужно
получить определенный результат (F19)
3. В поле Изменяя значение ячейки ввести координаты ячейки, значение
которой должны изменяться (G10).
Дополнительные задания:
Каждую задачу решить на отдельном листе электронной таблицы
В каждой задаче выделите аргумент(ы), функцию (создать формулу для вычисления функции),
параметры (числа, которые надо просто вписать в ячейки).
Найдите (подберите) значение аргумента в соответствии с желаемым значением функции.
Используйте для этого сервис «подбор параметра»
Задача 2.
Задача 3.
VI Подведение итогов
Анализ задачи показывает, что с помощью процедуры Подбор параметра в Excel можно решать
линейные уравнения . Практическое применение сервиса разнообразно.
VII Домашнее задание.
использования сервиса «Подбор параметра»
Учащимся предлагается самостоятельно придумать задание для
3
urok.doc
