Come cercare più valori in Excel: passo dopo passo

Quando si lavora con set di dati di grandi dimensioni in Microsoft Excel, può essere difficile trovare più valori che corrispondono a diverse condizioni. Alcune funzioni integrate, ad esempio CERCA.VERT, erano originariamente progettate per funzionare con valori singoli.

Diverse funzioni di Excel possono essere combinate per cercare più valori. Includono le funzioni VLOOKUP, INDEX, MATCH e IF. Le versioni correnti di Excel possono utilizzare matrici dinamiche mentre le versioni precedenti utilizzano formule di matrice.

Questo articolo ti mostrerà esattamente come utilizzare queste funzioni nelle formule che trovano più valori nei tuoi dati.

Andiamo!

Sommario

• Come utilizzare CERCA.VERT con più valori
  • Cosa sono le formule di matrice?
  • Esempio di CERCA VERT
  • Ultime versioni di Excel
  • Versioni precedenti
  • Corrispondenza esatta contro corrispondenza approssimativa
  • Maggiori informazioni su CERCA.VERT
• Come utilizzare INDEX e MATCH per cercare più valori
  • Come utilizzare INDEX e MATCH insieme in Excel 365
  • Lavorare con versioni precedenti di Excel
• Come utilizzare la funzione SE per trovare più valori
  • Versioni correnti di Excel
  • Versioni precedenti di Excel
• Come utilizzare SUMPRODUCT per più criteri
• 3 errori comuni con le funzioni di ricerca
  • 1. #N/D Errori
  • 2. #RIF! Errori
  • 3. Errori circolari
• Come utilizzare IFERROR con le funzioni di ricerca
• 7 tecniche di ricerca avanzate
  • 1. Ricerca della posizione relativa
  • 2. Funzione PICCOLA
  • 3. Funzione RIGA
  • 4. Funzione FILTRO
  • 5. Colonne ausiliarie
  • 6. Array dinamici
  • 7. Domanda di potere
• Pensieri finali

Come utilizzare CERCA.VERT con più valori

La funzione VLOOKUP viene spesso utilizzata per trovare singoli valori in un intervallo di dati. Tuttavia, puoi anche cercare più corrispondenze in Excel con questa formula di ricerca.

Per impostazione predefinita, restituirà solo il primo valore corrispondente che trova. È tuttavia possibile modificare la funzione in modo che restituisca più valori utilizzando una formula di matrice.

Cosa sono le formule di matrice?

Una formula di matrice è una formula che può eseguire calcoli su matrici di dati. Si chiama formula di matrice perché può restituire una matrice di risultati, piuttosto che un singolo valore.

Esistono diversi semplici passaggi durante la creazione di una formula di matrice:

1. Seleziona un intervallo di celle in cui cercare.

2. Immettere la formula nella barra della formula.

3. Premi Ctrl + Maiusc + Invio per completarlo.

La sintassi di una formula di matrice è simile a quella di una formula normale, ma include parentesi graffe {} intorno alla formula. Le parentesi graffe indicano che la formula è una formula di matrice e che restituirà una matrice di valori.

Gli esempi in questo articolo ti mostreranno come utilizzare correttamente le formule di matrice.

Esempio di CERCA VERT

Il nostro esempio ha cinque elementi nella colonna A di un foglio di lavoro:

• Mela

• Banana

• Pane

• Carota

• Ciliegia

Il compito è verificare se in questo elenco sono presenti tre frutti specifici: mela, banana e ciliegia.

Ultime versioni di Excel

La sintassi VLOOKUP dipende dalla versione di Excel in uso.

Le versioni più recenti ( Excel 365 o Excel 2021 ) supportano gli array dinamici. Questa funzione consente alle formule di restituire più risultati che "si riversano" in celle adiacenti.

Questa è la sintassi (il segno uguale inizia la formula):

=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])

• lookup_value: il valore che vuoi cercare.

• table_array: l'intera tabella di dati che vuoi cercare.

• col_index_num: il numero della colonna della tabella in table_array che contiene i dati che si desidera restituire.

• range_lookup: facoltativo. Specifica se si desidera una corrispondenza esatta o approssimativa.

Il nostro esempio specifico utilizza questa formula:

=CERCA.VERT({“mela”,”banana”,”ciliegia”},A1:A5,1,FALSO)

Quando la formula viene inserita nella cella B1, i risultati si riversano nelle celle C1 e D1. Questa immagine mostra l'esempio in azione:

Versioni precedenti

Se utilizzi una versione precedente di Excel che non supporta gli array dinamici (ad es. Excel 2019 o versioni precedenti), dovrai utilizzare un approccio leggermente diverso con una formula di matrice.

Segui questi passi:

1. Fare clic sulla cella in cui si desidera visualizzare i risultati per il primo elemento (ad esempio, colonna B).

2. Digita la seguente formula senza premere ancora Invio: =CERCA.VERT({“mela”,”banana”,”ciliegia”}, A1:A5, 1, FALSO)

3. Premi Ctrl + Maiusc + Invio per trasformarlo in una formula di matrice.

4. Copia la cella B1 e incollala nella cella sottostante (o usa i quadratini di riempimento).

Quando usi Ctrl + Maiusc + Invio, Excel aggiunge parentesi graffe attorno alla formula. Ciò indica che si tratta di una formula di matrice.

Corrispondenza esatta contro corrispondenza approssimativa

Per impostazione predefinita, la funzione VLOOKUP utilizza una corrispondenza approssimativa. Ciò significa che restituirà la corrispondenza più vicina che riesce a trovare, anche se i valori della cella non corrispondono esattamente.

Se vuoi eseguire una corrispondenza esatta, puoi impostare l'argomento range_lookup su FALSE.

Tieni presente che le corrispondenze approssimative funzionano meglio con valori numerici ordinati. Di solito non è appropriato quando il valore della cella è testo.

Maggiori informazioni su CERCA.VERT

Se vuoi saperne di più su questa versatile funzione, dai un'occhiata a questi articoli:

• come confrontare due colonne in Excel con VLOOKUP

• come trovare discrepanze con VLOOKUP

Ora che sei impostato con la funzione VLOOKUP, diamo un'occhiata ad altre due funzioni che possono fare ciò che fa in un modo diverso: INDICE e CONFRONTA.

Come utilizzare INDEX e MATCH per cercare più valori

È possibile combinare insieme le funzioni INDICE e CONFRONTA per trovare più valori in più righe.

La funzione INDICE in Excel restituisce un valore o un riferimento a una cella all'interno di un intervallo specificato.

=INDICE(matrice, numero_riga, [num_colonna])

• matrice: l'intervallo di celle in cui cercare il valore.

• row_num: il numero di riga all'interno dell'array da cui restituire un valore.

• column_num: (facoltativo) il numero di colonna all'interno dell'array da cui restituire un valore. Se omesso, la funzione restituisce l'intera riga.

La funzione CONFRONTA in Excel restituisce la posizione di un valore all'interno di un intervallo specificato.

=CONFRONTA(valore_ricerca, matrice_ricerca, [tipo_corrispondenza])

• lookup_value: il valore da cercare all'interno di lookup_array.

• lookup_array: l'intervallo di celle in cui cercare il valore_lookup.

• match_type: (facoltativo) il tipo di corrispondenza da eseguire. Se omesso, la funzione esegue una corrispondenza esatta.

Come utilizzare INDEX e MATCH insieme in Excel 365

Per utilizzare INDICE e CONFRONTA insieme per cercare più valori in Excel, è necessario utilizzare una formula di matrice.

Lavorando con i dati di esempio precedenti, questa è la formula in Excel 365:

=INDICE(A1:A5, CONFRONTA({“mela”,”banana”,”ciliegia”}, A1:A5, 0))

L'esempio precedente si scompone in questo modo:

• INDICE: restituisce il valore di una cella in un intervallo specificato in base a un determinato numero di riga e colonna. In questo caso, restituirà il valore dall'intervallo A1:A5.

• A1:A5: questo è l'intervallo di tabelle definito in cui stai cercando il valore e da cui verrà restituito il risultato.

• CONFRONTA: cerca un elemento specificato in un intervallo di celle e restituisce la posizione relativa di quell'elemento nell'intervallo.

• {“mela”,”banana”,”ciliegia”}: questa è la costante di matrice contenente i valori che vuoi cercare.

