Ordenar en Excel: organiza datos por texto, fecha, número o color
Ordenar datos en Excel es una tarea común y sencilla que te permite reorganizar la información según el tipo de ordenación que elijas. Normalmente, con la función integrada de Ordenar, puedes organizar números, cadenas de texto, fechas y horas en una o varias columnas. También puedes ordenar datos según una lista personalizada que hayas creado o por formato de celda (como color de fuente, color de fondo o icono) de forma rápida y sencilla.
Además de estas ordenaciones simples, en tu trabajo diario puedes necesitar ordenaciones más útiles y complejas. Este tutorial te mostrará diferentes tipos de ordenación para resolver tus necesidades en Excel.
Índice de contenidos:
1. Acceso a las opciones de ordenación en Excel
- 1.1 Botones de ordenación en la cinta de opciones
- 1.2 Cuadro de diálogo de ordenación
- 1.3 Opciones de ordenación en el menú Filtro
- 1.4 Opciones de ordenación con clic derecho
2. Usos básicos de la ordenación
- 2.1 Ordenar datos por textos, números o fechas
- 2.2 Ordenar datos por color de celda, color de fuente o icono de celda
- 2.3 Ordenar datos por varias columnas
- 2.4 Ordenar datos según una lista personalizada
- 2.5 Ordenar datos de izquierda a derecha (ordenación horizontal)
- 2.6 Ordenar datos distinguiendo mayúsculas y minúsculas
3. Usos avanzados de la ordenación
4. Ordenar datos automáticamente al ingresar o modificar datos
- 4.1 Ordenar automáticamente valores numéricos en una columna con fórmulas
- 4.2 Ordenar automáticamente valores de texto en una columna con fórmulas
- 4.3 Ordenar automáticamente la mezcla de valores numéricos y de texto en una columna con código VBA
- 5.3 Ordenar pestañas de hojas de cálculo por orden alfabético o por color de pestaña en Excel
- Ordenar pestañas de hojas de cálculo por orden alfabético con código VBA
- Ordenar pestañas de hojas de cálculo por orden alfabético / alfanumérico con una función potente
- Ordenar pestañas de hojas de cálculo por color de pestaña con código VBA
- Ordenar pestañas de hojas de cálculo por color de pestaña con una función sorprendente
Acceso a las opciones de ordenación en Excel
Excel ofrece varias formas de acceder a la función Ordenar y a sus opciones.
1.1 Botones de ordenación en la cinta de opciones
La forma más rápida de aplicar la función de ordenación es utilizando los botones de ordenación en la cinta de opciones.
Haz clic en cualquier celda de una columna con valores a ordenar y luego haz clic en la pestaña Data, en la sección Sort & Filter, selecciona el botón Sort A to Z o Sort Z to A para ordenar los datos en orden alfabético ascendente o descendente. Consulta la captura de pantalla:
Nota: Si hay filas en blanco en el área de datos, al aplicar estos botones, solo se podrá ordenar correctamente el área de datos de la celda seleccionada que esté por encima o por debajo de las filas en blanco.
1.2 Cuadro de diálogo de ordenación
En el grupo Sort & Filter de la pestaña Data, hay otro botón Sort, consulta la captura de pantalla:
Después de hacer clic en este botón Sort, aparecerá un cuadro de diálogo de ordenación como se muestra en la siguiente captura de pantalla:
Luego, en el cuadro de diálogo de ordenación, puedes elegir las reglas correspondientes para ordenar los datos según tus necesidades.
Consejos: También puedes usar atajos de teclado para abrir este cuadro de diálogo de ordenación, pulsa las teclas Alt + A + S + S consecutivamente en el teclado.
1.3 Opciones de ordenación en el menú Filtro
Si has aplicado filtros a tu área de datos, también puedes encontrar opciones de ordenación junto con las opciones de filtro. Al hacer clic en el icono de filtro en cualquier columna, verás las opciones de ordenación en la lista desplegable como se muestra en la siguiente captura de pantalla:
Nota: Estas opciones de ordenación cambiarán según los datos de la columna: Si hay texto, aparecerán Sort A to Z y Sort Z to A; si hay números, aparecerán Sort Smallest to Largest y Sort Largest to Smallest; si hay fechas, aparecerán Sort Oldest to Newest y Sort Newest to Oldest.
1.4 Opciones de ordenación con clic derecho
En Excel, también puedes utilizar las opciones de ordenación del menú contextual para ordenar datos de forma rápida y sencilla. Haz clic derecho en cualquier celda de una columna con valores a ordenar y, en el menú contextual, verás seis opciones de ordenación disponibles. Selecciona la que necesites, consulta la captura de pantalla:
Usos básicos de la ordenación
La función Ordenar de Excel te ayuda a realizar ordenaciones simples, como ordenar números, cadenas de texto o fechas en orden ascendente o descendente, o bien ordenar celdas según el color de fuente o de fondo. En esta sección se explican algunos usos básicos de esta función.
2.1 Ordenar datos por textos, números o fechas
Para ordenar un rango de datos por textos, números o fechas en orden ascendente o descendente, sigue estos pasos:
1. Selecciona el área de datos que deseas ordenar y luego haz clic en Data > Sort, consulta la captura de pantalla:
2. En el cuadro de diálogo de ordenación, realiza las siguientes operaciones:
- En la sección Column, selecciona el nombre de la columna por la que deseas ordenar;
- En la sección Sort On, selecciona la opción Cell Values;
- En la sección Order, especifica el orden de clasificación. (Para ordenar textos, selecciona A to Z o Z to A; para ordenar números, selecciona Smallest to Largest o Largest to Smallest; para ordenar fechas, selecciona Oldest to Newest o Newest to Oldest.)
- Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmarca esta opción.
3. Luego, haz clic en el botón OK, los datos seleccionados se ordenarán de inmediato según la columna que especificaste.
2.2 Ordenar datos por color de celda, color de fuente o icono de celda
Si deseas ordenar el área de datos según el color de celda, color de fuente o icono de formato condicional, la función Ordenar puede resolver esta tarea rápidamente.
Supón que tienes un rango de datos formateado con colores de celda, como se muestra en la siguiente captura de pantalla. Si necesitas reorganizar los datos según el color de celda, por ejemplo, colocar las filas de color rojo claro arriba, seguidas de las amarillas y luego las azules, para ordenar las filas por color de celda, haz lo siguiente:
1. Selecciona el rango de datos que deseas ordenar por color de celda y luego haz clic en Data > Sort para ir al cuadro de diálogo de ordenación.
2. En el cuadro de diálogo de ordenación, especifica las siguientes operaciones:
2.1) Establece el primer color de celda en la parte superior de esta manera:
- En la sección Column, selecciona Name u otra columna donde tengas las celdas coloreadas. En este ejemplo, tengo celdas coloreadas en todas las columnas, puedes elegir cualquier nombre de columna;
- En la sección Sort On, selecciona la opción Cell Color;
- En la sección Order, elige el color de celda que deseas colocar arriba o abajo;
2.2) Luego, haz clic en el botón Add Level para agregar la segunda y siguientes reglas, repite los pasos anteriores para establecer los demás colores de celda.
- Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Tras finalizar la configuración, haz clic en OK y el rango de datos se ordenará según el color de celda que especificaste, consulta la captura de pantalla:
Consejos: Siguiendo los pasos anteriores, también puedes ordenar los datos por color de fuente o icono de celda eligiendo la opción Color de fuente o Icono de formato condicional en el cuadro de diálogo de ordenación.
2.3 Ordenar datos por varias columnas
Si tienes un conjunto de datos grande como el que se muestra a continuación y deseas realizar una ordenación multinivel para visualizar mejor la información, por ejemplo, primero por la columna Región, luego por Estado y finalmente por Ventas, ¿cómo puedes hacerlo en Excel?
Para ordenar datos por varias columnas, sigue estos pasos:
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Data > Sort para ir al cuadro de diálogo de ordenación.
2. En el cuadro de diálogo de ordenación, haz clic dos veces en Add Level ya que hay tres columnas que deseas usar para ordenar. Verás dos niveles Then by añadidos a la lista:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. En las listas desplegables Sort by y Then by, selecciona los nombres de las columnas por las que deseas ordenar y luego selecciona Cell Values en la sección Sort On para cada columna seleccionada. Finalmente, elige el orden de clasificación que necesites.
4. Luego, haz clic en OK y obtendrás el resultado de la ordenación como se muestra en la siguiente captura de pantalla:
2.4 Ordenar datos según una lista personalizada
En lugar de ordenar datos en orden alfabético o numérico, la función Ordenar también te permite ordenar según una lista personalizada. Por ejemplo, si deseas ordenar el siguiente rango de datos por Estado - Not Started, In Progress, Completed, que tú defines. Aquí te explico cómo hacerlo.
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Data > Sort para ir al cuadro de diálogo de ordenación.
2. En el cuadro de diálogo de ordenación, selecciona la columna que deseas ordenar en la sección Column y luego elige Cell Values en la sección Sort On. En la sección Order, haz clic en la opción Custom List, consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Se abrirá un cuadro de Custom Lists, realiza las siguientes operaciones:
- Haz clic en NEW LIST en el cuadro de Custom lists;
- Introduce las cadenas de texto en el orden en que deseas ordenar en el cuadro List entries; (Al introducir los textos, pulsa la tecla Enter para separar las entradas.)
- Por último, haz clic en el botón Add, la nueva lista se añadirá al cuadro de Custom lists de inmediato.
4. Luego, haz clic en OK para volver al cuadro de diálogo de ordenación. La nueva lista personalizada de elementos ahora se muestra en la lista desplegable Order.
5. Finalmente, haz clic en OK para cerrar el cuadro de diálogo; como resultado, el rango de datos se habrá ordenado según la lista personalizada que definiste, consulta la captura de pantalla:
2.5 Ordenar datos de izquierda a derecha (ordenación horizontal)
Normalmente, siempre ordenas la tabla de datos verticalmente de arriba a abajo, pero a veces puedes querer ordenar los datos según los valores de las filas (de izquierda a derecha). Por ejemplo, en el siguiente rango de datos, quiero ordenarlo según los valores de la fila Name.
En este caso, la función Ordenar tiene una opción integrada que permite ordenar de izquierda a derecha. Sigue estos pasos:
1. Selecciona tu rango de datos (excluyendo los encabezados) que deseas ordenar y luego haz clic en Data > Sort para ir al cuadro de diálogo de ordenación.
2. En el cuadro de diálogo de ordenación que aparece, haz clic en el botón Options y luego, en el cuadro de diálogo Sort Options, selecciona la opción Sort left to right, consulta la captura de pantalla:
3. Haz clic en OK para volver al cuadro de diálogo de ordenación. Ahora, en la sección Row, especifica el número de fila por el que deseas ordenar los datos, luego selecciona Cell Values en la sección Sort On y finalmente elige el orden de clasificación en la sección Order que necesites, consulta la captura de pantalla:
4. Luego, haz clic en OK y tus datos se ordenarán como se muestra en la siguiente captura de pantalla:
2.6 Ordenar datos distinguiendo mayúsculas y minúsculas
Generalmente, la ordenación de datos no distingue entre mayúsculas y minúsculas, como se muestra en la primera captura de pantalla a continuación. Pero si quieres que la ordenación distinga mayúsculas y minúsculas, como en la segunda captura, ¿cómo puedes hacerlo en Excel?
Para realizar una ordenación distinguiendo mayúsculas y minúsculas en Excel, haz lo siguiente:
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Data > Sort para ir al cuadro de diálogo de ordenación.
2. En el cuadro de diálogo de ordenación, en la sección Column, especifica el nombre de la columna por la que deseas ordenar los datos, luego selecciona Cell Values en la sección Sort On y finalmente elige el orden de clasificación en la sección Order que necesites, consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Haz clic en el botón Options en este cuadro de diálogo y, en la ventana emergente Sort Options, marca la opción Case sensitive, consulta la captura de pantalla:
4. Haz clic en OK > OK para cerrar los cuadros de diálogo; los datos se ordenarán distinguiendo mayúsculas y minúsculas, primero las minúsculas y luego las mayúsculas cuando el texto sea igual. Consulta la captura de pantalla:
Usos avanzados de la ordenación
En tu trabajo diario, puedes encontrarte con otros problemas de ordenación más complejos, prácticos y específicos. Esta sección aborda varios tipos de tareas de ordenación en Excel.
3.1 Ordenar datos según la frecuencia
Supón que tienes una lista de datos en una columna y ahora quieres ordenar esta columna en orden descendente según la frecuencia de aparición de los datos, como se muestra en la siguiente captura de pantalla. Aquí te presento dos métodos para resolver esta tarea.
Ordenar datos por frecuencia con una columna auxiliar
En Excel no existe una forma directa de ordenar datos por recuento de apariciones. Aquí puedes crear una fórmula auxiliar para obtener la frecuencia de cada texto y luego ordenar por la columna auxiliar para obtener el resultado que necesitas.
1. Escribe la siguiente fórmula en una celda en blanco junto a los datos originales, por ejemplo en B2, y luego arrastra el controlador de relleno hacia abajo para aplicar la fórmula a las demás celdas, consulta la captura de pantalla:
=COUNTIF($A$2:$A$16,A2)
Nota: En la fórmula anterior, A2:A16 es la lista de datos que deseas ordenar por frecuencia, y A2 es el primer dato de esa lista.
2. Luego, mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta las capturas de pantalla:
3. Después, haz clic en el botón Sort; ahora la columna original se habrá ordenado por frecuencia como se muestra en las siguientes capturas de pantalla:
1. Tras obtener el resultado, puedes eliminar la columna auxiliar si lo deseas.
2. Si hay cadenas de texto que aparecen el mismo número de veces, es posible que el mismo texto no se ordene junto. En este caso, deberías ir al Sort cuadro de diálogo, ordenar primero por la columna auxiliar y luego por el texto, como se muestra en la siguiente captura de pantalla:
Ordenar datos por frecuencia con una función práctica
Si cuentas con Kutools para Excel, su función Advanced Sort te permite ordenar los datos por frecuencia de aparición sin necesidad de ninguna columna auxiliar, de forma rápida y sencilla.
1. Selecciona la lista de datos que deseas ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort, consulta la captura de pantalla:
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y luego elige Frequency en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación en la sección Order, consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, haz clic en OK, la lista de datos se ordenará por recuento de apariciones de inmediato, consulta la captura de pantalla:
3.2 Ordenar datos según la longitud de los caracteres
Si tienes una lista de datos que contiene cadenas de texto de diferente longitud, es posible que necesites ordenarla por la cantidad de caracteres para que la columna se vea ordenada. Esta sección explica cómo ordenar datos por el número de caracteres.
Ordenar datos por longitud de caracteres con una columna auxiliar
Para ordenar una columna por la longitud de los caracteres, debes usar la función LEN para calcular el número de caracteres de cada celda y luego aplicar la función Ordenar a la lista de datos. Haz lo siguiente:
1. Introduce esta fórmula =LEN(A2) en una celda en blanco junto a los datos originales, por ejemplo en B2, y luego arrastra el controlador de relleno hacia abajo para aplicar la fórmula, consulta la captura de pantalla:
2. Luego, mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Después, haz clic en el botón Sort; las cadenas se ordenarán por la longitud de los caracteres. Puedes eliminar la columna auxiliar B si lo deseas. Consulta la captura de pantalla:
Ordenar datos por longitud de caracteres con una opción sencilla
Para ordenar datos por el número de caracteres de forma rápida y sencilla, te recomiendo una herramienta práctica: Kutools para Excel. Con su función Advanced Sort puedes realizar esta tarea fácilmente.
1. Selecciona la lista de datos que deseas ordenar y haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y elige Text length en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación que necesites en la sección Order. Consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, haz clic en OK, las cadenas de texto de la lista se habrán ordenado por longitud de caracteres como se muestra en la siguiente captura de pantalla:
3.3 Ordenar nombres completos por apellidos
Al ordenar una lista de nombres completos, Excel utiliza el primer carácter del nombre para ordenar, pero ¿qué pasa si quieres ordenar los datos por apellidos? Aquí te muestro algunos trucos para ordenar nombres completos por apellidos en Excel fácilmente.
Ordenar nombres completos por apellidos con una columna auxiliar
Para ordenar nombres completos por apellidos, debes extraer los apellidos en una nueva columna y luego usar la función Ordenar para organizar los nombres completos alfabéticamente por apellidos. Haz lo siguiente:
1. Introduce o copia la siguiente fórmula en una celda en blanco junto a los datos originales, por ejemplo en B2, y luego arrastra el controlador de relleno hacia abajo para aplicar la fórmula, consulta la captura de pantalla:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Después, haz clic en el botón Sort y obtendrás los nombres completos ordenados por apellidos. Una vez hecho, puedes eliminar la columna auxiliar si lo deseas. Consulta la captura de pantalla:
Ordenar nombres completos por apellidos con un método rápido
Si no te manejas bien con las fórmulas, para ordenar los nombres completos por apellidos sin ninguna fórmula, la función Advanced Sort de Kutools para Excel puede ayudarte.
1. Selecciona las celdas de nombres completos que deseas ordenar y haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y elige Last name en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación que necesites en la sección Order. Consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, haz clic en OK, la columna de nombres completos se ordenará por apellidos de inmediato.
3.4 Ordenar direcciones de correo electrónico por dominio
Si tienes una lista de direcciones de correo electrónico que deseas ordenar por dominio en lugar de solo por la primera letra de la dirección, ¿cómo puedes lograrlo en Excel? Esta sección explica algunos métodos rápidos para ordenar direcciones de correo electrónico por dominio.
Ordenar direcciones de correo electrónico por dominio con una columna auxiliar
Aquí puedes crear una fórmula para extraer los dominios de correo electrónico en otra columna y luego ordenar las direcciones originales según la nueva columna de dominio.
1. Escribe la siguiente fórmula en una celda en blanco junto a tu dirección de correo electrónico, en este ejemplo la introduciré en la celda C2, luego arrastra el controlador de relleno al rango donde desees aplicar la fórmula. Todos los dominios de correo electrónico se extraerán de las direcciones, consulta la captura de pantalla:
=RIGHT(B2,LEN(B2)-FIND("@",B2))
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de diálogo Sort Warning, marca Expand the selection, consulta la captura de pantalla:
3. Después, haz clic en el botón Sort para ordenar los datos por dominios de correo electrónico en orden ascendente o descendente según lo especificado.
4. Tras ordenar, puedes eliminar la columna auxiliar si lo deseas.
Ordenar direcciones de correo electrónico por dominio en pocos clics
Si cuentas con Kutools para Excel, con su función Advanced Sort, las direcciones de correo electrónico pueden ordenarse por nombre de dominio alfabéticamente en solo unos clics.
1. Selecciona todo el rango de datos que deseas ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y elige Mail domain en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación que necesites en la sección Order. Consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Haz clic en OK y el rango de datos se ordenará por dominios de correo electrónico como se muestra en la siguiente captura de pantalla:
3.5 Ordenar una columna para que coincida con otra
Si tienes dos columnas con datos exactamente iguales o casi iguales pero en distinto orden, ahora puedes necesitar ordenarlas para que los mismos valores queden alineados en las mismas filas de ambas columnas. En esta sección te presento dos casos para este tipo de ordenación.
Ordenar dos columnas con los mismos elementos para que coincidan
Por ejemplo, tengo dos columnas que contienen los mismos elementos pero en distinto orden. Aquí quiero ordenar la segunda columna para que coincida con la primera, como se muestra en la siguiente captura de pantalla.
1. Escribe la siguiente fórmula en una celda en blanco junto a tus datos originales, luego arrastra el controlador de relleno para aplicar la fórmula a todas las celdas de la lista. Esto devolverá la posición de cada valor de la columna B respecto a la columna A, consulta la captura de pantalla:
=MATCH(B2,$A$2:$A$10,0)
2. Luego, selecciona la columna B y la nueva columna auxiliar y haz clic en Data > Sort para ir al cuadro de diálogo de ordenación. En el cuadro de diálogo de ordenación, realiza las siguientes operaciones:
- Selecciona la columna auxiliar por la que deseas ordenar los datos en la sección Column;
- Luego, elige Cell Values en la sección Sort On;
- Por último, selecciona la opción Smallest to Largest en la sección Order.
3. Después, haz clic en OK. Ahora tendrás las dos columnas alineadas exactamente como se muestra en la siguiente captura de pantalla. Puedes eliminar la columna de fórmula si lo deseas.
Ordenar dos columnas con elementos diferentes para que coincidan
A veces, los elementos de dos columnas pueden no ser exactamente iguales. Por ejemplo, quiero ordenar los datos de la segunda columna para que coincidan con los de la primera, de modo que los mismos valores queden alineados en las mismas filas, como se muestra en la siguiente captura de pantalla.
1. Inserta una nueva columna en blanco entre las dos columnas.
2. Introduce la siguiente fórmula en la celda B2 y luego arrastra el controlador de relleno para aplicarla a las celdas que necesites. Ahora verás que los datos de la columna C se han ordenado para coincidir con los de la columna A.
=IF(ISNA(MATCH(A2,$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH(A2,$C$2:$C$8,0)))
3.6 Ordenar y extraer valores únicos de una lista de datos
Si tienes una lista de valores que contiene algunos duplicados y solo quieres extraer los valores únicos y ordenarlos alfabéticamente como se muestra en la siguiente captura de pantalla, aquí te presento algunas fórmulas para este tipo de ordenación.
1. Introduce la siguiente fórmula en una celda en blanco – C2, y luego pulsa Ctrl + Shift + Enter para obtener el primer resultado, consulta la captura de pantalla:
=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")
Nota: En la fórmula, A2:A12 es la lista de datos de la que deseas extraer valores únicos y C1 es la celda encima de la fórmula que introdujiste. Modifícalos según tus necesidades.
2. Luego arrastra el controlador de relleno para extraer los valores hasta que aparezcan celdas en blanco; todos los valores únicos se extraerán y ordenarán en orden ascendente, consulta la captura de pantalla:
1. Si quieres que los valores únicos extraídos se ordenen en orden descendente, aplica la siguiente fórmula: (Recuerda pulsar Ctrl + Shift + Enter)
=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,">"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")
2. Si la lista de datos contiene celdas en blanco o valores numéricos, la fórmula anterior no funcionará. En ese caso, utiliza las siguientes fórmulas: (Recuerda pulsar Ctrl + Shift + Enter)
=IFERROR(SMALL(IF((COUNTIF($C$1:C1,$A$2:$A$12)=0)*ISNUMBER($A$2:$A$12),$A$2:$A$12,"A"),1),INDEX($A$2:$A$12,MATCH(SMALL(IF(ISTEXT($A$2:$A$12)*(COUNTIF(C1:$C$1,$A$2:$A$12)=0),COUNTIF($A$2:$A$12,"<"&$A$2:$A$12),""),1),IF(ISTEXT($A$2:$A$12),COUNTIF($A$2:$A$12,"<"&$A$2:$A$12),""),0)))
3.7 Ordenar varias filas o columnas de forma independiente a la vez
Es fácil ordenar un rango de datos según una fila o columna, pero a veces puedes querer ordenar alfabéticamente cada fila o columna del rango de forma independiente. ¿Cómo puedes lograrlo en Excel?
Ordenar varias filas individualmente
Supón que tienes un rango de datos que debe reorganizarse alfabéticamente en cada fila, como se muestra en la siguiente captura de pantalla. Para resolver este problema, aquí tienes dos trucos rápidos.
Ordenar varias filas de forma independiente a la vez con fórmula
1. Copia las etiquetas de las filas a otra ubicación donde desees obtener el resultado ordenado.
2. Luego, copia o introduce la siguiente fórmula en una celda en blanco – H2, y pulsa Ctrl + Shift + Enter para obtener el primer resultado, consulta la captura de pantalla:
=INDEX($B2:$E2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$E2, "<="&$B2:$E2),0))
3. Luego, selecciona la celda de la fórmula H2 y arrastra el controlador de relleno hacia la derecha para copiar la fórmula a otras celdas de la primera fila, en este ejemplo hasta la celda K2, consulta la captura de pantalla:
4. Continúa seleccionando las celdas de la fórmula en la primera fila (H2:K2) y arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras filas. Ahora verás que los valores de cada fila se han ordenado individualmente en orden ascendente.
Ordenar varias filas de forma independiente a la vez con código VBA
El siguiente código VBA también puede ayudarte a ordenar los datos de cada fila alfabéticamente con facilidad. Haz lo siguiente:
1. Selecciona los datos que deseas ordenar en cada fila.
2. Mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
3. Luego, haz clic en Insert > Module y pega el siguiente código en la ventana del módulo.
Código VBA: Ordenar varias filas de forma independiente a la vez
Sub SortIndividualR()
'Updateby Extendoffice
Dim xRg As Range, yRg As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Set xRg = Selection
If xRg.Count = 1 Then
MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Application.ScreenUpdating = False
For Each yRg In xRg.Rows
yRg.Sort Key1:=yRg.Cells(1, 1), _
Order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortRows
Next yRg
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
Application.ScreenUpdating = True
End Sub
4. Después, pulsa la tecla F5 para ejecutar este código; los datos de cada fila se ordenarán en orden ascendente de inmediato, consulta la captura de pantalla:
Ordenar varias columnas individualmente
Para ordenar los datos de cada columna individualmente, los siguientes dos métodos pueden ayudarte.
Ordenar varias columnas de forma independiente a la vez con fórmula
1. Copia las etiquetas de las columnas a otra ubicación donde desees obtener el resultado ordenado.
2. Luego, introduce la siguiente fórmula en una celda en blanco – F3, y pulsa Ctrl + Shift + Enter para obtener el primer resultado. Después, arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras filas, consulta la captura de pantalla:
=INDEX(A$3:A$6,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$6,"<="&A$3:A$6),0))
3. Continúa seleccionando las celdas de la fórmula en la primera fila (F3:F6) y arrastra el controlador de relleno hacia la derecha para copiar la fórmula a otras columnas. Ahora los valores de cada columna se habrán ordenado individualmente en orden ascendente como se muestra en la siguiente captura de pantalla:
Ordenar varias columnas de forma independiente a la vez con código VBA
Para ordenar los datos de varias columnas de forma independiente, el siguiente código VBA también puede ayudarte. Haz lo siguiente:
1. Mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
2. Luego, haz clic en Insert > Module y pega el siguiente código en la ventana del módulo.
Código VBA: Ordenar varias columnas de forma independiente a la vez
Sub SortIndividualJR()
'Updateby Extendoffice
Dim xRg As Range
Dim yRg As Range
Dim ws As Worksheet
Set ws = ActiveSheet
On Error Resume Next
Set xRg = Application.InputBox(Prompt:="Range Selection:", _
Title:="Kutools for excel", Type:=8)
Application.ScreenUpdating = False
For Each yRg In xRg
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=yRg, Order:=xlAscending
.SetRange ws.Range(yRg, yRg.End(xlDown))
.Header = xlNo
.MatchCase = False
.Apply
End With
Next yRg
Application.ScreenUpdating = True
End Sub
3. Después, pulsa la tecla F5 para ejecutar este código y aparecerá un cuadro de diálogo solicitando que selecciones el rango de datos que deseas ordenar, consulta la captura de pantalla:
4. Luego, haz clic en OK, cada columna se ordenará individualmente de forma rápida.
3.8 Ordenar datos aleatoriamente en Excel
Es común ordenar los datos alfabéticamente en orden ascendente o descendente, pero ¿alguna vez has intentado ordenar datos en un rango seleccionado de forma aleatoria? En esta sección te explico cómo mezclar un rango de celdas en orden aleatorio.
Ordenar una columna de datos aleatoriamente con una columna auxiliar
Normalmente puedes usar la función RAND para obtener números aleatorios y luego ordenar los datos según esta lista aleatoria. Haz lo siguiente:
1. Introduce esta fórmula: =RAND() en una celda en blanco junto a tus datos y arrastra el controlador de relleno hacia abajo para generar una lista de números aleatorios como se muestra en la siguiente captura de pantalla:
2. Mantén seleccionadas las celdas con la fórmula y luego haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Después, haz clic en el botón Sort; la lista de datos se mezclará de inmediato, consulta la captura de pantalla:
Ordenar celdas, filas o columnas aleatoriamente con una función sorprendente
Kutools para Excel incluye una potente función – Sort Range Randomly, con la que puedes aleatorizar datos en un rango de celdas, en cada columna/fila de una selección o aleatorizar filas o columnas completas de una vez.
1. Selecciona el rango de datos que deseas ordenar aleatoriamente y luego haz clic en Kutools Plus > Sort > Sort Range Randomly. Consulta la captura de pantalla:
2. En el cuadro de diálogo Sort/Select Range Randomly, bajo la pestaña Sort, elige una de las siguientes opciones según lo que necesites:
- Entire rows: Mezcla todas las filas del rango seleccionado aleatoriamente.
- Entire columns: Mezcla todas las columnas del rango seleccionado aleatoriamente.
- Cells in each row: Mezcla las celdas de cada fila individualmente.
- Cells in each column: Mezcla las celdas de cada columna individualmente.
- All cells in the range: Aleatoriza todas las celdas del rango seleccionado.
3. Luego, haz clic en Ok, los datos se aleatorizarán de inmediato.
3.9 Ordenar una lista de fechas por mes, día o mes y día
Al ordenar fechas en Excel, la función Ordenar organiza la lista de fechas por año, mes y día de forma predeterminada, pero en ciertos casos puedes necesitar organizar las fechas solo por mes o día, o por mes y día ignorando el año. ¿Cómo resolver este problema en Excel?
Ordenar una lista de fechas por mes o día con una columna auxiliar
Para ordenar las fechas solo por mes o día, puedes extraer los números de mes o día de las fechas y luego ordenar las fechas por la columna de mes o día extraída.
1. Introduce la siguiente fórmula en una celda en blanco junto a tus datos y luego arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras celdas, consulta la captura de pantalla:
=MONTH(B2) (extraer número de mes)
=DAY(B2) (extraer número de día)
2. Tras obtener los números de mes o día, mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Luego, haz clic en el botón Sort; las fechas se ordenarán ahora por mes, ignorando los años y días. Consulta la captura de pantalla:
Ordenar una lista de fechas por mes o día en pocos clics
Si cuentas con Kutools para Excel, con su función Advanced Sort puedes ordenar una lista de fechas solo por mes o día en pocos clics.
Kutools para Excel - Cargado con más de 300 herramientas esenciales para Excel. ¡Disfruta de funciones de IA permanentemente gratis! ¡Descarga ahora!
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y elige Month o Day en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación que necesites en la sección Order.
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, haz clic en OK, los datos se habrán ordenado según el mes o día que especificaste, ignorando el año, consulta la captura de pantalla:
Ordenar una lista de fechas por mes y día con una columna auxiliar
Ahora, si necesitas ordenar una lista de fechas solo por mes y día sin tener en cuenta el año, la función TEXT puede ayudarte a convertir una fecha en una cadena de texto con el formato especificado y luego aplicar la función Ordenar según lo necesites.
1. Introduce la siguiente fórmula en una celda en blanco junto a tus datos y luego copia la fórmula hasta el final de tus datos; el mes y el día se extraerán como cadena de texto como se muestra en la siguiente captura de pantalla:
=TEXT(B2,"MMDD")
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Luego, haz clic en el botón Sort; ahora tus datos se ordenarán solo por mes y día.
3.10 Ordenar una lista de fechas por día de la semana
Para ordenar una lista de fechas por día de la semana, es decir, ordenar fechas de lunes a domingo o de domingo a sábado. En esta sección te presento dos métodos para realizar esta tarea en Excel.
Ordenar una lista de fechas por día de la semana con una columna auxiliar
Para ordenar fechas por día de la semana, también necesitarás una columna auxiliar que devuelva un número correspondiente al día de la semana y luego ordenar las fechas por la columna auxiliar.
1. Introduce o copia la siguiente fórmula en una celda en blanco y luego arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras celdas, consulta la captura de pantalla:
=WEEKDAY(B2)(Empieza desde domingo (1) hasta sábado (7))
=WEEKDAY(B2,2)(Empieza desde lunes (1) hasta domingo (7))
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Después, haz clic en el botón Sort para obtener el resultado de la ordenación que necesitas, consulta la captura de pantalla:
Ordenar una lista de fechas por día de la semana con una opción sencilla
Con la función Advanced Sort de Kutools para Excel puedes resolver este problema rápidamente sin necesidad de ninguna fórmula auxiliar.
1. Selecciona el rango de datos que vas a ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y elige Day of the week en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación que necesites en la sección Order.
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, haz clic en OK, los datos se habrán ordenado por día de la semana según lo necesites. Consulta la captura de pantalla:
3.11 Ordenar una lista de fechas por trimestre
Esta sección explica cómo ordenar una lista de fechas por trimestre ignorando el año; se ofrecen dos trucos para ti.
Ordenar una lista de fechas por trimestre con una columna auxiliar
Al igual que en las soluciones anteriores, debes crear una columna auxiliar con una fórmula para extraer el número de trimestre de las fechas dadas y luego ordenar las fechas por esta nueva columna auxiliar.
1. Introduce o copia la siguiente fórmula en una celda en blanco y luego arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras celdas, consulta la captura de pantalla:
=ROUNDUP(MONTH(B2)/3,0)
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Después, haz clic en el botón Sort; el rango de datos se habrá ordenado por trimestre como se muestra en la siguiente captura de pantalla:
Ordenar una lista de fechas por trimestre con una función práctica
Si tienes instalado Kutools para Excel, la función Advanced Sort puede ayudarte a resolver esta tarea en pocos clics.
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, selecciona la columna que deseas ordenar en la sección Column y elige Quarter en la lista desplegable Sort On. Finalmente, especifica el orden de clasificación que necesites en la sección Order.
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Finalmente, haz clic en OK y el rango seleccionado se ordenará por trimestre según lo necesites.
3.12 Ordenar datos por nombres de mes o de día de la semana
Si tienes una lista de nombres de meses como texto, al ordenarlos se organizarán alfabéticamente en lugar de por el orden de los meses de enero a diciembre. Si necesitas ordenar los nombres de los meses de enero a diciembre, una ordenación personalizada dentro de la función Ordenar puede ayudarte.
1. Selecciona el rango de datos que deseas ordenar por nombres de mes y luego haz clic en Data > Sort para ir al cuadro de diálogo de ordenación.
2. En el cuadro de diálogo de ordenación, en la sección Column, selecciona el nombre de la columna que contiene los nombres de los meses. En la sección Sort on, elige Cell Values y finalmente, en la sección Order, selecciona Custom List, consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, en el cuadro de diálogo Custom List que aparece, selecciona los nombres completos de los meses (January, February, March, …) o los nombres abreviados (Jan, Feb, Mar…) según cómo estén listados los meses en tu hoja, consulta la captura de pantalla:
4. Después, haz clic en OK > OK para cerrar los cuadros de diálogo y ahora tus datos se habrán ordenado por nombre de mes en orden cronológico como se muestra en la siguiente captura de pantalla:
Consejos: Para ordenar por nombres de días de la semana, selecciona los nombres completos (Sunday, Monday, Tuesday, …) o abreviados (Sun, Mon, Tue…) en el cuadro de diálogo Custom Lists según lo necesites.
3.13 Ordenar datos por números pares o impares
Puede ser fácil ordenar números en orden ascendente o descendente en Excel, pero ¿alguna vez has intentado ordenar los números de impar a par o de par a impar en una lista? En esta sección te presento algunos métodos para realizar esta tarea.
Ordenar datos por números pares o impares con una columna auxiliar
Para ordenar la lista de números por pares o impares, debes crear una fórmula que identifique los números pares o impares y luego aplicar la función Ordenar.
1. Junto a la lista de números, introduce esta fórmula =ISODD(A2) en una celda en blanco y luego arrastra para copiar la fórmula a otras celdas. Ahora verás que aparecen TRUE y FALSE; TRUE indica números impares y FALSE indica números pares.
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Luego haz clic en el botón Sort; todos los números pares se ordenarán juntos y luego los impares, o viceversa. Consulta la captura de pantalla:
Ordenar datos por números pares o impares con una función útil
Con la función Advanced Sort de Kutools para Excel, puedes ordenar rápidamente los números de impares a pares o viceversa.
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, haz clic en la columna que deseas ordenar en la sección Column y luego elige Odd and even number en la sección Sort On. Después, especifica el orden de clasificación (A to Z ordena de par a impar y Z to A de impar a par), consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Después haz clic en OK y obtendrás los siguientes resultados:
3.14 Ordenar datos por valores absolutos
Si en la columna hay tanto números positivos como negativos, al ordenar, los números se organizarán en orden ascendente o descendente por defecto en Excel. Pero en ciertos casos puedes querer ignorar el signo negativo al ordenar los datos, es decir, ordenarlos por valores absolutos. ¿Cómo lograrlo en Excel?
Ordenar datos por valores absolutos con una columna auxiliar
Para ordenar números por valores absolutos, normalmente debes crear una fórmula para convertir todos los valores en absolutos y luego aplicar la función Ordenar.
1. En una celda en blanco adyacente, por ejemplo B2, introduce esta fórmula =ABS(A2) y luego arrastra el controlador de relleno para copiar la fórmula a otras celdas. Ahora verás que todos los valores se han convertido en valores absolutos:
2. Mantén seleccionadas las celdas con la fórmula y haz clic en Data > Sort A to Z o Sort Z to A según lo necesites. En el cuadro de advertencia Sort Warning que aparece, selecciona Expand the selection, consulta la captura de pantalla:
3. Luego haz clic en el botón Sort; todos los números se habrán ordenado por valores absolutos como se muestra en la siguiente captura de pantalla:
Ordenar datos por valores absolutos con una opción práctica
Si te resulta tedioso crear una columna auxiliar, te recomiendo Kutools para Excel. Con su función Advanced Sort puedes ordenar los números por valores absolutos directamente y de forma sencilla.
Kutools para Excel - Cargado con más de 300 herramientas esenciales para Excel. ¡Disfruta de funciones de IA permanentemente gratis! ¡Descarga ahora!
1. Selecciona el rango de datos que deseas ordenar y luego haz clic en Kutools Plus > Sort > Advanced Sort.
2. En el cuadro de diálogo Advanced Sort, haz clic en la columna que deseas ordenar en la sección Column y luego elige Absolute value en la sección Sort On. Después, especifica el orden de clasificación, consulta la captura de pantalla:
Consejos: Si tus datos incluyen encabezados, asegúrate de que la opción My data has headers esté marcada. Si tus datos no tienen encabezados, desmárcala.
3. Luego, haz clic en OK; todos los números se ordenarán de menor a mayor o de mayor a menor ignorando el signo negativo.
Ordenar datos automáticamente al ingresar o modificar datos
La función Ordenar en Excel no es dinámica; tendrás que volver a ordenar los datos cada vez que haya un cambio o cuando se agreguen nuevos datos. En esta sección te explico cómo ordenar tus datos automáticamente cada vez que se añade un nuevo valor al rango de datos.
4.1 Ordenar automáticamente valores numéricos en una columna con fórmulas
Para ordenar una lista de números en orden ascendente o descendente automáticamente, puedes usar fórmulas basadas en las funciones LARGE, SMALL y ROW.
1. Introduce o copia la siguiente fórmula en una celda en blanco junto a tus datos fuente y luego arrastra el controlador de relleno hacia abajo para aplicar la fórmula a las celdas que desees. Los números se organizarán de menor a mayor, consulta la captura de pantalla:
=IFERROR(SMALL($A$2:$A$100,ROWS(B$2:B2)),"")
Nota: En esta fórmula, A2:A100 es la lista de números que deseas ordenar automáticamente, incluyendo algunas celdas vacías para nuevas entradas, y B2 es la celda donde introduces la fórmula.
2. Ahora, al cambiar los datos fuente o introducir nuevos datos, la lista ordenada se actualizará automáticamente como se muestra en la siguiente demostración:
Consejos: Para ordenar automáticamente los números en orden descendente, aplica la siguiente fórmula:
=IFERROR(LARGE($A$2:$A$100,ROWS(B$2:B2)),"")
4.2 Ordenar automáticamente valores de texto en una columna con fórmulas
Si deseas ordenar automáticamente los valores de texto en una columna, la fórmula anterior no funcionará. Aquí otra fórmula puede ayudarte, haz lo siguiente:
1. Introduce o copia la siguiente fórmula en una celda en blanco junto a tu columna de datos y luego pulsa Ctrl + Shift + Enter para obtener el primer texto. Después, selecciona la celda de la fórmula y arrastra el controlador de relleno hacia abajo para las celdas que quieras ordenar, consulta la captura de pantalla:
=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")
2. A partir de ahora, al introducir valores o cambiar los datos originales en la columna A, las cadenas de texto en la columna B se ordenarán automáticamente en orden ascendente, consulta la siguiente demostración:
Consejos: Para ordenar automáticamente las cadenas de texto en orden descendente, aplica la siguiente fórmula (recuerda pulsar Ctrl + Shift + Enter):
=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")
4.3 Ordenar automáticamente la mezcla de valores numéricos y de texto en una columna con código VBA
Si en una columna hay tanto valores numéricos como de texto, para ordenar la lista de datos automáticamente, el siguiente código VBA puede ayudarte.
1. Haz clic derecho en la pestaña de la hoja donde quieres ordenar los datos automáticamente y luego selecciona View Code en el menú contextual. En la ventana Microsoft Visual Basic for Applications que aparece, copia y pega el siguiente código en el módulo en blanco, consulta la captura de pantalla:
Código VBA: ordenar automáticamente al ingresar o modificar datos en orden ascendente:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error Resume Next
If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Nota: En el código anterior, los datos introducidos se ordenarán automáticamente en la columna A. A1 es el encabezado y A2 es la primera celda de la lista de datos.
2. Luego, guarda y cierra la ventana del código. Ahora, al introducir nuevos datos o cambiar los datos originales en la columna A, los datos se ordenarán automáticamente en orden ascendente. Consulta la siguiente demostración:
Consejos: Si deseas ordenar la lista de datos en orden descendente, aplica el siguiente código:
Código VBA: ordenar automáticamente al ingresar o modificar datos en orden descendente:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error Resume Next
If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A1").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Otros casos de ordenación
Puedes encontrarte con otras necesidades de ordenación en tu trabajo diario. En esta sección te presento otros tipos de ordenación, como ordenar datos dentro de una celda, invertir el orden de los datos en una columna o fila y más.
5.1 Ordenar datos dentro de una celda
Para ordenar una cadena de texto dentro de una sola celda en orden alfabético, por ejemplo, ordenar el texto “HDAW” como “ADHW”; o para ordenar varias palabras separadas por comas en una celda, como ordenar “word, outlook, excel, access” como “access, excel, outlook, word”. Esta sección explica cómo resolver este tipo de ordenación en Excel.
Ordenar el valor de una cadena dentro de una celda en orden alfabético
Para ordenar el valor de una cadena dentro de una celda en orden alfabético, debes crear una función definida por el usuario. Haz lo siguiente:
1. Mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insert > Module y pega el siguiente código en la ventana del módulo.
Código VBA: ordenar el valor de texto dentro de una celda
Function SortCellContents(xRange As Range)
'Updateby Extendoffice
Dim xArr
Dim xF1, xF2 As Integer
Dim xStrValue As String
Dim xStrT As String
If xRange.Count <> 1 Then
Exit Function
End If
xStrValue = xRange.Value
ReDim xArr(1 To Len(xStrValue))
For xF1 = 1 To UBound(xArr)
xArr(xF1) = Mid(xStrValue, xF1, 1)
Next
For xF1 = 1 To UBound(xArr)
For xF2 = xF1 To UBound(xArr)
If Asc(xArr(xF2)) < Asc(xArr(xF1)) Then
xStrT = xArr(xF2)
xArr(xF2) = xArr(xF1)
xArr(xF1) = xStrT
End If
Next xF2
Next xF1
SortCellContents = Join(xArr, "")
End Function
3. Luego guarda y cierra este código, vuelve a tu hoja de cálculo. Después, introduce la fórmula =SortCellContents(A2) en una celda en blanco donde quieras el resultado y arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras celdas; todos los valores de texto de las celdas se ordenarán alfabéticamente, consulta la captura de pantalla:
Ordenar cadenas de texto separadas por un delimitador dentro de una celda en orden alfabético
Si hay varias palabras separadas por delimitadores específicos en una celda, para ordenarlas alfabéticamente dentro de la celda, la siguiente función definida por el usuario puede ayudarte.
1. Mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insert > Module y pega el siguiente código en la ventana del módulo.
Código VBA: ordenar cadenas de texto separadas por comas dentro de una celda
Function SortCellWithSeparator(CellAddress As Range, DelimiterChar As String, IncludeSpaces As Boolean) As String
'Updateby Extendoffice
Dim xRg As Range
Dim xString As String
Dim xF1, xF2 As Integer
Dim xSArr
Dim xStrT As String
Dim xStrValue As String
Set xRg = CellAddress
xStrValue = WorksheetFunction.Substitute(xRg.Value, " ", "")
xSArr = Split(xStrValue, DelimiterChar)
For xF1 = 0 To UBound(xSArr)
For xF2 = xF1 + 1 To UBound(xSArr)
If xSArr(xF2) < xSArr(xF1) Then
xStrT = xSArr(xF2)
xSArr(xF2) = xSArr(xF1)
xSArr(xF1) = xStrT
End If
Next xF2
Next xF1
xStrValue = ""
For xF1 = 0 To UBound(xSArr)
xStrValue = xStrValue & xSArr(xF1) & DelimiterChar
Next xF1
SortCellWithSeparator = xStrValue
SortCellWithSeparator = Left(SortCellWithSeparator, Len(SortCellWithSeparator) - 1)
If IncludeSpaces = True Then SortCellWithSeparator = WorksheetFunction.Substitute(SortCellWithSeparator, ",", ", ")
End Function
3. Luego guarda y cierra este código, vuelve a tu hoja de cálculo, introduce la fórmula =SortCellWithSeparator(A2,",",TRUE) en una celda en blanco donde quieras el resultado y luego arrastra el controlador de relleno hacia abajo para copiar la fórmula a otras celdas; todas las cadenas de texto de las celdas se ordenarán alfabéticamente, consulta la captura de pantalla:
Nota: Si tus cadenas de texto están separadas por otros delimitadores, solo tienes que cambiar la coma en esta fórmula por tu propio separador.
5.2 Invertir / voltear el orden de los datos en una columna o fila
A veces puedes necesitar invertir el orden de los datos de arriba abajo en un rango vertical o de izquierda a derecha en un rango horizontal. Esta sección te presenta tres métodos para resolver esta tarea en Excel.
Invertir / voltear el orden de los datos en una columna o fila con fórmulas
Las siguientes fórmulas pueden ayudarte a voltear el orden de los datos en una columna o fila. Haz lo siguiente:
Voltear el orden de los datos en una columna
Introduce o copia la siguiente fórmula en una celda en blanco donde quieras el resultado y luego arrastra el controlador de relleno hacia abajo para devolver los valores de las celdas en orden inverso, consulta la captura de pantalla:
=OFFSET($A$10,-(ROW(A1)-1),0)
Nota: En la fórmula anterior, A1 es la primera celda y A10 es la última celda de la columna.
Voltear el orden de los datos en una fila
Aplica la siguiente fórmula para voltear el orden de los datos horizontalmente en una fila:
=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)
Nota: En la fórmula anterior, A1 es la primera celda de la fila y1:1 es el número de fila donde están tus datos. Si los datos están en la fila10, debes cambiarlo por10:10.
Luego, arrastra el controlador de relleno hacia la derecha hasta que se extraigan todos los valores y obtendrás todos los datos invertidos horizontalmente, consulta la captura de pantalla:
Invertir / voltear el orden de los datos en varias columnas o filas con código VBA
Las fórmulas anteriores solo funcionan bien para una sola columna o fila. Si hay varias columnas o filas con datos que deseas invertir, los siguientes códigos VBA pueden ayudarte.
Voltear el orden de los datos en un rango de celdas verticalmente
1. Primero, haz una copia de seguridad de tus datos originales y luego mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insert > Module y pega el siguiente código en la ventana del módulo.
Código VBA: voltear un rango de celdas en orden inverso verticalmente
Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
k = UBound(Arr, 1)
For i = 1 To UBound(Arr, 1) / 2
xTemp = Arr(i, j)
Arr(i, j) = Arr(k, j)
Arr(k, j) = xTemp
k = k - 1
Next
Next
WorkRng.Formula = Arr
End Sub
3. Después, pulsa la tecla F5 para ejecutar este código y aparecerá un cuadro de diálogo solicitando que selecciones el rango de datos que deseas invertir verticalmente, consulta la captura de pantalla:
4. Luego, haz clic en OK; el rango de datos se invertirá verticalmente como se muestra en las siguientes capturas de pantalla:
Voltear el orden de los datos en un rango de celdas horizontalmente
Para invertir el rango de datos en orden horizontal, aplica el siguiente código VBA:
Código VBA: voltear un rango de celdas en orden inverso verticalmente
Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
k = UBound(Arr, 2)
For j = 1 To UBound(Arr, 2) / 2
xTemp = Arr(i, j)
Arr(i, j) = Arr(i, k)
Arr(i, k) = xTemp
k = k - 1
Next
Next
WorkRng.Formula = Arr
End Sub
Después de ejecutar este código, los datos de las filas se habrán invertido horizontalmente, consulta las capturas de pantalla:
Invertir / voltear el orden de los datos en columnas o filas con un solo clic
Si tienes Kutools para Excel, con sus funciones Flip Vertical Range y Flip Horizontal Range puedes invertir el rango de celdas vertical u horizontalmente con un solo clic.
Voltear el orden de los datos en un rango de celdas verticalmente
1. Selecciona el rango de datos que deseas invertir y luego haz clic en Kutools > Range > Flip Vertical Range > All / Only flip values, consulta la captura de pantalla:
Consejos: Si eliges la opción All, todo el formato de celda se invertirá al mismo tiempo; si eliges Only flip values, solo se invertirán los valores de las celdas.
2. Después, el rango de datos se invertirá verticalmente de inmediato.
Voltear el orden de los datos en un rango de celdas horizontalmente
1. Selecciona el rango de datos y luego haz clic en Kutools > Range > Flip Horizontal Range > All / Only flip values, consulta la captura de pantalla:
Consejos: Si eliges la opción All, todo el formato de celda se invertirá al mismo tiempo; si eliges Only flip values, solo se invertirán los valores de las celdas.
2. Luego, todos los valores de celda de la selección se invertirán horizontalmente de inmediato como se muestra en las siguientes capturas de pantalla:
5.3 Ordenar pestañas de hojas de cálculo por orden alfabético o por color de pestaña en Excel
Supón que tienes un gran número de hojas en tu libro de trabajo y necesitas ordenar las pestañas de hoja alfabéticamente o según el color de la pestaña. El método habitual de arrastrar y soltar las pestañas puede llevar mucho tiempo. En esta sección te presento algunos trucos para organizar las pestañas de hoja en orden ascendente o descendente.
Ordenar pestañas de hojas de cálculo por orden alfabético con código VBA
El siguiente código VBA puede ayudarte a ordenar las pestañas de hoja alfabéticamente en orden ascendente o descendente. Haz lo siguiente:
1. Mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insert > Module y luego pega la siguiente macro en la ventana del módulo.
VBA: ordenar todas las hojas en orden alfabético
Sub SortWorkBook()
'Updateby Extendoffice
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Click Yes to sort sheets in ascending order;" & Chr(10) & "Click No will sort in descending order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
For j = 1 To Application.Sheets.Count - 1
If xResult = vbYes Then
If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
Sheets(j).Move after:=Sheets(j + 1)
End If
ElseIf xResult = vbNo Then
If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
Application.Sheets(j).Move after:=Application.Sheets(j + 1)
End If
End If
Next
Next
End Sub
3. Después, pulsa la tecla F5 para ejecutar esta macro. En el cuadro de diálogo que aparece, haz clic en Yes para ordenar todas las hojas en orden alfabético ascendente o en No para ordenarlas en orden descendente según lo necesites.
Ordenar pestañas de hojas de cálculo por orden alfabético / alfanumérico con una función potente
Kutools para Excel ofrece una función potente – Sort Sheets, con la que puedes ordenar las hojas de cálculo en orden alfabético o alfanumérico, ordenarlas por color de pestaña o invertir el orden de las pestañas según lo necesites.
Kutools para Excel - Cargado con más de 300 herramientas esenciales para Excel. ¡Disfruta de funciones de IA permanentemente gratis! ¡Descarga ahora!
1. Abre el libro de trabajo cuyas pestañas de hoja deseas ordenar y luego haz clic en Kutools Plus > Worksheet > Sort Sheets, consulta la captura de pantalla:
2. En el cuadro de diálogo Sort Sheets, selecciona el tipo de ordenación que necesites en el panel derecho, como Alpha Sort o Alpha Numeric Sort. Consulta la captura de pantalla:
3. Luego, haz clic en Ok; todas las hojas se ordenarán según el tipo de ordenación que especificaste. Consulta las capturas de pantalla:
Ordenar pestañas de hojas de cálculo por color de pestaña con código VBA
Es común ordenar las pestañas de hoja alfabéticamente en Excel, pero ¿alguna vez has intentado ordenarlas por color de pestaña? El siguiente código VBA puede ayudarte a resolver este problema. Haz lo siguiente:
1. Mantén pulsadas las teclas ALT + F11 para abrir la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insert > Module y luego pega la siguiente macro en la ventana del módulo.
VBA: ordenar todas las hojas por color de pestaña
Sub SortWorkBookByColor()
'Updateby20140624
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
For i = 1 To Application.ActiveWorkbook.Worksheets.Count
If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
n = n + 1
ReDim Preserve xArray1(1 To n)
ReDim Preserve xArray2(1 To n)
xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
End If
Next
For i = 1 To n
For j = i To n
If xArray1(j) < xArray1(i) Then
temp = xArray2(i)
xArray2(i) = xArray2(j)
xArray2(j) = temp
temp = xArray1(i)
xArray1(i) = xArray1(j)
xArray1(j) = temp
End If
Next
Next
For i = n To 1 Step -1
Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
Next
End If
Application.ScreenUpdating = True
End Sub
3. Después, pulsa la tecla F5 para ejecutar este código; las hojas con el mismo color de pestaña se ordenarán juntas. Consulta las capturas de pantalla:
Ordenar pestañas de hojas de cálculo por color de pestaña con una función sorprendente
Con la función Sort Sheets de Kutools para Excel, también puedes ordenar las pestañas de hoja por color de forma rápida y sencilla.
1. Haz clic en Kutools Plus > Worksheet > Sort Sheets para ir al cuadro de diálogo Sort Sheets. En el cuadro de diálogo Sort Sheets, haz clic en el botón Color Sort en el panel derecho, consulta la captura de pantalla:
2. Luego, haz clic en Ok; las hojas con el mismo color de pestaña se agruparán como se muestra en las siguientes capturas de pantalla:
Las mejores herramientas de productividad para Office
Potencia tus habilidades en Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab lleva la interfaz de pestañas a Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de nuevas ventanas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!