Classifica automatica Stampa
Scritto da Maurizio 'Mao' Lettieri   
Giovedì 15 Ottobre 2009 13:40

 

Spesso capita di voler avere un elenco costantemente aggiornato secondo 1 o più variabili. Per ottenere questo è possibile sfruttare una macro oppure utilizzare delle formule per avere una sorta di classifica costantemente aggiornata. In questo articolo analizzeremo vari tipi di classifiche, da quelle più semplici ad un parametro con o senza eventuali doppioni fino a quelle che tengono in conto 2 o più parametri. (Link to Trix online)

Classifica Semplice

Foglio "Classifica1" nell'excel

Partiamo dal caso più semplice, in cui vogliamo avere una classifica aggiornata in base ad un parametro solo. Supponiamo di avere questo set di dati.

Img001.gif

Vogliamo ottenere un elenco ordinato secondo il numero di punti. Supponiamo che l'elenco dei nomi sia nelle celle A5:A8 mentre i punteggi nelle celle B5:B8. La formula da utilizzare per ottenere la classifica automatica è:

- Nome: F5=INDICE($A$5:$A$8;CONFRONTA(GRANDE($B$5:$B$8;RIF.RIGA(E5)-RIF.RIGA($E$4));$B$5:$B$8;0)) - formula matriciale da inserire con CTRL+MAIUS+Invio
- Punti: G5=CERCA.VERT(F5;$A$5:$B$8;2;0)
- Posizione: E5=RANGO(G5;$G$5:$G$8)

Trascinando la formula verso il basso per un numero di righe pari al numero di valori da ordinare, la classifica che otteniamo è questa

Img002.gif


Analizziamo ora i singoli step della formula
- RIF.RIGA(E5)-RIF.RIGA($E$4): questa parte della formula scorrerà l'elenco e assumerà valori da 1 a 4 (essendo 4 i valori da ordinare)
- GRANDE($B$5:$B$8;RIF.RIGA(E5)-RIF.RIGA($E$4)): grande restituisce l'n-esimo elemento di una serie per valore. Con il parametro 1, restituisce il massimo di una serie, con il parametro 2 il secondo più grande ecc
- CONFRONTA(...): confronta cerca all'interno della serie dei punti il numero ricavato con la formula GRANDE e restituisce la posizione nell'elenco
- INDICE(...): restituisce il valore n-esimo all'interno di una serie specificando la sua posizione.

Il problema di questa formula è il fatto di non riuscire a gestire eventuali valori doppi nell'elenco.
Se ad esempio utilizziamo un altro set di dati dove sono presenti 2 nomi con gli stessi punti otteniamo questo errore

Img003.gif

