Файл: Практикум Для студентов вузов Кемерово 2013 4 удк 004 (076) ббк 32. 81я7 И74.pdf

3. Электронные таблицы Excel
127
Рис. 3.15. Структура таблицы для решения системы уравнений
7. Мышью скопировать формулу из ячейки Е2 в ячейки Е3 и Е4. В соответствии с правилами копирования формул, в этих ячейках будут вычислены значения левых частей второго и третьего уравнений при значениях неизвестных, находящихся в одном и том же диапазоне А7:С7.
8. Назначить для ячеек диапазонов А2:С4, D2:D4, Е2:Е4,
А6:С7 стиль «Студенческий». Дополнительно обвести каждый из указанных диапазонов жирной рамкой (толщина 2,25–2,5).
Установить для ячеек диапазона А7:С7 формат «Числовой» с пятью десятичными знаками.
9. Запустить модуль «Поиск решения» командой «Дан- ные» – «Анализ» – «Поиск решения». Диалоговое окно настрой- ки модуля показано на рис. 3.16.
Одна из ячеек, в которых вычислены значения выражений в левых частях уравнений, в данном случае Е2, выбирается в качестве целевой. Ее адрес указывается в окне ввода «Устано- вить целевую ячейку». Компьютеру дается задание: подобрать значения неизвестных в ячейках А7:С7 так, чтобы значение це- левой ячейки стало равным значению правой части (свободного члена) первого уравнения системы – т.е. чтобы первое уравне- ние оказалось выполненным. Для этого переключатель «Рав- ной» устанавливается на позицию «Значению» и в соответст- вующее окно ввода с клавиатуры заносится численное значение свободного члена первого уравнения (в данном случае нельзя пользоваться ссылкой на ячейку, содержащую это значение).

3. Электронные таблицы Excel
128
Рис. 3.16. Окно настройки модуля «Поиск решения»
Требования выполнения двух других уравнений образуют дополнительные условия, накладываемые на значения перемен- ных. Эти условия заносятся в окно ввода «Ограничения» с по- мощью диалогового окна «Добавление ограничения» (рис. 3.17), появляющегося после нажатия виртуальной клавиши [Добавить].
Рис. 3.17. Окно добавления ограничений
Это окно содержит три внутренних окна ввода. В окно
«Ссылка на ячейки» вставляется ссылка на ячейку, содержащую формулу вычисления левой части второго уравнения, в окно
«Ограничение» – ссылка на ячейку, содержащую значение пра- вой части этого уравнения (здесь можно использовать способ быстрого создания ссылок щелчком по нужной ячейке). Второе окно ввода содержит один из знаков отношений (равенства или неравенства) и значок раскрывающегося списка. Раскрыв спи-

3. Электронные таблицы Excel
129 сок, можно с помощью мыши выбрать нужный знак – в данном случае – знак равенства. Затем следует щелкнуть [Добавить], если надо добавить следующее ограничение либо [OK], если вставка ограничений закончена. Таким же образом в окно
«Ограничения» вставляется второе условие (E4=D4), учиты- вающее третье уравнение системы. В окно ввода «Изменяя ячейки» (рис. 3.16) вставляются ссылки на ячейки, предназна- ченные для размещения искомого решения (диапазон А7:С7).
После выполнения этих подготовительных действий щелчком нажимается виртуальная клавиша [Выполнить].
Если компьютеру удается подобрать значения неизвест- ных, обеспечивающие выполнение как первого уравнения сис- темы (за счет выполнения целевого требования), так и второго, и третьего уравнений (за счет выполнения дополнительных ус- ловий), в возникающем диалоговом окне «Результаты поиска ре- шения» появляется сообщение: «Решение найдено» (рис. 3.18).
Имеющийся в окне переключатель позволяет либо занести в таблицу полученное решение, либо отменить его и восстановить ранее внесенные в ячейки значения.
Рис. 3.18. Извещение о результатах поиска решения
10. Щелкнуть в окне «Результаты поиска решения» клавишу [ОК].
Описанный метод применим как для систем линейных, так и для систем нелинейных алгебраических уравнений.
Замечание. Модуль «Поиск решения» так же, как и «Под- бор параметра», позволяет найти решение с ограниченной точ- ностью. Более точно решение системы уравнений можно найти,

3. Электронные таблицы Excel
130 используя методы линейной алгебры (для линейных уравнений) или специальные методы вычислительной математики (для уравнений произвольного вида).
Варианты заданий
Таблица 3.6
Варианты нелинейных уравнений для решения с помощью модуля «Подбор параметра»
№
Уравнение
Примечание
1 ctg
5
x
x

x

(0,

)
2 2
1
x
e
x

 
3 5
ln
x
x
 
4 ch
1
x
x


5 sh ctg
5
x
x

x

(0,

)
6


5 1
x
x


7 3
1 sin
x
x
 
8 3
1
x
x
e

 
9 3
cos
x
x

10 1
ch
10
x
x
e


11


3
ln
3 2
x
x
  
12 ln
0,5
x
e
x
x



13 th
2
x
x
 
14 1
x
x
e
x



15 1
2ln
5
x
x
 

3. Электронные таблицы Excel
131
Таблица 3.7
Варианты систем линейных уравнений для решения с помощью модуля «Поиск решения»
№
Коэффициенты уравнений
Своб. члены
№
Коэффициенты уравнений
Своб. члены
2,5 1
–0,5 4,8 3,7 3,7 2,7 15,5 1
2 1,5 6,4
–11,36 2 3,1
–1,5
–4,9
–12,18
–3,2 2,7
–4 11,36 1,4 6,4
–3,4
–3,4 3,2 0,8 –1,5
–8,55 5,6 0,9
–1,4 3,24 3
0,5
–3,5 3
–4,9 4
1,1 0,9 2,2 8,64
–2,7 4
2 20,15
–0,2 3,3 3,7 13,5 5,3 3,1 1,2 4,13 3,8 2,1 0,5 7,74 5
1,2
–6 2
6,36 6
0,6
–1,6 4
11,44
–0,8 4,2 3,1 0,98
–2 2
–0,8
–3,2
–3,2 2,5 –2,3 –1,98 0,25
–1,25 2,75
–1,575 7
1,2
–1,3 6,8 13,03 8
6,6 2,4
–4
–14,42 3,5 2,8 0,1
–0,14
–4 2
2,6 12,92 2,8 6,7 1,6 1,82 1,7 1,1 4,2 7,67 9
3,5 0,5 –0,8 2,73 10 0,6 2,4 1,5 4,32 0,9 5
–3,5 –1,59
–3 2
2,6
–8,38 2,7 4
0,5
–7,16 4,8 0,2 3,1 2,9 11 3,7 0,8
–1
–2,04 12 0,6
–2,4 5,2 2,28 1,2 6,1 –3,8 –32,67 4,4
–2 2,6 1,64 5,6 0,9 –1,4 16,74 4,7 0,3 3
5,94 13 1,3 0,8
–2 8,3 14 0,7 2,5 5,2 4,98
–0,8 3,1 –3,8 11,42
–3 2,1
–4
–3,23 5,8 1,1 –0,9 13,76 3,7 1,3 0,8 9,81 15 1,7 0,5 –2,5
–7,73 16 1
2,4 5,1 18,06
–1,6 3,9 2,4 9,71
–3 0,4
–0,6
–5,22

3. Электронные таблицы Excel
132
Лабораторная работа № 3
Обработка экспериментальных данных
Цель работы: освоение простейших приемов обработки экспериментальных данных с помощью электронных таблиц.
Предварительные сведения
С помощью Excel можно быстро произвести несложную обработку данных, полученных в ходе экспериментального ис- следования (например, при выполнении лабораторной работы по физике).
Рассмотрим процесс экспериментального изучения функ- циональной связи у(х) одного влияющего фактора х и зависящей от него величины у. Для этого производятся измерения значений
у
i
при различных значениях фактора x
i
(i=1, 2, …, N). По резуль- татам измерений необходимо построить зависимость y(x).
Поскольку при каждом измерении неминуемо возникают ошибки, которые носят случайный характер, по результатам эксперимента удается произвести не точное определение функ- ции y(x), а оценку возможной зависимости с помощью какой-то предполагаемой функции
 
