Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office
Computers

Come estrarre dati da un foglio di calcolo utilizzando VLOOKUP (cerca.ver), MATCH (confronta) e INDEX (indice)

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Italian (Italiano) translation by Fabio Pantano (you can also view the original English article)

Quando si ha la necessità di cercare ed estrarre una colonna di dati da una tabella e inserirla in un'altra, si utilizza la funzione VLOOKUP. Questa funzione opera su qualsiasi versione di Excel, sia Windows che Mac, e anche su Google Sheets. Consente di cercare dati in una tabella utilizzando un identificatore in comune con un'altra tabella. Le due tabelle possono essere su fogli diversi o anche su diverse cartelle di lavoro. C'è anche una funzione di HLOOKUP (cerca.orizz), che fa la stessa cosa, ma con dati disposti orizzontalmente, tra righe.

Le funzioni MATCH e INDEX sono valide da usare quando sei interessato a una posizione di dati specifici, ad esempio la colonna o la riga che contiene un nome di persona.

Opzioni Premium

Prima di analizzare le funzioni di Excel, lo sapete che l'Envato Market ha una serie di script e plugin per Excel per eseguire funzioni avanzate?

Ad esempio, è possibile:

Excel scripts and plugins on Envato Market
Script e plugin Excel su Envato Market

Video

Se si desidera seguire questo tutorial utilizzando un proprio file di Excel, è possibile farlo. O se preferisci, scarica il file zip allegato a questo tutorial, il quale contiene una cartella di lavoro di esempio chiamata vlookup example.xlsx.

Utilizzare il VLOOKUP (CERCA.VERT)

Quando il VLOOKUP rileva l'identificatore specificato nei dati di origine, può quindi trovare qualsiasi cella in quella riga restituendo le informazioni. Notare che nei dati di origine, l'identificatore deve essere nella prima colonna della tabella.

unique identifiers for vlookup
Un identificatore univoco dovrebbe essere tipo un numero di serie, dove due stessi valori non esistono nella tabella.

Sintassi

La sintassi della funzione VLOOKUP è:

= VLOOKUP(valore da cercare, intervallo in cui cercare il valore, numero di colonna nell'intervallo che contiene il valore restituito, [vero/falso])

Ecco cosa significano questi parametri:

  • Valore da cercare. La cella che contiene l'identificatore univoco.
  • Intervallo della tabella. Intervallo di celle che contiene l'identificatore della prima colonna, seguita dal resto dei dati in altre colonne.
  • Numero di colonna. Il numero della colonna che contiene i dati che stai cercando. Non confonderti con la lettera della colonna. Nell'immagine precedente, lo State è in colonna 4.
  • Vero/Falso. Questo argomento è facoltativo. Vero indica che una corrispondenza approssimativa è accettabile mentre Falso indica che solo una corrispondenza esatta è accettabile.

Per trovare gli importi di vendita dalla tabella nella figura sopra, usiamo questi argomenti:

syntax of vlookup
Sintassi della funzione VLOOKUP

Definire un nome di intervallo per creare un riferimento assoluto

Nel file Vlookup example.xlsx, guardate il foglio di lavoro di Sales Amounts. Immettere la formula in B5, poi utilizzare la funzionalità di riempimento automatico per copiare la formula verso il basso del foglio. Ciò significa che l'intervallo della tabella nella formula deve essere un riferimento assoluto. Un buon modo per farlo è quello di definire un nome per l'intervallo della tabella.

Definire un nome di intervallo in Excel

  1. Prima di inserire la formula, aprire il foglio di lavoro con i dati di origine.
  2. Selezionare tutte le celle da A4 (intestazione per la colonna Order #) fino a H203. Un modo rapido per farlo è quello di fare clic su A4, quindi premere Ctrl-MAIUSC-Fine (Comando-MAIUSC-Fine su Mac).
  3. Fare clic sulla casella nome sopra la colonna A (la casella nome visualizza A4).
  4. Digitare dati, quindi premere Invio.
  5. Nella formula ora è possibile utilizzare il nome dati anziché $A$4:$H$203.
Name box displaying a range name
Solitamente la casella nome solitamente visualizza il riferimento della cella corrente. Fare clic all'interno e digitare un nome per definire l'intervallo.

Definire un nome di intervallo su Google Sheets

In Google Sheets, definire un nome è un po' diverso.

  1. Fare clic sull'intestazione della prima colonna dei dati di origine, quindi premere Ctrl-Maiusc-Freccia destra (Comando-Maiusc-Freccia destra su Mac). Ciò seleziona la riga della colonna intestazioni
  2. Premere Ctrl-Maiusc-Freccia Giù (Comando-Maiusc-Freccia Giù su Mac). Questo seleziona i dati effettivi.
  3. Fare clic sul menu Dati, quindi selezionare Fogli e intervalli protetti.
  4. Nella casella Fogli e intervalli protetti sulla destra, digitare dati, quindi fare clic su Fine.
Data menu in Google Sheets
Definire un nome di intervallo in Google Sheets

Inserire la Formula

Per inserire la formula, aprire il foglio di lavoro Sales Amounts e cliccare in B5.

Inserire la formula:

=VLOOKUP(A5,dati,8,FALSO)

Premere Invio.

entering the vlookup function
Inserire la funzione VLOOKUP

Il risultato dovrebbe essere 40. Per compilare fino in basso i valori nella colonna, fare clic nuovamente su B5, se necessario. Posizionare il puntatore del mouse come punto di riempimento automatico nell'angolo inferiore destro della cella, il puntatore del mouse diventerà una crocetta.

mouse pointer on the autofill dot
Quando si posiziona il puntatore del mouse sul punto nell'angolo inferiore destro di una cella, diventa una croce di riempimento automatico

Fare doppio clic per riempire i valori nella colonna.

worksheet with data after using autofill
Fare doppio clic sulla croce di autocompilazione per copiare la formula nell'intera colonna

Se volete, potete eseguire la funzione VLOOKUP nelle successive colonne per estrarre altri campi, come name o state.

Utilizzare MATCH (CONFRONTA)

La funzione MATCH non restituisce alcun valore dati; fornendo il valore che stai cercando, la funzione restituisce la posizione di tale valore. È come chiedere dove si trova #135 Main Street e si ottiene come risposta che è il 4th edificio in fondo alla strada.

Sintassi

La sintassi della funzione MATCH è:

=MATCH(valore, vettore, [risultato])

Gli argomenti sono:

  • Valore. La cella che contiene l'identificatore univoco.
  • Matrice. L'intervallo di celle in cui si sta cercando.
  • Risultato. Facoltativo. È come specificare la precisione del confronto che si desidera fare, in questo modo:

Valore successivo più grande

-1

I valori devono essere in ordine decrescente.

Valore esatto

0

I valori possono essere in qualsiasi ordine.

Valore successivo più piccolo

1

Tipo predefinito. I valori devono essere in ordine crescente.

Come con la funzione VLOOKUP, probabilmente troverete la funzione MATCH più semplice da usare se si applica un nome di intervallo. Aprire il foglio con i dati di origine, selezionare da B4 (intestazione di colonna per order #) verso il basso, fare clic nella casella nome sopra la colonna A e chiamarlo order_number. Notare che i valori sono in ordine crescente.

name box with one column defined
Un intervallo nominato può essere solo una colonna, solo una riga, o anche solo una cella

Aprire il foglio di lavoro Match. In B5, inserire la funzione MATCH:

=MATCH(A5,order_number,1)

entering the match function
Inserire la funzione MATCH

Se non si definisce un nome di intervallo, è necessario scrivere la seguente funzione:

= MATCH(A5,'Source Data'! A5:A203, 0)

In entrambi i casi, si può vedere che questo è in 14th posizione (rendendolo come 13th ordine).

completed match function
Risultato della funzione MATCH

Utilizzare INDEX

La funzione INDEX è l'opposto della funzione MATCH ed è simile a VLOOKUP. Fornendo alla funzione la riga e la colonna dei dati di cui si desidera, ti dice il valore di ciò che contiene la cella.

Sintassi

La sintassi della funzione INDEX è:

=INDEX(matrice, riga, [colonna])

I parametri sono:

  • Matrice. Proprio come le altre due funzioni, questa è la tabella con i dati.
  • Numero di riga. Il numero della riga dei dati, che non è necessariamente la riga del foglio di lavoro. Se la matrice della tabella inizia alla riga 10 del foglio, allora è la riga #1.
  • Numero di colonna. Il numero della colonna dell'intervallo dati. Se la matrice inizia sulla colonna E, quella è la colonna #1.

La documentazione di Excel riporta che l'argomento numero di colonna è facoltativo, ma anche che il numero di riga è quasi facoltativo. Se la matrice della tabella contiene solo una riga o una colonna, non devi utilizzare l'altro argomento.

Apri il foglio index della cartella di lavoro e fare clic su C6. Innanzitutto vogliamo avere il contenuto nella riga 9, colonna 3 della tabella. Nella formula, useremo il nome della matrice che abbiamo creato in precedenza.

Inserire la formula:

=INDEX(data,A6,B6)

entering the index function
Inserimento della funzione index

Restituisce il nome di un cliente: Strevell. Modificare i valori di A6 e B6, e il risultato in C6 mostrerà risultati diversi (notare che molte righe hanno gli stessi stati e gli stessi nomi prodotto).

Conclusione

La capacità di un foglio di lavoro di guardare in un altro foglio di lavoro ed estrarre dati è un ottimo strumento. In questo modo, si può avere un unico foglio con tutti i dati necessari, quindi estrarre ciò che serve per istanze specifiche.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.