miércoles, noviembre 28, 2007

Ordenar texto en Excel con fórmulas.

Excel permite ordenar datos, ya sean texto o número, con facilidad y flexibilidad usando el menú Datos-Ordenar.
Pero hay situaciones en las cuales queremos realizar la tarea con fórmulas, como me consultaba uno de mis lectores hace unos días atrás.
La solución consiste en usar la función CONTAR.SI con un pequeño truco. Empecemos por plantear la situación. Supongamos esta lista de nombres que queremos ordenar alfabéticamente con fórmulas



El primer paso consiste en crear una columna auxiliar con esta fórmula:

=CONTAR.SI($A$2:$A$11;"<="&A2)

que copiamos a todo el rango de la columna B

Es importante notar que el segundo argumento de la función está formado por el texto "<=" concatenado con el operador "&" a la celda correspondiente de la columna A.



Como ven la función CONTAR.SI hace aquí las veces de la función JERARQUIA, que sólo funciona con números, no con texto.

El segundo paso consiste en crear una tabla con dos columnas. Una columna auxiliar donde ponemos los números de posición en forma ordenada y una segunda columna donde aparecerán los nombres. En esta columna ponemos esta fórmula

=INDICE($A$2:$A$11;COINCIDIR(D2;$B$2:$B$11;0))

Usamos INDICE y COINCIDIR ya que los números auxiliares en la tabla original están a la derecha de los nombres. Si estuvieran a la izquierda podríamos usar la función BUSCARV.



Como ven, tenemos nuestra lista ordenada en la tabla D1:E11.

Esta técnica tiene un serio inconveniente. Si un nombre aparece repetido, obtendremos un resultado #N/A



Para superar este problema creamos una segunda columna auxiliar. En esta columna ponemos esta fórmula relacionada a la primera columna auxiliar

=JERARQUIA(B2;$B$2:$B$11;2)+CONTAR.SI($B$2:B2;B2)-1

Ya hemos mostrado esta técnica para lograr "desempates" usando la función JERARQUIA.



Como ven, el primer Daniel recibe el número de orden 5 y el segundo, 6.

Todo lo que nos queda por hacer es aplicar la misma fórmula INDICE y COINCIDIR que usamos más arriba







Technorati Tags:

42 comentarios:

  1. Hola.

    Tengo una tabla con países y precios:

    A……..B……………………….C
    1...Alemania.......... 4,71 €
    2...Austria............. 3,60 €
    3...Bélgica............. 4,53 €
    4...Dinamarca........ 4,29 €
    5...España............. 2,40 €
    6...Finlandia........... 4,30 €
    7...Francia............. 5,30 €
    8...Grecia.............. 3,00 €
    9...Holanda............ 4,11 €
    10..Irlanda............. 7,05 €
    11..Italia............... 3,40 €
    12..Luxemburgo...... 3,20 €
    13..Portugal........... 3,00 €
    14..Reino Unido...... 7,62 €
    15..Suecia............. 4,82 €
    16..R. Checa.......... 1,81 €
    17..Chipre............. 2,82 €
    18..R. Eslovaca....... 1,58 €
    19..Eslovenia.......... 2,20 €
    20..Estonia............ 1,35 €
    21..Hungría........... 1,71 €
    22..Letonia............ 0,93 €
    23..Lituania........... 1,23 €
    24..Malta.............. 3,61 €
    25..Polonia............ 1,58 €

    Construyo una lista ordenada por precios decrecientes:

    ...G...................H
    Reino Unido.........7,62 €
    Irlanda..............7,05 €
    Francia.............5,30 €
    Suecia..............4,82 €
    Alemania...........4,71 €
    Bélgica..............4,53 €
    Finlandia............4,30 €
    Dinamarca..........4,29 €
    Holanda.............4,11 €
    Malta................3,61 €
    Austria..............3,60 €
    Italia................3,40 €
    Luxemburgo........3,20 €
    Grecia...............3,00 €
    Grecia...............3,00 €
    Chipre...............2,82 €
    España...............2,40 €
    Eslovenia............2,20 €
    R. Checa............1,81 €
    Hungría..............1,71 €
    R. Eslovaca.........1,58 €
    R. Eslovaca.........1,58 €
    Estonia.............1,35 €
    Lituania.............1,23 €
    Letonia..............0,93 €

    Para ello uso:

    Columna H ‘=INDICE(países;COINCIDIR(H1;precios;0);1)’
    Columna G ‘=K.ESIMO.MAYOR(precios;A1)’

    Pero, al estar repetidos varios precios, aparecen duplicados unos países y faltan otros.

    ¿Cómo puedo hacer para corregir esto?

    Gracias anticipadas.

    Saludos.

    ResponderBorrar
  2. Hola César

    es preferible que me mandes el archivo con la pregunta a poner toda la descripción en un comentario.
    En cuanto a la consulta, te sugiero que crees una columna auxiliar (digamos en al columna D)con la fórmula
    =JERARQUIA(C1;precios;)+CONTAR.SI($C$1:C1;C1)-1
    Luego en la la columna G pones esta fórmula
    =INDICE(paises;COINCIDIR(A1;ranking;0))
    aprovechando que en la columna A tienes ordenados los números del 1 al 25.
    En la columna G recibirás los paises ordenados por precio, de mayor a menor.
    Finalemnte para poner los precios correspondientes al lados de los paises, usas la fórmula
    =BUSCARV(G1;$B$1:$C$25;2;0)
    en la columna H

    ResponderBorrar
  3. Hola Jorge.

    Gracias por tu respuesta; funciona perfectamente.

    Saludos, César.

    ResponderBorrar
  4. Te felicito por lograr tan fantástica y útil solución!

    Gracias a tu voluntad altruista muchos de tus lectores podemos mejorar nuestras aplicaciones.

    Muchas gracias.

    ResponderBorrar
  5. Hola Jorge, muy útil y muy bien explicado los temas, muchas gracias.
    Esta formula me funciona muy bien con texto pero no así con números, ¿hay que modificar algo? supongamos que en lugar de nombres usamos numeros de artículos y desordenados, ¿como sería la fórmula? en la columna tengo algunos ceros.
    Gracias de antemano
    Saludos
    Enrique

    ResponderBorrar
  6. Para trabajar con números utilizas la función JERARQUIA. Como menciono en la nota, esta funcón trbaja sólo con números. Por eso publique esta solución par texto usando CONTAR.SI

    ResponderBorrar
  7. Ahora sí funciona de 10!!
    Muchas gracias Jorge, por tu tiempo y ayuda.
    Un abrazo
    Enrique

    ResponderBorrar
  8. Perdóname con antelación, pero creo que no comprendo. No soy un geeck de Excel así que creo que esa es la razón por la cual se me hace dificil la comprensión.

    De todos modos, no he encontrado lugar mejor que este sitio. Aquí me he acercado bastante a ordenar mi tabla.

    Tengo una serie de países con su respectivo incremento de productividad agraria (IPA) entre 1500 i 1800. Quiero ordenar esos países de mayor a menor según su incremento, pero no hay maneras.

    A...........B.............C
    .........países.........IPA
    1.......INGLATERRA......1,43
    2.......PAISES BAJOS....1,35
    3.......BÉLGICA.........0,80
    4.......ALEMANIA........0,91
    5.......ESPAÑA..........0,79
    6.......ITÁLIA..........0,71
    7.......FRANCIA.........1,14
    8.......POLONIA.........1,15
    9.......ÁUSTRIA.........0,89

    Se perfectamente que son pocos y podría ordenarlos a ojo, pero me he propuesto hacerlo de manera automática, porque se que Excel puede y de ese modo aprender un poco más sobre este programa.

    Gracias con antelación

    ResponderBorrar
  9. Seleccionas la celda C1 (supongo que es la que contiene el encabezamiento IPA), luego usas el menú Datos--Ordenar

    ResponderBorrar
  10. Muchas Gracias

    Ha sido muy util esta ayuda, gracias a ti he quedado bien muchas veces en el trabajo.

    ResponderBorrar
  11. Hola, queria saber con que formula puedo ordenar en forma automatica estos datos
    Yo se con la funcion k.esimo puedo ordenar los numeros, pero tambien quiero que arrastre el texto.
    PARTIDO Datos
    TIGRE 761
    SAN ISIDRO 2
    MERLO 161
    SAN MIGUEL 123
    LA MATANZA 123
    ITUZAINGO 8
    TRES DE FEBRERO 196
    SAN MARTIN 75
    MALVINAS ARGENTINAS 388
    GARIN 3
    ESCOBAR 465
    LUJAN 95
    CHIVILCOY 1030
    LOBOS 27770

    la columna datos sale de Datos automaticos, de otra planilla

    Yo lo que necesito que quede asi en forma automatica

    PARTIDO Datos
    LOBOS 27770
    CHIVILCOY 1030
    TIGRE 761
    ESCOBAR 465
    MALVINAS ARGENTINAS 388
    TRES DE FEBRERO 196
    MERLO 161
    SAN MIGUEL 123
    LA MATANZA 123
    LUJAN 95
    SAN MARTIN 75
    ITUZAINGO 8
    GARIN 3
    SAN ISIDRO 2

    ResponderBorrar
  12. ¿Por que no usar sencillamente el comando Ordenar de Excel? Si querés que se efectue en forma automática podría grabar una macro.
    Ahora, si queré hacerlo con fórmulas tendrías que usar K.ESIMO o JERARQUIA para ordenar "Datos" y luego INDICE con COINCIDIR para ordenar los "Partidos".

    ResponderBorrar
  13. Si la lista es:

    Un nombre mas un numero (Nombre 1, Nombre 2) lo termina colocando mal :(

    Nombre 1
    Nombre 10
    Nombre 2
    Nombre 3 etc

    Porque lo ordena segun su valor numerico

    ResponderBorrar
  14. Hola Jorge.
    Queria consultarte sobre un comentario de esta nota con respecto al uso de indice y coincidir cuando los datos a buscar estan a la derecha de la tabla y buscarv cuando estan a la izquierda.

    normalmente yo uso buscarv solo por costumbre, para lo cual debo generar una columna a la izquierda que contenga el valor de los datos a buscar, que se encuentran en las columnas de la izquierda de la tabla.

    La consulta es si existe algun beneficio adicional al usar indice y coincidir, en lugar de buscarv de la forma que lo hago, mas alla de los casos en que no sea posible insertar una columna a la izquierda o sea riesgoso para las macros que hagan referencia a estas celdas.

    Desde ya, mi permanente reconocimiento a tu labor, conocimiento e inteligencia.

    ResponderBorrar
  15. Hola Jorge.

    Como seria este mismo procedimiento, si tuvieramos que ordenar la tabla en base a mas de un criterio?

    Te mando un sincero abrazo.

    ResponderBorrar
  16. El principal beneficio de usar INDICE con COINCIDIR es la flexibilidad. POr ejemplo, podemos hacer búsquedas en una matriz, por fila y por columna, cosa que con BUSCARV no es posible.

    ResponderBorrar
  17. Si hay más de un criterio, podemos combinarlos en una columna auxiliar y usarla como parámetro. Por ejemplo, en una columna aparecen los apellidos y en otra los nombres propios. Podemos crear una tercera que combine el apellido y el nombre (=A2&B2, por ejemplo) y aplicar la fórmula a la columna auxiliar.

    ResponderBorrar
  18. Hola Jorge

    quisiera que pudieras ayuadarme en un datico con excel... resulta que estoy montando los boletines definitivos del colegio en el cual laboro y ya saco el promedio, el desempeño y otras cosas mas... pero no se como hacer que una celda me vote el puesto que queda el estudiante.
    por ejemplo

    pablo 3,8
    marcos 3,4
    richar 4,5
    lisa 5,0
    maria 2,5

    necesito que que una celda me vote quien ocupa el 1 puesto 2 3 4 o el 5 puesto...

    por fa... que debo hacer

    ResponderBorrar
  19. Por ejemplo, usar la función JERARQUIA. En el blog hay varias notas sobre el tema. Te sugiero ue hagas una búsqueda con la palabra "JERARQUIA" para ver las notas relevantes.

    ResponderBorrar
  20. muy bueno felicidades

    ResponderBorrar
  21. Hola Jorge, Bastante descriptivo y muy buena entreda la que has públicado, te escribo para felicitarte y así mismo para preguntarte algo, Tengo una base de datos la cuál quiero ordenar por un campo cómo puedo hacer esto? es decir, tengo el campo fecha, el campo correo, el campo Diágnostico y el campo solución... Entonces lo que me gustaría hacer es que todo quede ordenadosegún el diágostico, claro el diágnostico es un número y la solución igual, pero quiero que se ordenen de 1 al más alto que podría ser 20 y así las fechas y los correos y las soluciones correspondientes a cada diágnostico, claro, habrán diágnosticos repetidos pero con diferentes correos y soluciones diferentes, podrías hecharme una mano con esto? :D
    Te lo agradezco de antemano.

    ResponderBorrar
  22. No termino de entender la consulta. Excel tiene la funcionalidad Ordenar (ascendente y descendete), por lo que la solución a tu consulta parece ser trivial. Sospecho que te refieres a ordenar por fecha y luego por diagnóstico. También ésto es trivial. Excel permite ordenar por varios criterios en forma jerárquica.

    ResponderBorrar
  23. Saludos, relicé la aplicación con fórmula para el ordenado automático, esta excelente pero tengo un invonveniente, quisiera saber porqué al momento que elimino un nombre me aparece el primer número con un cero y el resto de datos se desplazan hacia abajo empezando el conteo desde el 2, Qué puedo hacer, mi correo es wwwliscompg@hotmail.com
    Agradezco la ayuda

    ResponderBorrar
  24. Supongo que estás borrando el nombre de la celda. Esto hace que siga habiendo un valor (vacío) en la celda. Tienes que eliminar toda la fila, no sólo la celda.

    En el futuro, no pongas tu correo en comentarios, excepto que te guste ver tu casilla de correo invadida de spam. :(

    ResponderBorrar
  25. Hola Jorge, tengo la siguiente situación y no se cómo resolverla:

    Valor1. Valor2. Valor3. Sumatoria
    Servicio1. 2 1 3 6
    Servicio2. 4 2 7 13
    Servicio3. 5 4 1 10

    Necesito qué la columna "sumatoria" se ordene de forma automática de mayor a menor a medida que ingreso más servicios pero que al ordenarlo me llame tanto el nombre como los valores 1,2 y 3 y no sólo el valor de la sumatoria (para eso utilizaría la función K.ESIMO)

    Espero que puedas ayudarme.

    Saludos.

    ResponderBorrar
  26. Una vez ordenados por el valor de la sumatoria, podrías usar JERARQUIA para determinar quien es el primero y luego INDICE con COINCIDIR para ordenar la tabla en una rango auxiliar. He mostrado esa técnica en algunas de las notas sobre tablas de posiciones.
    Otra posibilidad es usar tablas dinámicas, definiendo el orden de los valores del campo de las filas según la sumatoria (el campo de los valores).

    ResponderBorrar
  27. Buenas, necesito ayuda con una excel que utilizo como base de datos
    En ella tengo datos de pacientes, entre otros, num exp, nombre, direccion, dia de nacimiento,etc acabo de crear una nueva columna para calcular la edad en meses, para ello he utilizado la formula =DIAS360(O28;P1;VERDADERO)/30 donde "o28" es el la celda del dia de nacimiento y P1 es una celda con la formula =HOY(), hasta ahi todo perfecto, pero si quiero ordenar de alguna manera diferente las columnas ,por ejemplo por numero de expediente, la columna de meses , me da error. El error por lo que veo, proviene de la celda P1.
    Espero me haya explicado bien, les agradeceria muchisimo si me pudieran ayudar, ya que necesito ese dato.
    Gracias de antemano

    ResponderBorrar
  28. Xavi, por favor fijate lo que pongo en el enlace Ayuda (en la parte superior de la plantilla) y ponte en contacto conmigo por mail privado.

    ResponderBorrar
  29. Tengo una columna con números y otra con un valor de letra (D, S, V), sé cómo ordenar los números en otra columna de forma automática con k.esimo, pero ¿cuál sería la fórmula para que me arrastre la letra a una columna al lado de la de los números ordenados sin ordenar las letras, claro?

    Ej.
    2345 V
    2354 D
    123 V
    23456 S
    Debería quedar:
    123 V
    2345 V
    2354 D
    23456 S

    ResponderBorrar
  30. Para ordenar una columna sin ordenar las retantes del rango (Excel identifica automáticamente como tabla todo rango delimitado por una columna y una fila vacías), tienes que seleccionar la columna y aplicar Datos-Ordenar; cuando Excel pregunte "Ampliar la selección" marcas la opción "Continuar con la selección actual".

    ResponderBorrar
  31. No me he explicado bien.

    Si en vez de las letras, marco las casillas con colores, eso no me lo pasa a la columna de ordenación automática. ¿Cómo podría hacerlo?
    Gracias y perdona mi desconocimiento.

    ResponderBorrar
  32. Enviame el cuaderno y ponte en cintacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar
  33. me puedes ayudar...

    estoy creando una lista con materiales y precios...

    madera 1200
    hormigon 34000
    fierro 340

    luego en otra hoja del mismo archivo realizo una formula de multiplicación simple...
    cuando vuelvo a la lista de materiales que son muchos... quiero ordenarlos y las formula con multiplicación de la otra cambia por que al ordenar he perdido la posición antigua de los datos...

    que puedo hace para que las referencias de la formulas que multiplican sigan con la misma referencia??

    ResponderBorrar
  34. Buenas necesito su ayuda, como ordenar una columna que contiene números de cédulas con la letra "V" si es venezolano y "E" si es extranjero. Ejemplo en la columna "A" tengo los siguientes datos: V-9865142
    V-26785145
    V-13254124
    V- 2345120
    Gracias

    ResponderBorrar
  35. Sencillamente usando Ordenar del menú Datos.

    ResponderBorrar
  36. Hola, cómo estás.

    he seguido las indicaciones pero al ordenar no me respeta algunas variables uqe necesito, es para realizar rondas finales de un torneo de futbo, pasan los 4 primeros lugares, pero el siguiente valor de desempate es la diferencia de goles, al ordenar me pone el primero que encuentra aunque no sea el que deba de pasar


    en el ejemplo deben de pasar los # 2,3,5,7 que tiene mejor puntaje y diferencia de goles, pero las formulas me ponen 1,2,3,5.


    Los #1 y 7 tienen igual puntaje, peor por diferencia de goles debe de pasar el #7 no el 1, ¿cómo podría arreglar esto?

    # EQUIPO GF GC DIF PTS a1 a2
    1 AZTECAS UTM 13 15 -2 7 7 4
    2 La Naranja Mecanica 19 14 5 13 10 1
    3 Los Dengues 21 14 7 13 10 2
    4 LOS DISCIPULOS 11 15 -4 6 4 7
    5 Paris and Germain 13 14 -1 9 8 3
    6 PARIS SAINT GERMAIN O.E. 11 16 -5 4 3 8
    7 Leicester City 8 8 0 7 7 5
    8 Peñarol 9 9 0 7 7 6



    Muchas gracias, espero me puedan ayudar

    ResponderBorrar
  37. Hola Jorge.
    Mi duda es la siguiente.
    Tengo una columna en donde los datos son todos diferentes y solo se repiten algunos nombres, por ejemplo:

    CEO C, 252x 168, Carlos, 29 de enero 2016
    CEO M, 355x152 , Luis, 29 de enero 2016
    CEO Ñ, 485x152, Carlos, 29 de enero 2016
    CEO O, 157x152 , Luis, 29 de enero 2016
    CEO P, 1.57 x152 , Carlos, 29 de enero 2016
    CEO Q, 485x1.52 , Carlos, 29 de enero 2016
    CEO S, 135x1.77, Luis, 29 de enero 2016
    CEO T, 135x 1.77 , Luis, 29 de enero 2016
    CEO U 485 x1.52, Carlos, 29 de enero 2016
    CEO V, 540x152 , Andrea, 29 de enero 2016

    ¿Cómo puedo ordenar por Carlos, Luis y Andrea?

    De antemano, muchas gracias.

    ResponderBorrar
  38. Lo más sencillo es crear una columna auxiliar con los nombre, que puedes extraer con una fórmula o con texto a columnas, y ordenar de acuerdo a esa columna auxiliar.

    ResponderBorrar
  39. Saludos Jorge! una consulta, quiero ordenar una lista de estanterías que está en éste formato:

    39a
    40c
    59b
    1d
    2a

    y quiero ordenarla de ésta forma:

    2a
    39a
    59b
    40c
    1d

    Me explico? (1a,2a,3a... 1b,2b,3b... 1c,2c,3c... 1d,2d,3d)
    están todas en una sola columna (A2-A939)
    Muchísimas gracias!

    ResponderBorrar
  40. Tendrías que crear dos columnas auxiliares. Una que contenga las letras, que entiendo se encuentran siempre al final del valor, y la otra los npumeros. Para hacerlo puedes usar Datos-Texto en Columnas.
    Luego ordenas con dos niveles; el primero por letras y el segundo por valores numéricos.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.