• A1:A5: questo è l'intervallo in cui MATCH cercherà i valori dalla costante dell'array.

• 0: questo è il tipo di corrispondenza per la funzione MATCH. In questo caso, è 0, il che significa che stai cercando una corrispondenza esatta invece di una corrispondenza ravvicinata.

 Questa immagine mostra la formula in azione:

Lavorare con versioni precedenti di Excel

Se utilizzi un file Excel precedente che non supporta gli array dinamici (ad esempio, Excel 2019 o versioni precedenti), dovrai utilizzare un approccio diverso.

Poiché le versioni precedenti non supportano la "riversazione" delle formule nelle celle adiacenti, sarà necessario suddividere l'utilizzo in tre formule separate.

Segui questi passi:

1. Fare clic sulla cella in cui si desidera il risultato per il primo elemento (ad esempio, cella B1)

2. Digita la formula seguente:

3. =INDICE(A1:A5; CONFRONTA(“mela”, A1:A5; 0))

4. Premere Invio per eseguire la formula.

5. Digita questa formula nella cella B2: =INDEX(A1:A5, MATCH("banana", A1:A5, 0))

6. Digita questa formula nella cella B3: =INDICE(A1:A5, MATCH("ciliegia", A1:A5, 0))

Questa immagine mostra il riferimento di cella B3:

Le funzioni INDICE e CONFRONTA non sono le uniche che consentono di trovare più valori. Nella sezione successiva, esaminiamo come utilizzare la funzione IF in alternativa.

Come utilizzare la funzione SE per trovare più valori

Un altro modo per cercare più valori di cella in base a determinati criteri consiste nell'utilizzare la funzione SE con altre funzioni.

La funzione IF consente di testare più condizioni e restituire risultati diversi a seconda dell'esito di tali test.

Ad esempio, supponiamo di avere una tabella di dati sulle vendite con colonne per Prodotto e Vendite. Si desidera cercare e sommare l'importo delle vendite per due dei tre prodotti.

Versioni correnti di Excel

Per trovare la somma della colonna Vendite in cui il prodotto è "Mela" o "Banana" utilizzando la funzione SE, puoi utilizzare una formula di matrice con le funzioni SE, SOMMA e OR.

Supponendo che i tuoi dati inizino nella cella A1, usa la seguente formula:

=SOMMA(SE((A2:A4=”Mela”)+(A2:A4=”Banana”), B2:B4, 0))

La sezione (A2:A4=”Apple”)+(A2:A4=”Banana”) crea un array che ha valore 1 se la cella nell'intervallo A2:A4 contiene “Apple” o “Banana”, e 0 altrimenti .

L'istruzione IF controlla ogni elemento dell'argomento dell'array. Se il valore è 1 (ovvero, il prodotto è "Mela" o "Banana"), assume il valore corrispondente nella colonna Vendite (intervallo B2:B4); altrimenti, ci vuole 0.

La funzione SOMMA somma i valori della funzione IF, sommando in modo efficace i valori delle vendite sia per "Apple" che per "Banana".

Questa immagine mostra la formula in azione sull'intervallo di ricerca:

Versioni precedenti di Excel

In Excel 2019 o versioni precedenti, è necessario utilizzare una formula di matrice. Segui questi passi:

1. Digita la formula ma non premere Invio.

2. Premi Ctrl + Maiusc + Invio per renderlo una formula di matrice.

Excel aggiungerà parentesi graffe {} attorno alla formula, indicando che si tratta di una formula di matrice.

Successivamente, esaminiamo come utilizzare SUMPRODUCT per cercare diversi valori in base ai propri criteri. Andiamo!

Come utilizzare SUMPRODUCT per più criteri

La funzione SUMPRODUCT consente inoltre di cercare più valori in base a più criteri.

Poiché non richiede l'uso di una formula di matrice, la sintassi è la stessa indipendentemente dalla versione di Excel.

Utilizzando gli stessi dati dell'esempio precedente, la formula è simile a questa:

=PRODOTTOSOMMA((A2:A4=”Mela”)+(A2:A4=”Banana”), B2:B4)

La sezione (A2:A4=”Apple”)+(A2:A4=”Banana”) crea un array che ha valore 1 se la cella nell'intervallo A2:A4 contiene “Apple” o “Banana”, e 0 altrimenti .

La funzione SUMPRODUCT moltiplica gli elementi della matrice con gli elementi corrispondenti nella colonna Sales (intervallo B2:B4). Quindi somma i valori risultanti, sommando efficacemente i valori di vendita sia per "Apple" che per "Banana".

La formula seguente lo mostra in azione:

Le funzioni di Excel sono sorprendenti quando funzionano come previsto, ma a volte potresti incorrere in errori. Nella sezione successiva, trattiamo alcuni degli errori comuni e come puoi gestirli.

3 errori comuni con le funzioni di ricerca

Le funzioni di ricerca a volte possono restituire errori che possono essere frustranti e richiedere molto tempo per la risoluzione dei problemi. I tre errori più comuni che incontrerai sono:

1. #N/D Errori

2. #RIF! Errori

3. Errori circolari

1. #N/D Errori

L'errore #N/D si verifica quando non è possibile trovare il valore di ricerca nell'array di ricerca.

Esistono diversi motivi per cui può verificarsi questo errore, tra cui:

• il valore di ricerca è errato o errato.

• l'array di ricerca non è ordinato in ordine crescente.

• il valore di ricerca non è nel set di dati.

Quando il valore di ricerca non è nel set di dati, questa è un'informazione utile. Tuttavia, gli utenti inesperti di Excel potrebbero pensare che #N/D significhi che qualcosa è andato storto con la formula. La sezione successiva mostra come renderlo più user-friendly.

2. #RIF! Errori

Il #REF! l'errore si verifica quando l'array di ricerca o l'array restituito viene eliminato o spostato.

Questo errore può essere risolto aggiornando i riferimenti di cella nella funzione di ricerca.

3. Errori circolari

Quando combini funzioni in formule complesse, Excel potrebbe dirti che hai un riferimento circolare.

Troverai questi più facili da esaminare utilizzando la nostra guida per trovare riferimenti circolari in Excel .

Come utilizzare IFERROR con le funzioni di ricerca

La funzione IFERROR è uno strumento utile per gestire gli errori nelle funzioni di ricerca. Consente di specificare un valore o una formula da restituire se la funzione di ricerca restituisce un errore.

La sintassi per la funzione IFERROR è la seguente:

=SE.ERRORE(valore, valore_se_errore)

· valore: valore o formula che si desidera valutare.

· Valore_se_errore: il valore o la formula da restituire se il primo argomento restituisce un errore.

Ad esempio, supponiamo che tu abbia una funzione CERCA.VERT che cerca più valori in una tabella. Nell'immagine seguente, uno dei valori non esiste nell'intervallo di dati cercato.

Come puoi vedere, viene visualizzato l'errore #N/D, che può creare confusione per gli utenti di Excel inesperti.

Invece, puoi utilizzare IFERROR per visualizzare una cella vuota o un messaggio che dice "Non trovato" con questa sintassi:

=SE.ERRORE(CERCA.VERT(valore_ricerca, matrice_tabella, indice_colonna, FALSO), "Non trovato")

In questo esempio, se la funzione CERCA.VERT restituisce un errore, la funzione SE.ERRORE restituirà invece il messaggio "Non trovato".

Questa immagine mostra la formula in azione. La colonna B ha il valore mancante, mentre la colonna C e la colonna D hanno trovato corrispondenze.

Finora abbiamo coperto molto terreno e sei finalmente pronto per apprendere tecniche più avanzate per le ricerche, che è l'argomento della prossima sezione.

7 tecniche di ricerca avanzate

La ricerca di più valori in Excel può essere un'attività impegnativa, soprattutto quando si ha a che fare con set di dati di grandi dimensioni. Potresti riscontrare problemi di prestazioni con un'elaborazione lenta.

Esistono sette tecniche di ricerca avanzate che puoi utilizzare per rendere il processo più semplice ed efficiente.

• Ricerca della posizione relativa

• Funzione PICCOLA

• Funzione RIGA

• Funzione FILTRO

• Colonne ausiliarie

• Matrici dinamiche

• Domanda di potenza

1. Ricerca della posizione relativa

Uno dei modi più semplici per cercare più valori in Excel è utilizzare la ricerca della posizione relativa. Ciò comporta la specifica degli offset di riga e colonna da una data cella per individuare il valore desiderato.

Ad esempio, se si dispone di una tabella di dati e si desidera cercare un valore che si trova due righe in basso e tre colonne a destra di una determinata cella, è possibile utilizzare la seguente formula:

=SCARTO(cella, 2, 3)

2. Funzione PICCOLA

Un'altra tecnica utile per la ricerca di più valori è l'utilizzo della funzione PICCOLO. Questa funzione restituisce l'ennesimo valore più piccolo in un intervallo di celle.

Combinando questa funzione con altre funzioni di ricerca come INDICE e CONFRONTA, puoi cercare più valori in base a criteri specifici.

Ad esempio, la seguente formula cerca il secondo valore più piccolo in un intervallo di celle che soddisfano una determinata condizione:

=INDICE(dati, CONFRONTA(PICCOLO(SE(criteri, intervallo), 2), intervallo, 0))

3. Funzione RIGA

La funzione RIGA può essere utilizzata anche per ricerche di più valori in Excel. Questa funzione restituisce il numero di riga di una data cella, che può essere utilizzata per fare riferimento alle celle in una tabella di dati.

Ad esempio, la seguente formula cerca un valore in una tabella in base a un identificatore univoco:

=INDICE(dati, CONFRONTA(identificatore_univoco, dati[identificatore_univoco], 0), numero_indice_colonna)

4. Funzione FILTRO

La funzione FILTRO non è disponibile nelle versioni precedenti di Excel.

In Excel 365 è possibile utilizzarlo per filtrare un intervallo di celle in base a determinati criteri e restituire solo i valori che soddisfano tali criteri. Questa è la sintassi e tre argomenti:

=FILTRO(matrice, includi, [se_vuoto])

• array: i dati specifici che desideri filtrare.

• include: l'argomento include rappresenta i criteri o le condizioni che si desidera applicare all'array.

• [if_empty] (facoltativo): il valore da restituire se nessuna riga o colonna soddisfa i criteri specificati nell'argomento include.

Ad esempio, la formula seguente funziona sui dati di esempio per trovare corrispondenze per due dei tre elementi nella prima colonna e sommare i valori corrispondenti nella seconda colonna.

=SOMMA(FILTRO(B2:B4, (A2:A4=”Mela”)+(A2:A4=”Banana”)))

Questa immagine mostra quante righe sono abbinate e la somma dei diversi valori:

5. Colonne ausiliarie

Puoi utilizzare una colonna helper per unire più campi all'interno di una funzione come CERCA.VERT.

Supponi di lavorare con nomi e cognomi in un foglio separato. Puoi concatenarli in una colonna helper a cui fa riferimento la formula finale.

6. Array dinamici

Come si è appreso negli esempi precedenti, gli utenti di Microsoft 365 possono sfruttare gli array dinamici per la ricerca di più valori in Excel.

Gli array dinamici consentono di restituire più valori da una singola formula, semplificando la ricerca di grandi quantità di dati.

7. Domanda di potere

Power Query è un potente strumento in Excel che può essere utilizzato per restituire valori in base a più criteri.

Ad esempio, questo video trova dati disordinati in un foglio di calcolo e li ripulisce.

Pensieri finali

Questo conclude la nostra profonda immersione nell'arte di cercare più valori in Excel! Una volta che hai imparato le formule VLOOKUP, INDEX, MATCH e array, ti ritroverai a sfogliare set di dati complessi come un coltello caldo nel burro.

La chiave è capire la sintassi e la logica dietro ogni formula. Tieni presente che queste formule possono essere complesse, quindi è importante dedicare del tempo a testare accuratamente le formule prima di fare affidamento su di esse per importanti analisi dei dati.

Excel è uno strumento potente, ma è valido solo quanto l'utente. Quindi continua ad affinare queste abilità, continua a sperimentare e presto diventerai il maestro delle ricerche multivalore. Fino alla prossima volta, continua a sgranocchiare quei numeri e fai in modo che Excel faccia il duro lavoro per te!