Na semana passada no Ignite, a equipe do Excel introduziu matrizes dinâmicas. Hoje, um olhar mais atento à função RANDARRAY.
Recentemente, em minha entrada no jogo Excel Hash, criei um modelo para calcular a chance de a Terra ter uma nova atração turística, a Cratera Bennu em 2196. Esse modelo executou trinta milhões de cálculos e exigiu 200.001 fórmulas junto com um tabela de dados de linha. Aqui estão as fórmulas usadas em 200.001 células:
Para simplificar o modelo, você usaria RANDARRAY (100000) em vez da função RAND. Isso fará com que a fórmula seja calculada 100.000 vezes.
-
Você começa substituindo RAND () por RANDARRAY (100000) para gerar 100.000 respostas:
RANDARRAY(100000) -
Envie o RANDARRAY para NORM.INV para calcular 100.000 locais
NORM.INV(RANDARRAY(100000),$H$4,$H$5) -
Envie NORM.INV para VLOOKUP para determinar se Bennu afeta a Terra:
VLOOKUP(NORM.INV(RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE) -
E, por fim, somar os resultados de 100 mil
=SUM(VLOOKUP(NORM.INV(_xlfn.RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE))
O modelo final para executar 100.000 testes está contido em uma única fórmula:
O tamanho do arquivo diminui drasticamente: de 3.270.979 bytes para 37.723 bytes. O tempo de recálculo é reduzido pela metade. Assista aos tempos de Recálculo no vídeo abaixo.
Assistir vídeo
Baixar arquivo Excel
Para baixar o arquivo excel: streamlining-the-bennu-model-with-randarray.xlsm
De agora até o final de 2018, estou tornando meu novo e-book Excel Dynamic Arrays Straight To The Point gratuitamente.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Sempre comece o nome da sua mesa com 'tbl'"
Dietmar Gieringer
