Основы компьютерной грамотности. Базовый учебный курс

Подождите немного. Документ загружается.

Если функция определена, то следует подобрать её параметры так, что-

бы она располагалась как можно ближе к точкам статистических данных.

Один из методов достижения этой цели – метод наименьших квадратов,

предложенный немецким математиком К. Гауссом.

Суть этого метода заключается в следующем: график искомой функции

должен быть построен так, чтобы сумма квадратов отклонений координат

зависимой переменной (обычно y-координат) всех статистических точек от

y-координат графика выбранной функции была бы минимальной.

С помощью MS Excel этот метод можно реализовать, если добавить в

диаграмму линию тренда, которая является графиком предполагаемой рег-

рессионной модели. С помощью MS Excel методом наименьших квадратов

можно проверить, тренд какой функции ближе подходит к статистическим

точкам. Проверим это на нашем примере.

Добавить тренд можно следующим образом. Выделите построенную то-

чечную диаграмму, появится меню Диаграмма, выберите в нем команду

Добавить линию тренда (рис. 3.111).

В диалоговом окне Линия тренда (рис. 3.112) выбираем последовательно

сначала Линейную функцию, затем Степенную, далее Полиномиальную со сте-

пенью 2 (то есть квадратичную). Каждый раз на вкладке Параметры следу-

ет устанавливать флажки Показывать уравнения на диаграмме и Поместить на

диаграмму величину достоверности аппроксимации R^2. Результаты представ-

лены на рис. 3.113-3.115. После каждого шага следует удалять предыдущую

линию тренда (правой кнопкой мыши щелкните на линии тренда и в кон-

текстном меню выберите Очистить).

На графиках имеется коэффициент детерминации, обозначаемый R2. Он оп-

ределяет корректность подбора регрессионной модели. Коэффициент детер-

минации меняется от 0 до 1; если он равен 1, то график функции точно прохо-

Модуль 3. Работа с электронными таблицами

280

Рис. 3.111. Добавление линии тренда

modul_3_2.qxd 1/11/07 1:53 PM Page 280

дит через все точки статистической

выборки, чем ближе коэффициент к 1,

тем удачнее выбрана функция.

В нашем примере наибольший ко-

эффициент детерминации имеет мес-

то при использовании квадратичной

функции, наименьший – при степен-

ном тренде. Но следует отметить, что в

примере коэффициент детерминации

везде невысок, следовательно, и

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

лена неточно. Впрочем, определен-

ный в п. 3.8.2.1 коэффициент корре-

ляции между успеваемостью и нали-

чием компьютеров был также невы-

сок, что говорит о слабой зависимости

этих величин.

3.8. Статистическая обработка результатов наблюдений и измерений

281

Рис. 3.112. Типы линий трендов

Рис.3.113. Линейный тренд

Рис. 3.114. Степенной тренд

modul_3_2.qxd 1/11/07 1:54 PM Page 281

3.8.2.3. Экстраполяция зависимостей

Одной из наиболее часто используемых возможностей MS Excel являет-

ся экстраполяция ряда данных. Это бывает необходимо для оценки сущест-

вующей тенденции и составления на ее основе прогноза.

Чаще всего используют линейную экстраполяцию данных на основе ме-

тода наименьших квадратов. По найденной зависимости можно сделать

предположение об ожидаемых будущих значениях.

Насколько точным может быть такое предположение? Линейная зависи-

мость является разумным предположением, если факторы, влияющие на из-

менение данных за прошедший период, остаются неизменными. Разумеет-

ся, чем дальше пытаться продолжать прямую линию тренда, тем вероятнее

появление новых влияющих факторов или изменение существовавших ра-

нее зависимостей. Таким образом, подобные методы целесообразно приме-

нять для краткосрочных прогнозов.

Рассмотрим прогнозирование средства-

ми MS Excel на следующем примере. Име-

ются данные о количестве абитуриентов за

ряд лет, необходимо спрогнозировать ди-

намику на ближайшие четыре года. Дан-

ные представлены на рис. 3.116.

Выполнить простейшую экстраполя-

цию данных можно следующим образом:

выделить мышью ряд данных, а затем уста-

новить указатель мыши на маркер заполне-

ния (крестик в правом нижнем углу выделе-

ния) и, удерживая нажатой левую кнопку

мыши, протащить ее вниз (в нашем случае

на четыре ячейки вниз). Таким образом

можно экстраполировать (рис. 3.117) не

только данные о количестве абитуриентов

(столбец В), но и список годов (столбец А).

Модуль 3. Работа с электронными таблицами

282

Рис. 3.115. Квадратичный тренд

Рис. 3.116. Исходные данные

modul_3_2.qxd 1/11/07 1:54 PM Page 282

Можно продолжить существую-

щий ряд данных, оставив имеющие-

ся значения этого ряда без измене-

ний, но иногда нужно заменить и

исходные значения, для того чтобы

получить арифметическую или гео-

метрическую прогрессию во всех

ячейках диапазона. По этим данным

удобно строить линию тренда.

Вычислим новые значения в столбце С, озаглавленном Тенденция. Для

этого сначала скопируем значения ячеек диапазона В3:В16 в С3:С16. Выде-

лим диапазон С3:С20, затем в меню Правка → Заполнить → Прогрессия в диа-

логовом окне Прогрессия (рис. 3.118) поставим флажок Автоматическое опре-

деление шага, выберем тип прогрессии Арифметическая и щелкнем по кнопке

ОК (см. рис. 3.118). Результат прогнозирования представлен на рис. 3.119.

Проиллюстрируем задачу диаграммой с исходными данными, спрогно-

зированными значениями и линией линейного тренда. Для этого выделим

диапазон В3:В20, построим по нему точечную диаграмму и добавим линей-

ный тренд (рис. 3.120).

3.8. Статистическая обработка результатов наблюдений и измерений

283

Рис. 3.117. Экстраполяция данных

на четыре года

Рис. 3.118. Задание прогрессии

Рис. 3.119. Результат прогнозирования

с помощью арифметической прогрессии

modul_3_2.qxd 1/11/07 2:01 PM Page 283

Всех этих действий будет достаточно, если данные за прошедший пери-

од – фактические и неизменные. Если же возможны изменения, то прогноз

желательно осуществлять с помощью функций Тенденция для линейной эк-

страполяции или Рост для экспоненциальной экстраполяции, а также ис-

пользуя технику формул массива. Покажем, как это можно сделать, на при-

мере данных предыдущих задач.

Сначала зададим функцию Тенденция для существующих данных. Для

этого активизируем ячейку Е4, выберем в категории Статистические функ-

цию Тенденция, в диапазоне Известные значения зададим В3:В16 (рис. 3.121).

Затем выделим диапазон Е3:Е16, нажмем клавишу F2, чтобы разрешить

редактирование ячеек, далее, удерживая клавишу Ctrl, нажмем одновре-

менно клавиши Shift и Enter. Одновременное нажатие трех последних кла-

виш означает, что будет осуществляться работа с массивом данных.

В столбце Е появятся данные, совпадающие с данными столбца С. Так и

должно быть, разница заключается в том, что при изменении исходных

данных (столбец В) в столбце С изменений не произойдет, а в столбце Е

произойдет пересчет по формулам.

Модуль 3. Работа с электронными таблицами

284

Рис. 3.120. Диаграмма и график линейного тренда

Рис. 3.121. Задание значений аргументов функции Тенденция

modul_3_2.qxd 1/11/07 2:02 PM Page 284

Полученные значения могут быть использованы в качестве аргумента

массива Известные_значения_Y (см. рис. 3.113). Для получения значений по

х произведем следующие действия.

В столбце D зададим целые числа от 1 до 16 – это интервалы времени. Те-

перь осуществим прогнозирование с использованием функции Тенденция.

