Analisi dei dati di Excel - Funzioni di ricerca

Puoi utilizzare le funzioni di Excel per:

  • Trova valori in un intervallo di dati: CERCA.VERT e CERCA.ORIZZ
  • Ottieni un valore o il riferimento a un valore da una tabella o un intervallo: INDICE
  • Ottieni la posizione relativa di un elemento specificato in un intervallo di celle: CONFRONTA

È inoltre possibile combinare queste funzioni per ottenere i risultati richiesti in base agli input di cui si dispone.

Utilizzo della funzione CERCA.VERT

La sintassi della funzione CERCA.VERT è

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Dove

  • lookup_value- è il valore che vuoi cercare. Lookup_value può essere un valore o un riferimento a una cella. Lookup_value deve essere nella prima colonna dell'intervallo di celle specificato in table_array

  • table_array- è l'intervallo di celle in cui CERCA.VERT cercherà lookup_value e il valore restituito. table_array deve contenere

    • lookup_value nella prima colonna e

    • il valore restituito che desideri trovare

      Note- La prima colonna contenente lookup_value può essere ordinata in ordine crescente o meno. Tuttavia, il risultato sarà basato sull'ordine di questa colonna.

  • col_index_num- è il numero di colonna in table_array che contiene il valore restituito. I numeri iniziano con 1 per la colonna più a sinistra dell'array di tabelle

  • range_lookup- è un valore logico facoltativo che specifica se si desidera che CERCA.VERT trovi una corrispondenza esatta o approssimativa. range_lookup può essere

    • omesso, nel qual caso si presume che sia VERO e CERCA.VERT tenta di trovare una corrispondenza approssimativa

    • VERO, nel qual caso CERCA.VERT tenta di trovare una corrispondenza approssimativa. In altre parole, se non viene trovata una corrispondenza esatta, viene restituito il valore successivo più grande inferiore a lookup_value

    • FALSO, nel qual caso CERCA.VERT tenta di trovare una corrispondenza esatta

    • 1, nel qual caso si presume che sia VERO e CERCA.VERT tenta di trovare una corrispondenza approssimativa

    • 0, nel qual caso si presume che sia FALSO e CERCA.VERT tenta di trovare una corrispondenza esatta

Note- Se range_lookup viene omesso o TRUE o 1, VLOOKUP funziona correttamente solo quando la prima colonna in table_array è ordinata in ordine crescente. In caso contrario, potrebbero verificarsi valori errati. In tal caso, utilizzare FALSE per range_lookup.

Utilizzo della funzione CERCA.VERT con range_lookup TRUE

Considera un elenco di voti degli studenti. È possibile ottenere i voti corrispondenti con CERCA.VERT da un array contenente gli intervalli dei voti e la categoria di passaggio.

table_array -

Notare che i primi segni di colonna in base ai quali si ottengono i voti sono ordinati in ordine crescente. Quindi, usando TRUE per l'argomento range_lookup puoi ottenere una corrispondenza approssimativa che è ciò che è richiesto.

Assegna a questo array il nome Grades.

È buona norma denominare gli array in questo modo in modo da non dover ricordare gli intervalli di celle. Ora sei pronto per cercare il voto per l'elenco dei voti che hai come segue:

Come puoi osservare,

  • col_index_num - indica che la colonna del valore restituito in table_array è 2

  • il range_lookup è vero

    • La prima colonna contenente il valore di ricerca in table_array grades è in ordine crescente. Quindi, i risultati saranno corretti.

    • È possibile ottenere il valore restituito anche per le corrispondenze approssimative. cioè CERCA.VERT calcola come segue:

Marks Categoria di passaggio
<35 Fallire
> = 35 e <50 Terza classe
> = 50 e <60 Seconda classe
> = 60 e <75 Prima classe
> = 75 Prima classe con distinzione

Otterrai i seguenti risultati:

Utilizzo della funzione CERCA.VERT con range_lookup FALSE

Considera un elenco di prodotti contenente l'ID prodotto e il prezzo per ciascuno dei prodotti. L'ID del prodotto e il prezzo verranno aggiunti alla fine dell'elenco ogni volta che viene lanciato un nuovo prodotto. Ciò significherebbe che gli ID prodotto non devono essere in ordine crescente. L'elenco dei prodotti potrebbe essere come mostrato di seguito -

table_array -

Assegna a questo array il nome ProductInfo.

È possibile ottenere il prezzo di un prodotto dato l'ID del prodotto con la funzione CERCA.VERT poiché l'ID del prodotto si trova nella prima colonna. Il prezzo è nella colonna 3 e quindi col_index_ num dovrebbe essere 3.

  • Usa la funzione CERCA.VERT con range_lookup come TRUE
  • Usa la funzione CERCA.VERT con range_lookup come FALSE

La risposta corretta è dall'array ProductInfo è 171,65. Puoi controllare i risultati.

Osservi che hai -

  • Il risultato corretto quando range_lookup è FALSE e
  • Un risultato sbagliato quando range_lookup è TRUE.

Questo perché la prima colonna nella matrice ProductInfo non è ordinata in ordine crescente. Quindi, ricorda di usare FALSE ogni volta che i dati non sono ordinati.

Utilizzo della funzione CERCA ORIZZ

