—>Информационные технологии —>

Так как задача отыскания функциональной зависимости очень важна, в Excel введен набор функций, которые позволяют решать эту задачу. Эти функции основаны на методе наименьших квадратов. В качестве результата выдаются не только коэффициенты аппроксимирующей функции, но и статистические характеристики полученных результатов.

Функция ЛИНЕЙН

Функция ЛИНЕЙН вычисляет коэффициенты m и b прямой линии y = mx + b , которая наилучшим образом аппроксимирует имеющиеся данные, а также дополнительную регрессионную статистику. Функция возвращает массив данных, который описывает полученную прямую. Синтаксис функции:

ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])

Пример 1

Даны x и y: (0, 3), (1, 1), (2, 6), (3, 3), (4, 7). Найти коэффициенты m и b прямой линии y = mx + b , наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

Подготовим таблицу как показано ниже. Ячейки E2:F6 не заполняйте, они будут заполнены автоматически.

1. В A2:A6 введены значения x, блоку присвоено имя х.
2. В В2:В6 введены значения y, блоку присвоено имя y.
3. В E2:F6 введена табличная формула . Для того чтобы ввести табличную формулу, надо выделить блок ячеек E2:F6, ввести формулу и нажать комбинацию клавиш Ctrl Shift Enter . Фигурные скобки вводить вручную не надо.

Пояснение к блоку статистических результатов функции Линейн.

1. В E2 записан коэффициент m, в F2 — коэффициент b.
2. В E3:F3 стандартные отклонения для этих коэффициентов.
3. В E4 записан так называемый коэффициент детерминации R2. Этот коэффициент лежит на отрезке [0; 1]. Считается, что чем ближе этот коэффициент к 1, тем лучше регрессионное уравнение описывает зависимость. Иногда к такой интерпретации надо относиться с осторожностью.
4. В F4 находится стандартная ошибка для оценки у.
5. В E5 записано значение F-статистики, а в F5 — количество степеней свободы.
6. В E6:F6 записана регрессионная сумма квадратов (10) и остаточная сумма квадратов (14).

Функция НАКЛОН

Функция НАКЛОН вычисляет коэффициент m — тангенс угла наклона прямой регрессии. Например: =НАКЛОН(y;x)

Функция ОТРЕЗОК

Функция ОТРЕЗОК вычисляет коэффициент b — отрезок, отсекаемый прямой на оси ординат. Например: =ОТРЕЗОК(y;x)

Функция ПРЕДСКАЗ

Вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y-значение, соответствующее заданному x-значению. x- и y-значения — известны; новое значение предсказывается с использованием линейной регрессии. Этой функцией можно воспользоваться для прогнозирования.

x — точка данных, для которой предсказывается значение.

Функция ТЕНДЕНЦИЯ

Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы «известные_y» и «известные_x». Возвращает значения y, соответствующие этой прямой для заданного массива «новые_x».

ТЕНДЕНЦИЯ ( известные_y ;[известные_x];[новые_x];[константа])

Новые_x — новые значения x, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения y.

Не забывайте, что функция ТЕНДЕНЦИЯ может возвращать массив результатов, поэтому для неё надо указывать блок ячеек и вводить как табличную формулу.

Пример 2.

Постройте таблицу по образцу. В примечаниях показаны имена ячеек или формулы. В A5:A9 известныеX, в B5:B9 известныеY. Блоку A5:A12 присвоено имя Х. Рассчитайте Предсказ, Тенденцию и прямую mx+b.

Постройте диаграмму по образцу. На диаграмме видно, что прямая пересекает ось ординат в точке 2 (b=2), а наклон прямой равен 45° (m=1). Прямые Предсказ, Тенденция и mx+b слились в одну линию.

Даны x и y: (0, 3), (1, 1), (2, 6), (3, 3), (4, 7). Найти коэффициенты m и b прямой линии y = mx + b , наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

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

Простыми словами, чтобы, например, получить партицию по столбцам ABC, можно взять партиции для AC и B (или любой другой набор непересекающихся подмножеств) и пересечь их между собой. Операция пересечения двух партиций выделяет кластеры наибольшей длины, общие для обеих партиций.

x	f( x )
0	0
1	0,8415
2	0,9093
3	0,1411
4	−0,7568
5	−0,9589
6	−0,2794

d. Записи типа (1) и (3) являются самыми общими обовначениями функций, так как под ними можно понимать какие угодно функции, а потому, имея в руках только эти обозначения, мы ничего не сможем узнать о свойствах этих функций.

Подбор параметра в Excel

Сегодня среди основных областей применения зависимостей выделяют очистку данных. Она подразумевает разработку процессов выявления «грязных данных» с последующим их исправлением. Яркими представителями «грязных данных» являются дубликаты, ошибки в данных или опечатки, пропущенные значения, устаревшие данные, лишние пробелы и тому подобное.

В данной статье мы разобрали такие понятия, как функциональная зависимость, приближенная функциональная зависимость, рассмотрели, где они применяются, а также какие алгоритмы поиска ФЗ существуют. Также мы подробно разобрали базовые, но важные понятия, активно используемые в современных алгоритмах по поиску ФЗ.

Тривиальная функциональная зависимость:

• Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
• Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
• Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
• Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
• Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.

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