Como interpolar valores intermediários para dados arbitrários no Excel

Question

Tenho tabelas de dados como este exemplo, nove entradas em A1:B9 neste caso:

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

O acima representa nove medições de uma variável física crescente não linear em B, Tensão por exemplo, e A representa exatamente cada um dos nove minutos redondos em que a medição foi feita.

Quero criar uma segunda tabela, colunas E e F, com uma quantidade de linhas que seja o "próximo número inteiro" para o maior valor da coluna B. Neste caso, B9=36,7, portanto terá 37 linhas. A coluna F1:F37 conterá números inteiros de 1 a 37, a coluna E deverá ter valores numéricos que correspondam a F, na mesma relação entre as colunas A a B. Ou seja, interpolar os valores da coluna E correspondentes aos valores da coluna F.

Por exemplo, A3=3 e B3=7. Neste caso, F7=7 e E7=3 porque B já incluiu o inteiro 7 e tem um valor correspondente na coluna A. No entanto, F8=8, que é um valor intermediário não contido na coluna B. Portanto, E8 ficará entre 3 e 4, baseado nos dados originais, devendo ser interpolados.

A ideia é que ao traçar um gráfico, A1:B9 terá o mesmo formato que E1:F37. Neste exemplo, expandirei a tabela de dados para 37 resultados inteiros que teriam ocorrido ao longo das medições originais e verei a que horas (na coluna E, com casas decimais) esses valores teriam ocorrido.

O que eu tentei

Ao tentar resolver isso sozinho, consegui encontrar uma fórmula demorada (observe que em minha tentativa, minhas colunas E e F são invertidas em relação ao que descrevi acima).

1. Criei uma coluna (K) contendo a diferença entre os elementos da coluna B. K5 = B5-B4. Esse é o deslocamento Y para cada incremento X.
2. A coluna E conterá tantos números inteiros sequenciais (37), começando em 1, quanto o próximo valor inteiro do maior elemento em B. Nesse caso, B9 contém 36,7, então 37.
3. Em F1:F37 eu insiro a seguinte fórmula.

A célula F1 contém:

=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)))))))))  

Funciona muito bem. Mas não é uma fórmula automatizada; deve-se inserir tantos "IFs" quantos elementos nas colunas A+B (X+Y). Testei gráficos de dispersão com linhas de A1:B9 e E1:F37 (invertidos para a sequência X/Y correta) e eles geraram exatamente o mesmo formato de curva, então funciona.

Mas não é uma solução eficaz porque requer um processo tedioso, personalizado e manual para cada conjunto de dados. Estou procurando uma maneira de fazer isso de forma mais automatizada com recursos integrados ao Excel, ou pelo menos uma abordagem mais genérica usando fórmulas.

Answer 1

Resposta curta

A interpolação é baseada em uma equação que relaciona os valores de X e Y. Se você conhece a equação real, pode calcular diretamente quaisquer valores intermediários desejados. Caso contrário, você interpola usando uma aproximação. A qualidade da aproximação determina quão precisos serão seus valores intermediários. A interpolação linear será grosseira se você estiver aproximando uma curva com um número limitado de pontos. Existem várias outras abordagens que fornecerão melhores resultados e ferramentas de análise integradas que farão a maior parte do trabalho.

Resposta longa

Você está procurando uma "fórmula geral" ou solução que automatize a interpolação de valores intermediários. Você pode usar interpolação linear para praticamente qualquer dado, mas os resultados serão grosseiros se houver um número limitado de pontos de dados e uma curvatura significativa no formato dos dados. Não existe uma solução "tamanho único" se você deseja precisão. A melhor solução para um determinado conjunto de dados dependerá das características dos dados.

A equação

Não importa como você faça isso, a interpolação é realizada usando uma equação que define a relação entre X e Y. A equação será a real ou uma estimativa. Se for uma estimativa, há uma série de abordagens diferentes que são orientadas pela natureza dos dados e pelo que você precisa realizar.