Puoi usare HLOOKUP funzione se i dati sono in righe anziché in colonne.

Esempio

Prendiamo l'esempio delle informazioni sul prodotto. Supponiamo che l'array sia il seguente:

  • Assegna un nome a questo Array ProductRange. È possibile trovare il prezzo di un prodotto dato l'ID prodotto con la funzione CERCA.ORIZZ.

La sintassi della funzione CERCA.ORIZZ. È

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Dove

  • lookup_value - è il valore che si trova nella prima riga della tabella

  • table_array - è una tabella di informazioni in cui vengono cercati i dati

  • row_index_num - è il numero di riga in table_array da cui verrà restituito il valore corrispondente

  • range_lookup - è un valore logico che specifica se si desidera che CERCA.ORIZZ trovi una corrispondenza esatta o approssimativa

  • range_lookup può essere

    • omesso, nel qual caso si presume che sia TRUE e HLOOKUP cerca di trovare una corrispondenza approssimativa

    • VERO, nel qual caso CERCA.ORIZZ cerca di trovare una corrispondenza approssimativa. In altre parole, se non viene trovata una corrispondenza esatta, viene restituito il valore successivo più grande inferiore a lookup_value

    • FALSE, nel qual caso CERCA.VERT tenta di trovare una corrispondenza esatta

    • 1, nel qual caso si presume che sia VERO e CERCA.ORIZZ cerca di trovare una corrispondenza approssimativa

    • 0, nel qual caso si presume che sia FALSE e CERCA.VERT tenta di trovare una corrispondenza esatta

Note- Se range_lookup è Omissis, TRUE o 1, HLOOKUP funziona correttamente solo quando la prima colonna in table_array è ordinata in ordine crescente. In caso contrario, potrebbero verificarsi valori errati. In tal caso, utilizzare FALSE per range_lookup.

Utilizzo della funzione CERCA.ORIZZ con range_lookup FALSE

È possibile ottenere il prezzo di un prodotto dato l'ID prodotto con la funzione CERCA.ORIZZ, poiché l'ID prodotto si trova nella prima riga. Il prezzo è nella riga 3 e quindi row_index_ num dovrebbe essere 3.

  • Usa la funzione HLOOKUP con range_lookup come TRUE.
  • Usa la funzione HLOOKUP con range_lookup come FALSE.

La risposta corretta dall'array ProductRange è 171,65. Puoi controllare i risultati.

Osservi che come nel caso di CERCA.VERT, hai

  • Il risultato corretto quando range_lookup è FALSE e

  • Un risultato sbagliato quando range_lookup è TRUE.

Questo perché la prima riga nell'array ProductRange non è ordinata in ordine crescente. Quindi, ricorda di usare FALSE ogni volta che i dati non sono ordinati.

Utilizzo della funzione HLOOKUP con range_lookup TRUE

Considera l'esempio dei voti degli studenti utilizzati in CERCA.VERT. Supponiamo di avere i dati in righe invece che in colonne come mostrato nella tabella riportata di seguito -

table_array -

Assegna a questo array il nome GradesRange.

Notare che i segni della prima riga in base ai quali si ottengono i voti sono ordinati in ordine crescente. Quindi, usando HLOOKUP con TRUE per l'argomento range_lookup, puoi ottenere i voti con corrispondenza approssimativa e questo è ciò che è richiesto.

Come puoi osservare,

  • row_index_num - indica che la colonna del valore restituito in table_array è 2

  • il range_lookup è vero

    • La prima colonna contenente il valore di ricerca in table_array Grades è in ordine crescente. Quindi, i risultati saranno corretti.

    • È possibile ottenere il valore restituito anche per le corrispondenze approssimative. ovvero CERCA.ORIZZ calcola come segue:

Marks <35 > = 35 e <50 > = 50 e <60 > = 60 e <75 > = 75
Categoria di passaggio Fallire Terza classe Seconda classe Prima classe Prima classe con distinzione

Otterrai i seguenti risultati:

Utilizzo della funzione INDICE

Quando si dispone di una matrice di dati, è possibile recuperare un valore nella matrice specificando il numero di riga e il numero di colonna di quel valore nella matrice.

Considera i seguenti dati di vendita, in cui trovi le vendite in ciascuna delle regioni del nord, del sud, dell'est e dell'ovest dai venditori elencati.

  • Assegna alla matrice il nome SalesData.

Usando la funzione INDICE, puoi trovare -

  • Le vendite di uno qualsiasi dei venditori in una determinata regione.
  • Vendite totali in una regione da parte di tutti i commerciali.
  • Vendite totali da parte di un venditore in tutte le regioni.

Otterrai i seguenti risultati:

Supponi di non conoscere i numeri di riga per i venditori e i numeri di colonna per le regioni. Quindi, è necessario trovare il numero di riga e il numero di colonna prima di recuperare il valore con la funzione di indice.

Puoi farlo con la funzione CONFRONTA come spiegato nella sezione successiva.

Utilizzo della funzione MATCH

Se hai bisogno della posizione di un articolo in un intervallo, puoi utilizzare la funzione CONFRONTA. È possibile combinare le funzioni CORRISPONDENZA e INDICE come segue:

Otterrai i seguenti risultati: