Classifica dei primi 10

Uso delle funzioni MAX e GRANDE abbinate a codice vba per la ricerca dei 10 valori più alti e relativi indici.

In quest'esempio, esaminiamo come poter analizzare una tabella dati dove figurano i nomi degli agenti di un'ipotetica ditta e i relativi punteggi in un periodo determinato di tempo, per ricavare la Classifica dei primi 10, nell'arco di tutto il periodo. Questo è un esempio che si presta per ottenere numerose varianti nella ricerca di una classifica con evidenzazione degli agenti e relativi punteggi. Le funzioni usate non sono difficili da capire: la funzione MAX cerca il maggior valore presente in un elenco di dati numerici, e lo riporta nella cella dove risiede la formula. La funzione GRANDE è simile alla precedente, ma offre la possibilità di reperire la graduatoria di un valore in un insieme di dati, variando il parametro di ricerca: vediamo la sintassi delle due funzioni:

  • MAX(num1;num2;...) es.: =MAX(A2:A6)  Riporta il numero più grande tra quelli indicati nel range

  • Se un argomento è costituito da una matrice o da un riferimento, verranno utilizzati soltanto i numeri presenti nella matrice o nel riferimento, mentre le celle vuote, i valori logici o il testo verranno ignorati. Se non si desidera che i valori logici e il testo vengano ignorati, utilizzare la funzione MAX.VALORI al posto di questa funzione

  • GRANDE(matrice;k)


    • Matrice è la matrice o l'intervallo di dati di cui si desidera determinare il k-esimo valore più grande.
    K è la posizione nella matrice o nell'intervallo di celle dei dati da restituire (partendo dal più grande).
    es.: =GRANDE(A2:B6;3)  darà il 3° numero più grande tra i numeri presenti nel range.  Se una matrice non contiene alcun dato, GRANDE restituirà il valore di errore #NUM!.Se una matrice contiene più valori uguali, verrà considerato comunque il valore che si trova nella k esima posizione tra i valori uguali. Es.. =GRANDE(A1:A10;7) ed i valori nel range sono 5,3,6,3,2,1,4,8,9,3 verrà considerato il secondo 3 (7° posizione a parità di valore).

Per le altre caratteristiche delle funzioni vi rimando alla guida in linea (da consultare).

Questa è l'immagine della tabella:

La cella A22 porta la funzione =MAX(B3:H19) e quindi il valore 1° classificato, le altre celle fino alla J22, sulla stessa riga, portano la funzione =GRANDE(B3:H19;2) fino a =GRANDE(B3:H19;10) che porterà il 10° classificato. La riga 23 porta solo i colori di identificazione classifica, con all'interno il codice del colore, che serve (ColorIndex) nelle istruzioni Vba per colorare la cella che ospita il valore relativo alla graduatoria. La riga 24 invece ospita il nome dell'agente che ha realizzato il relativo punteggio presente due righe sopra. Questo nome viene "pescato" dal codice, che dopo aver colorato la cella relativa alla classifica punteggio, si sposta a inizio stessa riga, copia il nome, e lo "scarica" nella rispettiva cella della riga 24. Il pulsante per attivare la routine ha una doppia funzione, serve per attivare la routine e per azzerare i valori; funziona come interruttore. Vediamo la routine:

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Mostra" Then
CommandButton1.Caption = "Azzera"
Application.ScreenUpdating = False 'evita il saltellamento sul foglio
'dichiarazioni delle variabili
Dim CL As Object
x = Range("A22").Value 'assegnazioni dei valori alle variabili (celle con funzioni)
y = Range("B22").Value
w = Range("C22").Value
z = Range("D22").Value
t = Range("E22").Value
u = Range("F22").Value
m = Range("G22").Value
p = Range("H22").Value
a = Range("I22").Value
j = Range("J22").Value

'per ogni cella (CL) nel range B3:H19, se il valore della cella è uguale alla variabile (x) indicata, selezioni la 'cella, la colori, ti sposti 'alla cella di inizio riga (colonna A), copi il valore (nome agente), selezioni la cella di 'destinazione (A24) e incolli il nom. Quindi la 'routine continua per le rimanenti variabili (ElseIf).

For Each CL In Range("B3:H19")
If CL.Value = x Then
CL.Select
CL.Interior.ColorIndex = 6
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("A24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With


ElseIf CL.Value = y Then
CL.Select
CL.Interior.ColorIndex = 40
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("B24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = w Then
CL.Select
CL.Interior.ColorIndex = 38
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("C24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = z Then
CL.Select
CL.Interior.ColorIndex = 43
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("D24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = t Then
CL.Select
CL.Interior.ColorIndex = 4
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("E24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = u Then
CL.Select
CL.Interior.ColorIndex = 35
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("F24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = m Then
CL.Select
CL.Interior.ColorIndex = 15
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("G24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = p Then
CL.Select
CL.Interior.ColorIndex = 34
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("H24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = a Then
CL.Select
CL.Interior.ColorIndex = 8
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("I24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

ElseIf CL.Value = j Then
CL.Select
CL.Interior.ColorIndex = 3
With ActiveCell
Cells(ActiveCell.Row, 1).Select
Selection.Copy
Range("J24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

End If
Next
Application.CutCopyMode = False
'fine ciclo ricerche

Else
'questa è l'istruzione per togliere i colori e pulire le celle riga 24, e rinomina la Caption del commandbutton per 'reinizzializzare una 'nuova ricerca
CommandButton1.Caption = "Mostra"
Range("B3:H19").Interior.ColorIndex = xlNone
Range("B24:J24").ClearContents
End If
End Sub