Na sua outra pergunta, você usou dados baseados na equação Y=2^X. Quando você tiver a equação real, poderá interpolar com exatidão. Escolha um novo valor para Xou Ye a equação fornecerá o outro valor. Se você não conhece a equação real, precisa encontrar uma que a aproxime. Usarei esta resposta para focar nas abordagens de interpolação. Geralmente usam ferramentas de análise integradas que fazem a maior parte do trabalho. Se você precisar de mais detalhes sobre a mecânica de uso de uma ferramenta específica ou de uma abordagem mais automatizada, podemos expandir isso em outra resposta.

Tente encontrar a equação real

A melhor solução é ver se você consegue determinar qual é a equação real. Se você conhece o processo que gerou os dados, isso pode lhe dizer a natureza da equação. Muitos processos, quando sob condições controladas, de forma que você esteja lidando com uma única variável motriz e sem ruído aleatório, seguem uma curva simples para a qual o tipo de equação é conhecido. Portanto, o primeiro passo é observar o formato dos dados e ver se eles são semelhantes a algum deles.

Uma maneira fácil de fazer isso é representar graficamente os dados e adicionar uma linha de tendência. O Excel tem várias curvas comuns disponíveis para tentar ajustar.

Vamos tentar isso com os 2^Ndados da sua outra pergunta. Se você não reconhecesse o padrão numérico e tentasse a abordagem da linha de tendência, veria os ícones de curvas de formatos diferentes. A curva exponencial tem a mesma forma geral, e isso lhe daria isto:

O Excel usa eem vez de 2como base, que é apenas uma tradução (e ^0,693 é 2). Visualmente, você pode ver que a linha de tendência segue exatamente os dados. O R ² também lhe diz isso. R ² é uma medida estatística de quanto da variação nos dados você considera em sua equação. O valor 1significa que a equação representa 100% da variação ou um ajuste perfeito.

O exemplo nesta questão também tem uma forma exponencial. Se você tentar a mesma abordagem, obterá este resultado:

Portanto, esses dados não são exponenciais. Podemos tentar um polinômio, que descreve alguns processos naturais e é capaz de imitar uma variedade de curvas (falarei mais sobre isso mais tarde):

Como uma aproximação do processo por trás dos dados, não é uma boa opção. Na terceira ordem (uma equação contendo potências de X até X^3), ela tem mais pontos de inflexão importantes do que os dados e ainda assim não corresponde. Portanto, a equação subjacente não parece uma curva simples e comum, o que significa que a equação precisará ser aproximada.

Interpolação linear

Esta é a abordagem que você descreve em seus comentários. É direto, usa uma fórmula simples e bastante fácil de automatizar. Pode ser adequado se você tiver muitos pontos e as linhas retas entre eles estiverem suficientemente próximas. Em muitas curvas, segmentos curtos de algumas áreas estarão próximos de linhas retas. No entanto, é uma aproximação ruim para uma linha curva e seus resultados serão imprecisos em áreas com curvatura significativa. No seu exemplo, a área entre os valores X de 7 e 8 teria muita curvatura. Nesta área, uma linha reta comparada com a curva real ficaria assim:

Você está procurando uma solução geral que se aplique a todos os dados. Você pode achar que a interpolação linear é muito grosseira para alguns dados.

Regressão

As pessoas sugeriram a regressão como abordagem, aqui e em outros posts. Isso pode ser feito usando linhas de tendência ou suas funções de planilha subjacentes, ou as ferramentas de análise (acho que isso pode estar no Analysis Toolkit, o que pode exigir o carregamento dessa opção no Excel, ela pode não ser carregada por padrão).

A regressão tenta ajustar uma curva aos seus dados com o objetivo de minimizar o erro total entre os dados e a curva. Em seu uso normal, não é a ferramenta certa para esta tarefa (é o método usado para ajustar as linhas de tendência, e você viu como isso se compara ao que você precisa).

• Destina-se a situações em que seu objetivo é modelar o processo por trás dos dados. Os dados são considerados imprecisos e a regressão sugere o que realmente deveria ser. A curva encontrada pela regressão pode não passar por nenhum dos pontos de dados reais. No seu caso, os dados são fornecidos e considerados precisos. A curva deve passar por todos os pontos.

• A regressão tenta ajustar uma única equação a todos os dados. Não será eficaz se o processo que criou os dados não for descrito pelos tipos de equações disponíveis para serem testadas. Com muitos pontos de dados, a interpolação linear de cada segmento pode ser uma aproximação melhor do que uma curva de regressão para todos os dados.

No entanto, em vez de empregá-la da maneira usual, a regressão pode ser “abusada” como uma solução alternativa para o que você deseja, e geralmente funcionará. Quando você está tentando modelar um processo, geralmente valoriza-se a fórmula mais simples (navalha de Occam). Por outro lado, com uma equação bastante complexa, você pode ajustar qualquer coisa. Você sempre pode desenhar um rabisco que passará por cada ponto. Com Npontos, você pode encontrar uma N-1equação polinomial de ordem que passará por todos os pontos (pior cenário).

Digo “geralmente” porque, em alguns casos, é uma frase bastante tortuosa que seria inútil para o seu propósito. E observe que essa abordagem realmente não “modela” nada no sentido de que a equação resultante preveria o comportamento fora do intervalo dos dados.

Aqui está uma análise de seus dados usando regressão polinomial com equações de ordem superior sucessivamente (a primeira captura de tela inclui as ordens 3 a 5):

(Clique na imagem para obter um tamanho legível.) Observe que a ferramenta de análise inclui o tipo de interpolação que você deseja fazer; gerou os valores intermediários. Para cada análise, os a(n)valores são os coeficientes da equação encontrada. a(0)é uma constante, a(1)é o coeficiente para o termo X ^ 1, etc. Mostra o valor R ² do ajuste. Ele precisa ser virtual 1para estar próximo o suficiente para sua finalidade.

Destaquei os valores dos dados originais com as maiores diferenças. Nessa faixa de pedidos, o ajuste fica um pouco melhor a cada pedido sucessivo, mas os pontos específicos descritos com mais precisão podem mudar. Aqui está um gráfico desses três:

Quando chegamos ao polinômio de 6ª e 7ª ordem, fica assim:

Se optássemos por um polinômio de 8ª ordem para seus 9 valores, seria perfeito, mas a 7ª ordem provavelmente está próxima o suficiente. Para ter uma perspectiva, observe que a equação de 7ª ordem tem um R ² de 0,99999 e ainda não é perfeita.

Usar a ferramenta de análise de regressão para encontrar um ajuste adequado (neste caso, a equação de 7ª ou 8ª ordem) produziria os valores intermediários desejados. Mas é uma boa ideia traçar o resultado e observar a curva para garantir que não seja um rabisco.

Splines

Se você traçar seus dados e selecionar a opção de linhas suaves, o que o Excel usa para produzir isso são splines. Na verdade, quase todas as aplicações de computação gráfica (incluindo definições de fontes) são baseadas em splines para curvas suaves e transições de curvas. Seu nome vem da regra flexível que os desenhistas costumavam usar para conectar pontos arbitrários a uma curva.

Splines criam a curva para cada seção, uma seção por vez, considerando os pontos adjacentes. A curva passa por cada ponto e não há mudanças abruptas em nenhum dos lados do ponto, como ocorre ao conectar os pontos com linhas retas.

As equações usadas para splines não tentam modelar o processo que produziu os dados; é estritamente para parecer bonito. No entanto, a maioria dos processos segue algum tipo de curva contínua e suave. Quando você lida com um único segmento de curva, muitas equações diferentes que produzem curvas de formato geralmente semelhante produzirão valores muito semelhantes dentro do segmento. Portanto, na maioria dos casos, os splines produzirão uma boa aproximação para o que você deseja (e passa naturalmente por todos os pontos, ao contrário da regressão, que deve ser forçada em cada ponto).

Novamente, digo "a maioria dos casos". Splines funcionam muito bem para dados bastante uniformes e regulares e seguem as "regras" para uma curva. Ele pode fazer coisas inesperadas com dados incomuns. Por exemplo, umpergunta SU anteriorfoi sobre essa estranha "queda" negativa no gráfico que o Excel produziu dos dados:

Splines são um pouco como gelatina. Imagine um grande pedaço de gelatina e você restringe pontos específicos onde deseja. O resto da gelatina ficará saliente nos lugares necessários. Uma equação pode definir certos tipos de curvas. Se você forçar a curva através de pontos específicos, acontece a mesma coisa. Com splines, o efeito é limitado a uma protuberância estranha ou a um segmento de curva de aparência não natural; equações de regressão de ordem superior podem seguir um caminho selvagem.

