todas - referencia circular en excel 2013

¿Cómo lograr esto en Excel sin un razonamiento circular?

Question

Quiero crear una página de Excel donde, en primer lugar, tengo cuatro números aleatorios entre los valores 0-9 que se actualizan como quiero. Esto es fácil usando =RANDBETWEEN(0,9) en cuatro celdas (A1-D1, por ejemplo). Ahora es la parte difícil que quiero que cada uno de estos números aleatorios se detengan cuando alcanza un cierto valor entre 0-9, de modo que si continuamente actualizo las fórmulas si una de las cuatro celdas de número aleatorio alcanza un cierto valor, se detendrá en ese momento. valor y luego no producir más números aleatorios.

Entonces, por ejemplo, si configuro A1 como =RANDBETWEEN(0,9) , puedo seguir refrescando esa celda hasta que se convierta en 5 (o cualquier otro número 0-9) y luego se detenga.

Para lograr esto, quiero decir algo como:

=IF(A1=5,5,RANDBETWEEN(0,9))

por supuesto, esto no funciona, ya que es circular. ¿Cómo puedo lograr esto en Excel?

Answer 1

En el menú ARCHIVO -> OPCIONES -> FÓRMULA, habilite cálculos iterativos. con iteraciones máximas de 1

Luego usa la fórmula que sugeriste:

=IF(A1=5,5,RANDBETWEEN(0,9))

Se detendrá cuando sea igual a 5 y se quede allí.

O puede usar esta función que es similar al @ Gary''sStudent

Function MyRandBetween(Strt As Long, Ed As Long, Sty As Long) As Long
Application.Volatile
If Application.Caller.Value = Sty Then
    MyRandBetween = Sty
    Exit Function
Else
    MyRandBetween = Application.WorksheetFunction.RandBetween(Strt, Ed)
End If

End Function

Entonces tu fórmula sería

=MyRandBetween(0,9,5)

Se detendrá a las 5 y se quedará allí. De lo contrario, elegirá aleatoriamente un número.

Answer 2

Prueba esta macro corta:

Sub Leave5Alone()
    With Range("A1")
        If .Value = 5 Then
        Else
            .Value = Application.WorksheetFunction.RandBetween(1, 5)
        End If
    End With
End Sub

Esto se puede extender fácilmente para cualquier rango de celdas y valores de detención.

Answer 3

Una forma fácil de hacerlo es con los eventos de VBA. El evento calcular lo hará por usted. Pruebe lo siguiente en una hoja, donde ya tiene las fórmulas RANDBETWEEN :

Por ejemplo, aquí:

Option Explicit

Private Sub Worksheet_Calculate()

    Dim myCell  As Range

    For Each myCell In UsedRange
        If myCell.HasFormula And myCell.Value = 5 Then
            myCell.Value = myCell.Value
        End If
    Next myCell

End Sub

Sin embargo, si hay una fórmula como =A1+A2 que también devuelve 5 , la fórmula también se traducirá a valor. Para evitar esto, puede intentar buscar palabras como RANDBETWEEN en la fórmula. Para hacerlo, intente implementar esto en el código:

If InStr(1, UCase(myCell.Formula), "RANDBETWEEN") > 0 Then