Note: The other languages of the website are Google-translated. Back to English
Accedi  \/ 
x
or
x
Registrati  \/ 
x

or

Come usare INDEX e MATCH insieme in Excel

Quando si lavora con le tabelle di Excel, è possibile trovare costantemente situazioni in cui è necessario cercare un valore. In questo tutorial, ti mostreremo come applicare la combinazione delle funzioni INDICE e CONFRONTA per effettuare ricerche orizzontali e verticali, ricerche bidirezionali, ricerche con distinzione tra maiuscole e minuscole e le ricerche che soddisfano più criteri.

Cosa fanno le funzioni INDICE e CONFRONTA in Excel?

Come usare insieme le funzioni INDICE e CONFRONTA


Cosa fanno le funzioni INDICE e CONFRONTA in Excel?

Prima di utilizzare le funzioni INDEX e MATCH, assicuriamoci di sapere come possono INDEX e MATCH aiutarci a cercare prima i valori.

Utilizzo della funzione INDICE in Excel

I INDICE la funzione in Excel restituisce il valore in una determinata posizione in un intervallo specifico. La sintassi della funzione INDICE è la seguente:

=INDEX(array, row_num, [column_num])
  • schieramento (obbligatorio) si riferisce all'intervallo da cui si desidera restituire il valore.
  • numero_riga (obbligatorio, a meno che non sia presente column_num) si riferisce al numero di riga dell'array.
  • numero_colonna (facoltativo, ma obbligatorio se row_num è omesso) si riferisce al numero di colonna dell'array.

Ad esempio, per sapere il punteggio dell'esame finale di Jeff, il 6° studente della lista, puoi usare la funzione INDICE in questo modo:

=INDICE(E2:E11, 6) >>> ritorna 60

corrispondenza indice excel 01

√ Nota: la gamma E2: E11 è dove è elencato l'esame finale, mentre il numero 6 trova il punteggio dell'esame del 6esimo studente.

Qui facciamo un piccolo test. Per la formula =INDICE(B2:E2,3), che valore restituirà? ---Sì, tornerà Cina, le 3rd valore nell'intervallo indicato.

Ora dovremmo sapere che la funzione INDICE può funzionare perfettamente con intervalli orizzontali o verticali. Ma cosa succede se ne abbiamo bisogno per restituire un valore in un intervallo più ampio con più righe e colonne? Bene, in questo caso, dovremmo applicare sia un numero di riga che un numero di colonna. Ad esempio, per scoprirlo il paese da cui viene Emily con INDEX, possiamo individuare il valore con un numero di riga di 8 e un numero di colonna di 3 nelle celle da B2 a E11 in questo modo:

=INDICE(B2:E11,8,3) >>> ritorna Cina

corrispondenza indice excel 02

Secondo gli esempi sopra, sulla funzione INDICE in Excel, dovresti sapere che:

  • La funzione INDICE può funzionare con intervalli verticali e orizzontali.
  • La funzione INDICE non fa distinzione tra maiuscole e minuscole.
  • Il numero di riga precede il numero di colonna (se sono necessari entrambi i numeri) nella formula INDICE.

Tuttavia, per un database davvero grande con più righe e colonne, non è certamente conveniente per noi applicare la formula con un numero esatto di riga e di colonna. E questo è quando dovremmo combinare l'uso della funzione MATCH.

Ora, impariamo prima le basi della funzione CONFRONTA.


Utilizzo della funzione CONFRONTA in Excel

La funzione CONFRONTA in Excel restituisce un valore numerico, la posizione di un elemento specifico nell'intervallo specificato. La sintassi della funzione CONFRONTA è la seguente:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_array (obbligatorio) si riferisce all'intervallo di celle in cui si desidera che MATCH cerchi.
  • match_type (opzionale), 1, 0 or -1:
  • 1(predefinito), CONFRONTA troverà il valore più grande minore o uguale a valore di ricerca. I valori in lookup_array devono essere inseriti in ordine crescente.
  • 0, MATCH troverà il primo valore che è esattamente uguale a valore di ricerca. I valori in lookup_array può essere in qualsiasi ordine. (Nei casi in cui il tipo di corrispondenza è impostato su 0, è possibile utilizzare caratteri jolly.)
  • -1, MATCH troverà il valore più piccolo maggiore o uguale a valore di ricerca. I valori in lookup_array devono essere posti in ordine decrescente.

Ad esempio, per sapere la posizione di Vera nella lista dei nomi, puoi usare la formula MATCH in questo modo:

=CONFRONTA("vera",C2:C11,0) >>> ritorna 4

corrispondenza indice excel 03

√ Nota: la funzione CONFRONTA non fa distinzione tra maiuscole e minuscole. Il risultato “4” indica che il nome “Vera” si trova nella 4a posizione dell'elenco. Lo "0" nella formula è il tipo di corrispondenza che troverà il primo valore nell'array di ricerca che corrisponde esattamente al valore di ricerca "Vera".

Sapere la posizione del punteggio “96” nella riga da B2 a E2, puoi usare MATCH in questo modo:

=CONFRONTA(96,LA2:MI2,0) >>> ritorna 4

corrispondenza indice excel 04

☞ Cose che dovremmo sapere sulla funzione CONFRONTA in Excel:

  • La funzione CONFRONTA restituisce la posizione del valore di ricerca nella matrice di ricerca, non il valore stesso.
  • La funzione CONFRONTA restituisce la prima corrispondenza in caso di duplicati.
  • Proprio come la funzione INDICE, la funzione CONFRONTA può funzionare anche con intervalli verticali e orizzontali.
  • Anche MATCH non fa distinzione tra maiuscole e minuscole.
  • Se il valore di ricerca della formula CONFRONTA è in forma di testo, racchiuderlo tra virgolette.

Ora che conosciamo gli usi di base delle funzioni INDICE e CONFRONTA in Excel, rimbocchiamoci le maniche e prepariamoci a combinare le due funzioni.


Come usare insieme le funzioni INDICE e CONFRONTA

In questa parte parleremo di diverse circostanze per utilizzare le funzioni INDICE e CONFRONTA per soddisfare esigenze diverse.

Esempio per combinare INDEX e MATCH

Si prega di vedere l'esempio seguente per capire come possiamo combinare le funzioni INDICE e CONFRONTA:

Ad esempio, per sapere Il punteggio dell'esame finale di Evelyn, dovremmo usare la formula:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> ritorna 90

corrispondenza indice excel 05

Bene, poiché la formula può sembrare complicata, esaminiamone ogni parte.

corrispondenza indice excel 06

Come puoi vedere sopra, il grande INDICE la formula contiene tre argomenti:

  • schieramento: A2: D11 dice a INDEX di restituire il valore corrispondente dalle celle attraverso Da A2 a D11.
  • numero_riga: CONFRONTA("evelyn",B2:B11,0) dice a INDEX la riga esatta del valore.
  • Riguardo alla formula MATCH, possiamo spiegarla come: restituire la posizione del primo valore che è esattamente uguale a "evelyn" nelle celle da B2 a B11 in un valore numerico, che è 5.
  • numero_colonna: MATCH("esame finale",A1:D1,0) dice a INDEX la colonna esatta del valore.
  • Per quanto riguarda la formula MATCH, possiamo spiegarla come: restituire la posizione del primo valore che è esattamente uguale a "esame finale" nelle celle da A1 a D1 in un valore numerico, che è 4.

Quindi, puoi vedere la grande formula semplice come quella che abbiamo mostrato di seguito:

= INDICE (A2: D11,5,4)

