lunes, 9 de julio de 2012

Ordenar Automáticamente una Lista

"Tengo una tabla con cifras de ventas de distintas zonas y me gustaría generar otra tabla idéntica que me ordenara automáticamente de mayor a menor dichas zonas en función de las ventas logradas".

Vamos a ello. Utilizaremos tres funciones, a saber: K.ESIMO.MAYOR, INDICE y COINCIDIR. Partimos del siguiente ejemplo:

Lo que queremos conseguir es que tras realizar las fórmulas necesarias obtengamos una copia de la lista original pero ordenada en base a la cifra de ventas:

Lo primero que vamos a hacer es crear nombres para los dos rangos que utilizaremos dentro de nuestras fórmulas. Para ello seleccionamos el rango B3:B13 y en el cuadro de nombres (a la izquierda de la barra de fórmulas) escribimos el nombre ZONA. Hacemos lo mismo con el rango C3:C13 y le damos el nombre VENTAS.

Nos situamos ahora en la celda G3 y escribimos la siguiente fórmula:
=K.ESIMO.MAYOR(ventas;E3)   y copiamos dicha fórmula hasta la celda G13.


La función K.ESIMO.MAYOR  devuelve el k-ésimo mayor valor de un conjunto de datos. En nuestro ejemplo el conjunto de datos es el rango VENTAS y al indicarle como segundo argumento de la función E3 le estamos pidiendo el primer valor mayor de dicho conjunto (y así sucesivamente en la celda G4, G5, etcétera). De esta manera obtenemos una lista ordenada de mayor a menor de las ventas. Ya sólo nos queda colocar a la izquierda la zona con la que se corresponden dichas ventas.

Nos situamos ahora en la celda F3 y escribimos la fórmula:
=INDICE(zona;COINCIDIR(G3;ventas;0))


La función COINCIDIR nos proporciona el número de fila en el que se encuentra cada cifra de ventas en la tabla original. Introduciendo este dato en la función INDICE como argumento de número de fila ya tenemos resuelto el problema de localizar la zona con la que se corresponde cada cifra de ventas.

Ahora, cada vez que modifiquemos alguna cifra en la tabla original la tabla de la derecha se reorganizará automáticamente. Puede comprobarlo escribiendo, por ejemplo, 400 en Galicia y 600 en Madrid. El resultado será esta otra tabla:

