Используя данные из следующей таблицы, с помощью Excel постройте линейно-квадратичную экспоненциальную 2 44

Обновлено: 03.07.2024

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

Важным методом графического анализа является преобразование экспериментальных данных для получения прямой линии. Если существует прямая линейная зависимость между двумя переменными параметрами, данные могут быть приспособлены к уравнению прямой с известной формой \(y = mx + b\) с помощью метода, известного как линейная регрессия. Здесь \(m\) представляет наклон линии, а \(b\) представляет точку пересечения y, как показано на рисунке ниже. Это уравнение выражает математическую взаимосвязь между двумя нанесенными на график переменными и позволяет прогнозировать неизвестные значения параметров.

Уравнение для наиболее подходящей линии:

Компьютерные электронные таблицы — это мощные инструменты для работы с количественными данными и построения графиков. В этом упражнении для этой цели будет использоваться программа для работы с электронными таблицами Microsoft Excel©. В частности, студенты научатся использовать Excel для изучения ряда линейных графических отношений. Обратите внимание, что хотя Excel может подгонять кривые к нелинейным наборам данных, эта форма анализа обычно не так точна, как линейная регрессия.

Процедура

Часть 1. Простой линейный график

Сценарий. Определенный эксперимент предназначен для измерения объема 1 моля газообразного гелия при различных температурах, при сохранении постоянного давления газа на уровне 758 торр:

Запустите программу Microsoft Excel © (версия 2016 г., установлена на всех компьютерах во всех компьютерных центрах кампуса). Нажмите кнопку "Пуск" (в левом нижнем углу экрана), затем нажмите "Программы", а затем Microsoft Excel © .
Введите приведенные выше данные в первые два столбца электронной таблицы.

Зарезервируйте первую строку для меток столбцов.
Значения x необходимо вводить слева от значений y в электронной таблице. Помните, что независимая переменная (которую вы, как экспериментатор, контролируете) идет по оси X, а зависимая переменная (измеренные данные) — по оси Y.

Выделите набор данных (не метки столбцов), которые вы хотите отобразить (рис. 1).

Нажмите Вставить > Рекомендуемые диаграммы, а затем Разброс (рис. 2).

Выберите точечную диаграмму, на которой показаны только точки данных без соединительных линий — параметр с надписью Рассеивание только с маркерами (рис. 3).

Теперь вы должны увидеть точечную диаграмму на экране Excel, на которой можно предварительно просмотреть график (рис. 4).

Если все выглядит хорошо, пришло время добавить заголовки и пометить оси графика (рис. 5).

Сначала нажмите внутри диаграммы.
Перейдите на вкладку Дизайн и нажмите ДобавитьЭлемент диаграммы > Название диаграммы > Над диаграммой
Диаграмма должна иметь осмысленный пояснительный заголовок, начинающийся со слов «Y против X», за которым следует описание вашей системы.
Нажмите Названия осей (выберите Название основной горизонтальной оси и Название основной вертикальной оси), чтобы добавить метки к координатам x и y. оси. Обратите внимание, что важно маркировать оси как с указанием измерения, так и с используемыми единицами.

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

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

Нажмите кнопку Элементы диаграммы рядом с правым верхним углом диаграммы.
Установите флажок Линия тренда.
Нажмите Дополнительные параметры. Отобразится вариант, показанный на рис. 7.
Обратите внимание, что кнопка "Линейный" уже нажата. Теперь установите флажок Отображать уравнение на диаграмме и поле Отображать значение R-квадрата на диаграмме. Затем нажмите кнопку Закрыть.

Уравнение, которое теперь отображается на вашем графике, представляет собой уравнение подобранной линии тренда. Значение R 2 дает меру того, насколько хорошо данные соответствуют уравнению. Чем ближе значение R 2 к 1, тем лучше соответствие. Как правило, значения R 2 0,95 или выше считаются подходящими. Обратите внимание, что программа всегда будет подгонять линию тренда к данным, независимо от того, насколько хороши или ужасны данные. Вы должны оценить качество подбора и пригодность этого типа подбора для вашего набора данных.
Распечатайте полноразмерную копию подготовленного графика и прикрепите ее к отчету. Затем запишите в отчет следующую информацию:

