Contenuti
Nella seconda parte del nostro tutorial sulla funzione VPR (VLOOKUP) in Excel, analizzeremo alcuni esempi che ti aiuteranno a dirigere tutta la potenza VPR per risolvere i compiti di Excel più ambiziosi. Gli esempi presuppongono che tu abbia già una conoscenza di base del funzionamento di questa funzione. In caso contrario, potresti essere interessato a iniziare con la prima parte di questo tutorial, che spiega la sintassi e l'utilizzo di base. VPR. Bene, iniziamo.
Cerca in Excel in base a più criteri
Funzione VPR in Excel è uno strumento davvero potente per eseguire ricerche di un valore particolare in un database. Tuttavia, esiste una limitazione significativa: la sua sintassi consente di cercare un solo valore. Cosa succede se si desidera eseguire la ricerca in base a più condizioni? Di seguito troverai la soluzione.
Esempio 1: ricerca in base a 2 criteri diversi
Supponiamo di avere un elenco di ordini e di volerlo trovare Quantità di merci (Qtà), in base a due criteri – Nome del cliente (Cliente) è il nome del prodotto (Prodotto). La questione è complicata dal fatto che ciascuno degli acquirenti ha ordinato diversi tipi di merce, come si può vedere dalla tabella seguente:
funzione regolare VPR non funzionerà in questo scenario perché restituirà il primo valore trovato che corrisponde al valore di ricerca specificato. Ad esempio, se vuoi conoscere la quantità di un articolo Dolci'ordinato dall'acquirente Jeremy Hill, scrivi la seguente formula:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– questa formula restituirà il risultato 15corrispondente al prodotto Mele, perché è il primo valore che corrisponde.
C'è una semplice soluzione alternativa: creare una colonna aggiuntiva in cui combinare tutti i criteri desiderati. Nel nostro esempio, queste sono le colonne Nome del cliente (Cliente) è il nome del prodotto (Prodotto). Non dimenticare che la colonna unita deve essere sempre la colonna più a sinistra nell'intervallo di ricerca, poiché è la colonna di sinistra che la funzione VPR alza lo sguardo quando si cerca un valore.
Quindi, aggiungi una colonna ausiliaria alla tabella e copi la seguente formula su tutte le sue celle: =B2&C2. Se vuoi che la stringa sia più leggibile, puoi separare i valori combinati con uno spazio: =B2&» «&C2. Successivamente, puoi utilizzare la seguente formula:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Dov'è la cella B1 contiene il valore concatenato dell'argomento valore di ricerca (valore_ricerca) e 4 - Discussione col_index_num (column_number), ovvero il numero della colonna contenente i dati da recuperare.
Esempio 2: CERCA.VERT in base a due criteri con la tabella visualizzata su un altro foglio
Se devi aggiornare la tabella principale (Tabella principale) aggiungendo i dati della seconda tabella (Tabella di ricerca), che si trova su un altro foglio o in un'altra cartella di lavoro di Excel, puoi raccogliere il valore desiderato direttamente nella formula che inserisci nel tavolo principale.
Come nell'esempio precedente, avrai bisogno di una colonna ausiliaria nella tabella di ricerca con i valori combinati. Questa colonna deve essere la colonna più a sinistra nell'intervallo di ricerca.
Quindi la formula con VPR potrebbe essere così:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Qui, le colonne B e C contengono rispettivamente i nomi dei clienti e dei prodotti e il collegamento Ordini!$A&$2:$D$2 definisce una tabella da cercare in un altro foglio.
Per rendere la formula più leggibile, puoi dare un nome all'intervallo di visualizzazione, quindi la formula apparirà molto più semplice:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Affinché la formula funzioni, i valori nella colonna più a sinistra della tabella che stai guardando devono essere combinati esattamente come nei criteri di ricerca. Nella figura sopra, abbiamo combinato i valori u2bu2band mettendo uno spazio tra di loro, nello stesso modo in cui devi fare nel primo argomento della funzione (BXNUMX& “” & CXNUMX).
Ricorda! Funzione VPR limitato a 255 caratteri, non può cercare un valore con una lunghezza superiore a 255 caratteri. Tienilo a mente e assicurati che la lunghezza del valore desiderato non superi questo limite.
Concordo sul fatto che l'aggiunta di una colonna ausiliaria non sia la soluzione più elegante e non sempre accettabile. Puoi fare lo stesso senza la colonna di supporto, ma ciò richiederebbe una formula molto più complessa con una combinazione di funzioni INDICE (INDICE) e PARTITA (PIÙ ESPOSTO).
Estraiamo il 2°, 3°, ecc. valori usando VLOOKUP
Lo sai già VPR può restituire un solo valore corrispondente, più precisamente il primo trovato. Ma cosa succede se questo valore viene ripetuto più volte nell'array visualizzato e si desidera estrarre il 2° o il 3° di essi? E se tutti i valori? Il problema sembra complicato, ma la soluzione esiste!
Si supponga che una colonna della tabella contenga i nomi dei clienti (Nome cliente) e l'altra colonna contenga i prodotti (Prodotto) che hanno acquistato. Proviamo a trovare il 2°, 3° e 4° articolo acquistato da un determinato cliente.
Il modo più semplice è aggiungere una colonna ausiliaria prima della colonna Nome del cliente e riempilo con i nomi dei clienti con il numero di ripetizione di ogni nome, ad esempio, John Doe1, John Doe2 ecc. Faremo il trucco con la numerazione usando la funzione COUNTIF (COUNTIF), dato che i nominativi dei clienti sono nella colonna B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Successivamente è possibile utilizzare la funzione normale VPRper trovare l'ordine richiesto. Per esempio:
- Trovare 2-esimo articolo ordinato dal cliente Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Trovare 3-esimo articolo ordinato dal cliente Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
In effetti, puoi inserire un riferimento di cella come valore di ricerca anziché testo, come mostrato nella figura seguente:
Se stai solo cercando 2-e ripetizione, puoi farlo senza la colonna ausiliaria creando una formula più complessa:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
In questa formula:
- $ F $ 2 – una cella contenente il nome dell'acquirente (è invariato, si prega di notare – il link è assoluto);
- $B$ – colonna Nome del cliente;
- Table4 – Il tuo tavolo (questo posto può anche essere un intervallo regolare);
- $ C16 – la cella finale della tabella o dell'intervallo.
Questa formula trova solo il secondo valore corrispondente. Se è necessario estrarre le ripetizioni rimanenti, utilizzare la soluzione precedente.
Se hai bisogno di un elenco di tutte le corrispondenze, la funzione VPR questo non è un aiuto, poiché restituisce solo un valore alla volta - periodo. Ma Excel ha una funzione INDICE (INDEX), che può facilmente far fronte a questo compito. Come apparirà una tale formula, imparerai nel seguente esempio.
Recupera tutte le ripetizioni del valore desiderato
Come menzionato sopra VPR non è possibile estrarre tutti i valori duplicati dall'intervallo scansionato. Per fare ciò, è necessaria una formula leggermente più complessa, composta da diverse funzioni di Excel, come ad es INDICE (INDICE), SMALL (PICCOLO) e RIGA (LINEA)
Ad esempio, la formula seguente trova tutte le ripetizioni del valore dalla cella F2 nell'intervallo B2:B16 e restituisce il risultato dalle stesse righe nella colonna C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Immettere questa formula di matrice in più celle adiacenti, ad esempio le celle F4: F8come mostrato nella figura sottostante. Il numero di celle deve essere uguale o maggiore del numero massimo possibile di ripetizioni del valore cercato. Non dimenticare di fare clic CTRL + MAIUSC + INVIOper immettere correttamente la formula della matrice.
Se sei interessato a capire come funziona, tuffiamo un po' nei dettagli della formula:
Parte 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$FA$2=SI2:SI16 – confronta il valore nella cella F2 con ciascuno dei valori nell'intervallo B2:B16. Se viene trovata una corrispondenza, l'espressione STRINGA(C2:C16)-1 restituisce il numero della riga corrispondente (valore -1 consente di non includere la riga di intestazione). Se non ci sono corrispondenze, la funzione IF (IF) restituisce una stringa vuota.
Risultato della funzione IF (IF) ci sarà un tale array orizzontale: {1,"",3,"",5,"","","","","","",12,"","",""}
Parte 2:
ROW()-3
СТРОКА()-3
Ecco la funzione RIGA (LINE) funge da contatore aggiuntivo. Poiché la formula viene copiata nelle celle F4:F9, sottraiamo il numero 3 dal risultato della funzione per ottenere il valore 1 nella cella F4 (riga 4, sottrai 3) per ottenere 2 nella cella F5 (riga 5, sottrai 3) e così via.
Parte 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funzione SMALL (PICCOLO) ritorna no-oh il valore più piccolo nell'array di dati. Nel nostro caso, quale posizione (dalla più piccola) restituire è determinata dalla funzione RIGA (LINEA) (vedi Parte 2). Quindi, per una cella F4 function PICCOLO({array},1) problemi 1-esimo (il più piccolo) elemento dell'array, ad es 1. Per cellulare F5 problemi 2-esimo l'elemento più piccolo nell'array, cioè 3, ecc.
Parte 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funzione INDICE (INDEX) restituisce semplicemente il valore di una cella specifica in una matrice DO2: DO16. Per cellulare F4 function INDICE($C$2:$C$16) sarà di ritorno MeleNel F5 function INDICE($C$2:$C$16) sarà di ritorno Dolci' e così via.
Parte 5:
IFERROR()
ЕСЛИОШИБКА()
Infine, inseriamo la formula all'interno della funzione IFERROR (IFERROR), perché è improbabile che tu sia soddisfatto del messaggio di errore #at (#N/D) se il numero di celle in cui viene copiata la formula è inferiore al numero di valori duplicati nell'intervallo visualizzato.
XNUMXD ricerca per riga e colonna nota
L'esecuzione di una ricerca XNUMXD in Excel implica la ricerca di un valore in base a un numero di riga e colonna noto. In altre parole, stai estraendo il valore della cella all'intersezione di una particolare riga e colonna.
Quindi, torniamo alla nostra tabella e scriviamo una formula con una funzione VPR, che troverà informazioni sul costo dei limoni venduti a marzo.
Esistono diversi modi per eseguire una ricerca XNUMXD. Scopri le opzioni e scegli quella più adatta a te.
Funzioni VLOOKUP e MATCH
Puoi usare un sacco di funzioni VPR (CERCA.VERT) e PIÙ ESPOSTO (MATCH) per trovare il valore all'intersezione dei campi il nome del prodotto (stringa) e Mese (colonna) dell'array in questione:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
La formula sopra è una funzione regolare VPR, che cerca una corrispondenza esatta del valore "Limoni" nelle celle da A2 a A9. Ma poiché non sai in quale colonna si trovano le vendite di marzo, non sarai in grado di impostare il numero di colonna per il terzo argomento della funzione. VPR. Viene invece utilizzata la funzione PIÙ ESPOSTOper definire questa colonna.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Tradotta in linguaggio umano, questa formula significa:
- Stiamo cercando i personaggi “Mar” – argomento valore di ricerca (valore di ricerca);
- Guardando nelle celle da A1 a I1 – argomento ricerca_array (ricerca_array);
- Restituzione della corrispondenza esatta – argomento tipo_corrispondenza (tipo_corrispondenza).
utilizzando 0 nel terzo argomento, dici funzioni PIÙ ESPOSTO cerca il primo valore che corrisponde esattamente al valore che stai cercando. Questo è equivalente al valore FALSO (FALSO) per il quarto argomento VPR.
Ecco come creare una formula di ricerca bidirezionale in Excel, nota anche come ricerca bidimensionale o ricerca bidirezionale.
funzione SOMMAPRODOTTO
Funzione MATR.SOMMA.PRODOTTO (SUMPRODUCT) restituisce la somma dei prodotti degli array selezionati:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funzioni INDICE e ABBINAMENTO
Nel prossimo articolo ti spiegherò in dettaglio queste funzioni, quindi per ora puoi semplicemente copiare questa formula:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Intervalli denominati e operatore di intersezione
Se non ti piacciono tutte quelle complesse formule di Excel, ti potrebbe piacere questo modo visivo e memorabile:
- Seleziona la tabella, apri la scheda Formule (Formule) e fare clic Crea da selezione (Crea dalla selezione).
- Seleziona le caselle Riga superiore (nella riga sopra) e Colonna sinistra (nella colonna a sinistra). Microsoft Excel assegnerà nomi agli intervalli dai valori nella riga superiore e nella colonna di sinistra del foglio di calcolo. Ora puoi cercare utilizzando questi nomi direttamente senza creare formule.
- In qualsiasi cella vuota, scrivi =nome_riga nome_colonna, ad esempio così:
=LimoniMar
… o vice versa:
=Mar Limoni
Ricorda che i nomi di riga e colonna devono essere separati da uno spazio, che in questo caso funziona come l'operatore di intersezione.
Quando inserisci un nome, Microsoft Excel mostrerà un suggerimento con un elenco di nomi corrispondenti, proprio come quando inserisci una formula.
- Rassegna Stampa entrare e controlla il risultato
In generale, qualunque dei metodi di cui sopra tu scelga, il risultato di una ricerca bidimensionale sarà lo stesso:
Utilizzo di più VLOOKUP in una formula
Succede che la tabella principale e la tabella di ricerca non abbiano una sola colonna in comune, e questo ti impedisce di usare la solita funzione VPR. Tuttavia, esiste un'altra tabella che non contiene le informazioni che ci interessano, ma ha una colonna comune con la tabella principale e la tabella di ricerca.
Diamo un'occhiata al seguente esempio. Abbiamo una tabella principale con una colonna SKU (nuovo), dove vuoi aggiungere una colonna con i prezzi corrispondenti da un'altra tabella. Inoltre, abbiamo 2 tabelle di ricerca. Il primo (Tabella di ricerca 1) contiene i numeri aggiornati SKU (nuovo) e nomi di prodotti e il secondo (Tabella di ricerca 2) – nomi di prodotti e vecchi numeri SKU (vecchio).
Per aggiungere i prezzi dalla seconda tabella di ricerca alla tabella principale, è necessario eseguire un'azione nota come double VPR o annidato VPR.
- Scrivi una funzione VPR, che trova il nome del prodotto nella tabella Tabella di ricerca 1utilizzando SKU, come valore desiderato:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Qui Nuovo_SKU – intervallo denominato $A:$B sul tavolo Tabella di ricerca 1, 2 – questa è la colonna B, che contiene i nomi delle merci (vedi immagine sopra)
- Scrivi una formula per inserire i prezzi da una tabella Tabella di ricerca 2 sulla base di noti nomi di prodotti. Per fare ciò, incolla la formula che hai creato in precedenza come valore di ricerca per la nuova funzione VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Qui Prezzo – intervallo denominato $ A: $ C sul tavolo Tabella di ricerca 2, 3 è la colonna C contenente i prezzi.
La figura seguente mostra il risultato restituito dalla formula che abbiamo creato:
Sostituzione dinamica di dati da tabelle diverse utilizzando VLOOKUP e INDIRETTO
Innanzitutto, chiariamo cosa intendiamo con l'espressione “Sostituzione dinamica di dati da tabelle diverse” per assicurarci di capirci correttamente.
Ci sono situazioni in cui ci sono più fogli con dati dello stesso formato ed è necessario estrarre le informazioni necessarie da un determinato foglio, a seconda del valore che viene inserito in una determinata cella. Penso che sia più facile spiegarlo con un esempio.
Immagina di avere rapporti sulle vendite per diverse regioni con gli stessi prodotti e nello stesso formato. Vuoi trovare i dati di vendita per una regione specifica:
Se hai solo due di questi rapporti, puoi usare una formula vergognosamente semplice con funzioni VPR и IF (IF) per selezionare il report desiderato da cercare:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Dove:
- $D$2 è una cella contenente il nome del prodotto. Si noti che qui utilizziamo riferimenti assoluti per evitare di modificare il valore di ricerca quando si copia la formula in altre celle.
- $ D3 è una cella con il nome della regione. Stiamo utilizzando un riferimento di colonna assoluto e un riferimento di riga relativo perché prevediamo di copiare la formula in altre celle nella stessa colonna.
- FL_Sales и CA_Vendite – i nomi delle tabelle (o intervalli denominati) che contengono i corrispondenti report di vendita. È possibile, ovviamente, utilizzare i soliti nomi dei fogli e riferimenti all'intervallo di celle, ad esempio 'Foglio FL'!$A$3:$B$10, ma gli intervalli denominati sono molto più convenienti.
Tuttavia, quando sono presenti molte tabelle di questo tipo, la funzione IF non è la soluzione migliore. Invece, puoi usare la funzione INDIRETTI (INDIRETTO) per tornare all'intervallo di ricerca desiderato.
Come probabilmente saprai, la funzione INDIRETTI viene utilizzato per restituire un collegamento fornito da una stringa di testo, che è esattamente ciò di cui abbiamo bisogno ora. Quindi, sostituisci audacemente nella formula sopra l'espressione con la funzione IF collegare con la funzione INDIRETTI. Ecco una combinazione VPR и INDIRETTI funziona benissimo con:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Dove:
- $D$2 – questa è una cella con il nome del prodotto, è invariata per il collegamento assoluto.
- $ D3 è la cella contenente la prima parte del nome della regione. Nel nostro esempio, questo FL.
- _I saldi – la parte comune del nome di tutti gli intervalli o le tabelle con nome. Se combinato con il valore nella cella D3, forma il nome completo dell'intervallo richiesto. Di seguito sono riportati alcuni dettagli per coloro che sono nuovi alla funzione INDIRETTI.
Come funzionano INDIRETTO e VLOOKUP
Innanzitutto, lascia che ti ricordi la sintassi della funzione INDIRETTI (INDIRETTO):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Il primo argomento può essere un riferimento di cella (stile A1 o R1C1), un nome di intervallo o una stringa di testo. Il secondo argomento determina quale stile di collegamento è contenuto nel primo argomento:
- A1se l'argomento è VERO CODICE (VERO) o non specificato;
- R1C1, Se FCOME E (FALSO).
Nel nostro caso, il collegamento ha lo stile A1, quindi puoi omettere il secondo argomento e concentrarti sul primo.
Quindi torniamo ai nostri rapporti di vendita. Se ricordi, ogni rapporto è una tabella separata situata su un foglio separato. Affinché la formula funzioni correttamente, è necessario assegnare un nome alle tabelle (o intervalli) e tutti i nomi devono avere una parte comune. Ad esempio, in questo modo: CA_Vendite, FL_Vendite, TX_Vendite e così via. Come puoi vedere, “_Sales” è presente in tutti i nomi.
Funzione INDIRETTI collega il valore nella colonna D e la stringa di testo "_Sales", indicando così VPR in quale tabella cercare. Se la cella D3 contiene il valore "FL", la formula cercherà nella tabella FL_Vendite, se “CA” – nella tabella CA_Vendite e così via.
Il risultato delle funzioni VPR и INDIRETTI sarà il seguente:
Se i dati si trovano in diversi libri Excel, è necessario aggiungere il nome del libro prima dell'intervallo denominato, ad esempio:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Se la funzione INDIRETTI fa riferimento a un'altra cartella di lavoro, tale cartella di lavoro deve essere aperta. Se è chiuso, la funzione segnalerà un errore. #RIF! (#SSIL!).