Порядок решения: с использованием функции Поиск решения.

1.Введем в ячейку В17 формулу 3*С11^2+2*C11-15. В ячейку С17 введем начальное значение х=-1. Результат приведен на рис. 2 в столбцах В и С.

2.Введем команду Сервис, Поиск решения – открывается одноименное окно диалога (рис. 4.).

Введем в строку “Установить целевую ячейку” адрес ячейки, содержащей формулу В17, установим переключатель Значение и в строку вода “значению” введем значение “0”, а в строку “Изменяя ячейки” введем адрес ячейки содержащий значение Х - С17 с абсолютным адресом. Щелкнем по кнопке Выполнить. Открывается окно “Результаты поиска решения” (рис. 5). Для получения результата щелкните по кнопке ОК. В ячейке В17 отобразится значение точности поиска решения, а в ячейке С17- значение корня. Для сравнимости результатов на рис. 2 результаты помещены в столбцах E и F. Результат решения не зависит от выбранного начального значения корня. Сравните результаты в строках 17 и 18. Однако Функция Поиск решения, также как и функция Подбор параметра позволяет найти только один корень уравнения. Для получения значения второго корня необходимо изменить начальное значение. В строке 19 приведен результат поиска второго корня.

Второй корень можно найти при любом начальном значении Х, если установить ограничения на его значение. Щелкните по кнопке Добавить в окне диалога Поиск решения (рис. 4). Открывается окно диалога “Добавление ограничения” (рис. 6). Выберите в списке “Ссылка на ячейку” адрес ячейки, содержащей значение х, например, D20, в среднем списке - знак отношения, а в списке “Ограничения” введите значение ограничения, например, “0” и щелкните по кнопке ОК. Результат будет записан в окне “Ограничения” окна диалога Поиск решения (рис. 4). Пример решения приведен в строке 20 (рис. 2).

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

Пример 3. Задача о “пожарном ведре”. Дана заготовка из жести в виде круга диаметром R=0,75 м. Требуется выкроить из него конусообразное ведро таким образом, чтобы объем ведра был наибольший (рис. 7).

Разработаем математическую модель:

Объем пожарного ведра Q=1/3hS_осн, S_осн= pr²,

где r – радиус основания конуса, h – высота ведра, h=корень(R²-r²)

Радиус основания зависит от угла вырезки a. Длина окружности основания ведра l=(2p-a)R или l=2pr, отсюда

 

r=(2p-a)R/ (2p),
Q=1/3*Корень(R²-((2p-a)R/ (2p))^2)*p*(2p-a)R/ (2p))² (1)

Задача о пожарном ведре
Q=1/3*Корень(R^2-(2p-a)R/ (2p))*p*(2p-a)R/ (2p))^2
Исходные данные:
R=	0,75
Угол альфа	Целевая функция
1,84	0,046875
Рис. 8. Оптимизация

Объем ведра Q должен максимизироваться, поэтому выражение (1) называют целевой функцией. Оптимизируемым параметром является угол a. Ограничение в данном выражении одно: угол a должен быть больше 0, но меньше 2p. Решение приведено на рис. 8. Окно диалога приведено на рис. 9.

Библиографический список

 

1. Информатика. Базовый курс. 2-е издание / Под ред. С. В. Симоновича. — СПб.: Питер, 2005. — 640 с: ил.

2. Анеликова Л. А. Лабораторные работы по Excel. - M.: СОЛОН-ПРЕСС. 2006 128 с: ил. — (Серия «Элективный курс • Профильное обучение»)

3. Excel. Единый справочник/В. Н. Шитов. — М.: ГроссМедиа, 2005. - 512 с.

4. Решение математических задач средствами Excel: Практикум /В. Я. Гельман. — СПб.: Питер, 2003. — 240 с: ил.

5. Быков В. Л., Ашаев Ю. П. Основы информатики. Пособие. – Брест, Издательство БрГТУ, 2006