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, los datos 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 nueva forma de ordenar datos con fórmulas: ¡muy conveniente e increíblemente fácil de usar!
Función de Excel ORDENAR
La función ORDENAR en Excel está diseñada para ordenar un rango o una matriz en función de los valores de otro rango o matriz. La clasificación se puede hacer en una o más columnas.
SORTBY es una de las seis nuevas funciones de matriz dinámica disponibles en Excel para Microsoft 365 y Excel 2021. El resultado es una matriz dinámica que se propaga 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 los otros son opcionales:
ORDENAR POR(matriz, por_matriz1, [sort_order1], [by_array2, sort_order2],…)
Matriz (obligatorio): el rango de celdas o la matriz de valores para ordenar.
por_matriz1 (requerido) – el rango o matriz por ordenar.
sort_order1 (opcional) – orden de clasificación:
- 1 u omitido (predeterminado) – ascendente
- -1 – descendiente
Por_matriz2 / sort_order2… (opcional) – pares adicionales de matriz/orden para usar para ordenar.
¡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.
La 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 matriz y todo por_matriz los argumentos deben tener tamaños compatibles. Por ejemplo, cuando ordena por dos columnas, matriz, por_array1 y por_array2 deben tener el mismo número de filas; de lo contrario un error #VALOR Pasará.
- Si la matriz devuelta por SORTBY es el resultado final (salida a una celda y no pasada a otra función), Excel crea un rango de desbordamiento dinámico y lo llena con los resultados. Así que asegúrese de tener suficientes celdas vacías debajo 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 matriz referencia. Para hacer 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 ORDENAR en Excel
Este es un escenario típico del uso de una fórmula SORTBY en Excel:
Suponga que tiene una lista de proyectos con Valor campo. Desea ordenar los proyectos por su valor en una hoja aparte. Dado que otros usuarios no necesitan ver los números, prefiere no incluir Valor la columna de resultados.
La tarea se puede realizar fácilmente con la función SORTBY, para la cual proporciona los siguientes argumentos:
- Matriz es A2:A10 – porque no quieres eso Valor columna que se mostrará en los resultados, déjela fuera de la matriz.
- por_matriz1 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 ingresar llave. Los resultados se «vierten» 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)
Dónde Hoja 1 es la hoja de cálculo que contiene los datos originales.
Usando la función ORDENAR en Excel – fórmulas de ejemplo
A continuación, encontrará algunos ejemplos del uso de SORTBY que esperamos le 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 otro nivel de clasificación?
Suponiendo que nuestra tabla de muestra tiene dos campos, ESTADO (columna B) y Valor (columna C)queremos ordenar por primero ESTADO alfabéticamente, luego después Valor Descendiente.
Para ordenar por dos columnas, simplemente agregamos otro par de por_matriz / Orden de clasificación argumentos:
- Matriz es A2:C10; esta vez, queremos incluir las tres columnas en los resultados.
- por_matriz1 es B2:B10 – primero ordenar por ESTADO.
- sort_order1 es 1 – ordenar alfabéticamente de la A a la Z.
- Por_matriz2 es C2:C10 – luego ordenar 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 dijimos:
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 como esta:
ORDENAR(matriz, COINCIDIR(range_to_sort, lista personalizada0))
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ógico», por ejemplo, por importancia, en lugar de alfabéticamente.
Para hacer esto, primero creamos una lista personalizada en el orden de clasificación deseado (En curso, Hecho, 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 dominio de origen para matriz (A2:C10), el ESTADO columna para range_to_sort (B2:B10) y la lista personalizada para la que la creé lista personalizada (E2:E4).
=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0))
Como resultado, los proyectos se ordenan por su estado exactamente según sea necesario:
Para ordenar por la lista personalizada en orden inverso, coloque -1 para sort_order1 argumento:
=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0), -1)
Y tendrás los proyectos ordenados en sentido contrario:
¿Quiere ordenar aún más los registros en cada estado? Ningún problema. Simplemente agregue otro nivel de clasificación 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 cambio de tamaño 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. Dado que nuestra matriz de origen (C2:C10) contiene 9 filas y la lista personalizada (E2:E4) solo 3 filas, no podemos proporcionarla directamente a por_matriz argumento. En su lugar, usamos la función MATCH para crear una matriz de 9 filas:
MATCH(B2:B10, E2:E5, 0)
Aquí, usamos 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 dato ESTADO valor de la lista personalizada:
{1;3;2;1;3;2;2;1;2}
Esta matriz va directamente a por_matriz argumento a 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.
Aleatorizar en Excel con una fórmula
En versiones anteriores de Excel, podía 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 POR(matrizRANDARRAY(FILAS(matriz)))
Dónde matriz son los datos de origen que desea mezclar.
Esta fórmula genérica funciona tanto para una lista 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 los datos en A2:C10 manteniendo las filas juntas, use esto:
=SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10)))
Cómo funciona esta fórmula:
La función RANDARRAY produce una matriz de números aleatorios para usar en la clasificación y se la pasa a por_matriz Argumento ORDENAR. Para especificar cuántos números aleatorios generar, cuente el número de filas en el rango de origen usando la función FILAS y «alimente» ese número a FILA Argumento de RANDARRAY. ¡Esto es!
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 serán llamados con cada cambio de hoja. Para evitar la recursividad automática, puede utilizar Pascua especial > existencias característica para reemplazar 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 para por_matriz Argumento ORDENAR. 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 de texto en orden ascendente:
Así es como se usa la función ORDENAR en Excel para realizar una clasificación personalizada y otras cosas. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