уравнение наиболее подходящей линии тренда к вашим данным
наклон линии тренда
пересечение линии тренда по оси Y
хорошее или плохое соответствие строки данным и почему.

Путем графического отображения пяти измеренных значений устанавливается зависимость между объемом газа и температурой. График содержит визуальное представление отношения (график) и математическое выражение отношения (уравнение). Теперь его можно использовать для определенных прогнозов.

Например, предположим, что образец газообразного гелия массой 1 моль охлаждается до тех пор, пока его объем не станет равным 10,5 л. Вас просят определить температуру газа. Обратите внимание, что значение 10,5 л выходит за пределы графика данных. Как найти температуру, если она не попадает между известными точками? Есть два способа сделать это.

Метод (1). Экстраполируйте линию тренда и оцените, где находится точка на линии.

Нажмите вкладку Макет в верхнем меню, затем Линия тренда >Дополнительные параметры линии тренда.
В разделе Прогноз введите число в поле Назад, так как мы хотим продлить линию тренда в обратном направлении x. Чтобы решить, какое число ввести, посмотрите на свой график, чтобы увидеть, как далеко по оси X вам нужно пройти, чтобы покрыть область, где объем = 10,5 л. После ввода числа нажмите Закрыть, и теперь линия на вашем графике должна быть продлена в обратном направлении.
Теперь используйте свой график, чтобы оценить значение x, представив прямую линию вниз от y = 10,5 L до оси x. Запишите это значение в свой отчет.

Метод (2): подставьте это значение объема в уравнение линии тренда и найдите неизвестную температуру. Сделайте это и запишите ответ в своем отчете. Обратите внимание, что этот метод, как правило, более точен, чем экстраполяция и "на глаз" на графике.

Часть 2. Два набора данных с наложением

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

< tr> < td style="vertical-align:middle;">0,440

Данные A		Данные B
Количество красителя (моль)	Впитывающая способность (безразмерная)	Количество красителя (моль)	Впитывающая способность (безразмерная)
0,100	0,049	0,800	0,620
0.200	0,168	0,850
0,300	0,261	0,900	0,285
0,400	0,360	0,950	0,125
0,500	0,470
0,600	0,590
0,700	0,700
0,750	0,750

< /p>

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

Введите эти новые данные на новую страницу (лист 2) в Excel. Не забудьте пометить столбцы данных A и B. Опять же, не забудьте ввести значения x слева от значений y.
Во-первых, нарисуйте данные A только в виде диаграммы XY Scatter (так же, как вы сделали с данными в Части 1). Сопоставьте линию тренда с этими данными, используя линейную регрессию, и получите уравнение этой линии.
Теперь вам нужно добавить данные Б на этот график.

Активируйте график, щелкнув одну из точек данных на графике.
Нажмите диаграмму правой кнопкой мыши и выберите Выбрать данные. На листе появится поле Выбрать источник данных с исходными данными диаграммы.
Перейдите на вкладку Добавить и введите «Данные B» в качестве имени серии.
Нажмите на маленький значок под Значения серии X, затем выделите значения по оси X данных B.
Нажмите клавишу ввода, затем повторите эту процедуру для Значений серии Y, выделив значения по оси Y для данных B. Для каждого из этих шагов вы должны увидеть экран, аналогичный показанному на Рис. 8. Обратите внимание, что могут быть небольшие различия в зависимости от версии Microsoft Excel ©, установленной на вашем компьютере.

Нажмите "ОК" дважды, чтобы вернуться в главное окно Excel.
На этом этапе вы должны увидеть новые точки данных (обозначенные как Series 2), как показано на рис. 9. Теперь вы можете независимо проанализировать этот набор данных, вставив линию тренда, как и раньше.

Распечатайте полноразмерную копию подготовленного графика и прикрепите ее к отчету. Затем запишите в отчет следующую информацию:

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

Часть 3. Статистический анализ и простые диаграммы рассеяния

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

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

