Ирина Эланс

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

Основы работы с табличным процессором 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			¼		¼

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

Между величинами 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:

(1.6)

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

(1.7)

Подобранная прямая называется линией регрессии y на x, a и b называются коэффициентами регрессии.

Чем меньше величина

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

Коэффициент корреляции r и коэффициент регрессии a связаны соотношением:

где Dy, Dx - среднеквадратичное отклонение значений x и y.

Значение коэффициента корреляции удовлетворяет соотношению -1 ≤ r ≤ 1. Чем меньше отличается абсолютная величина r от единицы, тем ближе к линии регрессии располагаются экспериментальные точки. Если коэффициент корреляции равен нулю, то переменные x, y называются некоррелированными. Если r = 0, то это только означает, что между x, y не существует линейной связи, но между ними может существовать зависимость, отличная от линейной.

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

Значение t сравнивается со значением, взятым из таблицы распределения Стьюдента в соответствии с уровнем значимости a и числом степеней свободы n-2. Если t больше табличного, то коэффициент корреляции значимо отличен от нуля.

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

Рассмотрим метод наименьших квадратов на примере прямой линии Y=aX + b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

Задача

Для данных, представленных в таблице, найти в MS Excel коэффициенты a и b прямой линии y = ax + b.

Год	1	2	3	4	5
Выпуск продукции	21	32	12	31	19

1 способ:

Создаем новую рабочую книгу, сохраняем ее под именем Глава 2. В ячейки А1 и В1 впишем текстовые обозначения X и Y. Разместим координаты точек в диапазоне А2:В6. В ячейки А8 и В8 впишем текстовые обозначения a и b. Ячейкам А9 и В9 дадим имена коэффициентов а и b, вписывая эти буквы в поле имени ячейки и нажимая затем клавишу Enter. В ячейках А9 и В9 поместим начальные значения коэффициентов a и b , равные нулю (=0 и клавиша Enter).

В ячейках С2:С6 будем вычислять Y_i =a X_i + b . Для этого в ячейку С2 впишем формулу =a*A2+b и нажмем клавишу Enter. Аналогично в С3 впишем формулу =a*A3+b и нажмем клавишу Enter. Аналогично для ячеек С4, С5, С6. Начиная с ячейки С3 операция вписывания формул можно проделать копированием содержимого ячейки С2 в ячейки С3 – С6 (кнопки Копировать и Вставить на панели инструментов).

В ячейках D2:D6 будем вычислять ошибки приближения Y_i – (a X_i + b). Для этого в D2 поместим формулу =В2-С2 (Enter). В в D3 поместим формулу =В3-С3 (Enter) и т.д. Начиная с ячейки D3 операция вписывания формул можно проделать копированием содержимого ячейки D2 в ячейки D3 – D6 (кнопки Копировать и Вставить на панели инструментов).

В ячейку D8 вписываем текст «Сумма квадратов отклонений». Наконец, в ячейке D9 вычислим сумму квадратов отклонений. Воспользуемся функцией СУММКВ(диапазон_ячеек_D2:D6). Для этого вызовем мастер функций, нажав на кнопку со значком f_x, расположенную на панели инструментов. Появится диалоговое окно мастера функций. В списке, расположенном слева, выделим Математические функции. Затем в списке расположенном справа с помощью линии прокрутки найдем функцию СУММКВ и выделим ее. Ниже этих списков Мы видим синтаксис применения этой функции. У нее может быть несколько аргументов, разделяемых точкой с запятой. У нас таких аргументов 5 (ячейки D2 - D6). Однако, можно воспользоваться одним аргументом, указывая сразу диапазон ячеек D2:D6, содержимое каждой из ячеек должно возводиться в квадрат и суммироваться.

После выделения функции СУММКВ нажимаем кнопку ОК. Откроется следующее окно, в котором в окошке Число 1 и следует указать диапазон ячеек D2:D6. Программа подсчитала результат – 263,5 для начальных данных. Однако диапазон ячеек она указала не совсем точно. Исправляем неточность или вводим в первое окошко символы $D$2:$D$6 сами. Нажимаем кнопку ОК.

Окно закроется и в ячейке D9 появится результат вычисления суммы квадратов отклонений для заданных нами начальных данных a=0 и b=0, (для уравнения линии Y=0), равный 263,5.

Теперь все готово для решения задачи оптимизации. Выделим ячейку D9 и вызовем Решатель (меню Сервис – Поиск решения). В появившемся окне абсолютный адрес $D$9 целевой ячейки уже установлен.

Устанавливаем флажок Равной минимальному значению. Введем в окошко Изменяя ячейки абсолютные адреса $A$9:$B$9 диапазона ячеек, по которым будет минимизироваться значение целевой функции (неизвестные параметры a и b). Это можно сделать с клавиатуры, а также выделяя диапазон ячеек А9:В9 мышью. Ограничений в данной оптимизационной задаче нет.

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

Нажимаем кнопку ОК. Результат вычислений представлен ниже.

Таким образом, оптимальные значения коэффициентов линейной функции по критерию суммы квадратов отклонений равны а=-1,5 и b=28,5 . Следовательно, оптимальной является линия Y=-1,5x+28,5b.

Также оптимальную линию можно найти при помощи другого способа.

2 способ:

