ЛАБОРАТОРНАЯ РАБОТА № 14
РЕШЕНИЕ ЗАДАЧ С ПОМОЩЬЮ НАДСТРОЙКИ «ПОДБОР ПАРАМЕТРА»
Цель работы: научиться использовать надстройки ЭТ для решения задач управления.
Теоретические сведения
Когда желаемый результат вычислений по формуле известен, но неизвестны значения, необходимые для получения этого результата, можно воспользоваться средством Подбор параметра ( на вкладке Данные в группе Работа с данными выберите команду Анализ “что-если”, а затем выберите в списке пункт Подбор параметра ). При подборе параметра Excel изменяет значение в одной конкретной ячейке до тех пор, пока вычисления по формуле, ссылающейся на эту ячейку, не дадут нужного результата.
Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:
· формула для расчета;
· пустая ячейка для искомого значения;
· другие величины, которые используются в формуле (влияющие значения).
Ссылка на пустую ячейку обязательно должна присутствовать в формуле, так как именно она является переменной, значение которой ищет Excel. Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи.
Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит заданное число попыток или не найдет решения, лежащее в пределах указанной пользователем точности (настройка этих параметров осуществляется с помощью команды кнопка “Office”/Параметры Excel/Формулы/Параметры вычислений).
Задания для аудиторной работы
Задание 1. Определить количество книг по цене 150 р., которые необходимо продать, чтобы объем продаж составил 2600 р.
Решение
1. Создайте лист с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой B2 и другими данными B1, которые могут понадобиться при вычислениях (рис. 1).
Рис. 1. Подготовка к решению задачи.
2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Данные/Работа с данными/Анализ “что-если”/Подбор параметра. Открывается окно диалога Подбор параметра (рис. 2):
Рис. 2. Окно «Подбор параметра».
3. Введите в текстовое поле Значение число, соответствующее объему продаж - 2600. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Указанная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы получить решение (рис. 3).
Рис. 3. Результат подбора параметра.
После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 2600 р. необходимо продать 17 книг по цене 150 р. Для закрытия диалогового окна Результат подбора параметра щелкните по кнопке Оk.
Задание 2. Решить уравнение х2-5х+6=0.
Решение
Для нахождения корня уравнения выполним следующие действия:
1) выделите ячейку С2 под будущий корень уравнения (данное в ней появится после выполнения подбора параметра). Изначально предположим, что корень равен 0;
2) в ячейку С3 введите формулу для вычисления значения функции, в качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6;
3) в диалоговом окне Подбор параметра в поле Установить в ячейке введите ссылку на ячейку с формулой, в поле Значение – правую часть уравнения – в данном случае 0, в поле Изменяя значения ячейки – ссылку на ячейку, в которой будет храниться значение корня (ячейка $С$2).
После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра.
Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке С2.
Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нужно нажать кнопку Отмена.
Задание 3. Найти значение корней уравнения sin(cos(2x+1))=0 при x, изменяющемся от -π/2 до π/2 cшагом 0,2.
Решение
Установитекурсор в ячейку A2.
В ячейку А2 записываем нижнее значение аргумента: =-ПИ()/2 (Функция p вставляется из списка математических функций 
).
В ячейку А3 введите формулу для расчета следующего значения переменной х: =А2+0,2.
Используя автозаполнение, скопируйте в ячейки диапазона А3:А16 формулу ячейки А2. (Автозаполнение выполняется с помощью перетаскивания маркера заполнения в правом нижнем углу курсора-рамки с нажатой правой кнопкой мыши, с последующим выбором нужной команды).
В ячейку В2 записываем формулу вычисляемой функции =sin(cos(2*A2+1)).
Скопируйте формулу из ячейки В2в ячейки вниз, используя средство Автозаполнения, для того чтобы получить все следующие значения функции y= f(x).
Из математики известно, что функция при пересечении оси Ох меняет знак. Определите ячейку, в которой происходит смена знака функции.
Скопируйте формулу из любой ячейки диапазона В2:В16 в ячейку В18. В этой ячейке выполните подбор параметра по аналогии с заданием 2, выбрав в качестве зависимой ячейки А18 (в ней будет отображаться найденный корень уравнения).
Уточните значения всех аргументов х для f(x)= 0, если функция имеет несколько корней.