y x
. Эта предполагаемая функция называется регрессией у на х, а построение регрессии – регрес-
сионным анализом данных.
Выбранная форма регрессии может соответствовать ис- тинной функции y(x), форма которой вытекает, например, из законов физики или химии. Тем не менее, параметры этой функции из-за наличия ошибок измерения все равно определя- ются с некоторой погрешностью. Часто истинная форма связи у
и х вообще не известна, а форма регрессии выбирается из дру- гих соображений (например, удобства использования).
Прежде всего, анализируя диаграмму рассеяния – распо- ложение экспериментальных точек, нанесенных на координат- ную сетку (иногда употребляется термин «облакоточек»), вы- бирают качественный вид регрессионной функции. Чаще всего используют сравнительно простые функции:

3. Электронные таблицы Excel
133

линейную:
y
a bx
 
;

степенную:
b
y
ax

;

экспоненциальную:
bx
y
ae

;

показательную:
x
y
ab

;

гиперболическую:
b
y
a
x
 
Все эти функции имеют по два параметра (коэффициента)
a и b. Функции с бо льшим числом параметров применяются ре- же.
В основе регрессионного анализа лежит метод наимень-
ших квадратов. В соответствии с ним, форма и параметры рег- рессии должны быть такими, чтобы экспериментальные точки равномерно располагались по обе стороны графика регрессии.
Это соответствует предположению, что ошибки измерения но- сят случайный характер и измеренные значения могут с одина- ковой вероятностью оказаться как меньше, так и больше истин- ных значений у.
Если предполагать, что регрессия дает оценку истинной зависимости y(x), то аналогом ошибки измерения становится
отклонение – разность между измеренным значением y
i
и значе- нием регрессии при том же значении фактора x
i
. Если отклоне- ние может с равной вероятностью быть положительным или от- рицательным, его квадрат всегда неотрицателен. Чем меньше абсолютная величина отклонения, тем меньше и его квадрат.
Отсюда можно заключить, что параметры выбранной регрессии следует подобрать так, чтобы минимизировать функционал
7


2
( )
i
i
S
y
y x



,
(3.8) то есть сумму квадратов отклонений измеренных значений от регрессионных. Отсюда происходит название метода регресси- онного анализа – «метод наименьших квадратов».
_____________
7
Функционалом («функцией от функции») называется величина, зна- чение которой зависит от вида функции, входящей в ее выражение; в данном случае – от совокупности числовых значений параметров регрессионной функции.

3. Электронные таблицы Excel
134
Линии тренда
Для быстрого определения параметров и построения гра- фика регрессии одного из простых видов в Excel имеется встро- енное средство – построение линии тренда. Под линией тренда
(тенденции) понимается зависимость, так или иначе отражаю- щая изменение величины y в зависимости от х. Если линия тренда определяется какой-то алгебраической функцией, Excel отыскивает параметры этой функции по методу наименьших квадратов. Помимо перечисленных выше функций, в качестве регрессии при построении линии тренда можно выбрать поли- ном степени 2

6. При построении линии тренда можно одно- временно вывести на диаграмму ее уравнение.
Особым видом линии тренда является линейный фильтр, представляющий собой кусочно-линейную функцию. Он ис- пользуется редко и в данной работе не рассматривается.
Задания для самостоятельной работы
Для набора из 10 пар экспериментальных значений, при- веденных в табл. 3.8 (согласно индивидуальному варианту):
1. Подобрать форму регрессионной кривой и определить параметры регрессии с помощью модуля «Поиск решения».
2. Построить линию тренда, определить ее параметры.
3. Сравнить величины сумм квадратов отклонений для двух полученных регрессионных зависимостей.
4. Для обоих случаев построить диаграммы рассеяния с графиками регрессии.
Порядок выполнения работы
Определение параметров регрессии с помощью модуля
«Поиск решения»
Итоговый вид таблицы показан на рис. 3.19.
Последовательность действий:
1. По данным исходной таблицы результатов измерений
(диапазон А2:В11) построить точечную диаграмму, состоящую из отдельных точек («Вставка» – «Диаграммы» – [Точечная] –
«Точечная с маркерами»). По виду расположения точек выбрать

3. Электронные таблицы Excel
135 качественный вид функции, отображающей такую зависимость
(для диаграммы рассеяния, показанной на рис. 3.16, выбрана экспоненциальная функция).
2. В двух ячейках (D1:D2) задать произвольные начальные значения параметров a, b.
3. Вычислить в ячейках E2:E11 значения регрессионной функции. Для этого в ячейку Е2 ввести формулу функции с ис- пользованием абсолютных ссылок на ячейки D1 и D2. В качест- ве аргумента использовать значение из ячейки А2. Затем мы- шью скопировать формулу в ячейки Е3:Е11.
4. В ячейках F2:F11 вычислить отклонения значений рег- рессии от экспериментальных значений y
i
5. В ячейках G2:G11 вычислить квадраты отклонений.
6. В ячейке G12 вычислить сумму квадратов отклонений
(3.8) одним из следующих способов (по своему выбору):
Рис. 3.19. Таблица для регрессионного анализа а) использовать прием автосуммирования по диапазону- столбцу G2:G11. Для этого выделить диапазон и дополнитель- ную пустую ячейку G12, затем щелкнуть кнопку «Главная» –

3. Электронные таблицы Excel
136
«Редактирование» – [

]. При этом в пустой ячейке вычисляется сумма значений выделенных ячеек. Выделив ячейку G12, можно увидеть в строке формул, что сумма вычислена с помощью формулы =СУММ(G2:G11) . В формуле использована функция
СУММ(…), вычисляющая сумму значений ячеек и диапазонов, указанных в качестве аргументов; б) ввести ту же формулу в ячейку G12 с клавиатуры.
7. С помощью модуля «Поиск решения» определить ми- нимум значения целевой ячейки G12 путем изменения значений ячеек D1:D2, содержащих параметры регрессии.
Настройки модуля для рассматриваемого случая показаны на рис. 3.20. Они достаточно просты – следует отыскать значе- ния параметров a, b, сводящие к минимуму значение суммы квадратов отклонений, вычисленной в ячейке G12. Никаких ог- раничений при этом не накладывается.
После выполнения поиска в ячейках D1 и D2 окажутся найденные компьютером значения параметров a=0,717и
b=2,211, соответствующие минимуму функционала (3.8).
Рис. 3.20. Настройка модуля «Поиск решения» для определения параметров регрессии

3. Электронные таблицы Excel
137 8. Добавить график регрессии на диаграмму рассеяния.
Для этого щелкнуть по диаграмме правой кнопкой мыши и в контекстном меню выбрать пункт «Выбрать данные». Появляет- ся окно определения исходных данных диаграммы (рис. 3.7).
В разделе окна «Элементы легенды (ряды)» щелкнуть клавишу
[Добавить], после чего возникает окно диалога «Изменение ряда»
(рис. 3.21).
В окнах ввода «Имя ряда», «Значения Х» и «Значения Y» указываются соответственно имя ряда, которое используется для обозначения графика в легенде диаграммы, диапазон ячеек, содержащих аргументы (A2:A13), и диапазон ячеек, содержа- щих значения регрессии (E2:E13). Для этого можно использо- вать механизм быстрого создания ссылок. Имя ряда тоже можно создать ссылкой на ячейку Е1, содержащую соответствующий заголовок.
Рис. 3.21. Добавление ряда на диаграмму
После щелчка по клавише [ОК] на диаграмме рассеяния появляется новый набор точек-маркеров, которые отображают экспоненциальную кривую. Чтобы заменить отдельные точки на сплошную линию, надо щелкнуть правой кнопкой мыши по лю- бой из этих точек и в контекстном меню выбрать пункт «Фор- мат ряда данных». На вкладке «Цвет линии» поставить пере- ключатель в положение «Сплошная линия», а на вкладке «Па- раметры маркера» поставить переключатель в позицию «Нет».