Necesito usar la función RANDBETWEEN de Microsoft Excel y soy consciente de que se genera un nuevo conjunto de números aleatorios cada vez que haces algo más en cualquier parte de la hoja de trabajo. También conozco las soluciones para "congelar" un conjunto de números aleatorios que se ha generado, por ejemplo, para ordenar n listas de números aleatorios entre a y b. Mi pregunta es la siguiente: ¿POR QUÉ cambian los números aleatorios generados? ¿Hay algo en el algoritmo que requiera esto? FWIW, Google Sheets, LibreOffice Calc y Apple Numbers muestran el mismo comportamiento.
Respuesta1
Charles Williams tiene una buena explicación de cómo Excel calcula las cosas y por qué.
http://www.decisionmodels.com/calcsecretsi.htm
En esencia, si un libro contiene funciones volátiles (consulte la lista de Charles, ya que lo que es una función volátil ha cambiado a lo largo de los años con diferentes versiones de Excel), se activará un recálculo completo del libro cuando se cambie cualquier celda del libro (si Excel está configurado en cálculo automático). Si no hay funciones volátiles en el libro de trabajo, solo se volverán a calcular las celdas afectadas por el último cambio.
Otros aquí han declarado que el cálculo automático siempre calculará todo lo que hay en el libro cuando se cambia cualquier celda, pero eso es incorrecto. Sólo las funciones volátiles provocan el recálculo automático de todas las celdas del libro. Sin funciones volátiles, Excel recalcula sólo lo que es necesario recalcular.
Es por eso que las personas como yo, que conocen la diferencia, recomiendan encarecidamente evitar funciones volátiles como OFFSET() o INDIRECT() en las celdas de la hoja de cálculo si es posible, ya que el suero ralentizará el libro al provocar un recálculo completo después de cada cambio de celda. Aprender a usar INDEX() en lugar de OFFSET() puede resultar en mejoras dramáticas de velocidad, porque Index no es volátil (consulte el artículo de Charles para más detalles).
El motor de cálculo de Excel se basa en la filosofía de "recalcular o morir" que distinguió a Excel de los productos de la competencia cuando estaba en desarrollo. Mira este artículo:https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-defied-the-odds/
Respuesta2
Otras respuestas se centran en la mecánica del recálculo y el papel de las funciones volátiles, pero creo que se pierde parte de la esencia de la pregunta. Aborda las reglas que se han empleado pero no tanto el "por qué". Me centraré en eso.
Concepto de diseño
Permítanme comenzar con un concepto de diseño y los componentes básicos de la mayoría de las hojas de cálculo modernas. Hay una "división de responsabilidades". Las funciones son rutinas dedicadas que realizan una tarea específica y lo hacen cada vez que se les solicita. Aparte de eso, la aplicación de hoja de cálculo controla cuándo se les pregunta.
Las funciones, en sí mismas, no contienen ninguna lógica para elegir recalcular o no recalcular. La naturaleza de todas las funciones de Excel es realizar algún tipo de operación cada vez que se le solicite hacerlo. Ninguna función tiene su propia "memoria" de su valor actual o un historial de sus cálculos pasados. No tiene forma de saber si es la primera vez que se le pide que haga su trabajo. Por lo tanto, ninguna función nativa tiene la capacidad de ejecutarse solo una vez. Si se vuelve a calcular alguna función, hará lo que está diseñada para hacer y producirá un valor nuevo.
La aplicación de hoja de cálculo contiene cierta inteligencia que le permite ahorrar tiempo al evitar recálculos innecesarios (como se describe en la respuesta de teylyn). ElsoloEl momento en que se omite el recálculo es si la aplicación sabe que el valor de la función no se vio afectado por los cambios en la hoja de cálculo que desencadenaron la necesidad de volver a calcular las cosas.
Entonces, ¿qué pasa si necesita ejecutar una función una vez y luego conservar su valor? Tome su ejemplo de producir un número aleatorio y luego hacer que el resultado no cambie. Eso describe la creación de constantes generadas aleatoriamente y puede hacerlo con Excel.
Excel no puede saber cómo desea utilizar sus capacidades; si desea continuar produciendo nuevos valores o conservar el último conjunto. Cada opción es un caso de uso compatible con Excel. Excel se adapta a ambos casos brindándole las herramientas para calcular nuevos valores y las herramientas para preservar los valores antiguos. Puedes construir lo que quieras. Pero el usuario tiene la responsabilidad de hacer que haga lo que quiera.
Lógica de funciones
Entonces, veamos la lógica de lo que hace la función. ¿Podría tener sentido que la lógica de recálculo omita una función aleatoria porque su valor no debería verse afectado por otros cambios en la hoja de cálculo?
Que el valor de una función cambie o no al volver a calcularla depende de su propósito y en qué se basa. Un cálculo sobre valores constantes siempre producirá el mismo resultado. Una operación basada en valores de celda que no han cambiado producirá el mismo resultado.
Sin embargo, algunas funciones están diseñadas con la intención y el propósito de producir un resultado diferente cada vez. Considere, por ejemplo, funciones basadas en la hora actual. Producirán un nuevo resultado basado en el tiempo de recálculo. No puede tener una función cuyo propósito sea producir un valor basado en la hora actual y que no se actualice cuando se vuelva a calcular.
El objetivo del control del recálculo es hacer que siempre todo refleje el estado actual de las cosas cuando se activa el recálculo. Ese criterio no se cumpliría si no se actualizaran las funciones basadas en la hora actual.
Las funciones "aleatorias", como RANDBETWEEN, tienen el propósito de producir un nuevo número aleatorio cuando se recalculan. Eso es lo que pretenden hacer. Se podría argumentar que el recálculo podría omitirse porque el valor no debería verse afectado por otras cosas que suceden en la hoja de cálculo.
Si ese fuera el comportamiento predeterminado, estaría actualizando la hoja de cálculo, pero el valor aleatorio permanecería constante. Ese no es un comportamiento muy aleatorio. Eso respaldaría un caso de uso pero no el otro. Volviendo al concepto de diseño básico, se maneja como cualquier otra función. El comportamiento predeterminado es que Excel lo vuelva a calcular. Si desea conservar el valor anterior para su caso de uso, depende de usted hacerlo con las herramientas disponibles.
Respuesta3
Cada cambio en los lanzamientos de la hoja de trabajorecálculo automáticode todas las fórmulas, que es el comportamiento predeterminado.
Puede desactivarlo o congelar el valor real convirtiéndolo a valor puro (como lo opuesto a una fórmula).
Respuesta4
A pesar de los heroicos esfuerzos para hacer que esto suene como una característica y no como un error, encuentro extremadamente improbable que este comportamiento sea rutinariamente ventajoso. Las "características" nos hacen la vida más fácil, los "errores" la hacen más difícil. En mi caso, ingresar una etiqueta o explicación en una celda a la que no hace referencia ninguna otra celda provoca una nueva aleatorización que es incómoda de manejar. Yo lo llamaría un "error".
Sí, puedo controlar el recálculo manualmente (una molestia) o puedo pegar valores para evitarlo (nuevamente, una molestia), pero ¿en qué casos la nueva aleatorización causada por la entrada de texto en una región remota de la hoja hace la vida más fácil?
Incluso si hay cierta lógica detrás del manejo de funciones "volátiles", parecería trivialmente fácil activar el recálculo mediante criterios apropiados además de simplemente presionar "enter" después de ingresar texto en celdas no relacionadas y sin referencia, o mediante otras ediciones completamente no relacionadas. Prevenir eso debería ser la opción predeterminada y no puedo imaginar que sea difícil de implementar. Si alguien quiere esa "función", puede activarla, pero no puedo imaginar cuándo sería algo bueno. En el mejor de los casos, podría ser relativamente inofensivo. Para mí es una gran molestia por el tipo de hoja que estoy creando.
Finalmente, el paquete de herramientas de análisis le permite crear distribuciones de números aleatorios de diversas formas, y los rangos de números así creados no se recalculan (nuevan aleatorizan) en absoluto, a menos que usted se lo indique. Por eso recomiendo que las personas utilicen el Analysis ToolPak cuando satisfaga sus necesidades.