Как линеаризовать график excel
Линейная регрессия моделирует взаимосвязь между зависимыми y и независимыми x статистическими переменными данных. Другими словами, они выделяют тенденцию между двумя столбцами таблицы в электронной таблице. Например, если вы создали таблицу электронных таблиц Excel со столбцом месяца x и записали набор данных для каждого из месяцев в соседнем столбце y, линейная регрессия выделяет тренд между переменными x и y, добавляя линии тренда к графам таблиц. , Вот как вы можете добавить линейную регрессию в графики Excel.
Добавление линии тренда линейной регрессии на график
Сначала откройте пустую электронную таблицу Excel, выберите ячейку D3 и введите «Месяц» в качестве заголовка столбца, который будет переменной x. Затем щелкните ячейку E3 и введите «Значение Y» в качестве заголовка столбца переменной y. Это в основном таблица с записанным рядом значений данных за месяцы январь-май. Поэтому введите месяцы в ячейки от D4 до D8 и значения данных для них в ячейки от E4 до E8, как показано на снимке непосредственно ниже.
Теперь вы можете настроить график рассеяния для этой таблицы. Выделите все ячейки в таблице с помощью курсора. Нажмите вкладку Вставка и выберите рассеивать > Скаттер только с маркерами добавить график в электронную таблицу, как показано ниже. Кроме того, вы можете нажать горячую клавишу Alt + F1, чтобы вставить гистограмму. Затем вы должны щелкнуть правой кнопкой мыши по графику и выбрать Изменить тип диаграммы > X Y (разброс) > Скаттер только с маркерами,
Затем выберите одну из точек данных на диаграмме рассеяния и щелкните правой кнопкой мыши, чтобы открыть контекстное меню, которое включает Добавить трендлайн вариант. Выбрать Добавить трендлайн чтобы открыть окно, показанное на снимке прямо ниже. Это окно имеет пять вкладок, которые включают различные параметры форматирования для линий тренда линейной регрессии.
Сначала нажмите Параметры линии тренда и выберите тип регрессии оттуда. Вы можете выбрать экспоненциальный, линейный, логарифмический, Скользящая средняя, Сила а также многочлен Варианты типа регрессии оттуда. Выбрать линейный и нажмите близко чтобы добавить эту линию тренда на график, как показано ниже.
Линия тренда регрессии лайнера на приведенном выше графике подчеркивает, что между переменными x и y существует общая восходящая взаимосвязь, несмотря на несколько падений на графике. Обратите внимание, что линия тренда линейной регрессии не перекрывает ни одну из точек данных на графике, поэтому она не совпадает с графиком средней линии, соединяющим каждую точку.
Форматирование линии тренда линейной регрессии
Чтобы отформатировать линию тренда, щелкните ее правой кнопкой мыши и выберите Формат Trendline, Это снова откроет окно Format Trendline, из которого вы можете нажать Цвет линии, Выбрать Сплошная линия и нажмите цвет поле, чтобы открыть палитру, из которой вы можете выбрать альтернативный цвет для линии тренда.
Чтобы настроить стиль линии, перейдите на вкладку «Стиль линии». Затем вы можете настроить ширину стрелки и настроить параметры стрелки. нажмите Настройки стрелки кнопки для добавления стрелок в линию.
Добавьте эффект свечения на линию тренда, нажав пылать а также Мягкие края, Это откроет вкладку ниже, из которой вы можете добавить свечение, нажав на Пресеты кнопка. Затем выберите вариант свечения, чтобы выбрать эффект. щелчок цвет выбрать альтернативные цвета для эффекта, и вы можете перетащить Размер а также прозрачность бары для дальнейшей настройки свечения линии тренда.
Прогнозирование значений с помощью линейной регрессии
Каждая линия тренда линейной регрессии имеет свое собственное уравнение и значение квадрата r, которое можно добавить на график. Нажмите на Показать уравнение на графике установите флажок, чтобы добавить уравнение на график. Это уравнение включает в себя значение наклона и перехвата.
Чтобы добавить значение квадрата r на график, щелкните Отображение значения R-квадрата на графике флажок Это добавляет г квадрат на график чуть ниже уравнения, как на снимке ниже. Вы можете перетащить поле уравнения и корреляции, чтобы изменить его положение на графике рассеяния.
Функции линейной регрессии
Excel также включает функции линейной регрессии, с помощью которых вы можете найти значения наклона, точки пересечения и r квадрата для массивов данных y и x. Выберите ячейку электронной таблицы, чтобы добавить одну из этих функций, а затем нажмите Вставить функцию кнопка. Функции линейной регрессии являются статистическими, поэтому выберите статистическая из выпадающего меню категории. Затем вы можете выбрать RSQ, SLOPE или INTERCEPT чтобы открыть их окна функций, как показано ниже.
Окна RSQ, SLOPE и INTERCEPT практически одинаковы. Они включают в себя поля Known_y и Known_x, которые можно выбрать для добавления значений переменных y и x из вашей таблицы. Обратите внимание, что ячейки должны содержать только цифры, поэтому замените месяцы в таблице соответствующими цифрами, такими как 1 для января, 2 для февраля и т. Д. Затем нажмите Хорошо закрыть окно и добавить функцию в электронную таблицу.
Так что теперь вы можете украсить свои графики электронных таблиц Excel линиями тренда линейной регрессии. Они будут выделять общие тенденции для точек данных графиков, а с помощью уравнений регрессии они также являются удобными инструментами прогнозирования.
В этой статье описаны синтаксис формулы и использование функции LINEST в Microsoft Excel. Ссылки на дополнительные сведения о диаграммах и выполнении регрессионного анализа можно найти в разделе См. также.
Описание
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функцию ЛИНЕЙН также можно объединять с другими функциями для вычисления других видов моделей, являющихся линейными по неизвестным параметрам, включая полиномиальные, логарифмические, экспоненциальные и степенные ряды. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Инструкции приведены в данной статье после примеров.
Уравнение для прямой линии имеет следующий вид:
y = m1x1 + m2x2 +. + b
если существует несколько диапазонов значений x, где зависимые значения y — функции независимых значений x. Значения m — коэффициенты, соответствующие каждому значению x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив . Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.
Синтаксис
ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])
Аргументы функции ЛИНЕЙН описаны ниже.
Синтаксис
Известные_значения_y. Обязательный аргумент. Множество значений y, которые уже известны для соотношения y = mx + b.
Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.
Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.
Известные_значения_x. Необязательный аргумент. Множество значений x, которые уже известны для соотношения y = mx + b.
Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы известные_значения_y и известные_значения_x могут иметь любую форму — при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).
Если массив известные_значения_x опущен, то предполагается, что это массив , имеющий такой же размер, что и массив известные_значения_y.
Конст. Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.
Если аргумент конст имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.
Статистика. Необязательный аргумент. Логическое значение, которое указывает, требуется ли вернуть дополнительную регрессионную статистику.
Если статистика имеет true, то LINEST возвращает дополнительную регрессию; в результате возвращается массив .
Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.
Дополнительная регрессионная статистика.
Стандартные значения ошибок для коэффициентов m1,m2. mn.
Коэффициент определения. Сравнивает предполагаемые и фактические значения y и диапазоны значений от 0 до 1. Если значение 1, то в выборке будет отличная корреляция— разница между предполагаемым значением y и фактическим значением y не существует. С другой стороны, если коэффициент определения — 0, уравнение регрессии не помогает предсказать значение y. Сведения о том, как вычисляется 2, см. в разделе "Замечания" далее в этой теме.
Стандартная ошибка для оценки y.
F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.
Степени свободы. Степени свободы используются для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. ниже в разделе "Замечания". Далее в примере 4 показано использование величин F и df.
Регрессионная сумма квадратов.
Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе "Замечания" в конце данного раздела.
На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.
Замечания
Любую прямую можно описать ее наклоном и пересечением с осью y:
Наклон (m):
Чтобы найти наклон линии, обычно записанной как m, возьмите две точки на строке (x1;y1) и (x2;y2); наклон равен (y2 - y1)/(x2 - x1).
Y-перехват (b):
Y-пересечение строки, обычно записанное как b, — это значение y в точке, в которой линия пересекает ось y.
Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.
Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:
Наклон:
=ИНДЕКС( LINEST(known_y,known_x's);1)
Y-перехват:
=ИНДЕКС( LINEST(known_y,known_x),2)
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель ЛИНЕЙН. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:
где x и y — выборочные средние значения, например x = СРЗНАЧ(известные_значения_x), а y = СРЗНАЧ(известные_значения_y).
Функции ЛИННЕСТРОЙ и ЛОГЪЕСТ могут вычислять наилучшие прямые или экспоненциальное кривой, которые подходят для ваших данных. Однако необходимо решить, какой из двух результатов лучше всего подходит для ваших данных. Вы можетевычислить known_y(known_x) для прямой линии или РОСТ(known_y, known_x в) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, спрогнозируемых вдоль этой линии или кривой в фактических точках данных. Затем можно сравнить спрогнозируемые значения с фактическими значениями. Для наглядного сравнения можно отобразить оба этих диаграммы.
Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента определения r 2 — индикатор того, насколько хорошо уравнение, выданное в результате регрессионного анализа, объясняет связь между переменными. Значение r 2 равно ssreg/sstotal.
В некоторых случаях один или несколько столбцов X (предполагается, что значения Y и X — в столбцах) могут не иметь дополнительного прогнозируемого значения при наличии других столбцов X. Другими словами, удаление одного или более столбцов X может привести к одинаковой точности предсказания значений Y. В этом случае эти избыточные столбцы X следует не использовать в модели регрессии. Этот вариант называется "коллинеарность", так как любой избыточный X-столбец может быть выражен как сумма многих не избыточных X-столбцов. Функция ЛИНЕЙН проверяет коллинеарность и удаляет все избыточные X-столбцы из модели регрессии при их идентификации. Удалены столбцы X распознаются в результатах LINEST как имеющие коэффициенты 0 в дополнение к значениям 0 se. Если один или несколько столбцов будут удалены как избыточные, это влияет на df, поскольку df зависит от числа X столбцов, фактически используемых для прогнозирования. Подробные сведения о вычислении df см. в примере 4. Если значение df изменилось из-за удаления избыточных X-столбцов, это также влияет на значения Sey и F. Коллинеарность должна быть относительно редкой на практике. Однако чаще всего возникают ситуации, когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли тема в эксперименте участником определенной группы или не является ее участником. Если конст = ИСТИНА или опущен, функция LYST фактически вставляет дополнительный столбец X из всех 1 значений для моделирования перехвата. Если у вас есть столбец с значением 1 для каждой темы, если мальчик, или 0, а также столбец с 1 для каждой темы, если она является женщиной, или 0, последний столбец является избыточным, так как записи в нем могут быть получены из вычитания записи в столбце "самец" из записи в дополнительном столбце всех 1 значений, добавленных функцией LINEST.
Вычисление значения df для случаев, когда столбцы X удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n - k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.
При вводе константы массива (например, в качестве аргумента известные_значения_x) следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть другими в зависимости от региональных параметров.
Следует отметить, что значения y, предсказанные с помощью уравнения регрессии, возможно, не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.
Основной алгоритм, используемый в функции ЛИНЕЙН, отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК. Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:
Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.
Помимо вычисления статистики для других типов регрессии с помощью функции ЛГРФПРИБЛ, для вычисления диапазонов некоторых других типов регрессий можно использовать функцию ЛИНЕЙН, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:
работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:
y = m1*x + m2*x^2 + m3*x^3 + b
Формула может быть изменена для расчетов других типов регрессии, но в отдельных случаях требуется корректировка выходных значений и других статистических данных.
Значение F-теста, возвращаемое функцией ЛИНЕЙН, отличается от значения, возвращаемого функцией ФТЕСТ. Функция ЛИНЕЙН возвращает F-статистику, в то время как ФТЕСТ возвращает вероятность.
Примеры
Пример 1. Наклон и Y-пересечение
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Вы можете добавить предопределяные линии или полосы на диаграммы в нескольких приложения для Office. Добавляя линии, включая линии ряда, линии перепада, самый высокий уровень и полосы понижения, на определенную диаграмму, вы можете анализировать отображаемую информацию. Если отобразить линии или полосы больше не нужно, их можно удалить.
Новые возможности форматирования диаграмм в Excel ? Щелкните здесь для бесплатного 5-минутного обучающего видеоролика о том, как форматирование диаграмм.
Конкретные типы графиков и линир доступны в линихих и гограммах с областями, графиках, круговой и линихой диаграммах, диаграммах с областями и биржевых диаграммах.
Предопределяные типы линии и отсейки, которые можно добавить на диаграмму
На некоторые диаграммы можно добавить следующие линии и полосы:
Линии ряда Эти линии соединяют г ряд данных линир и гограммы с столбцами, чтобы подчеркнуть разницу в измерениях между каждым рядом данных. Вторичная круговая диаграмма и вторичная гряду по умолчанию отображают линии рядов, соединяющие основную круговую диаграмму со вторичной круговой или линичную диаграмму.
Линии перепада В двух- и объемных диаграммах с областью и графиками эти линии расширяются от точки данных до горизонтальной оси (категорий), что помогает уточнить, где заканчивается один маркер данных и начинается следующий маркер данных.
Высоко-низкие линии Доступны в двухерных графиках и отображаются по умолчанию в биржевых диаграммах. Линии с высокими значениями расширяются от самого высокого значения к наименьшему значению в каждой категории.
Полосы вверх и вниз На графиках с несколькими рядами данных полосы вверх и вниз указывают разницу между точками данных в первом и последнем рядах данных. По умолчанию эти полосы также добавляются в биржевые диаграммы, такие как Open-High-Low-Close и Volume-Open-High-Low-Close.
Добавление линий или полос
Щелкните линейчатую диаграмму, гистограмму, график, вторичную круговую диаграмму или гистограмму, диаграмму с областями или биржевую диаграмму, в которую вы хотите добавить линии или полосы.
Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.
На вкладке Макет в группе Анализ выполните одно из следующих действий:
Примечание: Для диаграмм различных типов доступны разные типы строк.
Совет: Вы можете изменить формат линий ряда, линий перепада, высоконизих или полос вверх, отображающихся на диаграмме, щелкнув линию или полосу правой кнопкой мыши и затем нажав кнопку Формат <линии или полосы>.
Удаление линий или полос
Щелкните линейчатую диаграмму, гистограмму, график, вторичную круговую диаграмму или гистограмму, диаграмму с областями или биржевую диаграмму, в которой есть стандартные линии или полосы.
Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.
На вкладке Макет в группе Анализ нажмите кнопку Линии или Полосы повышения и понижения и выберите Нет.
Совет: Вы также можете удалить линии или полосы сразу же после их добавления, нажав кнопку Отменить на панели быстрого доступа или клавиши CTRL+Z.
Вы можете добавить другие линии к любому ряду данных в диаграммах с областью, линичная диаграмма, столбец, график, биржевая, точечная или пузырьковая диаграмма, которая имеет объемную диаграмму без стека.
Добавление других линий
Этот шаг относится только Word для Mac: в меню Вид выберите пункт Разметка печати.
На диаграмме выберите ряд данных, в который вы хотите добавить линию, а затем перейдите на вкладку Конструктор диаграмм.
Например, щелкните одну из линий графика. Будут выделены все маркер данных этого ряд данных.
Выберите нужный вариант линии или нажмите кнопку Дополнительные параметры линии сетки.
В зависимости от типа диаграммы, некоторые параметры могут быть недоступны.
Удаление других линий
Этот шаг относится только Word для Mac: в меню Вид выберите пункт Разметка печати.
Щелкните диаграмму с линиями и перейдите на вкладку Конструктор диаграмм.
Интервальные графики (статистические диаграммы) представляют собой полезный инструмент для анализа частотных данных, предлагая пользователям возможность сортировать данные в группы (называемые рядами) на визуальном графике, аналогичном гистограмме. В этой статье пошагово описано как создать статистическую диаграмму и выполнить её настройку в Microsoft Excel.
Если вы хотите создавать статистические диаграммы в Excel, вам нужно будет использовать Excel 2016 или более позднюю версию. В более ранних версиях Office (Excel 2013 и до неё) эта функция отсутствует.
Как создать статистическую диаграмму в Excel
Говоря простым языком, частотный анализ данных состоит в том, что берутся собранные результаты и определяется, как часто встретились те или иные значения. В качестве примера можно взять результаты тестов учащихся и посчитать, в каких диапазонах чаще всего встречаются полученные студентами результаты.
Статистические диаграммы позволяют легко получать данные такого рода и визуализировать их в диаграмме Excel.
Начните с того, что введите данные в Microsoft Excel и выделите данные, на основе которых будет строится интервальный график. Вы можете выбрать данные вручную или кликните на любую ячейку в нужном диапазоне и нажмите Ctrl+A на клавиатуре.
В результате в вашу таблицу Excel будет вставлена гистограмма. Excel попытается параметры для данных, например, ширину интервалов, но вам может потребоваться внести изменения вручную после вставки диаграммы.
Форматирование гистограммы
Excel попытается определить интервалы и тип представления данных, но возможно вам придётся самостоятельно это настроить под ваши нужды. К примеру, в моём случае данные разбиты на 3 интервала, но я могу выбрать разбивку на интервалы с шагом в 10, либо отобразить данные по категориям. Рассмотрим это на конкретных примерах.
В открывшемся окне справа «Формат оси» выберите интервалы «По категориям»:
Теперь вы можете видеть каждое значение — в нашем случае это результаты тестов каждого из учеников.
Если нужен интервальный график, но с настраиваемой длиной интервала, то выберите вариант «Длина интервала» и установите нужную длину, например, 10:
Диапазоны нижней оси начинаются с наименьшего числа. Например, первая группа ячеек отображается как «[27, 37]», а самый большой диапазон заканчивается «[97, 107]», несмотря на то, что максимальный результат теста равен 100.
Вы можете выбрать определённое количество интервалов, в этом случае из максимального значения будет вычтено минимальное и полученный результат поделён на указанное количество интервалов — в результате интервалы могут заканчиваться на дробные числа:
Вы можете собрать все данные, которые больше определённого значения, в одном интервале, независимо от его длины. Для этого поставьте флажок «Выход за верхнюю границу интервала» и укажите значение, выше которого все результаты будут помещены в один интервал:
Аналогично в один интервал можно собрать все значения, ниже определённой величины, для этого поставьте флажок «Выход за нижнюю границу интервала» и укажите значение, ниже которого все результаты будут помещены в один интервал:
Эти опции работают в сочетании с другими форматами группировки интервалов, такими как ширина или количество интервалов.
Вы также можете вносить косметические изменения в вид интервального графика, включая замену заголовков и меток осей — для этого дважды кликните по области, которую вы хотите отредактировать. Дальнейшие изменения в тексте и цветах и параметрах панели можно выполнить, щёлкнув правой кнопкой мыши саму диаграмму и выбрав опцию «Форматировать область диаграммы».
Стандартные параметры форматирования диаграммы, в том числе изменение границ и параметров столбцов, появятся в меню «Формат области диаграммы» справа.
Если вас интересуют вопросы редактирования внешнего вида, то они более подробно рассмотрены в статье «Как сделать гистограмму в Microsoft Excel», где показано, как применять готовые стили или вручную настроить любые параметры графиков, в том числе формат текста.
Читайте также: