Функция РОСТ()
Еще одним способом построить линию экспоненциального тренда является использование функции РОСТ() , английское название GROWTH.
Синтаксис функции следующий:
РОСТ( известные_значения_y; ; ; )
Для работы функции нужно просто ввести ссылки на массив значений переменной Y (аргумент известные_значения_y ) и на массив значений переменной Х (аргумент известные_значения_x ). Функция рассчитает прогнозные значения Y для Х, указанных в аргументе новые_значения_x . Если требуется, чтобы экспоненциальная кривая y=a*EXP(b*x) имела a=1, т.е. проходила бы через точку (0;1), то необязательный аргумент конст должен быть установлен равным ЛОЖЬ (или 0).
Если среди значений y есть отрицательные, то с помощью функции РОСТ() аппроксимирующую кривую построить не удастся.
Безусловно, использование функции РОСТ() часто удобно, т.к. не требуется делать замену переменных и сводить задачу к линейному случаю.
Наконец, покажем как с помощью функции РОСТ() вычислить коэффициенты уравнения y= a *EXP( b *x).
Примечание : В MS EXCEL имеется специальная функция ЛГРФПРИБЛ() , которая позволяет вычислить коэффициенты уравнения y=a*EXP(b*x). Об этой функции см. ниже.
Чтобы вычислить коэффициент a (значение Y в точке Х=0) используйте формулу =РОСТ(C26:C45;B26:B45;0) . В диапазонах C26:C45 и B26:B45 должны находиться массивы значений переменной Y и X соответственно.
Чтобы вычислить коэффициент b используйте формулу:
= LN(РОСТ(C26:C45;B26:B45;МИН(B26:B45))/ РОСТ(C26:C45;B26:B45;МАКС(B26:B45)))/ (МИН(B26:B45)-МАКС(B26:B45))
Оценка точности
При любой аппроксимации особую важность приобретает оценка ее точности. Обозначим через e i разность (отклонение) между функциональными и экспериментальными значениями для точки x i , т
е. e i = y i — f (x i).
Очевидно, что для оценки точности аппроксимации можно использовать сумму отклонений, т. е. при выборе прямой для приближенного представления зависимости X от Y нужно отдавать предпочтение той, у которой наименьшее значение суммы e i во всех рассматриваемых точках. Однако, не все так просто, так как наряду с положительными отклонениями практически будут присутствовать и отрицательные.
Решить вопрос можно, используя модули отклонений или их квадраты. Последний метод получил наиболее широкое распространение. Он используется во многих областях, включая регрессионный анализ (в Excel его реализация осуществляется с помощью двух встроенных функций), и давно доказал свою эффективность.
Элементарная графическая иллюстрация
Это направление используется в математике для того, чтобы ученики могли наглядно увидеть способ применения МНК. Для наглядности можно привести следующий пример: на представленной схеме изображена красная линия, которая является найденной прямой у=0,165х + 2,184. А вот синей линии отвечает формула. Исходные данные могут состоять из многочисленных розовых точек.
Если учесть все наработки специалистов, то можно сделать вывод, что во время моделирования экономических, технических, физических и социальных процессов используются универсальные способы вычисления максимально приближённых значений функций по известным данным в некоторых фиксированных точках.
Необходимость решать уравнения с приближением функций возникает в следующих случаях:
- Работа с численным интегрированием и дифференцированием, а также решение различных дифференциальных уравнений.
- Построение приближённых формул для правильного вычисления показателей исследуемого процесса по соответствующим табличным данным, которые были получены в результате проведённых экспериментов.
- Определение показателей характерных величин математического процесса за пределами изучаемого интервала, что особенно актуально при прогнозировании.
- В случае необходимости вычисления значений функций в имеющихся промежуточных точках рассматриваемого интервала.
Если ученик попробует построить производную функцию для моделируемого процесса, который был задан таблицей, максимально описывающую этот процесс на основании универсального МНК, то в итоге она получит название аппроксимирующей функции. Эта тема особенно актуальна для изучения всех возможностей пакета MS Excel, так как с его помощью можно гораздо быстрее решать сложные математические задачи. На этот случай предусмотрено две интересные опции.
А во-вторых, элементарное добавление выбранных регрессий в диаграмму, которая была построена на основе обычной таблицы данных для исследуемой в определённый период характеристики процесса. Но эта опция становится доступной только тогда, когда если есть построенная диаграмма. Более подробно эта тема описывается в научной литературе.
Аппроксимация в Excel
Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.
Но без хорошо налаженного учета невозможно эффективное функционирование ни страны, ни области, ни предприятия, ни домашнего хозяйства при любой общественно-экономической формации общества! Для составления прогнозов и планов деятельности и развития необходимы исходные данные. Где их брать? Только один достоверный источник – это ваши статистические учетные данные предыдущих периодов времени.
Учитывать результаты своей деятельности, собирать и записывать информацию, обрабатывать и анализировать данные, применять результаты анализа для принятия правильных решений в будущем должен, в моем понимании, каждый здравомыслящий человек. Это есть ничто иное, как накопление и рациональное использование своего жизненного опыта. Если не вести учет важных данных, то вы через определенный период времени их забудете и, начав заниматься этими вопросами вновь, вы опять наделаете те же ошибки, что делали, когда впервые этим занимались.
«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…
«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…
«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…
«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…
Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда. ». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.
В этой статье я на простом примере покажу, как можно обрабатывать собранные статистические данные в Excel для возможности дальнейшего использования при прогнозировании будущих периодов.
Решение МНК
Мы ищем уравнение линейной регрессии, которое выглядит так: y = ax + b
Где:
- y – зависимая переменная
- x – независимая переменная
- a – коэффициент (это также наклон/градиент линии)
- b – коэффициент (это также точка, где линия пересекает ось Y)
Метод 1
Шаги, которые мы будем делать для поиска y = ax + b (сейчас мы их все пройдём на примере):
Шаг 1: Для каждой точки (x, y) вычислить x² и xy.
Шаг 2: Суммировать все x, y, x² и xy, это даст нам Σx, Σy, Σx² и Σxy (если кто забыл, Σ означает «сумма»).
Шаг 3: Рассчитать наклон a по этой формуле:
, где N – количество данных
Шаг 4: Рассчитать значение числа b:
, где N – количество данных
Шаг 5: Подставить найденные числа по местам в уравнение (y = ax + b)
Пример
После некоторых наблюдений, у нас появились данные о размерах и продажах некой торговой сети, у которой 5 магазинов:
| Размер (x) | Продажи (y) |
|---|---|
| 2 | 4 |
| 3 | 6 |
| 5 | 8 |
| 7 | 10 |
| 9 | 12 |
Для наглядности, например, это магазины мороженого, и 2-метровая лавочка продаёт в месяц 4 тонны мороженого, 7 метровая — 10 тонн.
Шаг 1:
Сразу можно записать, что N = 5 (количество данных; т.е. всего у нас данные по пяти магазинам, ведь у нас 5 строк данных).
Для каждой точки (x, y) вычисляем x² и xy. Для этого, к уже существующим столбцам добавим ещё два: x² и xy.
- x² получим путём возведения x (Размер) в квадрат
- xy получим путём умножения одного на второе
| x | y | x² | xy |
|---|---|---|---|
| 2 | 4 | 2² = 4 | 2 × 4 = 8 |
| 3 | 6 | 3² = 9 | 3 × 6 = 18 |
| 5 | 8 | 25 | 40 |
| 7 | 10 | 49 | 70 |
| 9 | 12 | 81 | 108 |
Шаг 2: Суммировать все x, y, x² и xy, это даст нам Σx, Σy, Σx² и Σxy (складываем каждый столбик):
| x | y | x² | xy |
|---|---|---|---|
| 2 | 4 | 2² = 4 | 2 × 4 = 8 |
| 3 | 6 | 3² = 9 | 3 × 6 = 18 |
| 5 | 8 | 25 | 40 |
| 7 | 10 | 49 | 70 |
| 9 | 12 | 81 | 108 |
| Σx = 26 | Σy = 40 | Σx² = 168 | Σxy = 244 |
Шаг 3: Рассчитать a (наклон графика) по этой формуле:
, где N – количество данных
Помним, что N = 5, значит:
Шаг 4: Рассчитать значение числа b:
, где N – количество данных
Помним, что N = 5, значит:
Шаг 5: Подставить найденные числа по местам в уравнение
y = ax + b ⇒ y = 1,0976x + 2,29248
Готово!
Далее можем проверить. Можем составить вот такой график, вместе с данными точками и полученной функцией:
Действительно, полученная функция и точки «ходят рука об руку».
Также мы можем использовать эту функцию, чтобы понять, как будут зависеть продажи фирмы от размера помещения. Например: руководство хочет открыть магазин размером в 11,5 м². Для этого подставляем 11,5 вместо x:
y = 1,0976x + 2,29248 ⇒ y = 1,0976 × 11,5 + 2,29248 = 14,91488
Ответ: этот магазин размером в 11,5 м² будет продавать около 15 тонн мороженого в месяц.
Метод 2
Мы продолжаем искать уравнение линейной регрессии, которое выглядит так: y = ax + b.
Используем тот же пример с сетью магазинов.
| Размер (x) | Продажи (y) |
|---|---|
| 2 | 4 |
| 3 | 6 |
| 5 | 8 |
| 7 | 10 |
| 9 | 12 |
Шаг 1: Опять суммируем все x, y, x² и xy, т.е. находим Σx, Σy, Σx² и Σxy (складываем каждый столбик):
| x | y | x² | xy |
|---|---|---|---|
| 2 | 4 | 2² = 4 | 2 × 4 = 8 |
| 3 | 6 | 3² = 9 | 3 × 6 = 18 |
| 5 | 8 | 25 | 40 |
| 7 | 10 | 49 | 70 |
| 9 | 12 | 81 | 108 |
| Σx = 26 | Σy = 40 | Σx² = 168 | Σxy = 244 |
Шаг 2: Записать вот такую систему уравнений (так мы будем искать параметры a и b):
Шаг 3: Помним, что N = 5. Таким образом, из нашего примера получаем систему:
Лучше конечно её переписать красиво:
Шаг 4: Решить систему.
Находим a = 1,0976; b = 2,29248; и ставим по местам в функцию (y = ax + b). Получается y = 1,0976x + 2,29248
Готово!
Для проверки лучше составить график с данными точками и найденной функцией, как в методе 1.
Узнайте также про Метод Крамера, Стандартное отклонение и Корреляции.
Графическая иллюстрация метода наименьших квадратов (мнк).
На графиках все прекрасно видно. Красная линия – это найденная прямая y = 0.165x+2.184, синяя линия – это , розовые точки – это исходные данные.
Для чего это нужно, к чему все эти аппроксимации?
Я лично использую для решения задач сглаживания данных, задач интерполяции и экстраполяции (в исходном примере могли бы попросить найти занчение наблюдаемой величины y при x=3 или при x=6 по методу МНК). Но подробнее поговорим об этом позже в другом разделе сайта.
К началу страницы
Доказательство.
Чтобы при найденных а и b функция принимала наименьшее значение, необходимо чтобы в этой точке матрица квадратичной формы дифференциала второго порядка для функции была положительно определенной. Покажем это.
Дифференциал второго порядка имеет вид:
То есть
Следовательно, матрица квадратичной формы имеет видпричем значения элементов не зависят от а и b .
Покажем, что матрица положительно определенная. Для этого нужно, чтобы угловые миноры были положительными.
Угловой минор первого порядка . Неравенство строгое, так как точки несовпадающие. В дальнейшем это будем подразумевать.
Угловой минор второго порядка
Докажем, что методом математической индукции.
-
Проверим справедливость неравенства для любого значения n, например для n=2.
Получили верное неравенство для любых несовпадающих значений и .
-
Предполагаем, что неравенство верное для n.
– верное.
-
Докажем, что неравенство верное для n+1.
То есть, нужно доказать, что исходя из предположения что – верное.
Поехали.
Выражение в фигурных скобках положительно по предположению пункта 2), а остальные слагаемые положительны, так как представляют собой квадраты чисел. Этим доказательство завершено.
-
Вывод : найденные значения а и b соответствуют наименьшему значению функции , следовательно, являются искомыми параметрами для метода наименьших квадратов.
Метод наименьших квадратов
В Excel, как известно, существует встроенная функция автосуммы, позволяющая вычислить значения всех значений, расположенных в выделенном диапазоне. Таким образом, ничто не помешает нам рассчитать значение выражения (e 1 2 + e 2 2 + e 3 2 + … e n 2).
В математической записи это имеет вид:
Так как изначально было принято решение об аппроксимировании с помощью прямой, то имеем:
Таким образом, задача нахождения прямой, которая лучше всего описывает конкретную зависимость величин X и Y, сводится к вычислению минимума функции двух переменных:
Для этого требуется приравнять к нулю частные производные по новым переменным a и b, и решить примитивную систему, состоящую из двух уравнений с 2-мя неизвестными вида:
После нехитрых преобразований, включая деление на 2 и манипуляции с суммами, получим:
Решая ее, например, методом Крамера, получаем стационарную точку с некими коэффициентами a * и b * . Это и есть минимум, т. е. для предсказания, какой товарооборот будет у магазина при определенной площади, подойдет прямая y = a * x + b * , представляющая собой регрессионную модель для примера, о котором идет речь. Конечно, она не позволит найти точный результат, но поможет получить представление о том, окупится ли покупка в кредит магазина конкретной площади.