ORDENAR DATOS EN EXCEL

1

ORDENAR DATOS EN EXCEL

En Excel puedes ordenar rápidamente una columna de datos utilizando los comandos Ordenar de A a Z y Ordenar de Z a A. Pero debes tener cuidado o de lo contrario solamente se ordenarán algunos de los datos.

Ordenar columnas en Excel

Utiliza esta técnica si no existen filas vacías dentro de los datos. Debes seleccionar una celda de la columna que deseas ordenar y en la ficha Inicio hacer clic en el botón Ordenar y filtrar y seleccionar el comando Ordenar de A a Z para ordenar alfabéticamente o en caso de que sean datos numéricos ordenar del más pequeño al más grande. Si necesitas un ordenamiento del más grande al más pequeño deberás utilizar el comando Ordenar de Z a A.

Ambos comandos los puedes encontrar también en la ficha Datos dentro del grupo

Ordenar y filtrar. Observa el siguiente ejemplo donde ordenaré los datos de manera

ascendente y descendente utilizando los comandos correspondientes.

2 Problemas al ordenar datos

Si existe alguna fila o columna vacía en los datos, entonces solamente se ordenará parte de ellos y la información quedará desordenada. Para ayudar a prevenir estos problemas sigue los siguientes pasos. Selecciona una celda de la columna que deseas ordenar y presiona la combinación de teclas CTRL+E lo cual seleccionará la región completa que se ordenará. Verifica que toda la información está incluida en el área seleccionada. Si no toda la información ha sido seleccionada, corrige cualquier celda en blanco antes de realizar el ordenamiento.

Si por alguna razón los datos no se ordenaron como esperabas recuerda utilizar el comando Deshacer para regresar la información a su estado original.

Orden personalizado en Excel

Excel nos permite ordenar los datos en un rango por cualquiera de sus columnas utilizando el comando “Ordenar de A a Z” para aplicar un orden ascendente o el comando

“Ordenar de Z a A” para un orden descendente pero existen ocasiones en las que necesitamos aplicar un orden personalizado en Excel.

El comando Orden personalizado en Excel

El comando Orden personalizado se encuentra en la ficha Inicio > Modificar > Ordenar y Filtrar.

Este comando abrirá el cuadro de diálogo Ordenar el cual nos permitirá ordenar los datos

por múltiples columnas e inclusive ordenarlas por el color de las celdas. También

podremos crear un orden personalizado para cualquiera de las columnas basándonos en

una lista personalizada.

3 Ordenar por varias columnas en Excel

Cuando trabajamos en Excel es muy fácil ordenar una tabla de datos por cualquiera de sus columnas ya sea de manera ascendente o descendente, pero a veces necesitamos ordenar por varias columnas y la única manera de hacerlo es utilizando el cuadro de diálogo Ordenar.

Por ejemplo, considera un rango de datos con información sobre órdenes de compra donde queremos ordenar la información por el nombre de la ciudad y posteriormente por la fecha de la orden. Todo comienza por seleccionar cualquiera de las celdas de datos y pulsando el comando Inicio > Modificar > Ordenar y Filtrar > Orden personalizado.

Una vez que se abre el cuadro de diálogo deberás elegir las opciones para el primer nivel

que son tres: Columna, Ordenar según y Criterio de ordenación. Para la opción Columna

encontrarás una lista con todos los encabezados de columna de la tabla de datos y solo

deberás elegir aquella por la cual se realizará el ordenamiento. Para nuestro ejemplo será

la columna Ciudad.

4

La opción Ordenar según la dejaremos con su valor predeterminado que es Valores y la opción Criterio de ordenación como A a Z ya que deseamos aplicar un orden ascendente en la columna Ciudad. Para agregar un segundo nivel de ordenación debes pulsar el botón Agregar nivel lo cual nos permitirá indicar la segunda columna por la cual deseamos ordenar los datos.

Observa que al detectar que la columna Fecha orden es de tipo fecha, las opciones para el Criterio de ordenación cambian y podemos seleccionar la opción De más antiguos a más recientes que nos permite ordenar las fechas de manera ascendente. Para aplicar los dos niveles de ordenación definidos debemos pulsar el botón Aceptar y obtendremos un resultado como el siguiente:

Las ciudades han sido ordenadas alfabéticamente y en el caso de la ciudad de Monterrey se ha colocado primero la orden del mes siete y posteriormente la del mes ocho. De esta misma manera podrás agregar tantos niveles de ordenación como necesites y Excel aplicará cada uno de ellos en el orden indicado.

Gestionar los niveles de ordenación

Como ya hemos visto en la sección anterior, el botón Agregar nivel nos permite crear

nuevos criterios de ordenación para poder ordenar los datos por varias columnas. Pero,

5

si por alguna razón decidimos eliminar alguno de ellos será suficiente con hacer clic sobre dicho criterio para seleccionarlo y en seguida pulsar el botón Eliminar nivel.

Así mismo el cuadro de diálogo Ordenar muestra el botón llamado Copiar nivel el cual nos permite hacer una copia de cualquiera de los criterios previamente creados. Solo será necesario seleccionar el criterio deseado y pulsar el botón Copiar nivel para crear una copia y posteriormente modificar sus propiedades.

En el ejemplo mostrado previamente, los criterios de ordenación se aplicaron en el mismo orden en el que aparecen en el listado. Si por alguna razón deseas modificar ese orden podrás hacerlo utilizando los botones de flechas que están al lado del botón Copiar nivel que son conocidos como los botones Subir y Bajar.

En nuestro ejemplo ordenamos los datos por la columna Ciudad y posteriormente por la columna Fecha orden. Si queremos invertir dicho orden, será suficiente con hacer clic en el primer nivel y después hacer clic en el botón Bajar para invertir el orden en que serán aplicados los criterios de ordenación. Observa el resultado de esta acción:

Ahora los datos están ordenados primero por fecha y luego por ciudad. Puedes observar

que para aquellas filas que tienen la misma fecha, las ciudades aparecen en orden

ascendente (alfabético) por ejemplo, para el día 01/07/2010 aparece primero Barcelona

y luego Monterrey o para el día 01/08/2010 se muestra Bogotá y después Monterrey.

6 Ordenar con lista personalizada en Excel

Otra de las posibilidades de ordenamiento que tenemos con el comando Orden personalizado en Excel es que podemos indicar un criterio basado en una lista personalizada. Con esta opción no estaremos limitados a ordenar siempre de manera ascendente o descendente sino que podremos definir nuestra propia secuencia de ordenamiento.

Si deseamos ordenar nuestros datos de ejemplo por su categoría, pero en un orden especial que no sea alfabético, entonces debemos hacer lo siguiente. Abrimos el cuadro de diálogo Ordenar e indicamos la columna Categoría y en Criterio de ordenación seleccionaremos la opción Lista personalizada.

Inmediatamente se mostrará el cuadro de diálogo Listas personalizadas donde podremos

definir el orden específico en que deseamos ordenar la columna. En el panel izquierdo

seleccionamos la opción “NUEVA LISTA” y a la derecha ingresaremos las entradas de

lista de la siguiente manera:

7

Si observas con detenimiento verás que los elementos indicados no están en orden alfabético, ni ascendente ni descendente, así que la única manera de lograr este tipo de ordenamiento es utilizando la lista personalizada. Para terminar pulsamos el botón Agregar para hacer permanente la creación de la lista personalizada y finalmente pulsamos Aceptar para regresar al cuadro de diálogo Ordenar donde observarás que la nueva lista personalizada se muestra como el Criterio de ordenación. Al aplicar este ordenamiento los datos quedarán de la siguiente manera:

En este ejemplo he creado la lista personalizada ingresando cada uno de los elementos dentro del cuadro de texto de entradas de lista, pero si tienes una cantidad considerable de elementos por ingresar, entonces te convendrá crear la lista personalizada desde un rango de celdas. Este método implica crear la lista personalizada antes de intentar ordenar los datos y los pasos para su creación los puedes encontrar en el artículo Crear

Orden personalizado por color

Además de los beneficios mencionados anteriormente, el comando Orden personalizado nos permite ordenar nuestros datos por el color de relleno de la celda, por el color de la fuente e inclusive por el conjunto de iconos de formato condicional desplegado dentro de las celdas.

Para mostrar esta funcionalidad he creado una regla de

columna Total para resaltar aquellas celdas que tienen un valor superior a los $2000 y ahora deseo ordenar los datos para colocar dichas celdas en la parte superior de la tabla.

Lo único que debo hacer es que al momento de crear el nivel de ordenación elegiré la

opción Color de celda de la siguiente manera:

8

Excel detectará automáticamente los posibles colores para una misma columna y nos permitirá elegir entre ellos. Después de elegir el color deseado y pulsar el botón Aceptar obtendremos el siguiente resultado:

El funcionamiento será el mismo si deseas ordenar por el color de fuente o también si deseas implementar un ordenamiento por el ícono de la celda.

El orden personalizado en Excel será de mucha ayuda cuando necesites ordenar tus

datos por varias columnas, ya sea alfabéticamente o utilizando una lista personalizada

para aplicar un criterio de ordenación especial. También nos será de gran ayuda para

ordenar los datos basados en los colores de las celdas o de la fuente.

9

FÓRMULA PARA ORDENAR DATOS EN EXCEL

Excel nos permite ordenar la información rápidamente utilizando el comando Ordenar y filtrar, pero en ocasiones necesitamos una fórmula para ordenar datos en Excel y en esta ocasión te mostraré una alternativa para hacerlo.

Los datos que utilizaremos en nuestro ejemplo tienen una columna de texto y otra columna de números. En el primer ejemplo ordenaremos los datos por los datos numéricos y en el segundo ejemplo ordenaremos por los valores de texto.

Fórmula para ordenar datos numéricos

Para nuestro primer ejemplo haremos un ordenamiento descendente de la columna Calificación. La función que nos permitirá hacer esto de una manera sencilla será la

Es importante mencionar que esta función está disponible a partir de Excel 2010, para versiones anteriores debes utilizar la función JERARQUIA. Ahora considera la siguiente fórmula:

=JERARQUIA.EQV(B2,$B$2:$B$9)

Esta fórmula obtiene la posición del valor en B2 dentro del rango B2:B9 que para nuestro

ejemplo será la posición 3, observa el resultado:

10

La columna Posición asigna el número 1 al valor más alto dentro del rango y a partir de ahí se enumeran todos los registros. Puedes ver que la posición 8 corresponde a la calificación 61 la cual es la más baja de todas las calificaciones. Ahora que ya tenemos las posiciones podemos ordenar los datos utilizando como referencia la columna posición y utilizando la función INDICE y la función COINCIDIR de la siguiente manera:

=INDICE($A$2:$A$9,COINCIDIR(E2,$C$2:$C$9,0))

La función INDICE obtiene un valor dentro de un rango con solo especificar su fila. Con esta fórmula estamos buscando dentro del rango A2:A9 y la fila la obtenemos al hacer coincidir el número 1 de la columna E con el número 1 de la columna C (Posición) que para nuestro ejemplo es Roberto:

Al copiar la fórmula hacia abajo obtenemos automáticamente el nombre de cada alumno de acuerdo a su posición. Para obtener el dato de la calificación usamos una fórmula idéntica a la anterior con la diferencia de que la función INDICE hará la búsqueda sobre el rango B2:B9:

Con esto hemos ordenado los datos de manera descendente utilizando una columna

numérica. Si queremos hacer un ordenamiento ascendente debemos hacer un solo

cambio en la función JERARQUIA.EQV de la columna Posición colocando un número 1

como el tercer argumento de la función lo cual hará que los datos se ordenen de manera

11

ascendente. Observa la fórmula de la celda C2 y el resultado del ordenamiento en los datos:

En este caso los datos se ordenan comenzando por la calificación de menor valor hasta la de mayor valor. Así que con solo modificar el tercer argumento de la función JERARQUIA.EQV podrás controlar el tipo de ordenamiento de los datos.

Desempate de valores numéricos

Nuestro ejemplo anterior funcionó a la perfección porque no tenemos calificaciones repetidas entre los alumnos pero observa lo que sucede si Ignacio y Brenda tienen la misma calificación:

Al tener dos datos numéricos del mismo valor, la función JERARQUIA.EQV determina un empate entre ambos y les otorga la misma posición, que en nuestro ejemplo es la posición 5, pero la siguiente posición otorgada será la posición 7 saltándose la posición 6 y por lo tanto generando un error en nuestra tabla ordenada.

Para solucionar este problema utilizaré una columna auxiliar donde sumaré a cada

