Qual é a maneira mais simples de interpolar e procurar em uma tabela x, y no Excel?

Question

Eu gostaria de fazer uma pesquisa e interpolação com base nos dados x, y da tabela a seguir. Eu gostaria que a equação fosse o mais simples possível para reduzir a quantidade de possíveis erros. A tabela completa tem cerca de 50 linhas x 30 colunas. Eu tenho cerca de 20 dessas tabelas. Aqui está um extrato de um:

   A       B      C    D 
1        0.1    0.2   0.3 
2  2.4   450    300   50
3  2.3   500    375   52
4  2.1   550    475   55
5  1.8   600    600   60

Por exemplo, a equação deve encontrar o valor para x = 2,27 e y = 0,15

    

Answer 1

Você tem um número de tabelas que possuem valores de índice na primeira linha e na primeira coluna de cada tabela e um conjunto de valores no interior da tabela, cada um deles associado a valores de índice de linha e coluna específicos.

Dado dois valores que, respectivamente, podem ser iguais a um índice de linha ou coluna, ou podem estar entre dois valores de índice de linha ou dois valores de índice de coluna, você deseja fazer uma interpolação de linha reta dos valores na tabela com base os dois valores dados para a primeira linha e primeira coluna.

Parafazerainterpolaçãodevaloresdeentradaqueseencontramentreosvaloresdeíndicenatabela,ositensaseguirsãonecessários:

• Onúmerodalinhadomenorvalordeíndiceverticalmaiorque(ouiguala)ovalordeentradaverticalvnum.Comoosvaloresdeíndicenacoluna1databelaestãoemordemdecrescente,issopodeserobtidocom:

  =MATCH(vnum,vrng,-1)

emquevnuméovalordeentradaevrngéointervaloverticaldeíndicesnaprimeiracolunadatabela.Oterceiroargumento-1dafunçãoMATCHindicaqueumapesquisa"menor que" será executada. Esse uso da função MATCH requer que o intervalo vertical de índices esteja em ordem decrescente.

• O maior valor de índice vertical que é menor que (ou igual) o valor de entrada vertical ( vnum ). Isso não pode ser obtido usando a função MATCH porque os valores de índice vertical não são classificados na ordem crescente exigida por MATCH . Em vez disso, a seguinte fórmula de matriz é usada.

  =MIN(IFERROR(1/(vnum>=vrng)*ROW(INDIRECT("1:"&ROWS(vrng))),ROWS(vrng)))

O elemento-chave nessa fórmula é vnum>=vrng , que produz uma matriz booleana na qual o primeiro TRUE está na posição de linha que contém o maior valor de índice vertical que é menor que o valor de entrada. (O uso de ">=" pode parecer contra-intuitivo; é necessário porque os índices na coluna estão em ordem decrescente.) O restante da fórmula converte essa posição de linha em um número de linha.

Esses dois números de linha superior e inferior são usados para calcular os valores de índice que colocam entre colchetes o valor de entrada acima e abaixo e os valores da tabela interna correspondentes a esses valores de índice.

Os números de coluna e os valores de colunas correspondentes para o intervalo de índice horizontal são construídos de maneira semelhante.

Os passos restantes funcionam através da aritmética da interpolação.

Comessesmuitospassos,podeparecerquecalcularosvaloresinterpoladosparaváriosvaloresdeentradaseriaimpraticável.Naverdade,ébemfácilusarumatabeladedadosbidirecionais("e se").

Aconfiguraçãodessescálculosparaváriastabelaspodesersimplificadaaindamaisusandoaversãocomumaúnicafórmuladeles.Parausá-lo,osintervalosnomeadosvnum,hnum,vrng,hrng,datarng,validvnumevalidhnumprecisariamserconfigurados.Astabelasprecisamestaremfolhasseparadasouempastasdetrabalhoseparadas.Seemfolhasseparadas,osnomesdecadafolhadevemserdefinidosparateroescopodaplanilha.

Afórmuladecálculodeetapaúnicaseriaentãoinseridanacélulasuperioresquerdadatabeladedados.Estafórmulade2.100(menosum)-característicaestáincluídanaplanilhaparadownload.

Umaplanilhacontendoesseconjuntodecálculospodeserbaixadausando este link .

    

Answer 2

Na verdade, você tem uma tabela de 3-d - x e y são variáveis independentes, e a que você precisa encontrar é z .

Eu conheço uma solução para a tabela 2-d, mas para 3-d ela deve ser semelhante.

Portanto, se você tiver uma tabela de 2-d, poderá descobrir qual fórmula se ajusta melhor aos seus dados, usando o recurso "linha de tendência" do Excel. Usando essa fórmula, você pode calcular y para qualquer x

1. Crie dispersão linear (XY) para ele (Insert = > Scatter);
2. Criar linha de tendência Polynominal ou Moving Average, marque "Exibir equação na gráfico "(clique com o botão direito na série = > Adicione a linha de tendência);
3. Copie a equação para a célula e substitua x pelo seu valor x desejado

Na captura de tela abaixo A12: A16 contém x , B12: B16 detém y e C12 contém fórmula que calcula y para qualquer x .

Quanto ao seu caso (dados tridimensionais), o Excel também tem capacidade de criar gráficos em 3-d, mas não sei se eles têm tendências em 3D.

    

Answer 3

Use a função Index and Match do Excel em combinação para alcançar seu resultado. Usando os dados acima foram colocados exatamente no excel e derivou a seguinte fórmula. A fórmula é inserida na célula D8.

=INDEX($B$3:$D$6,MATCH(E2,$A$3:$A$6,-1),MATCH(F2,$B$2:$D$2,-1))

    

Answer 4

Aqui está a fórmula que você está procurando: Dados dois pontos (x1, y1) e (x2, y2), para interpolar entre eles, use a fórmula:

y = (y2-y1) / (x2-x1) * (x-x1) + y1

em que x é a entrada e y é a saída. Dada a seguinte tabela

A fórmula é: = SE (ISERROR (MATCH (O3, R $ 4: R $ 10)), S $ 4, SE (ISERROR (ÍNDICE (R $ 4: R $ 10, MATCH (O $ 3, R $ 4: R $ 10) +1)), S $ 10 , (ÍNDICE (R $ 4: S $ 10, MATCH (O3, R $ 4: R $ 10) +1,2) -INDEX (R $ 4: S $ 10, MATCH (O3, R $ 4: R $ 10), 2)) / ( ÍNDICE (R $ 4: S $ 10, PARTIDA (O3, R $ 4: R $ 10) +1,1) -INDEX (R $ 4: S $ 10, PARTIDA (O3, R $ 4: R $ 10), 1)) * (O3- ÍNDICE (R $ 4: S $ 10, MATCH (O3, R $ 4: R $ 10), 1)) + ÍNDICE (R $ 4: S $ 10, MATCH (O3, R $ 4: R $ 10), 2)))

As funções iferror manipulam os pontos finais. Eles mantêm o valor no ponto final em vez de extrapolar.

    

Answer 5

É resolvido com três funções combinadas; Previsão, Deslocamento e Correspondência.

=FORECAST($B$9,OFFSET(B3:D3,0,MATCH($B$9,$B$2:$D$2,1)-1,1,2),OFFSET($B$2:$D$2,0,MATCH($B$9,$B$2:$D$2,1)-1,1,2))

=FORECAST(B10,OFFSET(E3:E6,MATCH(B10,A3:A6,-1)-1,0,2),OFFSET(A3:A6,MATCH(B10,A3:A6,-1)-1,0,2))

    

Answer 6

Eu escrevi código VBA para interpolação bilinear que você pode encontrar aqui: link

A vantagem deste método é que ele é uma única função que facilita a interpolação de uma grade de valores, como mostrado nesta imagem (note que as células são coloridas de acordo com os valores para melhor visualização do efeito da interpolação):

    

Answer 7

A melhor resposta para esse problema é encontrada neste vídeo link

A fórmula final é

=FORECAST(C3,OFFSET(interpol_array,MATCH($C3,lookup_array,1)-1,0,2),OFFSET(lookup_array,MATCH($C3,lookup_array,1)-1,0,2))

Onde o "interpol_array" é definido como a coluna da qual você deseja interpolar os valores e "lookup_array" é a coluna que você procura pelos valores.

Esta resposta também indica a mesma coisa link