Подбор параметра в excel лабораторная работа
Обновлено: 06.07.2024
Данная процедура относится к технологиям анализа целевой функции или технологиям «How can – анализа». Подбор параметра – это способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При этом значение в ячейке изменяется до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.
Чтобы воспользоваться процедурой Подбор параметра, необходимо выполнить последовательность действий:
1. Выберите команду Подбор параметра в меню Данные/Анализ «Что-если».
2. В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу.
3. Введите искомый результат в поле Значение.
4. В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой нужно подобрать. Формула в ячейке, указанной в поле Установить в ячейке должна ссылаться на эту ячейку.
Задание 1.
Вас просят дать в долг 10000 рублей, обещая вернуть через год – 2000, через 2 – 4000, через 3 – 7000. При какой годовой процентной ставке эта сделка имеет смысл?
В ячейку В7 (рис.) вводится формула для расчета чистого приведенного значения вклада с функцией ЧПС(ставка, 1-е значение, 2-е значение,…), аргумент которой «ставка» – ссылка на ячейку годовой учетной ставки (она пока пустая). Затем вызывается Подбор параметра.
Рис. Процедура Подбор ____________параметра
Лабораторная работа №15.
Совместная работа в приложениях MS Office.
Задание 1
Используя возможности текстового редактора WORD и табличного процессора EXCEL и указания к работе, создайте документ по приведенному образцу:
1. Находясь на странице документа Word, установить все поля страницы по 2 см.
2. Набрать текст, соблюдая точный повтор оригинала, до абзаца «Состав и количество затрат на питание представлен на диаграмме:» включительно.
3. Для создания диаграммы перейти в EXCEL и создать таблицу следующего содержания:
4. 4. Выполнить необходимые расчеты в столбцах «Стоимость продукта на одного человека» и «Стоимость продукта на всю группу», а также в ячейке «Итоговая стоимость». В ячейках, содержащих денежные расчеты, применить денежный формат с двумя знаками после запятой.
5. Построить круговую диаграмму, отражающую процент денежных затрат каждого продукта в составе общих затрат на питание всей группы.
6. Построить гистограмму, отражающую количество денежных затрат по каждому продукту с подписями данных.
7. Вставить построенные диаграммы в документ WORD.
8. Закончить создание документа по образцу.
Лабораторная работа №16.
Элементы автоматизации с помощью макросов
С помощью макросов можно автоматизировать многократно повторяющиеся действия. Макрос – это последовательность команд, которые написаны на VBA и хранятся в стандартном модуле среды разработки приложений. Эту последовательность можно не писать вручную, а воспользоваться макрорекордером, который преобразует действия пользователя в макрокоманды.
Задача 1. Записать макрос
Запишем макрос, который создает новую рабочую книгу, состоящую из единственного рабочего листа Отчет с шаблоном отчетной таблицы, в которую остается только внести числовые данные.
1. Добавить вкладку Разработчик на ленту команд: Меню MS Office/Параметры Excel/Показывать вкладку Разработчик на ленте команд – поставить флажок. Для активизации макрорекордера выбрать команду Разработчик/Запись макроса. Появится диалоговое окно Запись макроса, которое позволит задать параметры макроса.
2. Задать имя макроса: СоздатьОтчет. Сохранить в – Эта книга Задать описание: Создание рабочей книги с отчетной таблицей. Нажмите ОК. Теперь все производимые действия будут записываться до тех пор, пока не будет выбрана команда Разработчик/Остановить запись.
3. Последовательность действий, которую запишет макрорекордер:
a. Меню MS Office/Параметры Excel/Основные/раздел «При создании новых книг»: Число листов – 1/ОК.
b. Меню MS Office/Создать/Новая книга/кнопка Создать.
c. Переименовать ярлык Лист 1 в Отчет.
d. Выделить ячейку А2 и ввести в нее Отдел закупок.
e. Выделить ячейку А3 и ввести в нее Отдел рекламы.
f. Выделить ячейку А4 и ввести в нее Итого.
g. Двойным щелчком по границе столбцов А и В подберите ширину столбца А.
h. Выделить ячейку В1 и ввести в нее Расходы.
i. В ячейку В4 ввести формулу: =СУММ(В2:В3).
j. Выделить диапазон А1:В4, на вкладке Главная в разделе Стиль ячеек выбрать любые стили (можно поочередно применить несколько, например «Хороший» и «Примечание»). Когда таблица примет нужный вид – остановите запись макроса: Разработчик/Остановить запись.
4. Для выполнения только что записанной процедуры выберите команду Разработчик/Макросы, отобразится диалоговое окно Макросы. В нем выберите имя нашего макроса и нажмите кнопку Выполнить. Если нажать кнопку Изменить, то на экране отобразится окно редактора VBA с активизированным стандартным модулем, в котором будет код только что записанного макроса. Ничего менять не будем, закрываем окно модуля.
Если все выполнено верно, то после выполнения команды Выполнить будет создана еще одна рабочая книга с одним листом, названным «Отчет», на котором буде размещена заданная таблица. Введите данные в столбец В (например В2 - 1000 и В3 - 1200). Сумма будет подсчитана автоматически.
Макрос можно назначить кнопке, созданной с помощью панели инструментов «Элементы управления формы». Для этого выполним последовательность действий:
1) Разработчик/Вставить/ Элементы управления формы.
2) Выбрать левой кнопкой мыши инструмент «Кнопка» (указатель мыши принимает вид тонкого креста). Очертить мышкой, нажимая ее левую кнопку, контур будущей кнопки.
3) В окне Назначить макрос объекту выбрать имя нашего макроса и нажать ОК.
4) Поместите указатель мыши в центр кнопки и дважды щелкните левой кнопкой.
5) Сотрите имя «Кнопка 1» и введите имя кнопки «СоздатьОтчет», аналогичное имени макроса.
6) Проверьте работу макроса нажатием на созданную кнопку.
Задача 2.
Выполните создание следующих макросов:
1) макроса, выполняющего добавление в рабочую книгу листа;
2) макроса, вставляющего диаграмму (гистограмму) для любого ряда данных;
3) макроса, который выполняет построение графика функции .
Назначьте их кнопкам и проверьте работу макросов.
Лабораторная работа №17.
Анализ инвестиционных проектов средствами Excel
Инвестиционный процесс – это временная последовательность взаимосвязанных инвестиций (вложений денег), доходы от которых также растянуты во времени. Период, в течение которого инвестиции будут приносить отдачу, определяет продолжительность проекта с момента его начала и называется жизненным циклом инвестиции.
Инвестиционный процесс характеризуется двусторонним потоком платежей, где отрицательные члены потока являются вложениями денежных средств в инвестиционный проект, а положительные члены – доходами от инвестиций.
На оси времен инвестиционный проект может быть изображен двусторонней последовательностью платежей: отрицательные ординаты (вниз) – отток, положительные (вверх) – приток. При табличном задании притоку соответствует временной ряд платежей со знаком плюс и со знаком минус – оттоку. В наиболее стандартной ситуации денежный поток развивается таким образом, что инвестиции или отток капитала
предшествуют поступлениям или его притоку.
Рис. Пример потоков инвестиционного проекта
Критерии оценки эффективности инвестиционных проектов
Базой для расчета показателей эффективности являются так называемые чистые денежные потоки (Net Cash-Flow, NCF), включающие в себя выручку от реализации, текущие и инвестиционные затраты, прирост потребности в оборотном капитале и налоговые платежи. Название «чистые потоки» говорит о том, что потоки не учитывают схему финансирования – вложение собственных средств и привлечение кредитных ресурсов. Без этого вложения денежный поток проекта будет, естественно, получаться отрицательным на начальном этапе и накопленные денежные средства будут выглядеть так, как это показано на рисунке.
Рис. Чистые и дисконтированные денежные потоки
На этом графике инвестора интересует несколько значений. Во-первых, это срок окупаемости проекта. Такой срок определяется по времени, требующемуся для того, чтобы суммарные чистые доходы проекта сравнялись с его затратами. На графике это точка, в которой NCF нарастающим итогом выйдет на положительные значения.
Однако ни один инвестор не согласится расстаться с сегодняшними деньгами в пользу будущих, достаточно отдаленных доходов, если эти доходы будут лишь покрывать инвестиции. Поэтому в оценке эффективности проекта всегда используются дисконтированные денежные потоки, в которых NCF каждого года уменьшается на величину ставки дисконтирования по формуле:
где i – номер года проекта, а d – ставка дисконтирования. То есть будущие денежные потоки «обесцениваются» для инвестора с годовыми темпами, равными ставке дисконтирования.
Основными показателями эффективности инвестиций являются:
1) срок окупаемости – Ток;
2) чистый приведенный доход – NPV;
3) внутренняя норма доходности – IRR;
4) индекс прибыльности (рентабельности) – PI.
Срок окупаемости
Эффективность проекта оценивается через срок его окупаемости, то есть через определение периода времени, необходимого для покрытия средств, первоначально инвестированных в проект.
Правило срока окупаемости (правило возврата): инвестиционный проект может быть принят в том случае, если период его окупаемости меньше или равняется сроку реализации инвестиционного проекта.
В нашем примере по графику можно определить, что в 2011 году (то есть на третий год проекта) исходные затраты будут полностью покрыты. Учитывая, что длительность проекта 5 лет, по этому показателю он может быть принят.
Чистый приведенный доход (чистая приведенная стоимость) – NPV
Инвестиции оправдают себя только в том случае, если принесут новые ценности для инвестора. Чистая приведенная стоимость – это разность между рыночной стоимостью проекта и затратами на его реализацию. В EXCEL существует специальная функция, выполняющая операцию расчета NPV – ЧПС. Аргументы этой функции – значение процентной ставки за период и значения денежных потоков каждого периода. Применение функции может идти несколькими способами. Наиболее предпочтительно в качестве аргументов брать только поступления, а начальные вложения добавлять к рассчитанной приведенной стоимости поступлений. Разумеется, вложения должны быть отражены со знаком «плюс».
Основное правило NPV: инвестиционный проект следует рассматривать, если величина NPV – положительная. В случае отрицательного и нулевого значений проект должен быть отклонен.
В нашем примере NPV= $795,44. Значит, по этому показателю проект также можно будет принять.
Внутренняя норма доходности
Это ставка дисконтирования, приравнивающая сумму приведенных доходов от инвестиционного проекта к величие инвестиций, то есть вложения окупаются, но не приносят прибыль.
В EXCEL существует функция ВСД, которая позволяет вычислить IRR.
Основное правило IRR: если внутренняя норма доходности превышает запланированную доходность или доходность от альтернативных вложений, то проект стоит принять. Если наоборот – проект стоит отклонить.
В нашем примере IRR=30%, следовательно, по данному показателю проект тоже стоит принять.
Индекс прибыльности
Это отношение приведенной стоимости будущих денежных потоков от реализации инвестиционного проекта к приведенной стоимости первоначальных инвестиций. Его следует понимать, как дополнительную ценность, созданную на каждый вложенный рубль. При положительном значении NPV PI>1, при NPV< 0 PI<1. В нашем примере PI=1, 7647059.
При решении вопроса о целесообразности инвестиций пользуются
следующей сводной таблицей:
По совокупности показателей таблицы принимается решение об участии в проекте. Пример оформления работы приведен на рисунке:
Цель: Научиться применять в Excel технологию подбора параметра для анализа данных.
Теоретическая часть
Команда Подбор параметра (кнопка Анализ «что-если» вкладка Данные) обеспечивает вычисление значения аргумента (параметра) для заданного значения функции методом последовательных итераций.
Подбор параметра является основным методом исследования области допустимых значений для параметров модели. Если функционал имеет несколько параметров, подобный анализ выполняется последовательно для каждого параметра в отдельности, при этом задаваемое значение функции остаётся неизменным. После подбора параметра можно сравнить полученные результаты подбора с точки зрения их реалистичности. Например, получено отрицательное значение числа периодов амортизации или ставка процентов существенно превышает предельно допустимую.
При подборе параметра допускается использование каскада взаимосвязанных формульных выражений, связывающих значения различных ячеек, в том числе находящихся на других листах или в других рабочих книгах. Существенное ограничение для применения этого подхода – в каждый момент времени исследуется только один параметр функции.
Практическая часть
Задание 1. Используя режим подбора параметра определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250000р. (на основании файла «Ведомость ЗП», созданного в лабораторной работе №15).
Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинаково для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задаётся конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Порядок работы
Создайте рабочую книгу с именем с именем «Лабораторные № 20-23» в своей рабочей папке и откройте созданный в Лабораторной работе №15 файл «Ведомость ЗП».
Скопируйте содержимое листа «Ведомость ЗП» на Лист1 электронной книги «Лабораторные № 20-23» и присвойте ему имя «Подбор параметра».
Осуществите подбор параметра кнопкой Анализ «что-если» на вкладке «Данные».
Рис. 1. Задание параметров подбора параметра
В диалоговом окне Подбор параметра в первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G 12), во второй строке наберите заданное значение 250000, в третьей строке укажите адрес подбираемого значения % Премии (ячейка D 4), затем нажмите кнопку OK (рис. 2).
Рис. 2. Подтверждение результатов подбора параметра
Произойдёт обратный пересчёт % Премии. Результат подбора (рис. 3): если сумма к выдаче равна 250000р., то % Премии должен быть 562%.
Рис. 3 Подбор значения % Премии для заданной общей суммы
заработной платы, равной 250000р.
Задание 2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рис. 4.
Тема: Подбор параметра и организация обратного расчёта.
Цель: - изучение технологии подбора параметра при обратных расчетах.
Вид работы: фронтальный
Время выполнения: 2 часа
Задания к практической работе
Задание 1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250000 р. (на основании файла «Зарплата»).
Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений является ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Ход работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги. Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».
3. Осуществите подбор параметра командой Данные – Анализ «что - если» - Подбор параметра (рис. 1).
Рисунок 1 - Задание параметров подбора параметра.
В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку OK. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК (рис.2.).
Рисунок 2 - Подтверждение результатов подбора параметра.
Произойдет обратный пересчет % Премии. Результаты подбора (Рис. 3.):
Если сумма к выдаче равна 250000 р., то % Премии должен быть 203 %
Рисунок 3 - Подбор значения % Премии для заданной общей суммы заработной платы, равной 250000 р.
Задание 2. Используя режим подбора параметра, определить штатное расписания формы. Исходные данные на рис. 4.
Краткая справка. Известно, что в штате фирмы состоит:
8 младших менеджеров;
10 менеджеров;
3 заведующих отделами;
1 главный бухгалтер;
1 программист;
1 системный аналитик;
1 генеральный директор фирмы.
Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai*x+Bi, где x – оклад курьера; Ai и Bi – коэффициенты, показывающие:
Ai - во сколько раз превышает значение x;
Bi – на сколько превышается значение x.
Ход работы
1. Запустите редактор электронных таблиц Microsoft Excel.
2. Создайте таблицу штатного расписания фирмы по приведенному образцу (рис. 4). Введите исходные данные в рабочий лист электронной книги.
Рисунок 4 - Исходные данные для Задания 2.
3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
4. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = B6*$D$3 + C6 (ячейка D3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.
В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид = D6*E6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.
В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.
5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100000 р. Для этого в меню Данные активизируйте команду Подбор параметра.
В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;
в поле Значение наберите искомый результат 100000;
в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000 р.
6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.
Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 1 и 2 показывают, что поиск значения параметра формулы – это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel, можно решить любые уравнения с одной переменной.
Задание 3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 2), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.
Ход работы
1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициент уравнений для расчета согласно табл. 1. (один из пяти вариантов расчетов).
2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 р. Результаты подбора значений зарплаты скопируйте в табл. 2. в виде специальной вставки.
Рисунок 5 - Специальная вставка значений данных
Специальная вставка информации в виде значений позволяет копировать значения, полученные в результате расчетов, без дальнейшей их зависимости от пересчета формул.
При решении экономических и финансовых задач достаточно часто решение задачи приобретает вид математической модели (формулы), построенной на основе выявленных связей между изучаемыми элементами явления (результативного показателя) и самим явлением в целом. Изменяя составляющие элементы, можно проводить анализ изменения явления в целом. Таким образом, изучаются свойства и значения функции (явления) в тех или иных условиях. Этот анализ позволяет проводить инструмент MS Excel Подбор параметра. Рассмотрим возможности его применения на конкретных примерах.
Нахождение значения аргумента функции, соответствующего определённому значению функции
Использование функции Подбор параметра для нахождения значения аргумента функции, соответствующего заданному значению функции может быть представлено таким образом: поиск определенного результата для ячейки с помощью подбора значения другой ячейки.
Например, одна ячейка содержит формулу, в которой есть ссылки на другую ячейку.
Значение в ячейке С1 представляет собой среднее арифметическое значение в ячейках А1 и В1:
Допустим, что для целей исследования необходимо найти значение, которое должна принять ячейка А1, для того чтобы ячейка С1 приняла значение 855.
Безусловно, можно самостоятельно путём перебора значений в ячейке А1 достичь необходимый результат. Однако, в целях минимизации затрат времени следует воспользоваться функцией Подбор параметра.
Для этого необходимо:
1) выполнить команду Подбор параметра из меню Данные > Анализ "что-если".
В результате появится запрос Подбор параметра:
2) в поле Установить в ячейке ввести ссылку или имя ячейки, содержащую формулу, для которой следует подобрать параметр. Автоматически в поле Установить в ячейке отображается имя ячейки, которая была активной на момент выполнения команды Подбор параметра. Кнопка свёртывания окна диалога, расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует нажать кнопку для вывода на экран диалогового окна.
3) в поле Значение ввести число, которое должно возвращать формула с искомым значением параметра. Например, 855.
4) в поле Изменяя значение ячейки указать ссылку на ячейку, содержащую параметр, значение которого требуется подобрать для получения требуемого результата. На эту ячейку прямо или косвенно должна ссылаться формула, содержащаяся в ячейке, адрес которой указан в поле Установить в ячейке. В нашем случае это А1.
В итоге диалоговое окно примет следующий вид:
5) нажать кнопку ОК для закрытия диалогового окна. После выполнения этого действия появляется запрос Результат подбора параметра, а искомое значение параметра отображается в ячейке А1:
Использование функции Подбор параметра для нахождения значения аргумента функции при изменении вида ее графика
Допустим, что для решения поставленной задачи нам предстоит проанализировать построенный в Ms Excel график функции y = 3x-5 в диапазоне аргумента от –3 до 6.
Для этого следует:
1) в ячейки А1-А10 ввести значения от –3 до 6 с шагом 1; в ячейку В1 – ввести формулу и путём перетаскивания маркера заполнения скопировать эту формулу на ячейки В2-В10. В результате соответствующий участок листа примет следующий вид:
2) выделив диапазон В1-В10, выберите тип диаграммы "График" на вкладке Вставить в группе Диаграммы.
3) На вкладке Макет в группе Подписи нажмите кнопку Подписи данных, а затем выберите нужный параметр отображения.
4) На вкладке Конструктор в группе Данные нажмите Выбрать данные.
5) В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон А1-А10.
В результате должен быть построен график функции:
Далее предположим, что необходимо узнать значение аргумента данной функции, при котором значение самой функции будет равно 0.
Чтобы решить эту задачу с помощью построенного графика и функции Подбор параметра необходимо:
1) щелчком левой кнопки мыши на графике выделить ряд данных, содержащий маркер данных, который нужно изменить,
а затем выделить щелчком сам маркер
2) в меню Данные > Анализ выбрать функцию Поиск решения
3) если значение маркера данных получено из формулы, появится диалоговое окно Подбор параметра:
4) в поле Оптимизировать целевую функцию отображается ссылка на ячейку, содержащую формулу, в поле Значения – требуемая величина. Так, в данном случае следует указать ячейку В6 и значение "0" соответственно и нажать кнопку ОК.
При поиске решения можно изменять только одну ячейку.
При этом исходное значение аргумента в ряде данных сменится на значение, полученное в результате поиска решения 1,666667 (рис. 11. рис. 11.11).
Решение уравнений
может принимать значение 0 при двух значениях аргументов.
Однако, функция Поиск решения найдет только один корень уравнения – самый близкий к значению в ячейке, указанной в поле Оптимизировать целевую функцию.
Так, если попытаться решить указанное выше уравнение с помощью Ms Excel и встроенной в него функции Подбор параметра, то исходные данные можно представить в следующем виде:
Выполнив команду Подбор параметра из меню Данные > Анализ "Что-если", необходимо заполнить поля диалогового окна следующим образом:
В результате найденным корнем уравнения будет значение 2,121343 в ячейке А4 (рис. 11. рис. 11.14).
.
Для построения графика следует:
- в ячейки С4-С24 ввести значения от –10 до 10 с шагом 1; в ячейку D4 – ввести формулу 2*C4*C4-9 и путём перетаскивания маркера заполнения заполнить этой формулой ячейки D5-D24 ;
- выделив диапазон D4-D24 , выбрать тип диаграммы График в меню Вставка > Диаграммы;
- На вкладке Конструктор в группе Данные нажмите Выбрать данные.
- В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон С4-С24.
В результате должен быть построен график функции:
имеет 2 корня, к тому же эти корни примерно равны –2 и 2. Одни корень 2,121343 нам уже известен.
Для поиска второго корня можно поступить двояко, используя пункт А или Б методических указаний ниже:
А. Изменим значение, например, в ячейке С12 (более близкое к ожидаемому корню). Выделим ячейку D12 и выполним команду Подбор параметра из меню Данные > Анализ "Что-если". Заполним поля запроса:
и после щелчка по кнопке ОК в ячейке С12 получим значение второго корня -2,12125:
Б. Построим график функции в интервале от -10 до 10.
Щелчком левой кнопки мыши на графике выделим ряд данных, содержащий маркер данных, близкий ко второму корню:
Сделаем активной ячейку D11 со значением функции, равным 9 и в меню Данные > Анализ "что-если" выберем Подбор параметра. Заполним поле Изменяя значение ячейки запроса:
и щелкнув по кнопке ОК, в ячейке С11 получим значение второго корня -2,1213207:
Следует обратить внимание, что значения корня, полученные в п.А и п.Б имеют несущественное отличие. Это вызвано следующим обстоятельством. По умолчанию команда Подбор параметра прекращает итерационные вычисления, когда выполняется 100 итераций, либо при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры командой Параметры меню Файл > Формулы. Затем на вкладке Параметры вычислений в поле Предельное число итераций введите значение больше 100, а в поле Относительная погрешность – значение меньше 0,001.
Если Ms Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне запроса Результат подбора параметра и прервать вычисления, а затем нажать кнопку Шаг, чтобы просмотреть результаты каждой последовательной итерации. Когда Вы решаете задачу в пошаговом режиме, в этом окне запроса появляется кнопка Продолжить. Нажмите ее, когда решите вернуться в обычный режим подбора параметра.
Задание
Решить уравнение с использованием инструмента Excel Подбор параметра двумя способами:
- подбором аргумента для конкретного значения функции;
- посредством изменения графика функции.
Удостовериться с помощью построения графика в количестве корней уравнения. Определить все корни.
Читайте также: