CERCA.VERT è una delle funzioni più utilizzate in Excel. Cerca un valore in un intervallo e restituisce un valore corrispondente in un numero di colonna specificato.
Ora mi sono imbattuto in un problema in cui dovevo cercare l'intera riga e restituire i valori in tutte le colonne da quella riga (invece di restituire un singolo valore).
Quindi ecco cosa dovevo fare. Nel set di dati seguente, avevo i nomi dei rappresentanti di vendita e le vendite che hanno realizzato in 4 trimestri nel 2012. Avevo un menu a discesa con i loro nomi e volevo estrarre le vendite massime per quel rappresentante di vendita in quei quattro trimestri.
Potrei trovare 2 modi diversi per farlo: usando INDICE o CERCA.VERT.
Cerca l'intera riga/colonna utilizzando la formula INDICE
Ecco la formula che ho creato per farlo usando Index
=GRANDE(INDICE($B$4:$F$13,CONFRONTA(H3,$B$4:$B$13,0),0),1)
Come funziona:
Diamo prima un'occhiata alla funzione INDEX che è racchiusa all'interno della funzione LARGE.
=INDICE($C$4:$F$13,CONFRONTA(H3,$B$4:$B$13,0),0)
Analizziamo da vicino gli argomenti della funzione INDICE:
- Matrice - $B$4:$F$1
- Numero riga - CONFRONTA(H3,$B$4:$B$13,0)
- Numero di colonna - 0
Nota che ho usato il numero di colonna come 0.
Il trucco qui è che quando usi il numero di colonna come 0, restituisce tutti i valori in tutte le colonne. Quindi, se seleziono John nel menu a discesa, la formula dell'indice restituirebbe tutti e 4 i valori di vendita per John {91064,71690,67754,25427}.
Ora posso usare la funzione Large per estrarre il valore più grande
Suggerimento avanzato: utilizza il numero di colonna/riga come 0 nella formula dell'indice per restituire tutti i valori in colonne/righe.
Cerca l'intera riga/colonna utilizzando la formula CERCA.VERT
Mentre la formula dell'indice è ordinata, pulita e robusta, il modo VLOOKUP è un po' complesso. Finisce anche per rendere volatile la funzione. Tuttavia, c'è un trucco straordinario che vorrei condividere in questa sezione. Ecco la formula:
=GRANDE(CERCA.VERT(H3,B4:F13, RIGA(INDIRETTO("2:"&CONTA.VALORI($B$4:$F$4))), FALSO),1)
Come funziona
- RIGA(INDIRECT("2:"&COUNTA($B$4:$F$4))) - Questa formula restituisce una matrice {2;3;4;5}. Si noti che poiché utilizza INDIRETTO, questo rende questa formula volatile.
- CERCA.VERT(H3,B4:F13,ROW(INDIRETTO(“2:”&COUNTA($B$4:$F$4))),FALSE) - Ecco la parte migliore. Quando li metti insieme, diventa VLOOKUP(H3,B4:F13,{2;3;4;5},FALSE). Ora nota che invece di un singolo numero di colonna, gli ho assegnato un array di numeri di colonna. E CERCA.VERT cerca obbedientemente i valori in tutte queste colonne e restituisce un array.
- Ora usa la funzione LARGE per estrarre il valore più grande.
Ricorda di usare Ctrl + Maiusc + Invio per utilizzare questa formula.
Suggerimento avanzato: in VLOOKUP, invece di utilizzare un singolo numero di colonna, se si utilizza una matrice di numeri di colonna, verrà restituita una matrice di valori di ricerca.
