El tutorial se enfoca en cómo hacer un muestreo aleatorio en Excel sin repeticiones. Encontrará soluciones para todas las versiones de Excel 365, Excel 2021, Excel 2019 y anteriores.
Hace un tiempo, describimos algunas formas diferentes de seleccionar aleatoriamente en Excel. La mayoría de esas soluciones se basan en las funciones ALEATORIO y ALEATORIO ENTRE, que pueden generar números duplicados. En consecuencia, su muestra aleatoria podría contener valores repetidos. Si necesita una selección aleatoria sin duplicados, utilice los enfoques descritos en este tutorial.
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(datos, RANDARRAY(FILAS(datos))), SECUENCIA(n))
Donde n 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))
Por conveniencia, 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 recupera 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 conteo 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 al azar.
De los datos ordenados aleatoriamente, extrae una muestra de un tamaño específico. Para esto, proporciona la matriz mezclada a la función ÍNDICE y solicita recuperar los primeros N valores con la ayuda de la función SECUENCIA, que produce una secuencia de números del 1 al N. Debido a que los datos originales ya están ordenados 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 de esta manera:
ÍNDICE(ORDENAR(datos, RANDARRAY(FILAS(datos))), SECUENCIA(n), {1,2,…})
Donde n es el tamaño de la muestra y {1,2,…} son los números de columna a extraer.
Como ejemplo, seleccionemos filas aleatorias de A2:C10 sin entradas duplicadas, según el tamaño de la muestra en F1. Como 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 obtener 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 usted especifica los argumentos núm_fila y núm_columna para la función ÍNDICE: núm_fila es proporcionado por SECUENCIA y núm_columna por la constante de matriz.
Como hacer muestreo aleatorio en Excel 2010 – 2019
Como solo Excel para Microsoft 365 y Excel 2021 admiten matrices dinámicas, las funciones de matriz dinámica utilizadas en los ejemplos anteriores solo funcionan en Excel 365. Para otras versiones, tendrá que encontrar una solución diferente.
Supongamos que desea una selección aleatoria de la lista en A2:A10. Esto se puede hacer con 2 fórmulas separadas:
- Genera números aleatorios con la fórmula Rand. En nuestro caso, lo ingresamos en B2 y luego lo copiamos a B10:
=RAND() - Extraiga 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) - Copie la fórmula anterior en tantas celdas como valores aleatorios desee elegir. En este ejemplo, queremos 4 nombres, así que copiamos la fórmula de E2 a E5.
¡Hecho! Nuestra muestra aleatoria sin duplicados queda de la siguiente manera:
Cómo funciona esta fórmula:
Como en el primer ejemplo, usa la función ÍNDICE para recuperar valores de la columna A en función de números de fila aleatorios. La diferencia está en cómo obtienes esos 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 dada. 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 hay en las celdas anteriores. 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 ocurrencias, CONTAR.SI devuelve 1, del cual se resta 1 para mantener la clasificación original. Para las segundas apariciones, CONTAR.SI devuelve 2. Al restar 1, incrementa la clasificación en 1, evitando así las clasificaciones duplicadas.
Por ejemplo, para B2, RANK.EQ devuelve 1. Como esta es la primera aparición, COUNTIF también devuelve 1. RANK.EQ + COUNTIF da 2. Y – 1 restaura el rango 1.
Ahora, vea lo que sucede en el caso de la segunda ocurrencia. Para B5, RANK.EQ también devuelve 1 mientras que COUNTIF devuelve 2. Sumándolos da 3, del cual resta 1. Como resultado final, obtiene 2, que representa el rango del número en B5.
El rango va al argumento núm_fila de la función ÍNDICE y elige el valor de la fila correspondiente (se omite el argumento núm_columna, por lo que el valor predeterminado es 1). Esta es la razón por la que es tan importante evitar la clasificación duplicada. Si no fuera por la función CONTAR.SI, RANGO.EQ arrojaría 1 para B2 y B5, lo que provocaría que ÍNDICE devolviera el valor de la primera fila (Andrew) dos veces.
Cómo evitar que cambie la muestra aleatoria de Excel
Como todas las funciones aleatorias en Excel, como RAND, RANDBETWEEN y RANDARRAY son volátiles, se vuelven a calcular con cada cambio en la hoja de trabajo. Como resultado, su muestra aleatoria cambiará continuamente. Para evitar que esto suceda, use la función Pegado especial > Valores para reemplazar fórmulas con valores estáticos. Para ello, lleva a cabo estos pasos:
- 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 copiarlas.
- Haga clic derecho en el rango seleccionado y haga clic en Pegado especial > Valores. Alternativamente, presione Shift + F10 y luego V, que es el atajo para la función mencionada anteriormente.
Para conocer los pasos detallados, consulte Cómo convertir fórmulas a 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 nuestro Ultimate Suite instalado en su Excel, puede hacer un muestreo aleatorio con un clic del mouse en lugar de una fórmula. Así es cómo:
- En la pestaña Herramientas de Ablebits, haga clic en Aleatorizar > Seleccionar aleatoriamente.
- Seleccione el rango del que desea tomar una muestra.
- En el panel del complemento, haga lo siguiente:
- Elija si desea seleccionar filas, columnas o celdas al azar.
- Defina el tamaño de la muestra: que puede ser un porcentaje o un número.
- Haga clic en el botón Seleccionar.
¡Eso es todo! Como se muestra en la imagen a continuación, se selecciona una muestra aleatoria directamente en su conjunto de datos. Si desea copiarlo en algún lugar, simplemente presione un atajo de copia normal (Ctrl + C).
Así es como seleccionar una muestra aleatoria en Excel sin duplicados. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
(archivo .xlsx)
Versión totalmente funcional de Ultimate Suite de 14 días (archivo .zip)