Come ottenere più valori da stessi criteri in Microsoft Excel 2010
In questo articolo, impareremo come ottenere più valori dagli stessi criteri in Microsoft Excel 2010.
È facile cercare un valore con una chiave univoca in una tabella. Possiamo semplicemente usare CERCA.VERT. Ma quando non hai quella colonna univoca nei tuoi dati e devi cercare in più colonne per trovare una corrispondenza con un valore, CERCA.VERT non aiuta.
Quindi per cercare un valore in una tabella con più criteri useremo la formula INDICE-CONFRONTA-INDICE.
Formula generica per ricerca con criteri multipli
|
=INDEX(lookup_range,MATCH(1,INDEX((criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0,1),0)) |
_lookup_range: _ È l’intervallo da cui si desidera recuperare il valore.
_Criteria1, Criteria2, Criteria N: _ Questi sono i criteri che si desidera far corrispondere in range1, range2 e Range N. È possibile avere fino a 270 criteri – coppie di intervalli.
_Range1, range2, rangeN: _ Questi sono gli intervalli in cui corrisponderai ai tuoi rispettivi criteri.
Come funzionerà? Vediamo … ===== INDICE e CONFRONTA con criteri multipli Esempio Qui ho una tabella di dati. Voglio estrarre il nome del cliente utilizzando la data di prenotazione, il generatore e l’area. Quindi qui ho tre criteri e un intervallo di ricerca.
Scrivi questa formula nella cella I4 e premi invio.
|
=INDEX(E2:E16,MATCH(1,INDEXI1=A2:A16)(I2=B2:B16)(I3=C2:C16),0,1),0 |
Come funziona:
Sappiamo già come INDICE e funzione MATCH funzionano in EXCEL, quindi non lo spiegherò qui. Parleremo del trucco che abbiamo usato qui.
(I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16): La parte principale è questa. Ogni parte di questa istruzione restituisce un array di true false.
Quando i valori booleani vengono moltiplicati, restituiscono un array di 0 e 1.
La moltiplicazione funziona come operatore AND. Hense quando tutti i valori sono veri solo allora restituisce 1 else 0 (I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16) Tutto questo restituirà [width = “100%”, cols = “100 % “,]
|
\{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}* \{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}* \{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} |
Che si tradurrà in
|
\{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDICE ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): La funzione INDICE restituirà lo stesso array (\ {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) alla funzione MATCH come matrice di ricerca.
MATCH (1, INDEX ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): la funzione MATCH cercherà 1 nella matrice \ {0; 0; 0; 0 ; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. E restituirà il numero di indice del primo 1 trovato nell’array. Che è 8 qui.
INDEX (E2: E16, MATCH (1, INDEX I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1), 0:
Infine, INDEX restituirà il valore dall’intervallo dato (E2: E16) all’indice trovato (8).
Semplice? Scusa, non potrei renderlo più semplice.
Array Solution Se puoi premere CTRL + MAIUSC + INVIO di conseguenza puoi eliminare la funzione INDICE interna Scrivi questa formula e premi CTRL + MAIUSC INVIO.
|
=INDEX(E2:E16,MATCH(1,(I1=A2:A16)(I2=B2:B16)(I3=C2:C16),0)) |
Formula di matrice generica per ricerca di criteri multipli
|
=INDEX(lookup_range,MATCH(1,(criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0)) |
La formula funziona allo stesso modo come spiegazione sopra.
Spero che questo articolo su Come ottenere più valori dagli stessi criteri in Microsoft Excel 2010. Trova altri articoli sull’estrazione di valori e relative formule di Excel qui. Se ti sono piaciuti i nostri blog, condividilo con il tuo amici su Facebook. Puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected].
Articoli correlati:
link: / lookup-formas-use-vlookup-from-two-or-more-lookup-tables [Use VLOOKUP from two or more lookup-tables] | Per cercare da più tabelle possiamo adottare un approccio SE.ERRORE. Per cercare da più tabelle, utilizza l’errore come opzione per la tabella successiva. Un altro metodo può essere un approccio If.
link: / lookup-formas-how-to-do-case-sensitive-lookup-in-excel [Come eseguire la ricerca case sensitive in Excel] | la funzione CERCA.VERT di Excel non fa distinzione tra maiuscole e minuscole e restituirà il primo valore abbinato dall’elenco. INDEX-MATCH non fa eccezione, ma può essere modificato per renderlo sensibile al maiuscolo / minuscolo. Vediamo come … `link: / lookup-formas-lookup-spesso-testo-con-criteri-in-excel [Ricerca di testo che appare frequentemente con criteri in Excel]” | La ricerca appare più frequentemente nel testo in un intervallo che utilizziamo INDEX-MATCH con la funzione MODE. Ecco il metodo.
Articoli popolari:
link: / tips-if-condition-in-excel [Come utilizzare la funzione IF in Excel]: L’istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è TRUE o restituisce un altro valore specifico se FALSE .
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT in Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da intervalli diversi e lenzuola.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
link: / tips-countif-in-microsoft-excel [Come usare la funzione CONTA.SE in Excel]: Conta i valori con le condizioni usando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.