Contenuti
Se hai bisogno di ordinare l'elenco, ci sono molti modi al tuo servizio, il più semplice dei quali sono i pulsanti di ordinamento nella scheda o nel menu Dati (Dati — Ordina). Tuttavia, ci sono situazioni in cui l'ordinamento dell'elenco deve essere eseguito automaticamente, ad esempio le formule. Ciò potrebbe essere necessario, ad esempio, quando si generano dati per un elenco a discesa, quando si calcolano i dati per i grafici, ecc. Come ordinare al volo un elenco con una formula?
Metodo 1. Dati numerici
Se l'elenco contiene solo informazioni numeriche, l'ordinamento può essere eseguito facilmente utilizzando le funzioni MENO (PICCOLO) и LINE (RIGA):
Funzione MENO (PICCOLO) estrae dall'array (colonna A) l'n-esimo elemento più piccolo di una riga. Quelli. SMALL(A:A;1) è il numero più piccolo nella colonna, SMALL(A:A;2) è il secondo più piccolo e così via.
Funzione LINE (RIGA) restituisce il numero di riga per la cella specificata, ovvero ROW(A1)=1, ROW(A2)=2 ecc. In questo caso viene utilizzato semplicemente come generatore di una sequenza di numeri n=1,2,3… per la nostra lista ordinata. Con lo stesso successo è stato possibile creare una colonna aggiuntiva, riempirla manualmente con la sequenza numerica 1,2,3 … e farvi riferimento al posto della funzione RIGA.
Metodo 2. Elenco di testi e formule regolari
Se l'elenco non contiene numeri, ma testo, la funzione SMALL non funzionerà più, quindi devi seguire un percorso diverso, leggermente più lungo.
Innanzitutto, aggiungiamo una colonna di servizio con una formula in cui verrà calcolato il numero di serie di ciascun nome nella futura lista ordinata utilizzando la funzione COUNTIF (CONTA SE):
Nella versione inglese sarà:
=CONTA.SE(A:A,»<"&A1)+CONTA.SE($A$1:A1,"="&A1)
Il primo termine è una funzione per contare il numero di celle inferiore a quella corrente. La seconda è una rete di sicurezza nel caso in cui un nome si presenti più di una volta. Quindi non avranno gli stessi numeri, ma successivamente crescenti.
Ora i numeri ricevuti devono essere disposti in sequenza in ordine crescente. Per questo puoi usare la funzione MENO (PICCOLO) dal primo modo:
Bene, infine, non resta che estrarre i nomi dall'elenco in base ai loro numeri. Per fare ciò, puoi utilizzare la seguente formula:
Funzione PIÙ ESPOSTO (INCONTRO) ricerca nella colonna B il numero seriale desiderato (1, 2, 3, ecc.) e, infatti, restituisce il numero della riga in cui si trova tale numero. Funzione INDICE (INDICE) estrae dalla colonna A il nome in questo numero di riga.
Metodo 3: formula di matrice
Questo metodo è, in effetti, lo stesso algoritmo di posizionamento del Metodo 2, ma implementato da una formula di matrice. Per semplificare la formula, è stato assegnato il nome all'intervallo di celle C1:C10 Lista (selezionare le celle, premere Ctrl + F3 e pulsante Creare):
Nella cella E1, copia la nostra formula:
=INDICE(Lista; CONFRONTA(PICCOLO(CONTA.SE(Lista; “<"&Lista); RIGA(1:1)); CONTA.SE(Lista; "<"&Lista); 0))
Oppure nella versione inglese:
=INDICE(Lista, CONFRONTA(PICCOLO(CONTA.SE(Lista, «<"&Lista), RIGA(1:1)), CONTA.SE(Lista, "<"&Lista), 0))
e spingere CTRL + MAIUSC + INVIOper inserirlo come una formula di matrice. Quindi la formula risultante può essere copiata per l'intera lunghezza dell'elenco.
Se vuoi che la formula prenda in considerazione non un intervallo fisso, ma sia in grado di adeguarsi quando aggiungi nuovi elementi all'elenco, dovrai modificare leggermente la strategia.
Innanzitutto, l'intervallo Elenco dovrà essere impostato in modo dinamico. Per fare ciò, in fase di creazione, è necessario specificare non un intervallo fisso C3:C10, ma una formula speciale che farà riferimento a tutti i valori disponibili, indipendentemente dal loro numero. Clic Alt + F3 o apri la scheda Formule – Gestore dei nomi (Formule — Gestore nomi), crea un nuovo nome e nel campo Link (Riferimento) inserisci la seguente formula (presumo che l'intervallo di dati da ordinare inizi dalla cella C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=SCARTO(C1,0,0,SCHÖTZ(C1:C1000),1)
In secondo luogo, la formula dell'array di cui sopra dovrà essere allungata con un margine, con l'aspettativa di ulteriori dati inseriti in futuro. In questo caso, la formula della matrice inizierà a fornire un errore #NUMBER sulle celle che non sono ancora state riempite. Per intercettarlo, puoi utilizzare la funzione IFERROR, che deve essere aggiunto "intorno" alla nostra formula di matrice:
=SE ERRORE(INDICE(Lista; CONFRONTA(PICCOLO(CONTA.SE(Lista; “<"&Lista); RIGA(1:1)); CONTA.SE(Lista; "<"&Lista; 0));»»)
=SE.ERRORE(INDICE(Lista, CONFRONTA(PICCOLO(CONTA.SE(Lista, «<"&Lista), RIGA(1:1)), CONTA.SE(Lista, "<"&Lista), 0));"")
Cattura l'errore #NUMBER e restituisce invece un void (virgolette vuote).
:
- Ordina l'intervallo per colore
- Cosa sono le formule di matrice e perché sono necessarie
- ORDINA l'ordinamento e gli array dinamici nel nuovo Office 365