Excel

Линейная экстраполяция в excel: Прогнозирование значений в рядах — Служба поддержки Майкрософт

Прогнозирование значений в рядах — Служба поддержки Майкрософт

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

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

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

Начальное значение

Расширенный линейный ряд

1, 2

3, 4, 5

1, 3

5, 7, 9

100, 95

90, 85

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

    org/ItemList»>

  1. Выделите не менее двух ячеек, содержащих начальные значения для тренда.

    Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.

  2. Перетащите его в нужном направлении.

    Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.

Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд(вкладкаГлавная, группа Редактирование, кнопка Заполнить).

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

Начальное значение

Расширенный ряд роста

1, 2

4, 8, 16

1, 3

9, 27, 81

2, 3

4. 5, 6.75, 10.125

Чтобы заполнить ряд для тенденции роста, сделайте следующее:

  1. Выделите не менее двух ячеек, содержащих начальные значения для тренда.

    Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.

  2. Удерживая нажатой правую кнопку мыши, перетащите указатель заполнения в нужном направлении, отпустите кнопку мыши, а затем на ленте нажмите кнопку контекстное меню.

Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания. x), который создает ряд.

В обоих случаях шаг игнорируется. Созданный ряд эквивалентен значениям, возвращенным функцией ТЕНДЕНЦИЯ или функцией РОСТ.

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

  1. Вы выберите ячейку, в которой нужно начать ряд. Ячейка должна содержать первое значение ряда.

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

  2. На вкладке Главная в группе Редактирование нажмите кнопку Заполнить и выберите пункт Прогрессия.

  3. Выполните одно из указанных ниже действий.

    • Чтобы заполнить ряд вниз по worksheet, щелкните Столбцы.

    • Чтобы заполнить ряд по всему ряду, щелкните Строки.

  4. В поле Шаг введите значение, на которое вы хотите увеличить ряд.

Тип ряда

Результат шага

Линейная

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

Геометрическая

Первое начальное значение умножается на шаг. Результат и каждый последующий результат умножаются на шаг.

  1. В области Типвыберите линейный или Рост.

  2. В поле Остановить значение введите значение, на которое нужно остановить ряд.

Примечание: Если ряд имеет несколько начальных значений и Excel создать тенденцию, выберите значение Тренд.

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

Предполагается, что вы уже создали диаграмму, основанную на существующих данных. Если это не так, см. раздел Создание диаграммы.

  1. Щелкните диаграмму.

  2. Щелкните ряд данных, в который вы хотите добавить линия тренда или скользящее среднее.

  3. org/ListItem»>

    На вкладке Макет в группе Анализ нажмите кнопку Линия тренда ивыберите нужный тип линии тренда или скользящего среднего.

  4. Чтобы настроить параметры и отформатирование линии тренда или скользящего среднего, щелкните линию тренда правой кнопкой мыши и выберите в меню пункт Формат линии тренда.

  5. Выберите нужные параметры линии тренда, линии и эффекты.

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

    • Если выбрано значение Скользящегосреднего , введите в поле Период количество периодов, используемых для расчета лино-среднего.

Примечания: 

  • В поле На основе ряда перечислены все ряды данных на диаграмме, которые поддерживают линии тренда. Чтобы добавить линию тренда к другому ряду, щелкните имя в поле и выберите нужные параметры.

  • При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения x.

Важно: Начиная с Excel 2005 г., Excel способ вычисления значенияR2 для линейных линий тренда на диаграммах, где для перехваченной линии тренда установлено значение нуля (0). Эта корректировка исправит вычисления, которые дают неправильные значения R2,и выровняет вычислениеR2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих версиях Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме. 

Если вам нужно выполнить более сложный регрессионный анализ, в том числе вычислить и отсчитывать остаточные данные, используйте средство регрессионного анализа в надстройке «Надстройка «Надстройка анализа». Дополнительные сведения см. в окне Загрузка средства анализа.

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

Вот как можно использовать его для создания линейного тренда чисел в Excel в Интернете:

  1. Выделите не менее двух ячеек, содержащих начальные значения для тренда.

    Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.

  2. Перетащите его в нужном направлении.

Использование функции ПРОГНОЗ    Функция ПРЕДСПРОС вычисляет или предсказывает будущее значение с использованием существующих значений. Предсказываемое значение — это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. Эта функция используется для предсказания будущих продаж, требований к запасам и потребительских тенденций.

Использование функции ТЕНДЕНЦИЯ или ФУНКЦИИ РОСТ    Функции ТЕНДЕНЦИЯ и РОСТ могут выполнять экстраполяцию будущих значений y,которые расширяют прямую или экспоненциальный кривую, наилучшим образом описывающую существующие данные. Они также могут возвращать только значения yна основе известных значений x-длянаиболее подходящих строк или кривой. Для отстройки линии или кривой, описывающую существующие данные, используйте существующие значения x-value и y-value,возвращаемые функцией ТЕНДЕНЦИЯ или РОСТ.

Использование функции ЛИНИИСТОЛ или ФУНКЦИИ ЛОГЕСТ    Функцию ЛИННЕФ или LOGEST можно использовать для вычисления прямой или экспоненциальной кривой из существующих данных. Функции LINEST и LOGEST возвращают различные статистические данные о регрессии, включая наклон и отступ линии, которая лучше всего подходит.

В следующей таблице содержатся ссылки на дополнительные сведения об этих функциях.

Функция

Описание

Прогноз

Project значения

Тенденция

Project значения, которые соответствуют прямой линии тренда

Роста

Project, которые соответствуют экспоненциальной кривой

Линейн

Расчет прямой линии из существующих данных

LOGEST

Расчет экспоненциальной кривой из существующих данных

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

4. Построение прогнозов в Excel

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

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

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

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

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

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

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

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

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

Свойства коэффициента корреляции:

1) Если две случайные величины Х и Y независимы, то их коэффициент корреляции равен нулю, т.е. r=0.

2) Модуль коэффициента корреляции не превышает единицы, т.е. |r|1, что эквивалентно двойному неравенству:1r1.

3) Равенство коэффициента -1 или +1 показывает наличие функциональной (прямой) связи. Знак «+» указывает на связь прямую (увеличение или уменьшение одного признака сопровождается аналогичным изменением другого признака), знак «-» — на связь обратную (увеличение или уменьшение одного признака сопровождается противоположным по направлению изменением другого признака).

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

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

Линии на координатной плоскости, соответствующие уравнениям регрессии называются линиями регрессии.

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

Линейная регрессия

Уравнением линейной регрессии(выборочным)YнаХназывается зависимость от наблюдаемых значений величины Х, выраженная линейной функцией: , где величина называется коэффициентом линейной регрессии YнаХ, b— константа.

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

Если коэффициент корреляции двух величин ХиYравенr=1, то эти величины связаны линейной зависимостью. Коэффициент корреляции служит мерой силы (тесноты) линейной зависимости измеряемых величин. На практике, если коэффициент корреляции двух величинХиY|r|>0.5, то считают, что есть основания предполагать наличие линейной зависимости между этими величинами. Однако ориентироваться при выборе типа линии регрессии (линейной или нелинейной) лучше по виду эмпирической зависимости величинХиY.

Параболическая и полиномиальная регрессии.

Параболическойзависимостью величиныYот величиныХназывается зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):. Это уравнение называетсяуравнением параболической регрессииYнаХ. Параметрыа,b,сназываютсякоэффициентами параболической регрессии.

Уравнение параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальнойзависимостью величиныYот величиныХназывается зависимость, выраженная полиномомn-ого порядка: ,где числааi(i=0,1,…,n) называютсякоэффициентами полиномиальной регрессии.

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

Степенная регрессия.

Степеннойзависимостью величиныYот величиныХназывается зависимость вида: . Это уравнение называетсяуравнением степенной регрессииYнаХ. Параметрыаиbназываютсякоэффициентами степенной регрессии.

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

Показательная регрессия.

Показательной(илиэкспоненциальной) зависимостью величиныYот величиныХназывается зависимость вида: (или )

