Вопрос: Интерполяция промежуточных значений для произвольных данных в Excel 2013


У меня есть таблицы данных, подобные этому примеру, девять записей в A1: B9 в этом случае:

A    B
--   ---
1    2.9
2    5.06
3    7
4    8.84
5    10.87
6    13.24
7    16.22
8    20.25
9    36.7

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

Я хочу создать вторую таблицу, столбцы E и F, с количеством строк, которое является «следующим целым числом» для самого высокого значения в столбце B. В этом случае B9 = 36,7, таким образом, у него будет 37 строк. Столбец F1: F37 будет содержать целые числа от 1 до 37, столбец E должен иметь числовые значения, которые соответствуют F, в том же отношении, что и между столбцами от A до B. Другими словами, интерполируйте значения столбца E, соответствующие значениям столбца F.

Например, A3 = 3 и B3 = 7. В этом случае F7 = 7 и E7 = 3, поскольку B уже включает целое число 7 и имеет совпадающее значение в столбце A. Однако F8 = 8, которое является промежуточным значением, не содержащимся в столбце B. Таким образом, E8 будет находиться между 3 и 4, основываясь на исходных данных и должны быть интерполированы.

Идея заключается в том, что при построении графика A1: B9 будет иметь ту же форму, что и E1: F37. В этом примере я разбержу таблицу данных до 37 целых результатов, которые произошли бы в ходе первоначальных измерений, и посмотрим, какое время (в столбце E с десятичными знаками) эти значения произойдут.

Что я пробовал

Пытаясь решить это сам, мне удалось найти трудоемкую формулу (обратите внимание, что в моей попытке мои колонки E и F меняются на противоположные от описанных выше).

  1. Я создал столбец (K), содержащий разницу между элементами столбца B. K5 = B5-B4. Это смещение Y для каждого приращения X.
  2. Столбец E будет содержать столько последовательных целых чисел (37), начиная с 1, как следующее целочисленное значение самого большого элемента в B. В этом случае B9 содержит 36.7, поэтому 37.
  3. В F1: F37 я ввожу следующую формулу.

Ячейка F1 содержит:

=IF(E1>$B$9,$A$9+(E1-$B$9)/$K$9,IF(E1>$B$8,$A$8+(E1-$B$8)
    /$K$9,IF(E1>$B$7,$A$7+(‌​E1-$B$7)/$K$8,IF(E1>$B$6,$A$6+(E1-$B$6)
    /$K$7,IF(E1>$B$5,$A$5+(E1-$B$5)/$K$6,IF(E1‌​>$B$4,$A$4+
    (E1-$B$4)/$K$5,IF(E1>$B$3,$A$3+(E1-$B$3)/$K$4,IF(E1>$B$2,$A$2+
    (E1-$B$2‌​)/$K$3,IF(E1>$B$1,$A$1+(E1-$B$1)/$K$2,E1/$K$1)))))))))  

Это работает очень хорошо. Но это не автоматическая формула; необходимо ввести столько «IFs», сколько элементов в столбцах A + B (X + Y). Я тестировал диаграммы рассеяния с линиями от A1: B9 и E1: F37 (с обратной точностью для правильной последовательности X / Y), и они генерировали точно такую ​​же форму кривой, поэтому она работает.

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


4
2017-10-11 00:32


Источник


Это дубликат вашего последующего вопроса. Чтобы интерполировать, вам нужно определить взаимосвязь между значениями. В вашем этом вопросе полином 4-го порядка соответствует (или превышает) данные; в вашем следующем вопросе существует экспоненциальная зависимость между значениями. После того, как вы решите отношения, тогда это вопрос применения соответствующей формулы. Для этого набора данных вы должны вставить столбец одинаково разнесенных значений в строках 1..36, которые идут от 1..9, а затем использовать вариацию TREND для вычисления значений Y - Ron Rosenfeld
Для обоих ваших вопросов желаемая деятельность - REGRESSION, пожалуйста, измените формулировку своих вопросов. Для этого нет универсального встроенного решения, но вы можете найти полезные функции в Интернете с использованием правильных ключевых слов - Máté Juhász
@ MátéJuhász: Без значений X это также не регрессия. :-) Но регрессия на самом деле немного отличается от требований здесь. Регрессия - это приближение, основанное на всех данных, которые могут фактически не проходить через любую точку данных. То, что OP хочет здесь, - это интерполяция между фактическими точками. Он не указывает его характер, поэтому линейная интерполяция соответствовала бы требованию, но ответ Гэри Студента по другому вопросу использует фактическую экспоненциальную кривую. - fixer1234
Джентльмен, я был неудачен, чтобы задать второй вопрос при выборе 2 ^ n экспоненциальных данных. Большинство ответов находились в фокусе 2 ^ n. Это не должно. Вот почему я возвращаюсь сюда с исходными данными: девять элементов, X = от 1 до 9, Y = 2,9, 5,06, 7, 8,84, 10,87, 13,24, 16,22, 20,25, 36. Поскольку самым высоким элементом Y является 36, Я хотел бы расширить X до 36 элементов и, очевидно, X1 = 1, X12 = 7, X36 = 36, но мне нужно заполнить пробелы в Y относительно X. - Wagner Lip
Сначала у меня три проблемы; мой родной язык не английский, второй; У меня нет сильного математического фона, и в-третьих; кажется, что для моей таблицы Excel нет решения. Я не отказываюсь от вопросов, я просто следую рекомендациям по редактированию первого сообщения. Кроме того, возможно, я полностью не могу использовать этот форум. Благодарю. - Wagner Lip


Ответы:


Короткий ответ

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

Длительный ответ

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

Уравнение

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

В другом вопросе вы использовали данные, основанные на уравнении Y=2^X, Когда у вас есть фактическое уравнение, вы можете точно интерполировать. Выберите новое значение для X или Y и уравнение дает вам другое значение. Если вы не знаете фактическое уравнение, вам нужно найти тот, который приближается к нему. Я буду использовать этот ответ, чтобы сосредоточиться на подходах интерполяции. Обычно они используют встроенные инструменты анализа, которые выполняют большую часть работы. Если вам нужна более подробная информация о механизме использования определенного инструмента или более автоматизированного подхода, мы можем расширить его в другом ответе.

Попробуйте найти фактическое уравнение

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

Легкий способ сделать это - график данных и добавление линии тренда. В Excel есть ряд общих кривых, доступных для установки.

trend menu

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

2^N 

Excel использует e скорее, чем 2 как основание, которое является просто переводом (e0,693 является 2). Визуально, вы можете видеть, что линия тренда точно следует за данными. R2 также говорит вам об этом. р2 является статистической мерой того, сколько изменений в данных, которые вы учитываете с помощью вашего уравнения. Значение 1 означает, что уравнение учитывает 100% вариации или идеально подходит.

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

exp fit

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

poly 3

В качестве приближения процесса, лежащего за данными, это не очень удобно. В третьем порядке (уравнение, содержащее степени X до X ^ 3), оно имеет более крупные точки перегиба, чем данные, и все еще не совпадает. Таким образом, лежащее в основе уравнение не похоже на простую общую кривую, что означает, что уравнение нужно будет аппроксимировать.

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

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

straight vs curve

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

регрессия

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

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

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

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

Однако, вместо того, чтобы использовать его обычным способом, регрессию можно «злоупотреблять» в качестве обходного пути для того, что вы хотите, и это обычно будет работать. Когда вы пытаетесь моделировать процесс, простейшая формула обычно оценивается (бритва Оккама). С другой стороны, с достаточно сложным уравнением вы можете поместить что угодно. Вы всегда можете нарисовать каракули, которые пройдут через каждую точку. С N очков, вы можете найти N-1 которое будет проходить через все точки (наихудший сценарий).

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

Вот анализ ваших данных с использованием полиномиальной регрессии с последовательно вышестоящими уравнениями (первый снимок экрана включает заказы 3 - 5):

poly 3-5

(Нажмите на изображение для читаемого размера.) Обратите внимание, что инструмент anaysis включает в себя тип интерполяции, которую вы хотите сделать; он генерировал промежуточные значения. Для каждого анализа a(n) значения являются коэффициентами найденного уравнения. a(0) является константой, a(1) является коэффициентом для члена X ^ 1 и т. д. Он показывает R2 значение соответствия. Он должен быть практически 1 чтобы быть достаточно близко для вашей цели.

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

