Minimizando valores de coluna no Microsoft Excel em direção a um ótimo

Question

Na figura abaixo está um gráfico de 144 measurements em Column A mostrado no gráfico.

Eudecidiquequerodeixartodososvaloresmaispróximosde4300,queestãonomeio,paraqueeupossamostraroefeitoantesedepoisdaotimização.Agora,oúnicopropósitoémostrarumexemplovisualenãomatemático.Masentãoeudecidiquedeveriafazê-lonumericamenteusandoSolveroptimizationnoMicrosoftExceleaquicomoeufiz:EmcolumnBiassumiqueé=ABS(SUM(B1;-4300))aplicadoatodoColumnBeemD2éasomatotaldetodososvaloresemColumnB.Agora,meuproblemadeotimizaçãocomoprimeirotestefoiMinimizarD2paraumdeterminadovalor,porexemplo15000ondeoSolvermodificaráosvaloresemColumnAparaalcançaresteobjetivoeondetambémreceberianovosvaloresemColumnAqueestãomaispróximosde4300.

Nasegundafoto,vocêpodeveroresultado.

Meuproblemaestáaqui:emboraeutenhaconseguidominimizarmeusdadosparaumdeterminadovalor,oqueeurealmenteprecisoconsertaréofatodequeexistemalgunsvalores"mais distantes" do que os outros, daí eles não "merecem" seja tão próximo quanto os valores mais próximos de 4300 . Podemos pensar nisso como um fator de penalidade maior para valores distantes e uma penalidade menor para valores mais próximos. Como e onde devo introduzir esse fator de penalidade?

    

Answer 1

Um relacionamento de mapeamento linear move a média dos dados, mas não afeta sua dispersão (ou desvio padrão).

Se você quiser fazer com que os dados sejam movidos de maneira diferente, dependendo de quão longe está da média, é necessário definir um relacionamento de mapeamento.

Por exemplo, você pode usar uma transformação linear: Novo valor = New Mean + (valor antigo - Old Mean) * Factor Quando o fator é 1.0, os dados se movem sem alterar a dispersão. Quando o Fator é menor que 1 (mas maior que 0), os dados se aproximam da média, mas os dados que estavam originalmente mais afastados da média movem-se mais.

Se os dados forem caracterizados por uma distribuição normal (não posso dizer porque você não postou uma pasta de trabalho), primeiro determine a estatística Z, que mede quantos desvios padrão o dado é da média: Z = (valor antigo - média antiga) / desvio padrão antigo

Você pode então usar essa estatística Z para determinar um novo valor (ainda seguindo uma distribuição normal) que tenha uma nova média e um novo desvio padrão. Para realizar esse remapeamento, você usaria o inverso da fórmula anterior: = Nova Média + Z * Novo Desvio Padrão

Ao usar a relação Z de estatística e mapeamento inverso descrita acima, os novos dados mantêm sua distribuição normal, apenas com uma nova média e desvio padrão. Também mantém um link para os dados originais.

Eu usei a estatística Z e o relacionamento de mapeamento inverso para transformar um conjunto de dados inaceitáveis mostrados na captura de tela abaixo.

Osdadostransformadosmostradosnacapturadetelaabaixosimulamcomoelepodeficarcomamédialevantadaeodesviopadrãoreduzido,entãoamaioriadospontosficaentreoslimitesdeespecificação.