Это уравнение называется уравнениемпоказательной(илиэкспоненциальной)регрессииYнаХ. Параметрыа(илиk) иbназываютсякоэффициентами показательной(илиэкспоненциальной)регрессии.

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

Логарифмическая регрессия.

Логарифмическойзависимостью величиныYот величиныХназывается зависимость вида:.Это уравнение называетсяуравнением логарифмической регрессииYнаХ. Параметрыаиbназываютсякоэффициентами логарифмической регрессии.

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

Гиперболическая регрессия.

Гиперболическойзависимостью величиныYот величиныХназывается зависимость вида: . Это уравнение называетсяуравнением гиперболической регрессии YнаХ. Параметрыаиbназываютсякоэффициентами гиперболической регрессии.

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

Использование табличного процессора позволяет легко выполнить все этапы регрессионного анализа.

Использование функции ТЕНДЕНЦИЯ

Для прогнозирования процессов, изменение которых носит линейный характер можно использовать функцию ТЕНДЕНЦИЯ.

Эта функция позволяет находить значения в соответствии с линейным трендом. Она аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений Yи известных значения Х. Находит новые значенияY, в соответствии с этой прямой для новых значений Х. Например, если у нас есть данные изменения цены на энергоносители за несколько последних лет, то с помощью функции ТЕНДЕНЦИЯ мы можем получить прогноз на цену на энергоносители на будущий год. При этом если зависимость цены от времени близка к линейной, то результат будет удовлетворительным.

Предварительно, перед использованием функции ТЕНДЕНЦИЯ необходимо ввести (в столбец или в строку) массивы XиY, взаимосвязанных величин, а также значенияXдля которых будет спрогнозирована величинаY.

Затем, вызываем статистическую функцию ТЕНДЕНЦИЯ. Для этого необходимо вызвать Мастер функций, щелкнув на кнопке строки формул или отдав команду Вставка/Функция.В окне Мастера функций выберите категориюСтатистическиеи в полеВыберите функцию выберите из списка функцию ТЕНДЕНЦИЯ. В появившемся диалоговом окнеАргументы функции (рис.5.8) в соответствующие поля внесите ссылки на диапазоны ячеек в которых хранятся известные значения величинYиX, новые значения Х, для которых ТЕНДЕНЦИЯ возвращает соответствующие значенияY. С помощью поляКонст можно указать вычислять ли константу b или принять равной 0. Если это поле имеет значение ИСТИНА или опущено, то b вычисляется обычным образом, если ЛОЖЬ – то b полагается равным 0, и значенияподбираются таким образом, чтобы выполнялось соотношениеy = x.

Рис. 8. Фрагмент диалогового окна Аргументы функции.

После ввода всех необходимых аргументов функции необходимо нажать на кнопке ОК.

Быстрое построение линий регрессии в Excel: линия тренда.

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

Выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!).

Вызвать Мастер диаграмм (используя инструмент или команду Вставка/Диаграмма) и в открывшемся окне Мастер диаграмм выбрать в группе Тип – Точечная и сразу нажать Готово.

Не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма, в котором следует выбрать пункт Добавить линию тренда.

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

Рис.5.9. Вкладка Линия тренда.

.

Экстраполяция в Excel

Экстраполяция — это математический метод, который делает прогнозы за пределами определенного диапазона путем программирования и расширения прошлых известных данных. Так что это тип анализа и визуализации данных Excel. В этом уроке мы узнаем, как экстраполировать данные в Excel.

Содержание

  • Формула экстраполяции
  • Как экстраполировать график по линии тренда
  • Как экстраполировать нелинейные данные по линии тренда
    • Choose The Best Trendline
      • Exponential
      • Linear
      • Logarithmic
      • Polynomial
      • Power
      • Moving Average
  • Extrapolation Data by the Forecast Function
    • Forecast. Linear
      • Possible Errors
    • Forecast.ETS
      • Возможные ошибки
    • Листы экстраполяции
  • Функция Excel Trend
    • Возможные ошибки

Формула экстраполяции

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

  • A(a, b)
  • B(c, d)

Формула линейной экстраполяции:

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

Рисунок 1- Формула линейной экстраполяции в Excel

Как экстраполировать график по линии тренда 

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

  1. Выберите диапазон данных.
  2. Перейдите на вкладку Вставить на ленте.
  3. В разделе диаграммы щелкните диаграмму Line (вы также можете взять точечную диаграмму).
  4. Щелкните значок Chart Element и установите флажок Trendline.
  5. Дважды щелкните линию тренда на графике, чтобы открыть панель Format Trendline и применить пользовательскую настройку.
Рисунок 2. Как добавить линию тренда на график

Как экстраполировать нелинейные данные по линии тренда

ценить. Вот как это можно сделать:

  1. Нарисуйте точечную диаграмму.
  2. Нажмите на Значок Chart Element и установите флажок Trendline.
  3. Дважды щелкните линию тренда на графике, чтобы открыть панель Format Trendline .
  4. Добавьте на график различные типы линий тренда (экспоненциальные, логарифмические и полиномиальные) и установите флажки « Показать значение R-квадрата на графике » и « Показать уравнение на графике ».
    Чтобы определить наилучшую линию тренда, посмотрите на значение R-квадрат . Наибольшее значение R-квадрата показывает наилучшую линию тренда для ваших данных.
  5. подставьте x в уравнение, показанное на диаграмме.

Выберите лучшую линию тренда

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

Point: не используйте линии тренда для Радар , PIE , DONUT , BUBLE и 3D Графики .

В Excel у нас есть шесть типов линий тренда.

  1. Экспоненциальный
  2. Linear
  3. Logarithmic
  4. полиномиальные
  5. Power
  6. Средняя средняя
Экспоненциальные

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

Линейный

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

Логарифмический

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

Полином

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

Мощность

Эта линия тренда используется при сравнении измерений, которые увеличиваются с определенной скоростью. Недействительно для нулевых или отрицательных значений X.

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

Эта линия тренда использует среднее значение определенного числа точек данных с помощью параметра «Период».

Данные Экстраполяция с помощью функции прогноза

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

Здесь мы узнаем, как использовать функции Forecast.linear, и Forecast.ETS и как экстраполировать лист.

Forecast.Linear

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

Ниже приведен синтаксис функции Forecast.Linear:

=ПРОГНОЗ.ЛИНЕЙНЫЙ(x; известно Ys; известно Xs)

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

  1. Выберите пустую ячейку.
  2. Введите =прогноз или =прогноз.линейный в строке формул .
  3. Нажмите на значение x , которое вы хотите спрогнозировать, и введите точку с запятой или запятую (в зависимости от вашей версии Excel).
  4. Выберите все известных Ys , введите точку с запятой, а затем выберите все известные Xs .
  5. Нажмите Введите .
Рисунок 3- Синтаксис функции Forecast.Linear
Возможная ошибка

#Н/Д: Если размер known_Ys и known_Xs не равен или если один или оба пусты, возникает эта ошибка.

#DIV/0: Если дисперсия known_Xs равна нулю, возникает эта ошибка 

#ЗНАЧ: Если ввод x не является числом, произойдет эта ошибка. Нажмите на ссылку, если хотите узнать больше об ошибках в формулах Excel и о том, почему они возникают.

Forecast.ETS

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

Синтаксис функции Forecast.ETS:

=ПРОГНОЗ.ETS(целевая_дата значения временная шкала[сезонность], [данные_сопоставление]; [агрегация])

Целевая_дата : Точка, которую нужно спрогнозировать.

Значения : Здесь представлены все известные объемы продаж.

Временная шкала : В данном случае количество месяцев.

[ сезонность ]: Длина сезонного шаблона (необязательный аргумент.)

