Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
A3:B3. Суть метода состоит в том, чтобы найти такие значения независи- мых переменных, которые обеспечили бы равенство фактических свободных членов и вычисленных по формулам. Для этого окно диалога Поиск решения необходимо заполнить сле- дующим образом: 211 В нем заполнены лишь поля Изменяя ячейки – $A$3:$B$3 и Ограничения – $C$7:$C$8 = $D$7:$D$8. Поле редактирования Установить целевую ячейку – пустое, а состояние переключателя Равной – значения не имеет. В результа- те поиска будет найдено следующее решение: 2 , 1 2 1 = = x x 9.6. Регрессия В экономике, науке, технике и других областях человеческой деятельно- сти довольно часто возникает задача определения функциональной зависимо- сти между исходными данными, полученными либо в результате пассивного наблюдения, либо в результате проведения активного эксперимента. Такие аналитические зависимости называются уравнениями регрессии, и которые в общем случае имеют вид: ) ,... , , ( 3 2 1 n x x x x f y = Если функция (зависимая переменная) зависит от одной независимой переменной, то регрессия называется парной и имеет следующий вид: ) (x f y = , если же от многих – множественной – ) ,... , , ( 3 2 1 n x x x x f y = Далее будет рассматриваться только парная регрессия. Используемые при этом приемы и методы могут быть перенесены и на множественную рег- рессию. Если зависимость между зависимой и независимой переменной ли- нейная, то такая регрессия называется линейной, в противном случае регрессия называется нелинейной. Одним из наиболее распространенных методов построения уравнения регрессии является метод наименьших квадратов. Суть этого метода состоит в том, чтобы определить такие пара- метры уравнения регрессии, которые обеспечивали бы наименьшую сумму квадратов разностей между измеренным (наблюдаемым) значением зависи- мой переменной и ее вычисленным значением, при одних и тех же значениях независимой переменной, т.е.: min )) ( ( 2 1 → − ∑ = i i n i x f y , (9.4) где: i – номер значения независимой переменной x и, соответствующей ей, зависимой переменной y , n – общее количество измерений (наблюдений). Из (9.4) видно, что метод наименьших квадратов представляет собой за- дачу безусловной минимизации, которую можно решить, например, с помо- щью надстройки Поиск решения. 212 Графическая интерпретация метода наименьших квадратов приведена на рисунке далее. На ней точками обозначены экспериментальные (наблюдаемые) данные. Тогда линию уравнения регрессии (сплошная прямая) необходимо построить так, чтобы минимизировать сумму квадратов отклонений (пунктирная линия) ме- жду ней и экспериментальными значениями. Из графика видно, что линейная регрессия хотя и правильно отражает направление роста функции, но в тоже время является достаточно грубым приближением. В этом случае необходимо воспользоваться более сложной аппроксимирующей функцией. В качестве таких функций довольно часто ис- пользуются полиномы различной степени вида: n n x a x a x a x a a x f y + + + + + = = ) ( 3 3 2 2 1 0 В Microsoft Excel имеются различные способы построения уравнений регрессии. Некоторые из них рассмотрим на следующем примере. Пусть, в результате проведения эксперимента получены 10 пар значений независимой переменной x и зависимой переменной y , которые представле- ны в следующей таблице: x 1 2 3 4 5 6 7 8 9 10 y 0 4 5 4 3 2 3 5 8 9 9.7. Использование надстройки Поиск решения Применение надстройки Поиск решения для аппроксимации экспери- ментальных данных полиномами различной степени начнем с самого простого – поиска параметров 0 a и 1 a уравнения линейной регрессии: x a a x f 1 0 ) ( + = 213 Для этого необходимо: 1. Заполнить исходную таблицу следующим образом: а) Зарезервировать ячейки для параметров уравнения регрессии, например, B2:C2. б) Занести исходные данные, например, в диапазон A10:B19. в) В ячейку, например, C10, занести уравнение линейной регрессии =$B$2+$C$2*$A10, и скопировать его в диапазон ячеек C11:C19. г) В ячейку, например, C20, занести функцию =СУММКВРАЗН($B$10:$B$19;C$10:C$19), которая возвращает сумму квадратов разностей между наблюдаемыми значениями зависимой пе- ременной y и вычисленными по уравнению регрессии, при одних и тех же значениях независимой переменной x 2. Активизировать окно диалога Поиск решения, в котором: а) В поле редактирования Установить целевую ячейку указать адрес ячейки, которая содержит функцию цели, – $C$20. б) Переключатель Равной установить в положение минимальному значению. в) В поле редактирования Изменяя ячейки занести ссылки на ячейки, со- держащие параметры уравнения регрессии, – $B$2:$C$2. г) Поскольку метод наименьших квадратов представляет задачу миними- зации без ограничений, поле Ограничения не заполняется. д) Нажать кнопку Выполнить. В результате поиска решения в изменяемых ячейках B2:C2 будут получе- ны значения параметров, соответствующие следующему уравнению регрессии: 9x 0.63636351 9 0.79999997 ) ( + = x f При этом общая погрешность аппроксимации (ячейка C20) составит 30.69090909. Аналогичным образом можно определить параметры уравнений регрес- сии для полиномов второй, третьей и т.д. степеней. При этом необходимо: 1. Зарезервировать больше ячеек под параметры уравнений регрессии, на- пример, B3:I8. 2. В ячейки с уравнениями регрессии (со 2-й по 7-ю степени), например, D10:I10, записать следующие формулы: D10=$B$3+$C$3*$A10+$D$3*$A10^2 , E10=$B$4+$C$4*$A10+$D$4*$A10^2+$E$4*$A10^3 , F10=$B$5+$C$5*$A10+$D$5*$A10^2+$E$5*$A10^3+$F$5*$A10^4 , G10=$B$6+$C$6*$A10+$D$6*$A10^2+$E$6*$A10^3++$F$6*$A10^4+ +$G$6*$A10^5 , 214 H10=$B$7+$C$7*$A10+$D$7*$A10^2+$E$7*$A10^3++$F$7*$A10^4+ +$G$7*$A10^5+$H$7*$A10^6 , I10=$B$8+$C$8*$A10+$D$8*$A10^2+$E$8*$A10^3++$F$8*$A10^4+ +$G$8*$A10^5+$H$8*$A10^6+$I$8*$A10^7 . 3. В ячейки с функциями целей, например, D20:I20, записать такие формулы: D20=СУММКВРАЗН($B$10:$B$19,D$10:D$19) , E20=СУММКВРАЗН($B$10:$B$19,E$10:E$19) , F20=СУММКВРАЗН($B$10:$B$19,F$10:F$19) , G20=СУММКВРАЗН($B$10:$B$19,G$10:G$19) , H20=СУММКВРАЗН($B$10:$B$19,H$10:H$19) , I20=СУММКВРАЗН($B$10:$B$19,I$10:I$19) . Тогда, сформированная и заполненная, в соответствии с изложенным выше, таблица будет выглядеть следующим образом. В ней строки 1 и 9 выделены под названия искомых параметров поли- номов различной степени и переменные, соответственно. Под параметры урав- нений регрессии зарезервирован блок ячеек B2:I8. Значения независимой пе- ременной x занесены в диапазон ячеек A10:A19, а зависимой y – в B10:B19. В диапазоне ячеек C10:I19 записаны уравнения регрессии (полиномы с 1-й по 7-ю степени) для различных значений независимой переменной x , а в C20:I20 – функции цели СУММКВРАЗН() – суммы квадратов разности между на- блюдаемым значением зависимой переменной y и вычисленным, согласно урав- нениям регрессии, при одних и тех же значениях независимой переменной x В диапазоне ячеек C23:I25 сохранены модели поиска решения для полиномов раз- личной степени, чтобы, затем, каждой из них легко можно было воспользоваться. 215 Анализ содержимого ячеек функций цели (диапазон C20:I20) показыва- ет следующую закономерность – с повышение порядка уравнения полиноми- альной регрессии погрешность аппроксимации все время уменьшается. График, приведенный ниже, также показывает – линия уравнения регрессии более высокого порядка проходит более близко к исходным данным. На нем точками изображены данные, полученные в результате наблюдения (или эксперимента). Уравнение линейной регрессии изображено пунктирной линией, а полиномом 4-й степени – сплошной. При поиске параметров уравнения регрессии 5-й, и более высоких сте- пеней, необходимо устанавливать им некоторые начальные приближения. Причем, чем ближе от истинных значений они будут заданы, тем точнее и бы- стрее будет выполнен поиск. Например, в качестве начальных значений мож- но указать параметры одного из полиномов более низкой степени. Это обу- словлено нелинейностью функции цели (9.4) и конечной длиной машинного слова компьютера. Некоторого улучшения полученных решений можно также добиться, установив флажок Автоматическое масштабирование в окне диа- лога Параметры поиска решения. Теоретически самая высокая возможная степень аппроксимирующего полинома на единицу меньше числа наблюдаемых точек данных. В рассмат- риваемом примере – это полином степени не более 9-й. На практике, однако, нет необходимости стремиться к полному устранению погрешности, посколь- ку сами исходные данные никогда не являются точными. Наоборот, необхо- димо стремиться, конечно, без потери качества, ограничиться полиномом как можно меньшей степени. Применение описанной выше методики позволяет в качестве уравнения регрессии использовать не только степенные полиномы, но и другие функции. 216 9.8. Использование диаграмм Средства деловой графики Microsoft Excel позволяют найти уравнения регрессии, не прибегая к вычислениям. Линия уравнения регрессии в Microsoft Excel называется линией тренда и показывает тенденцию изменения данных, а также может служить для со- ставления прогнозов. Для построения линии тренда необходимо: 1. На основании экспериментальных данных построить диаграмму. При этом наиболее наглядной является Точечная диаграмма. 2. На построенной диаграмме выделить необходимый ряд данных. 3. Активизировать окно диалога Линия тренда по команде Добавить линию тренда из контекстного, или основного меню Диаграмма. 4. В появившемся окне диалога Линия тренда на вкладке Тип выбрать один из доступных типов линии тренда, и указать, если необходимо, его параметры (например, если полином – то какой степени). 5. На вкладке Параметры окна диалога Линия тренда указать: 217 •••• С помощью группового переключателя Название аппроксимирую- щей (сглаженной) кривой, и соответствующего поля редактирова- ния, – название тренда, которое будет отображаться в легенде. •••• С помощью счетчиков вперед на и назад на группы Прогноз – на сколько единиц продлить линию тренда относительно исходного диапазона изменения независимой переменной. •••• С помощью флажка пересечение кривой с осью Y в точке – точку пересечения линии тренда с осью Y – только если она известна. •••• С помощью флажка показывать уравнение на диаграмме – ото- бражать ли на диаграмме уравнение линии тренда. •••• С помощью флажка поместить на диаграмму величину достовер- ности аппроксимации (R^2) – отображать ли на диаграмме величи- ну достоверности аппроксимации. Чем она ближе к 1, тем точнее уравнение регрессии описывает зависимость между наблюдаемыми величинами. Если же она лежит близко к –1, то это говорит об обрат- ной зависимости между ними. 6. Нажать кнопку OK. При этом в результате аппроксимации исходных данных приведенного выше примера полиномом 4-й степени будет построена следующая линия тренда. 218 Коэффициент 2 R = 0.991 говорит о достаточно точной аппроксимации экспе- риментальных данных указанным полиномом. Для одного ряда данных можно последовательно построить (не убирая при этом предыдущие) различные линии трендов, с различными параметрами. При этом с помощью такой диаграммы достаточно легко произвести «грубый отсев» совсем неподходящих уравнений регрессии. Остальные же, затем, мож- но будет подвергнуть более тщательному анализу. Построенные таким образом линии трендов связываются с соответствующими рядами данных, и поэтому при изменении значения лю- бой точки ряда данных линии трендов автоматически пересчитываются и обновляются на диаграмме. 9.9. Использование надстройки Пакет анализа данных Надстройка Пакет анализа данных предназначена для выполнения подробного статистического анализа данных. Она содержит множество разно- образных инструментов и функций статистической обработки данных. Для их успешного применения необходимы соответствующие сведения из области стати- стики, описание которых выходит за рамки данного издания. Здесь же будет рас- смотрена лишь техника использования одного из его инструментов – Регрессия. Но, прежде чем использовать надстройку Пакет анализа данных, ее, как и любую другую надстройку, необходимо предварительно установить и загрузить. Инструмент Регрессия надстройки Пакет анализа данных позволяет вы- полнить достаточно детальный анализ параметров только линейной регрессии. Его возможности рассмотрим на исходных данных приведенного ранее примера. Для этого необходимо выполнить следующую последовательность действий. 219 1. По команде Сервис → Анализ данных активизировать окно диалога Ана- лиз данных, в котором в списке Инструменты анализа выбрать элемент Регрессия и нажать кнопку OK. 2. В раскрывшемся окне диалога Регрессия с помощью его элементов управ- ления задать параметры выполнения линейного регрессионного анализа, которые имеют следующие назначения: •••• Входной интервал Y – диапазон анализируемых зависимых данных. Он должен состоять из одного столбца. В случае нашего примера – это B10:B19. •••• Входной интервал X – диапазон независимых данных, подлежащих анализу. Microsoft Excel располагает независимые переменные этого диапазона слева направо в порядке возрастания. Максимальное число входных диапазонов равно 16. В нашем случае это A10:A19. •••• Метки – установить флажок, если первая строка или первый столбец входного интервала содержит заголовки, или снять – если заголовки отсутствуют. В последнем случае подходящие названия для данных выходного диапазона будут созданы автоматически. •••• Уровень надежности – установить флажок, чтобы включить в выходной диапазон дополнительный уровень надежности. В соответствующее поле ввести уровень надежности, который будет ис- пользован дополнительно к уровню 95%, применяемому по умолчанию. 220 •••• Константа - ноль – установить флажок, чтобы линия регрессии про- шла через начало координат. •••• Выходной интервал – ссылка на левую верхнюю ячейку выходного диапазона. Он должен умещать, по крайней мере, семь столбцов для итогового диапазона, который будет включать в себя: − результаты дисперсионного анализа, − коэффициенты регрессии, − стандартную погрешность вычисления Y, − среднеквадратичные отклонения, − число наблюдений, − стандартные погрешности для коэффициентов. • Новый рабочий лист – установить переключатель, чтобы открыть новый рабочий лист в книге и вставить результаты анализа, начиная с ячейки A1. Если в этом есть необходимость, введите имя нового листа в поле, расположенном напротив соответствующего положе- ния переключателя. • Новая рабочая книга – установить переключатель, чтобы открыть новую рабочую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге. • Остатки – установить флажок, чтобы включить остатки в выходной диапазон. • Стандартизированные остатки – установить флажок, чтобы вклю- чить стандартизированные остатки в выходной диапазон. • График остатков – установить флажок, чтобы построить диаграмму остатков для каждой независимой переменной. • График подбора – установить флажок, чтобы построить диаграммы наблюдаемых и предсказанных значений для каждой независимой пере- менной. • График нормальной вероятности – установить флажок, чтобы по- строить диаграмму нормальной вероятности. 3. Нажать кнопку OK. В результате на новом рабочем листе будут выведены результаты ли- нейного регрессионного анализа, и построены диаграммы, приведенные далее. 221 222 ЛЕКЦИЯ № 10. ИНТЕГРАЦИЯ ПРИЛОЖЕНИЙ В мультипрограммных операционных системах довольно часто возника- ет необходимость обмена информацией между одновременно работающими приложениями. В операционной системе Microsoft Windows наиболее распространен- ными механизмами такого обмена являются: |