Se hai letto i suggerimenti di Excel per un po ', hai sempre trovato qualcuno che parla dell'utilizzo delle funzioni INDICE () e CONFRONTA () di Excel invece di CERCA.VERT di Excel. Parlando per me stesso, era sempre troppo difficile provare a padroneggiare DUE nuove funzioni contemporaneamente. Ma è un bel trucco. Dammi cinque minuti e cercherò di spiegarlo in un inglese semplice.
La seconda revisione di 30 CERCA.VERT
Supponi di avere una tabella dei record dei dipendenti. La prima colonna è un numero di dipendente e le colonne rimanenti sono vari dati sul dipendente. Ogni volta che si dispone di un numero di dipendente nel foglio di lavoro, è possibile utilizzare CERCA.VERT per restituire un dato specifico sul dipendente. La sintassi è CERCA.VERT (valore, intervallo dati, n. Col., FALSO). Dice a Excel: "Vai all'intervallo di dati. Trova una riga che ha (valore) nella prima colonna dell'intervallo di dati. Restituisci il (col no.) Valore esimo da quella riga. Una volta capito, è molto semplice e potente.
Il problema
Un giorno, hai una situazione in cui hai il nome del dipendente, ma hai bisogno del numero del dipendente. Nell'immagine seguente, hai un nome in A10 e devi trovare il numero del dipendente in B10.
Quando il campo della chiave si trova a destra dei dati che desideri recuperare, CERCA.VERT non funzionerà. Se solo CERCA.VERT accettasse -1 come numero di colonna, non ci sarebbero problemi. Ma non è così. Una soluzione comune è inserire temporaneamente una nuova colonna A, copiare la colonna dei nomi nella nuova colonna A, popolare con CERCA.VERT, Incolla valori speciali, quindi eliminare la colonna temporanea A. I professionisti di Excel possono probabilmente fare questa mossa mentre dormono.
Ti suggerisco di accettare la sfida e di provare a utilizzare questo metodo a passo singolo. Sì, dovrai attaccare la formula al muro per alcune settimane, ma l'hai fatto anche con CERCA.VERT molto tempo fa, vero?
Penso che il motivo per cui questo è così difficile è che stai usando due funzioni che probabilmente non hai mai usato prima. Quindi, fammelo scomporre in due parti.
Innanzitutto, c'è la funzione INDICE (). Questa è una funzione dal nome orribile. Quando qualcuno dice "indice", non evoca nulla nella mia mente che sia simile a ciò che fa questa funzione. L'indice richiede tre argomenti.
=INDEX(data range, row number, column number)
In inglese, Excel passa all'intervallo di dati e restituisce il valore nell'intersezione della (numero riga) esima riga e (numero colonna) esima colonna. Ehi, pensaci: è piuttosto semplice, giusto? =INDEX($A$2:$C$6,4,2)ti darà il valore in B5.
Applicando INDEX () per il nostro problema, si può capire che per restituire il numero dei dipendenti della gamma, si può usare questo: =INDEX($A$2:$A$6,?,1). In realtà, questo pezzo sembra così banale da sembrare inutile. Ma, quando sostituisci il punto interrogativo con una funzione MATCH (), hai la soluzione.
Ecco la sintassi:
=MATCH(Value, Single-column data range, FALSE)
Indica a Excel: "Cerca nell'intervallo di dati e dimmi il numero di riga relativo in cui trovi una corrispondenza per (dati). Quindi, per trovare quale riga ha il dipendente in A10, dovresti usare =MATCH(A10,$B$2:$B$6,FALSE). Sì, questo è più complesso di Indice , ma dovrebbe essere proprio il vicolo dei professionisti di CERCA.VERT Se A10 contiene "Miller, Bob", questa PARTITA restituirà che si trova nella 3a riga dell'intervallo B2: B6.
Ecco fatto - la funzione MATCH () dice alla funzione Index in quale riga cercare - hai finito. Prendi la funzione Indice, sostituisci il nostro punto interrogativo con la funzione CONFRONTA e ora puoi eseguire l'equivalente di CERCA.VERT quando il campo chiave non si trova nella colonna di sinistra. Ecco la funzione da usare:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
La nota adesiva sul muro lo mostra effettivamente come due linee. Per prima cosa ho scritto la spiegazione per MATCH (). Di seguito ho scritto la spiegazione per INDICE (). Ho quindi disegnato una forma a imbuto tra i due per indicare che la funzione MATCH () scende al 2 ° argomento della funzione INDEX ().
Le prime volte che ho dovuto fare uno di questi, sono stato tentato di sbattere una nuova colonna temporanea A lì dentro, ma invece ho provato il dolore di farlo in questo modo. È più veloce e richiede meno manipolazioni. Quindi, la prossima volta che desideri inserire un numero negativo nella funzione CERCA.VERT, prova questa strana combinazione di INDICE e CONFRONTA per risolvere i tuoi problemi.
