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ê faz 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 foi gerado, digamos, para classificar n listas de números aleatórios entre a e b. Minha pergunta é a seguinte: POR QUE os números aleatórios gerados mudam? Existe algo no algoritmo que exige isso? FWIW, Planilhas Google, LibreOffice Calc e Apple Numbers exibem o mesmo comportamento.
Responder1
Charles Williams tem uma boa explicação de como o Excel calcula as coisas e por quê.
http://www.decisionmodels.com/calcsecretsi.htm
Em essência, se uma pasta de trabalho contiver funções voláteis (consulte a lista de Charles, uma vez que o que é uma função volátil mudou ao longo dos anos com diferentes versões do Excel), um recálculo completo da pasta de trabalho será acionado quando qualquer célula da pasta de trabalho for alterada (se o Excel está definido para cálculo automático). Se não houver funções voláteis na pasta de trabalho, apenas 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 da 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, aconselham fortemente evitar funções voláteis como OFFSET() ou INDIRECT() nas células da planilha, se possível, já que o whey irá desacelerar a pasta de trabalho, causando um recálculo completo após cada mudança de célula. Aprender a usar INDEX() em vez de OFFSET() pode resultar em melhorias drásticas de velocidade, porque Index não é volátil (veja o artigo de Charles para mais detalhes).
O mecanismo de cálculo do Excel é baseado na filosofia “recalcular ou morrer” que diferenciou o Excel dos produtos concorrentes quando estava em desenvolvimento. Confira este artigo:https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-defied-the-odds/
Responder2
Outras respostas concentram-se na mecânica do recálculo e no papel das funções voláteis, mas acho que isso perde parte da essência da questão. 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 da maioria das planilhas modernas. Existe uma “divisão de responsabilidades”. Funções são rotinas dedicadas que executam uma tarefa específica e fazem isso sempre que são solicitadas. Além disso, o aplicativo de planilha controla quando solicitado.
As funções em si não contêm nenhuma lógica para escolher recalcular ou não recalcular. A natureza de todas as funções do Excel é realizar algum tipo de operação sempre que acionada 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 há como saber se esta é a primeira vez que lhe pedem para fazer o seu trabalho. Portanto, nenhuma função nativa pode ser executada apenas uma vez. Se qualquer função for recalculada, ela fará o que foi projetada para fazer e produzirá um novo valor.
O aplicativo de planilha contém alguma inteligência que permite economizar tempo, ignorando recálculos desnecessários (conforme descrito na resposta de Teylyn). OapenasO tempo de recálculo ignorado ocorre se o aplicativo souber que o valor da função não foi afetado pelas alterações na planilha que desencadearam a necessidade de recalcular as coisas.
E daí se você precisar executar uma função uma vez e depois preservar seu valor? Veja o seu exemplo de produzir um número aleatório e depois fazer com que o resultado não mude. 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 compatível com o Excel. O Excel acomoda ambos os casos, fornecendo-lhe 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 com que ele faça o que quiser.
Lógica de Função
Então, vamos dar uma olhada na lógica do que a função faz. Poderia fazer sentido para a lógica de recálculo ignorar uma função aleatória porque seu valor não deveria ser afetado por outras alterações na planilha?
Se o valor de uma função muda ou não após o recálculo depende de sua finalidade e em que se baseia. Um cálculo em valores constantes produzirá sempre o mesmo resultado. Uma operação baseada em valores de células que não foram alterados produzirá o mesmo resultado.
Porém, algumas funções são projetadas com a intenção e o propósito de produzir sempre um resultado diferente. 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 objetivo é produzir um valor com base na hora atual e não atualizá-lo quando for recalculado.
O objetivo do controle do recálculo é sempre fazer com que tudo reflita o estado atual das coisas quando o recálculo for acionado. Esse critério não seria atendido se as funções baseadas na hora atual não fossem atualizadas.
As funções "aleatórias", como RANDBETWEEN, têm a finalidade de produzir um novo número aleatório quando recalculado. É 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ê atualizaria a planilha, mas o valor aleatório permaneceria constante. Esse 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 com que o Excel o recalcule. Se quiser preservar o valor anterior para o seu caso de uso, cabe a você fazer isso com as ferramentas disponíveis.
Responder3
Cada mudança na planilha é iniciadarecálculo automáticode 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).
Responder4
Apesar dos esforços heróicos para fazer com que isso pareça um recurso e não um bug, acho extremamente improvável que esse comportamento seja rotineiramente vantajoso. “Recursos” facilitam nossas vidas, “bugs” dificultam. No meu caso, inserir um rótulo ou explicação em uma célula que não é referenciada por nenhuma 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-randomização causada pela entrada de texto em uma região remota da planilha facilita a vida?
Mesmo que haja alguma lógica por trás do tratamento de funções "voláteis", pareceria trivialmente fácil acionar o recálculo através de critérios apropriados, em vez de simplesmente pressionar "enter" após a entrada de texto em células não relacionadas e não referenciadas, ou por outras edições completamente não relacionadas. Prevenir isso deveria ser o padrão e não consigo imaginar que seria difícil de implementar. Se alguém quiser esse "recurso", poderá ativá-lo, mas não consigo imaginar quando isso seria uma coisa boa. Na melhor das hipóteses, pode ser relativamente inofensivo. Para mim, é um grande incômodo por causa do tipo de planilha 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-randomizados), a menos que você solicite isso. Portanto, recomendo que as pessoas usem o Analysis ToolPak quando ele atender às suas necessidades.