martes, 18 de septiembre de 2012

Aleatorios sin repetición en Excel, sin macros.

Dos días atrás me llegó una consulta sobre la manera de conseguir ciertos números de manera aleatoria entre valores de un rango, pero sin repetir. Como es una pregunta que se me hace con cierta frecuencia, por fin me he decidido a publicar algunas soluciones al tema.
En esta primera ocasión veremos cómo hacerlo sin emplear macros.

...Quisiera hacerte esta consulta: deseo aplicar la función Aleatorio (no dispongo de la versión de excel Aleatorio.entre) por ejemplo para que me dé números enteros del 1 al 100. Sería: =REDONDEAR.MAS(ALEATORIO()*100;)

Mi intención es dar con 30 números, por lo que aplico la fórmula en 30 celdas (salen repes)... Empero: tendrían que ser 30 números diferentes. Ese es mi dilema...

Para dar solución a esta cuestión deberemos recordar un post publicado en este blog hace tres años (ver), es esa entrada se explicaba el uso de la función JERARQUIA, combinada a la función CONTAR.SI para conseguir una ordenación de ciertos valores; una ordenación única, esto es, sin repeticiones de posición. Esta es la esencia de la explicación siguiente.

Lo primero que tenemos que hacer es dotar de 'aleatoriedad' a nuestro modelo, por lo que comenzamos formulando en el rango A2:A21 veinte fórmulas =ALEATORIO(), que recuerdo que devuelve un número entre 0 y 1 (para el ejemplo que plantea arrastraríamos desde A2 hasta A101):

Aleatorios sin repetición en Excel, sin macros.


Las posibilidades de obtener dos valores iguales en el rango deben ser bastante próximas a cero, pero lo tendremos en cuenta para nuestra formulación...
El siguiente paso es fórmular, sobre el rango anterior A2:A21, la fórmula que devolverá números aleatorios sin repetir entre 1 y 20 (para mi ejemplo), a partir de la celda C2:
=JERARQUIA(A2;$A$2:$A$21)+CONTAR.SI($A$2:A2;A2)-1
fórmula idéntica a la vista en ver.
Esta fórmula JERARQUIA junto al CONTAR.SI nos asegura que los valores devueltos nunca serán iguales, es decir, que el orden dado no repetiría posiciones.

Aleatorios sin repetición en Excel, sin macros.


Dependiendo de cuantos números aleatorios sin repetición necesitemos arrastraremos el rango C2:C21. En mi caso, para demostrar que ciertamente nuestra formulación devuelve todos los valores sin repetir, he generado 20 aleatorios. Si en lugar de 20 fueran 100 ó 1000 arrastraríamos los rangos de nuestras fórmulas hasta tal número de filas.
En resumen, respondiendo la cuestión planteada, para conseguir 30 números aleatorios que vayan del 1 al 100, arrastraremos nuestra formulación =ALEATORIO() en A2:A101, y la fórmula =JERARQUIA(A2;$A$2:$A$21)+CONTAR.SI($A$2:A2;A2)-1 en C2:C31.
Es decir, el intervalo de valores nos lo da el número de ALEATORIOS y los valores sin repetición la fórmula JERARQUIA + CONTAR.SI.


Para comprobar que es cierto que no se repiten, en el rango E2:E21 he añadido una fórmula de conteo CONTAR.SI que evalúa cuantas veces aparece cada valor en el rango C2:C21:

Aleatorios sin repetición en Excel, sin macros.





156 comentarios:

  1. Estimado, siguiendo tu ejemplo, deseo volver a listar 20 números aleatorios sin repeterir en la columna"D", distinto al orden que tiene la columna "C" y asi repetir la misma acción en la columna "E" y "F", de manera que un número no se repita en la misma fila, obteniendo una tabla de 20 filas *4 columnas con números sin repetir del 1 al 20.
    Como lo haría?

    ResponderEliminar
    Respuestas
    1. Hola,
      si lo he entendido bien, necesitas copiar un =ALEATORIO() en un rango similar, siguiendo tu ejemplo en el rango A1:D20.
      Luego, para generar los aleatorios en otro rango similar de 20x4, por ejemplo F1:I20, empezando desde F1 copiarías:
      =JERARQUIA(A1;$A$1:$D$20)+CONTAR.SI($A$1:A1;A1)-1
      arrastrando por el rango...
      Espero haberte comprendido bien.
      Slds

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. Aún se repiten utilizando las dos funciones y además no se asignan todos los números, por ejemplo se generó 1,3,5,6,7,9, no se asignó el 2,4 y 8.
      Alguna solución?

      Eliminar
    4. Hola Ann,
      como estás?, un placer saludarte igualmente.

      aplicando correctamente la función comentada
      =JERARQUIA(A1;$A$1:$D$20)+CONTAR.SI($A$1:A1;A1)-1
      con los dolares en su sitio, es imposible no obtener una secuencia ordenada completa...

      Revisa la fórmula y comentas
      Un cordial saludo

      Eliminar
  2. Hola, en primer lugar estoy muy agradecido por tu pronta respuesta; pero por otro lado, creo que no me explique bien. Ahora espero explicarte mejor lo que necesito y sería genial si me puedas apoyar. Imaginemos que tenemos 5 personas a las que hay que asignarles una zona (del 1 al 5) cada mes (columnas A a la D) osea durante 4 meses. Un ejemplo de asignación sería la siguiente tabla:

    A B C
    Carlos 1 2 3
    Manuel 2 3 1
    Jose 3 1 2


    Como podrás observar lo que busco es que ninguna persona repita la zona (el mismo numero) en el siguiente mes (columna).

    En este pequeño ejemplo, corrigeme si me equivoco, estadísticamente podríamos tener 6 resultados que los podría escribir a mano.

    Resulta que se complica porque tengo 9 personas y 10 zonas que asignar y no quiero que estas personas repitan zona durante 9 meses, nillevar un patron intercalado (como en el ejemplo), sino de manera aleatoria.

    Es posible realizar esto con formulas?
    Una vez más agradezco tu consideración.

    ResponderEliminar
  3. Perdon, en mi ejemplo debi escribir 3 personas con 3 zonas distintas.
    Por cierto, intenté seguir el enlace de "responder" para hace una sola cadena pero no funciona, por eso escribo como si fuera un nuevo comentario. Gracias.

    ResponderEliminar
    Respuestas
    1. Hola de nuevo...
      vale, más claro ahora. Hablamos de generarr combinaciones.
      Hay un par de asuntos expuestos que hacen muy complicado el tema. El primero hacerlo con funciones/fórmulas; no me atrevería a decir que es imposible, pero se me antoja muy complicado... se podría confiar en el azar y lanzar algo de manera similar a lo expuesto anteriormente, esperando que el número de combinaciones, al ser menor de total posible, resuelva y obtengamos combinaciones aleatorias diferentes. Pero esto no es seguro, y probablemente (muy probablemente) obtengamos combinaciones iguales.
      La otra cuestión es que sí se podría obtener esas combinaciones mediante macros, pero una macro es un código de programación, y normalmente va sujeto a un patrón, por lo que parece tampoco serviría...

      Este asunto de obtener combinaciones es complicado, y hay bastante literatura al respecto en internet.
      Suerte!!

      Eliminar
  4. No se si estoy mal pero en el ejemplo de los números aleatorios, la segunda parte de la fórmula (+CONTAR.SI($A$2:A2,A2)-1) está de más... ya que el resultado se obtiene satisfactoriamente tan sólo con la fórmula JERARQUIA.

    Saludos.

    ResponderEliminar
    Respuestas
    1. Hola,
      esa parte de la fórmula se incluye por si acaso se repitieran dos o más aleatorios en ese rango...
      Lo normal es que la función ALEATORIO no devuelva dos valores iguales, pero las leyes de probabilidad son caprichosas, y hay que tenerlo previsto.
      Slds

      Eliminar
  5. Hola, resulta que tengo que hacer un bingo y he ocupado la función =ENTERO(ALEATORIO()*(X-Y)+Y), siendo x e y los límites del intervalo de cada columna, pero resulta que SIEMPRE sale un par repetido y no sé como poder regular eso. No chacho nada de excel y aunque escribo las fórmulas en la planilla no me resulta...

    ResponderEliminar
    Respuestas
    1. Hola Losataschu,
      sólo tienes que replicar el ejemplo explicado en esta entrada. En tu caso, el resultado de la fórmula que propones:
      =ENTERO(ALEATORIO()*(X-Y)+Y)
      la pondrías en el rango A2:A... (la celda que sea), y luego a partir de la celda C2:
      =JERARQUIA(A2;$A$2:$A$21)+CONTAR.SI($A$2:A2;A2)-1
      arrastrando hacia abajo, tantas celdas como valores quieras obtener.
      Slds

      Eliminar
    2. y cómo se pone eso del rango?

      Eliminar
    3. Bueno, eso es sencillo, en A2 copias la fórmula que indicas:
      =ENTERO(ALEATORIO()*(X-Y)+Y)
      con los valores adecuados, y arrastras hacia abajo la fórmula hasta donde necesites.
      Igual proceder desde C2 con la fórmula que te indiqué.
      Slds

      Eliminar
    4. Aah, pero si bajo a la celda C3, he de cambiar algo en la fórmula de jerarquia+contar?

      Eliminar
    5. Depende, sólo tienes que cambiar algo si el rango donde has metido la fórmula
      =ENTERO(ALEATORIO()*(X-Y)+Y)
      es diferente a A2:A....

      Si este rango fuera A3:A21, entonces la fórmula sería
      =JERARQUIA(A3;$A$3:$A$21)+CONTAR.SI($A$3:A3;A3)-1
      Sdls!!

      Eliminar
  6. Gracias, Excelforo. Tu solución es la mejor opción que he encontrado para realizar un sencillo juego de BINGO en Excel sin necesidad de programar en VB;

    Con tus fórmulas creo una columna de números que equivalen a todas las "bolas" del juego en el orden en que ha salido, pero necesitaría ahora una manera de hacer que los números me aparezcan uno a uno (por ejemplo, a tamaño aumentado en un conjunto de casillas combinadas a modo de pantalla de visualización) pero sin que ser renueven los aleatorios.

    ¿Alguna sugerencia?

    Muchas gracias y felices fiestas.

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias!!
      Entiendo que te gustaría que fuera sin macros..
      El asunto es complicado, ya que paraa recuperar esa secuencia de X números se necesitaría alguna función o quizá incluso algún Control de formulario, lo que requeriría en todo caso un Recálculo de la hoja, y por tanto de los Aleatorios que generan esos valores.
      Podrías manualmente copiar y pegar los valores obtenidos de toda una secuencia aleatoria, y luego sobre ese listado de valores fijos, aplicar alguna herramienta para mostrar los valores de uno en uno.
      Se me ocurre, para que quede vistoso, incorporar un Control de formulario tipo control de número, cambiando en su configuración los valores mínimo y máximo y mostrando el dato en alguna celda; por otro lado, empleando una función sencilla:
      =INDICE(valorControl;rango listado valores fijo)
      ir consiguiendo uno a uno los valores aleatorios ya fijados.

      Espero te haya podido orientar.

      Slds y feliz año nuevo!!

      Eliminar
  7. Gracias por la idea; ya lo indagaré, aunque, como dices, el asunto se complica si no queremos usar macros.

    Que tengas un buen año 2013.

    ResponderEliminar
  8. Ismael Romero, utilice tu propuesta para resolver una necesidad.... tal vez lo demas no se dieron cuenta, pero el razonamiento que utilizaste para combinar esas dos funciones fue sencillamente brutal!!!

    me quito el sombrero....... mis respetos......

    ResponderEliminar
    Respuestas
    1. Bueno.. me pondré colorado
      ;-)

      Muchas gracias... sólo apliqué diferentes técnicas/fórmulas explicadas para conseguirlo.

      Un cordial saludo y me alegro te sirviera!!

      Eliminar
  9. Tengo un problema. Necesito generar un número de 3 cifras, cuya primera cifra sea diferente a la tercera (que no sea capicua).
    La primera cifra la generé de la siguiente manera:
    =TRUNCAR(ALEATORIO()*(10-1)+1,0.
    La segunda cifra la generé así:
    =TRUNCAR(ALEATORIO()*(10-0)+0,0)
    la tercera cifra no sé cómo generarla ya que esta tendría que ser una cifra aleatoria entre cero y 9 pero que NO sea igual a la la primera.
    ¿Cómo se podría hacer esto?....apreciaré tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola COE,
      lo que ocurre con tu planteamiento es que se pierde la aleatoriedad.. ya que un dígito está condicionado, por tanto no podrías hacerlo directamente con la función ALEATORIO; o si lo haces tendrías que refrescar la hoja para calcule nuevamente hasta que aparezca un valor completo de tres dígitos no capicúa.

      Me temo que tendrás que crear una función o una macro para conseguir lo que quieres de una sola vez.

      Slds

      Eliminar
  10. Hola, muy bueno tu blog, lo estoy siguiendo mucho.
    Una pregunta, te ha pasado alguna vez que al copiar y pegar la fórmula de un número aleatorio por ejemplo =ALEATORIO.ENTRE(100;450), a otra celda te sigue copiando el mismo número. (he intentado copiar y pegar fórmula pero me sigue copiando el número. Lo curioso que hasta que no le doy doble clic a la celda y enter, no me genera otro número.
    Normalmente no me ocurre esto pero hay ocasiones que sí..
    Gracias por todo..
    Mª carmen

    ResponderEliminar
    Respuestas
    1. Hola Mª Carmen,
      con sinceridad nunca me ha ocurrido... demasiada casualidad.
      Podría pasarte que tengas marcado el cálculo manual (en lugar de automático)(ver Ficha Fórmulas > grupo Cálculo).

      Verifica esto, y me cuentas.
      Sdls cordiales

      Eliminar
    2. Oooohhhh.. si.. era eso..
      me estoy dando cuenta que excel es un mundo..fíjate que me quedo impresionada con eso.. jejej..
      Gracias..
      Mª Carmen

      Eliminar
  11. Excelente! Funcionó perfecto! Muchas gracias!

    Miriam

    ResponderEliminar
  12. Una consulta:
    Existe la forma de contar datos repetidos de una columna. En este caso conté los colores que existen (columna B) {=SUMA(1/CONTAR.SI(B2:B10,B2:B10))} Pulsando (crtl+shift+Enter)
    Pero para el caso de las 3 columnas, ¿como podría efectuar el conteo de los registros considerando las 3 columnas? En especial cuando me pidan Letra A / Color Azul / Numero 1

    Tomando como ejemplo en una hoja de excel desde la celda A1

    Letra Colores Numero
    A Rojo 1
    B Azul 2
    A Azul 1
    B Verde 3
    A Rojo 2
    B Rojo 1
    A Verde 1
    B Verde 2
    A Amarillo 1

    ¿Tendria que crear algun function?

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola que tal?
      si he entendido bien quieres contar el número de registros (de esos 9) que a la vez son 'Azul', letra 'A' y número 1.
      Si es así sólo emplea la función CONTAR.SI.CONJUNTO
      =CONTAR.SI.CONJUNTO(B2:B10;"Azul";A2:A10;"A";C2:C10;1)

      Slds cordiales

      Eliminar
    2. Claro, esa es una forma en el caso me pidan Letra A / Color Azul / Numero 1.

      Pero si me pidiesen ¿cuanto registros hay en total? (sin contar los repetidos? :(

      Eliminar
    3. Hola Alexander,
      bien, efectivamente la función CONTA.SI.CONJUNTO respondía a tu cuestión sobre el conteo de una combinación en concreto.

      Si quieres conocer el número de registros únicos absoluto te recomendaría algo muy sencillo, que es aplicar un filtro avanzado marcando la casilla de verificación Sólo registros únicos, así podrás saber cuáles son esos registros únicos y contarlos.

      Con funciones es algo más complejo, pero puedes ver un ejemplo para dos columnas en
      http://excelforo.blogspot.com.es/2010/11/contar-registros-unicos-sobre-dos.html

      Espero te sirva
      Slds

      Eliminar
    4. Genial! Es el ejemplo que necesitaba.
      Gracias :)

      Eliminar
  13. HOLA, QUICIERA SABER SOBRE UN CONJUNTO DE CARTONES (1000) DE BINGOS (15 NUMEROS POR CARTON) CUAL DE TODOS ESTA REPETIDO PARA ELIMINARLO Y COMO SE HACE.- GRACIAS.-

    ResponderEliminar
    Respuestas
    1. Hola,
      lo primero que yo haría para trabajar más fácilmente sería colocar todos los números en la misma fila, luego aplicar para cada fila una fórmula como sigue.
      Por simplificar muestro el ejemplo para cartones de 6 números, listado en el rango A1:F7 (como si fueran cartones de dos líneas y seis números).
      La fórmula a incluir en H1 para luego arrastrar sería:
      =SUMAPRODUCTO(($A$1:$A$8=A1)*($B$1:$B$8=B1)*($C$1:$C$8=C1)*($D$1:$D$8=D1)*($E$1:$E$8=E1)*($F$1:$F$8=F1))

      Este valor te dirá el número de veces que se repite la combinación exacta de números comparada con el resto de 'cartones'.
      Espero te sirva.

      P.D. por favor, evita escribir en mayúsculas... esto es como si gritaras.

      Eliminar
    2. gracias, probaré.-

      Eliminar
  14. hola.

    tengo un problema con una generacion de numeros al azar.

    lo que necesito es que al tener yo una serie de n digitos, quiero que me aparezcan aleatoriamente, pero que solo aparezcan los numeros de la lista original, por ejemplo:

    345 234 786 354 906 serian los numeros que quiero que aparezcan al azar en una lista, no quiero que aparezca ningun otro.

    es posible realizar esto??

    gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola,
      añade esos valores como rango en algún sitio, y genera un listado como el explicado pero sólo de 5 de longitud (obtendrás de manera aleatoria un rango de 1 a 5), a continuación ( o anidado) aplciar la función INDICE sobre tu rango de números, con posición de fila el aleatorio obtenido.
      Algo así:
      =INDICE(rango5números;aleatorio)

      Saludos

      Eliminar
    2. excelente, solamente edite las ";" por "," y se solucionó.

      gracias!

      Eliminar
    3. ;-)

      el punto o el punto y coma depende de la configuración del sistema.

      Slds

      Eliminar
  15. hola.

    estoy metido en un problema y quiero ver si me puedes apoyar, aparte de que le puede servir a alguien mas.

    necesito preparar unas pruebas para analisis sensorial, las cuales se componen de: muestra problema (A) y muestra referencia (B).

    Para cada muestra tengo 4 códigos (1, 2, 3, 4 para A y 5, 6, 7,8 para B) y necesito hacer las siguientes combinaciones:

    AAB
    BBA
    ABA
    BAB
    BAA
    ABB

    para cada combinacion se requiere identificar las muestras, por ejemplo:

    * para la combinacion AAB una opcion seria tener las muestras 1,3,7 (dos numeros de A y un numero de B)

    * para la combinacion BAB una opcion seria tener las muestras 6,2,5 (un numero de B, un numero de A, y un numero de B)


    ya logré combinar condicionales y formulas para que se me generen las combinaciones, pero en algunas aparecen repetidos los numeros, por ejemplo:

    * para la combinacion BBA una opcion seria 7,7,1 (dos numeros de B y un numero de A)


    ¿habrá alguna manera de poder generar las combinaciones sin que se repitan los numeros?


    gracias por tu apoyo!

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, la verdad no parece nada sencillo el planteamiento que propones.. y mucho menos conseguir los listados de posibilidades para cada combinación (AAB, BBA, etc) con fórmulas!
      Habría que saber, además, si la opcíon 2 3 7 es igual (consideras repetido) a 3 2 7...
      Desde luego creo que habría que emplear programación e intentar lograr una regla que cumpla todos los condicionantes.

      Creo que no puedo ayudarte con esto, lo siento de veras.. pero se me escapa de mis conocimientos.

      Un saludo y mucha suerte

      Eliminar
  16. Hola,
    Antes que nada felicitarte por el foro, la duda que me surgio es la siguiente:

    ¿Se podran generar numeros aleatorios en una celda con diferente intervalos?

    Es decir, por ejemplo generar un numero aleatorio entre el 1 y el 10 pero a la vez entre el 30y 40

    Saludos cordiales

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno.. no sabría decirte si es un aleatorio puro, pero podrías probar para una celda está fórmula:
      =SI(ALEATORIO()>0,5;ALEATORIO.ENTRE(1;10);ALEATORIO.ENTRE(30;40))
      al jugar sólo con ALEATORIOS el factor azar está asegurado.. aunque incluyamos una condición...

      Espero te sirva.
      Slds y muchas gracias por seguirme

      Eliminar
  17. Muchas gracias por tu ayuda,
    Me has dado una idea sobre cómo resolver el problema, sin embargo aquí propongo otra:
    =SI(Y(ALEATORIO.ENTRE(1,30)>11,ALEATORIO.ENTRE(1,30)<=19),ALEATORIO.ENTRE(1,10),ALEATORIO.ENTRE(20,30))
    Claro que como tú dices, tal vez no sea un aleatorio puro sin embargo el factor azar está asegurado.
    El problema surgió ya que debía generar letras aleatorias mayúsculas y minúsculas en un rango determinado utilizando la función CAR, (la cual emite caracteres de acuerdo a números específicos) debido a que las letras mayúsculas se encuentran del 95 al 60 y las minúsculas del 97 al 122, me vi en la necesidad de generar números aleatorios en intervalos diferentes.
    Gracias de nuevo!!!

    ResponderEliminar
  18. Y COMO PUEDO GENERAR CON CUADROS COMBINADOS SIEMPRE ME SALEN REPTIDOS QUE HAGO

    ResponderEliminar
    Respuestas
    1. Hola Jherom, que tal estás?... espero igualmente te encuentres bien.
      No termino de entender qué necesitas... pero para generar número aleatorios deberás emplear la función RND (si trabajas en VBA).

      Saludos cordiales

      Eliminar
  19. SI TENGO UNA SERIE DE NÚMEROS DEL 1 AL 45 COMO HAGO PARA SABER CUANTAS COMBINACIONES ME SALEN DE ESTA, TENIENDO EN CUENTA QUE LAS COMBINACIONES SON DE 6 DÍGITOS ?

    ResponderEliminar
    Respuestas
    1. Hola que tal estás, espero te encuentres también bien.
      para determinar número de combinaciones emplea la función COMBINAT.

      Saludos y gracias por no escribir en mayúsculas.

      Eliminar
  20. AHHH, NO ME ENTERO, SOY NOVEL EN EXCEL.
    Necesito una tonteria, como si fuese la loteria primitiva, en 7 casillas 7 numeros diferentes entre el 1 y 49 y que no se repitan.
    estoy utilizando la funcion aleatoria.entre(1;49) pero me he bloqueado, no se como seguir
    Graciass

    ResponderEliminar
    Respuestas
    1. Hola,
      sólo sigue las indicaciones de este mismo post.
      en la primera columna selecciona sólo 49 fórmulas =ALEATORIO()

      y en la columna C únicamente los 7 valores que necesitas....

      Saludos

      Eliminar
    2. Gracias, ahora si, pero he seleccionado 49 formulas =aleatorio.entre(1;49).
      Creo que funciona igual pero con numeros enteros
      Gracias
      Y felicidades por el foro

      Eliminar
    3. Hola...
      con =ALEATORIO.ENTRE(1;49) cabe el riesgo de tener dos valores repetidos!!

      Slds

      Eliminar
  21. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  22. Buenas gente..!!

    Estoy necesitando un poco de ayuda para resolver una cosita..


    Necesito ordenar unos datos algo asi..pero a su vez cuando se repitan los valores a ordenar utilice el criterio del RANKING para darle ese orden..

    Es decir .. si hay 3 personas con calificacion 100, bueno que se fije en el ranking y ponga 1ro. al de menor rank despues. al 2do y despues al 3ro


    Ranking Nombre Calificacion
    1 Sonia 77
    6 Ignacio 102
    4 Mauricio 68
    3 Roberto 88
    5 Lorena 61
    7 Jorge 98
    2 Brenda 102
    8 Teresa 74

    ResponderEliminar
    Respuestas
    1. Hola Sebis,
      creo que te puede servir la siguiente explicación de cóm utilizar la función JERARQUIA para ordenar un listado de datos
      http://excelforo.blogspot.com.es/2009/10/la-funcion-jerarquia-la-ordenacion-de.html
      Tendrás que modificar ligeramente ( a más fácil) la fórmula explicada.
      Saludos

      Eliminar
    2. Bueno en ese caso se me ocurren un par de alternativas más o menos sencillas.
      Una primer empleando tablas dinámicas y la creación de un campo calculado.
      Seleccionamos el rango A1:C9 (las tres columnas) y creamos nuestra TD, llevando al área de filas el campo Nombre, creamos un campo calculado con la fórmula:
      =Calificacion +Ranking /100
      y finalmente seleccionamos y ordenamos de manera personalizada el campo Nombre, indicando un orden ascendente por el campo nuevo calculado..

      Otra manera con matriciales requeriría, a primera vista, una columna auxiliar con la misma filosofía que la empleada en el campo calculado, por ejemplo en la columna D:
      =SI(CONTAR.SI($C$2:$C$9;C2)>1;$C$2:$C$9+$A$2:$A$9/100;$C$2:$C$9)
      y siguientes...
      Por último en la columna E obtendríamos el orden (numerado):
      =JERARQUIA.EQV(SI(CONTAR.SI($C$2:$C$9;C2)>1;C2+A2/100;C2);SI($C$2:$C$9=C2;$D$2:$D$9);1)

      Espero te sirva alguna de las ideas...
      Saludos

      Eliminar
    3. Lo probe ismael y me sigue sin dar... te puedo pasar adjunto el archivito haber si lo podes ver mejor quiza vos ?=

      muchas gracias desde ya y disculpa la molestia..

      Eliminar
    4. Hola Sebis,
      si, envíamelo a
      excelforo@gmail.com
      Saludos

      Eliminar
  23. Ya lo habia visto y probado ese Ismael.. y si realmente funciona.. pero no en mi caso..
    te explico porque..

    En ese metodo cuando detecta repetidos les asigna el nuevo orden de acuerdo a como esten ordenados en la lista.. al que este mas arriba le da el 1er orden al siguiente que este mas abajo le da el siguiente orden y asi sucesivamente..

    Yo lo que necesitaria es que cuando tenga q dar orden a los repetidos, chequee en la columna "Ranking" y de acuerdo a ella asigne los numeros correspondientes..

    ResponderEliminar
  24. hola como estan!!!! algo que quiero hacer es crear unos numeros aleatorios en el rango 100000 a 999999 sin que se puedan repetir, ya genere los numeros pero se repiten como le puedo hacer para que no pase saludos!!

    ResponderEliminar
    Respuestas
    1. Hola Ari,
      lo primero que deberíamos saber es cuántos números necesitas generar, pero básicamente tendrías que repetir (extrapolando) el proceso de este post...
      Comienza extendiendo el rango con la función ALEATORIO() en un rango de 900000 celdas, y luego aplica la fórmula JERARQUIA ... CONTAR.SI

      Aunque, en este caso, creo lo mejor es apliques lo comentado en
      http://excelforo.blogspot.com.es/2012/09/aleatorios-sin-repeticion-con-macros-en.html

      Slds

      Eliminar
  25. hola, disculpa es posible utilizar esta funcion para formar un formato de horarios de personal?

    ResponderEliminar
    Respuestas
    1. Hola wily,
      no entiendo el planteamiento??? 'formato de horarios'???
      Si puedes poner un ejemplo...
      Saludos

      Eliminar
  26. tengo 7 personas a mi cargo en las cuales tengo que rotarlos diariamente de lunes a domingo, en los que 5 tienen que estar en cada turno, tienen permitido descansar 2 dias en la semana, los cuales deben de quedar un dia de semana y el otro dia fin de semana, abra alguna forma de que un formato de excel de forma aleatoria me los asigne sin que se repitan cada demana?

    ResponderEliminar
    Respuestas
    1. Hola Wily,
      si te refieres a una fórmula (no un formato) que sea capaz de mostrar las diferentes combinaciones/permutaciones posibles diría que no creo posible tal cosa, o si lo fuera sería demasiado complejo...
      Para esto sí se podría aplicar una macro que te diera las diferentes posibilidades de esas 7 personas.

      Existe u código de Myrna Larson fantástico que responde a esto, puedes encontrarlo, por ejemplo en:
      http://answers.microsoft.com/en-us/office/forum/office_2010-excel/code-for-permutations-combinations/7b766aa5-5998-4905-8eb1-522bfe373d25

      Saludos

      Eliminar
  27. Hola!! quiero realizar por ejemplo: 10 combinaciones posibles de entre 6 u 8 numeros, me entiendes!!! que al yo tomar por ejemplo 2, 3 ,4 ,5,7, 8,9, 1, al seleccionar 3 de ellos pueda generar las combinaciones 234, 432, 324, me explico!! es posible hacerlo con excel!?

    ResponderEliminar
    Respuestas
    1. Hola,
      te recomiendo el link que adjunto justo en el comentario anterior, donde puedes ver el código desarrollado por Myrna Larson....
      Lo que quieres sólo sería posible realizarlo con macros.
      Slds

      Eliminar
  28. hola, buen post pero.. me pueden explicar como hacer esto:
    tengo dos grupos de 10 personas y quiero hacer 10 parejas aletorias, pero sin que
    repitan
    Ejemplo:
    pareja1: 1con 12
    pareja2: 7con19
    patreja3: 4con16 y asi pero que no se repitan las parejas aunque sea solo uno un ejemplo de lo que no se pide¨¨¨ pareja1: 1con12 pareja2:1con16 y asi eso no
    porfavor ayudenme es para un examen!

    ResponderEliminar
    Respuestas
    1. Hola,
      para tal cosa sería necesario algo de programación...
      Hay una autora especialista en temas de combinaciones/permutaciones (sobre lo que tú estás preguntando) llamada Myrna Larson
      Puedes ver una de sus macros en
      http://www.teachexcel.com/excel-help/excel-how-to.php?i=223306

      Ahí encontrarás el código para mostrar las combinaciones...

      Espero te oriente.
      Saludos

      Eliminar
  29. necesito generar na formula del 1 al 24 que me tire valores alertorios sin que se repitan al azar agradecería una función es para la escuela gracias

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      sólo sigue las indicaciones del post... ajustando a 24 valores.
      Saludos cordiales

      Eliminar
  30. Hola, buenos dias, me gustaria saber si existe algo que de alguna manera me repartiera una lista de valores entre cierta cantidad de individuos, es decir, si tengo 10 valores, me los reparta entre 4 individuos, mas sin embargo, al siguiente sorteo, al que le toco mas o menos cantidad, le toque de manera que se vayan igualando.

    Ejemplo: valores.. 20,50,20,35,64,95,82,67,64,85 repartidos entre A,B,C,D.

    Teoricamente quedarian:

    A: 3 valores
    B: 3 valores
    C: 2 valores
    D: 2 valores

    pero al siguiente quedaran:

    valores.. 20,50,20,35,64,95,82,67,64,85,58,96,48,72 repartidos entre A,B,C,D

    A: 3 valores
    B: 3 valores
    C: 4 valores
    D: 4 valores

    de esta manera se darian equitativamente los repartos.

    ResponderEliminar
  31. Hola qué tal. Antes que todo, muchas felicitaciones por el foro.

    Mi duda es la siguiente:

    Somos 20 empleados y estoy tratando de crear una tabla para designar 5 tareas, 2 de ellas designadas por mi (escogidos de una lista desplegable con los nombres de todos los empleados), pero quiero que las otras 3 sean establecidas de manera aleatoria, escogiendo de los empleados que no participaron en las tareas que fueron designadas previamente (desde ahora Tarea1 y Tarea2, respectivamente).
    por ejemplo:

    Si designé a Empleado1 realizar Tarea 1 y Empleado2 realizar Tarea 2, quiero que las tareas 3,4 y 5 sean realizadas por cualquier empleado menos Emplado1 y Empleado2 y que tampoco un empleado elegido de manera aleatoria, realice otra de las tareas que se designan de manera aleatoria, es decir, si Empleado3 salió escogido para realizar Tarea3, que no pueda ser designado para tareas 4 o 5, o que Empleado4 (escogido aleatoriamente) y que realiza Tarea4, haga tareas 3 o 5 (lo mismo para un empleado escogido para Tarea5)

    Las designaciones de las tareas 1 y 2, son establecidas al principio del día.

    Espero que se entienda y que puedan ayudarme. Tengo la sensación de que hay que usar una función "INDICE" con argumentos variables o función "BUSCAR" (en algunas de sus variantes vertical u horizontal) con argumentos variables.

    Muchas gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola y muchas gracias!
      hacerlo con funciones sería tedioso (básicamente habría que seguir el modelo arriba planteado).
      Quizá en este caso sea más útil lanzar un proceso (macro) que vaya completando las tareas para cada empleado teniendo en cuenta las previamente asignadas...
      Le daré una pensada e intentaré subir un post explicando.

      Saludos

      Eliminar
    2. Te estaría eternamente agradecido.

      Saludos!

      Eliminar
  32. Buenas a tod@s,
    Primero, felicitar por el foro, un gran pozo de sabiduría.
    Llevo bastante dándole tumbos a un asunto y no consiguió resolverlo. Os cuento:
    Estoy desarrollando un diseño experimental: tengo 100 objetos ordenados de forma aleatoria ( =ALEATORIO() ) y mi “deseo” es obtener 10 grupos generados aleatoriamente de 10 objetos, sin repetición, es decir, repartir los 100 objetos de forma aleatoria en 10 grupos de 10.
    Alguna idea, sugerencia o solución???
    Un abrazo y muchísimas gracias de antemano.


    ResponderEliminar
    Respuestas
    1. Muchas gracias Mark,
      es un gran esfuerzo personal mantener este blog y poder ayudar a todos los que llegan con alguna duda.
      En cuanto a tu pregunta, te diré que nunca es fácil trabajar con aleatorios (puedes leer varios post al respecto en este mismo blog).
      Una posibilidad que se me ocurre sería plantear algo similar a lo expuesto en esta entrada:
      http://excelforo.blogspot.com.es/2015/03/vba-listas-aleatorias-sin-repeticion.html
      la idea sería ir construyendo cada grupo de 10 teniendo en cuenta los valores de cada grupo anterior.. para ir limitando 'aleatorio' a cada paso...
      Espero te oriente de alguna manera
      Un cordial saludo

      Eliminar
    2. Gracias por la celeridad!
      Estuve mirando la aplicación de macros... y la verdad no lo he conseguido... aun me queda mucho camino para las macros.

      Aun así, apliqué un camino un poco más tedioso pero que para mi diseño, cumple todos los parámetros de aleatoriedad... la forma es bastante ruda:
      1. Genero aleatorios para todos los objetos (100): doy un ordinal en base a éstos, ordeno menor a mayor y escojo los 10 primeros y les asigno "grupo1"
      2. Los 90 restantes, genero aleatorios para todos (90): doy un ordinal en base a los aleatorios, ordeno y escojo los 10 primeros y les asigno "grupo2"

      y así hasta obtener los 10 grupos.

      No es una formulación de un sólo paso pero bueno!

      Nada, agradecer la ayuda y un saludo para todos!!!!

      Nos vemos por el foro!

      Eliminar
    3. ;-)
      bueno.. un procedimiento válido igualmente...
      En esencia la macro hubiera hecho el mismo proceso.
      Un saludo

      Eliminar
  33. Hola Ismael, haber si me puedes ayudar. Tengo en la celda G4 esta fórmula =INDICE(Newlist;ALEATORIO.ENTRE(1;CONTAR(Newlist))) donde me genera un valor aleatorio dentro del rango Newlist, pero por ejemplo yo lo que quiero es usar en celda G5 esa misma fórmula y que me escoja aleatoriamente dentro del rango Newlist un valor pero que esta vez sea distinto del valor de G4. ¿Qué tendría que modificar en la fórmula ó qué otra cosa tendría que hacer para conseguir para que me generen valores aleatorios dentro del rango Newlist sin que G4 y G5 sean iguales? Saludos!!

    ResponderEliminar
    Respuestas
    1. Gracias por la ayuda Ismael, pero no es lo que yo andaba buscando. Me he expresado mal anteriormente, yo no quiero generar numeros aleatoriamente, lo que busco es SELECCIONAR números aleatoriamente que estén dentro de un rango.
      Ejemplo:
      Supongamos el rango B2:B10 donde cada celda contiene un número:

      B2 contiene 13
      B3 contiene 34
      B4 contiene 7
      B5 contiene 25
      B6 contiene 19
      B7 contiene 28
      B8 contiene 30
      B9 contiene 11
      B10 contiene 44

      Por ejemplo yo tengo 2 celdas siguientes: C1 y C2

      Yo lo que quiero saber es cómo se puede hacer por ejemplo para que al pulsar un botón me seleccione aleatoriamente un número que esté dentro del rango B2:B10 y lo coloque en C1 y otro número en C2 y que no se repitan. ¿Se puede hacer? Gracias. Saludos.

      Eliminar
    2. Al final generar aleatorios sirve para varios fines.. si obtienes un aleatorio entre 1 y 9 (un número para cada celda de B2 a B10) luego podrías sacar el valor de la celda que quieres.

      Echa un vistazo a
      http://excelforo.blogspot.com.es/2012/09/aleatorios-sin-repeticion-en-excel-sin.html

      una vez tengas dos valores en C1 y C2 (valores de 1 a 9 sin repetirse) podrías sacar con una función
      =INDICE(B2:B10;C1)
      y
      =INDICE(B2:B10;C2)
      para conseguir los valores que buscas.
      Saludos

      Eliminar
  34. Buenas noches. Porfa, si me pudieras ayudar. Quiero crear una macro donde haga clic a un botón y me lleve ALEATORIAMENTE a una hoja cualquiera del mismo libro. Mil gracias!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías crear una variable que devuelva un aleatorio entre 1 y el número de hojas del libro (WorkSheets.Count).. emplea la función Randbetween, por ejemplo...

      Saludos

      Eliminar
  35. buenas tardes
    para ver como podria aleatorizar una secuencia de numeros consecutivos y que formula es la mas adecuada para la obtencion de esto .

    ResponderEliminar
  36. Es posible generar números aleatorios con un rango definido que promediados tenga una media conocida, un ejemplo:
    Obtener 10 números aleatorios que van de 50 a 120 y que promediados estos 10 números obtenga 70.

    Luis

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      ¿que tal estás?, un placer saludarte igualmente...
      Parece perder el sentido de aleatorio si ya estamos condicionando el resultado que debe salir... pero podrías añadir en las 10 celdas la fórmula:
      =70+ALEATORIO.ENTRE(-2;2)
      La media que obtengas deberá ser muy cercana a 70...
      Espero te sirva.
      Un cordial saludo

      Eliminar
  37. Gracias por la entrada, que me ha resultado de gran ayuda.

    Sin embargo, tengo una necesidad que puede complicar un poco las fórmulas (ignoro cuanto).

    Lo que preciso es sortear 20 premios entre 100 personas (valores de ejemplo), pero de manera secuencial. Es decir: el primer premio a repartir entre las 100 personas. Pero para el segundo premio hay que descontar a la persona que ha sido agraciada (y así sucesivamente). Todas las personas tienen las misma probabilidades de ganar el primer premio, pero a medida que el sorteo avanza, las posibilidades para las personas no premiadas aumentan (porque las premiadas han sido excluídas). Vaya, un sorteo de toda la vida ...

    ¿Cómo puedo hacer esto en excel aprovechando las funciones descritas? ¿han de emplearse otras funciones?.

    Muy agradecido de antemano,

    ResponderEliminar
  38. saludos
    necesito
    combinar numeros
    1 al 10 sin repetir en 6 espacios y saber cuantos combinaciones salen
    ejemplo
    1,2,3,4,5,6,
    1,2,3,4,5,7
    1,2,3,4,5,8
    pero yo tengo los valores de 1= 10; 2=67; 3= 45
    ¿Cómo puedo hacer esto en excel aprovechando las funciones descritas? ¿han de emplearse otras funciones?.

    Muy agradecido de antemano,

    ResponderEliminar
  39. Hola Ismael, un gusto saludarte y beneficiarme de tus buenas explicaciones. Quisiera que me ayudaras con un problema que tengo.
    Resulta que tengo 6 números, cada uno en su Celda, y quisiera que esos valores se cambiaran de celda(alternarlos entre si); ya sea con una Macro o con Formulas Internas.
    Gracias y espero respuestas. Emoticono grin

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      entiendo quieres que se cambien de posición dentro del mismo rango de manera aleatoria...
      si es así, tienes al menos dos opciones:
      1- con programación (algo más complejo)
      2-la segunda es repetir lo expuesto en este post para esos seis valores...
      saludos

      Eliminar
    2. Si, Muchas Gracias. Cree una columna con la formula del post y ordené mis 6 números en función de esa fila de números aleatorios. Me sirvió.

      Eliminar
  40. Buenas como puedo hacer una tabla aleatoria del 1 al 10 en una tabla 10 x 10 y como saber que fila se repite gracias.

    ResponderEliminar
    Respuestas
    1. Hola que tal?
      en esencia sería repetir el proceso descrito en el post pero para un rango de 10x10...

      Otra cosa es saber dentro de esa tabla cuando se repiten todos los valores de la fila completamente..
      No queda claro qué necesitas ;-)
      Saludos

      Eliminar
  41. Si amigo lo que en realidad quiero es que me expliques porque en realidad no se nada de excel, disculpa mi ignorancia, quisiera primero saber como hacer el aleatorio en una tabla 10*10 y después como saber si en alguna fila se repite el mismo orden de otra fila. Gracias!

    ResponderEliminar
    Respuestas
    1. Bien, es un tema bastante complicado el que planteas...
      trataré de subir una explicación al blog del asunto.
      Saludos

      Eliminar
  42. Ismael, Si me puedes colaborar, quiero generar un numero aleatorio usando Aleatorio.Entre(X,X), pero la condición es que este presente cierta información de TEXTO en otra celda. (El Aleatorio.Entre (dependerá de la celda que tiene el texto) Esto se tiene que repetir 12 veces en la misma columna. Te pongo el ejemplo y me indicas si es posible, El numero a generar seria el que va debajo de Qty y como indique, el rango depende de lo que encuentre en la columna Detalle.

    Fecha Detalle Qty Precio
    10/1/15 Asado $1.50
    10/1/15 Empanada 2.50
    10/1/15 Café 1.00
    10/1/15 Refresco 1.50
    Total 12 entradas con la misma fecha
    10/2/15 (se repiten 12 entradas con distinta fecha, los mismos productos en "Detalle" y los mismos precios)

    Cada producto bajo Detalle tiene diferente rango, el cual se aplicara en Qty, y al pasar la fecha al día siguiente, se repite el proceso.

    Gracias,

    ResponderEliminar
    Respuestas
    1. Hola,
      si la disposición de los elementos de 'Detalle' son coincidentes para cada tramo de 12 entradas, sí sería posible...
      Escribiré un post y lo subiré al blog en cuanto me sea posible.
      Saludos

      Eliminar
    2. Las 12 entradas en "detalle" siempre son las mismas, aunque no todo el tiempo se mostraran las 12 en la misma fecha, es posible que un día solo salgan 6 de las 12 pero esas 6 son parte de las 12 originales, por eso es que yo pensaba que si en la celda QTY se podría poner una condición de ver si "Detalle" esta vaciá, y si no que verifique si detalle es igual a una de las 12, y si es igual a digamos Café, que me de un numero aleatorio entre (x,y) - Me gustaría tener control de la información en (x,y) para cada uno de los 12 productos.

      Para tu información, tengo una tabla llamada Precios la cual utilizo para introducir los productos que están bajo "Description" y los Precios que están asociados con los productos. Para este ejercicio, primero introduzco la misma fecha 12 veces, después pongo los 12 productos y los 12 precios, después pongo las cantidades vendidas por día. Gracias.

      Eliminar
    3. ok,
      lo tendré en cuenta en la publicación de la explicación.
      Slds

      Eliminar
  43. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  44. Hola! me podrias resolver este ejercicio de Excel de método Monte carlo de números aleatorios?
    "crear 100 números aleatorios entre 1 y 3 inclusive y que la probabilidad del numero 1 sea de 0.5. La del numero 2 0.3 y la del numero 3 lo restante(0.2)"
    Millones de gracias!!

    ResponderEliminar
    Respuestas
    1. Hola Iara,
      cuál es la duda que te surge en la aplicación de técnicas en Excel??
      Saludos

      Eliminar
  45. saludos, por favor tendras en excel un archivo formulado que sirva confiablemente para las loterias de 4 cifras y 3 cifras, se alimente de resultados anteriores y su resultado sea ganador? de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      lo siento, no dispongo de tal cosa.
      Tendría que desarrollarlo, pero ten presente que no existe un sistema que sea capaz de generar una combinación mágica...
      recuerda que este tipo de juegos son de AZAR.
      Saludos

      Eliminar
  46. hola como puedo hacer una combinacion de numeros del 1 al 69. que sean de cinco numeros. sin que se repita cualquier numero ya combinado ejemplo
    1 5 6 8 9 o 45 62 95 32 85 culquiera de estos numeros ya no se podra combinar con ningun otro

    ResponderEliminar
    Respuestas
    1. Hola Agustín,
      el asunto es bastante complejo.. casi con toda seguridad hubiera que trabajar con programación, descartando cada nueva combinación que incumpla cualquiera de las condiciones:
      1-que repita números dentro de la secuencia de cinco números
      2-que repita la misma combinación que una anterior
      3-que repita algún número de otra combinación!
      Slds

      Eliminar
  47. es de mucha ayuda su portal y no esta por demás darle las gracias. Mi pregunta es : cómo se genera números aleatorios en excel sin repetir y en orden ??? claro esta que en un rango dado (45) y tal vez en varias columnas para usar el buscador ....

    ResponderEliminar
    Respuestas
    1. Hola Monis,
      si hablamos de aleatorios, sin repetición y en orden, casi pierden esa característica de aleatorios, ya que deben cumplir varias condiciones...
      Lo ideal sería obtener un listado como el del post y luego ordenarlos...
      :'(
      Slds

      Eliminar
  48. Buen dia Ismael, tengo una consulta a ver si me podrias ayudar
    Tengo una lista de numeros.
    Quiero buscar en esta lista cuantas veces se repite un numero, pero del derecho y al reves, por ejemplo:
    Cuantas veces existe en esta lista el numero: 123 o sus similares, 132, 321, 213, 231, 312, 321

    ResponderEliminar
    Respuestas
    1. Hola,
      el asunto parece pasaría por ir recorriendo cada una de las celdas del rango donde comparar el valor 'buscado', realizando conteos de cada dígito de manera individual.. esto es, descomponiendo cada valor en sus tres dígitos y realizando la búsqueda sobre la descomposición del valor buscado.
      Ejemplo, sobre el valor buscado: 123
      descompuesto en los dígitos 1-2-3
      para cada celda del rango o lista de números, añadimos una columna con la fórmula:
      =Y(ENCONTRAR(1;$A2)>0;ENCONTRAR(2;$A2)>0;ENCONTRAR(3;$A2)>0)
      A2 sería el valor de la lista (321, 456, 987, etcc)
      Si es VERDADERO es que los tres dígitos aparecen..
      luego simplemente puedes contar el número de VERDADEROS
      Saludos

      Eliminar
  49. Hola buenos dias.
    Mi pregunta es... como hago un sorteo de 40 personas en excel y que todas queden en diferente sitio ??
    Gracias de antemano.
    saludos

    ResponderEliminar
    Respuestas
    1. Hola José,
      asigna a cada persona un Id (desde 1 a 40).
      Extiende el rango del ejemplo hasta 40 (ahora este en 20)...
      luego, sobre el rango obtenido (columna C) a cada valor obtenido sin repetición le asocias su correspondiente nombre.
      Saludos

      Eliminar
    2. Hola buenas tarde.
      Gracias por contestar,pero no tengo ni idea de excel,si me puedes poner una formula o ejemplo te lo agradeceria.
      Puede que no me haya expresado bien, lo que tengo son 40 nonbres de personas en la cual van a participar en una competicion, y les tengo que aignar un puesto en la competicion al azar,o sea que la lista de inscripcion que tengo quede totalmente distinta.
      Gracias

      Saludos

      Eliminar
    3. =ALEATORIO.ENTRE(1;40) con esta fórmula podrás enumerar tu lista.

      Eliminar
    4. Hola José,
      replica el ejemplo de este mismo post.. con la modificación que te comenté anteriormente, y luego por cada número único entre 1 y 40 obtenido, asignas un nombre.
      En el listado del ejemplo, en la columna C, obtendrás una serie de números aleatorios (4,6,1,35,6, etc...).
      Previamente sobre tu listados de nombres has realizado una asignación de Id:
      1 - fulanito
      2 - menganito
      3 - zutanito
      etc

      así, sencillamente después de obtener el listado de números, buscas la relación

      Slds

      Eliminar
    5. Ok.muchas gracias por todo.Solucionado
      Saludos

      Eliminar
  50. Hola excelforo quisiera saber como combinar 5 variables en todos los modos posibles

    ResponderEliminar
    Respuestas
    1. espero pueda ayudarme

      Eliminar
    2. Hola Cinthia,
      para eso lo mejor es plantear la macro de Myrna Larson
      http://answers.microsoft.com/en-us/office/forum/office_2010-excel/code-for-permutations-combinations/7b766aa5-5998-4905-8eb1-522bfe373d25?auth=1
      en ese link encontrarás el código de la macro.
      Espero te sirva
      Saludos

      Eliminar
  51. hola, consulta, como hago para ordenar una combinación de 10 números generados usando la funcon aleatoria y jerarquia, yo quiero que me muestre esos 10 numeros ordenados de menor a mayor

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que generar en otro rango aparte una fórmula para que te tome en el orden que necesites (Asc o Desc) esos números aleatorios

      Echa un vistazo a
      http://excelforo.blogspot.com.es/2009/10/la-funcion-jerarquia-la-ordenacion-de.html

      Espero te oriente
      Saludos

      Eliminar
  52. Estupenda forma de hacer aleatorios sin duplicados.
    ¿Se podría hacer esto mismo de tal manera que no se modifiquen los números cada vez que actúo sobre la hoja? Tiene una explicación esta pregunta: he de realizar un sorteo, en una determinada hoja, sobre la participación de equipos en un torneo. Los números resultantes habrán de tener relaciones en otras hojas y no deben cambiarse cada vez que operemos en cualquiera de ellas.

    ResponderEliminar
    Respuestas
    1. Hola Neida...
      siempre podrías, una vez generado los valores, copiar y pegar como valores...
      ;-)
      Saludos

      Eliminar
  53. Hola!, una pregunta, se puede usar este método de igual manera con la función =CARACTER ?

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      supongo te refieres a utilizar el resultado descrito en el post anidado dentro de CARACTER...
      Si es así, la respuesta es afirmativa...
      solo ten presente que la función CARACTER admite como valor máximo el 255
      Slds

      Eliminar
  54. Hola requiero tu apoyo con una lógica que me permita conocer las siglas que faltan de un abanico de posibilidades de 8 siglas; por ejemplo: en la columna "A" estarán nombres de personas (que pueden repetirse pues deben estar por cada una de las 8 siglas antes mencionadas) y en la columna "B" se le asignan una de las 8 siglas según el requerimiento del momento, y lo que necesito es que en la columna "C" me indique cuales de las restantes siglas el faltan, tomando en cuenta las que ya puedan tener ese mismo nombre dentro de la columna, algo asi:
    Siglas leyenda Nombre Siglas asignadas
    x Pedro Perez x Le faltan: y z b c
    y Maria Perez y le faltan: x a b c
    z Joseito Perez z le faltan: x y a b
    a Pedro Perez a
    b Maria Perez z
    c Joseito Perez c

    ResponderEliminar
    Respuestas
    1. Hola,
      yo optaría por un SI condicional, que prueba la búsqueda con un CONTAR.SI.CONJUNTO de las combinaciones de nombre y siglas, finalizando con un CONCATENAR para unificar las siglas que faltan...
      En este caso, al ser solo ocho siglas, optaría por la formulación estándar, aunque una UDF con VBA sería otra opción

      En cuanto pueda escribiré un post con una posible solución.

      Eliminar
  55. buenas noches, tengo una duda sobre una tabla que estoy realizando para un contro de copias e impresiones, es como puedo hacer para que me aparezca un resultado si en unas celdas hay cierto tipo de dato, ya sea texto o numero.
    la formula que realice fue esta;
    =SI(Y(ESNUMERO(E4);H4="domingo";ESNUMERO(L4);ESTEXTO(Q4));"Ingrese Total Scanner";SI(Y(ESNUMERO(E4);ESNUMERO(H4);L4="domingo";ESTEXTO(Q4));"Ingrese Total Scanner";SI(Y(ESNUMERO(E4);ESNUMERO(H4);L4="domingo";ESNUMERO(Q4));I4+M4+Q4;SI(Y(ESTEXTO(E4);H4="Primero CF";ESTEXTO(L4);ESBLANCO(P4));"Ingrese Contador Copias";SI(Y(ESNUMERO(E4);H4="domingo";ESNUMERO(L4);ESNUMERO(Q4));I4+M4+Q4;SI(Y(ESNUMERO(E4);H4="domingo";L4="domingo";ESNUMERO(Q4));I4+M4+Q4;SI(Y(E4="domingo";H4="domingo";ESNUMERO(M4);ESBLANCO(P4));"Ingrese Total Scanner";SI(Y(E4="domingo";H4="domingo";ESNUMERO(M4);ESNUMERO(Q4));I4+M4+Q4;SI(Y(E4="domingo";H4="domingo";L4="domingo"; ESNUMERO(Q4)); I4+M4+Q4;SI(Y(E4="domingo";H4="domingo";L4="domingo";Q4=0);0;SI(Y(E4="domingo";H4="domingo";L4="domingo");"Ingrese Total Scanner";SI(Y(E4="domingo";H4="domingo");"Ingrese Contador Impresiones";SI(E4="domingo";"Ingrese contador Amp-red";SI(Y(ESTEXTO(E4);ESTEXTO(H4);ESTEXTO(L4);ESTEXTO(Q4));"";SI(Y(ESNUMERO(E4);ESNUMERO(H4);ESNUMERO(L4);ESNUMERO(Q4));I4+M4+Q4;SI(Y(ESNUMERO(E4);ESNUMERO(H4);ESNUMERO(L4));"Ingrese Total Scanner";SI(Y(ESNUMERO(E4);ESNUMERO(H4));"Ingrese Contador Imp";SI(ESNUMERO(E4);"Ingrese Numero Amp";SI(Y(ESTEXTO(E4);ESTEXTO(H4);ESTEXTO(L4));"Ingrese Contador Copias";SI(Y(ESTEXTO(E4);ESTEXTO(H4));"Ingrese Contador Copias";SI(ESTEXTO(E4);"Ingrese Contador Copias";"")))))))))))))))))))))
    mi pregunta es si puede hacer esto mediante alguna formula que coja las celdas seleccionadas y me haga las posibles combinaciones.
    Cordial saludo
    Juan Camilo Cardona

    ResponderEliminar
    Respuestas
    1. Hola,
      me pierdo con la fórmula.. habría que verla en su contexto.
      Te puedo decir que para evitar error al sumar celdas de texto con numéricas, lo mejor es que emplees la función SUMA y no que sumes celda a celda, esto es, mejor:
      SUMA(I4;M4;Q4) que
      I4+M4+Q4
      En el primer caso te sumará los valores numéricos incluso aunque haya texto en alguna de esas celdas.
      En cuanto a tu pregunta final:
      si puede hacer esto mediante alguna formula que coja las celdas seleccionadas y me haga las posibles combinaciones
      La respuesta es NO, hay que montarla combinando las opciones...

      Otra cosa es desarrollar una macro

      Slds

      Eliminar
  56. Buen dia. Excelente pagina. Felicidades.
    Me podrias por favor ayudar con los siguiente:
    Tengo un listado el cual tiene en la columna "A" Departamento y en la columna "B" Numero de empleado.
    Quisiera que poner en una celda el numero de Departamento y que en la otra celda arroje el numero de empleado.
    y que ademas en las siguientes celdas hacia abajo, me arroje los demas empleados que estan en el mismo departamento.
    me podrias por favor comentar que formulas y como utilizarlo por favor?
    Gracias de antemano.
    Rafa.

    ResponderEliminar
    Respuestas
    1. Hola Rafael,
      Supongo para un mismo departamento habrá muchos empleado (cada uno con su número de empleado).. por lo que entiendo que lo primero que necesitas es contar el número de empleados por cada Departamento... como es algo complejo hacerlo mediante fórmulas, te recomendaría (quizá te sirva) construir una tabla dinámica sobre el listado de tus dos columnas A y B.
      LLevas el campo Depto al área de filas y debajo de este el campo de Empleados...
      Finalmente lleva el campo Depto al área de valores y lo resumes por Recuento.
      Finalmente podrás ver, o si lo prefieres filtrar, por departamento y ver el listado de empleados y en el Total general el conteo...

      Espero te sirva
      Slds

      Eliminar
    2. Excelente, Muchas Gracias.
      Saludos!

      Eliminar
  57. Cordial Saludo!
    Gracias por su amable colaboración.
    He realizado el ejemplo anterior y funciona muy bien.
    Pero necesito realizar lo siguiente:
    Que por ejemplo en la celda C2 siempre me arroje un numero pero que no se repita.
    Entiendo que en el ejemplo anterior de C2 a C21 no está repitiendo números.
    Yo lo que realmente necesito es que en UNA UNICA CELDA, (no importa que, o cual celda sea) si empre me arroje un numero diferente, sin repetir.
    Y si es posible luego de que haya cumplido el ciclo vuelva y repita un ciclo.
    espero haberme hecho entender.
    Muchas Gracias por su amable ayuda.

    ResponderEliminar
    Respuestas
    1. Hola,
      el problema que planteas exigiría, en algún lugar, unas celdas que registren qué valores han ido saliendo, para determinar si está o no repetido.
      Lo más probable es que una macro que condicione la existencia del número resuelva la cuestión.
      Echa un vistazo a:
      http://excelforo.blogspot.com.es/2015/03/vba-listas-aleatorias-sin-repeticion.html
      Saludos

      Eliminar
  58. Buenas, necesito generar 5 columnas con números aleatorios en un rango de 1-1000, la primera columna con numero aleatorios sin repetición del 1-200; la segunda columna del 201-401 y así sucesivamente, ¿como podría hacerlo?

    ResponderEliminar
    Respuestas
    1. Hola,
      sigue las indicaciones de este post, pero para cada columna empezando a sumar el resultado de la fórmula contar dada desde 0, 200, 400, 600, etc

      Saludos

      Eliminar
  59. Hola. Tengo una macro q me genera combinaciones de 50 numeros de 1 al 50 y necesito generar unas 10000 combinaciones y la pc demora unas 2 horas en generar las 10000 combinaciones tengo un pentium con 1 gb de memoria que recomendas q cambie o agregue para q sea mas veloz? Saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      parece un equipo 'justito' de potencia... prueba con Access para generar el listado.
      Echa un vistazo
      http://excelforo.blogspot.com.es/2014/06/combinaciones-con-access.html
      Saludos

      Eliminar
    2. Es q tengo una macro hecha q a su vez me compara las listas generadas con otras, nose si pueda hacer edo en access, he leido acerca devla memoria cache, hay algo q se pueda configurar ahi para q el proceso sea mae rapido? Gracias

      Eliminar
    3. Hola Pablo,
      obviamente si tienes un buen equipo (quad core con mucha ram procesador > i5...) el proceso irá más rapido.
      Puedes configurar el número de procesadores por defecto que se emplea:
      Opciones de Excel > Avanzadas > sección Fórmulas
      aunque esto normalmente está ya optimizado.

      Quizá desde Windows puedas derivar parte de la cache a estos procesos de Excel.

      Saludos

      Eliminar
  60. COMO HAGO UN SORTEO DE TRES FECHAS Y UTILIZANDO ALEATORIO PARA VARIANTES

    ResponderEliminar
    Respuestas
    1. Hola fabi.calderon,
      cómo estás?, un placer saludarte igualmente

      Tendrías que replicar el ejemplo de post para un listado de tres valores.. determinando las fechas posibles

      Saludos cordiales

      Eliminar
  61. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  62. Hola, desearia saber como crear en excel un formato de torneo aleatorio, donde existan 2 columnas y las combine aleatoriamente una en la otra sin repetir, es posible hacer esto sin repetir datos y solo ingresando la informacion gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      existen opciones de combinar elementos entre sí (todas las combinaciones posibles)... si además quieres considerar la aleatoriedad la cosa se complica
      :(
      habría que tratar combinaciones de a dos

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.