INDICE e CONFRONTA su più colonne
Per cercare un valore eseguendo la corrispondenza su più colonne, una formula di matrice basata su INDICE e PARTITA funzioni che incorpora MATR.PRODOTTO, TRASPORRE e COLONNA ti farò un favore.
Come cercare un valore facendo corrispondere più colonne?
Per compilare il classe corrispondente di ogni studente come mostrato nella tabella sopra, dove le informazioni sono elencate su più colonne, puoi prima usare il trucco della funzione MMULT, TRANSPOSE e COLUMN per produrre un array di matrici. Quindi la funzione MATCH ti darà la posizione del tuo valore di ricerca, che verrà inviato a INDEX per recuperare il valore che stai cercando nell'array.
Sintassi generica
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Nota: questa è una formula di matrice che richiede di inserire con Ctrl + spostamento + Entra.
- intervallo_ritorno: L'intervallo da cui si desidera che la formula restituisca le informazioni sulla classe. Qui si riferisce alla gamma di classi.
- valore di ricerca: Il valore utilizzato dalla formula per trovare le informazioni sulla classe corrispondente. Qui si riferisce al nome dato.
- ricerca_array: L'intervallo di celle in cui valore di ricerca È elencato; L'intervallo con i valori da confrontare con il valore di ricerca. Qui si riferisce all'intervallo di nomi.
- tipo_corrispondenza 0: Forza MATCH a trovare il primo valore che è esattamente uguale a valore di ricerca.
Per trovare il classe di Jimmy, per favore copia o inserisci la formula sotto nella cella H5 e premi Ctrl + spostamento + Entra per ottenere il risultato:
= INDICE ($ B $ 5: $ B $ 7,(CONFRONTA(1,MMULT(--($ C $ 5: $ E $ 7=G5),TRASPOSI(COLONNA($ C $ 5: $ E $ 7)^0)),0)))
√ Nota: i simboli del dollaro ($) sopra indicano riferimenti assoluti, il che significa che il nome e gli intervalli di classe nella formula non cambieranno quando sposti o copi la formula in altre celle. Nota che non dovresti aggiungere i segni del dollaro al riferimento di cella che rappresenta il valore di ricerca, poiché vuoi che sia relativo quando lo copi in altre celle. Dopo aver inserito la formula, trascina verso il basso il quadratino di riempimento per applicare la formula alle celle sottostanti.
Spiegazione della formula
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=V5): Questo segmento controlla ogni valore nell'intervallo $ C $ 5: $ E $ 7 se sono uguali al valore nella cella G5 e genera un array VERO e FALSO come questo:
{VERO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO}.
Il doppio negativo convertirà quindi VERO e FALSO in 1 e 0 per produrre un array come questo:
{1,0,0; 0,0,0; 0,0,0}. - COLONNA($C$5:$E$7): La funzione COLUMN restituisce i numeri di colonna per l'intervallo $ C $ 5: $ E $ 7 in un array come questo: {} 3,4,5.
- TRASPORRE(COLONNA($C$5:$E$7)^0) = TRASPORRE({} 3,4,5^0): Dopo aver aumentato la potenza a 0, tutti i numeri nell'array {3,4,5} verranno convertiti in 1: {1,1,1}. La funzione TRANSPOSE quindi converte l'array di colonne in un array di righe come questo: {1; 1; 1}.
- MOLTO (--($C$5:$E$7=V5),TRASPORRE(COLONNA($C$5:$E$7)^0)) = MOLTO ({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): La funzione MMULT restituisce il prodotto matrice dei due array in questo modo: {1; 0; 0}.
- CONFRONTA(1,MOLTO (--($C$5:$E$7=V5),TRASPORRE(COLONNA($C$5:$E$7)^0)), 0) = CONFRONTA(1,{1; 0; 0}, 0): Il match_type 0 forza la funzione CONFRONTA a restituire la posizione della prima corrispondenza di 1 nell'array {1; 0; 0}, Che ha 1.
- INDICE($ B $ 5: $ B $ 7,(CONFRONTA(1,MOLTO (--($C$5:$E$7=V5),TRASPORRE(COLONNA($C$5:$E$7)^0)), 0))) = INDICE($ B $ 5: $ B $ 7,1): La funzione INDICE restituisce il 1st valore nell'intervallo di classe $ B $ 5: $ B $ 7, Che ha A.
Per cercare facilmente un valore facendo corrispondere più colonne, puoi anche utilizzare il nostro componente aggiuntivo professionale di Excel Kutools per Excel. Vedi le istruzioni qui per portare a termine la missione.
Funzioni correlate
La funzione INDICE di Excel restituisce il valore visualizzato in base a una determinata posizione 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.
La funzione MMULT di Excel restituisce il prodotto matrice di due matrici. Il risultato dell'array ha lo stesso numero di righe di array1 e lo stesso numero di colonne di array2.
La funzione TRANSPOSE di Excel ruota l'orientamento di un intervallo o di una matrice. Ad esempio, può ruotare una tabella disposta orizzontalmente in righe in verticalmente in colonne o viceversa.
La funzione COLONNA restituisce il numero di colonna in cui viene visualizzata la formula o restituisce il numero di colonna di un determinato riferimento. Ad esempio, la formula =COLUMN(BD) restituisce 56.
Formule correlate
Ricerca a criteri multipli con INDEX e MATCH
Quando si ha a che fare con un grande database in un foglio di calcolo Excel con diverse colonne e didascalie di riga, è sempre difficile trovare qualcosa che soddisfi più criteri. In questo caso, puoi utilizzare una formula di matrice con le funzioni INDICE e CONFRONTA.
Ricerca bidirezionale con INDEX e MATCH
Per cercare qualcosa su righe e colonne in Excel, o diciamo di cercare un valore all'intersezione della riga e della colonna specifiche, possiamo utilizzare l'aiuto delle funzioni INDICE e CONFRONTA.
Cerca il valore di corrispondenza più vicino con più criteri
In alcuni casi, potrebbe essere necessario cercare il valore di corrispondenza più vicino o approssimativo in base a più di un criterio. Con la combinazione delle funzioni INDICE, CONFRONTA e SE, puoi farlo rapidamente in Excel.
I migliori strumenti per la produttività in ufficio
Kutools per Excel: ti aiuta a distinguerti dalla folla
Vorresti completare il tuo lavoro quotidiano in modo rapido e perfetto? Kutools per Excel offre 300 potenti funzionalità avanzate (Combina cartelle di lavoro, somma per colore, contenuto di celle divise, converti data e così via ...) e risparmia l'80% di tempo per te.
- Progettato per 1500 scenari di lavoro, ti aiuta a risolvere l'80% dei problemi di Excel.
- Riduci migliaia di clic della tastiera e del mouse ogni giorno, allevia i tuoi occhi e le tue mani stanche.
- Diventa un esperto di Excel in 3 minuti. Non è più necessario ricordare formule dolorose e codici VBA.
- Prova gratuita illimitata di 30 giorni. Garanzia di rimborso di 60 giorni. Aggiornamento gratuito e supporto per 2 anni.
Scheda Office: abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Un secondo per passare da una dozzina di documenti aperti all'altra!
- Riduci ogni giorno centinaia di clic del mouse, dì addio alla mano del mouse.
- Aumenta la produttività del 50% durante la visualizzazione e la modifica di più documenti.
- Porta schede efficienti in Office (incluso Excel), proprio come Chrome, Firefox e il nuovo Internet Explorer.