Выделим диапазон Е17:Е20 и вставим функцию Тенденция (рис. 3.122).

Нажав кнопку ОК, по-

лучим прогнозное значе-

ние только в ячейке Е17,

далее следует нажать кла-

виши F2, затем

Ctrl+Shift+Enter. Таким

образом, мы получим

прогноз. Окончательный

вид рабочего листа пред-

ставлен на рис. 3.123.

3.8. Статистическая обработка результатов наблюдений и измерений

285

Рис. 3.122. Прогнозирование с помощью функции Тенденция

Рис. 3.123. Результат прогнозирования

modul_3_2.qxd 1/11/07 2:02 PM Page 285

3.8.3. Вопросы и задания

1. Какой показатель определяет степень зависимости между двумя фа-

кторами? В каких пределах он изменяется?

2. Что такое регрессионная модель?

3. Какие функции обычно используют, подбирая регрессионную мо-

дель?

4. Для чего нужен метод наименьших квадратов и в чем он заключается?

5. Что такое тренд?

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

7. В чем заключается преимущество прогноза с помощью функций

Тенденция и Рост?

Модуль 3. Работа с электронными таблицами

286

modul_3_2.qxd 1/11/07 2:02 PM Page 286

287

Заключение

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

практические навыки работы с электронными таблицами.

Попробовав однажды решать производственные задачи с помощью

электронных таблиц, вы будете пользоваться ими постоянно. Освоив эту

удобную и мощную технологию, вы захотите узнать более сложные и совер-

шенные приемы работы с табличным процессором.

modul_3_2.qxd 1/11/07 2:03 PM Page 287

Модуль 3. Работа с электронными таблицами

288

Пример теста

1. Диапазон ячеек электронной таблицы – это

1) множество клеток, образующих область произвольной формы

2) множество заполненных клеток электронной таблицы

3) множество пустых клеток электронной таблицы

4) множество клеток, образующих область прямоугольной формы

5) множество клеток, образующих область квадратной формы

2. Cколько ячеек в диапазоне С2:Е4?

1) 8.

2) 2.

3) 9.

4) 6.

5) 12.

3. При перемещении или копировании в электронной таблице абсолютные

адреса ячеек

1) преобразуются в зависимости от длины формулы

2) изменяются

3) преобразуются вне зависимости от нового положения формулы

4) преобразуются в зависимости от нового положения формулы

5) не изменяются

4. В электронной таблице в ячейке А1 записано число 5, в В1 – формула

=А1*2, в С1 – формула =А1+В1. Значение С1 равно

1) 15

2) 10

3) 20

4) 25

5) 5

modul_3_2.qxd 1/11/07 2:03 PM Page 288

5. Дан фрагмент электронной таблицы в режиме отображения формул:

Чему равно числовое значение ячеек В2 и В3, если в них скопировано

содержимое ячейки В1?

1) 21 и 21.

2) 1 и 1.

3) 21 и 22.

4) 22 и 21.

5) 20 и 21.

6. Ячейка электронной таблицы называется активной, если

1) она видна на экране

2) в ней находится информация

3) она пуста

4) она содержит формулу

5) в ней находится курсор

7. Дан фрагмент электронной таблицы в режиме отображения формул

Какие формулы находятся в ячейках В2 и В3, если в них скопировано

содержимое ячейки В1?

1) =В1+1 и В2+1.

2) =А2+1 и В2+1.

3) =А2+1 и А3+1.

4) =А1+1 и А2+1.

5) =А2+1 и В3+1.

8. В ячейке В1 находится формула ЕСЛИ(А1>22;«НОЧЬ»;«ДЕНЬ»). Что бу-

дет в ячейке В1, если А1=13?

1) НОЧЬ.

2) 22.

3) ИСТИНА.

4) ЛОЖЬ.

5) ДЕНЬ.

Пример теста

289

modul_3_2.qxd 1/11/07 2:04 PM Page 289