Generar muestras aleatorias en Excel

Supongamos que tenemos una lista de nombres como esta (la lista contiene 100 nombres propios)

y queremos extraer una muestra aleatoria de diez nombres.

En Excel podemos hacerlo con facilidad usando la función ALEATORIO.ENTRE para generar números aleatorios y usando este resultado como argumento en la función INDICE. Por supuesto tendremos que superar algunos inconvenientes, como, por ejemplo, números repetidos.

Empezamos por crear un nombre definido que re refiere al rango que contiene la lista (la población, en términos estadísticos)

En la hoja donde queremos que aparezca la muestra creamos una matriz que contenga los nombres (D4:E14 en nuestro ejemplo) y en el rango A5:A14 creamos una columna auxiliar con la fórmula

=ALEATORIO.ENTRE(1,CONTARA(Lista))

La fórmula que extrae los nombre en el rango E5:E14 es

=INDICE(Lista,A5)

donde "Lista" es el nombre que acabamos de definir.

Ahora basta con apretar F9 (Recalcular) para obtener una nueva muestra.

El problema con este modelo es que los números aleatorios no son únicos, es decir, pueden repetirse y por lo tanto generar nombres repetidos.

Para evitar esto creamos un mecanismo que verifique si hay números repetidos y en caso afirmativo, vuelva a calcular. Por supuesto, tendremos que usar macros.

En B5:B14 creamos un rango auxiliar con la fórmula

=CONTAR.SI($A$5:$A$14,A5)

Si el número aparece una única vez en el rango, el resultado es 1; en caso contrario será mayor que 1. Por ejemplo, en este caso el número 75 aparece dos veces y por lo tanto el nombre Cristian aparece dos veces en la muestra

En la celda B15 agregamos la fórmula " =SUMA(B5:B14)". Creamos el nombre definido " ControlValoresUnicos" que se refiere a esta celda. Si todos los números aparecen una única vez, el valor de la celda será 10; si es mayor de 10, sabemos que hay números repetidos.

Para automatizar esta operación usamos esta macro que ponemos en un módulo común del editor de Vb

Sub valores_unicos()

    Calculate
    While Range("ControlValoresUnicos") <> 10
        Calculate
    Wend
  
End Sub

La primer línea del código recalcula la hoja; luego la estructura While…Wend sigue recalculando hasta que la celda " ControlValoresUnicos" contiene el resultado 10.

El último paso es ocultar las columnas auxiliares y agregar un botón de la colección de Formularios de manera que el usuario puede generar una nueva muestra apretando este botón

El archivo con el ejemplo puede descargarse aquí

Registrar entradas con fórmulas – una aclaración

En la nota sobre registro de entradas con fórmulas en Excel, sugerí el uso de Tablas para evitar tener que copiar las fórmulas con cada entrada.

Sin embargo, y como me señalan varios de mis lectores, el uso de tablas en este caso puede dar resultados inesperados. Veamos:

Al introducir el segundo nombre nos hemos movido con las flechas y el resultado es 0, que por el formato fecha-hora vemos como 00/01/1900 00:00:00.

Sin embargo, si nos movemos con la tecla TAB veremos que el resultado es el correcto:

En conclusión: podemos usar un rango común de Excel, copiando las fórmulas o convertir el rango en Tabla y movernos dentro de esta con la tecla TAB.

Registrar fecha y hora de una entrada en Excel con eventos

En la nota anterior vimos como registrar la fecha y hora de una entrada (timestamp) usando fórmulas.

Por supuesto puede hacerse también con macros, más precisamente, programando un evento.

El código del evento, que ponemos en el módulo de la hoja correspondiente, es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngColumnaNombre As Range
   
    Set rngColumnaNombre = Range("A:A")
   
    If Union(Target, rngColumnaNombre).Address = rngColumnaNombre.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

En nuestro ejemplo la columna A contiene los nombres y la columna B el registro de la fecha. Si borramos un nombre, también la fecha es eliminada.

El código evalúa la celda activa cuando se produce un cambio en la hoja; si la celda activa está en la columna A y no esta vacía, se registra la fecha y hora en la celda paralela de la columna B.

En este código definimos una variable de tipo rango

Set rngColumnaNombre = Range("TablaNombres").Columns(1)

La variable se refiere a la primera columna de la tabla “TablaNombres”

Esto nos permite crear una rango dinámico de manera que valores introducidos fuera de la tabla no disparan el evento.

Existen, por supuesto, otras formas de crear rangos dinámicos en el código.

1 – crear un nombre que se refiera al rango dinámico usando una fórmula con las funciones DESREF o INDICE; por ejemplo

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$B:$B)+1)

El código es

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("TablaNombres")).Address = Range("TablaNombres").Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

2 – definir el rango en el código

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TablaNombres As Range
   
    Set TablaNombres = Range(Range("A2"), Cells(Range("A2").End(xlDown).Row + 1, 1))
    If Union(Target, TablaNombres).Address = TablaNombres.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub