Questo tutorial ti insegnerà come utilizzare la combinazione INDICE & CONFRONTA per eseguire ricerche in Excel e Fogli Google.
INDEX & MATCH, la coppia perfetta
Diamo un'occhiata più da vicino ad alcuni dei modi in cui puoi combinare le funzioni INDICE e CONFRONTA. La funzione CONFRONTA è progettata per restituire la posizione relativa di un elemento all'interno di una matrice, mentre la funzione INDICE può recuperare un elemento da una matrice data una posizione specifica. Questa sinergia tra i due consente loro di eseguire quasi tutti i tipi di ricerca di cui potresti aver bisogno.
La combinazione INDICE/CONFRONTA è stata storicamente utilizzata in sostituzione della funzione CERCA.VERT. Uno dei motivi principali è la possibilità di eseguire una ricerca a sinistra (vedere la sezione successiva).
Nota: la nuova funzione XLOOKUP ora può eseguire ricerche a sinistra.
Guarda a sinistra
Usiamo questa tabella delle statistiche di basket:
Vogliamo trovare il giocatore # di Bob. Poiché il Player # è a sinistra della colonna del nome, non possiamo usare un VLOOKUP.
Invece, potremmo fare una richiesta MATCH di base per calcolare la riga di Bob
=CONFRONTA(H2, B2:B5, 0)
Questo cercherà una corrispondenza esatta della parola "Bob", e quindi la nostra funzione restituirà il numero 2, poiché "Bob" è nel 2ns posizione.
Successivamente possiamo usare la funzione INDEX per restituire il Player #, corrispondente a una riga. Per ora, inseriamo manualmente "2" nella funzione:
=INDICE(A2:A5, 2)
Qui, INDEX farà riferimento a A3, poiché questo è il 2ns cella all'interno dell'intervallo A2:A5 e restituire il risultato di 42. Per il nostro obiettivo generale, possiamo quindi combinare questi due in:
=INDICE(A2:A5, CONFRONTA(H2, B2:B5, 0))
Il vantaggio qui è che siamo stati in grado di restituire un risultato da una colonna a sinistra di dove stavamo cercando.
Ricerca a due dimensioni
Diamo un'occhiata alla nostra tabella di prima:
Questa volta, tuttavia, vogliamo recuperare una statistica specifica. Abbiamo detto che vogliamo cercare i rimbalzi nella cella H1. Anziché dover scrivere diverse istruzioni IF per determinare da quale colonna ottenere il risultato, è possibile utilizzare nuovamente una funzione CONFRONTA. La funzione INDICE consente di specificare il valore della riga e il valore della colonna. Aggiungeremo qui un'altra funzione CONFRONTA per determinare quale colonna vogliamo. Sembrerà
=CONFRONTA (H1, A1: E1, 0)
La nostra cella in H1 è un menu a discesa che ci consente di scegliere quale categoria vogliamo cercare, quindi il nostro MATCH determina a quale colonna della tabella appartiene. Inseriamo questo nuovo bit nella nostra formula precedente. Nota che dobbiamo modificare il primo argomento in modo che sia a due dimensioni, poiché non vogliamo più solo un risultato dalla colonna A.
=INDICE(A2:E5, CONFRONTA(H2, B2:B5, 0), CONFRONTA(H1, A1:E1, 0))
Nel nostro esempio, vogliamo trovare Rimbalzi per Charlie. La nostra formula valuterà questo in questo modo:
=INDICE(A2:E5, MATCH("Charlie", B2:B5, 0), MATCH("Rimbalzi", A1:E1, 0)) =INDICE(A2:E5, 3, 4) =D4 =6
Ora abbiamo creato una configurazione flessibile che consente all'utente di recuperare qualsiasi valore desiderato dalla nostra tabella senza dover scrivere più formule o istruzioni IF ramificate.
Sezioni multiple
Non è usato spesso, ma INDEX ha un quinto argomento che può essere dato per determinare quale la zona all'interno dell'argomento uno da usare. Ciò significa che abbiamo bisogno di un modo per passare più aree nel primo argomento. Puoi farlo usando una serie aggiuntiva di parentesi. Questo esempio illustrerà come recuperare i risultati da diverse tabelle su un foglio di lavoro utilizzando INDEX.
Ecco il layout che utilizzeremo. Abbiamo statistiche per tre diversi quarti di gioco.
Nelle celle H1: H3, abbiamo creato elenchi a discesa di convalida dei dati per le nostre varie scelte. Il menu a discesa per il trimestre proviene da J2:J4. Lo useremo per un'altra istruzione MATCH, per determinare quale area usare. La nostra formula in H4 sarà simile a questa:
=INDICE((A3:E6, A10:E13, A17:E20), CONFRONTA(H2, B3:B6, 0), CONFRONTA(H1, A2:E2, 0), CONFRONTA(H3, J2:J4, 0))
Abbiamo già discusso di come funzionano le due funzioni MATCH interne, quindi concentriamoci sul primo e sull'ultimo argomento:
=INDICE((A3:E6, A10:E13, A17:E20),… , CONFRONTA(H3, J2:J4, 0))
Abbiamo dato alla funzione INDEX più array nel primo argomento racchiudendoli tutti tra parentesi. L'altro modo per farlo è usare Formule - Definisci nome. Potresti definire un nome chiamato "MyTables" con una definizione di
=INDICE(MyTable,MATCH(H2,Table1347[Name],0),MATCH(H1,Table1347[#Headers],0),MATCH(H3,J2:J4,0))
Torniamo all'intera dichiarazione. Le nostre varie funzioni CONFRONTA diranno alla funzione INDICE esattamente dove cercare. Innanzitutto, determineremo che "Charlie" è il 3rd riga. Quindi, vogliamo "Rimbalzi", che è il 4ns colonna. Infine, abbiamo determinato che vogliamo il risultato di 2ns tavolo. La formula valuterà attraverso questo in questo modo:
=INDICE((A3:E6, A10:E13, A17:E20), CONFRONTA(H2, B3:B6, 0), CONFRONTA(H1, A2:E2, 0), CONFRONTA(H3, J2:J4, 0)) =INDICE((A3:E6, A10:E13, A17:E20), 3, 4, 2) =INDICE(A10:E13, 3, 4) =D13 =14
Come accennato all'inizio di questo esempio, sei limitato ad avere le tabelle nello stesso foglio di lavoro. Se riesci a scrivere i modi corretti per dire al tuo INDEX da quale riga, colonna e/o area vuoi recuperare i dati, INDEX ti servirà molto bene.
Fogli Google -INDICE E CORRISPONDENZA
Tutti gli esempi precedenti funzionano esattamente allo stesso modo in Fogli Google come in Excel.
