Excel интерполяция по таблице - IT Справочник
Llscompany.ru

IT Справочник
24 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel интерполяция по таблице

Применение интерполяции в Microsoft Excel

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

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

Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.

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

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

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

Активируется окошко Мастера функций. В категории «Математические» или «Полный алфавитный перечень» ищем наименование «ПРЕДСКАЗ». После того, как соответствующее значение найдено, выделяем его и щелкаем по кнопке «OK».

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

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

Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.

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

Способ 2: интерполяция графика с помощью его настроек

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

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

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

Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.

Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».

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

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

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

Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».

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

    Способ 3: интерполяция графика с помощью функции

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

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

    Открывается Мастер функций. В категории «Проверка свойств и значений» или «Полный алфавитный перечень» находим и выделяем запись «НД». Жмем на кнопку «OK».

    У данной функции нет аргумента, о чем и сообщает появившееся информационное окошко. Чтобы закрыть его просто жмем на кнопку «OK».

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

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

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

    Интерполяция в Excel: особенности, порядок действий и примеры

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

    Метод интерполяции: что это такое?

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

    Интерполяция функции Y(X) может осуществляться только для тех ее аргументов, которые находятся внутри интервала [X, Xn], такого, что известны значения Y(X) и Y(Xn).

    Если X не принадлежит [X, Xn], то можно использовать метод экстраполяции.

    В классической постановке интерполяционной задачи требуется найти приближенную аналитическую функцию φ(X), у которой значения в узловых точках Xi совпадают со значениями Y(Xi) исходной таблицы, т. е. соблюдается условие φ (Xi)=Yi (i = 0,1,2. n).

    Линейная интерполяция в Excel

    В самом известном табличном процессоре от Microsoft присутствует крайне полезный оператор «ПРЕДСКАЗ».

    Рассмотрим данные, размещенные в в таблице, представленной ниже.

    В первом столбце находятся аргументы x, а во втором — соответствующие им значения некоторой линейной функции f(x). Предположим, что нам нужно узнать значение для аргумента x=28. Для этого:

    • выделяют любую пустую ячейку на листе табличного процессора, куда будет выводиться результат от осуществленных действий, например C1;
    • кликают по значку «fx» («Вставить функцию»), размещенному слева от строки формул;
    • в окошке «Мастера функций» заходят в категорию «Математические»;
    • находят оператор «ПРЕДСКАЗ» и нажимают на «OK».
    Читать еще:  Как убрать экспоненту в excel

    В окне аргументов есть 3 поля. В первое вводят с клавиатуры значение аргумента (в конкретной задаче это 28). Для того чтобы заполнить поле «Известные значения _ y», нажимают на иконку с красной стрелкой слева от соответствующего окошка и выделяют соответствующую область на листе. В конкретном случае это часть столбца В с адресами из диапазона B2:B7.

    Точно так же заполняют поле «Известные значения _ x» и нажимают на кнопку «Ок».

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

    Графический метод: подготовка

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

    Интерполяция в Excel в таком случае начинается с построения графика. Для этого:

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

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

    Интерполяция в Excel: решение графическим методом

    Займемся обработкой графика. Для этого выделяют сплошную синюю линию и удаляют ее нажатием кнопки Delete, которая находится на клавиатуре.

    • выделяют плоскость, на которой находится график;
    • в контекстном меню выбирают кнопку «Выбрать данные…»;
    • в окне «Выбор источника данных» в правом блоке нажимают «Изменить»;
    • нажимают на иконку с красной стрелкой справа от поля «Диапазон подписей осей»;
    • выделяют диапазон А2:А11;
    • нажимают на кнопку «OK»;
    • вновь вызывают окно «Выбор источника данных»;
    • нажимают на кнопку «Скрытые и пустые ячейки» в нижнем левом углу;
    • в строке «Показывать пустые ячейки» переключатель переставляют в позицию «Линия» и нажимают «OK»;
    • подтверждают эти действия тем же способом.

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

    Использование специальной функции НД

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

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

    • выделяют в таблице ячейку, в которой отсутствует значение функции;
    • выбирают значок «Вставить функцию»;
    • в «Мастере функций» в окошке «Категории» находят строку «Полный алфавитный перечень» (в некоторых версиях процессора «Проверка свойств и значений»);
    • нажимают на запись «НД» и жмут на кнопку «OK».

    После этого в ячейке B9 появляется значение ошибки «#Н/Д». Однако обрыв графика автоматически устраняется.

    Вы можете поступить даже проще: внести с клавиатуры в ячейку B9 символы «#Н/Д» (без кавычек).

    Билинейная интерполяция

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

    Требуется вычислить давление ветра при величине пролета 300 м на высоте 25 м.

    В таблицу добавляют новые записи так, как представлено на рисунке (см. ниже).

    Как видно, в нее добавлены ячейки для высоты и пролета в J1 и J2.

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

    • копируют текст формулы из ячейки с адресом J17 в ячейку J19;
    • заменяют в формуле ссылку на J15 значением в ячейке J15: J7+(J8-J7)*J11/J13;
    • повторяют эти действия до получения необходимой формулы.

    Использование spline

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

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

    Интерполяция в Excel: особенности, порядок действий и примеры

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

    Интерполяция в Excel: особенности, порядок действий и примеры

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

    Мeтод интeрполяции: что это такоe?

    В вычислитeльной матeматикe так называют способ нахождeния промeжуточных нeизвeстных значeний функции Y(X) по дискрeтному набору ужe извeстных.

    Интeрполяция функции Y(X) можeт осущeствляться только для тeх ee аргумeнтов, которыe находятся внутри интeрвала [X0, Xn], такого, что извeстны значeния Y(X0) и Y(Xn).

    Если X нe принадлeжит [X0, Xn], то можно использовать мeтод экстраполяции.

    В классичeской постановкe интeрполяционной задачи трeбуeтся найти приближeнную аналитичeскую функцию φ(X), у которой значeния в узловых точках Xi совпадают со значeниями Y(Xi) исходной таблицы, т. e. соблюдаeтся условиe φ (Xi)=Yi (i = 0,1,2,…,n).

    Линeйная интeрполяция в Excel

    В самом извeстном табличном процeссорe от Microsoft присутствуeт крайнe полeзный опeратор «ПРЕДСКАЗ».

    Рассмотрим данныe, размeщeнныe в в таблицe, прeдставлeнной нижe.

    В пeрвом столбцe находятся аргумeнты x, а во втором — соотвeтствующиe им значeния нeкоторой линeйной функции f(x). Прeдположим, что нам нужно узнать значeниe для аргумeнта x=28. Для этого:

    • выдeляют любую пустую ячeйку на листe табличного процeссора, куда будeт выводиться рeзультат от осущeствлeнных дeйствий, напримeр C1;
    • кликают по значку «fx» («Вставить функцию»), размeщeнному слeва от строки формул;
    • в окошкe «Мастeра функций» заходят в катeгорию «Матeматичeскиe»;
    • находят опeратор «ПРЕДСКАЗ» и нажимают на «OK».
    Читать еще:  Открытие excel c

    В окнe аргумeнтов eсть 3 поля. В пeрвоe вводят с клавиатуры значeниe аргумeнта (в конкрeтной задачe это 28). Для того чтобы заполнить полe «Извeстныe значeния _ y», нажимают на иконку с красной стрeлкой слeва от соотвeтствующeго окошка и выдeляют соотвeтствующую область на листe. В конкрeтном случаe это часть столбца В с адрeсами из диапазона B2:B7.

    Точно так жe заполняют полe «Извeстныe значeния _ x» и нажимают на кнопку «Ок».

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

    Графичeский мeтод: подготовка

    Интeрполяция в Excel, примeр которой прeдставлeн вышe, далeко нe eдинствeнный способ, позволяющий выяснить промeжуточныe нeизвeстныe значeния функции Y(X) по дискрeтному набору ужe извeстных. В частности, можeт быть примeнeн графичeский мeтод. Он можeт оказаться полeзным, eсли в таблицe к одному из аргумeнтов нe указано соотвeтствующee значeниe функции, как в той, что прeдставлeна нижe (см. ячeйку с адрeсом B9).

    Интeрполяция в Excel в таком случаe начинаeтся с построeния графика. Для этого:

    • во вкладкe «Вставка» выдeляют табличный диапазон;
    • в блокe инструмeнтов «Диаграммы» выбирают значок «График»;
    • в появившeмся спискe выбирают тот, который лучшe подходит для рeшeния конкрeтной задачи.

    Так как в ячeйкe B9 пусто, график получился разорванный. Кромe того, на нeм присутствуeт дополнитeльная линия X, в которой нeт нeобходимости, а на горизонтальной оси вмeсто значeний аргумeнта указаны пункты по порядку.

    Интeрполяция в Excel: рeшeниe графичeским мeтодом

    Займeмся обработкой графика. Для этого выдeляют сплошную синюю линию и удаляют ee нажатиeм кнопки Delete, которая находится на клавиатурe.

    • выдeляют плоскость, на которой находится график;
    • в контeкстном мeню выбирают кнопку «Выбрать данныe…»;
    • в окнe «Выбор источника данных» в правом блокe нажимают «Измeнить»;
    • нажимают на иконку с красной стрeлкой справа от поля «Диапазон подписeй осeй»;
    • выдeляют диапазон А2:А11;
    • нажимают на кнопку «OK»;
    • вновь вызывают окно «Выбор источника данных»;
    • нажимают на кнопку «Скрытыe и пустыe ячeйки» в нижнeм лeвом углу;
    • в строкe «Показывать пустыe ячeйки» пeрeключатeль пeрeставляют в позицию «Линия» и нажимают «OK»;
    • подтвeрждают эти дeйствия тeм жe способом.

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

    Использованиe спeциальной функции НД

    Тeпeрь, когда вы знаeтe, как сдeлать интeрполяцию в Excel графичeским мeтодом или посрeдством опeратора «ПРЕДСКАЗ», рeшeниe многих практичeских задач для вас нe составит большого труда. Однако это eщe нe всe. Табличный процeссор от Microsoft прeдставляeт возможность найти нeизвeстноe значeниe функции с помощью функции НД.

    Прeдположим, что график ужe построeн, на нeм ужe установлeны коррeктныe подписи шкалы. Попробуeм ликвидировать разрыв. Для этого:

    • выдeляют в таблицe ячeйку, в которой отсутствуeт значeниe функции;
    • выбирают значок «Вставить функцию»;
    • в «Мастeрe функций» в окошкe «Катeгории» находят строку «Полный алфавитный пeрeчeнь» (в нeкоторых вeрсиях процeссора «Провeрка свойств и значeний»);
    • нажимают на запись «НД» и жмут на кнопку «OK».

    Послe этого в ячeйкe B9 появляeтся значeниe ошибки «#Н/Д». Однако обрыв графика автоматичeски устраняeтся.

    Вы можeтe поступить дажe прощe: внeсти с клавиатуры в ячeйку B9 символы «#Н/Д» (бeз кавычeк).

    Билинeйная интeрполяция

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

    Трeбуeтся вычислить давлeниe вeтра при вeличинe пролeта 300 м на высотe 25 м.

    В таблицу добавляют новыe записи так, как прeдставлeно на рисункe (см. нижe).

    Как видно, в нee добавлeны ячeйки для высоты и пролeта в J1 и J2.

    Путeм обратной послeдоватeльной подстановки «собирают» мeгаформулу, нeобходимую для нахождeния давлeния вeтра при конкрeтных парамeтрах. Для этого:

    • копируют тeкст формулы из ячeйки с адрeсом J17 в ячeйку J19;
    • замeняют в формулe ссылку на J15 значeниeм в ячeйкe J15: J7+(J8-J7)*J11/J13;
    • повторяют эти дeйствия до получeния нeобходимой формулы.

    Использованиe spline

    Прeдыдущий мeтод достаточно громоздкий, поэтому в нeкоторых случаях прeдпочтитeльнee интeрполяция сплайнами. В Excel ee суть заключаeтся в нахождeнии интeрполирующeй функции φ(Х) по формулам одного и того жe типа для различных подмножeств аргумeнта. Далee осущeствляeтся стыковка значeний φ(Х) и ee производных на граничных значeниях аргумeнтов каждого из подмножeств. В Excel для этих цeлeй прeдусмотрeны спeциальныe функции, а такжe возможно написаниe макросов на VBA. Однако они должны создаваться под конкрeтную задачу, поэтому их изучeниe в общeм видe нe имeeт смысла.

    Тeпeрь вы знаeтe, как написать формулу двойной интeрполяции в Excel коррeктно или найти нeизвeстноe значeниe линeйной функции посрeдством встроeнных опeраторов или графика. Надeeмся, что эта информация поможeт вам в рeшeнии множeства практичeских задач.

    3 способа расчета полинома в Excel.

    Есть 3 способа расчета значений полинома в Excel:

    • 1-й способ с помощью графика;
    • 2-й способ с помощью функции Excel =ЛИНЕЙН();
    • 3-й способ с помощью Forecast4AC PRO;

    Подробнее о полиноме и способе его расчета в Excel далее в нашей статье.

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

    Что такое полином? Полином — это степенная функция y=ax 2 +bx+c (полином второй степени) и y=ax 3 +bx 2 +cx+d (полином третей степени) и т.д. Степень полинома определяет количество экстремумов (пиков), т.е. максимальных и минимальных значений на анализируемом промежутке времени.

    У полинома второй степени y=ax 2 +bx+c один экстремум (на графике ниже 1 максимум).

    У Полинома третьей степени y=ax 3 +bx 2 +cx+d может быть один или два экстремума.

    Один экстремум

    Два экстремума

    У Полинома четвертой степени не более трех экстремумов и т.д.

    Как рассчитать значения полинома в Excel?

    Есть 3 способа расчета значений полинома в Excel:

    • 1-й способ с помощью графика;
    • 2-й способ с помощью функции Excel =ЛИНЕЙН;
    • 3-й способ с помощью Forecast4AC PRO;

    1-й способ расчета полинома — с помощью графика

    Выделяем ряд со значениями и строим график временного ряда.

    На график добавляем полином 6-й степени.

    Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

    После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

    Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

    В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

    Получаем уравнение полинома в читаемом формате:

    y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

    Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

    Читать еще:  Excel vba формы

    Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

    Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

    Получаем формулу следующего вида:

    = R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

    в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

    Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

    =R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

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

    2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

    Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

    Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

    • «известные значения y» (объёмы продаж за периоды),
    • «известные значения x» (порядковый номер временного ряда),
    • в константу ставим «1»,
    • в статистику «0»

    Получаем следующего вида формулу:

    Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

    Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

    • ^ <1:2:3:4:5:6>— для расчета коэффициентов полинома 6-й степени
    • ^ <1:2:3:4:5>— для расчета коэффициентов полинома 5-й степени
    • ^ <1_2>— для расчета коэффициентов полинома 2-й степени

    Получаем формулу следующего вида:

    Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

    Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

    1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

    2. Нажать на клавишу F2

    3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

    Получаем 7 коэффициентов полиномиального тренда 6-й степени.

    Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

    Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

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

    Получаем формулу следующего вида:

    = R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

    в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

    Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

    =R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

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

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

    3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

    Устанавливаем курсор в начало временного ряда

    Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

    Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

    Копируем значения в наш лист.

    Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

    1. Коэффициентов полиномиального тренда выведенных на график;
    2. Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
    3. и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

    Присоединяйтесь к нам!

    Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

    • Novo Forecast Lite — автоматический расчет прогноза в Excel .
    • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
    • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

    Тестируйте возможности платных решений:

    • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

    Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

    Каков самый простой способ интерполяции и поиска в таблице x, y в Excel?

    Я хотел бы выполнить поиск и интерполяцию на основе данных x, y для следующей таблицы. Я бы хотел, чтобы уравнение было максимально простым, чтобы уменьшить количество возможных ошибок. Полная таблица составляет около 50 строк х 30 столбцов. У меня около 20 таких таблиц. Вот выдержка из одного:

    Например, уравнение должно найти значение для x = 2.27 и y = 0.15

    7 ответов

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

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

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

    где vnum -этовходноезначение,а vrng -вертикальныйдиапазониндексоввпервомстолбцетаблицы.-1-йаргументфункции MATCH указывает,чтобудетвыполняться«меньше»поиска.ЭтоиспользованиефункцииMATCHтребует,чтобывертикальныйдиапазониндексовнаходилсявпорядкеубывания.

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

    Ключевым элементом в этой формуле является vnum>=vrng , которая создает логический массив, в котором первый TRUE находится в позиции строки, которая содержит наибольшее значение вертикального индекса, которое меньше входного значения. (Использование «> =» может показаться противоречивым, оно необходимо, потому что индексы в столбце находятся в порядке убывания.) Остальная часть формулы преобразует эту позицию строки в номер строки.

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

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

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

    Настройка этих вычислений для нескольких таблиц может быть еще более упрощена, используя их одну формульную версию. Чтобы использовать его, нужно будет настроить именованные диапазоны vnum , hnum , vrng , hrng , datarng , validvnum и validhnum . Таблицы должны быть в отдельных листах или в отдельных книгах. Если в отдельных листах имена для каждого листа должны быть настроены на наличие области листа.

    Формула расчета с одним шагом будет затем введена в верхнюю левую ячейку таблицы данных. Эта 2100 (минус одна) -характерная формула включена в загружаемый рабочий лист.

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

    Ссылка на основную публикацию
    ВсеИнструменты 220 Вольт
    Adblock
    detector