4 funzioni di ricerca Excel per la ricerca efficiente di fogli di calcolo

La maggior parte delle volte, trovare una cella in un foglio di calcolo Excel è abbastanza semplice; se non puoi semplicemente scorrere le righe e le colonne, puoi usare CTRL + F per cercarlo. Ma se stai lavorando con un foglio di calcolo molto grande Come dividere un foglio di calcolo CSV Excel in file separati Come dividere un foglio di calcolo CSV Excel in file separati Uno dei difetti di Microsoft Excel è la dimensione limitata di un foglio di calcolo. Se hai bisogno di rendere il tuo file Excel più piccolo o dividere un file CSV di grandi dimensioni, continua a leggere! Leggi di più, può risparmiare molto tempo per utilizzare una di queste quattro funzioni di ricerca. Non importa la dimensione del tuo documento Microsoft Excel, saranno molto più efficienti.

La funzione CERCA.VERT

Questa funzione consente di specificare una colonna e un valore e restituirà un valore dalla riga corrispondente di una colonna diversa (se ciò non ha senso, diventerà chiaro in un momento). Due esempi in cui potresti fare ciò sono cercare il cognome di un dipendente in base al numero del dipendente o trovare un numero di telefono specificando un cognome. Ecco la sintassi della funzione:

= CERCA.VERT ([valore_valore], [matrice_tabella], [nome_index_num], [intervallo_intervallo])

[Lookup_value] è l'informazione che hai già; per esempio, se hai bisogno di sapere in che stato si trova una città, sarebbe il nome della città. [matrice_tabella] consente di specificare le celle in cui la funzione cercherà la ricerca e i valori restituiti. Quando selezioni il tuo intervallo, assicurati che la prima colonna inclusa nell'array sia quella che includerà il tuo valore di ricerca! Questo è cruciale. [col_index_num] è il numero della colonna che contiene il valore restituito.

[range_lookup] è un argomento facoltativo e prende 1 o 0. Se si immette 1 o si omette questo argomento, la funzione cerca il valore immesso o il numero più basso successivo. Quindi nell'immagine qui sotto, un CERCA.VERT alla ricerca di un punteggio SAT di 652 restituirà 646, poiché è il numero più vicino nell'elenco che è inferiore a 652 e [range_lookup] assume come valore predefinito 1.

Diamo un'occhiata a come potremmo usare questo. Come ho nei miei articoli su operatori booleani in Microsoft Excel Mini Tutorial: Usa la logica booleana per elaborare dati complessi Tutorial di Mini Excel: Usa logica booleana per elaborare dati complessi Operatori logici SE, NOT, AND e OR possono aiutarti a ottenere da Principiante di Excel per potenziare l'utente. Spieghiamo le basi di ogni funzione e dimostreremo come utilizzarle per ottenere i massimi risultati. Ulteriori informazioni e conteggio avanzato e aggiunta dell'esercitazione Mini Excel: Uso del conteggio avanzato e aggiunta di funzioni in Excel Tutorial di Mini Excel: Uso del conteggio avanzato e aggiunta di funzioni in Excel Il conteggio e l'aggiunta di formule possono apparire banali rispetto alle più avanzate formule di Excel. Ma possono aiutarti a risparmiare un sacco di tempo quando devi raccogliere informazioni sui dati nel tuo foglio di calcolo. Per saperne di più, userò un foglio di calcolo che ho generato con generateata.com. Contiene numeri ID, nomi e cognomi, città, stato e punteggi SAT. Diciamo che voglio trovare il punteggio SAT di una persona con il cognome “Winters.” VLOOKUP lo rende facile. Ecco la formula che useremmo:

= VLOOKUP ("Winters", C2: F101, 4, 0)

Dato che i punteggi SAT sono la quarta colonna oltre la colonna del cognome, ho usato 4 per l'argomento indice della colonna. Nota che quando stai cercando testo, l'impostazione di [range_lookup] su 0 è una buona idea; senza di essa, puoi ottenere risultati cattivi. Ecco cosa Microsoft ci offre:

Restituì 651, il punteggio SAT appartenente allo studente di nome Kennedy Winters, che si trova nella riga 92 (visualizzato nell'insieme sopra). Ci sarebbe voluto molto più tempo per scorrere cercando il nome piuttosto che per digitare rapidamente la sintassi!

VLOOKUP può anche essere abbastanza utile se stai usando Microsoft Excel per fare le tue tasse facendo le tue tasse? 5 formule di Excel che devi conoscere facendo le tue tasse? 5 formule di Excel da sapere Sono due giorni prima che le tasse siano dovute e non si vuole pagare un'altra tassa di deposito tardiva. Questo è il momento di sfruttare la potenza di Excel per ottenere tutto in ordine. Leggi di più .

Note su CERCA.VERT

Alcune cose sono buone da ricordare quando usi VLOOKUP. Innanzitutto, come ho detto in precedenza, assicurati che la prima colonna dell'intervallo sia quella che include il tuo valore di ricerca. Se non è nella prima colonna, la funzione restituirà risultati errati. Se le tue colonne sono ben organizzate, questo non dovrebbe essere un problema.

La seconda cosa da tenere presente è che VLOOKUP restituirà sempre un solo valore. Se avessimo usato “Georgia” come valore di ricerca, avrebbe restituito il punteggio del primo studente della Georgia, e non ha dato alcuna indicazione che ci sono in effetti due studenti della Georgia.

La funzione CERCA.ORIZZ

Dove CERCA.VERT trova i valori corrispondenti in un'altra colonna, CERCA.ORG trova i valori corrispondenti in una riga diversa. Poiché in genere è più semplice eseguire la scansione delle intestazioni di colonna finché non trovi quella giusta e utilizzare un filtro per trovare quello che stai cercando, HLOOKUP è il modo migliore di utilizzarlo quando hai veramente grandi fogli di calcolo o stai lavorando con valori organizzati in base al tempo. Ecco la sintassi:

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

[lookup_value], ancora una volta, è il valore che conosci e desideri trovare un valore corrispondente per. [table_array] è le celle in cui si desidera cercare. [row_index_num] specifica la riga da cui verrà il valore restituito. E [range_lookup] è lo stesso di sopra; lascia vuoto per ottenere il valore più vicino quando possibile, oppure inserisci 0 per cercare solo corrispondenze esatte.

Per questo esempio, ho creato un nuovo foglio di lavoro con generateata.com. Questo contiene una riga per ogni stato, insieme a un punteggio SAT (diremo che è il punteggio medio per lo stato) negli anni 2000-2014. Utilizzeremo HLOOKUP per trovare il punteggio medio nel Minnesota nel 2013. Ecco come lo faremo:

= HLOOKUP (2013, A1: P51, 24)

(Nota che il 2013 non è tra virgolette perché è un numero e non una stringa, inoltre, il 24 viene dal Minnesota che si trova nella 24a riga.) Come puoi vedere nell'immagine qui sotto, il punteggio viene restituito:

Il Minnesotan aveva una media di 1014 nel 2013.

Note su HLOOKUP

Come con CERCA.VERT, il valore di ricerca deve essere nella prima riga dell'array della tabella; raramente si tratta di un problema con HLOOKUP, poiché di solito si utilizza un titolo di colonna per un valore di ricerca. HLOOKUP restituisce anche solo un singolo valore.

Le funzioni INDICE e MATCH

INDICE e MATCH sono due funzioni diverse, ma quando vengono utilizzate insieme possono rendere la ricerca di un foglio di calcolo molto più veloce. Entrambe le funzioni presentano degli svantaggi, ma combinandole svilupperemo i punti di forza di entrambi. Prima, però, la sintassi:

= INDICE ([matrice], [numero_riga], [numero_colonna]) = MATCH ([valore_supera], [matrice_supera], [tipo_vista])

In INDICE, [array] è l'array in cui cercherai. [row_number] e [column_number] possono essere utilizzati per restringere la ricerca; daremo un'occhiata a questo in un momento.

MATCH's [lookup_value] è un termine di ricerca che può essere una stringa o un numero; [lookup_array] è la matrice in cui Microsoft Excel cercherà il termine di ricerca. [match_type] è un argomento facoltativo che può essere 1, 0 o -1; 1 restituirà il valore più grande che è minore o uguale al termine di ricerca; 0 restituirà solo il tuo termine esatto; e -1 restituirà il valore più piccolo maggiore o uguale al termine di ricerca.

Potrebbe non essere chiaro come utilizzeremo queste due funzioni insieme, quindi lo spiegherò qui. MATCH accetta un termine di ricerca e restituisce un riferimento di cella. Nell'immagine sottostante, puoi vedere che in una ricerca per il valore 646 nella colonna F, MATCH restituisce 4.

L'INDICE, d'altra parte, fa il contrario: prende un riferimento di cella e restituisce il valore in esso. Potete vedere qui che, quando viene detto di restituire la sesta cella della colonna Città, INDICE ritorna “Ancoraggio,” il valore dalla riga 6.

Quello che andremo a fare è combinare i due in modo che MATCH restituisca un riferimento di cella e INDEX usa quel riferimento per cercare il valore in una cella. Diciamo che ricordi che c'era uno studente il cui cognome era Waters, e vuoi vedere qual è stato il punteggio di questo studente. Ecco la formula che useremo:

= INDICE (F: F, MATCH ("Waters", C: C, 0))

Noterai che il tipo di corrispondenza è impostato su 0 qui; quando stai cercando una stringa, è quello che vorresti usare. Ecco cosa otteniamo quando eseguiamo quella funzione:

Come puoi vedere dal riquadro, Owen Waters ha ottenuto un punteggio di 1720, il numero che appare quando eseguiamo la funzione. Questo può non sembrare tutto ciò che è utile quando puoi solo guardare alcune colonne, ma immagina quanto tempo avresti salvato se avessi dovuto farlo 50 volte su un foglio di calcolo di database di grandi dimensioni Excel vs. Accesso: un foglio di calcolo può sostituire un database? Excel vs. Accesso: un foglio di calcolo può sostituire un database? Quale strumento dovresti usare per gestire i dati? Access ed Excel sono entrambi dotati di funzionalità di filtraggio, confronto e interrogazione dei dati. Ti mostreremo qual è il più adatto alle tue esigenze. Leggi altro che conteneva diverse centinaia di colonne!

Lascia che inizi la ricerca

Microsoft Excel ha un sacco di funzioni estremamente potenti 3 Formule di Excel pazzesche che fanno cose stupefacenti 3 Formule di Excel pazzesche che fanno cose stupefacenti Formule di formattazione condizionale in Microsoft Excel possono fare cose meravigliose. Ecco alcuni accurati trucchi per la produttività in Excel. Leggi di più, e le quattro sopra elencate graffiano semplicemente la superficie. Anche con questa panoramica sommaria, tuttavia, dovresti riuscire a risparmiare molto tempo quando lavori con fogli di lavoro di grandi dimensioni. Se vuoi vedere quanto ancora puoi fare con INDEX, dai un'occhiata “L'indice imponente” in Microsoft Excel Hero.

Come gestisci ricerche in grandi fogli di calcolo? Come hai trovato queste funzioni di ricerca per essere utile? Condividi i tuoi pensieri ed esperienze qui sotto!

Crediti immagine: lente d'ingrandimento su un foglio di calcolo Via Shutterstock

Scopri di più su: Microsoft Excel, foglio di calcolo.