Ordenar texto en Excel con fórmulas.

miércoles, noviembre 28, 2007

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:

29 comments:

CÉSAR 29 noviembre, 2007 13:04  

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.

Jorge L. Dunkelman 29 noviembre, 2007 18:31  

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

CÉSAR 30 noviembre, 2007 09:58  

Hola Jorge.

Gracias por tu respuesta; funciona perfectamente.

Saludos, César.

Anónimo,  30 noviembre, 2007 15:05  

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.

Anónimo,  17 abril, 2008 22:05  

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

Jorge L. Dunkelman 17 abril, 2008 23:39  

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

Anónimo,  18 abril, 2008 16:24  

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

Anónimo,  13 octubre, 2008 18:33  

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

Jorge L. Dunkelman 14 octubre, 2008 07:42  

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

Félix 13 julio, 2010 15:43  

Muchas Gracias

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

Anónimo,  01 agosto, 2011 21:30  

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

Jorge L. Dunkelman 02 agosto, 2011 06:58  

¿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".

Anónimo,  11 octubre, 2011 06:28  

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

Jose Riu,  19 enero, 2012 15:26  

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.

Jose Riu,  19 enero, 2012 19:30  

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.

Jorge L. Dunkelman 20 enero, 2012 12:43  

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.

Jorge L. Dunkelman 20 enero, 2012 13:34  

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.

ronald cañas 21 febrero, 2012 02:30  

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

Jorge L. Dunkelman 22 febrero, 2012 18:48  

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.

Anónimo,  10 mayo, 2012 23:01  

muy bueno felicidades

Anónimo,  11 julio, 2012 20:16  

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.

Jorge L. Dunkelman 12 julio, 2012 11:34  

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.

Anónimo,  02 septiembre, 2013 16:59  

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

Jorge Dunkelman 02 septiembre, 2013 20:01  

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. :(

Anónimo,  26 octubre, 2013 08:06  

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.

Jorge Dunkelman 27 octubre, 2013 21:34  

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

Xavi Antó 13 junio, 2014 18:26  

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

Jorge Dunkelman 14 junio, 2014 09:35  

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.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP