Se si dispone di un foglio di lavoro di grandi dimensioni, molti CERCA.VERT possono iniziare a rallentare le cose. Hai un foglio di lavoro lento a causa di CERCA.VERT? Sto parlando di un foglio di lavoro che richiede 40 secondi o 4 minuti per il calcolo. Nell'articolo di oggi una formula straordinaria con due CERCA.VERT utilizzando la ricerca per intervallo risolverà il problema.
CERCA.VERT è una funzione relativamente costosa. Quando si cerca una corrispondenza esatta, Excel deve esaminare la tabella di ricerca una riga alla volta.
La cartella di lavoro che sto usando oggi sta eseguendo 7000 VLOOKUP in una tabella di 116.000 elementi. Su una macchina a 64 bit molto veloce con 8 core, il tempo di ricalcolo è di 3,01 secondi.
Un modo per migliorare CERCA.VERT è spostare gli elementi più venduti nella parte superiore della tabella di ricerca. Ottieni un report dei primi 100 articoli più venduti e spostali in cima all'elenco. L'ordinamento per popolarità migliora il tempo di ricalcolo a 0,369 secondi. Questo è otto volte più veloce del primo risultato.
Ma c'è un modo per accelerare ancora di più le cose. Mentre stai costruendo il tuo CERCA.VERT, quando arrivi al quarto argomento per scegliere Falso, c'è un'altra opzione che non viene quasi mai utilizzata. Excel dice "Vero" fa una "corrispondenza approssimativa". Questo non è affatto corretto. Se il team di Excel fosse onesto, spiegherebbe che True “fornisce una risposta corretta la maggior parte delle volte, ma altre volte, senza alcun preavviso, inseriremo la risposta sbagliata. Spero che non ti dispiaccia riaffermare i tuoi numeri alla Securities and Exchange Commission. "
Certo, c'è un momento opportuno per usare True. Vedi questo articolo. Ma sarebbe davvero brutto usare True quando stai cercando di fare una corrispondenza esatta.
Se provi a usare True per una corrispondenza esatta, otterrai la risposta giusta la maggior parte delle volte. Ma quando l'elemento che stai cercando non è nella tabella, Excel ti darà il valore da una riga diversa. Questa è la parte che rende "Vero" un non-principiante per tutti in Contabilità. La chiusura non è mai corretta in Contabilità.
Nota
Ho imparato il seguente trucco da Charles Williams. È il massimo esperto al mondo sulla velocità dei fogli di lavoro. Se hai una cartella di lavoro lenta, assumi Charles Williams per mezza giornata di consulenza. Può trovare i colli di bottiglia e rendere più veloce il tuo foglio di lavoro. Trova Charles su http://www.decisionmodels.com.
Mentre io e tutti i contabili rifiutiamo l'argomento "Vero" di CERCA.VERT a causa dell'imprevedibilità, Charles Williams sostiene per Vero. Sottolinea che il Vero è molto più veloce del Falso. Centinaia di volte più veloce. Ammette che a volte ottieni la risposta sbagliata. Ma ha un modo per affrontare le risposte sbagliate.
Charles in realtà vuole che tu esegua due VLOOKUP. Innanzitutto, esegui un CERCA.VERT e restituisci la colonna 1 dalla tabella. Verifica se il risultato è quello che stavi cercando in primo luogo. Se quel risultato corrisponde, allora sai che è sicuro fare il vero CERCA.VERT per restituire qualche altra colonna dalla tabella:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
A prima vista, questo sembra folle. Per utilizzare il metodo di Charles, devi eseguire il doppio dei CERCA.VERT. Tuttavia, quando si calcola il tempo di calcolo per questo metodo, è 35 volte più veloce del normale CERCA.VERT.
Notare che mentre la maggior parte delle tabelle di ricerca non deve essere ordinata, quando si utilizza True come quarto argomento, la tabella deve essere ordinata. Per una discussione di 7 minuti su come la versione True di CERCA.VERT passa attraverso la tabella di ricerca, vedere http://mrx.cl/TrueVLOOKUP.
Grazie a Charles Williams per avermi insegnato questa funzione ea Scott St. Amant per averla nominata per un suggerimento tra i primi 40.
Guarda un video
- CERCA.VERT se utilizzato con False è una funzione lenta
- Ordinare i dati AZ non velocizza la funzione
- L'ordinamento per popolarità potrebbe accelerare la funzione
- Il passaggio a CERCA.VERT con True è più veloce, ma se l'elemento non viene trovato segnalerà la risposta sbagliata
- Per mitigare il problema, esegui un CERCA.VERT (A2, Tabella, 1, Vero) per vedere se il risultato è prima A2
- 14000 CERCA.VERT (Vero) e 7000 SE sono più veloci di 7000 CERCA.VERT (Falso)
Trascrizione generata automaticamente
- Impara Excel da Podcast
- episodio 2031 vlookup più veloce I'm
- podcasting tutti i suggerimenti in questo libro
- fare clic sulla I nell'angolo in alto a destra
- per accedere alla watch list
- ciao bentornato al mr. cast di hutnik
- Sono Bill Jelen, ho fatto questo
- video prima che sia uno dei miei preferiti
- trucchi se hai lo sguardo se tu
- avere vlookup star prendendo 30 40 50
- secondi quattro minuti sai tutto
- adorerai questo video se il tuo
- vlookup stick un secondo, basta fare clic su Avanti
- e vai al video successivo II ho un file
- vlookup qui sta esaminando una tabella
- di 115.000 elementi che eseguono 7000 vlookup così
- useremo un po 'di Charles Williams
- dal codice Excel veloce per vedere quanto tempo
- per fare questo vlookup va bene quattro
- punto zero nove secondi che è il
- tipico vlookup con virgola false in
- fine e tutto questo è venuto fuori perché lungo
- molto tempo fa sono stato adescato da un tizio
- Twitter che ha detto che sarebbe stato meglio se
- ordineresti la tua tabella di ricerca a
- inviando ho detto di no che non è affatto vero
- non importa se andiamo a
- invio o discendente o completamente
- random il vlookup deve solo andare a cercare
- da articolo a articolo a articolo e così quando noi
- ordinare la tabella vedere che ci vuole effettivamente
- più lungo quattro virgola otto quattro secondi così
- sai che non è vero che l'ordinamento del file
- il tavolo lo farà andare più veloce ma
- davvero la cosa che potrebbe farlo andare
- più velocemente se in qualche modo potessi ordinare
- popolarità se potessi ottenere il meglio
- vendita di articoli in cima alla lista
- anche tu conosci i tuoi primi cinquanta, lo sai
- quali sono i tuoi 50 articoli più venduti
- portali in cima alla lista e
- guarda che per i secondi scende a 0,36
- secondi un miglioramento di dieci volte nel tempo
- usando l'ordinamento per popolarità ora hey alcuni
- anni fa ho avuto la fortuna di esserlo
- invitato ad Amsterdam per presentare ad un
- Vertice di Excel lì e non è come
- la maggior parte dei miei seminari dove sono solo io
- proprio c'erano due tracce quindi stanza a
- e la stanza B e io eravamo nella stanza be
- parlando di visualizzazioni e oltre in camera
- indovina chi era seduto in quella stanza
- stava bene Charles Williams e Charles
- ecco
- il suo nome viene menzionato attraverso il
- muro così lui viene a guardarlo lui
- guarda la mia piccola demo lì dove vado
- da quattro secondi a 0,36 secondi he
- viene da me dopo, dice che scommetto
- sei abbastanza contento di questo
- miglioramento
- Dico sì, è una tenda piena
- miglioramento ora Charles Charles ha il
- servizio di fast Excel il nostro modello decisionale
- i modelli decisionali ci limitano
- mezza giornata analizzerà la tua cartella di lavoro
- e lui afferma di farne cento
- volte più velocemente a destra troverà il file
- colli di bottiglia Annette e Charles Charles
- viene da lui dice guarda la virgola falsa
- che tu e i tuoi amici contabili siete
- farlo è la cosa più lenta in Excel
- se vuoi fare una virgola true è un file
- mille volte più veloce e poi Charles
- dice che questa clausola successiva è se non lo fa
- importa davvero, dice ora a volte lo è
- sbagliato oh aspetta Charles non lo fai
- capire un ragioniere a volte lo è
- sbagliato è un non partente che non accettiamo
- a volte è sbagliato e l'ora
- che è sbagliata la virgola true quando
- stai facendo una virgola vero è andiamo a cercare
- per un P 3 2 2 1 1 e non è stato trovato
- ti daranno solo l'oggetto
- meno bene e non lo diranno
- non siamo riusciti a trovarlo sono solo
- ti daranno solo Adam
- solo meno quello che è inaccettabile e
- Charles dice bene, ecco cosa potremmo
- immagina se hai fatto una vlookup di P 3 2
- 2 1 1 into just column G ask for the
- first column comma true and see if what
- you get back is what you were looking
- for if what you get back is what you're
- looking for then you know it's safe to
- go to the second vlookup if it's not
- what you were looking for then you have
- an if statement there that says not
- found alright so we do a vlookup of a2
- into the table comma 1 see if it's equal
- to 82 if it is then it's safe to go on
- and do the second vlookup otherwise they
- not found I said Charles do you realize
- I'm doing 7000 vlookups and now you're
- gonna be doing 7 14000 vlookups and 7000
- if statements I I said you really think
- this is gonna be faster sure I said well
- I'll bet you a pint alright so here we
- go remember the vlookup that all of us
- are doing with the comma falls 4.0 9
- seconds the sort by popularity which is
- kind of hard to do
- Oh point three seconds here we go here's
- Charles Williams are you ready look at
- that point zero four four one hundredths
- of a second from four seconds down to
- four one hundreds of a second imagine if
- you had a spreadsheet that was taking
- for 40 seconds to calculate and how much
- faster would be using this to vlookup
- method it's an amazing trick and yeah I
- guess I stole the trick and put it in
- the book although I put a great cartoon
- version of Charles Williams in there
- saying he's the fastest guy in all of
- Excel you can buy this tip and all of
- the other tips in this book click that I
- at the top right hand corner
- all right so recap vlookup when used
- with false it's a slow function sorting
- the data a disease does not speed up the
- function unless you sell a lot of things
- to begin with a and B sorting by
- popularity does it's about a tenfold
- aumentare potresti passare a vlookup
- con true ma segnalerà l'errore
- rispondere se gli elementi non sono stati trovati così siamo
- in realtà farò due ricerche di visualizzazioni a
- due nella colonna uno al tavolo e
- vedere se quello che otteniamo è un due se
- è sicuro andare a fare il vlookup in
- la colonna comune per avere altrimenti un file
- se dichiarazione dice non trovato tutto a posto
- oh hey grazie a Charles Williams per
- insegnandomi quel fantastico trucco e
- grazie a te per essere passato a vedere
- la prossima volta per un altro cast netto da
- MrExcel
Download file
Scarica il file di esempio qui: Podcast2031.xlsm