Come si vede da questo esempio, per ogni valore doppio viene riportato n-volte lo stesso valore (il primo partendo dall'alto). Per superare questo limite bisogna utilizzare una versione rivista di questa formula, come vedete nel successivo paragrafo.


Classifica con Doppioni

Foglio "Classifica2" nell'excel

In questo paragrafo, vogliamo analizzare come ordinare un elenco dove sono presenti nomi con lo stesso punteggio.
Per questo caso utilizziamo lo stesso set di dati che ci ha dato problemi con le formule precedenti.



Img004.gif

In questo caso le formule da utilizzare sono

- Nome: F5=INDICE($A$5:$A$13;CONFRONTA(GRANDE($B$5:$B$13+RIF.RIGA($B$5:$B$13)/100;RIF.RIGA(E5)-RIF.RIGA($E$4));$B$5:$B$13+RIF.RIGA($B$5:$B$13)/100;0)) - formula matriciale da inserire con CTRL+MAIUS+Invio
- Punti: G5=CERCA.VERT(F5;$A$4:$C$13;2;0)

L'unica differenza rispetto al caso precedente è il fatto di modificare leggermente i valori prima di utilizzare la funzione GRANDE.
Infatti aggiungendo una somma piccolissima differente per ogni valore ai punti, si ottiene un elenco di valori con lo stesso ordinamento ma senza doppioni. Uno dei tanti metodi per aggiungere un valore piccolissimo diverso per ogni valore è legare questo al riferimento della riga. In questo caso ad ogni punteggio viene sottratto il valore della riga diviso per 100. ($B$2:$B$10-RIF.RIGA($B$2:$B$10)/100). Il diviso 100 serve ad evitare che il valore sommato modifichi l'ordine. In questo caso essendo i punteggi dei valori interi, sommando dei valori minore a 1, l'ordinamento non cambia. Il valore 100 va quindi tarato a seconda del problema, tenendo presente che rif.riga(ultima cella)/k sia minore della minima differenza tra 2 valori dell'elenco.
Analogamente va modificato l'elenco dei valori in cui effettuare il CONFRONTA.
In questo caso l'ordinamento viene creato posizionando i doppioni in ordine inverso rispetto all'ordine iniziale. Infatti il valore che possiede un riferimento di riga maggiori vede il proprio punteggio incrementato di una quantità maggiore.
Nel caso si volesse rispettare l'ordine iniziale, basta aggiungere l'inverso della somma precedente ai vecchi punteggi.
=INDICE($A$22:$A$30;CONFRONTA(GRANDE($B$22:$B$30-RIF.RIGA($B$22:$B$30)/100;RIF.RIGA(E22)-RIF.RIGA($E$21));$B$22:$B$30-RIF.RIGA($B$22:$B$30)/100;0))

Utilizzando quest'ultima formula l'elenco ordinato che otteniamo è il seguente.

Img005.gif


Classifica con 2 Parametri

Foglio "Classifica3" nell'excel

Come ultimo caso, analizziamo come ordinare un elenco in base 2 parametri, dove il secondo serve per ordinare i campi che possidono il medesimo valore come primo parametro. Per questo esempio utilizziamo il seguente elenco.



Img006.gif

Per risolvere questo esempio dobbiamo solo variare lievemente la formula del caso precedente.
=INDICE($A$5:$A$13;CONFRONTA(GRANDE($B$5:$B$13+($C$5:$C$13)/100-RIF.RIGA($B$5:$B$13)/10000;RIF.RIGA(E5)-RIF.RIGA($E$4));$B$5:$B$13+($C$5:$C$13)/100-RIF.RIGA($B$5:$B$13)/10000;0))

L'unica differenza rispetto al caso precedente è il fatto di aggiungere ai punti il valore dei gol diviso 100. Così facendo, la discriminate dei gol permette di ordinare i nomi con parità di punti. Per sicurezza lasciamo inoltre il punto della formula in cui si aggiunge RIF.RIGA in modo date da discriminare eventuali nomi con stessi punti e gol.
Applicando questa formula otteniamo questo ordinamento


Classifica con Condizione

Foglio "Classifica4" nell'excel

Vediamo ora come creare una classifica che rispetta anche una condizione. Ad esempio vogliamo calcolare la classifica solo delle squadre che hanno fatto più di 3 gol, usando sempre la base dati precedente.

Per risolvere questo esempio dobbiamo solo variare lievemente la formula del caso precedente.

=SE(RIF.RIGA(E5)-RIF.RIGA($E$4)>CONTA.SE(C5:C13;">=3");"";(INDICE($A$5:$A$13;CONFRONTA(GRANDE (($B$5:$B$13)*($C$5:$C$13>3)+RIF.RIGA($B$5:$B$13)/10000;RIF.RIGA(E5)-RIF.RIGA($E$4));($B$5:$B$13)* ($C$5:$C$13>3) +RIF.RIGA($B$5:$B$13)/10000;0))))

Il primo SE serve per discriminare le righe dove comparirà un valore dalla righe dove deve essere lasciato vuoto perchè si è superato il numero di squadre che rispettano la condizione dei 3 gol.

La seconda variazione è l'argomento di GRANDE che viene modificato come segue ($B$5:$B$13)*($C$5:$C$13>3), in modo tale che se i gol sono meno di 3, il valore dei punti viene impostato a 0.

Anche le formule delle colonne punti e gol sono leggermente diverse per gestire le righe vuote

=SE(F5<>"";CERCA.VERT(F5;$A$4:$C$13;2;0);"")
=SE(F5<>"";CERCA.VERT(F5;$A$4:$C$13;3;0);"")
 

 

Allegati:
FileDescrizioneDimensione del File
Scarica questo file (Classifica.xls)Classifica.xlsEsempio45 Kb
Ultimo aggiornamento Venerdì 09 Agosto 2013 15:30