67 comentarios:

  1. Hola, de primera intención no funcionó el procedimiento indicado ya que en la primera columna (en la cual están los valores a ordenar) tenía varios "100%" y 0%; por lo cual, al ejecutar el procedimiento, sí me aparecía (por ejemplo) tres veces el "100%" en tres filas, pero jalaba los mismos datos a las demás columnas de las tres filas. ¡Pero lo pude resolver! :D Agregué dos columnas más; una con los números 0.00000000001, 0.00000000002, 0.00000000003, etc y en la segunda sumé la columna con los porcentajes a ordenar con la anterior; de tal manera que los tres 100% se convirtieron en 100.00000000001%, 100.00000000002% y 100.00000000003%; obviamente el valor se muestra igual a 100% ante nuestros ojos, pero para el Exel ya son diferentes y en consecuencia las tres filas aparecen con todos los datos diferenciados ¡:D!

    ResponderEliminar
  2. Hola, Gracias por la información, pude realizar el ordenamiento, el único inconveniente surgió cuando en las VENTAS existian dos o mas numeros iguales, el nombre de ZONA que muestra es siempre el primero que encuentra. Como puedo solucionar esto? muchas gracias por la info..!!

    ResponderEliminar
  3. Que buena didáctica y que bien ejemplificada la explicación, estos aportes son muy importantes. Y las personas que tenemos la ventaja de trabajar en alguna empresa de servicios informáticos Barcelona o consultoría informática, destacamos esto, ya que es muy importante entender el buen uso y funcionamiento de las herramientas informáticas.

    ResponderEliminar
  4. Muchas gracias, realice la hoja para unos amigos de una peña de pesca y fliparon.gracias

    ResponderEliminar
  5. Encantado de ser de utilidad. Un saludo

    ResponderEliminar
  6. y como lo ordenariamos en caso de que existiesen valores duplicados en la columna ventas?

    ResponderEliminar
    Respuestas
    1. Necesito que me indiques qué criterio quieres seguir para ordenar los valores duplicados.

      Eliminar
  7. siguiendo tu mismo ejemplo y criterios, pero con resultados duplicados en ventas,
    =K.ESIMO.MAYOR(ventas;E3) devuelve perfectamente ordenado las ventas, pero
    =INDICE(zona;COINCIDIR(G3;ventas;0)) devuelve duplicado el nombre de zona


    ZONA VENTAS Nº ZONA VENTAS
    GALICIA 624 1 MADRID 810
    MADRID 810 2 CANARIAS 702
    CATALUÑA 505 3 GALICIA 624
    LA RIOJA 386 4 VALENCIA 600
    VALENCIA 600 5 CATALUÑA 505
    ARAGON 400 6 CATALUÑA 505
    ASTURIAS 505 7 BALEARES 430
    CANTABRIA 356 8 ARAGON 400
    PAIS VASCO 400 9 ARAGON 400
    ANDALUCIA 345 10 LA RIOJA 386
    BALEARES 430 11 CANTABRIA 356
    CANARIAS 702 12 ANDALUCIA 345

    ResponderEliminar
    Respuestas
    1. Te he mandado un mail con la solución utilizando las funciones JERARQUIA y CONTAR.SI para establecer un orden dentro de los repetidos y después lo resolvemos de nuevo con INDICE y COINCIDIR.

      Eliminar
    2. buen día, se me presento el mismo problema, será posible que me envíes al correo felipe0930@gmail.com lo mismo del compañero al que le respondiste:


      "Te he mandado un mail con la solución utilizando las funciones JERARQUIA y CONTAR.SI para establecer un orden dentro de los repetidos y después lo resolvemos de nuevo con INDICE y COINCIDIR."

      muchas gracias por su ayuda.

      Eliminar
    3. Hola Andres Felipe. Tienes la solución en mi penúltimo post: Ordenar Automáticamente una Lista con Valores Repetidos. Un saludo

      Eliminar
    4. Hola, yo también tengo esa misma duda, me puedes enviar la respuesta a mi correo rulos7@gmail.com
      Gracias

      Eliminar
  8. Ante todo gracias por el aporte, me ha sido de mucha utilidad. Por favor me puedes brindar también la solución en caso de que existiese valor duplicado en la columna de ventas?.

    ResponderEliminar
    Respuestas
    1. Sí pero necesito un mail donde mandarte la solución. Un saludo

      Eliminar
    2. Tengo el mismo problema me podrias ayudar, Saludos.

      Eliminar
    3. Ya te lo he mandado. Un saludo

      Eliminar
    4. Mi correo es esteban_a3@yahoo.es, para que me ayudes conlos nombres repetidos

      Eliminar
  9. Tengo el mismo problema me podrias ayudar, Saludos y Gracias.

    ResponderEliminar
    Respuestas
    1. Sin problema si me das un mail al que enviártelo. Un saludo

      Eliminar
  10. Gracias Kiko.

    Ya he actualizado mi perfil y puedes ver mi email; sergicantenys@gmail.com

    De nuevo muchas gracias, tu ayuda me ha facilitado muchísimo mi trabajo.

    Estupendo Blog el tuyo.

    Saludos

    ResponderEliminar
  11. Tengo el mismo problema de duplicidades, me podrias enviar la soluciona a claudio.alvarez.pavez@gmail.com

    Muchas gracias desde ya....

    ResponderEliminar
  12. También tengo el mismo problema de duplicidades, te agradecería si me pudieras enviar la soluciona a fransoy7@yahoo.es

    ResponderEliminar
    Respuestas
    1. Tienes la solución en mi post: Ordenar Automáticamente una Lista con Valores Repetidos.
      Un saludo

      Eliminar
  13. Hola Necesito que me envíes también el Mail con la solución para valores duplicados! Muchas gracias de antemano!
    omarjmontillas@gmail.com

    ResponderEliminar
  14. Una enorme disculpa por molestar pero abusando de su confianza le enviare un mail, con el archivo Excel en el cual le explicare con más detalle, esperando poder en tendernos.
    Disculpe por las molestias que le ocasiono.
    0101kid01@gmail.com

    ResponderEliminar
  15. Jairo Cabañeros30 enero, 2013 20:51

    Estimado Kiko. Muchas gracias por tus aportaciones. Son de grandisima utilidad.
    ¿Podrías enviarme a mí también la solución para valores duplicados? Muchas gracias de antemano. Mi correo: jairocab@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Jairo. Tienes la solución en mi último post: Ordenar Automáticamente una Lista con Valores Repetidos. Un saludo

      Eliminar
  16. tengo el mismo problema de que poseo valores repetidos
    eduardopleomax@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola Eduardo. Tienes la solución en mi último post: Ordenar Automáticamente una Lista con Valores Repetidos.
      Un saludo

      Eliminar
  17. Buenas tardes me lo podrías enviar a mi también, para el caso de repeticiones, mi correo es javier.vadillo@padeldaganzo.es, muy buena solución

    ResponderEliminar
    Respuestas
    1. Hola Javier. Tienes la solución en mi último post: Ordenar Automáticamente una Lista con Valores Repetidos.
      Un saludo y gracias por tus comentarios.

      Eliminar
    2. Hola Kiko, muy bueno el post, me sirvió de mucho, solamente tengo la misma consulta acerca de las repeticiones y no encuentro este último post que usted comenta, le agradezco si me puede enviar esa parte (davidchavesq@gmail.com), muchas gracias!

      Eliminar
    3. Lo puedes encontrar en: http://lareboticadeexcel.blogspot.com.es/2013/01/ordenar-automaticamente-una-lista-con.html

      Eliminar
  18. Ya he visto la ordenación con repetición muchas gracias por el aporte.

    ResponderEliminar
  19. Buenos dias Kiko Post excelente de verdad de mucha ayuda si me puedes pasar tambien la solucion a valores repetidos a pedrojasper@gmail.com
    o ya tienes este post publicado mil gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Hola Pedro. Tienes la solución en mi post de enero 2013: Ordenar Automáticamente una Lista con Valores Repetidos. Un saludo

      Eliminar
  20. Quiero hacer algo parecido pero se trata de una classificacion de un concurso de doma clasica, en la parte donde tu tienes las zonas y ventas, yo tendre el nombre del jinete y la puntuacion por orden de salida, luego quiero que en el otro sitio me salga pero ordenado por puntuacion de mayor a menor, supongo que es lo mismo, solo que no entiendo, ya que uso poco excel cuando dices: Para ello seleccionamos el rango B3:B13 y en el cuadro de nombres (a la izquierda de la barra de fórmulas) escribimos el nombre ZONA. Hacemos lo mismo con el rango C3:C13 y le damos el nombre VENTAS. Esta parte no la entiendo, tienes algun ejemplo que me puedas enviar y yo entonces copiarme las formulas.

    mi mail es : armandc20@hotmail.com, si quieres pasarme el archivo ya echo, donde pone zona tiene que poner NOM y donde pone ventas PUNTUACIO.

    Gracias

    ResponderEliminar
    Respuestas
    1. Ya te he mandado el archivo resuelto. Espero que funcione correctamente. Un saludo

      Eliminar
  21. Hola no he encontrado el post donde aparece la solución a los duplicados te agradeciria muchísimo si me enviaras tambien a mi. hanselretrieve@hotmail.com

    ResponderEliminar
    Respuestas
    1. En esta dirección lo encontrarás: http://lareboticadeexcel.blogspot.com.es/2013/01/ordenar-automaticamente-una-lista-con.html
      Un saludo

      Eliminar
  22. hola! tengo una tabla Excel con varias columnas con datos, entre ellos, una con fecha. necesito q cada vez q agregue nuevos datos, se ordene automáticamente x fecha. Alguien puede ayudarme? gracias

    belen. Belhrs@hotmail.com

    ResponderEliminar
    Respuestas
    1. Te he mandado un mail con la solución. Saludos

      Eliminar
  23. tengo una tabla de 4 columnas y 30 filas, (nombre, presupuesto, venta, porcentaje) necesito que se copie la tabla inicial 3 veces mas(lo cual ya pude hacer) y en una se ordene por presupuesto de mayor a menor, en al otra por venta de mayor a menos y en la ultima por porcentaje de mayor a menor, pero que se actualicen automaticamente al cambiar los valores de la tabla inicial. El problema no es jalar los datos a las otras 3 tablas lo que pasa es que debo ordenar cada una nuevamente y pierdo tiempo... porfavor.... ayuuuudenme......gracias
    jade: jadecami@live.com

    ResponderEliminar
    Respuestas
    1. En principio, es la misma solución que muestro en el post: Ordenar Automáticamente una Lista con Valores Repetidos, sólo que tendrás que aplicarla para tres tablas diferentes y completar 2 rangos más que los que utilizo yo en el ejemplo.

      Eliminar
  24. Kiko, buenas, necesito configurar una tabla:
    COL1 (13 DIGITOS NUMERICOS):Debe tener exactamente 13 digitos, en caso de ser de menor cantidad de digitos debo completar con 0 a la izquierda.
    COL2 (30 DIGITOS ALFANUMERICOS): Tambien debe tener exactamente 30 digitos, en caso de que seam menor debo completar con espacios a la derecha, caso contrario debo cortar el texto.
    Al mismo tiempo debo ordenar de forma ascendete las dos columnas.
    Quedo atento a tu amable y pronta respuesta
    Mi correo es disthebron@hotmail.com
    Saludos

    ResponderEliminar
    Respuestas
    1. Te he mandado ya la solución. Un saludo

      Eliminar
  25. Hola Kiko,
    tu ejemplo se adapta a mis necesidades, pero con el añadido de que las ventas pueden tener valores iguales. Entonces en la segunda columna ordenada, se me repiten datos. ¿como podría solucionarlo?
    Mi correo es ivagaber@gmail.com

    ResponderEliminar
  26. Perdón, referente a la consulta anterior, en mi caso las zonas sería el dato que se podría repetir.
    Gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Tienes la solución en el post de este blog: "Ordenar lista con valores repetidos". Un saludo.

      Eliminar
  27. quisiera saber si me pueden enviar la solucion cuando existen dos valores iguales dentro de la columna ventas mi correo es ronaldcabrera-2007@hotmail.com

    ResponderEliminar
    Respuestas
    1. Puedes ver la solución en mi post: http://lareboticadeexcel.blogspot.com.es/2013/01/ordenar-automaticamente-una-lista-con.html
      Un saludo

      Eliminar
  28. profe como hago para ordenar automaticamente letras no numeros

    ResponderEliminar
    Respuestas
    1. Encontrarás la solución en este link:
      http://jldexcelsp.blogspot.com.es/2007/11/ordenar-texto-en-excel-con-frmulas.html
      Un saludo

      Eliminar
  29. buen día, se me presento el mismo problema, será posible que me envíes al correo fedelozanol@gmail.com lo mismo del compañero al que le respondiste:


    "Te he mandado un mail con la solución utilizando las funciones JERARQUIA y CONTAR.SI para establecer un orden dentro de los repetidos y después lo resolvemos de nuevo con INDICE y COINCIDIR."

    muchas gracias por su ayuda.

    ResponderEliminar
  30. Agradeceré me ayude a resolver el problema de importe de ventas duplicado, para que en el caso del ejemplo, no jale siempre el mismo nombre de zona. gracias.
    jeanlimu@gmail.com

    ResponderEliminar
  31. Hola Kiko. Ante todo muchas gracias por exponer estos ejemplos tan valiosos que a los novatos como yo nos vienen de maravilla.
    Estoy realizando con los compañeros del trabajo una pelotilla para el mundial y el caso es que me he quedado atascado en la última hoja que consiste en ordenar automáticamente tres columnas que son la siguientes:
    - A Número otorgado al hacer "jerarquía" por puntos
    - B Apostante
    - C Puntos

    El caso es que me gustaría poder ordenar todas estas columnas por el número de jerarquía (columna A) y que en caso de que la propia jerarquía esté duplicada me respete ese criterio.
    No sé si lo he sabido explicar correctamente.
    Un saludo

    ResponderEliminar
  32. Hola Gaizka. Necesito que me mandes un ejemplo en la hoja con lo que tienes y lo que quieres conseguir a kiko.rial@gmail.com
    Gracias

    ResponderEliminar
  33. Hola Kiko!

    He encontrado su blog mientras buscaba una solución para hacer un listado de TOP DE VENTA POR MARCA EN CADA TIENDA.

    La función jerarquía por sí sola no me ha ayudado ya que tengo que encontrar alguna otra que me ayude diferenciar el cambio en el número de tienda y que la numeración comience de nuevo. Espero estarme explicando, de cualquier forma le estoy enviando un correo con un pequeño ejemplo de lo que quiero lograr.


    Espero pueda ayudarme, ya que he estado poniendo las jerarquías manualmente y es una lata considerando que tengo como 700 tiendas!


    Le envío un cordial saludo y gracias anticipadas!

    ResponderEliminar
    Respuestas
    1. Ya te he mandado la solución. Un abrazo

      Eliminar
  34. Estimado, tengo el mismo problema que la mayoría, pues tengo valores repetidos.

    -Saludos

    ResponderEliminar
    Respuestas
    1. Tienes la solución en mi post: Ordenar Automáticamente una Lista con Valores Repetidos.
      Un saludo

      Eliminar
  35. Hola, vuelvo a consultarlo por otro tema. Necesito ordenar datos únicos de una columna que contienen valores en otra columna que me coincidan con otras dos columnas similares, una con datos únicos y otra con valores distintos.
    Por ejemplo:
    problema solución
    empresa cuota cliente aporte empresa cuota cliente aporte
    1111 -28514.7 1111 6116.52 1111 -28514.7 1111 6116.52
    1113 -10483.37 1260 753.07 1113 -10483.37
    1114 -10001.17 1310 3264.87 1114 -10001.17
    1115 -8419.06 1371 1959.17 1115 -8419.06
    1210 -10000 1461 2879.06 1210 -10000
    1260 -600 1470 0 1260 -600 1260 753.07
    1310 -3200 1681 141902.37 1310 -3200 1310 3264.87
    1370 -22418.84 1740 13253.16 1370 -22418.84
    1371 -7000 1841 79733.27 1371 -7000 1371 1959.17
    1461 -892126.04 1981 573509.58 1461 -892126.04 1461 2879.06
    1500 -678921 2041 159283.11 1470 0
    1681 -398000 2047 5856.82 1500 -678921
    1740 -13147.89 2210 2130.06 1681 -398000 1681 141902.37
    1841 -61170 2260 2443.96 1740 -13147.89 1740 13253.16
    1890 -2765.4 2380 48760.36 1841 -61170 1841 79733.27
    1901 -710 2380 143566.58 1890 -2765.4
    1968 -1600 2460 37.57 1901 -710
    1969 -1400 2580 0 1968 -1600
    2041 -158309.67 2941 1352550.06 1969 -1400
    2102 -3600 1981 573509.58
    2210 -1550 2041 -158309.67 2041 159283.11
    2260 -2443.96 2047 5856.82
    2380 -229018.77 2102 -3600
    2451 -85000 2210 -1550 2210 2130.06
    2510 -4300 2260 -2443.96 2260 2443.96
    2941 -1385741.39 2380 -229018.77 2380 48760.36
    2380 143566.58
    2451 -85000
    2510 -4300
    2460 37.57
    2580 0
    2941 -1385741.39 2941 1352550.06


    Muchas gracias,
    Daniel.

    ResponderEliminar
    Respuestas
    1. Hola Daniel. Lo siento pero no entiendo lo que quieres hacer. Por favor mándame un archivo con lo que tienes y lo que quieres conseguir a kiko.rial@gmail.com

      Eliminar