Ordinare elenco con duplicati Excel

Ordinare un elenco con duplicati su Microsoft Excel è possibile in diversi modi. In particolare, questa procedura può essere utile per mettere in ordine gli articoli più venduti con nome e relativo ammontare, i migliori alunni, i prodotti più costosi o quelli sui quali si fa il maggiore margine.

Se vuoi, puoi scaricare il file che riassume l'articolo con i diversi metodi proposti cliccando sul pulsante.

In questo articolo consideriamo il caso con valori duplicati che è leggermente più complesso ma puoi consultare questo articolo se l'elenco che stai utilizzando ha unicamente valori univoci. Nello stesso articolo è spiegato come ricavare l’ordine numerico utilizzando le funzioni INDICE, CONFRONTA, GRANDE e RIF.RIGA.

I problemi nascono quando utilizziamo dei duplicati dato che la combinazione INDICE CONFRONTA purtroppo restituisce sempre e solo il primo riferimento. In questo caso, Anthony è più in alto rispetto a Markkanen e quindi viene ripetuto due volte anzichè darci la classifica corretta. Questo è un problema fastidioso che si ripete spesso quando si lavora con prodotti simili, pensiamo ad esempio a prodotti venduti assieme con lo stesso costo e nelle stesse quantità. In questo caso, entrambi hanno lo stesso numero di punti (332,25).

Bisogna stare molto attenti dato che la procedura di prima ordinare i punti per poi trovare il giocatore può creare dei problemi - per fortuna, con le ultime versioni di 365 il procedimento sarà quasi immediato e senza possibilità di errore.

Ordinare elenco con duplicati Excel: soluzione con Excel 365

Le nuove funzioni disponibili con l'arrivo dei dynamic array (in questo caso utilizzeremo DATI.ORDINA, DATI.ORDINA.PER e FILTRO) hanno semplificato in maniera evidente le formule necessarie per ordinare dei dati con dei valori duplicati.

Assumiamo che la tabella di partenza si chiami TBL e presenti queste tre colonne:

• Giocatore + squadra
• Divisione
• Punti

Se volessimo ordinare i giocatori per numeri di punti ci basterebbe utilizzare questa formula:

=DATI.ORDINA(TBL;3;-1)

La formula prende tutto il riferimento della tabella, dato che TBL comprende le tre colonne. Ordina poi il risultato per la terza colonna, quella dei punti (3) e li inserisce in ordine decrescente (-1).

Un'alternativa è rappresentata da un'altra formula, leggermente diversa:

=DATI.ORDINA.PER(TBL;TBL[PUNTI];-1)

In questo caso la logica è molto simile. Prendiamo l'intera tabella TBL per poi ordinare per la terza colonna (TBL_PUNTI) in ordine decrescente (-1).

Volessimo ordinare soltanto una sezione della tabella, ad esempio restituendo unicamente i giocatori di una certa divisione che è inserita nella cella U2, potremmo fare così:

=DATI.ORDINA(FILTRO(TBL;TBL[DIV]=U2;"");3;-1)

In questi casi aiuta guardare prima alla parte con FILTRO:

=FILTRO(TBL;TBL[DIV]=U2;"")

Stiamo filtrando la tabella TBL di partenza per ottenere esclusivamente i valori per la colonna DIV che siano uguali alla cella U2 (EAST o WEST).

A questo punto l'array non fa altro che effettuare l'operazione vista in precedenza: prende i valori che corrispondono alla nostra condizione per poi metterli in ordine per la terza colonna (3, sempre i punti) in ordine decrescente (-1).

Chiaramente le condizioni potrebbero essere molto più complesse ma andrebbero sempre inserite dalla funzione FILTRO.

Ordinare elenco con duplicati Excel: soluzione con array

Se non abbiamo una delle ultime versioni di Excel possiamo comunque sistemare tutto con una mix più complesso, che richiede l'utilizzo degli array (pre-365). Gli array devono essere inseriti con CTRL + SHIFT + ENTER per funzionare, se non vedi le parentesi graffe significa che l'array non è stato inserito e quindi la formula non funzionerà. Spesso non vengono utilizzati dato che risultano complicati per collaboratori poco esperti che inseriscono la formula solo con ENTER e vedono solo degli errori anzichè i valori.

Formula generale:

INDICE([intervallo_valori_testuali];PICCOLO(SE([intervallo_valori_numerici]=[valore_numerico];RIF.RIGA([numero_di_valori]));CONTA.SE([intervallo_valori_numerici_decrescente_progressivo];[valore_numerico])))

Facciamo un po' di ordine.

INDICE considera i valori testuali del primo elenco, PICCOLO ci aiuta a orientarci sul foglio (partirà dall'alto) mentre SE considera l'intervallo dei valori numerici del primo elenco, il valore numerico è quello del nuovo elenco in ordine. RIF.RIGA considera i numeri da 1 a n, contando i possibili valori dell'elenco (in questo caso 6) mentre CONTA.SE considera progressivamente l'intervallo di valori in ordine fino al valore che ci interessa, aiutandoci poichè definisce se prendere il primo o il secondo valore testuale riferito allo stesso numero

Formula pratica:

{=INDICE($B$3:$B$8;PICCOLO(SE($C$3:$C$8=F12;RIF.RIGA($1:$6));CONTA.SE($F$12:F12;F12)))}

La formula deve essere trascinata dalla cella E12 in giù, in modo da considerare gli altri riferimenti riga (selezionando quindi il secondo valore più grande, il terzo e così via). Nel punto che prima dava due volte lo stesso nome (Anthony e nuovamente Anthony), vediamo come invece vi siano due valori distinti (Anthony e Markkanen). Vediamo che Anthony viene prima di Markannen solo perchè è il valore più in alto nella tabella di riferimento.

Ordinare elenco con duplicati Excel: INDICE e AGGREGA

Un po' tortuoso, ma efficace, è anche l'utilizzo della funzione AGGREGA. Questa funzione è presente da Excel 2010 e permette di creare numerose operazioni quali somme e ricerche di dati tramite una sintassi molto particolare. Per dettagli a riguardo, ecco la pagina ufficiale di Microsoft sulla funzione stessa. Il vantaggio è indubbiamente che questa formula funziona con ENTER, quindi non necessita di essere inserita come un array. Fra i due metodi, questo è quello preferibile.

Formula pratica:

INDICE($B$3:$B$8;AGGREGA(15;6;(RIF.RIGA($1:$6))/($C$3:$C$8=I12);CONTA.SE($I$12:I12;I12)))

Analizzando le funzioni utilizzate, INDICE è uguale a prima e considera l'elenco dei nomi. Con AGGREGA(15;6 stiamo segnalando che stiamo cercando il valore più piccolo (come posizione) di un certo elenco. Abbiamo 6 valori, quindi inseriamo RIF.RIGA($1:$6). Potremmo inserire anche $200, non fa differenza. Questo è il riferimento che deve essere uguale almeno al numero di evidenze dell'INDICE che utilizziamo. Inserire però /($C$3:$C$8=I12) sta restringendo il campo dell'intervallo unicamente ai valori che sono pari al valore della cella I12, proprio per gestire correttamente i duplicati. CONTA.SE($I$12:I12;I12) è un modo per indicare la progressione dell'intervallo, permettendoci di evitare duplicati. La prima volta che la formula incontrerà 332,25 ci indicherà Anthony, la seconda Markkanen proprio come ci aspettiamo.

Ordinare elenco con duplicati Excel: condizioni multiple

Le cose si complicano quando consideriamo condizioni multiple. In questo caso dobbiamo anche modificare la procedura per trovare i valori più grandi in assoluto, dato che quella fatta in precedenza non è sufficiente. Dato che sono tutti giocatori NBA, facciamo un esempio con le due diverse conference: i giocatori sono stati divisi anche per WEST e EAST, come vediamo nella colonna L. Nella cella O10 abbiamo inserito EAST per spiegare a Excel che ci interessano unicamente i giocatori di quella conference. I giocatori WEST devono essere ignorati per la nostra classifica.

Formula pratica:

{=SE.ERRORE(GRANDE(SE($L$3:$L$8=$O$10;$M$3:$M$8);RIF.RIGA(A1));"")}

Anche in questo caso è consigliabile inserire la formula con CTRL+SHIFT+ENTER per far apparire le parentesi graffe. Sulle ultime versioni di Excel (ad esempio 365) non dovrebbe essere fondamentale e la formula funzionerà comunque, ma per questioni di compatibilità suggerisco di inserire comunque la formula con CTRL+SHIFT+ENTER (o CSE).

In questo caso stiamo utilizzando SE.ERRORE per restituire un valore vuoto se ci sono errori nell'elenco, mentre la funzione GRANDE ha la condizione di dover considerare unicamente i valori dell'intervallo sulla colonna L unicamente SE nella colonna O, sulla stessa riga, troviamo O10 (cioè EAST). RIF.RIGA viene utilizzato per metterli in ordine dal più grande al più piccolo.

Ora è il momento di recuperare i dati testuali che ci interessano. Pure in questo caso dovremo modificare la formula con INDICE e AGGREGA per aggiungere una ulteriore condizione.

Formula pratica:

=SE.ERRORE(INDICE($K$3:$K$8;
AGGREGA(15;6;
(RIF.RIGA($1:$6))/
(($M$3:$M$8=M12)*($L$3:$L$8=$O$10));
CONTA.SE($M$12:M12;M12)));"")

In questo caso, la parte focale è quella dopo il RIF.RIGA. Come vediamo, non solo la formula deve tenere conto della condizione ($M$3:$M$8=M12) che rappresenta i numeri (essendo M12 pari a 400,75), ma anche della condizione ($L$3:$L$8=$O$10) che segnala l'appartenenza dei singoli giocatori nella EAST Conference. La formula è corretta dato che restituisce Markkanen anzichè Anthony, benchè entrambi i giocatori abbiano lo stesso punteggio - infatti Markkanen è nella EAST Conference, a differenza di Anthony che è nella WEST.

Solo un dettaglio finale: per inserire la conference, è sufficiente un semplice INDICE CONFRONTA o CERCA.VERT.