Codificando uma função do Excel para preencher lacunas de dados

Question

No Excel, tenho atualmente o seguinte gráfico de dispersão e seu conjunto de dados correspondente para uma das séries:

Euestoutentandocriaralgumtipodefunçãoquemepermitainterpolarpontosparaaslacunasdedadosquetenhoatualmente;Oobjetivofinalécriarumacurvasuaveparaográficodedispersão.Conceitualmente,afunçãoqueestoutentandocriarédaseguinteforma:

Dado:

nisaninteger,x=3+20n,y=3+20(n+1)=IF(B(3+20n)<cell<B(3+20(n+1)),add(y-x)/19topreviousvalue,donothing)

Ouseja,tenhopontosdedadosnascolunas23,43,63,...etc.eestoutentandopreencheraslacunas,adicionandoumintervaloexclusivoparacadabinparaacélulaanterior.Esteintervaloéobtidotomandooslimitesinferioresuperiordecadabinedividindopor19.Emoutraspalavras,cadabindeveficarassim:

Eu tenho que fazer isso por cerca de 1000 células, então fazer isso manualmente é tedioso. Alguma orientação sobre como criar tal função que me permitiria executar tudo de uma só vez seria muito apreciada; Além disso, se há uma maneira melhor de fazer isso, qualquer informação seria de grande ajuda.

    

Answer 1

Primeiro, adicione uma coluna extra. Nesta coluna você pode usar esta fórmula:

=IF(MOD((ROW()-3),20)=0,B3,INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+3))+  
(INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+23))-INDIRECT("B"&  
(QUOTIENT((ROW()-3),20)*20+3)))/20*(ROW()-(QUOTIENT((ROW()-3),20)*20+3)))

Como essa fórmula funciona:

MOD(ROW()-3),20)=0

Isto verifica se a linha é 23,43,63, ... Se assim for, pega a célula adjacente, se não a próxima fórmula ocorre:

INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+3))

Isto usa a função indireta para calcular o adres da primeira célula que linha é menor que a linha atual e também 23,43,63. Subtrai 3 da linha atual e divide-a por 20 e arredonda para um inteiro inferior. Em seguida, ele multiplica por 20 e adiciona 3 para obter o número da linha correto. Isso é então concatenado ao B para formar o nível da célula desejada.

+(INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+23))-INDIRECT("B"&  
(QUOTIENT((ROW()-3),20)*20+3)))/20*(ROW()-(QUOTIENT((ROW()-3),20)*20+3))

Esta é simplesmente uma fórmula de interpolação do formulário:

 y_int = y_start + [(y_end-y_start)/(x_end-x_start)]*(x_int-x_start)

y_start é o que eu expliquei acima. Então você tem y_end que é quase o mesmo, apenas você adiciona 23 em vez de 3 para obter o próximo valor. Então você divide por 20, que é a diferença entre os dois valores seguintes. O x_int-x_start é a seguinte fórmula:

ROW()-(QUOTIENT((ROW()-3),20)*20+3)

Isso pega a linha atual e subtrai o 23,43,63 .. que é menor, usando o mesmo método que eu encontrei para y_start.