Contenido
Si necesita ordenar la lista, hay muchas formas a su servicio, la más fácil de las cuales son los botones de ordenación en la pestaña o en el menú. Datos (Datos — Ordenar). Sin embargo, hay situaciones en las que la clasificación de la lista debe realizarse automáticamente, es decir, fórmulas. Esto puede ser necesario, por ejemplo, al generar datos para una lista desplegable, al calcular datos para gráficos, etc. ¿Cómo ordenar una lista con una fórmula sobre la marcha?
Método 1. Datos numéricos
Si la lista contiene solo información numérica, entonces la clasificación se puede hacer fácilmente usando las funciones MENOS (PEQUEÑA) и LÍNEA (FILA):
Función MENOS (PEQUEÑA) extrae de la matriz (columna A) el n-ésimo elemento más pequeño de una fila. Aquellos. PEQUEÑO(A:A;1) es el número más pequeño de la columna, PEQUEÑO(A:A;2) es el segundo más pequeño, y así sucesivamente.
Función LÍNEA (FILA) devuelve el número de fila de la celda especificada, es decir, FILA(A1)=1, FILA(A2)=2, etc. En este caso, se utiliza simplemente como generador de una secuencia de números n=1,2,3... para nuestra lista ordenada. Con el mismo éxito, fue posible hacer una columna adicional, llenarla manualmente con la secuencia numérica 1,2,3… y referirla en lugar de la función FILA.
Método 2. Lista de texto y fórmulas regulares
Si la lista no contiene números, sino texto, entonces la función PEQUEÑO ya no funcionará, por lo que debe seguir un camino diferente, un poco más largo.
Primero, agreguemos una columna de servicio con una fórmula donde el número de serie de cada nombre en la futura lista ordenada se calculará usando la función COUNTIF (CONTAR.SI):
En la versión en inglés será:
=CONTAR.SI(A:A,»<"&A1)+CONTAR.SI($A$1:A1,"="&A1)
El primer término es una función para contar el número de celdas que son menores que la actual. El segundo es una red de seguridad en caso de que algún nombre aparezca más de una vez. Entonces no tendrán los mismos números, sino que aumentarán sucesivamente.
Ahora los números recibidos deben organizarse secuencialmente en orden ascendente. Para esto puedes usar la función MENOS (PEQUEÑA) de la primera manera:
Bueno, finalmente, solo queda sacar los nombres de la lista por sus números. Para hacer esto, puede usar la siguiente fórmula:
Función MÁS EXPUESTO (JUEGO) busca en la columna B el número de serie deseado (1, 2, 3, etc.) y, de hecho, devuelve el número de la línea donde se encuentra este número. Función ÍNDICE (ÍNDICE) saca de la columna A el nombre en este número de línea.
Método 3: fórmula de matriz
Este método es, de hecho, el mismo algoritmo de ubicación que en el Método 2, pero implementado mediante una fórmula de matriz. Para simplificar la fórmula, al rango de celdas C1:C10 se le dio el nombre Lista (seleccione celdas, presione Ctrl + F3 y botón Crear):
En la celda E1, copia nuestra fórmula:
=ÍNDICE(Lista; COINCIDIR(PEQUEÑO(CONTAR.SI(Lista; “<"&Lista); FILA(1:1)); CONTAR.SI(Lista; "<"&Lista); 0))
O en la versión en inglés:
=ÍNDICE(Lista, COINCIDIR(PEQUEÑO(CONTAR.SI(Lista, «<"&Lista), FILA(1:1)), CONTAR.SI(Lista, "<"&Lista), 0))
y empujar Ctrl + Shift + Enterpara ingresarlo como una fórmula matricial. Luego, la fórmula resultante se puede copiar a lo largo de toda la lista.
Si desea que la fórmula no tenga en cuenta un rango fijo, sino que pueda ajustarse al agregar nuevos elementos a la lista, deberá cambiar ligeramente la estrategia.
Primero, el rango de la lista deberá establecerse dinámicamente. Para hacer esto, al crear, debe especificar no un rango fijo C3: C10, sino una fórmula especial que se referirá a todos los valores disponibles, independientemente de su número. Hacer clic Alt + F3 o abre la pestaña Fórmulas – Administrador de nombres (Fórmulas — Administrador de nombres), crea un nuevo nombre y en el campo Enlace (Referencia) ingrese la siguiente fórmula (supongo que el rango de datos a ordenar comienza desde la celda C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=DESPLAZAMIENTO(C1,0,0,SCHÖTZ(C1:C1000),1)
En segundo lugar, la fórmula de matriz anterior deberá estirarse con un margen, con la expectativa de que se ingresen datos adicionales en el futuro. En este caso, la fórmula de matriz comenzará a dar un error #NÚMERO en las celdas que aún no están llenas. Para interceptarlo, puedes usar la función IFERROR, que debe agregarse "alrededor" de nuestra fórmula de matriz:
=IFERROR (ÍNDICE(Lista; COINCIDIR(PEQUEÑO(CONTAR.SI(Lista; “<"&Lista); FILA(1:1)); CONTAR.SI(Lista; "<"&Lista); 0));»»)
=SI.ERROR(NDEX(Lista, COINCIDIR(PEQUEÑO(CONTAR.SI(Lista, «<"&Lista), FILA(1:1)), CONTAR.SI(Lista, "<"&Lista), 0));"")
Capta el error #NUMBER y genera un vacío (comillas vacías) en su lugar.
:
- Ordenar rango por color
- ¿Qué son las fórmulas de matriz y por qué son necesarias?
- Clasificación SORT y arreglos dinámicos en el nuevo Office 365