Nell'esempio, abbiamo utilizzato valori codificati, "evelyn" e "esame finale". Tuttavia, in una formula così grande, non vogliamo valori hardcoded poiché dovremo cambiarli ogni volta che cercheremo qualcosa di nuovo. In tali circostanze, possiamo utilizzare i riferimenti di cella per rendere dinamica la formula in questo modo:

= INDICE (A2: D11,INCONTRO(G2,B2:B11,0),INCONTRO(F3,A1:D1,0))

corrispondenza indice excel 07


INDEX e MATCH per applicare una ricerca a sinistra

Ora, supponiamo che tu debba conoscere la classe di Evelyn, come possiamo usare INDEX e MATCH per conoscere la risposta? Se hai prestato attenzione, dovresti notare che la colonna della classe si trova sul lato sinistro della colonna del nome e che va oltre la capacità di un'altra potente funzione di ricerca di Excel, VLOOKUP.

In effetti, la capacità di ricerca a sinistra sembra essere uno degli aspetti in cui la combinazione di INDICE e CONFRONTA è superiore a CERCA.VERT.

Sapere La classe di Evelyn, tutto ciò che devi fare è modificare il valore nella cella F3 in "Classe" e utilizzare la stessa formula mostrata sopra, le funzioni INDICE e CONFRONTA ti diranno subito la risposta:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> ritorna A

corrispondenza indice excel 08

Se hai installatoguidato Kutools for Excel, un addin professionale di Excel sviluppato dal nostro team, puoi anche applicare una ricerca a sinistra per i valori specificati con il suo CERCA da destra a sinistra funzionalità con pochi clic. Per implementare la funzione, vai su Kutools scheda nel tuo excel, trova il Formula gruppo e fare clic su CERCA da destra a sinistra nell'elenco a discesa di Super CERCA. Vedrai una finestra di dialogo pop-up come questa:

corrispondenza indice excel 09

Fare clic qui per passaggi concreti per applicare la funzione di ricerca a sinistra con Kutools per Excel.


INDEX e MATCH per applicare una ricerca bidirezionale

Ora, sei in grado di creare la formula di combinazione INDICE e CONFRONTA con valori di ricerca dinamici per applicare ricerche bidirezionali? Esercitiamoci creando formule nelle celle G3, G4 e G5 come mostrato di seguito:

corrispondenza indice excel 10

Ecco le risposte:

Cella G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Cella G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Cella G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Nota: dopo aver applicato le formule, puoi facilmente ottenere le informazioni di qualsiasi studente modificando il nome nella cella G2.


INDEX e MATCH per applicare una ricerca con distinzione tra maiuscole e minuscole

Dagli esempi precedenti, sappiamo che le funzioni INDICE e CONFRONTA non fanno distinzione tra maiuscole e minuscole. Tuttavia, nei casi in cui hai bisogno della tua formula per distinguere i caratteri maiuscoli e minuscoli, puoi aggiungere ESATTO funzione per le tue formule in questo modo:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Nota: questa è una formula di matrice che richiede di inserire con CTRL + MAIUSC + INVIO. Verrà quindi visualizzato un paio di parentesi graffe nella barra della formula.

Ad esempio, per sapere Il punteggio dell'esame di JIMMY, usa le funzioni in questo modo:

corrispondenza indice excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> ritorna 86

Oppure puoi usare i riferimenti di cella:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> ritorna 86
Nota: non dimenticare di entrare con CTRL + MAIUSC + INVIO.


INDEX e MATCH per applicare una ricerca con più criteri

Quando si ha a che fare con un database di grandi dimensioni con diverse colonne e didascalie di riga, è sempre difficile trovare qualcosa che soddisfi più condizioni. In questo caso, vedere la formula seguente per cercare più criteri:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Nota: questa è una formula di matrice che richiede di entrare con CTRL + MAIUSC + INVIO. Verrà quindi visualizzato un paio di parentesi graffe nella barra della formula.

Ad esempio, per trovare il punteggio dell'esame finale di Coco della classe A che viene dall'India, la formula è la seguente:

corrispondenza indice excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> ritorna 88
√ Nota: non dimenticare di entrare con CTRL + MAIUSC + INVIO.

Bene, cosa succede se ti dimentichi costantemente di usare? CTRL + MAIUSC + INVIO completare la formula in modo che la formula restituisca risultati errati? Qui abbiamo una formula più complessa, con la quale puoi semplicemente completare con una semplice Entra chiave:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Per lo stesso esempio sopra per trovare il punteggio dell'esame finale di Coco della classe A che viene dall'India, la formula che ha bisogno solo di un consueto Entra colpo è il seguente:

corrispondenza indice excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> ritorna 88

Qui non useremo valori hardcoded, poiché vorremo una formula universale nel caso con più criteri. Solo in questo modo, possiamo facilmente ottenere il risultato che vogliamo modificando i valori nelle celle G2, G3, G4 nell'esempio sopra.

Con una lunga Kutools for Excel's Funzione di ricerca multi-condizione, puoi cercare valori specifici con più criteri in pochi clic. Per implementare la funzione, vai su Kutools scheda nel tuo excel, trova il Formula gruppo e fare clic su Ricerca multi-condizione nell'elenco a discesa di Super CERCA. Vedrai quindi una finestra di dialogo pop-up come mostrato di seguito:

corrispondenza indice excel 14

Fare clic qui per passaggi concreti per applicare la funzione di ricerca a più condizioni con Kutools per Excel.


INDEX e MATCH per applicare una ricerca su più colonne

Se abbiamo un foglio di calcolo Excel con colonne diverse che condividono una didascalia come mostrato di seguito, come possiamo abbinare il nome di ogni studente con la sua classe con INDEX e MATCH?

corrispondenza indice excel 15

Qui, lascia che ti mostri il modo per completare l'attività con il nostro strumento professionale Kutools for Excel. Con i suoi Assistente alle formule, puoi abbinare rapidamente gli studenti alle loro classi seguendo i passaggi mostrati di seguito:

1. Seleziona la cella di destinazione in cui desideri applicare la funzione.

2. Sotto il Kutools scheda, vai a formula Helper, fare clic su formula Helper nell'elenco a discesa.

corrispondenza indice excel 16

3. Scegliere Ricerca dal tipo di formula, quindi fare clic su Indice e corrispondenza su più colonne.

corrispondenza indice excel 17

4. a. Fare clic sul 1° icona di corrispondenza indice excelpulsante a destra di Lookup_col per selezionare le celle da cui si desidera restituire un valore, ad esempio i nomi delle classi. (Puoi selezionare solo una singola colonna o riga qui.)
    B. Fare clic sul 2° icona di corrispondenza indice excelpulsante a destra di Table_rng per selezionare le celle in modo che corrispondano ai valori nel selezionato Lookup_col, ovvero i nomi degli studenti.
    C. Fare clic sul 3° icona di corrispondenza indice excelpulsante a destra di Valore di ricerca per selezionare la cella da cercare, ovvero il nome dello studente che si vuole associare alla sua classe.

corrispondenza indice excel 18

5. Clic Ok, vedrai il nome della classe di Jimmy mostrato nella cella di destinazione.

corrispondenza indice excel 19

6. Ora puoi trascinare la maniglia di riempimento verso il basso per riempire le classi di altri studenti.

corrispondenza indice excel 20

Fai clic per scaricare Kutools per Excel per una prova gratuita di 30 giorni.



  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne e conservazione dei dati; Contenuto delle celle divise; Combina righe duplicate e somma / media... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Formule preferite e di inserimento rapido, Intervalli, grafici e immagini; Crittografa celle con password; Crea mailing list e invia email ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Raggruppamento tabelle pivot per numero della settimana, giorno della settimana e altro ... Mostra celle sbloccate e bloccate da diversi colori; Evidenzia le celle che hanno formula / nome...
scheda kte 201905
  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
fondo officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.