Аппроксимация данных и определение выбросов

⇐ ПредыдущаяСтр 6 из 7Следующая ⇒

Цель занятия: В программе Microsoft Excel научиться выполнять различными способами аппроксимацию данных и определение выбросов.

Решаемые задачи

1) Линейная аппроксимация.

2) Нелинейная аппроксимация.

3) Определения выбросов в рядах данных

Задание 1

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Выполнить линейную аппроксимацию функции с использованием функций НАКЛОН и ОТРЕЗОК и путем добавления линейного тренда на график

Порядок выполнения работы

Создать файл в Excel вида Фамилия_МСС_Пр08. xls.

Задачу делать на листе с названием Данные

1. Перенести данные в файл (x, F(x)) Фамилия_МСС_Пр08. xls

2. Построить точечную диаграмму (линии прямые, маркеров нет, легенда внизу) по данным. Перенести на отдельный лист диаграмм (перенос см. в задании 2 лабораторной работы №1). Лист назвать Д-1.

3. Вычислить параметры линейной зависимости Y=k*X+b.

Функция НАКЛОН вычисляет k, функция ОТРЕЗОК вычисляет b

Выделить ячейки с ответами светлозеленым фоном.

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

5. На листе Д-1 добавить новый ряд и построить точечную диаграмму (цвет линии красный, толщина 5).

6. Для функции F(x) построить линейный тренд автоматически:

а) Выделить мышью график исходных данных на диаграмме

б) Щелкнуть правой клавишей мыши

в) В контекстном меню выбрать команду «Добавить линию тренда» (рис. 8.1)

Рис. 8.1 Выбор команды
«Добавить линию тренда»

г) В диалоговом окне «Формат линии тренда» (рис.8.2)

Рис. 8.2 Диалоговое окно
«Формат линии тренда»

выбрать параметр линии тренда – «Линейная», поставить галочки рядом с «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации»

7. Сравнить коэффициенты линейной зависимости, отображенные на диаграмме и полученные с помощью функций НАКЛОН, ОТРЕЗОК.

Задание 2

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Выполнить линейную аппроксимацию функции с использованием функции ЛИНЕЙН

Порядок выполнения работы

1. Выделить правее столбцов с данными две горизонтальные ячейки

2. Нажать значок «Вставить функцию» (слева от строки формул)

3. В подокне «Выберите функцию» найти функции ЛИНЕЙН

4. Указать параметры функции ЛИНЕЙН (х, у – аналогично функциям НАКЛОН, ОТРЕЗОК) Конст = ИСТИНА, Статистика =ЛОЖЬ

5. Щелкнуть мышкой в строке формул и нажать клавиши Ctrl-Shift-Enter, т.к. работаем с формулой массива, аналогично с функцией ЧАСТОТА в лабораторной работе №1.

6. Выделить ячейки с ответами светлозеленым фоном.

7. Сравнить полученные значения с ранее вычисленными. Результаты работы функции ЛИНЕЙН должны совпадать с результатам функций НАКЛОН, ОТРЕЗОК.

Задание 3

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Выполнить нелинейную аппроксимацию функции различными способами.

Порядок выполнения работы

1. Для диаграммы Д-1 сделать копию и переместит в конец. Переименовать копию в Д-Тренд.

2. Убрать с диаграммы линейный ряд, рассчитанный самостоятельно.

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

а) Выделить мышью график исходных данных на диаграмме

б) Щелкнуть правой клавишей мыши

в) В контекстном меню выбрать команду «Добавить линию тренда»

г) В диалоговом окне выбрать параметр линии тренда – «Полиномиальная», поставить галочку в окне «Показывать уравнение на диаграмме» и в окне «Поместить на диаграмму величину достоверности аппроксимации». Степень полинома установить 2, толщина линии 2, цвет красный.

д) Аналогично проделать для:

- полиномиального тренда (аппроксимации) степени 3 (толщина 2, цвет зеленый)

- полиномиального тренда степени 4 (толщина 2, цвет темно-фиолетовый).

- логарифмического тренда (толщина 2, цвет желтый)

- полиномиального тренда (толщина 2, цвет коричневый)

Задание 4

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Определить выбросы в рядах данных.

Порядок выполнения работы

1. Определения выбросов в рядах данных с помощью стандартного отклонения

а) Для диаграммы Д-1 создать копию и переместить в конец. Переименовать в Д-Выбр.

Убрать с диаграммы линейный ряд, рассчитанный самостоятельно

б) Сделать описательную статистику как в МСС_Пр01 и разместить ее на отдельном листе ОпСтат

в) На листе ОпСтат сделать заготовки для четырех горизонтальных линий, которые нанести на диаграмму Д-Выбр:

Среднее-Сигма (среднее минус стандартное отклонение) и Среднее+Сигма (среднее плюс стандартное отклонение) - красным цветом, толщина линий 2

Среднее-3Сигма (среднее минус три стандартных отклонения) и Среднее+3Сигма (среднее плюс три стандартных отклонения) - фиолетовым цветом, толщина линий 2

2. Определения выбросов в рядах данных с помощью с помощью межквартильного размаха (на том же листе ОпСтат)

а) Рассчитать 1-й и 3-й квартили (Q1 и Q3), используя функцию Еxcel - КВАРТИЛЬ

б) Вычесть первый квартиль из третьего — получится мера распределения данных, называемая межквартильным размахом (МР)

[МР устойчив к экстремальным значениям распределения, в отличие от стандартной ошибки, которая более чувствительна к выбросам]

в) Рассчитать нижнюю и верхнюю внутренние границы, отстоящие на 1,5МР от квартилей (Q1 и Q3):

Gн(нижняя внутренняя граница )= Q1 -1,5МР

Gв (верхняя внутренняя граница )= Q3+1,5МР

г) Нанести на диаграмму Д-Выбр эти границы как линии зеленого цвета, толщина линий 2

д) Рассчитать нижнюю и верхнюю внешние границы, отстоящие на 3МР от квартилей (Q₁ и Q₃).

[Значение за пределами внутренних границ – экстремально. Нормальное распределение дает одну точку на 100 вне внутренних границ, и одну на 500 000 – вне внешних.]

е) Нанести на диаграмму Д-Выбр эти границы как линии черного цвета, толщина линий 2

Контрольные вопросы по лабораторной работе №8

1. Аппроксимация

2. Для чего используют аппроксимацию?

3. Какие методы аппроксимации использовались в данной работе?

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

5. Какие в Excel можно строить тренды

6. Межквартильный диапазон

7. Для чего строят тренд?

8. Что такое 0, 1, 2, 3, 4 квартиль

Лабораторная работа №9

Интерполяция и экстраполяция

Цель занятия: В программе Microsoft Excel (далее - Excel) выполнить интерполяцию и экстраполяцию функции.

Решаемые задачи

1) Линейная экстраполяция

2) Линейная интерполяция

3) Построения нелинейных экстраполирующих функций.

Задание 1

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Построить линейную экстраполяцию функции с использованием функций Excel

Порядок выполнения работы

1. Перенести данные в файл (x, F(x)) Фамилия_МСС_Пр09. xls

Лист назвать Данные

3. Найти, используя функцию КВАРТИЛЬ (или КВАРТИЛЬ.ВКЛ) 0, 1, 2, 3, 4 квартили (Q0, Q1, Q2, Q3, Q4).

4. Выполнить линейную экстраполяцию данных с использованием функций НАКЛОН, ОТРЕЗОК

а) На новом листе Аппр с помощью функций НАКЛОН, ОТРЕЗОК вычислить параметры линейной зависимости Y=k*X+b (функция НАКЛОН вычисляет k, функция ОТРЕЗОК вычисляет b). Вычисления провести для трех наборов данных: Q0-Q1, Q0- Q2, Q0- Q2, Q0- Q3)

б) На листе Данные в новых столбцах построить три набора данных на интервале Q0-Q4: исходный набор и экстраполированные значения по линейной зависимости Y=k*X+b

1 набор = исходные данные Q0- Q1 и экстраполированные данные Q1 - Q4.

2 набор = исходные данные Q0- Q2 и экстраполированные данные Q2 - Q4

3 набор = исходные данные Q0- Q3 и экстраполированные данные Q3 - Q4

в) На листе Д-1 добавить новые ряды к точечной диаграмме:

1 набор - толщина линии 2, цвет красный,

2 набор - толщина линии 2, цвет зеленый,

3 набор - толщина линии 2, цвет фиолетовый

г) Для экстраполированных участков найти среднее квадратическое отклонение по формуле

где N – число экстраполированных значений, сумма по экстраполированным значениям, Y – разность точного и экстраполированного значения.

ΣY² найти с помощью функции Excel СУММКВРАЗН.

Среднее квадратическое отклонение показывает насколько экстраполированные (прогнозные значения) отличаются от реальных или как разные типы экстраполяции отличаются друг от друга.

5. Выполнить линейную экстраполяцию данных с использованием функции ТЕНДЕНЦИЯ

а) На листе Данные в новых столбцах построить три набора данных на интервале Q0-Q4: исходный набор и экстраполированные значения по линейной зависимости Y=k*X+b с использованием функции ТЕНДЕНЦИЯ (параметр Константа = ИСТИНА)

1 набор = исходные данные Q0- Q1 и экстраполированные данные Q1 - Q4.

2 набор = исходные данные Q0- Q2 и экстраполированные данные Q2 - Q4

3 набор = исходные данные Q0- Q3 и экстраполированные данные Q3 - Q4

б) Для экстраполированных (по формуле ТЕНДЕНЦИЯ) участков найти среднее квадратическое отклонение. Формула см. выше.

Задание 2

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Построить линейную интерполяцию функции с использованием возможностей Excel

Порядок выполнения работы

1. Перенести данные в файл (x, F(x)) Фамилия_МСС_Пр09. xls.

Лист назвать Данные

3. Создать новые наборы разреженных исходных данных: оставить значения для Q0 и Q4, а между ними оставить:

-- каждую вторую точку (первый набор данных)

-- каждую третью точку (второй набор данных)

4. На новом листе Аппр2 с помощью функций НАКЛОН, ОТРЕЗОК вычислить параметры линейной зависимости Y=k*X+b. Функция НАКЛОН вычисляет k, функция ОТРЕЗОК вычисляет b (вычисления провести для двух указанных наборов данных)

5. На листе Данные2 в новом столбце Аппрокс2 построить 2 набора данных на интервале Q0-Q4: исходный набор и интерполированные значения по линейной зависимости Y=k*X+b

6. На листе Д-2 добавить новые ряды к точечной диаграмме: 1 набор - толщина линии 2, цвет красный, 2 набор - толщина линии 2, цвет синий.

7. На листе Данные2 в новых столбцах построить 2 набора данных на интервале Q0- Q4: исходный набор и интепролированные значения с использованием функции ТЕНДЕНЦИЯ (параметр Константа = ИСТИНА)

8. Найти среднее квадратическое отклонение (от исходных данных) интерполяции с использование функций НАКЛОН, ОТРЕЗОК и ТЕНДЕНЦИЯ. Формула см. выше.

Задание 3

Исходные данные: вычислить как показано ниже в «Порядок выполнения работы»

Требуется:

Построить нелинейную экстраполяцию с использованием возможностей Excel

Порядок выполнения работы

1. На новом листе Данные3 выполнить следующие приготовления и вычисления.

В первом столбце с именем Х проставить числа от 1 до 20. Во 2 и 3 столбцах с именами X^2 и X^3 вычислить значения X^2 и X^3. В 4 и 5 столбцах с именами Y(X, X^2) и Y(X, X^2, X^3) (квадратическая и кубическая функции) вычислить значения функций Y=X^2+X и Y= X^3+X^2+X.

2. Вычисление нелинейной экстраполирующей функции с помощью функции ТЕНДЕНЦИЯ

а) Два следующих столбца назвать ТЕНДЕНЦИЯ2 и ТЕНДЕНЦИЯ3.

В столбце ТЕНДЕНЦИЯ2 вычислить, начиная со строки, где стоит Х=11 значения функции ТЕНДЕНЦИЯ со следующими исходными данными:

«Известные значения Y»– первые 10 значений их столбца Y(X, X^2)

«Известные значения X»– первые 10 значений из столбца X^2

«Новые значения X» – одиннадцатое значение из столбца X^2

«Константа X» - ИСТИНА

Аналогично для столбца ТЕНДЕНЦИЯ3, только там значения брать из столбцов Y(X, X^2, X^3) и X^3.

б) Назвать следующие два столбца ОтнПогреш2 и ОтнПогреш2. В них вычислить относительную погрешность экстраполяции в процентах как

для вычисленных значений в столбцах ТЕНДЕНЦИЯ2 и ТЕНДЕНЦИЯ3

Записать на том же листе Данные3 максимальные по модулю значения относительной погрешности в таблицу: первую строку с данными назвать Функция1 а два столбца назвать Y2 и Y3

в) В столбцах именами Y(X, X^2) и Y(X, X^2, X^3) сменить функции на Y=2*X^2+5*X+12 и Y= 9*X^3+3*X^2+10*X+20. Посмотреть на максимальные по модулю значения относительной погрешности, занести данные в таблицу в строку с именем Функция2.

г) В столбцах именами Y(X, X^2) и Y(X, X^2, X^3) сменить функции на

Y=2*X^2-2*X+10 и Y= 2.3*X^3-1.6*X^2+7*X+12. Посмотреть на максимальные по модулю значения относительной погрешности, занести данные в таблицу в строку с именем Функция3.

д) В столбцах именами Y(X, X^2) и Y(X, X^2, X^3) сменить функции на

Y=2*X^2-12*X+10 и Y= 2.3*X^3-16*X^2+7*X+12. Посмотреть на максимальные по модулю значения относительной погрешности, занести данные в таблицу в строку с именем Функция4.

е) По данным таблицы записать выводы о том какие функции лучше всего экстраполирует функция ТЕНДЕНЦИЯ. Записать вывод в Excel-файле и выделить вывод светло-зеленой заливкой ячейки.

Контрольные вопросы по лабораторной работе №9

1. Аппроксимация

2. Интерполяция

3. Экстраполяция

4. Что делает функция Excel НАКЛОН. Графическое представление результата

5. Что делает функция Excel ОТРЕЗОК. Формула результата

6. Регрессия

7. Линейная экстраполяция

8. Линейная интерполяция

9. Транспонирование

10. Что делает функция Excel ТЕНДЕНЦИЯ

11. Метод наименьших квадратов (МНК)

Лабораторная работа №10

Фильтрация данных

Цель занятия: В программе Microsoft Excel научиться выполнять различными способами фильтрацию данных и определение выбросов.

Решаемые задачи

1) Локальное сглаживание методом скользящего среднего

2) Локальное сглаживание с неравными весами коэффициентов

3) Экспоненциальное сглаживание

4) Сглаживание методом Envelope для выделения положительных и отрицательных аномалий

Задание 1

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Выполнить локальное сглаживание методом скользящего среднего.

Порядок выполнения работы

Создать файл в Excel вида Фамилия_МСС_Пр10. xls.

Задачу делать на листе с названием Данные

1. Перенести данные в файл (x, F(x)) Фамилия_МСС_Пр10. xls

2. Построить точечную диаграмму (линии прямые, маркеров нет, легенда внизу, толщина линии 2, цвет синий) по данным. Перенести на отдельный лист диаграмм (перенос см. в задании 2 лабораторной работы №1). Лист назвать Д-1.

3. Вычислить для данных по добыче скользящее среднее для 5 точечного (L=2), 7 (L=3), 9 (L=4) точечного фильтра. Число точек в фильтре N=2*L+1.

Формулы для вычисления сглаженного значения:

где c_l – весовые множители, удовлетворяющие условию

Длина интервала суммирования 2L+1 и этот интервал «скользит» по исходным данным.

В данной работе использовать наиболее часто применяемую формулу для вычисления весовых множителей:

Если L=1, то c_-1 = c₀ = c₁ = 1/3, то формула скользящего среднего примет вид:

Вычисление значений N точеного фильтра начинать c L+1 исходных данных и закачивать на NN-L значении, где NN – число исходных данных.

4. Построить точечную диаграмму: исходные данные и сглаженные кривые. Лист назвать D- Sr Оформление диаграммы как и в предыдущих работах (точечная диаграмма, прямые линии без точек, размещение на отдельном листе диаграмм, легенда внизу, подписи осей)

Задание 2

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Выполнить локальное сглаживание с неравными весами коэффициентов.

Порядок выполнения работы

Задачу делать на листе с названием Данные

1. Рассчитать сглаженный значения по профилю для следующих фильтров:

а) трехточечный фильтр Y_F[i] = 0.25*Y[i-1]+ 0.5* Y[i]+ 0.25* Y[i+1]

б) пятиточеный фильтр Y_F[i] = (Y[i-2]+2*Y[i-1]+4*Y[i]+2*Y[i+1]+ Y[i+2])/10

2. Построить графики исходного и сглаженных значений.

Оформление как и в предыдущих работах (точечная диаграмма, прямые линии без точек, по оси Х – год, размещение на отдельном листе диаграмм, легенда внизу).

Лист диаграмм назвать D-Сгл.

Графики назвать Фильтр 3-точечн., Фильтр 5-точечн.

Цвета кривых: исходный – синий, Фильтр3 – красный, Фильтр5 – темно-зеленый).

3. Оценить сглаживающие свойства фильтров: рассчитать для всех случаев величины S, характеризующие отклонение сглаженного поля Y_F от исходного Y.

Вычисление ведется по точкам, для которых рассчитан фильтр.

Чем больше величина S, тем больше сглаживание

Задание 3

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Выполнить экспоненциальное сглаживание.

Порядок выполнения работы

1. Методика экспоненциального сглаживания

В отличие от метода скользящего среднего в расчете экспоненциального среднего участвуют все налюдения исходного ряда данных, но с различными весовыми множителями. Формула метода экспоненциального сглаживания:

где a – коэффициент экспоненциального сглаживания, задавае6мый как 0 < a < 1.

a – это вес старого значения в данной точке.

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

2. Вычислить экспоненциальное сглаживание для a=0.01, a=0.5, a=0.99.

3. Построить точечную диаграмму: исходные данные и три сглаженных кривых. Лист назвать D-Exp. Оформление диаграммы как и в предыдущих работах (точечная диаграмма, прямые линии без маркеров, размещение на отдельном листе диаграмм, легенда внизу, подписи осей)

Задание 4

Исходные данные: функция Y_новое2(k=0.05), рассчитанная в задании 1 лабораторной работы №2.

Требуется:

Сглаживание методом Envelope для выделения положительных и отрицательных аномалий.

Порядок выполнения работы

1. Метод расчета

При сглаживании получаются две линии: верхняя (UB) и нижняя (LB):

UB = (1 + D/100)*MMA( N ),

LB = (1 - D/100) *MMA( N ),

где MMA — локальное сглаживание методом скользящего среднего (см. Задание 1);

N — количество точек для усреднения;

D — величина отклонения (в процентах) скользящей средней.

Взять N=5 и N =7, величины D взять и 10, 30, 50

2. Построить 2 точечные диаграммы, на каждой исходные данные и 3 пары (UB и LB) сглаженных кривых (с разными D). Листы назвать D- EN5 (для N=5) и D- EN7 (для N=7). Оформление диаграммы как и в предыдущих работах (точечная диаграмма, прямые линии без маркеров, размещение на отдельном листе диаграмм, легенда внизу, подписи осей)

Контрольные вопросы по лабораторной работе №10

1. Фильтрация данных

2. Скользящее среднее

3. Формула трехточечного фильтра

4. Формула пятиточечного фильтра

5. Чему равны суммы весов для трехточечного фильтра и пятиточечного фильтров, используемые в работе?

6. Формула для оценки сглаживающих свойств фильтра, что показывает результат этой формулы?

7. Экспоненциальное сглаживание

8. К чему приводит увеличение коэффициента экспоненциального сглаживания α

Лабораторная работа №11

Дата добавления: 2019-07-15; просмотров: 417; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 1 2 3 4 567 Следующая ⇒

Мы поможем в написании ваших работ!