Конспект урока по Информатике «Использование функции «Подбор параметра» в электронных таблицах»

Тема урока:

«Использование функции «Подбор параметра» в электронных таблицах»

Тип урока: практическая работа обучающего характера.

Формы работы: индивидуальная, фронтальная.

Методы обучения: наглядный, практический, словесный

Оборудование: компьютер с ОС MS Windows, интерактивная доска, электронная таблица Excel, электронная версия урока – презентация, подготовленная в Power Point, раздаточный материал.

Цели:

Образовательные: уметь использовать функцию подбор параметра для решения задач обратного счета

Развивающие: развитие логического мышления, творческого воображения, умения применять информацию в нестандартных ситуациях, развитие познавательного интереса;

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

Ход урока.

1. Организационный момент. Актуализация знаний.

2. Объяснение новой темы.

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

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

Можно рассмотреть процедуру поиска параметра на простом примере:

Задача №1.

Решить уравнение: 2*х+3=15

Задача №2.

Решить уравнение 10 * x — 10 / x = 15. Здесь параметр (аргумент) — x. Пусть это будет ячейка A3. Нужно ввести в эту ячейку любое число, лежащее в области определения функции (в данном примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Нужно ввести формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Подбор параметра в меню Сервис. Вводимые параметры поиска:

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

Ввести искомый результат в поле Значение.

В поле Изменяя значение ячейки ввести ссылку на ячейку, содержащую подбираемое значение.

Кликнуть на клавише OK.

По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Уравнение имеет два решения, а параметр подобран только один — это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается в качестве результата поиска.

Достаточно сложно правильно определить наиболее подходящее начальное значение. Чаще можно сделать какие-либо предположения об искомом параметре, например, параметр должен быть целым (тогда получаем первое решение нашего уравнения) или неположительным (второе решение).

Задача №3. Имеется прямоугольный лист картона размером a и b. В каждом из четырех углов вырезают квадраты одинакового размера, а затем загибают оставшуюся часть по продолжениям сторон квадратов для получения коробки. Определить, сторону вырезаемых квадратов, при которой полученная коробка будет иметь заданный объем V.

Решить задачу при: 1) a=100, b=80, V=48000; 2) a=100, b=80, V=14600.

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

Практическая работа с классом.

1. Запустите программу MS EXCEL.

2. Заполним ячейки следующим образом:
   

3. Введём формулу в ячейку В3
   =В4*(В1-2*В4)*(В2-2*В4)
   Нажать клавишу ENTER.

4. В меню выберем команду подбора параметра: Сервис  Подбор параметра… На панели «Подбор параметра» в поле «Установить в ячейке» введём адрес ячейки, в которую будет введено значение объёма (в данном случае – В3). Для этого установить курсор в указанное поле и щёлкнуть на ячейке В3. В поле «Значение» введём требуемое значение объёма коробки (в данном случае 48 000). В поле «Изменяя значение ячейки» введём адрес ячейки, в которой будет производиться подбор значения величины х (в данном случае – В4). Для этого установить курсор в указанное поле и щёлкнуть на ячейке В4. Нажмём кнопку ОК. На появившейся панели «Результат подбора параметра» будет выведена информация о том, что решение найдено. Нажмём кнопку ОК. В ячейке В4 появится подобранное значение – число 10.
   
   
   

5. Для второго случая (V=14 600) ученики самостоятельно выполняют рассмотренные действия. В результате в ячейке В4 появится число 2,001211. (Проверка: 2∙(80-2∙2) ∙(100-2∙2)=14592)
   Вывод: Не всегда в задачах с практическим содержанием можно получить точное решение. В первом случае рассмотренной задачи было найдено точное значение величины х, во втором – приближённое.
   
   

Задача №4.

Вы хотите зарабатывать 100 000 рублей в месяц, но не знаете точно, какой должен быть Ваш оклад, чтобы достичь этой цели. Примерно Вам известно как рассчитывается заработная плата, и прежде чем идти к начальнику по поводу увеличения жалования, хотите определить свою ставку.

Для решения этой задачи надо использовать команду Сервис Подбор параметра.

Чтобы подобрать нужное значение, нужно:

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

Ниже приведены данные для постановки проблемы. Оба окна отображают один и тот же лист (первое -результаты, второе — формулы).

2. Выбрать команду Сервис Подбор параметра -откроется диалоговое окно «Подбор параметра».

3. В поле Установить в ячейке указать координаты ячейки, содержащей формулу, в которой нужно достичь определенного результата, щелкнув ее.

4. В поле Значение указать значение, которое должно быть результатом формулы.

5. В поле Изменяя значение ячейки ввести координаты ячейки, значение которой Excel должен изменять.

6. Щелкнуть по кнопке «ОК» — откроется окно, информирующее о том, что решение найдено(если оно существует).

7. В появившемся диалоговом окне щелкнуть по кнопке «ОК», если результат соответствует предъявленным требованиям; в противном случае щелкнуть по кнопке «Отмена»для возврата к исходным значениям ячеек.