É assim que os splines representam a curva dos seus dados:

Se você comparar isso com as curvas de regressão de ordem superior, os splines são mais “responsivos” às variações locais.

Fiz essa análise usando o LibreOffice Calc, que possui um suplemento de análise que inclui splines. Como você pode ver, isso também produz para splines os resultados interpolados que você está procurando. Não tenho acesso imediato ao Analysis Toolkit do Excel, então não sei se o Excel inclui splines. Caso contrário, o LO Calc será executado no Windows e é gratuito.

Conclusão

Isto abrange as abordagens que você pode usar para interpolar os valores intermediários. Pode ser que abordagens diferentes funcionem melhor com dados diferentes. Ou seus requisitos podem ser aproximados, rápidos e fáceis. Decida que tipo de interpolação você precisa. Se precisar de mais detalhes sobre como fazer isso, podemos abordar a mecânica em outra resposta.

Answer 2

Lendo seus comentários e revisões da pergunta, há algumas coisas que você deseja fazer que não foram abordadas na minha resposta anterior. Esta resposta tratará desses itens e incluí um passo a passo de como você realizaria todo o processo de interpolação.

Dados imprecisos

Você descreve o processo que gerou os dados como fazendo leituras em um intervalo de tempo e os números são arredondados. A equação é tão boa quanto os dados. Em sua análise real, você deve usar os números mais precisos disponíveis (talvez você estivesse apenas mantendo seu exemplo simples, mostrando tempos arredondados).

No entanto, os dados que você mostra não se ajustam precisamente ao tipo de curva que você normalmente vê em um processo físico. As curvas teóricas são geralmente suaves quando há apenas uma variável de condução e nenhum ruído. Se você estiver usando um equipamento muito preciso para acionar uma leitura em um intervalo predefinido e para fornecer uma medição precisa, poderá aceitar os resultados como precisos. No entanto, se você cronometrar manualmente a leitura e fizer a leitura manualmente, os Xvalores poderão estar em momentos imprecisos, mesmo que as próprias leituras sejam precisas. Mudar um pouco os valores individuais Xpara um lado ou para outro introduzirá os tipos de pequenas irregularidades que você vê na curva de seus dados (a menos que o exemplo seja apenas números que você inventou para fins de exemplo).

Se for esse o caso, você pode se beneficiar do uso da regressão para estimar o melhor ajuste.

Usando Y como X

No seu problema, você deseja definir valores para Y(valores inteiros de 1 a 37 neste exemplo) e encontrar os valores X associados. Isso foi fácil de fazer no seu Y=2^Xproblema porque essa equação simples pode ser facilmente revertida para X=log(Y)/log(2)e você pode calcular diretamente qualquer valor que desejar. Se a equação não for algo simples, muitas vezes não existe uma maneira prática de invertê-la. A abordagem de regressão "abusada" na minha resposta anterior fornece uma equação de ordem superior, mas é "unidirecional", muitas vezes não prática para resolver a equação reversa.

A abordagem mais simples é apenas reverter Xe Ydesde o início. Isso fornece uma equação que você pode usar com os valores inteiros introduzidos (a análise fornece os coeficientes da equação conforme descrito na resposta anterior).

Nunca é demais ver se uma curva simples funcionará. Aqui estão os dados invertidos e você pode ver que não há um ajuste útil:

Então, tente um ajuste polinomial. No entanto, este é um caso como o que descrevi na resposta anterior. Os valores de 1 a 8 se ajustam bem, mas 9 causa indigestão. Um polinômio de 3ª ordem dá um solavanco:

Fica progressivamente mais “interessante” à medida que a ordem da equação aumenta. Na 7ª ordem, você obtém isto:

Passa quase exatamente por todos os pontos, mas a curva entre 8 e 9 não é útil. Uma solução seria contentar-se com a interpolação linear entre 8 e 9. Neste caso, porém, você poderia obter valores melhores incorporando splines para a extremidade superior. A opção splines oferece um ajuste bonito e uma curva que faz mais sentido entre 8 e 9:

Infelizmente, as equações spline são um pouco complicadas e as equações não são fornecidas. No entanto, você poderia fazer a interpolação linear nos valores intermediários fornecidos pela análise, o que deveria deixá-lo muito próximo de números que se ajustassem a uma curva razoável.

Extrapolação vs. Interpolação

Neste exemplo, seu primeiro Yvalor é 2,9. Você deseja produzir valores para 1e 2que estão fora do intervalo dos dados. Isso requer extrapolação em vez de interpolação, o que é um requisito muito diferente.

• Se a equação for conhecida, como no seu Y=2^Xexemplo, você poderá calcular qualquer valor que desejar.

• Se o processo que gera os dados segue uma curva simples e você está confiante no ajuste, você pode projetar valores fora do intervalo de dados e até mesmo obter um intervalo de confiança significativo para o intervalo em que os valores poderiam realmente estar (com base em quanta variação existe entre os dados e a curva dentro do intervalo dos dados).

• Se você estiver forçando o ajuste de uma equação de ordem superior aos dados, as projeções fora do intervalo dos dados geralmente não terão sentido.

• Se você estiver usando splines, não haverá base para projetar fora do intervalo de dados.

Quaisquer projeções que você faça fora do intervalo de seus dados serão tão boas quanto a equação que você usa e, se você não estiver usando uma equação exata, quanto mais longe você se afastar dos dados, mais imprecisos eles serão.

Olhando para a curva logarítmica no primeiro gráfico, você pode ver que ela projetaria um valor muito diferente do esperado.

Para as equações polinomiais, o coeficiente de potência zero é uma constante e esse é o valor que seria produzido para um Xvalor de 0. Essa é uma maneira simples de ver onde a curva iria nessa direção.

Observe que na 4ª ou 5ª ordem, os pontos de 1 a 8 são bastante precisos. Mas quando você sai do intervalo, as equações podem se comportar de maneira muito diferente.

Extrapolação usando dados limitados

Uma maneira de melhorar as coisas é ajustar apenas os pontos nessa extremidade e incluir tantos pontos sucessivos quantos seguirem o formato da curva nessa extremidade. O ponto 9 está obviamente fora de questão. Existem várias inflexões na curva antes disso, uma delas em torno do ponto 5 ou 6, portanto, os pontos mais altos seguem uma curva diferente. Usando apenas os pontos de 1 a 5, você chega perto de um ajuste perfeito com um polinômio de 3ª ordem. Essa equação projetaria um ponto zero de 0,12095 (compare com a tabela acima) e para um valor Xde 1,.0.3493

O que acontece se você apenas ajustar uma linha reta aos primeiros cinco pontos:

Isso projeta um ponto zero de -0,5138 e para um Xde 1,.-0.0071

Essa gama de resultados possíveis indica o nível de incerteza fora do intervalo dos seus dados. Não há resposta certa. E isso estava no final “bem comportado” da sua curva. O Yvalor para um Xde 9é 36.7. Você quer ir para 37. Os splines sugerem que a curva é assintótica em 9. Projetar uma linha reta nos dados brutos produziria um valor um pouco maior que 9(o mesmo com um polinômio de 4ª ordem). Um polinômio de 3ª ordem sugere um valor menor que 9(assim como a 5ª e a 6ª ordens). Um polinômio de 7ª ordem sugere um valor substancialmente acima 9. Portanto, qualquer coisa fora do intervalo de dados é uma suposição ou qualquer coisa que você queira que seja.

Juntando tudo

Então, vamos ver como seria a solução real. Assumiremos que você já tentou encontrar uma equação exata e testou curvas comuns usando uma linha de tendência. O próximo passo seria tentar a regressão porque isso fornece a fórmula da curva e você pode inserir seus valores inteiros.

Não tenho acesso imediato ao Excel 2013 ou ao Analysis Toolkit. Usarei o LibreOffice Calc para ilustrar isso. Não é idêntico, mas é próximo o suficiente para que você possa segui-lo no Excel. No LO Calc, esta é na verdade uma extensão gratuita que precisa ser carregada. estou a usarCorelPolyGUI, que pode ser baixadoaqui. Minha lembrança do Analysis Toolkit é que ele não incluía splines. Se ainda for o caso e você quiser fazer isso no Excel, me depareieste suplemento gratuito(que eu não testei). Uma alternativa seria usar o LO Calc, que roda em Windows e é gratuito.