[data_complation] : Хотя временная шкала требует постоянного шага между точками данных, FORECAST. ETS поддерживает до 30% отсутствующих данных и автоматически подстроится под него (необязательный аргумент).

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

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

  • Введите синтаксис функции и введите аргументы, как мы уже упоминали.
  • Нажмите Enter.
    • Рисунок 4- Синтаксис функции Прогноз.ETS
    Возможные ошибки

    #Н/Д : Если массивы значений и временной шкалы имеют разные размеры, эта ошибка возвращается

    #VALUE , 0 Аргументы агрегации или не являются числовыми, возникнет эта ошибка.

    #NUM : Эта ошибка будет возвращена, если сезонность превышает 87600, значение завершения данных отличается от 0 или 1, 9Значение агрегации 0005 недопустимо (любое нецелое число или вне диапазона от 1 до 7), или функция не может определить согласованный размер шага на временной шкале . Если вы хотите узнать больше об ошибках в формулах Excel и о том, почему они возникают, нажмите на ссылку.

    Экстраполяция листов

    Excel 2016 и более поздние версии предоставляют инструмент для прогнозирования листа. Этот инструмент создает таблицу по вашим данным и определяет нижнюю и верхнюю доверительные границы.

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

    Рисунок 5. Инструмент «Лист прогнозов» в Excel

    Если вам нужно настроить диаграмму прогноза, вы можете отредактировать ее, нажав вариантов :

    • Где начинается или заканчивается прогноз
    • Изменить доверительный интервал
    • Добавить статистику прогноза
    • Изменить временную шкалу и диапазон значений
    • И агрегировать дубликаты, используя

    Затем нажмите кнопку Создать и посмотрите результат.

    Рисунок 6. Результат работы инструмента «Лист прогноза»

    Функция тренда в Excel

    Еще одна функция для экстраполяции данных без построения графиков — это функция тренда в Excel. Эта статистическая функция будет предсказывать будущие тенденции в соответствии с известными значениями на основе линейной регрессии.

    Синтаксис функции тренда:

    =TREND(известные_Y; [известные_X]; [новые_X]; [постоянные])

    Известные Y : Значения Y, которые мы уже знаем.

    Известные значения X : Уже известные значения X (необязательный аргумент.)

    Const : согласно Y=mX+b, если const ложно, b равно нулю, но если const истинно или пропущено, b рассчитывается нормально.

    Рисунок 7 – Функция тренда в Excel

    Возможные ошибки

    #ССЫЛКА! : Если аргументов known_Xs и аргументов known_Ys имеют разные размеры, возникает эта ошибка. значение в качестве других аргументов функции. Нажмите на ссылку, если хотите узнать больше об ошибках в формулах Excel и о том, почему они возникают.

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

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

    Часто задаваемые вопросы

    1- Надежна ли экстраполяция?

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

    2- В чем разница между экстраполяцией и регрессией?

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

    3- В чем разница между функциями ПРОГНОЗ и ТЕНДЕНЦИЯ?

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

    Как экстраполировать данные в Excel (5 удобных способов)

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

    Скачать практическую рабочую тетрадь

    5 удобных способов экстраполировать данные в Excel

    1. Использование формулы для экстраполяции данных в Excel

    2. Применение линии тренда для экстраполяции данных в Excel

    (a) Линия тренда для экстраполяции данных на графике

    (b) Линия тренда для экстраполяции нелинейных данных

    3. Использование функции ПРОГНОЗ для экстраполяции данных в Excel

    (a) Использование ПРОГНОЗА. Линейная функция

    (b) Использование ПРОГНОЗА. Функция ETS

    4. Применение команды прогнозного листа для экстраполяции данных в Excel

    5. Вставка функции TREND для экстраполяции данных

    Заключение

    Статьи по Теме

    Загрузить рабочую тетрадь

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

    Экстраполяция данных

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

    5 удобных способов экстраполировать данные в Excel

    В данном наборе данных у нас есть список из 7 человек с разным ростом и весом. Теперь будем экстраполировать последние 2 неизвестные веса человека, используя 4 различные удобные методы.

    1. Использование формулы для экстраполяции данных в Excel

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

    Шаги:

    • Сначала выберите ячейку F10 . Запишите следующую формулу для экстраполяции данных .

    =F8+ (D10-D8)/(D9-D8)*(F9-F8)

    • Во-вторых, нажмите Введите  и ячейка F10 будет представлять вес первого человека.

    • В-третьих, используйте инструмент Fill Handle и перетащите его вниз из ячейки F10 в F11. Следовательно, мы получим результаты других ячеек.

    2. Применение линии тренда для экстраполяции данных в Excel

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

    (a) Линия тренда для экстраполяции данных на графике

    Шаги:

    • Сначала выберите столбец высота и вес из заданного набора данных.
    • Затем нажмите на Вставка и перейдите к команде Рекомендуемые графики .

    • Теперь щелкните любой график из списка Рекомендуемые графики . Затем вы получите предварительный просмотр выбранной диаграммы на правой боковой панели.
    • Наконец, нажмите OK.

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

    (b) Линия тренда для экстраполяции нелинейных данных

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

    Шаги:

    • Следуя описанному выше методу Trendline , мы создадим следующий график .

    • Вы можете отобразить на графике другие типы линий тренда , такие как экспоненциальная , логарифмическая и полиномиальная , дважды щелкнув вышеупомянутую линию тренда и выбрав «Отображать значение R-квадрата на графике» и « Отображать уравнение на графике ».
    • Посмотрите на значение R-квадрата , чтобы выбрать оптимальную линию тренда. Линия тренда, которая лучше всего соответствует вашим данным, имеет самое высокое значение R-квадрата .
    • В уравнение, отображаемое на графике выше, введите x . После этого просмотрите результаты взвешивания последних двух человек на графике и наборе данных выше.

    Подробнее: Как экстраполировать линию тренда в Excel (4 быстрых метода) 

    3. Использование функции ПРОГНОЗ для экстраполяции данных в Excel

    Вы будете использовать функцию ПРОГНОЗ , когда вам нужна функция для прогнозирования ваших данных без создания диаграмм и графиков. Вы можете экстраполировать числовые данные на линейный тренд с помощью функции ПРОГНОЗА . Кроме того, вы можете экстраполировать лист или даже периодический шаблон. Здесь мы рассмотрим, как экстраполировать лист, а также как использовать функции FORECAST.LINEAR и FORECAST.ETS.

    (a) Использование ПРОГНОЗА. ЛИНЕЙНАЯ Функция

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

    Шаги:

    • Сначала выберите ячейку F10 . Запишите следующую формулу с ПРОГНОЗ.ЛИНЕЙНЫЙ функция для экстраполяции данных.

    =ПРОГНОЗ.ЛИНЕЙНЫЙ(D10,F5:F9,D5:D9)

    • Во-вторых, нажмите Введите и ячейка F10 будет представлять вес первого человека .

    • В-третьих, используйте инструмент Fill Handle и перетащите его вниз из ячейки F10 в F11. Следовательно, мы получим результаты других ячеек.

    (b) Использование ПРОГНОЗА. Функция ETS

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

    Шаги:

    • Сначала выберите ячейку F10 . Запишите следующую формулу с функцией FORECAST.ETS для экстраполяции данных.

    =ПРОГНОЗ.ETS(D10,F5:F9,D5:D9)

    • Во-вторых, нажмите Введите и ячейка F10 будет представлять вес первого человека .

    • В-третьих, используйте инструмент Fill Handle и перетащите его вниз из ячейки F10 в F11. Следовательно, мы получаем результаты других ячеек.

    Подробнее: Как экстраполировать график в Excel (2 простых метода)

    4. Применение команды прогнозного листа для экстраполяции данных в Excel

    Команда Forecast Sheet создает таблицу в соответствии с набором данных и оценивает доверительный интервал.

    Шаги:

    • Выберите столбцы высота и вес .

    • Перейдите на вкладку Данные .
    • Теперь нажмите на команду Forecast Sheet .

    • Наконец, вы получите следующие результаты.

    5.

    Вставка функции TREND для экстраполяции данных

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

    Шаги:

    • Сначала выберите ячейку F10 . Запишите следующую формулу с функцией TREND для экстраполяции данных.

    =ТРЕНД(F5:F9,D5:D9,D10:D11)

    • Во-вторых, нажмите Введите и ячейка F10 будет представлять вес первого человека .

    • В-третьих, используйте инструмент Fill Handle и перетащите его вниз из ячейки F10 в F11 Таким образом, мы получаем результаты других ячеек.

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *