Вопрос или проблема
Мне нужно вычислить результирующее “Y” на основе входного значения ‘X’.
Уравнение, которое я использую ниже, работает, пока значение ‘X’ не превышает 200%.
Как я могу отредактировать формулу, чтобы исправить эту проблему?
Данные и вычисления в Excel
Формулы:
- F2: =INDEX($B$2:$B$7,(MATCH(E2,A2:A7,1)))+(E2-INDEX($A$2:$A$7,(MATCH(E2,A2:A7,1))))*(INDEX($B$2:$B$7,(MATCH(E2,A2:A7,1))+1)-INDEX($B$2:$B$7,(MATCH(E2,A2:A7,1))))/(INDEX($A$2:$A$7,(MATCH(E2,A2:A7,1))+1)-INDEX($A$2:$A$7,(MATCH(E2,A2:A7,1))))
- F3: =INDEX($B$2:$B$7,(MATCH(E3,A2:A7,1)))+(E3-INDEX($A$2:$A$7,(MATCH(E3,A2:A7,1))))*(INDEX($B$2:$B$7,(MATCH(E3,A2:A7,1))+1)-INDEX($B$2:$B$7,(MATCH(E3,A2:A7,1))))/(INDEX($A$2:$A$7,(MATCH(E3,A2:A7,1))+1)-INDEX($A$2:$A$7,(MATCH(E3,A2:A7,1))))
- F4: =INDEX($B$2:$B$7,(MATCH(E4,A2:A7,1)))+(E4-INDEX($A$2:$A$7,(MATCH(E4,A2:A7,1))))*(INDEX($B$2:$B$7,(MATCH(E4,A2:A7,1))+1)-INDEX($B$2:$B$7,(MATCH(E4,A2:A7,1))))/(INDEX($A$2:$A$7,(MATCH(E4,A2:A7,1))+1)-INDEX($A$2:$A$7,(MATCH(E4,A2:A7,1))))
- F5: =INDEX($B$2:$B$7,(MATCH(E5,A2:A7,1)))+(E5-INDEX($A$2:$A$7,(MATCH(E5,A2:A7,1))))*(INDEX($B$2:$B$7,(MATCH(E5,A2:A7,1))+1)-INDEX($B$2:$B$7,(MATCH(E5,A2:A7,1))))/(INDEX($A$2:$A$7,(MATCH(E5,A2:A7,1))+1)-INDEX($A$2:$A$7,(MATCH(E5,A2:A7,1))))
Вы хотите посмотреть за пределами диапазона данных, так что это больше не интерполяция, а экстраполяция.
В этом случае самый простой способ справиться с этим — сократить диапазон данных x с A2:A7 до A2:A6.
Для F2 следовательно, получится следующая формула:
=INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1))+
(E2-INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$6,1)))*
(INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1)+1)-
INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1)))/
(INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$6,1)+1)-
INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$6,1)))
Эту нотацию можно сократить и упростить различными способами, но я не знаю, связано ли это с использованием более старой версии Excel.
В Excel 2021 или более поздних версиях вы можете использовать формулу, подобную этой:
=LET(xw,E2,data,OFFSET(XLOOKUP(xw,$A$2:$A$6,$A$2:$B$6,,-1,2),,,2),
FORECAST.LINEAR(xw,INDEX(data,,2),INDEX(data,,1)))
Ответ или решение
Ошибка #REF! в Интерполяции Excel: Решение и Предложения
В процессе работы с данными в Excel, пользователи часто сталкиваются с ошибками, такими как #REF!, особенно при интерполяции значений. В данной статье мы рассмотрим, как исправить формулы интерполяции, чтобы избежать этой ошибки, а также предложим альтернативный подход для экстраполяции в случае, если вводимое значение выходит за пределы заданного диапазона.
Проблема
Пользователь сталкивается с проблемой, когда при вводе значения X, превышающего 200%, формулы, используемые для интерполяции в Excel, возвращают ошибку #REF!. Это связано с тем, что функция MATCH не может найти значение, превышающее максимальное значение в диапазоне.
Решение
Чтобы избежать ошибки #REF! и корректно выполнять интерполяцию, необходимо следовать следующим шагам:
-
Сокращение диапазона: Убедитесь, что диапазон A2:A7 корректен. Если ожидаются значения, превышающие максимальное значение (в вашем случае 200%), то рекомендуется сократить диапазон до A2:A6 (или другого подходящего значения).
-
Исправление формулы: Формулы в ячейках F2-F5 можно модифицировать с учётом нового диапазона:
=INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1)) + (E2 - INDEX($A$2:$A$7, MATCH(E2, $A$2:$A$6, 1))) * (INDEX($B$2:$B$7, MATCH(E2, $A$2:$A$6, 1) + 1) - INDEX($B$2:$B$7, MATCH(E2, $A$2:$A$6, 1))) / (INDEX($A$2:$A$7, MATCH(E2, $A$2:$A$6, 1) + 1) - INDEX($A$2:$A$7, MATCH(E2, $A$2:$A$6, 1))) -
Использование функции FORECAST.LINEAR: Если у вас Excel версии 2021 или выше, вы можете воспользоваться более простой и удобной функцией. Например, замените формулы в ячейках F2-F5 на:
=LET(xw, E2, data, OFFSET(XLOOKUP(xw, $A$2:$A$6, $A$2:$B$6, , -1, 2), , , 2), FORECAST.LINEAR(xw, INDEX(data,,2), INDEX(data,,1)))Эта формула позволяет не только интерполировать, но и экстраполировать значения для ввода
X, превышающего границы исходного диапазона, без возврата ошибки.
Подводя итоги
Избегание ошибки #REF! при интерполяции в Excel достигается за счёт корректировки диапазонов и адаптации формул в соответствии с требуемыми границами значений. Повышение функциональности формул с использованием новых функций, таких как FORECAST.LINEAR, обеспечивает более надежные и простые в использовании решения.
Если у вас возникли дополнительные вопросы по экстраполяции и интерполяции в Excel или по любой другой теме, обратитесь к специализированным ресурсам или сообществам, где профессионалы могут предоставить дополнительную помощь.