Расчет стоимости недвижимости. Подбор формул со многими неизвестными

Подбор формул со многими неизвестными

Использование линии тренда графиков Excel – наиболее наглядный и информативный способ восстановления зависимости и исследования связи между двумя переменными. Для зависимостей со многими неизвестными подбор формул выполняют с помощью специальных функций из группы Статистические - ЛИНЕЙН и ЛГРФПРИБЛ. Кроме того, функции ТЕНДЕНЦИЯ и РОСТ позволяют вычислить значения аппроксимирующей функции в диапазоне наблюдения. Функции ЛИНЕЙН и ТЕНДЕНЦИЯ применяют для восстановления линейных зависимостей вида y=b+a₁x₁+a₂x₂+…+a_nx_n, а функции ЛГРФПРИБЛ и РОСТ - для нелинейных (показательных) зависимостей вида y=ba₁^X1a₂^X2…a_n^Xn.

Степень близости подбираемой функции оценивается коэффициентом детерминации R². Если нет других теоретических соображений, то выбирают функцию с коэффициентом R², стремящимся к 1.

Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают массив с т.н. регрессионной статистикой, в котором содержатся вычисленные значения параметров (b,a₁,a₂,…a_n), коэффициент детерминации R² и другие данные, характеризующие аппроксимирующую функцию. Формат функций ЛИНЕЙН, ЛГРФПРИБЛ и их применение поясним на примере.

Агентство недвижимости оценивает однокомнатные квартиры по трем переменным: х1 – общая площадь, х2 – площадь кухни, х3 – этаж квартиры, предполагая, что между каждой переменной х1, х2, х3 и зависимой переменной y (стоимость) существует линейная зависимость: y=b+a₁x₁+a₂x₂+a₃x₃. Подобрать формулу для вычисления стоимости однокомнатных квартир и вычислить стоимость квартиры с данными: х1=42кв.м, х2=11кв.м, х3=5эт. Собранные рекламные данные занесены в приведенную ниже таблицу.

Последовательность действий для решения задачи следующая:

1. Заведите приведенную таблицу в Excel, в ячейки A1:D14.

2. Выделите диапазон ячеек B17:E21 (рис. 2.2) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.

Вызовите мастер функций, выберите статистическую функцию ЛИНЕЙН и заполните параметры функции как на рис. 2.1. Параметр Изв_знач_y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1, поскольку мы хотим получить дополнительную статистику.