Función SORTBY de Excel: clasificación personalizada con fórmula

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 realizar 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(matriz, por_matriz1, [sort_order1], [by_array2, sort_order2],…)

Formación (obligatorio): el rango de celdas o conjunto de valores que se ordenarán.

Por_matriz1 (obligatorio): el rango o matriz por el que ordenar.

ordenar_orden1 (opcional) – el orden de clasificación:

• 1 u omitido (predeterminado): ascendente
• -1 – descendente

Por_matriz2 / ordenar_orden2… (opcional): pares de matriz/orden adicionales para usar en la clasificación.

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:

• Por_matriz Los argumentos deben tener una fila de alto o una columna de ancho.
• El formación y todo por_matriz Los argumentos deben tener dimensiones compatibles. Por ejemplo, al ordenar por dos columnas, formación, por_array1 y por_array2 debe 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 derrame dinámico y lo completa con los resultados. Por lo tanto, asegúrese de tener suficientes celdas vacías abajo y/o a la derecha de la celda donde ingresa la fórmula; de lo contrario, obtendrá un #ErrorDERRAME.
• 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 formación referencia. 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 con nombre dinámico.

Fórmula básica SORTBY en Excel

Este es un escenario típico de uso de una fórmula SORTBY en Excel:

Supongamos que tiene una lista de proyectos con el Valor campo. Desea ordenar los proyectos por su valor en una hoja separada. Como otros usuarios no necesitan ver los números, preferirás no incluirlos. Valor columna en los resultados.

La tarea se puede realizar fácilmente con la función SORTBY, para la cual se proporcionan los siguientes argumentos:

• Formación es A2:A10 – ya que no desea el Valor columna que se mostrará en los resultados, la deja fuera de la matriz.
• Por_matriz1 es B2:B10 – ordenar por Valor.
• ordenar_orden1 es -1 – descendente, es decir, de mayor a menor.

Juntando los argumentos 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 el botón Ingresar llave. Los resultados se «derramarán» automáticamente a tantas celdas como sea necesario (D2:D10 en nuestro caso). Pero técnicamente, la fórmula está solo 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)

Dónde Hoja1 es la hoja de trabajo que contiene los datos originales.

Uso de la función SORTBY 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 analizada anteriormente ordena los datos por una columna. Pero ¿qué pasa si necesitas agregar un nivel más de clasificación?

Suponiendo que nuestra tabla de muestra tiene dos campos, Estado (columna B) y Valor (columna C)queremos ordenar primero por Estado alfabéticamente y luego por Valor descendiendo.

Para ordenar por dos columnas, simplemente agregamos un par más de por_matriz / Orden de clasificación argumentos:

• Formación es A2:C10; esta vez, queremos incluir las tres columnas en los resultados.
• Por_matriz1 es B2:B10 – primero, ordenar por Estado.
• ordenar_orden1 es 1: ordene alfabéticamente de la A a la Z.
• Por_matriz2 es C2:C10; luego, ordene por Valor.
• ordenar_orden2 es -1: ordena 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:
>

Ordenación personalizada en Excel con una fórmula

Para ordenar los datos en un orden personalizado, puede utilizar la función Ordenación personalizada de Excel o crear una fórmula ORDENAR COINCIDENCIA de esta manera:

ORDENAR(matriz, COINCIDIR(rango_para_ordenar, lista personalizada0))

Si observa más de cerca nuestro conjunto de datos, probablemente le resultará más conveniente ordenar los proyectos por su estado «lógicamente», es decir, por importancia, en lugar de alfabéticamente.

Para hacerlo, primero creamos una lista personalizada en el orden de clasificación deseado (En curso, Terminado, 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 formación (A2:C10), el Estado columna para rango_para_ordenar (B2:B10), y la lista personalizada que creamos para lista personalizada (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 ordenar_orden1 argumento:

=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0), -1)

Y tendrás los proyectos ordenados en la dirección opuesta:
>

¿Quiere ordenar registros adicionalmente dentro de cada estado? Ningún problema. Simplemente, agregue un nivel de clasificación más a la fórmula, digamos por Valor (C2:C10), y definir el orden deseado de clasificación, 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 Ordenació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 «ordenar por» cuyas dimensiones sean compatibles con la matriz de origen. Como nuestra matriz fuente (C2:C10) contiene 9 filas y la lista personalizada (E2:E4) solo 3 filas, no podemos suministrarla directamente al por_matriz argumento. En su lugar, usamos la función COINCIDIR para crear una matriz de 9 filas:

MATCH(B2:B10, E2:E5, 0)

Aquí utilizamos el Estado columna (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 determinado Estado valor en la lista personalizada:

{1;3;2;1;3;2;2;1;2}

Esta matriz va directamente al por_matriz argumento de la función SORTBY y la 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.

Ordenación aleatoria en Excel con una fórmula

En versiones anteriores de Excel, puede realizar una clasificación aleatoria con la función ALEATORIO como se explica en este tutorial: Cómo ordenar aleatoriamente una lista en Excel.

En el nuevo Excel, puede utilizar una función RANDARRAY más potente junto con SORTBY:

ORDENAR POR(formaciónRANDARRAY(FILAS(formación)))

Dónde formación 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 por_matriz argumento de SORTBY. Para especificar cuántos números aleatorios generar, cuente el número de filas en el rango de origen utilizando la función FILAS y «alimente» ese número al filas argumento de RANDARRAY. ¡Eso es todo!

Ordenar celdas por longitud de cadena

Para ordenar celdas por la longitud de las cadenas de texto que contienen, use la función LEN para contar el número de caracteres en cada celda y proporcione las longitudes calculadas al por_matriz argumento de SORTBY. El Orden de clasificación El argumento 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 del texto en orden ascendente:
>