Contidos
Se precisas ordenar a lista, hai moitas formas ao teu servizo, a máis sinxela son os botóns de ordenación da pestana ou do menú. Data (Datos — Ordenar). Non obstante, hai situacións nas que a ordenación da lista debe facerse de forma automática, é dicir, fórmulas. Isto pode ser necesario, por exemplo, ao xerar datos para unha lista despregábel, ao calcular datos para gráficos, etc. Como ordenar unha lista cunha fórmula sobre a marcha?
Método 1. Datos numéricos
Se a lista só contén información numérica, a clasificación pódese facer facilmente mediante as funcións MENOS (PEQUENA) и LIÑA (FILA):
función MENOS (PEQUENA) saca da matriz (columna A) o n-ésimo elemento máis pequeno nunha fila. Eses. PEQUENO(A:A;1) é o número máis pequeno da columna, PEQUENO(A:A;2) é o segundo máis pequeno, etc.
función LIÑA (FILA) devolve o número de fila para a cela especificada, é dicir, FILA(A1)=1, FILA(A2)=2 etc. Neste caso, úsase simplemente como xerador dunha secuencia de números n=1,2,3... para nosa lista ordenada. Co mesmo éxito, foi posible facer unha columna adicional, enchela manualmente coa secuencia numérica 1,2,3... e referirse a ela en lugar da función FILA.
Método 2. Lista de textos e fórmulas regulares
Se a lista non contén números, senón texto, entón a función PEQUENA deixará de funcionar, polo que tes que ir por un camiño diferente, un pouco máis longo.
En primeiro lugar, imos engadir unha columna de servizo cunha fórmula onde se calculará o número de serie de cada nome na futura lista ordenada mediante a función COUNTIF (CONTAR SE):
Na versión en inglés será:
=CONTARSE(A:A,»<"&A1)+CONTARSE($A$1:A1,"="&A1)
O primeiro termo é unha función para contar o número de celas que son menores que a actual. O segundo é unha rede de seguridade por se algún nome ocorre máis dunha vez. Daquela non terán os mesmos números, senón que van en aumento.
Agora os números recibidos deben ordenarse secuencialmente en orde ascendente. Para iso podes usar a función MENOS (PEQUENA) do primeiro xeito:
Ben, finalmente, só queda sacar os nomes da lista polos seus números. Para iso, pode usar a seguinte fórmula:
función MÁIS EXPOÑA (Coincidir) busca na columna B o número de serie desexado (1, 2, 3, etc.) e, de feito, devolve o número da liña onde se atopa este número. Función Index (ÍNDICE) saca da columna A o nome deste número de liña.
Método 3: fórmula matricial
Este método é, de feito, o mesmo algoritmo de colocación que no Método-2, pero implementado mediante unha fórmula matricial. Para simplificar a fórmula, o rango de celas C1:C10 recibiu o nome lista (seleccione celas, prema Ctrl + F3 e botón crear):
Na cela E1, copie a nosa fórmula:
=INDEX(Lista; COINCIDIR(PEQUENO(CONTARSE(Lista; “<"&Lista); FILA(1:1)); CONTARSE(Lista; "<"&Lista); 0))
Ou na versión inglesa:
=INDEX(Lista, COINCIDENCIAR(PEQUENO(CONTARSE(Lista, «<"&Lista), FILA(1:1)), CONTARSE(Lista, "<"&Lista), 0))
e empuxar Ctrl + Maiús + Entrarpara introducilo como fórmula matricial. A continuación, a fórmula resultante pódese copiar en toda a lonxitude da lista.
Se queres que a fórmula non teña en conta un rango fixo, senón que se poida axustar ao engadir novos elementos á lista, terás que cambiar lixeiramente a estratexia.
En primeiro lugar, o intervalo da Lista deberá configurarse de forma dinámica. Para iso, ao crear, cómpre especificar non un intervalo fixo C3:C10, senón unha fórmula especial que fará referencia a todos os valores dispoñibles, independentemente do seu número. Fai clic Alt + F3 ou abra a pestana Fórmulas – Xestor de nomes (Fórmulas — Xestor de nomes), cree un novo nome e no campo ligazón (Referencia) introduza a seguinte fórmula (supoño que o intervalo de datos a ordenar comeza a partir da cela C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1;SCHÖTZ(C1000:C1);XNUMX)
En segundo lugar, a fórmula da matriz anterior terá que estirarse cunha marxe, coa expectativa de que se introduzan datos adicionais no futuro. Neste caso, a fórmula matricial comezará a dar un erro #NUMBER nas celas que aínda non están cubertas. Para interceptalo, pode usar a función SE ERRO, que hai que engadir "ao redor" da nosa fórmula matricial:
=SE ERRO(ÍNDICE(Lista; COINCIDIR(PEQUENO(CONTARSE(Lista; “<"&Lista); FILA(1:1)); CONTARSE(Lista; "<"&Lista); 0));»»)
=SEERRO(NDEX(Lista, COINCIDIR(PEQUENO(CONTARSE(Lista, «<"&Lista), FILA(1:1)), CONTARSE(Lista, "<"&Lista), 0));"")
Detecta o erro #NUMBER e, no seu lugar, mostra un baleiro (comiñas baleiras).
:
- Ordenar intervalos por cor
- Que son as fórmulas matriciales e por que son necesarias
- Ordenar e ordenar matrices dinámicas no novo Office 365