Простой статистический анализ этих наборов данных может включать расчет средней и медианной концентрации, а также стандартного отклонения. Среднее значение (\(\bar \)) – это просто среднее значение, определяемое как сумма (\(\Sigma\)) каждого из измерений (\(x_\)) в наборе данных, деленная на количество измерений. (\(Н\)):

Медиана (\(M\)) – это среднее значение набора данных, упорядоченного в числовом виде, где половина измерений находится выше медианы, а половина – ниже. Среднее местоположение измерений \(N\) можно найти с помощью:

Если \(N\) является нечетным числом, формула дает целое число, представляющее значение, соответствующее среднему местоположению в упорядоченном распределении измерений. Например, в наборе чисел (3 1 5 4 9 9 8) срединное положение равно (7 + 1)/2, или 4-е значение.Применительно к числовому упорядоченному набору (1 3 4 5 8 9 9) число 5 является 4-м значением и, следовательно, медианой — три балла выше 5 и три ниже 5. Обратите внимание, что если бы было только 6 чисел в наборе (1 3 4 5 8 9) срединное положение равно (6 + 1)/2, или 3,5-е значение. В этом случае медиана находится посередине между 3-м и 4-м значениями в упорядоченном распределении, или 4,5.

Стандартное отклонение (\(s\)) – это мера вариации в наборе данных, которая определяется как квадратный корень из суммы квадратов, деленный на количество измерений минус один:

Итак, чтобы найти \(s\), вычтите каждое измерение из среднего, возведите результат в квадрат, добавьте его к результатам каждого квадрата разности, разделите эту сумму на количество измерений минус один, затем извлеките квадратный корень. этого результата. Чем больше это значение, тем больше разброс данных и ниже точность измерений.

Хотя среднее значение, медиану и стандартное отклонение можно рассчитать вручную, для определения этих значений часто удобнее использовать калькулятор или компьютер. Microsoft Excel © особенно хорошо подходит для такого статистического анализа, особенно для больших наборов данных.

Чтобы получить медиану, введите «= median(a1:a10)». Чтобы получить стандартное отклонение, введите "=stdev(a1:a10)".

Отклонение выбросов

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

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

что вы отклонили балл
какой пункт вы отклонили
почему вы его отклонили

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

Построение точечной диаграммы

В отличие от линейных графиков, созданных до сих пор, точечная диаграмма просто показывает изменение измерений одной переменной в заданном наборе данных, т. е. обеспечивает визуальное представление «шума» в данных. Данные нанесены в столбец, и здесь нет зависимости от x-y (рисунок 10). Обратите внимание, что наборы данных с большей степенью разброса будут иметь более высокое стандартное отклонение и состоят из менее точных измерений, чем наборы данных с небольшой степенью разброса.

Определение 1. Мы используем ту же терминологию, что и в определении 3 регрессионного анализа, за исключением того, что степени свободы df_Res и df_Reg изменяются с учетом количества k независимых переменных.

Свойство 1:

Доказательство. Доказательство такое же, как и для свойства 1 регрессионного анализа.

Свойство 2: где R – коэффициент множественной корреляции (определенный в определении 1 множественной корреляции)

Доказательство. Эти свойства являются множественными регрессионными аналогами свойств 2, 3 и 5f регрессионного анализа соответственно, и их доказательства аналогичны.

Наблюдение: из свойства 2 и второго утверждения свойства 3

который представляет собой многомерную версию свойства 1 основных понятий корреляции.

Свойство 3:

Свойство 4: MS_Res — это несмещенная оценка того, где находится дисперсия условий ошибки

Наблюдение: на основании свойства 4 и свойства 4 множественной регрессии с использованием матриц ковариационная матрица B может быть оценена с помощью

В частности, диагональ C = [c_ij] содержит дисперсию b_{j< /sub>}, поэтому стандартная ошибка b_j может быть выражена как

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

Рисунок 1. Создание линии регрессии с использованием матричных методов

Результат показан на рисунке 1. Диапазон E4:G14 содержит матрицу дизайна X, а диапазон I4:I14 содержит Y. Матрица (X T X) -1 в диапазоне E17:G19 может быть рассчитана по формуле массива

