Si tiene una hoja de trabajo grande, muchas VLOOKUP pueden comenzar a ralentizar las cosas. ¿Tiene una hoja de trabajo lenta debido a VLOOKUP? Estoy hablando de una hoja de trabajo que requiere 40 segundos o 4 minutos para calcular. En el artículo de hoy, una fórmula asombrosa con dos BUSCARV utilizando la búsqueda de rango resolverá el problema.
VLOOKUP es una función relativamente cara. Cuando busca una coincidencia exacta, Excel tiene que revisar la tabla de búsqueda una fila a la vez.
El libro de trabajo que estoy usando hoy está haciendo 7000 VLOOKUP en una tabla de 116,000 elementos. En una máquina realmente rápida de 64 bits con 8 núcleos, el tiempo de recalc es de 3,01 segundos.
Una forma de mejorar BUSCARV es mover los artículos más vendidos a la parte superior de la tabla de búsqueda. Obtenga un informe de los 100 artículos más vendidos y mueva esos artículos al principio de la lista. La clasificación por popularidad mejora el tiempo de recalc a 0,369 segundos. Esto es ocho veces más rápido que el primer resultado.
Pero hay una forma de acelerar aún más las cosas. A medida que está construyendo su BUSCARV, cuando llega al cuarto argumento para elegir Falso, hay otra opción que casi nunca se usa. Excel dice que "Verdadero" hace una "coincidencia aproximada". Esto no es del todo correcto. Si el equipo de Excel fuera honesto, explicaría que Verdadero “proporciona una respuesta correcta la mayor parte del tiempo, pero otras veces, sin previo aviso, vamos a introducir la respuesta incorrecta. Espero que no le importe reiterar sus números a la Comisión de Bolsa y Valores ".
Seguro, hay un momento adecuado para usar True. Vea este artículo. Pero sería realmente malo usar True cuando intenta hacer una coincidencia exacta.
Si intenta utilizar True para una coincidencia exacta, obtendrá la respuesta correcta la mayor parte del tiempo. Pero cuando el elemento que busca no está en la tabla, Excel le dará el valor de una fila diferente. Ésta es la parte que hace que "True" sea algo que no sea un comienzo para todos en Contabilidad. Cerrar nunca es correcto en Contabilidad.
Nota
Aprendí el siguiente truco de Charles Williams. Es el mayor experto mundial en velocidad de hojas de trabajo. Si tiene un libro de trabajo lento, contrate a Charles Williams para medio día de consultoría. Puede encontrar los cuellos de botella y hacer que su hoja de trabajo sea más rápida. Encuentre a Charles en http://www.decisionmodels.com.
Mientras que yo y todos los contadores rechazamos el argumento "Verdadero" de BUSCARV debido a la imprevisibilidad, Charles Williams defiende Verdad. Señala que el verdadero es mucho más rápido que el falso. Cientos de veces más rápido. Admite que a veces se obtiene una respuesta incorrecta. Pero tiene una forma de lidiar con las respuestas incorrectas.
Charles realmente quiere que hagas dos VLOOKUP. Primero, haga una VLOOKUP y devuelva la columna 1 de la tabla. Vea si el resultado es lo que buscaba en primer lugar. Si ese resultado coincide, entonces sabrá que es seguro hacer VLOOKUP real para devolver alguna otra columna de la tabla:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
A primera vista, esto parece una locura. Para utilizar el método de Charles, debe hacer el doble de BUSCARV. Pero, cuando calcula el tiempo de cálculo para este método, es 35 veces más rápido que el VLOOKUP normal.
Tenga en cuenta que, si bien la mayoría de las tablas de búsqueda no tienen que ordenarse, cuando utiliza True como cuarto argumento, la tabla debe ordenarse. Para una discusión de 7 minutos sobre cómo la versión verdadera de BUSCARV salta a través de la tabla de búsqueda, consulte http://mrx.cl/TrueVLOOKUP.
Gracias a Charles Williams por enseñarme esta función y a Scott St. Amant por nominarla para uno de los 40 mejores consejos.
Ver video
- VLOOKUP cuando se usa con False es una función lenta
- Ordenar los datos AZ no acelera la función
- Ordenar por popularidad podría acelerar la función
- Cambiar a BUSCARV con Verdadero es más rápido, pero informará la respuesta incorrecta si no se encuentra el elemento
- Para mitigar el problema, haga una BUSCARV (A2, Tabla, 1, Verdadero) para ver si el resultado es A2 primero
- 14000 VLOOKUP (verdadero) y 7000 IF se ejecutan más rápido que 7000 VLOOKUP (falso)
Transcripción generada automáticamente
- Aprenda Excel de Podcast
- episodio 2031 vlookup más rápido estoy
- Podcasting todos los consejos de este libro.
- haga clic en la I en la esquina superior derecha
- para llegar a la lista de vigilancia
- Bienvenidos de nuevo al sr. elenco hutnik
- Soy Bill Jelen, hice esto, esto
- video antes de que sea uno de mis favoritos
- trucos si tienes el look si tu
- tener vlookup star tomando 30 40 50
- segundos cuatro minutos sabes algo
- te va a encantar este video si tu
- vlookup stick un segundo simplemente haga clic en Siguiente
- y pase al siguiente video. Tengo un
- vlookup aquí está mirando en una tabla
- de 115.000 elementos con 7.000 vlookup, por lo que
- vamos a usar algo de Charles Williams
- del código rápido de Excel para ver cuánto tiempo
- se necesita para hacer esto vlookup bien cuatro
- punto cero nueve segundos ese es el
- vlookup típico con coma falsa en el
- final y todo esto surgió por mucho tiempo
- Hace mucho tiempo fui atrapado por un tipo en
- Twitter que dijo que sería mejor si
- ordenarías tu tabla de búsqueda a
- enviando dije que no, eso no es cierto en absoluto
- no importa si vamos a
- enviando o descendiendo o completamente
- aleatorio el vlookup solo tiene que ir a buscar
- de un artículo a otro y así cuando
- ordenar la tabla ver que realmente se necesita
- más cuatro punto ocho cuatro segundos así que
- sabes que no es cierto que ordenar el
- la mesa lo hará ir más rápido pero
- realmente lo que podría hacerlo funcionar
- más rápido si de alguna manera pudieras ordenar por
- popularidad si pudieras obtener lo mejor
- vender artículos en la parte superior de la lista
- incluso tu conoces tus cincuenta mejores, sabes
- cuáles son sus 50 artículos más vendidos
- llevarlos a la parte superior de la lista y
- mira que por segundos baja a 0.36
- segundos una mejora diez veces mayor en el tiempo
- usando ordenar por popularidad ahora hey algunos
- Hace años tuve la suerte de ser
- invitado a Amsterdam para presentar en un
- Excel cumbre allí y no es como
- la mayoría de mis seminarios donde solo soy yo
- justo había dos pistas, así que la habitación a
- y la habitación B y yo estaba en la habitación
- hablando de vlookups y más en la habitación
- adivina quién estaba sentado en esa habitación
- Charles Williams estaba bien y Charles
- aquí está
- su nombre se menciona a través del
- pared para que se acerque a mirarlo
- mira mi pequeña demostración allí donde voy
- de cuatro segundos a 0.36 segundos él
- se me acerca después dice que apuesto
- estás bastante feliz con eso
- mejora
- Yo digo que si, es una carpa llena
- mejora ahora Charles Charles tiene la
- servicio de Excel rápido nuestro modelo de decisión
- los modelos de decisión limitados en los que estamos
- medio día analizará tu cuaderno
- y dice que hace cien
- Veces más rápido, a la derecha, encontrará el
- cuellos de botella Annette y Charles Charles
- comes from he says look at comma false
- that you and your accountant friends are
- doing it is the slowest thing in Excel
- if you would do a comma true it's a
- thousand times faster and then Charles
- says this next Clause is if it doesn't
- really matter he says now sometimes it's
- wrong oh wait Charles you don't
- understand an accountant sometimes is
- wrong is a non-starter we do not accept
- sometimes it's wrong and and the time
- that it's wrong the comma true when
- you're doing a comma true is we go look
- for a P 3 2 2 1 1 and it's not found
- they're gonna give you the item just
- less alright and they're not gonna tell
- you we couldn't find it they're just
- gonna they're just gonna give you Adam
- just less that that's unacceptable and
- Charles says well here's what we could
- do imagine if you did a vlookup of 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
- aumentar, podría cambiar a vlookup
- con verdadero pero informará el mal
- responda si los elementos no se encuentran, así que estamos
- en realidad voy a hacer dos búsquedas de vlookups
- dos en la columna uno en la mesa y
- mira si lo que obtenemos es un dos si
- es seguro ir a hacer el vlookup en
- la columna común para tener un
- si la declaración dice que no se encuentra bien
- oh hola gracias a Charles Williams por
- enseñándome ese truco asombroso y
- gracias a ti por pasar a ver
- la próxima vez para otro lanzamiento de red de
- MrExcel
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2031.xlsm
