Решение задачи подбор параметра

Подбор параметра является частью блока задач, который называют инструментами анализа "что-если". Используется, когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата. На вкладке Данные в группе Работа с данными выбрать пиктограмму Анализ «что-если», а затем выбрать в списке пункт Подбор параметра.

В окне команды Подбор параметра необходимо заполнить поля:

- Установить в ячейке: вводят адрес ячейки, содержащей формулу;

- Значение: вводят искомое значение;

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

В ячейку, задаваемую в поле Изменяя значение ячейки по определенному алгоритму подставляются значения до тех пор, пока не будет найдено решение, задаваемое в поле Значение. По умолчанию по команде Подбор параметра вычисления прекращаются либо после 100 операций, либо когда найденное решение отличается от заданного не более чем на 0.001. Если требуется большая точность, то следует нажать кнопку Office, выбрать команду Параметры - Формул ы и изменить поле Относительная погрешность.

Рассмотрим пример нахождения всех корней уравнения

х³ - 0,01х² - 0,7044х + 0,139104 = 0.

Для нахождения корней их предварительно нужно локализовать. С этой целью необходимо построить график функции или ее протабулировать (построить таблицу значений). Например, протабулируем полином на отрезке [-1, 1] с шагом 0,2. Для этого в ячейки А2:А11 введем значения х, в ячейку В2 введем следующую формулу:

=A2^3-0.01*A2^2-0.7044*A2+0.139104

Построим график функции, Из графика видно, что полином меняет знак на интервалах: [-1, -0,8], [0,2, 0,4] и [0,6, 0,8]. Это означает, что на каждом из них имеется корень данного полинома.

Возьмем, например, их средние точки: -0,9; 0,3; и 0,0,37 и введем их соответственно в диапазон ячеек С2:С4. В ячейку D2 введем формулу

=С2^3-0,01*С2^2-0,7044*С2+0,139104

Выделим эту ячейку и с помощью маркера заполнения протащим введенную в нее формулу в диапазоне D2:D4. Таким образом, в ячейках D2:D4 вычисляются значения полинома при значениях аргумента, введенного в ячейки С2:С4, соответственно.

Найдем корни полинома методом последовательных приближений с помощью команды Подбор параметра.

- в поле диалогового окна Установить в ячейке введите ссылку на ячейку D2 (в этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения);

- в поле Значение введите 0 (в этом поле в данном случае указывается значение правой части уравнения). Для нахождения корня уравнения с помощью средства подбора параметров надо записать уравнение так, чтобы его правая часть не содержала переменную;

- в поле Изменяя значение ячейки введите ссылку на ячейку С2 (в этом поле дается ссылка на ячейку, отведенную под переменную).

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

После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помещает в ячейку С2. В данном случае оно равно -0,9199995441260787.

Аналогично в ячейках СЗ и С4 находим два оставшихся корня. Они равны, соответственно, 0,2100004312921519 и 0,719999779247747.

 

 

Задание на лабораторную работу

5.3.1  На основании таблицы "Доходы фирм по продаже компьютеров в 1- ом полугодии текущего года" (таблица 5.1) постройте:

 

Таблица 5.1 - Доходы фирм по продаже комрьютеров (тыс руб)
Фирма	январь	февраль	март	апрель	май	июнь
фирма1	120	90	130	127	100	69
фирма2	78	86	102	133	90	84
фирма3	160	155	150	158	140	162

 

1) гистограмму распределения доходов фирмы1 по месяцам 1 квартала (январь, февраль, март).

2) график распределения доходов фирмы2 за полгода

3) гистограмму доходов всех фирм за полгода

4) круговую диаграмму доходов всех фирм в январе

5.3.2 Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента в соответствии с вариантом из таблицы 5.2, результаты оформить в виде таблицы. Построить график функции.

На графике:

- разными маркерами выделить максимальное и минимальное значения функции;

- дать названия диаграмме и осям;

- метки на оси X представить в формате с фиксированной точкой, а на оси Y – с плавающей точкой.

5.3.3. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента в соответствии с вариантом из таблицы 5.3, результаты оформить в виде таблицы. Построить поверхность.

5.3.4. Найти решения уравнения согласно варианту в таблице 5.4. Для поиска корней строим график функции (левая часть уравнения). Аргументы необходимо задать таким образом, чтобы график функции пересекал ось абцисс. Решением уравнения являются значения аргумента, которые обращают функцию в 0. Эти значения выбираются по графику, а затем уточняются с помощью средства «Подбор параметра».

 

Таблица 5.2 – Варианты заданий для построения графика

№ вар	Функция	Интервал изменения аргумента х	Шаг изменения аргумента х
1	y =		0.2
2	y =		0.4
3	y =		0.2
4	y =		0.2
5	y =		0.2
6	y =		0.2
7	y =		0.1
8	y =		0.2
9	y =		0.2
10	y =		0.2
11	y =		0.2
12	y =		0.2
13	y =		0.2
14	y =		0.2

 

Таблица 5.3 – Варианты заданий для построения поверхности

№ вар	Функция	Интервал изменения аргументов х, y	Шаг изменения аргументов х,у
1			0.1
2			0.2
3			0.2
4			0.2
5			0.1
6			0,1
7			0,2
8			0,1
9			0,2
10			0,2
11			0,1
12			0,1
13			0.1
14			0.1

 

Таблица 5.4– Варианты заданий для решения уравнения

№ вар	Уравнение
1
2
3
4
5
6
7
8
9
10
11
12

Содержание отчета

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

 

5.5 Контрольные вопросы

 

1. Какие средства имеются в электронных таблицах Excel для построения графиков и диаграмм?

2. Какие этапы необходимо выполнить для построения графика с помощью мастера диаграмм?

3. Как изменить формат отображения ряда данных на диаграмме?

4. Какие изменения можно внести при редактировании формата оси на диаграмме?

5. Какие типы диаграмм вы знаете. Как изменить вид диаграммы?

 

СПИСОК ЛИТЕРАТУРЫ

1. Безручко Т.В. Практикум по курсу " Информатика": работа в Windows, Word, Excel / В. Т. Безручко. - М.: Финансы и статистика, 2002. - 270 с.: ил.

2. Бобцов А.А. Эффективная работа с пакетом программ Мicrosoft Office. Учебно-методическое пособие./ А.А.Бобцов, Е.В.Рукуйжа. – СпбГУ ИТМО, 2008. – 129 с.

3. Информатика: базовый курс: учеб. пособие для студ. втузов / Ред. С. В. Симонович. - СПб. и др.: Питер, 2008. - 640 c.: ил.

4. Каймин В.А. Информатика: учеб./ В. А. Каймин. - 3-е изд. - М.:ИНФРА - М, 2003. - 272 с.

5. Корнелл;П. Анализ данных в Excel. Просто как дважды два /П. Корнелл; пер. с англ. – М.:Эксмо, 2007. – 224 с.: ил.

6. Рудикова Л.В. Мicrosoft Excel для студента. СПб.:БХВ-Петербург, 2005. – 368 с.: ил.