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.





69 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. 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