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.

Microsoft_Excel_Ordinare_Duplicati_Errore

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).

Ordinare elenco con duplicati Excel: soluzione con array

Possiamo comunque sistemare tutto con una mix più complesso, che richiede l’utilizzo degli array. 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.

Microsoft_Excel_Ordinare_Duplicati_Array

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

Microsoft_Excel_Ordinare_Duplicati_Indice_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

Microsoft_Excel_Ordinare_Duplicati_Grande_Array

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.

Microsoft_Excel_Ordinare_Duplicati_Condizioni_Multiple

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.

Microsoft_Excel_Ordinare_Duplicati_Indice_Confronta

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

9 commenti su “Ordinare elenco con duplicati Excel”

  1. Ciao, scusa la mia ignoranza: utilizzando l’array, quando seleziono J15:J22 per copiare la formula non mi si aggiornano i riferimenti delle celle. Non riesco a capire dove sbaglio.
    Grazie

    1. Ciao Paolo,

      prova inserendo dalla prima cella e trascinando in giù, comunque non funziona? Ricordati che il primo è bloccato con il $!

      A presto e buona giornata

      Marco

  2. CIAO MARCO IL TUO MODO DI SPIEGARE E’ ASSOLUTAMENTE EFFICACE. QUANDO LEGGO QUI SEMBRA TUTTO PIù CHIARO.
    VOLEVO EFFETTUARE UN ULTERIORE PASSAGGIO A PARTIRE DA QUESTA SOLUZIONE, E POPOLANDO UNA TABELLA CON TUTTE LE INFORMAZIONI NECESSARIE, ORDINARE QUESTI DATI ALTROVE SULLA BASE DI DUE O PIU’ CRITERI E NON SOLTANTO UNO.

    1. Ciao Christian,

      prima di tutto grazie per i complimenti e la fiducia!

      Spero di aver capito bene la tua domanda, che mi ha dato l’ispirazione per ampliare l’articolo – in fondo trovi un esempio nel quale ci siano ulteriori condizioni per ordinare i risultati finali aggiungendo una condizione di partenza.

      A presto

      Marco

  3. Grazieeee funziona!!! Complimenti per la spiegazione chiarissima!!!

    è la prima volta che copiando una funzione da internet e portandola sul mio file va tutto come dovrebbe XD

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *