El tutorial muestra cómo usar la función ORDENAR para ordenar matrices de datos dinámicamente. Aprenderá una fórmula para ordenar alfabéticamente en Excel, organizar números en orden ascendente o descendente, ordenar por varias columnas y más.
La funcionalidad Ordenar existe desde hace mucho tiempo. Pero con la introducción de matrices dinámicas en Excel 365, apareció una forma sorprendentemente simple de ordenar con fórmulas. La belleza de este método es que los resultados se actualizan automáticamente cuando cambian los datos de origen.
Función ORDENAR de Excel
La función ORDENAR en Excel ordena el contenido de una matriz o rango por columnas o filas, en orden ascendente o descendente.
SORT pertenece al grupo de funciones de matrices dinámicas. El resultado es una matriz dinámica que se propaga automáticamente a las celdas vecinas vertical u horizontalmente, según la forma de la matriz de origen.
La sintaxis de la función ORDENAR es la siguiente:
CLASIFICAR(matriz, [sort_index], [sort_order], [by_col])
Dónde:
Matriz (obligatorio): es una matriz de valores o un rango de celdas para ordenar. Estos pueden ser cualquier valor, incluidos texto, números, fechas, horas, etc.
Sort_index (opcional): un número entero que indica por qué columna o fila ordenar. Si se omite, se utiliza el índice predeterminado 1.
Sort_order (opcional): define el orden de clasificación:
- 1 u omitido (predeterminado) – orden ascendente, es decir, de menor a mayor
- -1 – orden descendente, es decir, de mayor a menor
By_col (opcional): un valor lógico que indica la dirección de clasificación:
- FALSO u omitido (predeterminado): ordenar por fila. Usarás esta opción la mayor parte del tiempo.
- VERDADERO – ordenar por columna. Use esta opción si sus datos están organizados horizontalmente en columnas como en este ejemplo.
Función ORDENAR de Excel: consejos y notas
SORT es una nueva función de matriz dinámica y, como tal, tiene un par de especificidades que debe tener en cuenta:
- Actualmente, la función ORDENAR solo está disponible en Microsoft 365 y Excel 2021. Excel 2019 y Excel 2016 no admiten fórmulas de matriz dinámica, por lo que la función ORDENAR no está disponible en estas versiones.
- Si la matriz devuelta por una fórmula SORT es el resultado final (es decir, no se pasa a otra función), Excel crea dinámicamente un rango de tamaño adecuado y lo completa con los valores ordenados. Por lo tanto, asegúrese de tener siempre suficientes celdas vacías hacia abajo o hacia la derecha de la celda donde ingresa la fórmula; de lo contrario, se producirá un error #SPILL.
- Los resultados se actualizan dinámicamente a medida que cambian los datos de origen. Sin embargo, la matriz proporcionada a la fórmula no se amplía automáticamente para incluir nuevas entradas que se agregan fuera de la matriz a la que se hace referencia. Para incluir dichos elementos, debe actualizar la referencia de matriz en su fórmula o convertir el rango de origen en una tabla como se muestra en este ejemploo cree un rango dinámico con nombre.
Fórmula ORDENAR básica de Excel
Este ejemplo muestra una fórmula básica para ordenar datos en Excel en orden ascendente y descendente.
Supongamos que sus datos están ordenados alfabéticamente como se muestra en la captura de pantalla a continuación. Está buscando ordenar números en la columna B sin dividir o mezclar datos.
Fórmula para ordenar en orden ascendente
Para ordenar los valores en la columna B de menor a mayor, esta es la fórmula que debe usar:
=SORT(A2:B8, 2, 1)
Dónde:
- A2:B8 es la matriz de origen
- 2 es el número de columna para ordenar
- 1 es el orden de clasificación ascendente
Dado que nuestros datos están organizados en filas, el último argumento se puede omitir para que sea FALSO de forma predeterminada: ordenar por filas.
Simplemente ingrese la fórmula en cualquier celda vacía (D2 en nuestro caso), presione Entrar y los resultados se verterán automáticamente en D2: E8.
Fórmula para ordenar en orden descendente
Para ordenar los datos de forma descendente, es decir, de mayor a menor, establezca el argumento sort_order en -1 de esta manera:
=SORT(A2:B8, 2, -1)
Ingrese la fórmula en la celda superior izquierda del rango de destino y obtendrá este resultado:
De manera similar, puede ordenar los valores de texto en orden alfabético de la A a la Z o de la Z a la A.
Cómo ordenar datos en Excel usando fórmula
Los siguientes ejemplos muestran algunos usos típicos de la función ORDENAR en Excel y un par de usos no triviales.
Excel ORDENAR por columna
Cuando ordena datos en Excel, en su mayor parte cambia el orden de las filas. Pero cuando sus datos están organizados horizontalmente con filas que contienen etiquetas y columnas que contienen registros, es posible que deba ordenarlos de izquierda a derecha, en lugar de de arriba a abajo.
Para ordenar por columna en Excel, establezca el argumento by_col en VERDADERO. En este caso, sort_index representará una fila, no una columna.
Por ejemplo, para ordenar los siguientes datos por Cant. de mayor a menor, usa esta fórmula:
=SORT(B1:H2, 2, 1, TRUE)
Dónde:
- B1:H2 es la fuente de datos para ordenar
- 2 es el índice de clasificación, ya que estamos clasificando números en la segunda fila
- -1 indica el orden de clasificación descendente
- VERDADERO significa ordenar columnas, no filas
Ordenar por varias columnas en orden diferente (ordenación de varios niveles)
Cuando trabaje con modelos de datos complejos, a menudo puede necesitar una ordenación de varios niveles. ¿Se puede hacer eso con una fórmula? ¡Sí, fácilmente! Lo que hace es proporcionar constantes de matriz para los argumentos sort_index y sort_order.
Por ejemplo, para ordenar los datos a continuación primero por Región (columna A) de la A a la Z, y luego por Cantidad. (columna C) de menor a mayor, establece los siguientes argumentos:
- Array son los datos en A2:C13.
- Sort_index es la constante de matriz {1,3}, ya que primero ordenamos por región (primera columna) y luego por cantidad. (3ra columna).
- Sort_order es la constante de matriz {1,-1}, ya que la primera columna debe ordenarse en orden ascendente y la tercera columna en orden descendente.
- By_col se omite porque ordenamos las filas, lo cual es predeterminado.
Poniendo los argumentos juntos, obtenemos esta fórmula:
=SORT(A2:C13, {1,3}, {1,-1})
¡Y funciona perfectamente! Los valores de texto en la primera columna están ordenados alfabéticamente y los números en la tercera columna de mayor a menor:
Ordenar y filtrar en Excel
En caso de que esté buscando filtrar datos con algunos criterios y poner la salida en orden, use las funciones ORDENAR y FILTRAR juntas:
CLASIFICAR(FILTRO(matriz, rango_de_criterios=criterios), [sort_index], [sort_order], [by_col])
La función FILTRO obtiene una matriz de valores según los criterios que defina y pasa esa matriz al primer argumento de ORDENAR.
Lo mejor de esta fórmula es que también genera los resultados como un rango de dispersión dinámico, sin tener que presionar Ctrl + Shift + Enter o adivinar en cuántas celdas copiarlo. Como de costumbre, escribe una fórmula en la celda superior y presiona la tecla Intro.
Como ejemplo, vamos a extraer elementos con una cantidad igual o superior a 30 (>=30) de los datos de origen en A2:B9 y organizar los resultados en orden ascendente.
Para esto, primero configuramos la condición, digamos, en la celda E2 como se muestra en la imagen a continuación. Y luego, construya nuestra fórmula SORT de Excel de esta manera:
=SORT(FILTER(A2:B9, B2:B9>=E2), 2)
Además de la matriz generada por la función FILTER, solo especificamos el argumento sort_index (columna 2). Los dos argumentos restantes se omiten porque los valores predeterminados funcionan exactamente como necesitamos (ordenación ascendente, por fila).
Obtenga N valores más grandes o más pequeños y ordene los resultados
Al analizar grandes cantidades de información, a menudo existe la necesidad de extraer una cierta cantidad de valores principales. Tal vez no solo extraerlos, sino también organizarlos en el orden deseado. E idealmente, elija qué columnas incluir en los resultados. ¿Suena complicado? ¡No con las nuevas funciones de matriz dinámica!
Aquí hay una fórmula genérica:
ÍNDICE(ORDENAR(…), SECUENCIA(n), {columna1_a_retorno, columna2_a_retorno, …})
Donde n es el número de valores que desea devolver.
A partir del siguiente conjunto de datos, suponga que desea obtener una lista de los 3 principales según los números de la columna C.
Para hacerlo, primero ordena la matriz A2:C13 por la tercera columna en orden descendente:
SORT(A2:C13, 3, -1)
Y luego, anide la fórmula anterior en el primer argumento (matriz) de la función ÍNDICE para ordenar la matriz de mayor a menor.
Para el segundo argumento (row_num), que indica cuántas filas devolver, genere los números secuenciales necesarios mediante la función SEQUENCE. Como necesitamos 3 valores principales, usamos SEQUENCE(3), que es lo mismo que proporcionar una constante de matriz vertical {1;2;3} directamente en la fórmula.
Para el tercer argumento (col_num), que define cuántas columnas devolver, proporcione los números de columna en forma de constante de matriz horizontal. Queremos devolver las columnas B y C, por lo que usamos la matriz {2,3}.
Finalmente, obtenemos la siguiente fórmula:
=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})
Y produce exactamente los resultados que queremos:
Regresar 3 fondo valores, simplemente ordene los datos originales de menor a mayor. Para esto, cambie el argumento sort_order de -1 a 1:
=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})
Devolver un valor ordenado en una posición específica
Mirando desde otro ángulo, ¿qué sucede si solo desea devolver una posición de clasificación específica? Digamos, ¿solo el primer registro, solo el segundo o solo el tercero de la lista ordenada? Para hacerlo, use la versión simplificada de la fórmula INDEX SORT discutida anteriormente:
ÍNDICE(ORDENAR(…), n, {columna1_a_retorno, columna2_a_retorno, …})
Donde n es la posición de interés.
Por ejemplo, para obtener una posición particular desde arriba (es decir, de los datos ordenados de forma descendente), use esta fórmula:
=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})
Para obtener una posición específica desde abajo (es decir, de los datos ordenados de forma ascendente), use este:
=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})
Donde A2:C13 son datos de origen, F1 es la posición desde arriba, I1 es la posición desde abajo y {2,3} son las columnas que se devolverán.
Use la tabla de Excel para obtener una matriz de ordenación para expandirse automáticamente
Como ya sabe, la matriz ordenada se actualiza automáticamente cuando realiza cambios en los datos originales. Este es el comportamiento estándar de todas las funciones de matrices dinámicas, incluido SORT. Sin embargo, cuando agrega nuevas entradas fuera de la matriz a la que se hace referencia, no se incluyen automáticamente en una fórmula. Si desea que su fórmula responda a dichos cambios, convierta el rango de origen en una tabla de Excel completamente funcional y use referencias estructuradas en su fórmula.
Para ver cómo funciona en la práctica, considere el siguiente ejemplo.
Supongamos que utiliza la siguiente fórmula SORT de Excel para organizar los valores en el rango A2:B8 en orden alfabético:
=SORT(A2:B8, 1, 1)
Luego, ingresa una nueva entrada en la fila 9… y se siente decepcionado al ver que la entrada recién agregada queda fuera del rango de derrame:
Ahora, convierta el rango de origen en una tabla. Para esto, simplemente seleccione su rango, incluidos los encabezados de columna (A1: B8) y presione Ctrl + T. Al crear su fórmula, seleccione el rango de origen con el mouse y el nombre de la tabla se insertará en la fórmula automáticamente (esto se llama una referencia estructurada):
=SORT(Table1, 1, 1)
Cuando escribe una nueva entrada justo debajo de la última fila, la tabla se expandirá automáticamente y los nuevos datos se incluirán en el rango de derrame de la fórmula ORDENAR:
La función ORDENAR de Excel no funciona
Si su fórmula SORT da como resultado un error, lo más probable es que se deba a las siguientes razones.
Error #NAME: versión anterior de Excel
ORDENAR es una función nueva y solo funciona en Excel 365 y Excel 2021. En versiones anteriores donde esta función no es compatible, un #NAME? se produce un error.
#Error SPILL: algo bloquea el rango de derrame
Si una o más celdas en el rango de derrame no están completamente en blanco o combinadas, un #¡DERRAME! se muestra el error. Para solucionarlo, simplemente elimine el bloqueo. Para obtener más información, consulte Excel #SPILL! error: qué significa y cómo solucionarlo.
Error #VALUE: argumentos no válidos
Siempre que te encuentres con un #¡VALOR! error, verifique los argumentos sort_index y sort_order. Sort_index no debe exceder el número de columnas de la matriz, y sort_order debe ser 1 (ascendente) o -1 (descendente).
#REF error: el libro de origen está cerrado
Dado que las matrices dinámicas tienen soporte limitado para referencias entre libros de trabajo, la función SORT requiere que ambos archivos estén abiertos. Si el libro de trabajo de origen está cerrado, una fórmula arrojará un #REF. error. Para solucionarlo, simplemente abra el archivo al que se hace referencia.
Así es como ordenar datos en Excel usando fórmula. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)