contingut
Si necessiteu ordenar la llista, hi ha moltes maneres al vostre servei, la més fàcil de les quals són els botons d'ordenació de la pestanya o del menú. dades (Dades — Ordena). Tanmateix, hi ha situacions en què l'ordenació de la llista s'ha de fer automàticament, és a dir, fórmules. Això pot ser necessari, per exemple, quan es generen dades per a una llista desplegable, quan es calculen dades per a gràfics, etc. Com ordenar una llista amb una fórmula sobre la marxa?
Mètode 1. Dades numèriques
Si la llista només conté informació numèrica, l'ordenació es pot fer fàcilment mitjançant les funcions MENYS (PETIT) и LÍNIA (FILERA):
function MENYS (PETIT) treu de la matriu (columna A) l'enèsim element més petit en una fila. Aquells. PETIT(A:A;1) és el nombre més petit de la columna, PETIT(A:A;2) és el segon més petit, i així successivament.
function LÍNIA (FILERA) retorna el número de fila per a la cel·la especificada, és a dir, FILA(A1)=1, FILA(A2)=2, etc. En aquest cas, s'utilitza simplement com a generador d'una seqüència de nombres n=1,2,3... per la nostra llista ordenada. Amb el mateix èxit, va ser possible fer una columna addicional, omplir-la manualment amb la seqüència numèrica 1,2,3... i fer-hi referència en lloc de la funció FILA.
Mètode 2. Llista de text i fórmules regulars
Si la llista no conté números, sinó text, aleshores la funció PETIT ja no funcionarà, de manera que haureu de seguir un camí diferent, una mica més llarg.
Primer, afegim una columna de servei amb una fórmula on es calcularà el número de sèrie de cada nom a la llista ordenada futura mitjançant la funció COUNTIF (COUNTIF):
En la versió anglesa serà:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
El primer terme és una funció per comptar el nombre de cel·les que són inferiors a l'actual. El segon és una xarxa de seguretat per si algun nom es presenta més d'una vegada. Aleshores no tindran el mateix nombre, però successivament augmentant.
Ara els números rebuts s'han d'ordenar seqüencialment en ordre ascendent. Per a això podeu utilitzar la funció MENYS (PETIT) de la primera manera:
Bé, finalment, només queda treure els noms de la llista pels seus números. Per fer-ho, podeu utilitzar la fórmula següent:
function MÉS EXPOSADA (PARTIDA) cerca a la columna B el número de sèrie desitjat (1, 2, 3, etc.) i, de fet, retorna el número de la línia on es troba aquest número. Funció ÍNDEX (ÍNDEX) treu de la columna A el nom d'aquest número de línia.
Mètode 3: Fórmula de matriu
Aquest mètode és, de fet, el mateix algorisme de col·locació que al Mètode-2, però implementat mitjançant una fórmula matricial. Per simplificar la fórmula, el rang de cel·les C1:C10 va rebre el nom llista (seleccioneu cel·les, premeu Ctrl + F3 i botó Create):
A la cel·la E1, copieu la nostra fórmula:
=ÍNDEX(Llista; COINCIDENCIAR(SMALL(COUNTIF(Llista; “<"&Llista); FILA(1:1)); COUNTIF(Llista; "<"&Llista); 0))
O en la versió anglesa:
=ÍNDEX(Llista, COINCORDA(SMALL(COUNTIF(Llista, «<"&Llista), FILA(1:1)), COUNTIF(Llista, "<"&Llista), 0))
i empeny Ctrl + Maj + Retornper introduir-lo com a fórmula matricial. Aleshores, la fórmula resultant es pot copiar per tota la longitud de la llista.
Si voleu que la fórmula no tingui en compte un rang fix, sinó que es pugui ajustar en afegir nous elements a la llista, haureu de canviar lleugerament l'estratègia.
En primer lloc, l'interval de la llista s'haurà de configurar dinàmicament. Per fer-ho, en crear, heu d'especificar no un rang fix C3:C10, sinó una fórmula especial que farà referència a tots els valors disponibles, independentment del seu nombre. Feu clic Alt + F3 o obre la pestanya Fórmules – Gestor de noms (Fórmules — Gestor de noms), creeu un nom nou i al camp Enllaç (Referència) introduïu la fórmula següent (suposo que l'interval de dades a ordenar comença des de la cel·la C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
En segon lloc, la fórmula de matriu anterior s'haurà d'estendre amb un marge, amb l'expectativa d'introduir dades addicionals en el futur. En aquest cas, la fórmula de matriu començarà a donar un error #NUMBER a les cel·les que encara no s'han omplert. Per interceptar-lo, podeu utilitzar la funció SI ERROR, que cal afegir "al voltant" de la nostra fórmula de matriu:
=SI ERROR(ÍNDEX(Llista; COINCORDA(PETIT(COMPTARSI(Llista; “<"&Llista); FILA(1:1)); COUNTIF(Llista; "<"&Llista); 0));»»)
=SIERROR(NDEX(Llista, COINCORDA(SMALL(COUNTIF(Llista, «<"&Llista), FILA(1:1)), COUNTIF(Llista, "<"&Llista), 0));"")
Capta l'error #NUMBER i en el seu lloc genera un buit (cometes buides).
:
- Ordena la gamma per color
- Què són les fórmules de matriu i per què es necessiten
- L'ORDENACIÓ i les matrius dinàmiques al nou Office 365