Distribuição lognormal no Excel: LOGNORM.INV, parâmetros e quando usar
Resumindo: um valor que costuma ser moderado, mas de vez em quando sai várias vezes maior, como o custo de um reparo. Nunca fica abaixo de zero.
Uma distribuição lognormal descreve uma grandeza que não pode ficar abaixo de zero e tem cauda superior longa, como um custo, uma perda ou uma duração; o logaritmo dela tem distribuição normal. No Excel, =LOGNORM.INV(RAND(), LN(median), s) sorteia valores dela. O custo de reparo de um sinistro, com mediana de 2.000 (US$) e s = 0,8, tem média de 2.754, acima da mediana, e P90 de 5.576, que é 2,8 vezes a mediana.
O que é a distribuição lognormal
Onde a distribuição normal soma efeitos pequenos e independentes, a lognormal os multiplica: um reparo que se mostra 20% mais complexo, depois exige uma peça 30% mais cara e leva 10% mais tempo. Os valores são sempre positivos, a maioria se concentra na faixa baixa e uma cauda fina se estende bem à direita: alguns sinistros custam várias vezes o valor típico.
Como ler o gráfico: cada barra conta quantos dos 10.000 valores simulados caíram naquele trecho, e a linha é a curva teórica. Quanto mais alta a barra, mais vezes os valores caem ali. O trecho longo e baixo à direita são os sinistros raros e caros.
Dois números a definem. A mediana é o valor típico: metade dos resultados fica abaixo dela. A dispersão s define até onde a cauda chega: perto de 0,25, os valores ficam bem próximos da mediana; perto de 1, alguns são várias vezes maiores. (Tecnicamente, s é o desvio padrão do logaritmo dos valores.) A média, o pico e os percentis decorrem desses dois números.
Fórmulas: média, desvio padrão, pico e percentis
| Grandeza | Fórmula e exemplo |
|---|---|
| Média | mediana × es²/2 = 2.000 × e0,32 = 2.754 |
| Desvio padrão | média × √(es² − 1) = 2.608 |
| Pico (moda) | mediana × e−s² = 1.055 |
| P10 e P90 | mediana × e∓1,2816 s = 717 e 5.576 |
A ordem pico < mediana < média é típica de uma grandeza assimétrica à direita. A cauda puxa a média para cima: só 34,5% dos sinistros simulados custam mais que o sinistro médio. Um orçamento feito com a mediana, ou com o custo mais provável, fica abaixo da média.
A distribuição lognormal no Excel
É fácil ler mal o LOGNORM.INV(probability, mean, standard_dev) (INV.LOGNORMAL.N no Excel em português): a média e o desvio padrão dele são os do logaritmo dos valores, não dos valores. Para o sinistro acima, são LN(2.000) = 7,60 e 0,8. Informar a média e o desvio padrão dos próprios custos é o erro mais comum com essa função e dá resultados sem sentido. Por isso a planilha guarda a mediana em uma célula e passa LN(median); em uma célula só, o sorteio é =LOGNORM.INV(RAND(),LN(D6),F6).
| Célula | Fórmula | Valor |
|---|---|---|
| Número aleatório u (I6) | =RAND() | |
| Sorteio (J6) | =LOGNORM.INV(I6, | |
| Sorteio em uma célula | =LOGNORM.INV(RAND(), | |
| Média (K6) | =D6*EXP(F6^2/2) | 2.754 |
| P10 (L6) | =LOGNORM.INV(0.1, | 717 |
| P90 (M6) | =LOGNORM.INV(0.9, | 5.576 |
Conferir os números: valores exatos contra 10.000 sorteios do Excel e do xellstorm
| Exato | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Média | 2.754 | 2.797 | 2.754 |
| Desvio padrão | 2.608 | 2.659 | 2.603 |
| P10 | 717 | 734 | 717 |
| P50 (mediana) | 2.000 | 2.020 | 2.000 |
| P90 | 5.576 | 5.642 | 5.575 |
O Excel sorteia com RAND(), totalmente ao acaso; o xellstorm distribui os sorteios de modo uniforme pela faixa (amostragem por Hipercubo Latino), por isso as estatísticas dele ficam mais perto dos valores exatos.
Se você conhece a média e o desvio padrão dos valores, converta-os antes: s = √(ln(1 + (dp / média)²)) e mediana = média / √(1 + (dp / média)²). Uma média de 3.000 e um desvio padrão de 1.500 dão s = 0,4724 e mediana de 2.683. No Excel: =SQRT(LN(1+(sd/mean)^2)) e =mean/SQRT(1+(sd/mean)^2). LOGNORM.DIST(x, LN(median), s, TRUE) (DIST.LOGNORMAL.N no Excel em português) dá a chance de um valor abaixo de x, e o antigo LOGINV funciona como o LOGNORM.INV.
Quando usar a distribuição lognormal
- Custos, perdas e valores de sinistros que não podem ser negativos e às vezes chegam a várias vezes o valor típico.
- Durações e prazos de entrega: um serviço raramente termina muito mais rápido que o normal, mas pode levar bem mais tempo, como no exemplo de prazos de entrega, em que uma lognormal é ajustada a entregas passadas.
- Preços e fatores de crescimento que mudam por percentuais: um preço que sobe ou desce por percentuais aleatórios ao longo de muitos períodos acaba lognormal.
Em comparação com as alternativas: a distribuição normal é simétrica e pode ficar negativa; uma distribuição PERT ou triangular tem máximo rígido, o que serve para a faixa de um especialista, mas não para uma grandeza que pode estourar por um múltiplo; a distribuição gama tem o mesmo tipo de forma, com cauda mais fina. Se você tem dados, ajuste a lognormal e a gama e compare.
Como usar a distribuição lognormal no xellstorm
- Transforme a célula em entrada e, na etapa Distribuições, escolha Lognormal.
- Os campos seguem o scipy: s é a dispersão do logaritmo, scale a mediana e loc um deslocamento, 0 a menos que os valores comecem acima de zero. Para o sinistro: s 0,8, scale 2.000, loc 0. As colunas Média e P10 – P90 confirmam o que você digitou.
- Se você pensa em percentis, selecione a entrada e use “A partir de estimativas” no painel lateral: informe um P10, um P50 e um P90 e escolha Lognormal, que encontra s e a mediana por você. Com valores passados, “A partir de dados” ajusta uma lognormal com loc 0 e a compara com outras formas.
“Importar da planilha” converte células =LOGNORM.INV(RAND(), mu, sigma) em entradas lognormais, e também funções de suplementos como RiskLognorm (média e desvio padrão dos valores), RiskLognorm2 (do logaritmo deles), VoseLognormal e PsiLogNormal.
Perguntas
Como gerar números aleatórios lognormais no Excel?
Use =LOGNORM.INV(RAND(), LN(median), s), em que a mediana é o valor típico e s o desvio padrão do logaritmo dele. Para uma mediana de 2.000 e s = 0,8, fica =LOGNORM.INV(RAND(), LN(2000), 0.8). Cada recálculo sorteia um valor novo.
Quais são a média e o desvio padrão no LOGNORM.INV?
São a média e o desvio padrão de ln(x), muitas vezes escritos μ e σ, não dos valores em si. μ é o logaritmo da mediana: LN(2.000) = 7,60. Para partir da média e do desvio padrão dos valores, converta-os: σ = √(ln(1 + (dp / média)²)) e μ = ln(média) − σ² / 2.
Por que a média de uma distribuição lognormal é maior que a mediana?
Porque a cauda se estende bem à direita enquanto os valores não podem cair abaixo de zero: o valor muito grande que aparece de vez em quando puxa a média para cima. A média é a mediana × es²/2, então a diferença cresce com a dispersão: com s = 0,8, uma mediana de 2.000 dá uma média de 2.754.
Lognormal ou normal: qual usar?
Use a distribuição normal para grandezas que variam de forma simétrica em torno de um alvo e ficam longe de zero. Use a lognormal para grandezas positivas cujo lado superior é mais longo que o inferior: custos, perdas, durações, preços. Quando o desvio padrão é pequeno em relação à média (menos de cerca de um décimo dela), as duas são quase idênticas.
Veja também
- Guia
Qual distribuição de probabilidade usar? Guia para modelos de Monte Carlo
Encontre a distribuição de probabilidade certa em duas perguntas: PERT, triangular, normal, lognormal, Weibull, Poisson, binomial, beta e outras, com a finalidade de cada uma e a fórmula dela no Excel. - Guia
Distribuição gama no Excel: tempos de espera, totais e GAMMA.INV
A distribuição gama descreve grandezas positivas e assimétricas à direita, como o tempo total de várias etapas. Forma e escala, GAMMA.INV e GAMMA.DIST no Excel, e gama x lognormal. - Prazos de entrega
Qual é a chance de uma entrega chegar no prazo?
Ajuste distribuições a um pequeno histórico sintético de entregas, compare probabilidades de pontualidade e veja por que mais iterações não compensam uma amostra pequena. Inclui um arquivo do Excel.
O xellstorm é uma ferramenta de simulação de Monte Carlo para modelos do Excel, que roda no navegador: sem suplemento, e o arquivo nunca sai do seu computador.