calificación el número de fila donde se encuentran los datos. De esta manera, la

calificación 77 de Sonia en la columna auxiliar tendrá un valor de 77.000002 porque se

12

encuentra en la fila 2 de la hoja de Excel, la calificación de 72 de Ignacio tendrá un valor de 72.000003. La fórmula utilizada en la columna auxiliar será la siguiente:

=B2+0.000001*FILA()

Otro cambio que debemos hacer es modificar la fórmula de la columna Posición para que la función JERARQUIA.EQV haga el ordenamiento utilizando la columna auxiliar. El resultado es el siguiente:

Observa que nuestra tabla de datos ordenada ya no muestra el error ocasionado por la repetición de calificaciones de Ignacio y Brenda.

Fórmula para ordenar texto en Excel

Para ordenar texto en Excel utilizando fórmulas es importante recordar la manera en que la herramienta trabaja con texto especialmente al utilizar operadores de comparación.

Observa la siguiente tabla de datos:

Al utilizar el operador Mayor que (>) para comparar dos textos, Excel nos responderá si el primero de ellos tiene una posición mayor dentro del alfabeto. Para la celda C2 el resultado es falso porque la letra A no está después de la letra B. Sin embargo, para la celda C3 el resultado es verdadero porque la letra Z está efectivamente después de la letra A.

Lo mejor de todo es que esta comparación funciona correctamente para palabras y lo

puedes ver con los ejemplos de las últimas dos filas. El hecho de que Excel nos devuelva

13

este resultado al comparar dos textos nos facilitará mucho el ordenamiento con fórmulas. Para llegar a la solución de ordenamiento utilizaremos la función CONTAR.SI la cual nos ayudará a contar la cantidad de cadenas de texto que se encuentran ubicadas alfabéticamente antes de otra. Considera la siguiente fórmula:

=CONTAR.SI($A$2:$A$9,"<="&A2)

La función CONTAR.SI contará todos los elementos del rango A2:A9 que sean menores o iguales que el valor de la celda A2 y de esta manera sabremos la cantidad de valores que le anteceden alfabéticamente. Observa el resultado de esta fórmula:

En nuestro ejemplo Brenda tiene la posición 1 porque la función CONTAR.SI encuentra que solo el texto de la misma celda A8 es menor o igual a Brenda. La posición 2 es para Ignacio porque Brenda e Ignacio son los valores ubicados en una posición alfabética menor o igual dentro del rango. Para generar la tabla con los datos ordenados utilizamos la misa técnica que en el ejemplo anterior con la función INDICE y COINCIDIR:

De esta manera hemos ordenado los datos de manera ascendente por la columna

Nombre. Si queremos ordenarlos de manera descendente será suficiente con cambiar el

símbolo Mayor qué (>). En la siguiente imagen puedes ver el cambio en la fórmula lo cual

ocasiona un cambio en la posición y ordenamiento de los datos:

14 Desempate de posiciones en valores de texto

Al igual que con los números, si tenemos dos celdas con el mismo texto tendremos un error en los datos ordenados así que debemos utilizar un criterio de desempate similar al explicado anteriormente utilizando una columna auxiliar con la siguiente fórmula:

=CONTAR.SI($A$2:$A$9,"<="&A2) + 0.000001*FILA()

Como ya vimos en el ejemplo anterior, la función CONTAR.SI nos devuelve la cantidad de elementos que preceden alfabéticamente a la celda A2 y a ese resultado le sumaremos el número de fila para asegurarnos de que sea un valor único dentro de la columna auxiliar. Para obtener la posición final utilizaremos la función JERARQUIA.EQV de la siguiente manera:

=JERARQUIA.EQV(C2,$C$2:$C$9,1)

Finalmente modificamos las fórmulas en la tabla de datos para que la función COINCIDIR consulte el resultado de la función JERARQUIA.EQV. Observa cómo se ordenan los datos de manera ascendente por la columna de texto y las dos celdas con el mismo valor aparecen juntas:

En realidad hemos revisado más que una sola fórmula para ordenar datos en Excel,

pero las opciones presentadas cubren una buena cantidad de posibilidades de

ordenamiento que seguramente te serán de utilidad. Puedes descargar el libro de trabajo

con los ejemplos descritos en este artículo para que experimentes por tu cuenta.