Suggerimento
Provare a usare le nuove funzioni CERCA.X e CONFRONTA.X , versioni migliorate delle funzioni descritte in questo articolo. Queste nuove funzioni funzionano in qualsiasi direzione e restituiscono corrispondenze esatte per impostazione predefinita, rendendole più facili e comode da usare rispetto ai loro predecessori.
Supponiamo di avere un elenco di numeri di sede dell'ufficio e di dover sapere quali dipendenti sono presenti in ogni ufficio. Il foglio di calcolo è enorme, quindi potresti pensare che sia un compito impegnativo. In realtà è abbastanza facile da eseguire con una funzione di ricerca.
Le funzioni CERCA.VERT e CERCA.ORIZZ, insieme alle funzioni INDICE e CONFRONTA sono tra le più utili di Excel.
Nota
La funzionalità Ricerca guidata non è più disponibile in Excel.
Ecco un esempio di come usare CERCA.VERT.
=CERCA.VERT(B2;C2:E7;3;VERO)
In questo esempio B2 è il primo argomento, ovvero un elemento di dati necessario alla funzione per funzionare. Per CERCA.VERT, questo primo argomento rappresenta il valore che si desidera trovare. Questo argomento può essere un riferimento di cella oppure un valore fisso, ad esempio "rossa", o 21.000. Il secondo argomento è l'intervallo di celle, C2-:E7, in cui cercare il valore da trovare. Il terzo argomento è la colonna nell'intervallo di celle che contiene il valore cercato.
Il quarto argomento è facoltativo. Immettere TRUE o FALSE. Se si immette VERO o non si immette alcun argomento, la funzione restituisce una corrispondenza approssimativa del valore specificato nel primo argomento. Se si immette FALSO, la funzione troverà la corrispondenza del valore fornito dal primo argomento. In altre parole, lasciare il quarto argomento vuoto o immettere VERO offre una maggiore flessibilità.
Questo esempio mostra questa funzione. Quando si immette un valore nella cella B2 (primo argomento), CERCA.VERT esegue la ricerca nelle celle nell'intervallo C2:E7 (secondo argomento) e restituisce la corrispondenza approssimativa più vicina dalla terza colonna dell'intervallo, colonna E (terzo argomento).
Il quarto argomento è vuoto, quindi la funzione restituisce una corrispondenza approssimativa. Se fosse stato specificato, sarebbe stato necessario immettere uno dei valori nella colonna C o D per ottenere un risultato.
Quando si ha dimestichezza con CERCA.VERT, la funzione CERCA.ORIZZ è altrettanto facile da usare. Immetti gli stessi argomenti, ma la ricerca viene eseguita per righe anziché per colonne.
Uso delle funzioni INDICE e CONFRONTA invece di CERCA.VERT
L'uso di CERCA.VERT presenta alcune limitazioni: la funzione CERCA.VERT può cercare un valore solo da sinistra a destra. Ciò significa che la colonna contenente il valore cercato deve sempre trovarsi a sinistra della colonna contenente il valore restituito. Se il foglio di calcolo non è creato in questo modo, non usare CERCA.VERT. Usare invece la combinazione delle funzioni INDICE e CONFRONTA.
Questo esempio mostra un elenco in cui il valore da cercare, Chicago, non è nella colonna all'estrema sinistra. Pertanto, non è possibile usare CERCA.VERT. Si userà invece la funzione CONFRONTA per trovare Chicago nell'intervallo B1:B11. Il nome della città viene trovato nella riga 4. La funzione INDICE usa quindi questo valore come argomento di ricerca e trova la popolazione di Chicago nella 4a colonna (colonna D). La formula usata è visualizzata nella cella A14.
Provalo
Se si vogliono sperimentare le funzioni di ricerca prima di provarle con i propri dati, ecco alcuni dati di esempio.
Esempio di CERCA.VERT al lavoro
Copiare i dati seguenti in un foglio di calcolo vuoto.
Suggerimento
Prima di incollare i dati in Excel, impostare la larghezza delle colonne comprese nell'intervallo da A a C su 250 pixel, quindi fare clic su Testo a capo (gruppo Allineamento della scheda Home).
| Densità | Viscosità | Temperatura |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Formula | Descrizione | Risultato |
| =CERCA.VERT(1;A2:C10;2) | Usando una corrispondenza approssimativa, cerca il valore 1 nella colonna A, trova il valore più grande che è minore o uguale a 1 nella colonna A, ovvero 0,946, e restituisce il valore della colonna B nella stessa riga. | 2,17 |
| =CERCA.VERT(1;A2:C10;3;VERO) | Usando una corrispondenza approssimativa, cerca il valore 1 nella colonna A, trova il valore più grande minore o uguale a 1 nella colonna A, ovvero 0,946, e restituisce il valore della colonna C nella stessa riga. | 100 |
| =CERCA.VERT(0;7;A2:C10;3;FALSO) | Usando una corrispondenza esatta, cerca il valore 0,7 nella colonna A. Poiché non esiste alcuna corrispondenza esatta nella colonna A, verrà restituito un errore. | #N/D |
| =CERCA.VERT(0,1;A2:C10;2;VERO) | Usando una corrispondenza approssimativa, cerca il valore 0,1 nella colonna A. Poiché 0,1 è minore del valore più piccolo nella colonna A, verrà restituito un errore. | #N/D |
| =CERCA.VERT(2,A2:C10,2,VERO) | Usando una corrispondenza approssimativa, cerca il valore 2 nella colonna A, trova il valore più grande minore o uguale a 2 nella colonna A, ovvero 1,29, e restituisce il valore della colonna B nella stessa riga. | 1,71 |
Esempio di CERCA.ORIZZ
Copiare tutte le celle della tabella sotto e incollarle nella cella A1 all'interno di un foglio di lavoro vuoto di Excel.
Suggerimento
Prima di incollare i dati in Excel, impostare la larghezza delle colonne comprese nell'intervallo da A a C su 250 pixel, quindi fare clic su Testo a capo (gruppo Allineamento della scheda Home).
| Assi | Cuscinetti | Bulloni |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Formula | Descrizione | Risultato |
| =CERCA.ORIZZ("Assi"; A1:C4; 2; VERO) | Cerca "Assi" nella riga 1 e restituisce il valore della riga 2 nella stessa colonna (colonna A). | 4 |
| =CERCA.ORIZZ("Cuscinetti"; A1:C4; 3; FALSO) | Cerca "Cuscinetti" nella riga 1 e restituisce il valore della riga 3 nella stessa colonna (colonna B). | 7 |
| =CERCA.ORIZZ("B"; A1:C4; 3; VERO) | Cerca "B" nella riga 1 e restituisce il valore della riga 3 nella stessa colonna. Dato che non viene trovata una corrispondenza esatta per "B", viene usato il valore più grande nella riga 1 minore di "B", ovvero "Assi" nella colonna A. | 5 |
| =CERCA.ORIZZ("Bulloni"; A1:C4; 4) | Cerca "Bulloni" nella riga 1 e restituisce il valore della riga 4 nella stessa colonna (colonna C). | 11 |
| =CERCA.ORIZZ(3; {1;2;3\"a";"b";"c"\"d";"e";"f"}; 2; VERO) | Cerca il numero 3 nella costante di matrice a tre righe e restituisce il valore della riga 2 nella stessa colonna (in questo caso la terza). La costante di matrice contiene tre righe di valori, ognuna separata da un punto e virgola (;). Dato che "c" si trova nella riga 2 e nella stessa colonna di 3, viene restituito "c". | c |
Esempi di INDICE e CONFRONTA
Quest'ultimo esempio utilizza le funzioni INDICE e CONFRONTA per restituire il primo numero di fattura e la data corrispondente per ognuna delle cinque città. Poiché la data viene restituita come numero, si utilizza la funzione TESTO per formattarla come data. La funzione INDICE usa in genere il risultato della funzione CONFRONTA come argomento. La combinazione delle funzioni INDICE e CONFRONTA viene usata due volte in ogni formula, la prima per restituire il numero di fattura, quindi per restituire la data.
Copiare tutte le celle della tabella sotto e incollarle nella cella A1 all'interno di un foglio di lavoro vuoto di Excel.
Suggerimento
Prima di incollare i dati in Excel, impostare la larghezza delle colonne comprese nell'intervallo da A a D su 250 pixel, quindi fare clic su Testo a capo (gruppo Allineamento della scheda Home).
| Fattura | Città | Data fattura | Prima fattura per città, con data |
|---|---|---|---|
| 3115 | Atlanta | 07.04.12 | ="Atlanta = "&INDICE($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Data fattura: " & TESTO(INDICE($A$2:$C$33,CONFRONTA("Atlanta",$B$2:$B$33,0),3),"d/m/yy") |
| 3137 | Atlanta | 09.04.12 | ="Austin = "&INDICE($A$2:$C$33,CONFRONTA("Austin",$B$2:$B$33,0),1)& ", Data fattura: " & TESTO(INDICE($A$2:$C$33,CONFRONTA("Austin",$B$2:$B$33,0),3),"d/m/yy") |
| 3154 | Atlanta | 11.04.12 | ="Dallas = "&INDICE($A$2:$C$33,CONFRONTA("Dallas",$B$2:$B$33,0),1)& ", Data fattura: " & TESTO(INDICE($A$2:$C$33,CONFRONTA("Dallas",$B$2:$B$33,0),3),"d/m/yy") |
| 3191 | Atlanta | 21.04.12 | ="New Orleans = "&INDICE($A$2:$C$33,CONFRONTA("New Orleans",$B$2:$B$33,0),1)& ", Data fattura: " & TESTO(INDICE($A$2:$C$33,CONFRONTA("New Orleans",$B$2:$B$33,0),3),"d/m/yy") |
| 3293 | Atlanta | 25.04.12 | ="Tampa = "&INDICE($A$2:$C$33,CONFRONTA("Tampa",$B$2:$B$33,0),1)& ", Data fattura: " & TESTO(INDICE($A$2:$C$33,CONFRONTA("Tampa",$B$2:$B$33,0),3),"d/m/yy") |
| 3331 | Atlanta | 27.04.12 | |
| 3350 | Atlanta | 28.04.12 | |
| 3390 | Atlanta | 01.05.12 | |
| 3441 | Atlanta | 02.05.12 | |
| 3517 | Atlanta | 08.05.12 | |
| 3124 | Austin | 09.04.12 | |
| 3155 | Austin | 11.04.12 | |
| 3177 | Austin | 19.04.12 | |
| 3357 | Austin | 28.04.12 | |
| 3492 | Austin | 06.05.12 | |
| 3316 | Dallas | 25.04.12 | |
| 3346 | Dallas | 28.04.12 | |
| 3372 | Dallas | 01.05.12 | |
| 3414 | Dallas | 01.05.12 | |
| 3451 | Dallas | 02.05.12 | |
| 3467 | Dallas | 02.05.12 | |
| 3474 | Dallas | 04.05.12 | |
| 3490 | Dallas | 05.05.12 | |
| 3503 | Dallas | 08.05.12 | |
| 3151 | New Orleans | 09.04.12 | |
| 3438 | New Orleans | 02.05.12 | |
| 3471 | New Orleans | 04.05.12 | |
| 3160 | Tampa | 18.04.12 | |
| 3328 | Tampa | 26.04.12 | |
| 3368 | Tampa | 29.04.12 | |
| 3420 | Tampa | 01.05.12 | |
| 3501 | Tampa | 06.05.12 |
Vedere anche
Scheda di riferimento rapido: promemoria su CERCA.VERT
Funzioni di ricerca e di riferimento (riferimento)
Usare l'argomento matrice_tabella in una funzione CERCA.VERT