novpes.ru - Как составить семейный бюджет? Таблица семейного бюджета

Закрыть ... [X]

Как в экселе сделать таблицу для графика


На заключительном уроке темы мы познакомимся с наиболее известным приложением ФНП, которое находит самое широкое применение в различных областях науки и практической деятельности. Это может быть физика, химия, биология, экономика, социология, психология и так далее, так далее. Волею судьбы мне часто приходится иметь дело с экономикой, и поэтому сегодня я оформлю вам путёвку в удивительную страну под названием Эконометрика =) …Как это не хотите?! Там очень хорошо – нужно только решиться! …Но вот то, что вы, наверное, определённо хотите – так это научиться решать задачи методом наименьших квадратов. И особо прилежные читатели научатся решать их не только безошибочно, но ещё и ОЧЕНЬ БЫСТРО ;-) Но сначала общая постановка задачи + сопутствующий пример:

Пусть в некоторой предметной области исследуются показатели , которые имеют количественное выражение. При этом есть все основания полагать, что показатель  зависит от показателя . Это полагание может быть как научной гипотезой, так и основываться на элементарном здравом смысле. Оставим, однако, науку в сторонке и исследуем более аппетитные области – а именно, продовольственные магазины. Обозначим через:

 – торговую площадь продовольственного магазина, кв.м.,
 – годовой товарооборот продовольственного магазина, млн. руб.

Совершенно понятно, что чем больше площадь магазина, тем в большинстве случаев будет больше его товарооборот.

Предположим, что после проведения  наблюдений/опытов/подсчётов/танцев с бубном в нашем распоряжении оказываются числовые данные:

С гастрономами, думаю, всё понятно:  – это площадь 1-го магазина,  – его годовой товарооборот,  – площадь 2-го магазина,   – его годовой товарооборот и т.д. Кстати, совсем не обязательно иметь доступ к секретным материалам – довольно точную оценку товарооборота можно получить средствами математической статистики. Впрочем, не отвлекаемся, курс коммерческого шпионажа – он уже платный  =)

Табличные данные также можно записать в виде точек  и изобразить в привычной для нас декартовой системе .

Ответим на важный вопрос: сколько точек нужно для качественного исследования?

Чем больше, тем лучше. Минимально допустимый набор состоит из 5-6 точек. Кроме того, при небольшом количестве данных в выборку нельзя включать «аномальные» результаты. Так, например, небольшой элитный магазин может выручать на порядки больше «своих коллег», искажая тем самым общую закономерность, которую и требуется найти!

Если совсем просто – нам нужно подобрать функцию , график которой проходит как можно ближе к точкам . Такую функцию называют аппроксимирующей (аппроксимация – приближение) или теоретической функцией. Вообще говоря, тут сразу появляется очевидный «претендент» – многочлен высокой степени, график которого проходит через ВСЕ точки. Но этот вариант сложен, а зачастую и просто некорректен (т.к. график  будет всё время «петлять» и плохо отражать главную тенденцию).

Таким образом, разыскиваемая функция должна быть достаточно простА и в то же время отражать зависимость адекватно. Как вы догадываетесь, один из методов нахождения таких функций и называется методом наименьших квадратов. Сначала разберём его суть в общем виде. Пусть некоторая функция  приближает экспериментальные данные :
Метод наименьших квадратов – общая постановка задачи
Как оценить точность данного приближения? Вычислим  и разности (отклонения)  между экспериментальными и функциональными значениями (изучаем чертёж). Первая мысль, которая приходит в голову – это оценить, насколько великА сумма , но проблема состоит в том, что разности могут быть и отрицательны (например, ) и отклонения в результате такого суммирования будут взаимоуничтожаться. Поэтому в качестве оценки точности приближения напрашивается принять сумму модулей отклонений:

 или в свёрнутом виде:  (вдруг кто не знает: – это значок суммы, а  – вспомогательная переменная-«счётчик», которая принимает значения от 1 до ).

Приближая экспериментальные точки различными функциями, мы будет получать разные значения , и очевидно, где эта сумма меньше – та функция и точнее.

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

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

И сейчас мы возвращаемся к другому важному моменту: как отмечалось выше, подбираемая функция должна быть достаточно простА – но ведь и таких функций тоже немало: линейная, гиперболическая, экспоненциальная, логарифмическая, квадратичная и т.д. И, конечно же, тут сразу бы хотелось «сократить поле деятельности». Какой класс функций выбрать для исследования? Примитивный, но эффективный приём:

– Проще всего изобразить точки  на чертеже и проанализировать их расположение. Если они имеют тенденцию располагаться по прямой, то следует искать уравнение прямой  с оптимальными значениями  и .  Иными словами, задача состоит в нахождении ТАКИХ коэффициентов  – чтобы сумма квадратов отклонений   была наименьшей.

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

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

И по существу нам требуется решить стандартную задачу – найти минимум функции двух переменных.

Вспомним про наш пример: предположим, что «магазинные» точки  имеют тенденцию располагаться по прямой линии и есть все основания полагать наличие линейной зависимости  товарооборота от торговой площади. Найдём ТАКИЕ коэффициенты «а» и «бэ», чтобы сумма квадратов отклонений  была наименьшей. Всё как обычно – сначала частные производные 1-го порядка. Согласно правилу линейности дифференцировать можно прямо под значком суммы:

Если хотите использовать данную информацию для реферата или курсовика – буду очень благодарен за поставленную ссылку в списке источников, такие подробные выкладки найдёте мало где:

Составим стандартную систему:

Сокращаем каждое уравнение на «двойку» и, кроме того, «разваливаем» суммы:

Примечание: самостоятельно проанализируйте, почему «а» и «бэ» можно вынести за значок суммы. Кстати, формально это можно проделать и с суммой

Перепишем систему в «прикладном» виде:

после чего начинает прорисовываться алгоритм решения нашей задачи:

Координаты точек  мы знаем? Знаем. Суммы  найти можем? Легко. Составляем простейшую систему двух линейных уравнений с двумя неизвестными («а» и «бэ»). Систему решаем, например, методом Крамера, в результате чего получаем стационарную точку . Проверяя достаточное условие экстремума, можно убедиться, что в данной точке функция  достигает именно минимума. Проверка сопряжена с дополнительными выкладками и поэтому оставим её за кадром (при необходимости недостающий кадр можно посмотреть здесь). Делаем окончательный вывод:

Функция  наилучшим образом (по крайне мере, по сравнению с любой другой линейной функцией) приближает экспериментальные точки . Грубо говоря, её график проходит максимально близко к этим точкам. В традициях эконометрики полученную аппроксимирующую функцию также называют уравнением пАрной линейной регрессии.

Рассматриваемая задача имеет большое практическое значение. В ситуации с нашим примером, уравнение  позволяет прогнозировать, какой товарооборот («игрек») будет у магазина при том или ином значении торговой площади (том или ином значении «икс»). Да, полученный прогноз будет лишь прогнозом, но во многих случаях он окажется достаточно точным.

Я разберу всего лишь одну задачу с «реальными» числами, поскольку никаких трудностей в ней нет – все вычисления на уровне школьной программы 7-8 класса. В 95 процентов случаев вам будет предложено отыскать как раз линейную функцию, но в самом конце статьи я покажу, что ничуть не сложнее отыскать уравнения оптимальной гиперболы, экспоненты и некоторых других функций.

По сути, осталось раздать обещанные плюшки – чтобы вы научились решать такие примеры не только безошибочно, но ещё и быстро. Внимательно изучаем стандарт:

Задача

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

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

Заметьте, что «иксовые» значения – натуральные, и это имеет характерный содержательный смысл, о котором я расскажу чуть позже; но они, разумеется, могут быть и дробными. Кроме того, в зависимости от содержания той или иной задачи как «иксовые», так и «игрековые» значения полностью или частично могут быть отрицательными. Ну а у нас дана «безликая» задача, и мы начинаем её решение:

Коэффициенты  оптимальной функции  найдём как решение системы:

В целях более компактной записи переменную-«счётчик» можно опустить, поскольку и так понятно, что суммирование осуществляется от 1 до .

Расчёт нужных сумм удобнее оформить в табличном виде:

Вычисления можно провести на микрокалькуляторе, но гораздо лучше использовать Эксель – и быстрее, и без ошибок; смотрим короткий видеоролик:

Таким образом, получаем следующую систему:

Тут можно умножить второе уравнение на 3 и из 1-го уравнения почленно вычесть 2-е. Но это везение – на практике системы чаще не подарочны, и в таких случаях спасает метод Крамера:
, значит, система имеет единственное решение.

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

Получены правые части соответствующих уравнений, значит, система решена правильно.

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

В отличие от прямой зависимости товарооборота магазина от его площади, найденная зависимость является обратной (принцип «чем больше – тем меньше»), и этот факт сразу выявляется по отрицательному угловому коэффициенту. Функция  сообщает нам о том, что с увеличение некоего показателя  на 1 единицу значение зависимого показателя  уменьшается в среднем на 0,65 единиц. Как говорится, чем выше цена на гречку, тем меньше её продано.

Для построения графика аппроксимирующей функции найдём два её значения:

и выполним чертёж:
Эмпирические точки и линейный тренд
Построенная прямая называется линией тренда (а именно –  линией линейного тренда, т.е. в общем случае тренд – это не обязательно прямая линия). Всем знакомо выражение «быть в тренде», и, думаю, что этот термин не нуждается в дополнительных комментариях.

