Cómo obtener una muestra aleatoria en Excel sin duplicados

Selección aleatoria de Excel de la lista sin duplicados

Solo funciona en Excel 365 y Excel 2021 que admiten matrices dinámicas.

Para hacer una selección aleatoria de una lista sin repeticiones, use esta fórmula genérica:

ÍNDICE(ORDENAR(DATOSRANDARRAY(FILAS(DATOS))), SECUENCIA(norte))

Dónde norte es el tamaño de selección deseado.

Por ejemplo, para obtener 5 nombres aleatorios únicos de la lista en A2:A10, esta es la fórmula que debe usar:

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))

Para mayor comodidad, puede ingresar el tamaño de la muestra en una celda predefinida, digamos C2, y proporcionar la referencia de celda a la función SECUENCIA:

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))

Cómo funciona esta fórmula:

Aquí hay una explicación de alto nivel de la lógica de la fórmula: la función RANDARRAY crea una matriz de números aleatorios, ORDENAR ordena los valores originales por esos números e ÍNDICE toma tantos valores como especifica SECUENCIA.

Un desglose detallado sigue a continuación:

La función ROWS cuenta cuántas filas contiene su conjunto de datos y pasa el número a la función RANDARRAY para que pueda generar la misma cantidad de decimales aleatorios:

RANDARRAY(ROWS(A2:C10))

Esta matriz de decimales aleatorios se usa como la matriz «ordenar por» por la función ORDENAR. Como resultado, sus datos originales se barajan aleatoriamente.

A partir de datos ordenados al azar, extraiga una muestra de cierto tamaño. Para hacer esto, proporcione la matriz mezclada a la función ÍNDICE y solicite recuperar la primera norte valores utilizando la función SEQUENCE, que produce una secuencia de números del 1 al norte. Dado que los datos originales ya están clasificados en orden aleatorio, realmente no nos importa qué posiciones recuperar, solo importa la cantidad.

Seleccione filas aleatorias en Excel sin duplicados

Solo funciona en Excel 365 y Excel 2021 que admiten matrices dinámicas.

Para seleccionar filas aleatorias sin repeticiones, cree una fórmula como esta:

ÍNDICE(ORDENAR(DATOSRANDARRAY(FILAS(DATOS))), SECUENCIA(norte), {1,2,…})

Dónde norte es el tamaño de la muestra y {1,2,…} son los números de columna a extraer.

Por ejemplo, seleccionemos filas aleatorias de A2:C10 sin entradas duplicadas según el tamaño de la muestra de F1. Dado que nuestros datos están en 3 columnas, proporcionamos esta matriz constante a la fórmula: {1,2,3}

=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})

Y obtienes el siguiente resultado:

Cómo funciona esta fórmula:

La fórmula funciona exactamente con la misma lógica que la anterior. Un pequeño cambio que hace una gran diferencia es que especificas tanto núm_fila y núm_columna argumentos a la función ÍNDICE: núm_fila es proporcionado por SEQUENCE y núm_columna por la constante de la matriz.

Como hacer muestreo aleatorio en Excel 2010 – 2019

Dado que solo Excel para Microsoft 365 y Excel 2021 admiten matrices dinámicas, las funciones de matriz dinámica que se usaron en los ejemplos anteriores solo funcionan en Excel 365. Para otras versiones, deberá encontrar una solución diferente.

Suponiendo que desea una selección aleatoria de la lista de A2: A10. Esto se puede hacer con 2 fórmulas separadas:

1. Genera números aleatorios con la fórmula Rand. En nuestro caso, lo insertamos en B2, luego lo copiamos en B10:

   =RAND()

2. Dibuje el primer valor aleatorio con la siguiente fórmula, que ingresa en E2:

   =INDEX($A$2:$A$10, RANK.EQ(B2, $B$2:$B$10) + COUNTIF($B$2:B2, B2) - 1)

3. Copie la fórmula anterior en tantas celdas como desee para elegir valores aleatorios. En este ejemplo, queremos 4 nombres, así que copiamos la fórmula de E2 a E5.

¡Terminado! Nuestra muestra aleatoria sin duplicados se ve así:

Cómo funciona esta fórmula:

Como en el primer ejemplo, use la función ÍNDICE para recuperar valores de la columna A en función de números de fila aleatorios. La diferencia es cómo obtienes estos números:

La función RAND llena el rango B2:B10 con decimales aleatorios.

La función RANK.EQ calcula el rango de un número aleatorio en una fila determinada. Por ejemplo, en E2, RANK.EQ(B2, $B$2:$B$10) clasifica el número de B2 frente a todos los números de B2:B10. Cuando se copia a E3, la referencia relativa B2 cambia a B3 y devuelve el rango del número en B3, y así sucesivamente.

La función CONTAR.SI encuentra cuántas ocurrencias de un número dado existen en las celdas de arriba. Por ejemplo, en E2, CONTAR.SI($B$2:B2, B2) comprueba solo una celda, B2 en sí misma, y ​​devuelve 1. En E5, la fórmula cambia a CONTAR.SI($B$2:B5, B5) y devuelve 2, porque B5 contiene el mismo valor que B2 (tenga en cuenta que esto es solo para explicar mejor la lógica de la fórmula; en un conjunto de datos pequeño, las posibilidades de obtener números aleatorios duplicados son cercanas a cero).

Como resultado, para todas las primeras apariciones, CONTAR.SI devuelve 1, del cual se resta 1 para mantener el rango original. Para la segunda ocurrencia, COUNTIF devuelve 2. Restar 1 aumenta el rango en 1, evitando así rangos duplicados.

Por ejemplo, para B2, RANK.EQ devuelve 1. Dado que esta es la primera aparición, COUNTIF también devuelve 1. RANK.EQ + COUNTIF devuelve 2. Y – 1 restaura el rango 1.

Ahora, vea lo que sucede en el caso de 2^cuando apariencia Para B5, RANK.EQ también devuelve 1, mientras que COUNTIF devuelve 2. Al sumarlos, se obtiene 3, del cual resta 1. Termina con 2, que es el rango del número en B5.

El rango va a núm_fila argumento de la función ÍNDICE y elija el valor de la fila correspondiente (el núm_columna el argumento se omite, por lo que el valor predeterminado es 1). Por eso es tan importante evitar la doble clasificación. Si no fuera por la función COUNTIF, RANK.EQ devolvería 1 tanto para B2 como para B5, lo que provocaría que INDEX devolviera el valor de la primera fila (Andrew) dos veces.

Cómo evitar que cambie la muestra aleatoria de Excel

Debido a que todas las funciones de aleatorización en Excel, como RAND, RANDBETWEEN y RANDARRAY son volátiles, se vuelven a calcular con cada cambio en la hoja de cálculo. Como resultado, su muestra aleatoria cambiará continuamente. Para evitar esto, use la función Pegado especial > Valores para reemplazar fórmulas con valores estáticos. Para hacer esto, realice estos pasos:

1. Seleccione todas las celdas con su fórmula (cualquier fórmula que contenga la función RAND, RANDBENTWEEN o RANDARRAY) y presione Ctrl + C para copiarlos.
2. Haga clic derecho en el rango seleccionado y haga clic en Pascua especial > existencias. Alternativamente, presione Mayús + F10 y luego Vque es el atajo para la función mencionada anteriormente.

Para conocer los pasos detallados, consulte Cómo convertir fórmulas en valores en Excel.

Selección aleatoria de Excel: filas, columnas o celdas

Funciona en todas las versiones de Excel 365 a Excel 2010.

Si tiene Ultimate Suite instalado en Excel, puede hacer un muestreo aleatorio con un clic del mouse en lugar de una fórmula. Así es cómo:

1. En Herramientas de Ablebits pestaña, haga clic Aleatorizar > Seleccionar aleatorio.
2. Seleccione el rango del que desea elegir una muestra.
3. En el panel de complementos, haga lo siguiente:
   • Elija si desea seleccionar aleatoriamente filas, columnas o celdas.
   • Defina el tamaño de la muestra: puede ser un porcentaje o un número.
   • Seguir adelante Seleccionar botón.

¡Esto es! Como se muestra en la imagen a continuación, se extrae una muestra aleatoria directamente en su conjunto de datos. Si desea copiarlo en algún lugar, presione un atajo de copia normal (Ctrl+C).

Descargas disponibles

Muestra aleatoria sin duplicados – ejemplos de fórmulas (archivo .xlsx)
Ultimate Suite, versión totalmente funcional durante 14 días (archivo .exe)