Графики функций — необходимы для графического анализа собранных

Графики функций - необходимы для графического анализа собранных количественных данных с целью формулирования выводов о закономерностях изменения и развития исследуемого процесса

I. Этапы построения графика 1. Создаём файл - рабочую книгу. На разных листах можно строить разные модели. 2. Формируем таблицу значений (X, Y) 3. Выбираем шаг, вводим в столбик X первые значения, выделяем, маркером заполнения (черный крест), протаскиваем вниз 4. В столбик Y вводим формулу функции (начиная со знака равно) 5. Строим график ВставкаДиаграммы 2

Пример 1 Построение функции Y=X^2 диапазоне [0, 4], с шагом 0. 5 в 1. В столбик A вводим значения X: 0; 0, 5; 1. Выделяем и удерживая левую кнопку мыши протягиваем до значения 4 3

2. В ячейку B 3 вводим формулу для расчета функции =A 3^2 (в ячейке A 3 находится значение X) Нажимаем Enter и получаем результат расчета Встаем на ячейку В 3, находим черный крест и копируем введенную формулу вниз, удерживая ЛКМ 4

3. Белым крестом выделяем все данные столбцов А и B, выбираем ВставкаДиаграммыТочечная (При необходимости выделить столбцы, не расположенные подряд, удерживаем кнопку Ctrl) 5

На данном графике показана зависимость Y oт X 6

4. Изменим диапазон горизонтальной шкалы графика. Щелкаем правой кнопкой мыши по цифре на оси, выбираем Формат оси 7

Задаем параметры (от -3 до 10) 8

9

Построение нескольких графиков Пример 2 Построить графики функций y=3*ln(x) и y=0, 3*exp(x) в диапазоне [0; 2, 1], с шагом 0. 3 1. В ячейку B 1 вводим 3, в ячейку С 1 – коэффициент 0, 3 2. В столбик A вводим значения X: 0; 0, 3; 0, 6, выделяем и протягиваем далее 3. В ячейку В 3 – формулу =$B$1*LN(A 3), протягиваем 4. В ячейку С 3 – формулу =$С$1*EXP(A 3), протягиваем вниз 10

Замечания: 1. Знак $ - абсолютный адрес ячейки, который не меняется при копировании формулы 2. Слово #ЧИСЛО! - невозможность расчета функции для X=0 11

Выделяем столбцы c данными (с A 2 по C 10) ВставкаДиаграммыТочечная 12

Выделяем столбцы только со значениями функции (с В 2 по C 10) ВставкаДиаграммыГрафик Горизонтальная ось – это номер наблюдения 13

II. Построение линии тренда Тренд – зависимость, определяющая общее направление изменения изучаемого явления Примеры - Линейный - Экспоненциальный - Логарифмический - Полулогарифмический - Полиноминальный - Гиперболический 14

Этапы построения тренда 1. Формируем таблицу с исходными данными 2. Выделяем и строим поле корреляции (ВставкаДиаграммыТочечная) 3. Щелкаем правой кнопкой мыши по одной из точек поля корреляции. В контекстном меню выбираем Добавить линию тренда. 4. Отмечаем тип тренда (линейный, экспоненциальный, …) 5. Отмечаем пункты √ Показывать уравнение на диаграмме √ Поместить на диаграмму величину … (R 2) 15

Пример 3 Собраны данные для анализа стоимости однокомнатных квартир на определенную дату Построим разные типы трендов и сравним 16

1. Выделяем данные столбцов X и Y, строим поле корреляции (Вставка Диаграммы Точечная) 17

Правой кнопкой мыши щелкаем по точке поля корреляции Добавить линию тренда 18

19 Выбираем тип тренда

Данные с линейным трендом и уравнением тренда 20

Данные с логарифмическим трендом и уравнением тренда 21

Данные с полиноминальным трендом и уравнением тренда 22

Прогноз на основе полиноминального тренда, как наиболее точного из построенных вариантов 1. Вводим значения коэффициентов, полученные на графике, в ячейки B 1, B 2, B 3 Это позволит автоматизировать расчеты при изменении коэффициентов 2. Вводим формулу тренда в ячейку С 6 =$B$1+$B$2*A 6+$B$3*A 6^2 3. Протягиваем вниз Получили в столбце C прогнозные значения У для каждого значения Х 23

24 Прогнозирование, полиноминальный тренд

Оценка качества построенного прогноза Средняя ошибка аппроксимации показывает, на сколько процентов в среднем отличаются реальные значения уt от расчетных : если , то качество уравнения хорошее. 25

Этапы расчета средней ошибки аппроксимации 1. Вводим вспомогательный столбец D, в котором рассчитываем модуль с помощью функции =ABS((B 6 -C 6)/B 6) 2. Протягиваем формулу вниз 3. В ячейке D 17 находим сумму столбца с модулями, два раза нажав на 4. В ячейке D 18 полученную сумму умножаем на 100 и делим на количество наблюдений =D 17*100/11 Это и будет величиной ошибки. 26

Расчет средней ошибки аппроксимации 27

Средняя ошибка аппроксимации равна 1, 09%, качество прогноза на основе полиноминального тренда хорошее 28

Примеры анализируемых зависимостей 1. Стоимость квартиры в зависимости от площади, района, этажа, наличия ремонта, … 2. Размер заработной платы в зависимости от возраста, наличия образования, стажа, … 3. Объем продаж в зависимости от затрат на рекламу, торговой наценки, цен на аналогичные товары, наличия гарантии, … 4. Стоимость автомобиля в зависимости от года выпуска, пробега, объема двигателя, … 29

30