Сначала находим неизвестные коэффициенты a и b линейной регрессии y=ax+b по известным формулам:

(2.1)

Составим таблицу с расчетными данными и вычислим коэффициенты a и b:

То есть имеем следующие уравнение: y=-1,5x+28,5.

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

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

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

x_i	y_i
x₀	y₀
x₁	y₁
.	.
.	.
.	.
x_n	y_n

Расстояние между аргументами произвольное.

Нужно найти функцию g(x), приближенно описывающую функцию f(x).

Функция g(x) может выглядеть в виде линейной, квадратичной, степенной и так далее.

Рассмотрим линейную функцию:

В общем виде она выглядит: g=ax+b.

Запишем для данного случая систему:

Рассмотрим квадратичную функцию.

В общем виде она выглядит: g=ax₂+bx+c

Запишем для данного случая систему:

_{- Вычисляем определитель}

_{- Вычисляем определитель
перого порядка}

_{- Вычисляем определитель
второго порядка}

На практике данный способ реализуется следующим образом: Для данных, заданных в таблице установить линейную зависимость: g=ax+b.

В поле ввода вводим n=4.
Строчкой ниже вводим T:=, на панеле "Матрицы" выбираем кнопку "Создать матрицу или вектор", выбираем количество строк=2, а количество столбцов=5.
В каждый квадрат первой строчки матрицы по порядку вводим цифры: 1, 2, 3, 4, 5.
В каждый квадрат второй строчки матрицы по поряду вводим цифры: 26, 32, 12, 31, 19.
В следующей строчке вводим Mx:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате водим i=0, а в верхнем - n, в следующем квадрате вводим T, на панели "Матрицы" выбираем кнопку "Нижний индекс" и в появившемся квадрате вводим 0,i.
Рядом вводим Mx=.
В следующей строчке вводим Mx2:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате вводим i=0, а в верхнем - n, в следующем квадрате вводим скобки и в них вводим T, на панели "Матрицы" выбираем кнопку "Нижний индекс" и в появившемся квадрате вводим 0,i, за скобками на панели "Арифметика" выбираем кнопку "Возведение в степень", в появившемся квадрате вводим 2.
Рядом вводим Mx2=.
В следующей строчке ввести My:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате вводим i=0, а в верхнем - n, в следующем квадрате вводим T, на панели "Матрицы" выбираем кнопку "Нижний индекс" и в появившемся квадрате вводим 1,i.
Рядом вводим My=.
В следующей строчке вводим Mxy:=, на панели ""Матанализ" выбираем кнопку "Суммирование", в нижнем квадрате вводим i=0, а в верхнем - n, в следующем квадрате вводим скобки, в них T_0,i умножить T_1,i.
Рядом вводим Mxy=.
В следующей строчке вводим D:=, на панели "Матрицы" выбираем кнопку "Вычисление определителя", а затем "Создать матрицу или вектор", указываем количество строк=2, количество столбцов=2.
В первой строке, в появившихся квадратах поочередно вводим: Mx и n+1.
В квадратах второй строки вводим Mx2 и Mx. Рядом вводим D=.
В следующей строчке вводим D1:=, на панели "Матрицы" выбираем кнопку "Вычисление определителя", а затем "Создать матрицу или вектор", указываем количество строк=2, количество столбцов=2.
В первой строке, в появившихся квадратах поочередно вводим: My и n+1.
В квадратах второй строки вводим Mxy и Mx. Рядом вводим D1=.
В следующей строчке вводим D2:=, на панели "Матрицы" выбираем кнопку "Вычисление определителя", а затем "Создать матрицу или вектор", указываем количество строк=2, количество столбцов=2.
В первой строке, в появившихся квадратах поочередно вводим: Mx и My.
В квадратах второй строки вводим Mx2 и Mxy. Рядом вводим D2=.
Ниже вводим a=D1, знак деления, D. Рядом вводим a=.
Ниже вводим b=D2, знак деления, D. Рядом вводим b=.

В итоге получаем следующее:

Вывод: при реализации МНК в Mathcad 2000 мы находили определители, далее пользовались соотношением определителей. Показатели записали в виде матрицы.

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

При нахождении a и b в линейном уравнении y=ax+b в табличном процессоре Excel и математическом редакторе Mathcad 2000, a и b совпадают, что означает о правильности решения.

a=-1,5, b=28,5. Из этого следует, что y=-1,5x+28,5

Среда использования	a	b
Excel	-1,5	28,5
Mathcad	-1,5	28,5

Очень часто и Excel и Mathcad используют вместе:

При осуществлении линейной аппроксимации функций методом наименьших квадратов для составления уравнения регрессии сначала необходимо вычислить числа М_х, М_ху, М_у, М_х², это удобно сделать в Microsoft Excel, используя автосуммирование.

По полученным значениям М_х, М_ху, М_у, М_х² составляется система, после решения которой можно будет записать уравнение регрессии. Решить систему можно с помощью MathCAD 2000.

Правильность вычислений можно проверить в MathCAD 2000. Функции interсept(Х,У) и slope(Х,У) вычисляют по заданным векторам экспериментальных данных Х, У значения а₀ и а₁ для записи уравнения линейной регрессии в виде j (х)=а₀+а₁х.

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

Основы работы с табличным процессором Excel 📙 Курсовая → 🆔 163331