Hai mai avuto bisogno di estrarre un valore da un intervallo in base a un valore corrispondente nel tuo elenco? Ad esempio, potresti voler estrarre dinamicamente il prezzo di un prodotto in base all’ID del prodotto.
Se il tuo set di dati contiene l’ID prodotto, puoi usare una formula per determinare il prezzo.
Con questa formula impostata come formula per colonne, ogni riga (e tutte le nuove righe aggiunte) mostrerà il prezzo corrispondente dal set di dati del prodotto.
Diversi approcci a questa formula
Puoi utilizzare tre metodi per estrarre i dati da un intervallo in base a un valore di ricerca corrispondente:
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
Esamineremo come utilizzare ciascuna di queste formule, oltre a illustrare i pro e i contro di ogni approccio.
VLOOKUP
Una formula VLOOKUP cerca un valore e ne restituisce uno corrispondente nella stessa riga, ma in una colonna diversa (specificata). Il formato per una formula VLOOKUP è riportato di seguito:
=VLOOKUP([Valore di ricerca], [Set di dati cercato], [Numero di colonna nel set di dati],[Falso o vero in base alle esigenze di corrispondenza esatte])
Per calcolare il prezzo nell’esempio sopra usando una funzione VLOOKUP, la formula sarà simile alla seguente:
=VLOOKUP([Product ID associato]@row, {Product Data | Product}, 4, false)
L'intervallo di riferimento {Product Data | Product} tra fogli diversi è simile al seguente:
Inoltre, la formula restituisce i valori nella colonna Price (Prezzo) in questo modo:
SUGGERIMENTO: includi la formula in una formula IFERROR in modo da risolvere i casi in cui non viene trovata alcuna corrispondenza nel set di dati cercato. In questo esempio, la formula sarebbe simile a quella riportata di seguito:
=IFERROR(VLOOKUP([Product ID associato]@row, {Product Data | Product}, 4, false), "Nessuna corrispondenza trovata")
Vantaggi:
- Formula semplice/veloce
Svantaggi:
- Richiede che il valore di ricerca sia la prima colonna del set di dati in cui viene eseguita la ricerca
- Impossibile estrarre i valori a sinistra della colonna dei valori di ricerca
- Si interrompe se viene aggiunta una nuova colonna o se una colonna viene rimossa tra il valore di ricerca e la colonna inserita o se le colonne vengono riordinate
INDEX(MATCH())
Una formula INDEX(MATCH()) cerca in un intervallo e raccoglie il valore che corrisponde ai criteri specificati. Il formato per una formula INDEX(MATCH()) è riportato di seguito:
=INDEX([Intervallo con valore da restituire],MATCH([Valore di ricerca],[Intervallo con valore cercato],[0, 1 o -1 a seconda del tipo di ricerca]))
Per calcolare il prezzo nell’esempio sopra usando una funzione INDEX(MATCH()), la formula sarà simile alla seguente:
=IFERROR(INDEX({Product Data | Price}, MATCH([Product ID associato]@row, {Product Data | Product ID}, 0)), "Nessuna corrispondenza trovata")
L’intervallo di riferimento {Product Data | Price} tra fogli diversi è simile al seguente:
L’intervallo di riferimento {Product Data | Product ID} tra fogli diversi è simile al seguente:
Come indicato in precedenza, abbiamo inoltre incluso la nostra funzione INDEX(MATCH()) in una formula IFERROR per mostrare "Nessuna corrispondenza trovata" se non viene trovato alcun ID prodotto corrispondente per la riga; la formula restituisce quindi i valori nella colonna Price (Prezzo) in questo modo:
Vantaggi:
- Consente di modificare l’ordine delle colonne o di eliminare le colonne inutilizzate senza interruzioni
- Può estrarre valori da colonne a sinistra o a destra dell’intervallo dei valori di ricerca
- Più veloce per set di dati di dimensioni maggiori
- INDEX(MATCH(),MATCH()) può essere utilizzata per la corrispondenza dinamica di colonne e righe
- Il numero totale di celle con riferimento è in genere inferiore, il che aiuta a rientrare ampiamente nel limite totale di 100.000 celle a cui si fa riferimento nei riferimenti incrociati tra fogli.
Svantaggi:
- Richiede più di un riferimento incrociato tra fogli per i casi in cui esistono dati di riferimento in un foglio separato
- Limitato a un singolo criterio di corrispondenza
INDEX(COLLECT())
Una formula INDEX(COLLECT()) cerca in un intervallo e raccoglie il valore che corrisponde a uno o più criteri specificati. Il formato per una formula INDEX(COLLECT()) è riportato di seguito:
=INDEX(COLLECT([Intervallo con valore da restituire],[Intervallo con criterio],[Criterio],[Intervallo 2 con criterio],[Criterio], ecc.),[1 per l’indice di riga da restituire])
Per calcolare il prezzo nell’esempio sopra usando una funzione INDEX(COLLECT()), la formula sarà simile alla seguente:
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [Product ID associato]@row), 1), "Nessuna corrispondenza trovata")
L’intervallo di riferimento {Product Data | Price} tra fogli diversi è simile al seguente:
L’intervallo di riferimento {Product Data | Product ID} tra fogli diversi è simile al seguente:
Come indicato in precedenza, abbiamo inoltre incluso la nostra funzione INDEX(COLLECT()) in una formula IFERROR per mostrare "Nessuna corrispondenza trovata" se non viene trovato alcun ID prodotto corrispondente per la riga; la formula restituisce quindi i valori nella colonna Price (Prezzo) in questo modo:
Vantaggi:
- Consente di modificare l’ordine delle colonne o di eliminare le colonne inutilizzate senza interruzioni
- Può estrarre valori da colonne a sinistra o a destra dell’intervallo dei valori di ricerca
- In genere è più veloce della funzione VLOOKUP, ma può essere più lenta di INDEX/MATCH.
- Consente di utilizzare più criteri all’interno della formula COLLECT in modo che corrispondano su più colonne o creino criteri più complessi.
- Consente di fornire la seconda, la terza, ecc. corrispondenza, sostituendo l'elemento ", 1" alla fine della formula, invece di restituire sempre la prima corrispondenza.
Svantaggi:
- Richiede più di un riferimento incrociato tra fogli per i casi in cui esistono dati di riferimento in un foglio separato.
- Può essere più lenta rispetto all’utilizzo di INDEX/MATCH, soprattutto se si utilizzano più criteri.
Hai ancora bisogno di assistenza?
Usa il modello Formula Handbook (Manuale delle formule) per trovare altre risorse di assistenza e visualizzare oltre 100 formule, inclusi un glossario di ogni funzione che puoi allenarti a usare in tempo reale ed esempi di formule avanzate e utilizzate più di frequente.
Trova esempi di come altri clienti Smartsheet utilizzano questa funzione o chiedi informazioni sul tuo caso d’uso specifico nella community di Smartsheet.
Chiedi alla community