Ordinare elenco con duplicati Excel

Usando Microsoft Excel, è possibile ordinare un elenco di numeri con relativi riferimenti. Questo 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.

In fondo all’articolo troverai un file Excel da scaricare che riassume l’articolo con i due diversi metodi proposti.

Ci sono varie possibilità che dipendono dai dati, in questo caso consideriamo il caso con i duplicati che è leggermente più complesso ma se l’elenco considerato non ha valori che si ripetono si può fare riferimento a questo articolo. Dallo stesso articolo è possibile ricavare come sia stato creato l’ordine numerico con le funzioni INDICE, CONFRONTA, GRANDE e RIF.RIGA.

Microsoft_Excel_Ordinare_Elenco_Duplicati_Uno

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

Metodo 1: 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 vedete 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.

INDICE($I$4:$I$11;PICCOLO(SE($J$4:$J$11=J15;RIF.RIGA($1:$8));CONTA.SE($J$15:J15;J15)))

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.

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

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:

– [intervallo_valori_testuali] = I4:I11

– [intervallo_valori_numerici] = J4:J11

– [valore_numerico] = J15 (primo valore dell’intervallo)

– [numero_di_valori] = 1:8 (da uno a otto)

– [intervallo_valori_numerici_decrescente_progressivo] = J15:J15

Formula pratica:

INDICE($I$4:$I$11;PICCOLO(SE($J$4:$J$11=J15;RIF.RIGA($1:$8));CONTA.SE($J$15:J15;J15)))

Microsoft_Excel_Ordinare_Elenco_Duplicati_Due

La formula deve essere trascinata dalla cella I15 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, vediamo come invece vi siano due valori distinti.

Microsoft_Excel_Ordinare_Elenco_Duplicati_Tre

Metodo 2: 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.

Microsoft_Excel_Ordinare_Elenco_Duplicati_Aggrega

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 8 valori, quindi inseriamo RIF.RIGA($1:$8). 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ò /($M$4:$M$11)=M15 sta definendo il campo dell’intervallo unicamente ai valori che sono pari al valore della cella M15.

CONTA.SE($M$15:M15;M15) è 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.

2 commenti su “Ordinare elenco con duplicati Excel

Lascia un commento

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