Задание 13. Выполнение процедуры Подбор параметра для поиска корней уравнения
Объем получаемой прибыли описывается следующей зависимостью:
Х3-2,92*Х2+1,4355*Х+0,7911136 (6.1)
Необходимо найти при каком значении параметра Х объем прибыли принимает значение равное нулю, то есть корни уравнения на интервале значений Х от-1,5 до 3
Для этого заполните значения Х на указанном диапазоне с шагом 0,25.
В ячейку A2 введите -1,5, в A3 -1,25.
Выделите обе ячейки (A21:A22) и с помощью маркера заполнения растяните до A39.
В B21 введите формулу уравнения (рис. 6.9) и скопируйте ее для всего диапазона Х.
Рис. 6. 9. Расчетная таблица после выполнения вычислений
Для наглядности построим точечный график (рис. 6.10).
Рис. 6. 10. График функции
На диаграмме вы видите, что график пересекает ось Х приблизительно в точках –0,3; 1,2; 2. Для того чтобы определить точные значения воспользуйтесь процедурой Подбора параметра.
В интервале значений Х от – 0,5 до – 0,25 Y переходит через нулевое значение.
Курсор установите в ячейку Y26, так как там значение Y находится ближе к нулю.
Выберите команду Подбор параметра (рис. 6.6).
В поле «Установить в ячейке» появившегося окна (рис. 6.11) введите ссылку на ячейку В26, содержащую формулу;
Рис. 6. 11. Окно процедуры Подбор параметра
В поле «Значение» наберите искомый результат 0;
В поле «Изменяя значение ячейки» введите ссылку на изменяемую ячейку А26 и щелкните на кнопке ОК.
В ячейке А26 получите новое значение Х, при котором по данной формуле наиболее близко удалось приблизиться к значению Y, равной нулю (рис. 6.12).
Рис. 6. 12. Расчетная таблица после выполнения подбора параметров
Аналогичным образом выполните подбор параметра в оставшихся двух местах пересечения оси Х.
Примечание. Вы видите, что полученные значения Y не точно равны нулю. Но это все, что при данных установках мог достичь Excel.
Анализ задачи показывает, что с помощью Excel можно решать уравнения. Однако благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, - это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.
3.2.Таблицы подстановки
Таблицы подстановки
Задачу прогнозирования для нескольких вариантов условий можно также решить с помощью таблиц подстановки. Вообще таблицами подстановки называются такие таблицы, которые автоматически заполняются результатами расчетов по одной или нескольким формулам. Эти формулы должны ссылаться на ячейки ввода – ячейки рабочего листа, в которые Excel поочередно подставляет варианты переменной, чтобы получить результат расчета для заполнения таблицы подстановки. Существует два типа таблиц подстановки: таблицы с одним входом (одной переменной) и таблицы с двумя входами (двумя переменными).
Задание 14. Таблица с одним входом
Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами. Можно использовать любое количество ссылок на формулы. Верхняя левая ячейка таблицы не используется.
Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в ячейки соответствующего столбца.
Рис. 6. 13. Таблица подстановки с одним входом
Воспользуемся тем же примером расчета, который рассматривали при подборе параметров. Для этого откройте лист «Задание 12».
На рис. 6.5. отображены результаты расчетов при цене товара, равной 1 руб. Создайте таблицу подстановки для следующих вариантов цены: 5, 10, 15, 20, 25 и 30 руб. Для этого нужно выполнить следующие операции:
В ячейки строки E9:J9 и столбца D2:D7 последовательно введите заданные варианты переменной (цены).
Следующие действия зависят от того, введены варианты в строку или в столбец:
Для таблицы подстановки, организованной в столбцы, следует:
В ячейку E2 скопировать формулу прибыли (из ячейки В8). Правее этой ячейки можно ввести другие формулы, например, расчета расходов (из В3 в F2) – рис. 6.17.
Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D2:F8 –для строк). В диапазон попадает ячейка, содержащая только текст (пустая ячейка), набор цен и формулы расчета прибыли и расходов.
На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» - Таблица данных…(рис. 6.14).
Рис. 6. 14. Выбор команды подстановки таблицы данных
Появится диалоговое окно «Таблица подстановки» (рис. 6.15), в которые нужно ввести адрес ячейки ввода с формулой расчета прибыли.
Рис. 6. 15. Окно задания параметров в вертикальную таблицу подстановки
После нажатия кнопки ОК Excel поочередно подставит в ячейку ввода варианты переменных, а результатами расчетов заполнит соответствующие ячейки таблицы подстановки (рис. 6.17).
Для таблицы подстановки, организованной в строки, следует:
В ячейки D12 и D13 скопируйте формулу прибыли из ячейки В8 ( рис. 6.17).
Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D11:J13).
На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» - Таблица данных… (рис. 6.14).
В диалоговом окне «Таблица подстановки» (рис. 6.16) введите адрес ячейки ввода с формулой расчета прибыли.
Рис. 6. 16. Окно задания параметров в горизонтальную таблицу подстановки
Рис. 6. 17. Результаты выполнения процедуры Таблица данных с одним входом
Для любой таблицы данных постройте график зависимости прибыли и расходов от цены (рис. 6.18). Для этого
Рис. 6. 18. График зависимости прибыли и расходов от цены (логарифмическая шкала)