Aqui, inseri os valores X e Y (invertidos) nas colunas A e B e abri a caixa de diálogo de análise. Destacar os valores X e clicar no botão X carrega os intervalos de dados e selecionei polinômio.

Na próxima aba, especifico que quero usar 0em 7graus (um polinômio de 7ª ordem com todas as ordens).

Para especificar a saída, seleciono C1 e clico em Colunas, e ele registra as colunas necessárias para a saída. Eu seleciono que desejo que ele produza os dados originais, os resultados calculados, e selecionei que ele adicione três pontos intermediários entre cada ponto de dados original. E eu digo que quero um gráfico dos resultados em um novo gráfico. Em seguida, vá ao menu calcular e clique em calcular.

E aí está. Se você observar os valores calculados, poderá notar um problema. Isso ficará aparente na próxima etapa.

Aqui, adicionei os valores 1diretos 37. Neste ponto, queremos lidar apenas com interpolação, então adicionei uma fórmula para calcular apenas os valores 3por meio de 36. A fórmula apenas expande os coeficientes listados nos resultados (os valores a(n)). A fórmula em 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

Este é apenas cada coeficiente multiplicado pela potência associada do valor X. Arraste para baixo e você terá seus resultados. Bem, não exatamente; você tem que olhar para ele para ver se passa no teste de sanidade. Sabíamos que havia um problema entre 8e 9, mas isso é metade dos valores que você deseja. Poderíamos usar os valores de 3through 20, mas não faz sentido combinar tantos valores de outro método. Então vamos usar splines para tudo isso.

Abra a caixa de diálogo de análise novamente e altere o método para "splines" na guia de entrada (não mostrado aqui). Dê a ele um novo intervalo de saída e peça para calcular. Isso é tudo que é preciso.

Temos novos resultados para trabalhar. Dividir o intervalo de dados em tantos segmentos mantém cada segmento curto, portanto a interpolação linear deve ser muito boa (muito melhor do que usá-la nos dados originais).

O processo de ajuste ou interpolação de curva envolve a criação de pontos de dados; usando seu próprio julgamento sobre como a curva "deveria" (ou não deveria) parecer (a regressão pressupõe que mesmo os dados originais são imprecisos).

Dar uma verificação de sanidade a esses dados mostra que mesmo splines produzem uma curva de conexão com uma protuberância; um valor ultrapassa um pouco 9, o que provavelmente é um artefato, e não um reflexo do processo que você estava medindo. Nesse caso, uma curva assintótica 9é mais provável, então atribuí arbitrariamente ao ponto alto um valor que é um fio de cabelo menor do que 9ao observá-lo. A suposição não é que meu valor seja preciso, apenas que seja uma melhoria. Para esta ilustração, criei uma nova coluna com os valores que serão utilizados.

Adicionei uma coluna com seus números 1até 37. Da discussão anterior, não temos uma base confiável para projetar valores para 1e 2, então deixei-os em branco. Pois 37, optei pela suposição assintótica e a fiz 9. Os valores de 3through 36são encontrados por interpolação linear (e é uma fórmula que você pode adaptar a outros dados). A fórmula no 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)

A função TREND apenas interpola quando o intervalo é de dois pontos. A sintaxe é:

TREND(Y_range, X_range, X_value)  

A função OFFSET é usada para cada faixa. Em cada caso, ele usa a função MATCH para encontrar a primeira linha do intervalo que contém o valor alvo. Os -1valores são porque são deslocamentos e não locais; uma correspondência na primeira linha é um deslocamento 0da linha de referência. E observe que a Ycoluna é deslocada por 2, neste caso, porque adicionei uma coluna extra para ajustar manualmente um valor. Os parâmetros OFFSET escolhem a coluna que contém os valores Y ou X e selecionam uma altura de intervalo de 2, que fornece os valores abaixo e acima do alvo.

O resultado:

O assistente de análise faz o trabalho pesado e, quer você esteja usando regressão polinomial ou splines, é necessária apenas uma fórmula para gerar o resultado.