Интерполирование точек данных в Excel

Я уверен, что это та проблема, которую другие решали много раз раньше.

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

Итак, что я получу от каждого человека, это набор пар {date, value}, где в наборе отсутствуют даты.

Мне нужен полный набор пар {date, value}, где для каждой даты с диапазоном значение известно (либо измерено, либо вычислено). Я ожидаю, что для этого проекта будет достаточно простой линейной интерполяции.

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

Спасибо.

ПРИМЕЧАНИЕ. Когда эти наборы данных будут завершены, я определю наклон (т.е. использование в день), и из этого мы можем начать делать сравнения между домами.

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ После первых нескольких предложений: Я не хочу вручную определять, где находятся отверстия в моем измерительном наборе (слишком много неполных измерительных комплектов!). Я ищу что-то (существующее) автоматически, чтобы сделать это для меня. Поэтому, если мой ввод

{2009-06-01,  10}
{2009-06-03,  20}
{2009-06-06, 110}

Затем я ожидаю, что автоматически получим

{2009-06-01,  10}
{2009-06-02,  15}
{2009-06-03,  20}
{2009-06-04,  50}
{2009-06-05,  80}
{2009-06-06, 110}

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

Ответы

Ответ 1

Самый простой способ сделать это, вероятно, следующий:

Столбцы A и B должны содержать ваш ввод, а столбец G должен содержать все ваши значения даты. Формула переходит в столбец E.

Ответ 2

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

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

Примечания:

  • Формула работает:

    • с помощью функции MATCH, чтобы найти строку в диапазоне inputs непосредственно перед поиском значения (например, 3 - значение перед 3.5)
    • используя OFFSET, чтобы выбрать квадрат этой строки, а следующий (в светло-фиолетовом)
    • используя FORECAST для построения линейной интерполяции с использованием только этих двух точек и получения результата

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

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

Если вы хотите скопировать-вставить формулу, это:

=FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1

(inputs - именованный диапазон)

Ответ 3

Существуют две функции: LINEST и TREND, что вы можете попытаться увидеть, что дает вам лучшие результаты. Они берут множество известных Xs и Ys вместе с новым значением X и вычисляют новое значение Y. Разница в том, что LINEST выполняет простую линейную регрессию, тогда как TREND сначала попытается найти кривую, которая соответствует вашим данным, прежде чем делать регрессию.

Ответ 4

Хороший графический способ увидеть, насколько хорошо соответствуют ваши интерполированные результаты:

Возьмите пары даты, значения и нарисуйте их, используя график XY в Excel (а не линейную диаграмму). Щелкните правой кнопкой мыши на полученной строке на графике и нажмите "Добавить трендлайн". Существует множество различных вариантов выбора того, какой тип подгонки кривой используется. Затем вы можете перейти к свойствам вновь созданной линии тренда и отобразить уравнение и значение R-квадрата.

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

Ответ 5

Ответ выше, YGA не обрабатывает конец диапазона случаев, когда требуемое значение Х является таким же, как значение диапазона задания X. Используя пример, предоставленный YGA, формула excel вернет # DIV/0! ошибка, если было запрошено интерполированное значение в 9999. Это, очевидно, является частью причины, по которой YGA добавила крайние конечные точки 9999 и -9999 к диапазону входных данных, а затем предполагает, что все прогнозируемые значения находятся между этими двумя числами. Если такое дополнение нежелательно или невозможно, можно избежать другого # DIV/0! ошибка заключается в проверке соответствия точного входного значения по следующей формуле:

=IF(ISNA(MATCH(F3,inputs,0)),FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1)),OFFSET(inputs,MATCH(F3,inputs)-1,1,1,1))

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

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

Ответ 6

в качестве альтернативы.

=INDEX(yVals,MATCH(J7,xVals,1))+(J7-MATCH(J7,xVals,1))*(INDEX(yVals,MATCH(J7,xVals,1)+1)-INDEX(yVals,MATCH(J7,xVals,1)))/(INDEX(xVals,MATCH(J7,xVals,1)+1)-MATCH(J7,xVals,1))

где j7 - значение x.

xvals - диапазон значений x yvals - диапазон значений y

проще поместить это в код.

Ответ 7

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

  • Создайте для него линейный разброс (XY) (Insert = > Scatter);
  • Создайте линию тренда полиномиального или скользящего среднего, установите флажок "Отображать уравнение на диаграмме "(щелкните правой кнопкой мыши по строке = > Добавить линию тренда);
  • Скопируйте уравнение в ячейку и замените x на нужное значение x

На скриншоте ниже A12: A16 содержит x, B12: B16 содержит y 's, а C12 содержит формулу, которая вычисляет y для любого x.

Интерполяция Excel

Я впервые разместил ответ здесь, но позже нашел этот вопрос