Jim ha descritto una situazione in cui ha un elenco dei nomi dei dipendenti e dei loro stipendi. Vuole determinare chi sono i cinque dipendenti più pagati. Utilizza la funzione GRANDE per identificare i cinque stipendi maggiori e quindi tenta di utilizzare CERCA.VERT per restituire i nomi appartenenti a tali stipendi. Funziona bene a meno che non ci siano duplicati nei primi cinque stipendi (le persone vengono pagate con lo stesso stipendio). In caso affermativo, CERCA.VERT restituisce solo il nome del primo dipendente con quello stipendio.

Per restituire tutti i nomi propri, ci sono un paio di cose che potresti fare.

Un metodo potrebbe essere quello di bypassare completamente l’utilizzo di una formula. Invece, potresti usare la funzione Filtro automatico in Excel:

  1. Seleziona una cella qualsiasi nella tabella dei dati.

  2. Scegli dati | Filtro | Filtro automatico. Excel aggiunge frecce a discesa a destra di ogni intestazione di colonna nella tabella.

  3. Utilizzare l’elenco a discesa nella parte superiore della colonna degli stipendi per scegliere Primi 10. Excel visualizza la finestra di dialogo Primi 10 filtri automatici. (Vedi figura 1.)

  4. Regola il controllo centrale da 10 a 5.

  5. Fare clic su OK. Excel visualizza i primi cinque stipendi nell’elenco.

Quando segui questi passaggi, potresti effettivamente ritrovarti con più di cinque record visibili, in particolare se ci sono legami negli stipendi dei dipendenti. Il filtro identifica i primi cinque stipendi e quindi visualizza tutti i record con gli stipendi corrispondenti a quelli.

Se non si desidera utilizzare il filtro automatico, un’altra opzione è semplicemente assicurarsi che ci sia qualcosa di unico in ciascuno dei record nell’elenco dei dipendenti. Ad esempio, se i nomi dei dipendenti sono nella colonna B e gli stipendi sono nella colonna C, allora potresti usare la seguente formula nella colonna A per rendere unico ogni record:

=C2+ROW()/100000000

Questo aggiungerà il numero di riga diviso per 100.000.000 e creerà un valore univoco. Se hai (ad esempio) stipendi identici di 98.765,43 nelle righe 2 e 49 nella colonna A, saranno:

98765.43000002 98765.43000049

Il numero grande (100.000.000) è così che se avessi un numero identico nella riga 65536, otterrai:

98765.43065536

E anche in questo caso il valore arrotondato a 2 cifre decimali sarebbe ancora il numero reale. Se GRANDE e CERCA.VERT vengono eseguiti con i valori “non univoci” nella colonna A, restituirai gli stipendi più alti (e le persone associate), in base alla posizione della persona all’interno dell’elenco.

Un terzo approccio consiste nell’usare le funzioni RANK e COUNTIF per restituire una “classifica” univoca per ogni valore nell’elenco degli stipendi. Se gli stipendi sono nell’intervallo B1: B50, inserisci quanto segue nella cella C1 e copialo nell’intervallo:

=RANK(B1,$B$1:$B$50)+COUNTIF($B$1:B1,B1)-1

È ora possibile utilizzare INDICE sui valori della classifica per restituire il nome associato a ogni stipendio.

Infine, un quarto approccio consiste nel creare una macro che possa restituire le informazioni desiderate. Ci sono molti modi in cui una macro potrebbe essere implementata; il seguente è solo uno di loro:

Function VLIndex(vValue, rngAll As Range, _   iCol As Integer, lIndex As Long)

Dim x As Long     Dim lCount As Long     Dim vArray() As Variant     Dim rng As Range     On Error GoTo errhandler

Set rng = Intersect(rngAll, rngAll.Columns(1))

ReDim vArray(1 To rng.Rows.Count)

lCount = 0     For x = 1 To rng.Rows.Count         If rng.Cells(x).Value = vValue Then             lCount = lCount + 1             vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value         End If     Next x

ReDim Preserve vArray(1 To lCount)

If lCount = 0 Then         VLIndex = CVErr(xlErrNA)

ElseIf lIndex > lCount Then         VLIndex = CVErr(xlErrNum)

Else         VLIndex = vArray(lIndex)

End If errhandler:

If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)

End Function

I parametri passati a questa funzione definita dall’utente sono il valore, l’intervallo di celle in cui cercare, l ‘”offset” da questo intervallo per la ricerca (il numero di colonne a destra è positivo, a sinistra è negativo) e il numero del duplicato (1 è il primo valore, 2 il secondo e così via).

Per usarlo, ad esempio, supponi che A1: B1 contenga le intestazioni di colonna, A2: A100 contenga gli stipendi e B2: B100 contenga i nomi dei dipendenti.

Nella cella E2 puoi inserire quanto segue per determinare lo stipendio più alto nella tabella:

=LARGE($A$2:$A$100,ROW()-1)

Nella cella F2 puoi inserire la seguente formula per determinare se la riga ha dei duplicati e tenere traccia del “valore” corrente di quel duplicato:

=IF(E2=E1,1+F1,1)

Nella cella G2 è possibile utilizzare la seguente formula, che richiama la funzione definita dall’utente:

=VLIndex(E2,$A$2:$A$100,1,F2)

Copia le celle da E2: G2 a E3: G6 e avrai (nella colonna G) i nomi dei dipendenti con i cinque stipendi più alti.

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (3077) si applica a Microsoft Excel 97, 2000, 2002 e 2003.