Questo è un altro esempio di velocità della formula. Di 'che devi fare 12 colonne di CERCA.VERT. Puoi renderlo più veloce utilizzando una funzione MATCH e 12 INDEX.
Nella figura seguente, dovrai eseguire 12 funzioni CERCA.VERT per ogni numero di conto. CERCA.VERT è potente, ma richiede molto tempo per eseguire i calcoli.
Inoltre, la formula deve essere modificata in ogni cella durante la copia. Il terzo argomento deve cambiare da 2 a 3 per febbraio, quindi 4 per marzo e così via.
Una soluzione alternativa consiste nell'aggiungere una riga con i numeri di colonna. Quindi, il terzo argomento di CERCA.VERT può puntare a questa riga. Almeno puoi copiare la stessa formula da B4 e incollarla in C4: M4 prima di copiare l'intero set.
Ma qui c'è un approccio molto più veloce. Aggiungi una nuova colonna B con Dove? come titolo. La colonna B contiene una funzione MATCH. Questa funzione è molto simile a CERCA.VERT: stai cercando il valore in A4 nella colonna P4: P227. Lo 0 alla fine è come il Falso alla fine di CERCA.VERT. Specifica che desideri una corrispondenza esatta. Ecco la grande differenza: MATCH restituisce dove si trova il valore. La risposta di 208 dice che A308 è la 208a cella nell'intervallo P4: P227. Da una prospettiva temporale di ricalcolo, CORRISPONDENZA e CERCA.VERT sono quasi uguali.
Posso sentire cosa stai pensando. “A che serve sapere dove si trova qualcosa? Non ho mai avuto un manager che mi ha chiamato e mi ha chiesto: "In che riga è quel credito?" "
Mentre gli esseri umani chiedono raramente in quale riga si trova qualcosa, la funzione INDICE può usare quella posizione. La formula seguente indica a Excel di restituire il 208esimo articolo da Q4: Q227.
Mentre copi questa formula, la matrice di valori si sposta nella tabella di ricerca. Per ogni riga, stai eseguendo una funzione MATCH e 12 INDEX. La funzione INDICE è incredibilmente veloce rispetto a CERCA.VERT. L'intero set di formule calcolerà l'85% più velocemente di 12 colonne di CERCA.VERT.
Guarda un video
- Di 'che devi fare 12 colonne di CERCA.VERT
- Utilizzare con attenzione un singolo segno di dollaro prima della colonna del valore di ricerca
- Usa con attenzione quattro simboli di dollaro per la tabella di ricerca
- Stai ancora codificando l'argomento della terza colonna.
- Una soluzione comune consiste nell'aggiungere una riga di celle helper con il numero di colonna.
- Un'altra soluzione meno efficiente consiste nell'usare COLONNA (B2) all'interno della formula CERCA.VERT.
- Tuttavia, eseguire 12 CERCA.VERT per ogni riga è molto inefficiente
- Invece, aggiungi una colonna helper con un'intestazione WHERE ed esegui una singola corrispondenza.
- La PARTITA richiede tanto tempo quanto il CERCA.VERT per gennaio.
- È quindi possibile utilizzare 12 funzioni INDICE. Questi sono incredibilmente veloci rispetto a CERCA.VERT.
- L'INDICE punterà a una singola colonna di risposte con $ prima delle righe.
- L'INDICE punterà alla colonna helper con un $ prima della colonna.
Trascrizione del video
Impara Excel dal podcast, episodio 2028 - Sostituzione di molti CERCA.VERT con una PARTITA!
Fai clic sulla "i" nell'angolo in alto a destra per accedere alla playlist, trasmetterò in podcast l'intero libro!
Ehi, bentornato al netcast, sono Bill Jelen! È un problema classico, dobbiamo fare CERCA.VERT una volta al mese, giusto? E puoi stare incredibilmente attento qui a premere F4 3 volte per bloccarlo nella colonna, quindi premere F4 una volta che il blocco l'intera riga. Ma quando arrivi a questo punto, il, 2, FALSE che 2 è hard-coded, e mentre lo copi, dovrai modificare il 2 in 3, giusto? Ora, un modo inefficiente per farlo, un modo che non mi piace è usare la colonna di B1. La colonna B1 è ovviamente 2, ma mentre la copi, vedi che cambierà nella colonna C1, che è 3, ma pensaci, questo è costantemente il calcolo del numero di colonna più e più volte. Quindi quello che vedo fare le persone e perché, sai, preferisco più delle colonne, è che lo trascineremo tenendo premuto Ctrl,mettiamo i numeri 2-13 lassù in una cella helper, e poi, quando arriviamo a questo punto, andiamo su e specifichiamo quel numero di colonna. Premere F4 2 volte per bloccarlo sulla riga,, FALSE e così via. Ma anche con quel metodo, CERCA.VERT è incredibilmente inefficiente, perché deve cercare tra tutti questi elementi qui finché non trova A308 e questa è la figura B4. Quando poi si sposta in C4, si dimentica di essere andato a guardare, e ricomincia da capo, va bene. Quindi hai una delle funzioni più lente in tutto Excel, CERCA.VERT, FALSO che viene eseguito più e più volte per lo stesso elemento.perché deve cercare qui tutti questi elementi finché non trova A308 e questa è la cifra B4. Quando poi si sposta in C4, si dimentica di essere andato a guardare, e ricomincia da capo, va bene. Quindi hai una delle funzioni più lente in tutto Excel, CERCA.VERT, FALSO che viene eseguito più e più volte per lo stesso elemento.perché deve cercare qui tutti questi elementi finché non trova A308 e questa è la cifra B4. Quando poi si sposta in C4, si dimentica di essere andato a guardare, e ricomincia tutto da capo, va bene. Quindi hai una delle funzioni più lente in tutto Excel, CERCA.VERT, FALSO che viene eseguito più e più volte per lo stesso elemento.
Quindi ecco il modo molto, molto più veloce da percorrere, inseriremo una colonna helper, e questa colonna helper la chiamo Dove? Come in dove diavolo è A308? Useremo a = MATCH, cercheremo A308 nella prima riga della tabella, premeremo F4 lì,, 0 per una corrispondenza esatta, va bene, ci dice che "Hey, guarda qui, è nella riga, 6, come fantastico è quello? " Ma mentre copiamo, vedete, è sempre in posti diversi. Va bene, ora questa partita dura quanto il CERCA.VERT di gennaio, anche lì sono morti, ma ecco la cosa incredibile. Da lì non dobbiamo mai eseguire un CERCA.VERT per il resto della riga, potremmo semplicemente fare = INDICE, INDICE dice "Ecco una serie di risposte". Andrò alle celle di gennaio e qui premerò con molta attenzione F4 2 volte, quindi lo blocco su 4: 227,ma la Q può cambiare mentre mi muovo. Virgola, e poi vuole sapere quale riga, beh, questa sarà la risposta in B4, premerò F4 3 volte per ottenere il $ prima della B, va bene, copiarlo.
Questa formula, queste formule INDICE, queste 12 avverranno in meno del tempo necessario per eseguire il CERCA.VERT di febbraio, va bene. Se mettiamo il timer di Charles Williams su questo, l'intera cosa calcolerà circa il 14% del tempo di 12 CERCA.VERT. Il tuo manager non vuole vedere dove? Bene, nascondi quella colonna, tutto continua a funzionare, va bene, questo è un bel modo per accelerare i 12 mesi o le 52 settimane di CERCA.VERT. Bene, questo suggerimento e tanti altri suggerimenti sono in questo libro. Fai clic sulla "i" nell'angolo in alto a destra, puoi acquistare il libro, $ 10 e-book, $ 25 per il libro stampato, va bene.
Quindi oggi abbiamo avuto un problema in cui 12 colonne di CERCA.VERT, puoi inserire con cura $ in, ma poi quel terzo argomento deve ancora essere codificato. Potresti usare la colonna (B2), non ne sono un fan, perché ci sono centinaia di righe * 12 colonne dove lo calcola ancora e ancora. Usa semplicemente una cella helper di seguito, metti i numeri 2-12 e indica quello, è ancora inefficiente, però, perché CERCA.VERT dopo aver calcolato gennaio, deve ricominciare dall'inizio per febbraio. Quindi consiglio di aggiungere una colonna con un'intestazione di "Dove?" e facendo una singola PARTITA lì. Quella CORRISPONDENZA dura quanto il CERCA.VERT per gennaio, ma le 12 funzioni INDICE impiegheranno meno tempo rispetto al CERCA.VERT per febbraio e hai ridotto un sacco di tempo. Ancora una volta, fai attenzione con $ nella funzione INDICE in entrambi i posti, uno appena prima delle righe,e l'altra prima delle colonne, un riferimento misto in entrambe.
Ehi, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2028.xlsx