Вычислим сумму квадратов отклонений  между эмпирическими  и теоретическими  значениями. Геометрически – это сумма квадратов длин «малиновых» отрезков (два из которых настолько малы, что их даже не видно).

Вычисления сведём в таблицу:

Их можно опять же провести вручную, на всякий случай приведу пример для 1-й точки:

но намного эффективнее поступить уже известным образом:

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

Найдем соответствующую сумму квадратов отклонений – чтобы различать, я обозначу их буквой «эпсилон». Техника точно такая же:

И снова на всякий пожарный вычисления для 1-й точки:

В Экселе пользуемся стандартной функцией EXP (синтаксис можно посмотреть в экселевской Справке).

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

Но тут следует отметить, что «хуже» – это ещё не значит, что плохо. Сейчас построил график этой экспоненциальной функции – и он тоже проходит близко к точкам  – да так, что без аналитического исследования и сказать трудно, какая функция точнее.

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

Имеются следующие данные о розничном товарообороте магазина за первое полугодие:

Используя аналитическое выравнивание по прямой, определите объем товарооборота за июль.

Да без проблем: нумеруем месяцы 1, 2, 3, 4, 5, 6 и используем обычный алгоритм, в результате чего получаем уравнение – единственное, когда речь идёт о времени, то обычно используют букву «тэ» (хотя это не критично). Полученное уравнение показывает, что в первом полугодии  товарооборот увеличивался в среднем на 27,74 д.е. за месяц. Получим прогноз на июль (месяц №7):  д.е.

И подобных задач – тьма тьмущая. Желающие могут воспользоваться дополнительным сервисом, а именно моим экселевским калькулятором (демо-версия), который решает разобранную задачу практически мгновенно! Рабочая версия программы доступна по обмену или за символическую плaтy.

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

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

С формально-технической точки зрения она получается из «линейной» системы  (обозначим её «звёздочкой») заменой «икса» на . Ну а уж суммы-то  рассчитаете, после чего до оптимальных коэффициентов «а» и «бэ» рукой подать.

Если есть все основания полагать, что точки  располагаются по логарифмической кривой , то для розыска оптимальных значений   и  находим минимум функции . Формально в системе () нужно заменить  на :

При вычислениях в Экселе используйте функцию LN. ПризнАюсь, мне не составит особого труда создать калькуляторы для каждого из рассматриваемых случаев, но всё-таки будет лучше, если вы сами «запрограммируете» вычисления. Видеоматериалы урока в помощь.

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

Теперь, сопоставляя полученную функцию с линейной функцией , приходим к выводу, что в системе () нужно  заменить на , а  – на . Для удобства обозначим :

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

Чтобы приблизить экспериментальные точки  оптимальной параболой , следует найти минимум функции трёх переменных . После осуществления стандартных действий получаем следующую «рабочую» систему:

Да, конечно, сумм здесь побольше, но при использовании любимого приложения трудностей вообще никаких. И напоследок расскажу, как с помощью Экселя быстро выполнить проверку и построить нужную линию тренда: создаём точечную диаграмму, выделяем мышью любую из точек  и через правый щелчок выбираем опцию «Добавить линию тренда». Далее выбираем тип диаграммы и на вкладке «Параметры» активируем опцию «Показывать уравнение на диаграмме». ОК

Как всегда статью хочется завершить какой-нибудь красивой фразой, и я уже чуть было не напечатал «Будьте в тренде!». Но вовремя передумал. И не из-за того, что она шаблонна. Не знаю, кому как, а мне что-то совсем не хочется следовать пропагандируемому американскому и в особенности европейскому тренду как в экселе сделать таблицу для графика =) Поэтому я пожелаю каждому из вас придерживаться своей собственной линии!

Автор: Емелин Александр


 Блог Емелина Александра

Высшая математика для заочников и не только >>>

(Переход на главную страницу)


Источник: http://www.mathprofi.ru/metod_naimenshih_kvadratov.html


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



Рекомендуем посмотреть ещё:



Как построить диаграмму в Excel Занимательные уроки excel Высыпания на коже при месячных

Как в экселе сделать таблицу для графика Метод наименьших квадратов безошибочно и быстро!
Как в экселе сделать таблицу для графика Как построить график в Excel Подробная инструкция
Как в экселе сделать таблицу для графика Простая линейная регрессия
Как в экселе сделать таблицу для графика Путь воина » Полезняшки Excel
Как в экселе сделать таблицу для графика Скользящая средняя в Excel
Как в экселе сделать таблицу для графика Бэрри-Кейк - интернет-магазин кондитерского инвентаря
Вакуумные массажные банки Газета ПравДа 38 от by Newspaper PravDa - issuu Дизайн ногтей на ногах - идеальный педикюр на отпуск, фото Как избавиться от прыщиков на лице? форум Как плести фенечки из ниток мулине Крестик

ШОКИРУЮЩИЕ НОВОСТИ