В соответствии со свойством 1 множественной регрессии с использованием матриц вектор коэффициентов B (в диапазоне K4:K6) можно рассчитать с помощью формулы массива:

Прогнозируемые значения Y, т. е. Y-hat, затем можно рассчитать с помощью формулы массива

Стандартная ошибка каждого из коэффициентов в B может быть рассчитана следующим образом. Сначала вычислите массив членов ошибки E (диапазон O4:O14), используя формулу массива I4:I14 – M4:M14. Тогда, как и в случае простой регрессии, SS_Res = DEVSQ(O4:O14) = 277,36, df_Res = n – k – 1 = 11 – 2 – 1 = 8 и MS_Res = SS _Res/df_Res = 34,67 (дополнительные сведения см. в разделе Анализ множественной регрессии).

Из наблюдения, следующего за свойством 4, следует, что MS_Res (X T X) -1 равно ковариационная матрица для коэффициентов, поэтому квадратный корень из диагональных членов представляет собой стандартную ошибку коэффициентов. В частности, стандартная ошибка отрезка b₀ (в ячейке K9) выражается формулой =КОРЕНЬ(I17), стандартная ошибка цветового коэффициента b₁ (в ячейке K10) выражается формулой =КОРЕНЬ(J18), а стандартная ошибка коэффициента качества b₂ (в ячейке K11) выражается формулой =КОРЕНЬ(K19).

Функции Excel. Функции НАКЛОН, ОТРЕЗОК, КРОНСИМВ и ПРОГНОЗ не работают для множественной регрессии, но функции ТРЕНД и ЛИНЕЙН поддерживают множественную регрессию, как и инструмент анализа данных регрессии.

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

ЛИНЕЙН работает так же, как и в случае простой линейной регрессии, за исключением того, что вместо использования области 5 × 2 для вывода требуется область 5 × k, где k = количество независимых переменных + 1. Таким образом, для модели с 3 независимыми переменными вам нужно выделить пустую область 5 × 4. Как и раньше, вам нужно вручную добавить соответствующие ярлыки для ясности.

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

Пример 2. Мы вернемся к примеру 1 множественной корреляции, анализируя модель, в которой уровень бедности можно оценить как линейную комбинацию уровня младенческой смертности, доли белого населения и уровня насильственных преступлений (на 100 000 человек).

Нам нужно найти параметры b₀, b₁ и такие, что

На рисунке 2 показано, как использовать TREND и LINEST.

Рисунок 2. ТРЕНД и ЛИНЕЙН для данных в примере 1

Здесь показаны данные для первых 15 из 50 штатов (столбцы от A до E) и прогнозируемый процент бедности, когда младенческая смертность, процент белых в населении и уровень преступности соответствуют указанным (диапазон G6:J8). Выделив диапазон J6:J8, вводим формулу массива =TREND(B4:B53,C4:E53,G6:I8). Как видно из рис. 2, модель предсказывает уровень бедности в 12,87 %, когда младенческая смертность составляет 7,0, белые составляют 80 % населения, а насильственные преступления составляют 400 на 100 000 человек.

На рис. 2 также показаны выходные данные функции ЛИНЕЙН после того, как мы выделим заштрихованный диапазон H13:K17 и введем =ЛИНЕЙН(B4:B53,C4:E53,ИСТИНА,ИСТИНА). Заголовки столбцов b₁, b₂, b_{3< /sub> и intercept относятся только к первым двум строкам (обратите внимание на порядок коэффициентов). Остальные три строки содержат по два значения, помеченных слева и справа.}

Итак, мы видим, что линия регрессии

Бедность = 0,437 + 1,279 ∙ Детская смертность + 0,0363 ∙ Белые + 0,00142 ∙ Преступность

Здесь бедность представляет прогнозируемое значение. Мы также видим, что R Square равен 0,337 (т.е. 33,7% дисперсии уровня бедности объясняется моделью), стандартная ошибка оценки составляет 2,47 и т. д.

Мы также можем использовать инструмент регрессионного анализа данных, чтобы получить результат, показанный на рис. 3.

Рисунок 3. Выходные данные инструмента регрессионного анализа данных

Поскольку p-значение = 0.00026

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

Если мы повторно запустим инструмент регрессионного анализа данных, используя только переменную младенческой смертности, мы получим результаты, показанные на рис. 4.

Рисунок 4. Упрощенная регрессионная модель для примера 1

И снова мы видим, что модель Бедность = 4,27 + 1,23 ∙ Младенческая смертность хорошо подходит для данных (значение p = 1,96E-05

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

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

Пример 3. Определите, подходит ли регрессионная модель для данных из примера 1 метода наименьших квадратов для множественной регрессии с помощью инструмента регрессионного анализа данных.

Результаты анализа показаны на рис. 5.

Рисунок 5. Выходные данные инструмента регрессионного анализа данных

Поскольку значение p = 0,00497

Наблюдение: мы можем рассчитать все записи в регрессионном анализе данных на рисунке 5, используя формулы Excel следующим образом:

Статистика регрессии

Множественное значение R – SQRT(F7) или расчет по определению 1 множественной корреляции
R Square = G14/G16
Скорректированный R-квадрат — расчет на основе R-квадрата с использованием определения 2 множественной корреляции.
Стандартная ошибка = SQRT(H15)
Наблюдения = COUNT(A4:A14)

Дисперсный анализ

SS_T = DEVSQ(C4:C14)
SS_Reg = DEVSQ(M4:M14) из рисунка 3 метода наименьших квадратов для множественной регрессии
SS_{Разрешение} = G16-G14
Все остальные записи можно рассчитать аналогично тому, как мы рассчитали значения ANOVA для примера 1 проверки соответствия линии регрессии (см. рис. 1 на этой веб-странице).

Коэффициенты (в третьей таблице) — показываем, как рассчитать поля перехвата; поля цвета и качества аналогичны

Коэффициент и стандартную ошибку можно рассчитать, как показано на рис. 3 метода наименьших квадратов для множественной регрессии.
Стат. = F19/G19
P-значение = T.DIST.2T(ABS(H19),F15)
Нижние 95 % = F19-T.INV.2T(0,05;F15)*G19
Верхние 95 % = F19+T.INV.2T(0,05;F15)*G19

Остальные результаты регрессионного анализа данных показаны на рис. 6.

Рисунок 6. Остатки/процентили на выходе регрессии

Остаточный результат

Наблюдения с 1 по 11 соответствуют необработанным данным в A4:C14 (из рисунка 5). В частности, записи для наблюдения 1 можно рассчитать следующим образом:

Прогнозируемая цена = F19+A4*F20+B4*F21 (из рис. 5)
Остатки = C4–F26
Стандартные остатки =G26/СТАНДОТКЛОН.S(G26:G36)

Вероятностный вывод

Процентиль: ячейка J26 содержит формулу =100/(2*E36), ячейка J27 содержит формулу =J26+100/E36 (аналогично для ячеек с J28 по J36).
Цена: это просто значения цен в диапазоне C4:C14 (на рис. 5) в отсортированном порядке. Например. формула дополнительного массива =QSORT(C4:C14) может быть помещена в диапазон K26:K36.

Наконец, инструмент анализа данных создает следующие диаграммы рассеяния.

График нормальной вероятности

Здесь показана зависимость процентиля от цены из выходных данных таблицы на рис. 6. Этот график используется для определения того, соответствуют ли данные нормальному распределению. Может быть полезно добавить линию тренда, чтобы увидеть, соответствуют ли данные прямой линии. Это можно сделать, щелкнув график и выбрав «Макет» > «Анализ|Линия тренда» и выбрав «Линейная линия тренда».
Он играет ту же роль, что и график QQ. Фактически, за исключением масштаба, он генерирует тот же график, что и график QQ, созданный дополнительным инструментом анализа данных (переключение осей).

Рисунок 7. График нормальной вероятности

График на рис. 7 показывает, что данные в разумных пределах соответствуют нормальному предположению.

Остаточные графики

Для каждой независимой переменной создается один график. Для примера 2 генерируются два графика: "Цвет по сравнению с остатками" и "Качество по сравнению с остатками".
Эти графики используются для определения того, соответствуют ли данные предположениям о линейности и однородности дисперсии. Для выполнения предположения об однородности дисперсии каждый график должен отображать случайный набор точек. Если появляется определенная форма точек или если вертикальный разброс точек не является постоянным на горизонтальных интервалах одинаковой длины, то это указывает на нарушение предположения об однородности дисперсий.
Для выполнения допущения о линейности остатки должны иметь среднее значение 0, на что указывает приблизительно равный разброс точек выше и ниже оси X.

Рисунок 8. Остаточные графики

График цветового остатка на рис. 8 показывает приемлемое соответствие линейности и однородности предположений о дисперсии. График Quality Residual немного менее точен, но для такого небольшого числа точек выборки он подходит неплохо.

Два графика на рис. 9 показывают явные проблемы. К счастью, они не основаны на данных примера 3.

Рисунок 9. Остаточные графики, показывающие нарушение предположений

Для диаграммы слева на рисунке 9 вертикальный разброс точек в правой части диаграммы больше, чем в левой. Это явный признак того, что дисперсии не являются однородными. Для диаграммы справа точки не кажутся случайными, а также несколько точек находятся ниже оси x (что указывает на нарушение линейности). Диаграмма на рис. 10 идеально соответствует тому, что нам нужно: случайное распределение точек с одинаковым числом точек выше и ниже оси x.

Рисунок 10. Остатки, предположения о линейности и дисперсии

Графики выравнивания по линии

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

Рисунок 11. Графики аппроксимации линий для примера 3

Наблюдение: результаты примера 3 можно представить следующим образом:

Множественный регрессионный анализ использовался для проверки того, влияют ли определенные характеристики на цену бриллиантов. Результаты регрессии показали, что два предиктора объяснили 81,3% дисперсии (R 2 = 0,85, F(2,8) = 22,79, p

Вы можете выразить p-значения другими способами, а также добавить уравнение регрессии: цена = 1,75 + 4,90*цвет + 3,76*качество

В этом учебном пособии вы найдете подробное описание всех параметров линии тренда, доступных в Excel, а также того, когда их использовать. Вы также узнаете, как отобразить уравнение линии тренда на диаграмме и найти наклон линии тренда.

Добавить линию тренда в Excel очень просто. Единственная реальная проблема состоит в том, чтобы выбрать тип линии тренда, который лучше всего соответствует типу данных, которые вы анализируете. В этом руководстве вы найдете подробное описание всех параметров линии тренда, доступных в Excel, и того, когда их использовать. Если вы ищете, как вставить линию тренда в диаграмму Excel, ознакомьтесь с приведенным выше руководством по ссылке.

Типы линий тренда в Excel

При добавлении линии тренда в Excel у вас есть выбор из 6 различных вариантов. Кроме того, Microsoft Excel позволяет отображать уравнение линии тренда и значение R-квадрата на диаграмме:

Уравнение линии тренда — это формула, позволяющая найти линию, которая лучше всего соответствует точкам данных.
Значение R-квадрата измеряет надежность линии тренда: чем ближе R2 к 1, тем лучше линия тренда соответствует данным.

Ниже вы найдете краткое описание каждого типа линии тренда с примерами графиков.

Линейная линия тренда

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

Например, следующая линейная линия тренда показывает устойчивый рост продаж в течение 6 месяцев. А значение R2, равное 0,9855, указывает на довольно хорошее совпадение оценочных значений линии тренда с фактическими данными.

Экспоненциальная линия тренда

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

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

Хорошим примером экспоненциальной кривой является убыль всей популяции диких тигров на Земле.

Логарифмическая линия тренда

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

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

Полиномиальная линия тренда

Полиномиальная криволинейная линия тренда хорошо подходит для больших наборов данных с колеблющимися значениями, которые имеют более одного подъема и падения.

Как правило, многочлен классифицируется по степени наибольшего показателя. Степень полиномиальной линии тренда также можно определить по количеству изгибов на графике. Как правило, линия тренда квадратичного полинома имеет один изгиб (холм или впадина), кубический полином имеет 1 или 2 изгиба, а полином четвертой степени имеет до 3 изгибов.

При добавлении полиномиальной линии тренда в диаграмму Excel вы указываете степень, введя соответствующее число в поле Порядок на панели Формат линии тренда, которое по умолчанию равно 2:
< /p>

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

Линия тренда мощности

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

Линия тренда мощности не может быть добавлена к диаграмме Excel, содержащей нулевые или отрицательные значения.

Например, давайте нарисуем силовую линию тренда, чтобы визуализировать скорость химической реакции. Обратите внимание на значение R-квадрата 0,9918, что означает, что наша линия тренда почти идеально соответствует данным.

Скользящая средняя линия тренда

Когда точки данных на диаграмме имеют много взлетов и падений, линия тренда скользящего среднего может сгладить резкие колебания значений данных, чтобы более четко показать закономерность. Для этого Excel вычисляет скользящее среднее за указанное вами количество периодов (по умолчанию 2) и помещает эти средние значения в виде точек на линии. Чем выше значение Period, тем более плавной будет линия.

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

Уравнения и формулы линий тренда в Excel

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

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

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

Важное примечание! Формулы линии тренда следует использовать только с точечными диаграммами XY, потому что только эта диаграмма отображает оси x и y в виде числовых значений. Дополнительную информацию см. в разделе Почему уравнение линии тренда в Excel может быть неправильным.

Уравнение и формулы линейной линии тренда

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

b – наклон линии тренда.
a – это точка пересечения по оси y, которая представляет собой ожидаемое среднее значение y, когда все переменные x равны 0. На диаграмме, это точка пересечения линии тренда с осью y.

Для линейной регрессии Microsoft Excel предоставляет специальные функции для получения коэффициентов наклона и пересечения.

Наклон линии тренда
b: =SLOPE(y,x)

Y-пересечение
a: =INTERCEPT(y,x)

Предполагая, что диапазон x равен B2:B13, а диапазон y равен C2:C13, в реальной жизни формулы выглядят следующим образом:

Того же результата можно добиться, используя функцию ЛИНЕЙН в качестве формулы массива. Для этого выделите 2 соседние ячейки в одной строке, введите формулу и нажмите Ctrl+Shift+Enter, чтобы завершить ее:

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

Уравнение и формулы экспоненциальной линии тренда

Для экспоненциальной линии тренда в Excel используется следующее уравнение:

Где a и b — вычисляемые коэффициенты, а e — математическая константа e (основание натурального логарифма).

Коэффициенты можно рассчитать с помощью следующих общих формул:

a: =EXP(INDEX(LINEST(LN(y), x), 1, 2))

b: =ИНДЕКС(ЛИНЕЙН(LN(y), x), 1)

Для нашего примера набора данных формулы имеют следующий вид:

a: =EXP(ИНДЕКС(ЛИНЕЙН(LN(C2:C13), B2:B13), 1, 2))

b: =ИНДЕКС(ЛИНЕЙН(LN(C2:C13), B2:B13), 1)

Логарифмическое уравнение и формулы линии тренда

Вот логарифмическое уравнение линии тренда в Excel:

Где a и b — константы, а ln — функция натурального логарифма.

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

a: =ИНДЕКС(ЛИНЕЙН(y, LN(x)), 1)

b: =ИНДЕКС(ЛИНЕЙН(y, LN(x)), 1, 2)

Для нашего примера набора данных мы используем следующие:

a: =ИНДЕКС(ЛИНЕЙН(C2:C13, LN(B2:B13)), 1)

b: =ИНДЕКС(ЛИНЕЙН(C2:C13, LN(B2:B13)), 1, 2)

Полиномиальное уравнение линии тренда и формулы

Для построения полиномиальной линии тренда в Excel используется следующее уравнение:

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

Квадратичная (2-го порядка) полиномиальная линия тренда

Уравнение: y = b₂x 2 + b₁x + a

a: =ИНДЕКС(ЛИНЕЙН(y, x^), 1, 3)

Кубическая (3-го порядка) полиномиальная линия тренда

a: =ИНДЕКС(ЛИНЕЙН(y, x^), 1, 4)

Формулы для полиномиальных линий тренда более высокой степени могут быть построены с использованием того же шаблона.

Для нашего набора данных лучше подходит полиномиальная линия тренда 2-го порядка, поэтому мы используем следующие формулы:

a: =ИНДЕКС(ЛИНЕЙШ(C2:C13, B2:B13^), 1, 3)

Уравнение и формулы силовой линии тренда

Линия тренда мощности в Excel строится на основе этого простого уравнения:

Где a и b — константы, которые можно рассчитать по следующим формулам:

a: =EXP(INDEX(LINEST(LN(y), LN(x),,), 1, 2))

b: =ИНДЕКС(ЛИНЕЙН(LN(y), LN(x),,), 1)

В нашем случае отлично работают следующие формулы:

a: =EXP(ИНДЕКС(ЛИНЕЙН(LN(C2:C13), LN(B2:B13),,), 1, 2))

b: =ИНДЕКС(ЛИНЕЙН(LN(C2:C13), LN(B2:B13),,), 1)

Уравнение линии тренда в Excel неверно: причины и способы устранения

Если вы считаете, что Excel неправильно нарисовал линию тренда или формула линии тренда, отображаемая на диаграмме, неверна, следующие два пункта могут пролить свет на ситуацию.

Уравнение линии тренда Excel верно только в точечных диаграммах

Формулы линий тренда Excel следует использовать только с диаграммами XY (разброс), потому что только в этом типе диаграммы оси Y и X отображаются в виде числовых значений.

В линейных диаграммах, столбчатых и гистограммах числовые значения отображаются только по оси Y. Ось X представлена линейным рядом (1, 2, 3,…) независимо от того, являются ли метки осей числами или текстом. Когда вы создаете линию тренда на этих диаграммах, Excel использует предполагаемые значения x в формуле линии тренда.

Числа округлены в уравнении линии тренда Excel

Чтобы занимать меньше места на диаграмме, Excel отображает очень мало значащих цифр в уравнении линии тренда. Приятный с точки зрения дизайна, он значительно снижает точность формулы, когда вы вручную указываете значения x в уравнении.

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

Вот как вы можете создавать различные типы линий тренда в Excel и получать их уравнения. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

59 комментариев к "Типы линий тренда, уравнения и формулы Excel"

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

Что, если мы хотим найти коэффициенты уравнения линии тренда с точкой пересечения по оси y, равной определенному значению (например, y-int. = 0)?

Моя линия тренда построена правильно, но решение уравнения линии тренда для y дает неверные значения y. Уравнение представляет собой полином 6-й степени. Правильные значения y находятся в диапазоне от -20 до 80. Значения x находятся в диапазоне от 1900 до 2050. Все неправильные значения y находятся в диапазоне -2,38E+15, плюс и минус. Я увеличил десятичное значение до 30, но это не повлияло на результат. Любое предложение о том, как решить эту проблему?

Мастерская работа. Профессора колледжа принимают к сведению. Обучайте понятным языком, чтобы учащиеся могли взять с собой практические знания. Вам не нужно доказывать, насколько вы умны.

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

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

Здравствуйте!
Вот статья, которая может быть вам полезна: Расчет процентного изменения
Также обратите внимание на статью: Как рассчитать процентную дисперсию для отрицательных чисел
Надеюсь, я ответил на ваш вопрос. Если у вас есть другие вопросы, не стесняйтесь спрашивать.

Это очень полезная информация. Однако я не понимаю, как рассчитать значение по линии тренда степенного закона в Excel. Например, линия тренда, на которую я смотрю, имеет значения от 100 до 700 по оси x, от 10 000 до 50 000 по оси y и кривую, которая асимптотична по обеим осям, уменьшаясь справа налево. Приведенное уравнение линии тренда: y = 3E + 08x^(-1,611). Что это значит?! Подстановка любого значения x в уравнение дает значение y = 3E + 0!

Читайте также: