Por que os números do RANDBETWEEN do Excel são alterados para qualquer operação na planilha?

14

Eu preciso usar a função RANDBETWEEN do Microsoft Excel e estou ciente de que um novo conjunto de números aleatórios é gerado sempre que você fizer qualquer outra coisa em qualquer lugar da planilha. Também estou ciente das soluções alternativas para "congelar" um conjunto de números aleatórios que foram gerados, por exemplo, para classificar n listas de números aleatórios entre a e b. Minha pergunta é: por que os números aleatórios gerados mudam? Existe algo no algoritmo que exige isso? FWIW, Google Sheets, LibreOffice Calc e Apple Numbers apresentam o mesmo comportamento.

    
por nowradioguy 25.09.2017 / 05:09

5 respostas

28

Charles Williams tem uma boa explicação de como o Excel calcula as coisas e por quê.

link

Em essência, se uma pasta de trabalho contiver funções voláteis (consulte a lista de Charles, pois o que é uma função volátil mudou ao longo dos anos com diferentes versões do Excel), um recálculo de pasta de trabalho inteiro será acionado quando qualquer célula na pasta de trabalho for alterada (se o Excel estiver configurado para cálculo automático). Se não houver funções voláteis na pasta de trabalho, somente as células afetadas pela última alteração serão recalculadas.

Outros aqui afirmaram que o cálculo automático sempre calculará tudo na pasta de trabalho quando qualquer célula for alterada, mas isso está errado. Somente funções voláteis causam o recálculo automático de todas as células na pasta de trabalho. Sem funções voláteis, o Excel recalcula apenas o que precisa ser recalculado.

É por isso que pessoas como eu, que sabem a diferença, recomendam strongmente evitar funções voláteis como OFFSET () ou INDIRECT () nas células da planilha se possível, uma vez que o whey diminuirá a pasta de trabalho causando um recálculo completo após cada célula mudança. Aprender a usar INDEX () ao invés de OFFSET () pode resultar em melhorias dramáticas de velocidade, porque o Index não é volátil (veja o artigo de Charles para mais detalhes).

O mecanismo de cálculo do Excel é baseado na filosofia "recalc ou die" que diferencia o Excel dos produtos concorrentes quando estava em desenvolvimento. Confira este artigo:

    
por 25.09.2017 / 07:16
12

Outras respostas enfocam a mecânica do recálculo e o papel das funções voláteis, mas acho que falta parte da essência da questão. Ele aborda as regras que foram empregadas, mas não tanto o "porquê". Vou me concentrar nisso.

Conceito de design

Deixe-me começar com um conceito de design e os blocos de construção das planilhas mais modernas. Existe uma "divisão de responsabilidade". Funções são rotinas dedicadas que executam uma tarefa específica e fazem isso toda vez que são solicitadas. Separado disso, o aplicativo de planilha eletrônica controla quando eles são perguntados.

As funções, por si só, não contêm nenhuma lógica para escolher recalcular ou não recalcular. A natureza de todas as funções do Excel é executar algum tipo de operação sempre que for acionado para isso. Nenhuma função possui sua própria "memória" de seu valor atual ou um histórico de seus cálculos anteriores. Não tem como saber se esta é a primeira vez que lhe é pedido que faça o seu trabalho. Portanto, nenhuma função nativa tem a capacidade de executar apenas uma vez. Se alguma função for recalculada, ela fará o que foi projetado e produzirá um novo valor.

O aplicativo de planilha contém algumas informações que permitem economizar tempo ignorando recálculos desnecessários (conforme descrito na resposta do teylyn). O recálculo de tempo de somente é ignorado é se o aplicativo souber que o valor da função não foi afetado pelas alterações da planilha que acionaram a necessidade de recalcular as coisas.

E se você precisar que uma função seja executada uma vez e depois preservar seu valor? Tome o seu exemplo de produzir um número aleatório e, em seguida, não ter o resultado alterado. Isso descreve a criação de constantes geradas aleatoriamente e você pode fazer isso com o Excel.

O Excel não sabe como você deseja usar seus recursos; Se você deseja continuar produzindo novos valores ou preservar o último conjunto. Cada opção é um caso de uso que o Excel suporta. O Excel acomoda os dois casos fornecendo as ferramentas para calcular novos valores e as ferramentas para preservar valores antigos. Você pode construir o que quiser. Mas o usuário tem a responsabilidade de fazer o que quiser.

Lógica de funções

Então, vamos ver a lógica do que a função faz. Pode fazer sentido para a lógica de recálculo ignorar uma função aleatória porque seu valor não deve ser afetado por outras alterações na planilha?

O fato de o valor de uma função ser ou não alterado no recálculo depende de sua finalidade e do que é baseado. Um cálculo em valores constantes sempre produzirá o mesmo resultado. Uma operação baseada em valores de células que não foram alterados produzirá o mesmo resultado.

No entanto, algumas funções são projetadas com a intenção e propósito de produzir um resultado diferente a cada vez. Considere, por exemplo, funções baseadas na hora atual. Eles produzirão um novo resultado com base no tempo de recálculo. Você não pode ter uma função cujo propósito seja produzir um valor baseado na hora atual e não atualizar quando recalculado.

O objetivo do controle de recálculo é sempre fazer com que tudo reflita o estado atual das coisas quando o recálculo é acionado. Esse critério não seria cumprido se as funções baseadas na hora atual não fossem atualizadas.

As funções "aleatórias", como RANDBETWEEN, têm o propósito de produzir um novo número aleatório quando recalculadas. É isso que eles pretendem fazer. Você poderia argumentar que o recálculo poderia ser ignorado porque o valor não deveria ser afetado por outras coisas que acontecem na planilha.

Se esse fosse o comportamento padrão, você estaria atualizando a planilha, mas o valor aleatório permaneceria constante. Isso não é um comportamento muito aleatório. Isso apoiaria um caso de uso, mas não o outro. Voltando ao conceito básico de design, ele é tratado como qualquer outra função. O comportamento padrão é fazer o Excel recalculá-lo. Se você quiser preservar o valor anterior para o seu caso de uso, cabe a você fazer isso com as ferramentas disponíveis.

    
por 25.09.2017 / 08:45
2

Cada alteração na planilha lança recálculo automático de todas as fórmulas, que é o comportamento padrão.

Você pode desativá-lo ou congelar o valor real convertendo-o em valor puro (como o oposto de uma fórmula).

    
por 25.09.2017 / 05:24
1

É classificado como uma fórmula "volátil" e, como tal, faz parte da lista de funções recalculadas quando uma alteração é detectada ou uma nova função é inserida.

Outra opção a considerar é mudar a planilha para o cálculo manual para que você controle quando isso acontecer - apenas lembre-se de recalcular, já que qualquer alteração nos valores ou nas fórmulas não causará um recálculo ...

    
por 25.09.2017 / 06:02
0
Apesar dos esforços heróicos para fazer soar como um recurso e não um bug, acho extremamente improvável que esse comportamento seja rotineiramente vantajoso. "Recursos" tornam nossas vidas mais fáceis, "bugs" tornam isso mais difícil. No meu caso, inserir um rótulo ou explicação em uma célula que não é referenciada por qualquer outra célula causa uma nova randomização que é inconveniente de lidar. Eu chamaria isso de "bug".

Sim, posso controlar o recálculo manualmente (uma dor) ou posso colar valores para evitá-lo (novamente, uma dor), mas em que casos a re-aleatorização causada pela entrada de texto em uma região remota da planilha Vida mais fácil?

Mesmo que exista alguma lógica por trás do manuseio de funções "voláteis", parece trivialmente fácil acionar o recálculo por meio de critérios apropriados, além de simplesmente digitar "enter" após a entrada de texto em células não relacionadas e não referenciadas ou por outras entidades completamente não relacionadas. edições. Evitar isso deve ser o padrão e não posso imaginar que seria difícil de implementar. Se alguém quiser esse "recurso", ele pode ativá-lo, mas não consigo imaginar quando isso seria bom. Na melhor das hipóteses, pode ser relativamente inofensivo. Para mim, é um grande incômodo por causa do tipo de folha que estou criando.

Finalmente - O Analysis Toolpak permite criar distribuições de números aleatórios de várias maneiras, e os intervalos de números assim criados não são recalculados (re-randomize), a menos que você diga para fazer isso. Por isso, recomendo que as pessoas usem o Analysis ToolPak quando ele atender às suas necessidades.

    
por 16.11.2017 / 20:04