Задачи для самостоятельного решения

Задача1

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

Эта задача может быть решена с помощью встроенных финансовых функций. Но можно для решения этой задачи использовать средство Подбор параметра.

Если в банк положить Х руб. под 4,5% годовых, то в конце года банк выплатит (1+0,045) * х = 1,045 * х.Поскольку в конце года по условию задачи нужно получить сумму 1000 руб., то для решения задачи требуется решить уравнение 1,054 * х = 1000.

Данные расположены на рабочем листе, как показаны ниже

Чтобы решить задачу, нужно выполнить следующие действия.

1. Сделать активной ячейку В3.
2. Вызвать средство Подбор параметра.
3. Проверить, что в поле Установить в ячейке диалогового окна Подбор параметра указана ссылка на ячейку В3.
4. В поле Значение ввести значение 1000.
5. В поле Изменяя значение ячейки указать ссылку на ячейку В2. 6. Нажать кнопку ок.

Средство Подбор параметра найдет решение,равное 956,94, и поместит это значение в ячейку В2.

Задача 2

Для покупки автомобиля Вам необходима сумма200000 руб. У вас есть возможность взять 30-летнюю ипотечную ссуду со ставкой 8% годовых. При этом нужно сделать 20% взнос. Определить, какую сумму нужно взять в банке, чтобы на руки вы получили требуемую сумму.

Пусть в банке выдали х руб. (S).Первоначальный взнос (V) составляет 20% * х. Остается сумма S = S-V. Представим исходные данные, как показано на рисунке

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

— в поле Установить в ячейке — ссылка на ячейку D2,

— в поле Значение — значение 200000,

— в поле Изменяя значение ячейки — ссылка на ячейку А2.

С помощью средства Подбор параметра будет найден ответ: 250000 руб.

3. Выполнение практической работы

Инструкционная карта практического задания №9

По дисциплине: Информатика

Группа: З-21

Отделения: «Земельно – имущественные отношения»

Тема: Использование функции «Подбор параметра» в электронных таблицах.

Цели:

• Образовательная: уметь использовать функцию подбор параметров для решения задач обратного счета.

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

Приобретаемые навыки и умения

• Научиться применять функцию подбор параметров.

• Использовать режим СПЕЦИАЛЬНАЯ ВСТАВКА для копирования данных с одного листа на другой.

Развитие способностей студента:

• Исследовательских: исследовать структуру таблицы, приемы работы с таблицей, с операцией специальная вставка для копирования, с функцией ПОДБОР ПАРАМЕТРОВ.

• Проектировочных: создание и описание структуры таблицы, исходных данных, формул для вычислений

• Технологических: организация последовательности действий и применения операций при создании таблиц и использовании функций

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

Оборудование: ИК, компьютер, MS Excel

Продолжительность работы: 40 мин

Задание практической работы: Выполнить последовательно все действия, описанные в ИК, получить результат и составить отчет в тетради.

ХОД ЗАНЯТИЯ

ЗАДАНИЕ 1. Создать табл.1 по образцу:

Таблица 1.

1. Каждый оклад вычисляется от базовой минимальной зарплаты курьера, которая вносится в ячейку ЕЗ, и вычисляется по формуле оклад = А * X + В, где X – оклад курьера, А – коэффициент, показывающий, во сколько раз превышается значение от X, В – коэффициент, показывающий, на сколько превышается значение от X.
2. В ячейку ЕЗ введите произвольное число в качестве зарплаты курьера.
3. Введите формулы и рассчитайте зарплату каждому сотруднику.
4. Задайте формулу и рассчитайте зарплату всех работающих в данной должности
5. Вычислите суммарный фонд заработной платы.
6. Произведите подбор зарплат сотрудников фирмы, если суммарный фонд зарплаты составит всего 100000 р., для этого использовать команду СЕРВИС–ПОДБОР ПАРАМЕТРА.
Разберитесь, какие данные необходимо внести в диалоговое окно функции ПОДБОР ПАРАМЕТРА.

ЗАДАНИЕ 2. Используя режим ПОДБОР ПАРАМЕТРА и таблицу расчета штатного расписания, определите величину заработной платы сотрудников фирмы для ряда значений фонда заработной платы.

Для этого: Методом подбора определяете зарплаты сотрудников фирмы для различных значений фонда заработной платы, значения даны в табл.2.
Результаты подбора значений зарплат скопируйте в таблицу в виде СПЕЦИАЛЬНОЙ ВСТАВКИ. Специальная вставка позволяет копировать только значения без дальнейшей зависимости их от пересчета формул. Для выполнения этой операции использовать команду ПРАВКА – КОПИРОВАТЬ, установить курсор в таблицу ответов и задать режим ПРАВКА – СПЕЦИАЛЬНАЯ ВСТАВКА – выбрать соответствующий параметр.

Таблица 2.

ЗАДАНИЕ 3. Опишите порядок выполнения всех примененных операций в тетради.

3. Итог урока .

4. Домашнее задание.