Прочитав ваши комментарии и исправления к вопросу, есть пара вещей, которые вы хотите сделать, но которые не отражены в моем предыдущем ответе. Этот ответ будет касаться этих вопросов, и я включил пошаговое руководство по выполнению всего процесса интерполяции.
Неточные данные
Вы описываете процесс, который сгенерировал данные, как получение показаний за интервал времени, а числа округляются. Уравнение только так хорошо, как данные. В вашем фактическом анализе вы должны использовать самые точные доступные числа (возможно, вы просто сохраняли свой пример простым, показывая округленные времена).
Однако данные, которые вы показываете, не совсем соответствуют виду кривой, которую вы обычно видите для физического процесса. Теоретические кривые, как правило, гладкие, когда есть только одна переменная движения и нет шума. Если вы используете очень точное оборудование как для запуска считывания с заданным интервалом, так и для обеспечения точного измерения, вы можете принять результаты как точные. Однако, если вы вручную синхронизируете показания и вручную снимаете показания, значения X могут быть неточными, даже если сами показания являются точными. Сдвиг отдельных значений X немного тем или иным образом привнесет виды небольших неровностей, которые вы видите на кривой ваших данных (если только пример не является просто числами, которые вы составили для целей примера).
Если это так, вы можете воспользоваться регрессией для оценки наилучшего соответствия.
Используя Y как X
В вашей задаче вы хотите определить значения для Y (целочисленные значения от 1 до 37 в этом примере) и найти соответствующие значения X. Это было достаточно легко сделать в вашей задаче Y=2^X потому что это простое уравнение можно легко преобразовать в X=log(Y)/log(2) , и вы можете напрямую рассчитать любое значение, которое хотите. Если уравнение не является чем-то простым, часто не существует практического способа его инвертировать. Подход "злоупотребления" регрессии в моем предыдущем ответе дает вам уравнение высокого порядка, но это "однонаправленное", часто непрактичное решение для обратного уравнения.
Самый простой подход - просто поменять местами X и Y с самого начала. Это дает вам уравнение, которое вы можете использовать с целочисленными значениями, которые вы вводите (анализ дает вам коэффициенты уравнения, как описано в предыдущем ответе).
Никогда не больно видеть, сработает ли простая кривая. Вот обратные данные, и вы можете видеть, что это бесполезно:
Итак, попробуйте полиномиальную посадку. Тем не менее, это тот случай, который я описал в предыдущем ответе. Значения от 1 до 8 подходят хорошо, но 9 дает расстройство желудка. Полином 3-го порядка дает вам удар:
Он становится все более "интересным" с увеличением порядка уравнения. К 7-му заказу вы получите это:
Он проходит почти точно через каждую точку, но кривая между 8 и 9 бесполезна. Одним из решений было бы обойтись с линейной интерполяцией между 8 и 9. В этом случае, однако, вы можете получить лучшие значения, включив сплайны для верхнего конца. Опция сплайнов обеспечивает хорошую подгонку и кривую, которая имеет больше смысла между 8 и 9:
К сожалению, сплайн-уравнения немного запутаны, и уравнения не представлены. Тем не менее, вы можете выполнить линейную интерполяцию для промежуточных значений, предоставленных анализом, что должно очень приблизить вас к числам, которые соответствуют разумной кривой.
Экстраполяция против интерполяции
В этом примере ваше первое значение Y составляет 2,9. Вы хотите получить значения для 1 и 2 , которые находятся за пределами диапазона данных. Это требует экстраполяции, а не интерполяции, что является совсем другим требованием.
Если уравнение известно, как ваш пример Y=2^X , вы можете вычислить любое значение, которое вы хотите.
Если известно, что процесс, генерирующий данные, следует простой кривой, и вы уверены в соответствии, вы можете спроецировать значения за пределы диапазона данных и даже получить значимый доверительный интервал для диапазона, которым эти значения могут быть (на основе сколько различий между данными и кривой внутри диапазона данных).
Если вы принудительно подгоняете уравнение высокого порядка к данным, проекции за пределами диапазона данных обычно не имеют смысла.
Если вы используете сплайны, для проецирования за пределы диапазона данных нет никаких оснований.
Какие бы прогнозы вы ни делали за пределами диапазона ваших данных, они настолько же хороши, насколько и уравнение, которое вы используете, и если вы не используете точное уравнение, чем дальше вы получаете от своих данных, тем более неточными они будут.
Глядя на логарифмическую кривую на первом графике, вы можете увидеть, что она будет проецировать значение, отличное от ожидаемого.
Для полиномиальных уравнений коэффициент нулевой мощности является константой, и это значение, которое будет получено для значения X равного 0 . Так что это простой способ посмотреть, куда пойдет кривая в этом направлении.
Обратите внимание, что к 4-му или 5-му порядку точки с 1 по 8 довольно точны. Но как только вы выходите за пределы диапазона, уравнения могут вести себя совсем по-другому.
Экстраполяция с использованием ограниченных данных
Один из способов улучшения состоит в том, чтобы подогнать только точки на этом конце и включить столько последовательных точек, сколько следует форме кривой на этом конце. Точка 9 явно отсутствует. До этого на кривой было несколько перегибов, один из которых находится вокруг точки 5 или 6, поэтому точки выше, чем это, следуют другой кривой. Используя только точки с 1 по 5, вы приближаетесь к полному многочлену 3-го порядка. Это уравнение будет проецировать нулевую точку 0,12095 (сравните с таблицей выше), а для значения X 1 0.3493 .
Что произойдет, если вы просто подгоните прямую линию к первым пяти точкам:
Это проецирует нулевую точку -0,5138, а для X 1 -0.0071 .
Этот диапазон возможных результатов указывает на уровень неопределенности за пределами диапазона ваших данных. Там нет правильного ответа. И это было в конце концов. Значение Y для X 9 составляет 36.7 . Вы хотите перейти на 37. Сплайны предполагают, что кривая асимптотическая в 9 . Проецирование прямой линии в исходных данных приведет к значению чуть больше 9 (то же самое с полиномом 4-го порядка). Полином 3-го порядка предлагает значение меньше 9 (как и 5-й и 6-й порядки). Полином 7-го порядка предполагает значение, значительно превышающее 9 . Таким образом, все, что находится за пределами диапазона данных - это предположение, или все, что вы хотите.
Собираем все вместе
Итак, давайте рассмотрим, как будет выглядеть реальное решение. Предположим, вы уже пытались найти точное уравнение и протестировали общие кривые, используя линию тренда. Следующим шагом будет попытка регрессии, потому что это дает вам формулу для кривой, и вы можете вставить свои целочисленные значения.
У меня нет готового доступа к Excel 2013 или инструментам анализа. Я буду использовать LibreOffice Calc, чтобы проиллюстрировать это. Он не идентичен, но достаточно близок, чтобы вы могли следить за ним в Excel. В LO Calc это фактически бесплатное расширение, которое необходимо загрузить. Я использую CorelPolyGUI, который можно скачать здесь. Мое воспоминание об инструменте анализа состоит в том, что в нем не было сплайнов. Если это все еще так, и вы хотите сделать это в Excel, я наткнулся на эту бесплатную надстройку (которую я не тестировал). Альтернативой может быть использование LO Calc, который будет работать в Windows и является бесплатным.
Здесь я ввел значения X и Y (в обратном порядке) в столбцах A и B и открыл диалоговое окно анализа. Выделение значений X и нажатие кнопки X загружает диапазоны данных, и я выбрал полином.
На следующей вкладке я указываю, что хочу использовать от 0 до 7 градусов (полином 7-го порядка со всеми ордерами).
Чтобы указать выходные данные, я выбираю C1 и нажимаю «Столбцы», и он регистрирует столбцы, необходимые для вывода. Я выбираю, что я хочу, чтобы он выводил исходные данные, вычисленные результаты, и я решил добавить три промежуточные точки между каждой исходной точкой данных. И я говорю, что хочу график результатов на новом графике. Затем перейдите в меню расчета и нажмите кнопку рассчитать.
И вот оно. Если вы посмотрите на рассчитанные значения, вы можете заметить проблему. Это станет очевидным на следующем этапе.
Здесь я добавил значения от 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 , но нет смысла объединять столько значений из другого метода. Так что давайте просто использовать сплайны для всего этого.
Снова откройте диалоговое окно анализа и измените метод на "сплайны" на вкладке ввода (здесь не показано). Дайте ему новый выходной диапазон и попросите его рассчитать. Это все, что нужно.
У нас есть новые результаты для работы. Разделение диапазона данных на это множество сегментов делает каждый сегмент коротким, поэтому линейная интерполяция должна быть довольно хорошей (намного лучше, чем использовать ее для исходных данных).
Процесс подбора кривой или интерполяции включает создание точек данных; используя ваше собственное суждение о том, как кривая "должна" (или не должна) выглядеть, (регрессия предполагает, что даже исходные данные являются неточными).
Проверка этих данных показывает, что даже сплайны образуют соединительную кривую с выпуклостью; одно значение немного больше 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, которая дает вам значения ниже и выше цели.
Результат:
Мастер анализа выполняет тяжелую работу, и независимо от того, используете ли вы полиномиальную регрессию или сплайны, для генерации результата требуется всего одна формула.
