Основы работы с табличным процессором Excel

 

Содержание

Введение……………………………………….....…………………………………..3

1. Метод наименьших квадратов.........................................................................…..4

2. Реализация МНК средствами Excel.........................…………………………....12

3. Реализация МНК средствами математического редактора Mathcad 2000…...15

4. Сравнение результатов………………….……………....………….....................21

Заключение………………………………………………………………………….22

Список используемой литературы………………………………………………...23 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Введение

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

     В данной курсовой работе рассматриваются  основы работы с табличным процессором  Excel. Задачами являются: изучение метода наименьших квадратов, реализование МНК средствами математического редактора Mathcad 2000 и c помощью Microsoft Excel, сравнение результатов программных обеспечений.

     Цель  курсовой – рассмотреть метод  наименьших квадратов при помощи Excel, научиться применять его на практике и сравнить c применением мнк в Mathcad 2000.

     Между величинами может существовать точная (функциональная) связь, когда одному значению аргумента соответствует одно определенное значение.

     При выборе аппроксимации следует исходить из конкретной задачи исследования.

       Аппроксимация функции методом наименьших квадратов является простой и легко реализуемой как в Excel, так и в MathCAD. МНК «сглаживает» функцию, выбирая промежуточные значения, что является выгодным решением.

  
 
 

1.Метод  наименьших квадратов

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

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

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

     Большие затруднения представляются при  определении из наблюдений величин, которые не могут быть измерены непосредственно. Если, например, желают определить элементы орбиты планеты или кометы, то светила эти наблюдаются несколько раз, и в результате получают лишь координаты их (склонение и прямое восхождение) в известные времена; самые же элементы выводятся затем решением уравнений, связывающих наблюдаемые координаты с элементами орбиты планеты или кометы. При этом, если бы число уравнений равнялось числу неизвестных, то для каждой неизвестной получилась бы одна определённая величина; если же число уравнений больше числа неизвестных, то, вследствие ошибок наблюдений, результаты решений отдельных групп этих уравнений в различных сочетаниях оказываются не совсем согласными между собой.

     До  начала XIX в. учёные не имели опредёленных правил для решения системы уравнений, в которой число неизвестных менее числа уравнений; до этого времени употреблялись частные приёмы, зависевшие от вида уравнений и от остроумия вычислителей, и потому разные вычислители, исходя из тех же данных наблюдений, приходили к различным выводам. Лежандру (1805—06) и Гауссу (1794—95) принадлежит первое применение к решению указанной системы уравнений теории вероятности, исходя из начал, аналогичных с началом арифметической середины, уже издавна и, так сказать, бессознательно применяемых к выводам результатов в простейшем случае многократных измерений. Как и в случае арифметической середины, вновь изобретённый способ не даёт, конечно, истинных значений искомых, но даёт зато вероятнейшие значения. Этот способ распространён и усовершенствован дальнейшими изысканиями Лапласа, Энке, Бесселя, Ганзена и др. и получил название метода наименьших квадратов, потому что после подстановки в начальные уравнения неизвестных величин, выведенных этим способом, в правых частях уравнений получаются если и не нули, то небольшие величины, сумма квадратов которых оказывается меньшей, чем сумма квадратов подобных же остатков, после подстановки каких бы то ни было других значений неизвестных. Помимо этого, решение уравнений по способу наименьших квадратов даёт возможность выводить вероятные ошибки неизвестных, то есть даёт величины, по которым судят о степени точности выводов.

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

     При аналитическом исследовании взаимосвязи  между двумя величинами x и y производят ряд наблюдений и в результате получается таблица значений: 
 

 

     Эта таблица обычно получается как итог каких-либо экспериментов, в которых  (независимая величина) задается экспериментатором, а получается в результате опыта. Поэтому эти значения будем называть эмпирическими или опытными значениями.

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

                                                   (1.1)

(где  - параметры), значения которой при возможно мало отличались бы от опытных значений .

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

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

Разности  называются отклонениями и представляют собой расстояния по вертикали от точек   до графика эмпирической функции.

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

                                 (1.2)

будет минимальной.

     Поясним геометрический смысл метода наименьших квадратов.

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

     Построение  эмпирической формулы состоит из двух этапов: выяснение общего вида этой формулы и определение ее наилучших параметров.

Если  неизвестен характер зависимости между  данными величинами x и y , то вид эмпирической зависимости является произвольным. Предпочтение отдается простым формулам, обладающим хорошей точностью. Удачный выбор эмпирической формулы в значительной мере зависит от знаний исследователя в предметной области, используя которые он может указать класс функций из теоретических соображений. Большое значение имеет изображение полученных данных в декартовых или в специальных системах координат (полулогарифмической, логарифмической и т.д.). По положению точек можно примерно угадать общий вид зависимости путем установления сходства между построенным графиком и образцами известных кривых.

     Определение наилучших коэффициентов входящих в эмпирическую формулу производят хорошо известными аналитическими методами.

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

                                    (1.3)

Таким образом, нахождение коэффициентов  сводится к решению системы (1.3).

     Эта система упрощается, если эмпирическая формула (1.1) линейна относительно параметров , тогда система (1.3) - будет линейной.

     Конкретный  вид системы (1.3) зависит от того, из какого класса эмпирических формул мы ищем зависимость (1.1). В случае линейной зависимости система (1.3) примет вид:

                    (1.4)

     Эта линейная система может быть решена любым известным методом (методом Гаусса, простых итераций, формулами Крамера).

     В случае квадратичной зависимости  система (1.3) примет вид:

                                  (1.5)

     Применение  МНК в экономике: 

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

     В соответствии с пунктом 2 статьи 241 и  статьи 245 Налогового кодекса Российской Федерации шкала регрессии ставок единого социального налога в 2001 г. применяется налогоплательщиками  при условии, что фактический размер выплат, начисленный в среднем на одного работника и принимавшийся за базу при расчете страховых взносов в Пенсионный фонд Российской Федерации во втором полугодии 2000 г., превышал 25000 рублей.

     При этом у налогоплательщиков с численностью работников свыше 30 человек не учитываются выплаты 10 процентам работников, имеющих наибольшие по размеру выплаты, у налогоплательщиков с численностью работников до 30 человек (включительно) – выплаты 30 процентам работников, имеющих наибольшие по размеру выплаты.

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

     Линейная  функция (линия регрессии):

     Необходимо  определить параметры функции y = ax+b. Составим функцию S:

     Продифференцируем выражение (8.4) по a и b, сформируем систему линейных уравнений, решив которую мы получим следующие значения параметров: