Questão Como interpolar valores intermediários para dados arbitrários no Excel 2013


Eu 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, Voltage por exemplo, e A representa exatamente cada um dos nove minutos redondos em que a medição foi feita.

Eu quero criar uma segunda tabela, colunas E e F, com uma quantidade de linhas que é o "próximo inteiro" para o maior valor na coluna B. Nesse caso, B9 = 36,7, portanto, terá 37 linhas. A coluna F1: F37 conterá inteiros de 1 a 37, a coluna E deve ter valores numéricos que correspondam a F, na mesma relação entre as colunas A e B. Em outras palavras, interpole 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. Entretanto, F8 = 8, que é um valor intermediário não contido na coluna B. Assim, E8 ficará entre 3 e 4, com base nos dados originais, e devem ser interpolados.

A ideia é ao traçar um gráfico, A1: B9 terá a mesma forma que E1: F37. Neste exemplo, expandirei a tabela de dados para 37 resultados inteiros que teriam ocorrido durante o curso das medições originais e verificaria 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 estão invertidas do que descrevi acima).

  1. Eu 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), iniciando em 1, quanto o próximo valor inteiro do elemento maior em B. Nesse caso, B9 contém 36,7, portanto, 37.
  3. Na F1: F37 eu introduzo 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)))))))))  

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

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


4


origem


Esta é uma duplicata da sua pergunta subsequente. Para interpolar, você precisa decidir sobre o relacionamento entre os valores. Em sua pergunta, um ajuste polinomial de 4a ordem (ou ajuste excessivo) dos dados; na sua pergunta subseqüente, existe uma relação exponencial entre os valores. Depois de decidir sobre o relacionamento, é uma questão de aplicar a fórmula apropriada. Para este conjunto de dados, você deve inserir uma coluna de valores igualmente espaçados nas linhas 1..36 que vão de 1..9, e então usar uma variação do TREND função para calcular os valores Y - Ron Rosenfeld
Para ambas as suas perguntas a atividade desejada é REGRESSÃO, por favor mude o texto de suas perguntas. Não existe uma solução universal para isso, mas você pode achar úteis na Web usando as palavras-chave certas - Máté Juhász
@ MátéJuhász: Sem valores X, também não é regressão. :-) Mas a regressão é na verdade um pouco diferente dos requisitos aqui. Regressão é uma aproximação baseada em todos os dados, que podem não passar por nenhum ponto de dados. O que o OP quer aqui é a interpolação entre os pontos reais. Ele não especifica a natureza dele, de modo que a interpolação linear atende ao requisito, mas a resposta do aluno de Gary sobre a outra pergunta usa a curva exponencial real. - fixer1234
Gentleman, tive a infelicidade de produzir a segunda pergunta, ao escolher um dado exponencial. A maioria das respostas foi colocada sobre o foco. Não deveria. É por isso que estou retornando aqui, com os dados originais: Nove elementos, X = 1 a 9, Y = 2,9, 5,06, 7, 8,84, 10,87, 13,24, 16,22, 20,25, 36. Como o maior elemento Y é 36, Eu gostaria de expandir X para 36 elementos e, obviamente, X1 = 1, X12 = 7, X36 = 36, mas eu preciso preencher os espaços em branco em Y em relação a X. - Wagner Lip
Eu tenho três problemas, primeiro; minha língua nativa não é inglesa, segundo; Eu não tenho um forte conhecimento de matemática e terceiro; parece que não há solução para o meu exercício de tabela do Excel. Eu não estou abandonando as perguntas, estou apenas seguindo a recomendação para editar o primeiro post. Além disso, é possível que eu esteja falhando completamente no uso deste fórum. Obrigado. - Wagner Lip


Respostas:


Resposta curta

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

Resposta longa

Você está procurando por uma "fórmula geral" ou solução que automatiza 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 na forma dos dados. Não existe uma solução "tamanho único" se você quiser 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, existem várias abordagens diferentes que são orientadas pela natureza dos dados e pelo que você precisa realizar.

