Hoy veremos de cerca la sintaxis y los usos típicos de la nueva función SORTBY de matriz dinámica. Aprenderá cómo ordenar de forma personalizada en Excel con una fórmula, ordenar aleatoriamente una lista, organizar celdas por longitud de texto y más.
Microsoft Excel ofrece varias formas de organizar los datos de texto alfabéticamente, las fechas cronológicamente y los números de menor a mayor o de mayor a menor. También hay una forma de ordenar por sus propias listas personalizadas. Además de la funcionalidad Ordenar convencional, Excel 365 presenta una forma completamente nueva de ordenar datos con fórmulas: ¡muy conveniente e increíblemente fácil de usar!
Función ORDENAR de Excel
La función ORDENAR en Excel está diseñada para ordenar un rango o matriz en función de los valores de otro rango o matriz. La clasificación se puede hacer por una o varias columnas.
SORTBY es una de las seis nuevas funciones de matriz dinámica disponibles en Excel para Microsoft 365 y Excel 2021. Su resultado es una matriz dinámica que se extiende a las celdas vecinas y se actualiza automáticamente cuando cambian los datos de origen.
La función SORTBY tiene un número variable de argumentos: los dos primeros son obligatorios y el otro es opcional:
ORDENAR POR(matriz, por_matriz1, [sort_order1], [by_array2, sort_order2],…)
Formación (obligatorio): el rango de celdas o la matriz de valores que se ordenarán.
por_matriz1 (requerido) – el rango o matriz por ordenar.
Sort_order1 (opcional) – el orden de clasificación:
- 1 u omitido (predeterminado) – ascendente
- -1 – descendente
Por_matriz2 / Sort_order2… (opcional): pares adicionales de matriz/orden para usar en la clasificación.
¡Nota IMPORTANTE! Actualmente, la función ORDENAR solo está disponible con suscripciones de Microsoft 365 y Excel 2021. En Excel 2019, Excel 2016 y versiones anteriores, la función ORDENAR no está disponible.
Función SORTBY – 4 cosas para recordar
Para que una fórmula SORTBY de Excel funcione correctamente, hay algunos puntos importantes a tener en cuenta:
- Los argumentos By_array deben tener una fila de alto o una columna de ancho.
- La matriz y todos los argumentos por_matriz deben tener dimensiones compatibles. Por ejemplo, al ordenar por dos columnas, array, by_array1 y by_array2 deben tener el mismo número de filas; de lo contrario un error #VALOR ocurrira.
- Si la matriz devuelta por SORTBY es el resultado final (salida en una celda y no pasada a otra función), Excel crea un rango de dispersión dinámico y lo llena con los resultados. Por lo tanto, asegúrese de tener suficientes celdas vacías hacia abajo y/o a la derecha de la celda donde ingresa la fórmula, de lo contrario obtendrá un #DERRAME error.
- Los resultados de las fórmulas SORTBY se actualizan automáticamente cada vez que cambian los datos de origen. Sin embargo, las nuevas entradas que se agregan fuera de la matriz a la que se hace referencia en la fórmula no se incluyen en los resultados a menos que actualice la referencia de la matriz. Para que la matriz a la que se hace referencia se expanda automáticamente, convierta el rango de origen en una tabla de Excel o cree un rango dinámico con nombre.
Fórmula básica ORDENAR en Excel
Aquí hay un escenario típico de usar una fórmula SORTBY en Excel:
Supongamos que tiene una lista de proyectos con el campo Valor. Desea ordenar los proyectos por su valor en una hoja aparte. Como otros usuarios no necesitan ver los números, preferiría no incluir la columna Valor en los resultados.
La tarea se puede realizar fácilmente con la función SORTBY, para la cual proporciona los siguientes argumentos:
- La matriz es A2:A10: dado que no desea que la columna Valor se muestre en los resultados, la deja fuera de la matriz.
- By_array1 es B2:B10 – ordenar por Valor.
- Sort_order1 es -1 – descendente, es decir, de mayor a menor.
Poniendo los argumentos juntos, obtenemos esta fórmula:
=SORTBY(A2:B10, B2:B10, -1)
Para simplificar, usamos la fórmula en la misma hoja: ingrésela en D2 y presione la tecla Intro. Los resultados se «derraman» automáticamente en tantas celdas como sea necesario (D2:D10 en nuestro caso). Pero, técnicamente, la fórmula solo está en la primera celda y, al eliminarla de D2, se eliminarán todos los resultados.
Cuando se usa en otra hoja, la fórmula toma la siguiente forma:
=SORTBY(Sheet1!A2:A10, Sheet1!B2:B10, -1)
Donde Sheet1 es la hoja de cálculo que contiene los datos originales.
Usando la función ORDENAR en Excel – ejemplos de fórmulas
A continuación, encontrará algunos ejemplos más del uso de SORTBY, que esperamos resulten útiles y reveladores.
Ordenar por varias columnas
La fórmula básica discutida anteriormente ordena los datos por una columna. Pero, ¿qué sucede si necesita agregar un nivel más de clasificación?
Suponiendo que nuestra tabla de muestra tiene dos campos, Estado (columna B) y Valor (columna C), deseamos ordenar primero por Estado alfabéticamente y luego por Valor de forma descendente.
Para ordenar por dos columnas, solo agregamos un par más de los argumentos by_array / sort_order:
- La matriz es A2:C10; esta vez, queremos incluir las tres columnas en los resultados.
- By_array1 es B2:B10 – primero, ordene por Estado.
- Sort_order1 es 1 – ordenar alfabéticamente de la A a la Z.
- By_array2 es C2:C10; luego, ordene por Valor.
- Sort_order2 es -1 – ordenar de mayor a menor.
Como resultado, obtenemos la siguiente fórmula:
=SORTBY(A2:B10, B2:B10, 1, C2:C10, -1)
Lo que reorganiza nuestros datos exactamente como le indicamos:
Clasificación personalizada en Excel con una fórmula
Para clasificar los datos en un orden personalizado, puede usar la función de clasificación personalizada de Excel o crear una fórmula SORTBY MATCH de esta manera:
ORDENAR(matriz, COINCIDIR(rango_a_ordenar, lista_personalizada, 0))
Echando un vistazo más de cerca a nuestro conjunto de datos, probablemente le resulte más conveniente ordenar los proyectos por su estado «lógicamente», por ejemplo, por importancia, en lugar de alfabéticamente.
Para hacerlo, primero creamos una lista personalizada en el orden de clasificación deseado (En progreso, Completado, En espera) escribiendo cada valor en una celda separada en el rango E2:E4.
Y luego, utilizando la fórmula genérica anterior, proporcionamos el rango de origen para la matriz (A2:C10), la columna Estado para range_to_sort (B2:B10) y la lista personalizada que creamos para custom_list (E2:E4).
=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0))
Como resultado, tenemos los proyectos ordenados por su estado exactamente según sea necesario:
Para ordenar por lista personalizada en orden inverso, coloque -1 para el argumento sort_order1:
=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0), -1)
Y tendrás los proyectos ordenados en la dirección opuesta:
¿Quiere ordenar adicionalmente los registros dentro de cada estado? No hay problema. Simplemente, agregue un nivel de clasificación más a la fórmula, digamos por Valor (C2:C10), y defina el orden de clasificación deseado, ascendente en nuestro caso:
=SORTBY(A2:C10, MATCH(B2:B10, E2:E5, 0), 1, C2:C10, 1)
Una gran ventaja de la fórmula SORTBY sobre la función de clasificación personalizada de Excel es que la fórmula se actualiza automáticamente cada vez que cambian los datos originales, mientras que la función requiere limpieza y reordenación con cada cambio.
Cómo funciona esta fórmula:
Como ya se mencionó, la función SORTBY de Excel solo puede procesar matrices «ordenadas por» cuyas dimensiones sean compatibles con la matriz de origen. Como nuestra matriz de origen (C2:C10) contiene 9 filas y la lista personalizada (E2:E4) solo 3 filas, no podemos suministrarla directamente al argumento by_array. En su lugar, usamos la función MATCH para crear una matriz de 9 filas:
MATCH(B2:B10, E2:E5, 0)
Aquí, usamos la columna Estado (B2:B10) como valores de búsqueda y nuestra lista personalizada (E2:E5) como matriz de búsqueda. El último argumento se establece en 0 para buscar coincidencias exactas. Como resultado, obtenemos una matriz de 9 números, cada uno de los cuales representa la posición relativa de un valor de estado dado en la lista personalizada:
{1;3;2;1;3;2;2;1;2}
Esta matriz va directamente al argumento by_array de la función SORTBY y lo obliga a colocar los datos en el orden correspondiente a los elementos de la matriz, es decir, las primeras entradas representadas por 1, luego las entradas representadas por 2, y así sucesivamente.
Clasificación aleatoria en Excel con una fórmula
En versiones anteriores de Excel, puede ordenar aleatoriamente con la función RAND como se explica en este tutorial: Cómo ordenar aleatoriamente una lista en Excel.
En el nuevo Excel, puede usar una función RANDARRAY más poderosa junto con ORDENAR:
ORDENAR(matriz, RANDARRAY(FILAS(matriz)))
Donde matriz son los datos de origen que desea mezclar.
Esta fórmula genérica funciona tanto para una lista que consta de una sola columna como para un rango de varias columnas.
Por ejemplo, para ordenar aleatoriamente una lista en A2:A10, use esta fórmula:
=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))
Para mezclar datos en A2:C10 manteniendo las filas juntas, use este:
=SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10)))
Cómo funciona esta fórmula:
La función RANDARRAY produce una matriz de números aleatorios que se usarán para ordenar, y la pasa en el argumento by_array de SORTBY. Para especificar cuántos números aleatorios generar, cuenta el número de filas en el rango de origen usando la función ROWS y «alimenta» ese número al argumento de filas de RANDARRAY. ¡Eso es todo!
Nota. Al igual que su predecesor, RANDARRAY es una función volátil y genera una nueva matriz de números aleatorios cada vez que se vuelve a calcular la hoja de cálculo. Como resultado, sus datos se volverán a utilizar con cada cambio en la hoja. Para evitar el recurso automático, puede usar la función Pegado especial > Valores para reemplazar las fórmulas con sus valores.
Ordenar celdas por longitud de cadena
Para ordenar las celdas por la longitud de las cadenas de texto que contienen, use la función LEN para contar la cantidad de caracteres en cada celda y proporcione las longitudes calculadas al argumento by_array de SORTBY. El argumento sort_order se puede establecer en 1 o -1, según el orden de clasificación preferido.
Para ordenar por cadena de texto de menor a mayor:
ORDENAR(matriz, LEN(matriz), 1)
Para ordenar por cadena de texto de mayor a menor:
ORDENAR(matriz, LEN(matriz), -1)
Y aquí hay una fórmula que demuestra este enfoque en datos reales:
=SORTBY(A2:A7, LEN(A2:A7), 1)
Donde A2:A7 son las celdas originales que desea ordenar por longitud de texto en orden ascendente:
ORDENAR vs ORDENAR
En el grupo de nuevas funciones de matrices dinámicas de Excel, hay dos diseñadas para ordenar. A continuación, enumeramos las diferencias y similitudes más esenciales, así como cuándo es mejor usar cada una.
- A diferencia de la función SORT, SORTBY no requiere que la matriz «ordenar por» forme parte de la matriz de origen, ni es necesario que aparezca en los resultados. Entonces, cuando su tarea es ordenar un rango basado en otra matriz independiente o una lista personalizada, ORDENAR es la función adecuada para usar. Si está buscando ordenar un rango en función de sus propios valores, ORDENAR es más apropiado.
- Ambas funciones admiten múltiples niveles de clasificación y ambas se pueden encadenar con otras funciones convencionales y de matrices dinámicas.
- Ambas funciones solo están disponibles para usuarios de Excel 365 y Excel 2021.
La función ORDENAR de Excel no funciona
En caso de que su fórmula SORTBY devuelva un error, lo más probable es que se deba a una de las siguientes razones.
Argumentos by_array no válidos
Los argumentos by_array deben ser de una sola fila o una sola columna y compatibles en tamaño con el argumento de la matriz. Por ejemplo, si matriz tiene 10 filas, by_array también debe incluir 10 filas. De lo contrario, un #VALOR! se produce un error.
Argumentos sort_order no válidos
Los argumentos sort_order solo pueden ser 1 (ascendente) o -1 (descendente). Si no se establece ningún valor, ORDENAR por defecto es en orden ascendente. Si se establece cualquier otro valor, un #VALOR! se devuelve el error.
No hay suficiente espacio para los resultados.
Como cualquier otra función de matriz dinámica, SORTBY derrama los resultados en un rango actualizable y redimensionable automáticamente. Si no hay suficientes celdas vacías para mostrar todos los valores, ¡un #DERRAME! se lanza el error.
El libro de origen está cerrado
Si una fórmula ORDENAR hace referencia a otro archivo de Excel, ambos libros deben estar abiertos. Si el libro de trabajo de origen está cerrado, un #REF! se produce un error.
Su versión de Excel no admite matrices dinámicas
Cuando se usa en una versión predinámica de Excel, la función ORDENAR devuelve un #NOMBRE? error.
Así es como se usa la función ORDENAR en Excel para hacer una ordenación personalizada y otras cosas. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)