Come VLOOKUP da un altro foglio in Google Sheets
Puoi sbloccare tutto il potenziale di Vlookup, (ricerca verticale), in Google Sheets usandolo per chiamare i dati tra diversi fogli di calcolo e cartelle di lavoro.
La funzione Vlookup è una funzione incredibilmente potente che può trasformare ore di lavoro in pochi secondi. Mentre la funzione è utile per l’analisi dei dati su un foglio singolare, è possibile migliorare ulteriormente l’usabilità di Vlookup chiamando i dati su diversi fogli di calcolo.
Con questa funzione, è possibile cercare e recuperare valori specifici da grandi quantità di dati in modo rapido ed efficiente.
Essere in grado di utilizzarlo su più fogli di calcolo rende più facile mantenere puliti i dati e aggiornarli automaticamente quando ci sono modifiche tra i fogli.
Questo articolo copre:
La formula di VLOOKUP
La sintassi di Vlookup è piuttosto semplice ma può essere utilizzata in modo molto complesso.
Come suggerisce il “verticale” nel suo nome, Vlookup cerca la colonna più a sinistra nell’intervallo stabilito per una corrispondenza di riga e restituisce il valore nella cella identificata dall’indice.
Cerca una colonna e restituisce un valore dalla riga corrispondente, di solito da una colonna diversa.
Uno dei maggiori punti deboli in Vlookup è che non è possibile cercare una corrispondenza in una colonna a destra e riportare un risultato da una colonna alla sua sinistra. In altre parole, Vlookup può solo guardare o girare a destra.
Questo è l’aspetto della formula quando suddivisa per parte:
=Vlookup(search_key,range,index,is_sorted)
Diamo un’occhiata a ciascun componente singolarmente:
- =Vlookup (): Questa è la funzione stessa senza alcun parametro. È ciò che dice a Google Sheets di agire.
- search_key: questo parametro definisce quali informazioni stiamo cercando di abbinare. Potrebbe essere un nome, un numero, un valore booleano o qualcosa di diverso. Potrebbe essere un valore statico che abbiamo definito o potrebbe essere un valore relativo memorizzato in una cella. Se impostiamo questo come ” A2 “cercherà il valore nella cella”A2”. Se lo impostiamo per “true” corrisponderà alla stringa di testo ” true.”
- gamma: Questo indica alla funzione Vlookup dove cercare una corrispondenza e l’intervallo in cui si trova il valore che dovrebbe restituire. Se stiamo cercando una corrispondenza di valore nella colonna B e vogliamo restituire un valore nelle colonne C o D, impostiamo l’intervallo come B:D.
- indice: questo parametro indica a Vlookup quale valore di colonna restituire. L’indice è relativo all’intervallo, non al foglio. Quindi è un numero invece di una lettera come le colonne sono solitamente definite. Se la nostra ricerca corrisponde nella colonna B e restituisce un valore dalla colonna C, il valore dell’indice è 2. Se restituiamo la colonna D da una corrispondenza della colonna B, il valore dell’indice è 3.
- is_sorted: il nome di questo parametro non è così chiaro in quanto restituisce corrispondenze esatte quando è impostato su “false” e la corrispondenza più vicina quando è impostata su “true.”True è impostato di default, ma false è raccomandato nella maggior parte degli usi.
Ora diamo un’occhiata a tutto messo insieme:
=Vlookup(A2,A2:B5,2,false)
Utilizzo di VLOOKUP nella stessa cartella di lavoro e in una cartella di lavoro diversa
Mentre la maggior parte del tempo, usiamo la formula VLOOKUP in Fogli Google sullo stesso foglio, molte volte potrebbe essere necessario utilizzarlo per VLOOKUP tra due fogli nella stessa cartella di lavoro o anche attraverso la cartella di lavoro diversa.
Ad esempio, è possibile recuperare i dati da elementi specifici in un foglio di lavoro, mentre i dati di ricerca si trovano in un foglio diverso o in una cartella di lavoro diversa.
Il modo in cui si utilizza la funzione Vlookup per inserire dati da un altro foglio è leggermente diverso quando si lavora nella stessa cartella di lavoro o in una cartella di lavoro diversa.
La formula per la stessa cartella di lavoro Vlookup è simile:
=vlookup(search_key,{sheet name}!{cell range},index,is_sorted)
Notate che c’è un”!”tra il nome del foglio e l’intervallo di celle. Inoltre, non ci sono virgolette nell’intervallo su un Vlookup della stessa cartella di lavoro.
Nel nostro test, vogliamo impostare l’intervallo su A3:B6 sul nostro foglio chiamato “Chiamato.”Stiamo cercando una corrispondenza nella colonna A e restituendo il valore nella colonna B.
Di seguito è riportata la formula che lo farà:
=Vlookup(A2,Called!A2:B5,2,false)
La formula precedente recupera il valore dalla seconda colonna dal foglio chiamato “Chiamato” nel foglio corrente.
Ora, diamo un’occhiata a un esempio in cui dobbiamo recuperare il valore da una cartella di lavoro di Google Sheets diversa
=vlookup(search_key,importrange("{sheetsURL}","{sheet name}!{cell range}"),index,is_sorted)
Questa versione utilizza un nuovo comando chiamato “importrange()”.
La sintassi di questo comando richiede di definire l’URL della cartella di lavoro da cui stai importando i dati, definire il foglio specifico e impostare l’intervallo.
La formula si rompe in questo modo:
Importrange("{sheetsURL}","{sheet name}!{cel range}")
- {sheetsURL}: Tra virgolette, aggiungi l’URL del file Google Sheets a cui vuoi accedere. Esempio: “https://docs.google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI”
- {nome foglio}!{cel range}: Impostalo nello stesso modo in cui lo hai configurato nel metodo same-workbook. Tuttavia, si noti che questa volta ci sono virgolette attorno al nome del foglio e all’intervallo di celle.
Ora guardalo con un valore aggiunto e tutti i valori aggiunti :
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)
Per ribadire il concetto, ci sono tre valori importanti che è necessario impostare un cross-foglio Vlookup di riferimento:
- URL Cartella di lavoro, {sheetsURL}
- Pagina di Foglio di {nome del foglio}
- Intervallo di celle {intervallo di celle}:
In esecuzione attraverso la Vlookup processo
Cerchiamo di capire il processo di come VLOOKUP funziona quando si fa riferimento ad altri fogli o cartelle di lavoro.
VLOOKUP da un altro foglio di lavoro nella stessa cartella di lavoro
Usiamo un semplice caso di inventario del prodotto per mostrare come funziona Vlookup.
Abbiamo una linea di quattro prodotti in questo esempio: Gadget, aggeggi, Thingamabobs e Widget. Attualmente, non abbiamo alcun aggeggi in magazzino. Il nostro primo foglio, “Active” ci fornisce un elenco di tutti i nostri prodotti e ci dice se un prodotto è in magazzino:
Abbiamo anche un secondo foglio che abbiamo chiamato “Chiamato” che elenca quanti di ogni prodotto che abbiamo in magazzino.
Ora diciamo che vogliamo che la scheda “Attiva” mostri quanti prodotti sono in magazzino, ma vogliamo solo preoccuparci di aggiornare una pagina di foglio di calcolo.
- Inserisci la nostra formula Vlookup nella cella più in alto — nel nostro caso, stiamo usando
=Vlookup(A2,Called!A2:B5,2,false)in cell C2.
- Trascinare l’indicatore in basso a destra verso il basso per applicarlo per ogni prodotto (freccia blu).
Ora la nostra pagina ” Attiva “sta estraendo i valori dalla pagina” Chiamata”. Uno dei vantaggi dell’utilizzo di Vlookup è che i prodotti corrisponderanno comunque se entrambi i fogli di calcolo li elencano in un ordine diverso.
Inoltre, non verrà eliminato se un prodotto manca dal foglio di calcolo di riferimento.
VLOOKUP da un altro foglio di lavoro in una cartella di lavoro diversa
Il processo di riferimento dei dati da una cartella di lavoro diversa è un po ‘ più complicato.
Useremo la stessa linea di prodotti di Gadget, aggeggi, Thingamabobs e Widget dell’esempio precedente.
Questa volta, vogliamo portare le stesse informazioni in un foglio chiamato “Esterno” in una cartella di lavoro diversa.
vogliamo portare le informazioni da “Chiamato” foglio della cartella di lavoro:
Ora diciamo che vogliamo avere il “Fuori” scheda di mostrare come molti prodotti sono in stock, ma noi vogliamo solo preoccuparsi di aggiornare la “Chiamata” pagina foglio di lavoro nella cartella di lavoro originale.
Inserire il nostro Vlookup formula nella cella più in alto — nel nostro caso, stiamo usando
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.
Ora la nostra seconda cartella di lavoro di riferimento la prima cartella di lavoro per inventario:
Questo può essere estremamente utile per qualcuno analizzando le informazioni nel primo foglio di lavoro, senza alcun rischio di errore di dati originale.
È anche molto utile per inserire una frazione delle informazioni in un foglio di calcolo per facilitare l’analisi in un altro. Il comando Vlookup rende più facile portare solo le informazioni desiderate.
Alcuni suggerimenti quando si utilizza VLOOKUP per fare riferimento a un altro foglio/cartella di lavoro
Ecco alcuni suggerimenti da tenere a mente quando si fa riferimento a un altro foglio o cartelle di lavoro nella formula:
Sii specifico sull’intervallo
La funzione Vlookup può essere molto affamata di prestazioni e causare un rallentamento delle prestazioni di una cartella di lavoro.
È possibile evitare prestazioni lente essendo specifici con gli intervalli di riferimento.
- Invece di chiamare intere colonne come “A:B “fare riferimento alle celle iniziali e finali specifiche come “A1: B1000”. Questo riduce la quantità di lavoro che Google Fogli deve fare per portare la stessa quantità di informazioni.
- Se stai cercando informazioni nella colonna A e riportando il risultato nella colonna D, usa un riferimento come “A1:D1000” invece di “A1:F1000”. Non è necessario fare riferimento alle colonne E ed F se nell’intervallo se non vengono utilizzate.
Ciò è particolarmente importante quando si chiamano informazioni tra diverse cartelle di lavoro. Quando si sta facendo un cross-workbook Vlookup, richiede larghezza di banda Internet per trasferire i dati tra i due.
Usa le istruzioni condizionali per evitare chiamate non necessarie
Un altro modo per prevenire il rallentamento con Vlookup cross-sheet consiste nell’utilizzare un’istruzione condizionale per determinare se Google Sheets deve eseguire il Vlookup.
Ad esempio, se ci sono informazioni sul foglio che stai utilizzando la chiamata Vlookup che ti dice che non è necessario eseguirlo, usalo a tuo vantaggio.
Nel nostro esempio di prodotto, il foglio “Attivo” elenca se un prodotto è disponibile o meno. Poiché sappiamo che un prodotto è esaurito, non è necessario utilizzare Vlookup per definire il conteggio delle scorte.
Per questo, useremo la funzione “=if ()”. Questa funzione chiede se una data condizione è vera o falsa, quindi fa qualcosa di diverso per ogni caso. La sintassi è simile a questa:
=if(logical_expression, value_if_true, value_if_false)
In un semplice caso d’uso, possiamo usarlo per determinare se il valore nella cella A1 è maggiore del valore nella cella B1. Quindi l’espressione A1 > B1 sarebbe simile a questa:
=if(A1>B1, "A1 is greater", "B1 is greater")
La formula restituirà il testo “A1 è maggiore” se A1 è il numero maggiore e “B1 è maggiore” se B1 è il numero maggiore.
Nel caso del nostro foglio di lavoro del prodotto, se il valore “In magazzino” è “No”, non vogliamo eseguire Vlookup. Quindi impostiamo la nostra dichiarazione if come questa:
- logical_expression: B2= ” YES “- Questo eseguirà il” valore se vero “se i dati nella cella B2 sono”YES”.
- Value_if_true: Vlookup (A2,Chiamato!A2: B5, 2, false) –Questo eseguirà il Vlookup se l’espressione logica restituisce true.
- Value_if_false: “esaurito” – Questo restituirà il testo “esaurito” se il valore nella cella B2 è diverso da ” SÌ.”
Se mettiamo tutto insieme viene fuori in questo modo:
=IF(B2="YES",Vlookup(A2,Called!A2:B5,2,false),"out of stock")
Si noti come la cella C3 ora dice “esaurito” invece di restituire il valore “0”. In questo caso, abbiamo evitato l’esecuzione di un Vlookup perché non avevamo bisogno di riportare i dati.
Mentre stai inserendo ulteriori informazioni in fogli Google per calcolare i risultati, sta creando meno lavoro per il programma.
È molto meno lavoro eseguire molti “controlli if” di un singolo Vlookup. L’utilizzo di questa tecnica ti aiuterà ad accelerare i fogli Google e migliorare le prestazioni.
Assicurati di avere l’autorizzazione
Per ovvi motivi di sicurezza, Google Sheets non ti consente di estrarre i dati da un’altra cartella di lavoro a meno che tu non abbia l’autorizzazione a farlo.
Per fare riferimento a una cartella di lavoro da un’altra con Vlookup, è necessario essere il creatore di entrambi o avere il permesso di utilizzare entrambi. Puoi essere aggiunto come utente autorizzato tramite account o tramite un URL di condivisione.
Utilizzando Vlookup per fare riferimento alle informazioni su diversi fogli e cartelle di lavoro è uno strumento incredibilmente potente per avere a vostra disposizione.
Il comando è particolarmente utile con l’uso di fogli incrociati perché rifletterà tutte le modifiche apportate al foglio originale su tutti i fogli di riferimento.