Na sua outra pergunta, você usou dados com base na equação Y=2^X. Quando você tem a equação real, você pode interpolar exatamente. Escolha um novo valor para X ou Y e a equação fornece o outro valor. Se você não conhece a equação real, precisa encontrar uma que a aproxime. Usarei essa resposta para me concentrar nas abordagens de interpolação. Geralmente, eles usam ferramentas de análise internas que realizam a maior parte do trabalho. Se você precisar de mais detalhes sobre a mecânica de usar uma ferramenta específica ou uma abordagem mais automatizada, podemos expandir isso em outra resposta.

Tente encontrar a equação real

A melhor solução é ver se você pode determinar qual é a equação real. Se você conhece o processo que gerou os dados, isso pode indicar a natureza da equação. Muitos processos, quando sob condições controladas, de modo que você está lidando com uma única variável de acionamento e nenhum ruído aleatório, segue uma curva simples para a qual o tipo de equação é conhecido. Então, um primeiro passo é olhar para a forma dos dados e ver se é semelhante a um 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 encaixar.

trend menu

Vamos tentar isso com o 2^N dados 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 diferentes curvas. A curva exponencial é a mesma forma geral, e isso lhe daria isto:

2^N 

O Excel usa e ao invés de 2 como a base, que é apenas uma tradução (e0,693 é 2). Visualmente, você pode ver que a linha de tendência segue exatamente os dados. O R2 também lhe diz isso. R2 é uma medida estatística de quanto da variação nos dados que você considera com sua equação. O valor que 1 significa que a equação responde por 100% da variação, ou um ajuste perfeito.

O exemplo nesta questão também tem uma espécie de formato exponencial. Se você tentar a mesma abordagem, você obtém este resultado:

exp fit

Então, 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 depois):

poly 3

Como uma aproximação do processo por trás dos dados, não é um ótimo ajuste. Na terceira ordem (uma equação contendo potências de X até X ^ 3), tem mais pontos de inflexão do que os dados e ainda 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 nos seus comentários. É simples, usando uma fórmula simples e bastante fácil de automatizar. Pode ser adequado se você tiver muitos pontos e as linhas retas estiverem próximas o suficiente. 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 qualquer 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:

straight vs curve

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

Regressão

As pessoas sugeriram regressão como uma 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 podem estar no Analysis Toolkit, o que pode exigir o carregamento dessa opção no Excel, pode não ser carregado 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 essa tarefa (é o método usado para ajustar as linhas de tendência, e você viu como isso é comparado ao que você precisa).

  • É destinado 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 suposto 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 dados e assumidos como precisos. A curva deve passar por todos os pontos.

  • 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 teste. Com muitos pontos de dados, a interpolação linear de cada segmento pode ser uma melhor aproximação do que uma curva de regressão para todos os dados.

No entanto, ao invés de empregá-lo da maneira usual, a regressão pode ser "abusada" como uma solução alternativa para o que você quer, e normalmente funcionará. Quando você está tentando modelar um processo, a fórmula mais simples é geralmente avaliada (navalha de Occam). Por outro lado, com uma equação complexa o suficiente, você pode ajustar qualquer coisa. Você sempre pode desenhar um rabisco que passará por todos os pontos. Com N pontos, você pode encontrar um N-1 ordem equação polinomial que passará por todos os pontos (pior cenário).

Eu digo "geralmente" porque em alguns casos, é uma linha bem torturada 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 previa um comportamento fora do intervalo dos dados.

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

poly 3-5

(Clique na imagem para um tamanho legível.) Observe que a ferramenta anaysis inclui o tipo de interpolação que você deseja fazer; gerou os valores intermediários. Para cada análise, o 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 R2 valor do ajuste. Precisa ser virtualmente 1 estar perto o suficiente para o seu propósito.

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

poly 3-5 chart

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

poly 6-7

poly 6-7 chart

Se fôssemos para um polinômio de 8ª ordem para seus 9 valores, seria perfeito, mas a 7ª ordem provavelmente está perto o suficiente. Para perspectiva, observe que a equação de 7ª ordem tem um R2 de 0,9999 e ainda não é perfeito.

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

Splines

Se você traçar seus dados e selecionar a opção para linhas suaves, o que o Excel usa para produzir isso é 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. É nomeado após a regra flexível que os desenhistas usaram uma vez para conectar pontos arbitrários com 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 ambos os lados do ponto, como acontece quando você conecta os pontos com linhas retas.

As equações usadas para splines não tentam modelar o processo que produziu os dados; é estritamente 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 forma geralmente semelhante produzirão valores muito semelhantes dentro do segmento. Portanto, na maioria dos casos, as splines produzirão uma boa aproximação para o que você deseja (e, naturalmente, passa por todos os pontos, ao contrário da regressão, que deve ser forçada em cada ponto).

Mais uma vez, eu digo "a maioria dos casos". Splines funcionam muito bem para dados que são bastante uniformes e regulares, e seguem as "regras" para uma curva. Pode fazer algumas coisas inesperadas com dados incomuns. Por exemplo, um pergunta anterior do SU foi sobre esse estranho "mergulho" negativo no gráfico Excel produzido dos dados:

dip

Splines são um pouco como Jello. Imagine um grande pedaço de Jello e você restringe pontos específicos onde você os quer. O resto da gelatina vai inchar em lugares que precisa. Uma equação pode definir certos tipos de curvas. Se você forçar a curva através de pontos específicos, a mesma coisa acontece. Com splines, o efeito é limitado a um bulbo estranho ou a um segmento curvo de aspecto não natural; equações de regressão de alta ordem podem seguir um caminho selvagem.

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

spline

spline chart

Se você comparar isso com as curvas de regressão de alta ordem, as splines serão mais "responsivas" à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 splines, os resultados interpolados que você está procurando. Eu não tenho acesso pronto ao Analysis Toolkit do Excel, então não sei se o Excel inclui splines. Se não, o LO Calc será executado no Windows e é gratuito.

Linha de fundo

Isso abrange as abordagens que você pode usar para interpolar os valores intermediários. Pode ser que diferentes abordagens funcionem melhor com dados diferentes. Ou, suas necessidades podem ser qualquer coisa aproximada, rápida e fácil. Decida que tipo de interpolação você precisa. Se você precisar de mais detalhes sobre como realizá-lo, podemos abordar a mecânica em outra resposta.


7



Caro Fixer1234, não posso nem começar a agradecer. Eu tenho muito mais do que esperava. Agora eu tenho material para lidar e jogar por um longo tempo, aprendendo e melhorando. Você é um professor perfeito de muitas maneiras. Precisamos de mais pessoas como você no mundo. Obrigado pela sua dedicação, tempo e conhecimento. Wagner - Wagner Lip
@WagnerLip, de nada. Feliz em ajudar. Em algum momento, talvez você esteja fazendo isso por outra pessoa. :-) - fixer1234
Fixer1234, obrigado novamente. Sou um participante muito ativo em um grande fórum eletrônico desde 2002, ajudando o máximo possível. É muito gratificante ajudar as pessoas à procura de conhecimento. Na verdade, esse problema do Excel é desenvolver uma maneira de ajudar as pessoas no fórum a produzir melhores escalas (ângulos e marcas) de botões em equipamentos eletrônicos. - Wagner Lip


Ao ler seus comentários e revisões da pergunta, há algumas coisas que você deseja fazer que não estão realmente cobertas na minha resposta anterior. Essa resposta vai lidar com esses itens, e eu incluí um passo-a-passo passo-a-passo de como você realizaria todo o processo de interpolação.

Dados imprecisos

Você descreve o processo que gerou os dados como 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 horários arredondados).

No entanto, os dados que você mostra não se ajustam exatamente ao tipo de curva que você normalmente vê para um processo físico. Curvas teóricas geralmente são suaves quando há apenas uma variável de direção e nenhum ruído. Se você estiver usando um equipamento muito preciso, tanto para acionar uma leitura em um intervalo predefinido quanto para fornecer uma medida precisa, você pode aceitar os resultados como precisos. No entanto, se você estiver sincronizando manualmente a leitura e fazendo a leitura manualmente, X os valores podem ser imprecisos, mesmo que as leituras sejam precisas. Indivíduo deslocando X valores um pouco de um jeito ou de outro introduzirão os tipos de pequenas irregularidades que você vê na curva de seus dados (a menos que o exemplo seja apenas números que você criou para os propósitos de um exemplo).

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

Usando Y como X

Em seu problema, você deseja definir valores para Y (valores inteiros de 1 a 37 neste exemplo) e encontre os valores X associados. Isso foi fácil o suficiente para fazer em seu Y=2^X problema 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 é algo simples, muitas vezes não há uma maneira prática de invertê-la. A abordagem de regressão "abusada" na minha resposta anterior fornece uma equação de alta ordem, mas é "uma direção", muitas vezes não é prático para resolver a equação inversa.

A abordagem mais simples é apenas inverter X e Y do começo. 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).

Não custa saber se uma curva simples funcionará. Aqui estão os dados invertidos, e você pode ver que não há um ajuste útil:

log

Então, tente um ajuste polinomial. No entanto, este é um caso como descrevi na resposta anterior. Os valores de 1 a 8 se encaixam bem, mas 9 dá indigestão. Um polinómio de 3ª ordem dá-te uma pancada:

poly3

Torna-se progressivamente mais "interessante" à medida que a ordem da equação aumenta. Pela 7ª ordem, você recebe isto:

poly7

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

splines

Infelizmente, as equações spline são um pouco confusas e as equações não são fornecidas. No entanto, você pode fazer a interpolação linear nos valores intermediários fornecidos pela análise, o que deve aproximá-lo muito dos números que se ajustam a uma curva razoável.

Extrapolação vs. Interpolação

Neste exemplo, o seu primeiro Y valor é 2.9. Você quer produzir valores para 1 e 2, que 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 é conhecida, como o seu Y=2^X Por exemplo, você pode calcular qualquer valor desejado.

  • Se o processo que gera os dados seguir uma curva simples e você tiver certeza do ajuste, poderá projetar valores fora do intervalo de dados e até obter um intervalo de confiança significativo para o intervalo que os valores poderiam realmente ser (com base em Quanta variação existe entre os dados e a curva dentro do intervalo de dados).

  • Se você está usando uma equação de alta ordem para os dados, projeções fora do alcance dos dados geralmente não têm sentido.

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

Quaisquer que sejam as projeções que você fizer fora do alcance de seus dados sã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ê conseguir de seus dados, mais impreciso será.

Olhando para a curva de log no primeiro gráfico, você pode ver que projetaria um valor muito diferente do que você esperaria.

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

zero vals

Note que pela 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 é encaixar apenas os pontos nesse ponto e incluir tantos pontos sucessivos quanto a forma da curva naquele ponto. O ponto 9 está obviamente fora. Existem várias inflexões na curva antes disso, uma delas em torno do ponto 5 ou 6, então pontos maiores do que 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 X valor de 1, 0.3493.

O que acontece se você apenas encaixar uma linha reta nos cinco primeiros pontos:

straight

Que projeta um ponto zero de -0,5138 e para um X do 1, -0.0071.

Esse intervalo de possíveis resultados indica o nível de incerteza fora do intervalo de seus dados. Não há resposta certa. E isso foi no final "bem-comportado" de sua curva. o Y valor para um X do 9 é 36.7. Você quer ir para 37. As splines sugerem que a curva é assintótica em 9. Projetar uma linha reta nos dados brutos produziria um valor um pouco maior que 9 (mesmo com um polinômio de 4ª ordem). Um polinômio de 3ª ordem sugere um valor menor que 9 (como as 5ª e 6ª ordens). Um polinômio de 7ª ordem sugere um valor substancialmente acima 9. Portanto, qualquer coisa fora do intervalo de dados é um palpite ou qualquer coisa que você queira que seja.

Colocando tudo junto

Então, vamos analisar como seria a solução real. Vamos supor que você já tenha tentado encontrar uma equação exata e testado curvas comuns usando uma linha de tendência. O próximo passo seria tentar a regressão porque isso fornece a fórmula para a curva e você pode inserir seus valores inteiros.

Eu não tenho acesso imediato ao Excel 2013 ou ao Analysis Toolkit. Vou usar o LibreOffice Calc para ilustrar isso. Não é idêntico, mas está perto o suficiente para que você possa segui-lo no Excel. No LO Calc, esta é realmente uma extensão gratuita que precisa ser carregada. estou a usar CorelPolyGUI, que pode ser baixado Aqui. Minha lembrança do Analysis Toolkit é que ele não inclui splines. Se isso ainda é o caso e você quer fazer isso no Excel, me deparei com este suplemento grátis (que eu não testei). Uma alternativa seria usar o LO Calc, que será executado no Windows e é gratuito.

step 1

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

step 2

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

step 3

Para especificar a saída, seleciono C1 e clico em Colunas e registra as colunas necessárias para a saída. Eu seleciono que eu quero que ele produza os dados originais, os resultados calculados, e selecionei para adicionar 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á para o menu calcular e clique em calcular.

step 4

E aí está. Se você observar os valores calculados, poderá notar um problema. Isso se tornará aparente no próximo passo.

step 5

Aqui, eu adicionei o 1 através 37 valores. Neste momento, queremos apenas lidar com a interpolação, por isso adicionamos uma fórmula para calcular apenas os valores 3 através 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 isso para baixo e você terá seus resultados. Bem não é bem assim; você tem que olhar para ver se passa no teste de sanidade. Sabíamos que havia um problema entre 8 e 9, mas isso acaba sendo metade dos valores que você quer. Nós poderíamos usar os valores de 3 através 20, mas não faz sentido combinar muitos valores de outro método. Então vamos usar splines para a coisa toda.

step 6

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

step 7

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

step 8

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

Ao fornecer esses dados, uma verificação de sanidade mostra que até mesmo as ranhuras produzem uma curva de conexão com uma protuberância; um valor vai ligeiramente 9, o que provavelmente é um artefato em vez de um reflexo do processo que você estava medindo. Neste caso, uma curva assintótica em 9 é mais provável, então eu atribui arbitrariamente ao ponto alto um valor que é um cabelo menor que 9 por eyeballing isso. A suposição não é que meu valor seja preciso, apenas que é uma melhoria. Para esta ilustração, criei uma nova coluna com os valores que serão usados.

Eu adicionei uma coluna com seus números 1 através 37. A partir da discussão anterior, não temos uma base confiável para projetar valores para 1 e 2, então deixei em branco. Para 37, Eu fui com a suposição assintótica e fiz isso 9. Os valores para 3 através 36 sã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 TENDÊNCIA apenas interpola quando o intervalo é de dois pontos. A sintaxe é:

TREND(Y_range, X_range, X_value)  

A função OFFSET é usada para cada intervalo. Em cada caso, ele usa a função MATCH para encontrar a primeira linha do intervalo que contém o valor de destino. o -1 os valores são porque esses são deslocamentos em vez de locais; um jogo na primeira linha é um deslocamento de 0 da linha de referência. E note que o Y coluna é compensada por 2, neste caso, porque eu 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 da meta.

O resultado:

result

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.


3