Ricerca con criteri multipli tramite INDICE e CONFRONTA
Quando si lavora con un database di grandi dimensioni in un foglio di calcolo Excel, ricco di numerose colonne e intestazioni di riga, trovare dati che soddisfino criteri multipli può diventare un’impresa complessa. In questi casi, la soluzione ideale è utilizzare una formula matriciale con le funzioni INDICE e CONFRONTA.
Come si esegue una ricerca con criteri multipli?
Per individuare il prodotto che è bianco, di taglia media e costa $18, come mostrato nell’immagine sopra, puoi sfruttare la logica booleana per generare un array di 1 e 0 che indichi le righe corrispondenti a tutti i criteri. La funzione CONFRONTA individuerà quindi la posizione della prima riga che soddisfa tali condizioni, e INDICE recupererà l’ID prodotto corrispondente sulla stessa riga.
Sintassi generica
=INDEX()return_range,MATCH(1,())criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Nota: questa è una formula matriciale che richiede di confermare con Ctrl+Maiusc+Invio.
- intervallo_di_restituzione: L'intervallo da cui si desidera che la formula combinata restituisca l'ID prodotto. In questo caso, si riferisce all'intervallo contenente gli ID prodotto.
- valore_criterio: I criteri utilizzati per individuare la posizione dell'ID prodotto. Si riferiscono ai valori presenti nelle celle H3, H5 e H6.
- intervallo_criteri: Gli intervalli corrispondenti in cui sono elencati i valori_criterio. Si riferiscono agli intervalli relativi a colore, taglia e prezzo.
- tipo_corrispondenza 0: Impone alla funzione CONFRONTA di trovare il primo valore esattamente uguale al valore_ricerca.
Per trovare il prodotto che è biancoe di taglia mediacon un prezzo di $18, copiare o inserire la formula seguente nella cella H8 e premere Ctrl+Maiusc+Invioper ottenere il risultato:
=INDICE()B5:B10,CONFRONTA(1,())«Bianco»=C5:C10)*(«Media»=D5:D10)*(18=E5:E10),0))
Oppure, utilizzare Un riferimento di cella per rendere la formula dinamica:
=INDICE()B5:B10,CONFRONTA(1,())h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
Spiegazione della formula
=INDEX()B5:B10,MATCH(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10):La formula confronta il colore nella cella h3 con tutti i colori nell'intervallo C5:C10; confronta la taglia in H5 con tutte le taglie in D5:D10; confronta il prezzo in H6 con tutti i prezzi in E5:E10. Il risultato iniziale è simile a questo:
{VERO;FALSO;VERO;FALSO;VERO;FALSO}*{FALSO;FALSO;VERO;VERO;VERO;FALSO}*{FALSO;FALSO;FALSO;VERO;VERO;FALSO}.
La moltiplicazione trasforma i valori VERO e FALSO in 1 e 0:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Dopo la moltiplicazione, si ottiene un singolo array simile a questo:
{0;0;0;0;1;0}. - CONFRONTA(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=CONFRONTA(1,)Il tipo_corrispondenza 0 indica alla funzione CONFRONTA di cercare una corrispondenza esatta. La funzione restituisce quindi la posizione di 1 nell'array {0;0;0;0;1;0}, che è 5.
- INDICE()B5:B10,CONFRONTA(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDICE(B5:B10La funzione INDICE restituisce il 5° valore nell'intervallo degli ID prodotto B5:B10, che è 30005.
Funzioni correlate
La funzione INDICE di Excel restituisce il Valore visualizzato in base a una posizione specificata da un intervallo o da una matrice.
La funzione CONFRONTA di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa del valore.
Formule correlate
Ricerca del valore più vicino con criteri multipli
In alcuni casi potrebbe essere necessario trovare il valore più vicino o approssimativo in base a più criteri. Combinando le funzioni INDICE, CONFRONTA e SE, puoi ottenere rapidamente il risultato desiderato in Excel.
Corrispondenza approssimativa con INDICE e CONFRONTA
A volte è necessario trovare corrispondenze approssimative in Excel per valutare le prestazioni dei dipendenti, assegnare voti agli studenti, calcolare le spese postali in base al peso e molto altro. In questo tutorial scoprirai come sfruttare al meglio le funzioni INDICE e CONFRONTA per ottenere esattamente i risultati che cerchi.
Intervallo di valore di ricerca da un altro foglio di lavoro o cartella di lavoro
Se sai già come usare la funzione CERCA.VERT per cercare valori all’interno di un foglio di lavoro, estendere la ricerca a un altro foglio o a un’altra cartella non sarà affatto un problema. Questo tutorial ti mostra come cercare valori in un altro foglio di lavoro in Excel.
I Migliori Strumenti per la Produttività in Office
Kutools per Excel – Aiuta te a distinguerti dalla folla
Kutools per Excel Vanta oltre 300 funzionalità,assicurandoti che ciò di cui hai bisogno sia sempre a portata di clic...
Office Tab – Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Passa tra decine di documenti aperti in un secondo!
- Riduci centinaia di clic del mouse ogni giorno e dì addio al “mouse hand”.
- Aumenta la tua produttività del 50 % visualizzando e modificando più documenti contemporaneamente.
- Porta l’efficienza delle schede in Office (incluso Excel), proprio come in Chrome, Edge e Firefox.