poly 3-5 chart

Когда мы добираемся до полинома 6-го и 7-го порядка, он выглядит так:

poly 6-7

poly 6-7 chart

Если бы мы пошли к полиному 8-го порядка для ваших 9 значений, это было бы прекрасно, но 7-й порядок, вероятно, достаточно близко. Для перспективы заметим, что уравнение 7-го порядка имеет R2 .99999 и до сих пор не совершенен.

Используя инструмент анализа регрессии, чтобы найти подходящую подгонку (в данном случае уравнение 7-го или 8-го порядка), вы получите промежуточные значения, которые вы хотите. Но неплохо было бы наметить результат и глазное яблоко, чтобы убедиться, что это не карабин.

Сплайны

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

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

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

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

dip

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

Вот как сплайны представляют собой кривую ваших данных:

spline

spline chart

Если сравнить это с кривыми регрессии высокого порядка, сплайны более «реагируют» на локальные вариации.

Я сделал этот анализ, используя LibreOffice Calc, у которого есть надстройка для анализа, которая включает сплайны. Как вы можете видеть, это также производит для сплайнов, результаты интерполирования, которые вы ищете. У меня нет готового доступа к Инструменту анализа Excel, поэтому я не знаю, включает ли Excel в сплайны. Если нет, LO Calc будет работать в Windows, и это бесплатно.

Нижняя линия

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


7
2017-10-17 18:06



Дорогой Fixer1234, я даже не могу поблагодарить вас. Я получил гораздо больше, чем ожидал. Теперь у меня есть материал, который можно долго заниматься, учиться и совершенствоваться. Вы - прекрасный учитель по-разному. Нам нужно больше таких людей, как ты, в мире. Спасибо за вашу преданность делу, время и знания. Вагнер. - Wagner Lip
@WagnerLip, пожалуйста. Рад помочь. В какой-то момент, возможно, вы будете делать это для кого-то другого. :-) - fixer1234
Fixer1234, еще раз спасибо. Я очень активно участвую в большом электронном форуме с 2002 года, помогая в максимально возможной степени. Это очень полезно, помогая людям искать знания. На самом деле, эта проблема Excel - это разработать способ помочь людям на форуме создавать лучшие масштабы (углы и метки) ручек в электронном оборудовании. - Wagner Lip


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

Неточные данные

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

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

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

Используя Y как X

В вашей проблеме вы хотите определить значения для Y (целочисленные значения от 1 до 37 в этом примере) и найдите соответствующие значения X. Это было достаточно легко сделать в вашем Y=2^X потому что это простое уравнение легко изменить на X=log(Y)/log(2), и вы можете напрямую рассчитать любое значение, которое вы хотите. Если уравнение не является чем-то простым, часто нет практического способа его инвертировать. В моем предыдущем ответе «злоупотребление» регрессионным подходом дает вам уравнение высокого порядка, но оно «одностороннее», часто нецелесообразно решать для обратного уравнения.

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

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

log

Итак, попробуйте полиномиальную посадку. Однако это такой случай, как я описал в предыдущем ответе. Значения от 1 до 8 подходят хорошо, но 9 дает ему диспепсию. Полином 3-го порядка дает вам удар:

poly3

Он становится все более «интересным» по мере увеличения порядка уравнения. К 7-му порядку вы получите следующее:

poly7

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

splines

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

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

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

  • Если уравнение известно, как и ваше Y=2^X Например, вы можете вычислить любое значение, которое вы хотите.

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

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

  • Если вы используете сплайны, нет оснований для проецирования вне диапазона данных.

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

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

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

zero vals

Обратите внимание, что в четвертом или пятом порядке точки с 1 по 8 довольно точны. Но как только вы выходите за пределы диапазона, уравнения могут вести себя по-разному.

Экстраполяция с использованием ограниченных данных

Один из способов улучшить ситуацию состоит в том, чтобы поместить только точки в этом конце и включить столько последовательных точек, сколько следует за кривой кривой с этой целью. Точка 9, очевидно, отсутствует. На кривой есть несколько перегибов, одна из которых находится вокруг точки 5 или 6, поэтому точки выше, чем за другой. Используя только точки с 1 по 5, вы приближаетесь к идеальной подгонке с полиномом 3-го порядка. Это уравнение будет проектировать нулевую точку 0,12095 (по сравнению с таблицей выше), а для X значение 1, 0.3493,

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

straight

Это проектирует нулевую точку -0,5138 и для X из 1, -0.0071,

Этот диапазон возможных результатов указывает на уровень неопределенности вне диапазона ваших данных. Правильного ответа нет. И это было на «хорошем» конце вашей кривой. Y значение для X из 9 является 36.7, Вы хотите перейти к 37. Спланы предполагают, что кривая асимптотична при 9, Проецирование прямой линии в необработанных данных создало бы ценность немного больше, чем 9 (то же, что и полином 4-го порядка). Полином третьего порядка предлагает значение меньше, чем 9 (как и 5-й и 6-й порядки). Полином 7-го порядка предлагает значение, существенно превышающее 9, Таким образом, все, что находится за пределами диапазона данных, является предположением или тем, что вы хотите.

Объединяя все это

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

У меня нет готового доступа к Excel 2013 или Analysis Toolkit. Я буду использовать LibreOffice Calc, чтобы проиллюстрировать это. Это не идентично, но достаточно близко, чтобы вы могли следить за ним в Excel. В LO Calc это фактически бесплатное расширение, которое необходимо загрузить. я использую CorelPolyGUI, которые можно загрузить Вот, Мое воспоминание об инструменте Analysis Toolkit заключается в том, что он не включает сплайны. Если это все еще так, и вы хотите сделать это в Excel, я натолкнулся эта бесплатная надстройка (который я не тестировал). Альтернативой будет использование LO Calc, который будет работать в Windows и свободен.

step 1

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

step 2

На следующей вкладке я указываю, что хочу использовать 0 в 7 степеней (полином 7-го порядка со всеми порядками).

step 3

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

step 4

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

step 5

Здесь я добавил 1 через 37 значения. На данный момент мы хотим только иметь дело с интерполяцией, поэтому я добавил формулу для вычисления только значений 3 через 36, Формула просто расширяет коэффициенты, перечисленные в результатах (значения a (n)). Формула в I2:

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

Это только каждый коэффициент, умноженный на связанную мощность значения X. Перетащите это, и у вас есть свои результаты. Ну не совсем; вы должны посмотреть на него, чтобы проверить, проходит ли он тест на здравомыслие. Мы знали, что существует проблема между 8 а также 9, но это оказывается половиной значений, которые вы хотите. Мы могли бы использовать значения из 3 через 20, но нет смысла сочетать многие значения с другим методом. Так что давайте просто использовать сплайны для всего.

step 6

Откройте диалоговое окно анализа еще раз и измените метод на «сплайны» на вкладке ввода (здесь не показано). Дайте ему новый выходной диапазон и скажите ему рассчитать. Это все, что нужно.

step 7

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

step 8

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

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

Я добавил столбец с вашими номерами 1 через 37, Из предыдущего обсуждения у нас нет надежной основы для прогнозирования значений для 1 а также 2, поэтому я оставил их пустыми. Для 37, Я пошел с асимптотическим допущением и сделал это 9, Значения для 3 через 36 найдены линейной интерполяцией (и это формула, которую вы могли бы адаптировать к другим данным). Формула в Q3:

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

Функция TREND просто интерполируется, когда диапазон составляет две точки. Синтаксис:

TREND(Y_range, X_range, X_value)  

Функция OFFSET используется для каждого диапазона. В каждом случае он использует функцию MATCH для поиска первой строки диапазона, содержащего целевое значение. -1 значения - это потому, что это смещения, а не местоположения; совпадение в первой строке является смещением 0 из ссылочной строки. И обратите внимание, что Y столбец компенсируется 2, в этом случае, потому что я добавил дополнительный столбец для ручной настройки значения. Параметры OFFSET выбирают столбец, содержащий значения Y или X, и выбирают высоту диапазона 2, которая дает значения ниже и выше цели.

Результат:

result

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


3
2017-10-18 21:42