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

Экстраполяция в Excel (Эксель)

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

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

Пример работы с табличными данными

Имеется таблица с конкретным диапазоном аргументов от 5 до 50, которые относятся к функции (f(x)). В данном примере надо вычислить результат для числа, которое находится за рамкой изветсных аргументов. В данном случае это число 55. Чтобы это сделать надо работать с функцией ПРЕДСКАЗ.

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

Появится новое окно, в нём нужно выбрать среди категорий именно категорию «Статистические» и после этого ниже в списке надо выбрать «ПРЕДСКАЗ» и в конце подтвердить действие.

Далее откроется новое окно аргументов функции. Там где указание аргумента Х, вводим адрес нужной ячейки. Это наиболее оптимально, так как потом адрес ячейки можно выбрать другой и провести уже другие вычисления. Следующее поле называется «Известные значения Y». В нём надо выделить все ячейки с числами, с которыми ведется работа. В последнем поле известных значений Х, нужно выделить все значения которые относятся к аргументу X (первый столбец с примера). Когда эти действия произведены, подтверждаем изменения.

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

Пример работы с данными в графиках линией тренда

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

Далее будет отображен график по выбранными ранее данным. Важное примечание : нужно удалить в нём линию обозначающую аргумент (указана стрелкой на изображении).

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

Появится новое окно, в нём кликаем соответствующую кнопку для изменения данных.

Далее кликаем на «Диапазон подписей оси» и после этого нужно выделить столбец значений, которые надо ЛКМ выделить числа, которые нам нужны, в данном примере это столбец с значениями x, после этого подтверждаем действие. Также не забудьте подтвердить действие в окне выбора источника данных, которое было открыто ранее.

Этим действиями мы подготовили график для работы. А теперь построим линию тренда. Для этого нужно нажать по графику, который мы подготовили и выше, в главном меню разделов, будет активирован нужным нам раздел «Макет», в нем надо выбрать пункт «Анализ» и выбрать кнопку линии тренда. Далее кликаем на «Линейное приближение», это наиболее оптимальный вариант. После этих действий линия тренда будет добавлена в график.

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

Далее будет открыто новое окно, в котором можно задать параметры линии тренда. Ищем в окне настройки прогноза, и задаем число 1 (период), так как пять единиц значений = одному периоду, это было сделано так как значение за пределами 50 возьмем вновь 55.

Результатом будет удлинение длины графика соответственно к параметрам линии тренда.

Применение экстраполяции в Microsoft Excel

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

Использование экстраполяции

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

Читать еще:  Как сделать калькулятор в access?

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

Способ 1: экстраполяция для табличных данных

Прежде всего, применим метод экстраполяции к содержимому табличного диапазона. Для примера возьмем таблицу, в которой имеется ряд аргументов (X) от 5 до 50 и ряд соответствующих им значений функции (f(x)). Нам нужно найти значение функции для аргумента 55, который находится за пределом указанного массива данных. Для этих целей используем функцию ПРЕДСКАЗ.

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

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

В поле «X» следует указать значение аргумента, функцию от которого нам следует вычислить. Можно просто вбить с клавиатуры нужное число, а можно указать координаты ячейки, если аргумент записан на листе. Второй вариант даже предпочтительнее. Если мы произведем внесение именно таким способом, то для того, чтобы просмотреть значение функции для другого аргумента нам не придется менять формулу, а достаточно будет изменить вводную в соответствующей ячейке. Для того, чтобы указать координаты этой ячейки, если был выбран все-таки второй вариант, достаточно установить курсор в соответствующее поле и выделить эту ячейку. Её адрес тут же отобразится в окне аргументов.

В поле «Известные значения y» следует указать весь имеющийся у нас диапазон значений функции. Он отображается в колонке «f(x)». Следовательно, устанавливаем курсор в соответствующее поле и выделяем всю эту колонку без её наименования.

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

После того, как все данные внесены, жмем на кнопку «OK».

После этих действий результат вычисления путем экстраполяции будет выведен в ячейку, которая была выделена в первом пункте данной инструкции перед запуском Мастера функций. В данном случае значение функции для аргумента 55 равно 338.

  • Если все-таки был выбран вариант с добавлением ссылки на ячейку, в которой содержится искомый аргумент, то мы легко сможем его поменять и просмотреть значение функции для любого другого числа. Например, искомое значение для аргумента 85 буде равно 518.
  • Способ 2: экстраполяция для графика

    Выполнить процедуру экстраполяции для графика можно путем построения линии тренда.

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

    После того, как график построен, удаляем из него дополнительную линию аргумента, выделив её и нажав на кнопку Delete на клавиатуре компьютера.

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

    В запустившемся окне выбора источника данных кликаем по кнопке «Изменить» в блоке редактирования подписи горизонтальной оси.

    Открывается окно установки подписи оси. Ставим курсор в поле данного окна, а затем выделяем все данные столбца «X» без его наименования. Затем жмем на кнопку «OK».

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

    Теперь наш график подготовлен и можно, непосредственно, приступать к построению линии тренда. Кликаем по графику, после чего на ленте активируется дополнительный набор вкладок – «Работа с диаграммами». Перемещаемся во вкладку «Макет» и жмем на кнопку «Линия тренда» в блоке «Анализ». Кликаем по пункту «Линейное приближение» или «Экспоненциальное приближение».

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

    Запускается окно формата линии тренда. В разделе «Параметры линии тренда» есть блок настроек «Прогноз». Как и в предыдущем способе, давайте для экстраполяции возьмем аргумент 55. Как видим, пока что график имеет длину до аргумента 50 включительно. Получается, нам нужно будет его продлить ещё на 5 единиц. На горизонтальной оси видно, что 5 единиц равно одному делению. Значит это один период. В поле «Вперед на» вписываем значение «1». Жмем на кнопку «Закрыть» в нижнем правом углу окна.

    Читать еще:  Как сделать запрос в access 2003?

  • Как видим, график был продлен на указанную длину с помощью линии тренда.
  • Итак, мы рассмотрели простейшие примеры экстраполяции для таблиц и для графиков. В первом случае используется функция ПРЕДСКАЗ, а во втором – линия тренда. Но на основе этих примеров можно решать и гораздо более сложные задачи прогнозирования.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Прогнозирование значений в рядах

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

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

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

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

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

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

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

    Перетащите маркер заполнения в нужном направлении, увеличив значения или уменьшив значения.

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

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

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

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

    Чтобы заполнить ряд для экспоненциальной тенденции, выполните указанные ниже действия.

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

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

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

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

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

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

    В линейной серии начальные значения применяются к алгоритму наименьших квадратов (y = mx + b) для создания ряда.

    В ряде роста начальные значения применяются к алгоритму экспоненциальной кривой (y = b * m ^ x) для создания ряда.

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

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

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

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

    Читать еще:  Как сделать форму главной в access?

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

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

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

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

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

    Результат значения шага

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

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

    В разделе типвыберите вариант линейный или рост.

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

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

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

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

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

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

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

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

    Если вы выбрали параметр полином, введите в поле порядок самое высокое значение для независимой переменной.

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

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

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

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

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

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

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

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

    Перетащите маркер заполнения в нужном направлении, увеличив значения или уменьшив значения.

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

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

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

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

    Ссылка на основную публикацию
    Adblock
    detector