Урок Множина розв’язку задач на підбір параметра
Використання засобу табличного процесора Підбір параметрів
Мета
Навчальна:
• сформувати знання про упорядкування даних у табличному процесорі;
• сформувати навички введення та редагування формул.
Розвивальна:
• розвиток творчих здібностей;
• розвиток логічного мислення.
Виховна:
• формування навичок самостійності та самоаналізу в роботі з електронними засобами обробки інформації.
Мотивація
При анализе данных бывает важно определить, как изменение одной из переменных повлияет на результат формулы, как подобрать оптимальное значение переменной. Например, вам нужно определить, при каком тираже затраты на производство книги совпадут с запланированным доходом (фиксированной ценой), то есть с какого тиража вы начнете получать прибыль.
В арсенале Microsoft Excel имеется команда Подбор параметра (меню Сервис), которая позволяет осуществить поиск именно того значения переменной величины, при котором будет достигнут указанный результат. Подбор параметра можно использовать для исследования уравнений и функций.
План урока
1. орг. Момент
2. Изучение нового материала на примерах
Вправа
Найти точки пересечения с осями координат функции у= (х-6)5
Ключ к заданию
Для того, чтобы найти точку пересечения с осью ординат, нужно вычислить значение функции при х=0.
Создайте таблицу, в которой в ячейке В2 значение 0, а в ячейку В3, с помощью мастера формул, введите формулу «=СТЕПЕНЬ(В2-6;5)». Точка перескчения с осью ординат имеет координаты (0;-7776)
Для того чтобы найти точки пересечения с осью абсцисс функции у=(х-6) нужно решить уравнение (х-6)5=0. То есть подобрать такое значение х, при котором формула «=(х-6)5» принимает нулевое значение. Для этого:
· выделите ячейку ВЗ;
· в меню Сервис выберите команду Подбор параметра;
· в диалоговом окне <Подбор параметра> введите требуемые значения, если нужно добиться того, чтобы в ячейке ВЗ было установлено значение 0, посредством изменения значений ячейки В2:
· выберите О/С;
· в диалоговом окне <Результаты подбора параметра> выберите ОК.
Результат должен разочаровать - найдена точка (5,: -0.) на первый взгляд очень близкая к искомой, но не совсем та. Однако если округлить полученные значения с точностью до целых, результат вполне может удовлетворить (6; 0). Именно такое значение можно найти аналитически.
При решении этой задачи была построена компьютерная модель процесса, описываемого математической формулой у=(х-6)5, а затем проведен компьютерный эксперимент. Команда Подбор параметра скрупулезно подставляла в заданную формулу различные числа, пока не нашла нужного значения.
Упражнения
1. Решите квадратное уравнение х2-12х+36=0.
2. Найдите точки пересечения с осями координат функции у=(х+2)3.
3. В последнее время в городском транспорте стало появляться все больше кондукторов. Поставим задачу выяснить целесообразность подобного нововведения в нашем регионе.
Ключ к заданию
Рассмотрим некоторые аспекты данного явления, чтобы выяснить возможные преимущества и недостатки:
· кондуктору придется платить заработную плату;
· можно будет сократить штат контролеров.
· сразу большое количество людей сможет найти работу;
· с введением кондукторов можно решить проблему безбилетников;
· отпадает необходимость продавать абонементные билеты на городской транспорт;
· отпадает необходимость устанавливать компостеры;
· продолжить список вы можете самостоятельно.
Попробуем рассмотреть этот вопрос только с одной стороны. Кондуктору придется платить заработную плату, причем на каждый автобус (троллейбус, трамвай) придется нанять несколько кондукторов. С другой стороны, безбилетникам теперь уж точно придется оплачивать проезд.
Поставим задачу: определить, сколько безбилетников в сутки должны приобрести билеты, чтобы окупилась зарплата кондуктора.
Предположим, мы хотим установить заработную плату кондуктора 800 грн. Автобус находится на маршруте с 5 часов утра до 1 часа ночи - в этом случае на каждый автобус придется нанять сразу трех кондукторов, которые будут работать посменно. Следовательно, суммарная месячная заработная плата кондукторов на одном автобусе должна составлять 800x3=2 400 (грн).
Создайте таблицу, введя следующие данные:
«Средняя заработная плата кондукторов в месяц» и «Размер штрафа» (ячейки С2 и СЗ) - фиксированные величины, а в ячейку С5 («Разность между заработной платой кондукторов и суммой штрафов») введите формулу «=С2/30-СЗ*С4», где'С2/30 - средняя заработная плата кондукторов в сутки. Ячейку С4 оставьте пустой, так как именно эту величину мы пытаемся определить.
Для того чтобы работа кондуктора стала оправданной, разность, вычисляемая в ячейке С5. должна стать равной нулю. Эта разность зависит от двух фиксированных величин и одной изменяемой (числи безбилетников). Для определения числа безбилетников, при котором уравняются доход от штрафов и расходы на выплату зарплаты, выполните следующее:
♦ выделите ячейку С5;
♦ в меню Сервис выберите команду Подбор параметра;
♦ в диалоговом окне <Подбор параметра> введите требуемые значения, если нужно добиться того, чтобы в ячейке $С$5 было установлено значение 0, посредством изменения значений ячейки $С$4;
♦ выберите ОК
♦ в диалоговом окне <Результаты подбора параметра> выберите ОК.
В результате, в ячейке С4 появится число 8. Следовательно, если в сутки восемь потенциальных безбилетников оплатят свой проезд, работа кондуктора окупается. Если потенциальных безбилетников больше, работа кондуктора будет приносить доход.
2. Итог урока
Упражнение ДЗ
Используя построенную ранее модель, решите следующую задачу: какую заработную плату реально выплачивать каждому кондуктору, если считать, что его работа заставит оплачивать проезд 5 потенциальных безбилетников в сутки.
Урок 2
Упражнение
Предположим вы решили приобрести машину. Нужно определить, выгодно ли ездить на ней на работу.
Ключ к заданию
Будем считать, что для поездки на работу вы пользуетесь двумя видами транспорта: трамваем и метро. Следовательно, ежедневные расходы на дорогу в оба конца составляют 10 грн
Примем стоимость бензина равной 9 грн 50 копеек, а расход -11 литров на 100 км. Следовательно, для вычисления затрат на один ки-юметр пути нужно цену бензина умножить на 0,11.
Создайте таблицу, где присутствуют две фиксированные величины («Средняя стоимость проезда на городском транспорте в сутки» и «Стоимость бензина», соответственно в ячейках С2 иС4) я две формулы: в ячейке С5 «=СЗ*С4*0,11» («Затраты на дорогу при пользовании автомобилем») и в ячейке С6 «=С2-С5» («Разность между стоимостью проезда на городском транспорте и затратами на автомобильные поездки»).
Средняя стоимость проезда на городском транспорта в сутки | 10 |
Километраж | |
Стоимость бензина | 3,2 |
Затраты на дорогу при пользовании автомобилем | 0 |
Разность между стоимостью проезда на городском транспорте и за-ра-тами на автомобильные поездки | 10 |
Необходимо определить, при каком километраже сравняются затраты на пользование городским транспортом и автомобильным. Для этого:
· выделите ячейку С6;
· в меню Сервис выберите команду Подбор параметра;
· в диалоговом окне <Подбор параметра> введите требуемые значения (рис. 11.51), если нужно, чтобы в ячейке $С$6 было установлено значение 0, посредством изменения значений ячейки $С$3 и выберите ОК;
· в диалоговом окне <Результаты подбора параметра в диалоговом окне <Результаты подбора параметра> выберите ОК
В результате в ячейке С3 будет вычислено ближайшее подходящее значение 28, 4090909 (кілометраж в оба конца). Значит, половина этого значения уравнивает затраты. При увеличении километража возрастает выгода применения личного автомобиля.
Разумеется, при решении задачи мы не учли расходы на обслуживание и ремонт автомобиля и не можем претендовать на предельно точное значение, однако подобный компьютерный эксперимент позволяет оценить ситуацию.
Упражнение Самостоятельная практическая работа
Определите, на каком расстоянии от дома должна находиться ваша работа, чтобы было выгодно пользоваться автомобилем, если для поездок на городском транспорте, вы приобретаете единый проездной билет, стоимостью 180 грн.
Упражнение ДЗ
Какой должна быть стоимость бензина (при постоянно растущих ценах), чтобы на машине стало выгодно ездить на работу, удаленную на 25 км.
