Изучение сервиса MS Excel "Подбор параметра"

Подбор параметра Цель:  показать   учащимся   эффективный   способ   решения   линейных   уравнений   в   электронных таблицах  с помощью метода «подбор параметра». Тип урока: комбинированный (объяснение материала+практикум) Задачи:  образовательные   научить строить информационную модель, адекватную поставленной задаче; развивать информационное видение явлений и процессов окружающего мира при создании моделей; развивающие  формировать познавательный интерес школьников к предмету;  развитие критического мышление (умение оценивать, сопоставлять полученные данные) воспитательные:    воспитание целеустремленности, трудолюбия, ответственности; научить оценивать свои способности, навыки самоанализа выполненной работы Приборы и материалы:  ПК,  презентация для демонстрации, программа 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