Uso de Filtro avanzado en Excel para resolver el problema de Mort. Aunque los filtros regulares se han vuelto más poderosos, todavía hay ocasiones en que el Filtro avanzado puede hacer algunos trucos que otros no pueden.
Ver video
- El filtro avanzado es más "avanzado" que el filtro normal porque:
- 1) Puede copiar a un nuevo rango
- 2) Puede crear criterios más complejos, como Campo 1 = A o Campo 2 = A
- 3) es rápido
- Mort está tratando de procesar 100K filas en VBA recorriendo registros o usando una matriz
- Siempre será más rápido usar las funciones integradas de Excel que escribir su propio código.
- Necesita un rango de entrada y luego un rango de criterios y / o un rango de salida
- Para el rango de entrada: una sola fila de encabezados sobre los datos
- Agregar una fila temporal para los títulos
- Para el rango de salida: una fila de encabezados para las columnas que desea extraer
- Para el rango de criterios: encabezados en la fila 1, valores que comienzan en la fila 2
- Complicación: las versiones anteriores de Excel no permitirían que el rango de salida esté en otra hoja
- Si está escribiendo una macro que podría ejecutarse en 2003, use un rango con nombre para el rango de entrada para eludir
Transcripción del video
Aprenda Excel de Podcast, Episodio 2060: Filtro avanzado de Excel
Oye, bienvenido de nuevo a Netcast, soy Bill Jelen. La pregunta de hoy enviada por Mort. Mort, tiene 100.000 filas de datos y está interesado en las Columnas A, B y D, donde la Columna C coincide con algún año en particular. Entonces quiere que una persona ingrese un año y luego obtenga las Columnas A, B y D. Y Mort tiene algo de VBA en el que está usando matrices para hacer esto y dije: "Espera un segundo, ya sabes, el filtro avanzado haría esto un mucho mejor." Muy bien, y ahora solo para revisar, volví, miré hacia atrás a través de mis videos. No he cubierto el filtro avanzado en mucho tiempo, así que deberíamos hablar de esto.
El filtro avanzado requiere un rango de entrada y luego al menos uno de estos: un rango de criterios o un rango de salida. Aunque hoy usaremos ambos. Muy bien, entonces el rango de entrada son tus datos y debes tener encabezados sobre los datos. Entonces, Mort no tiene encabezados, por lo que voy a insertar temporalmente una fila aquí y simplemente hacer como en el Campo 1. Mort sabe cuáles son sus datos y, por lo tanto, podría poner encabezados reales allí. Y no estamos usando nada llamado, estos datos en las Columnas E a la O, así que no tengo que agregar títulos allí, ¿de acuerdo? Así que ahora, de A1 a D, 100000 se convierte en mi rango de entrada. Y luego el rango de salida y el rango de criterios. Bueno, el rango de salida es solo una lista de los títulos que desea. Así que voy a poner el rango de salida aquí y no necesitamos el campo 3, así que 'Lo llevaré a un lado. Entonces, este rango aquí mismo, A1 a C1, se convierte en mi rango de salida que le dice a Excel qué campos quiero del rango de entrada. Y podrían estar en un orden diferente si quisiera reordenar las cosas, por ejemplo, si quiero el Campo 4 primero, y luego el Campo 1 y luego el Campo 2. Y nuevamente, estos serían títulos reales como el número de factura. No sé cómo son los datos de Mort.
Y luego, el rango de criterios es un título y el valor que desea. Entonces, digamos que estaba tratando de obtener algo en el año 2014. Este se convierte en el rango de criterios como ese. Muy bien, solo una advertencia aquí. Estoy en Excel 2016 y es posible hacer un filtro avanzado entre dos hojas en Excel 2016, pero si retrocede y no recuerdo cuál es, tal vez 2003, no estoy seguro. En algún momento del pasado, solía ser que no podía hacer un filtro avanzado de una hoja a otra, por lo que tendría que venir aquí y nombrar su rango de entrada. Tendrías que crear un nombre aquí. MyName o algo así, ¿de acuerdo? Y esa sería la forma en que podrías lograrlo, de acuerdo. No necesariamente en Excel 2016, pero de nuevo, yo 'No estoy seguro si Mort va a ejecutar esto en versiones anteriores de los datos.
Muy bien, de nuevo aquí en Datos, vamos a Filtro avanzado, de acuerdo. Y vamos a Copiar a otra ubicación que habilite nuestro rango de salida allí. Muy bien, entonces el rango de la lista, ¿dónde están los datos? Debido a que estoy en Excel 2016, voy a apuntar a los Datos, en lugar de usar el rango de nombres, así que ese es mi rango de entrada. El rango de criterios son esas celdas allí mismo, a donde vamos - salida, solo serán estas tres celdas allí. Y luego hacemos clic en Aceptar. ¡Muy bien y BAM! Así de rápido, rápido es. ¿Y si quisiéramos un año diferente? Si quisiéramos un año diferente, eliminaríamos los resultados, los pondríamos en 2015 y luego haríamos un filtro avanzado nuevamente. Copiar a otra ubicación, hacer clic en Aceptar y ahí están todos los registros de 2015. Velocidad del rayo.
Muy bien, aunque soy un fanático del filtro avanzado en Excel normal, era un gran fanático del filtro avanzado en VBA, está bien, porque VBA hace que el filtro avanzado sea realmente, realmente, realmente simple. Muy bien, entonces vamos a escribir un código aquí para Mort, asumiendo que los datos de Mort no tienen encabezados y vamos a tener que agregar temporalmente los encabezados, ¿de acuerdo? Entonces, cambiaré a VBA, Alt + F11 y vamos a ejecutar esto desde la hoja de trabajo que tiene los datos. Entonces: Dim WS As Worksheet, Set WS = ActiveSheet. Y luego, inserte la Fila 1 y simplemente agregue algunos encabezados: A, B, Año y D. Calcule cuántas filas de datos tenemos hoy y luego comience desde la celda A1 saliendo 4 columnas hasta la fila final, asígnele ser rango de entrada. Muy bien, y este es en realidad el código de Mort aquí mismo, donde pidió el InputBox,obtiene el año que quieren y luego pregunta qué año o cómo quieren nombrar la nueva hoja, está bien. Así que realmente insertará una hoja sobre la marcha y luego I- Dimensionará una nueva hoja, WSN, como ActiveSheet. Entonces sé que WS es la hoja original, WSN es la nueva hoja que se acaba de agregar. En la nueva hoja, ponga el rango de criterios de modo que debajo de la Columna E esté el encabezado que coincida con este encabezado aquí, y luego, la respuesta que nos dieron va en E2. El rango de salida serán mis otros tres encabezados: A, B y D. Y nuevamente, si tú o Mort los cambian a encabezados reales, probablemente sea mejor que A, B, D y tú también cámbielos a títulos reales, ¿de acuerdo? Así que todo esto es solo un poco de trabajo previo aquí. Esta impresionante línea de código hará todo el filtro avanzado. Entonces,desde InputRange hacemos un AdvancedFilter, vamos a copiar. Ese es nuestro filtro de elección en su lugar o copia. CriteriaRange es E1 a E2, CopyToRange es A a C. Valores únicos -No, queremos todos los valores. Muy bien, esa línea de código hace toda la magia de recorrer todos los registros o reemplaza recorrer todos los registros o hacer las matrices. Y luego terminamos, borraremos el rango de criterios y luego eliminaremos la Fila 1 en la hoja de trabajo original.Y luego terminamos, borraremos el rango de criterios y luego eliminaremos la Fila 1 en la hoja de trabajo original.Y luego terminamos, borraremos el rango de criterios y luego eliminaremos la Fila 1 en la hoja de trabajo original.
Bien, volvamos aquí a nuestros datos. Facilitaremos la ejecución de esto, por lo que: Insertar, una Forma y llamar a este Filtro, Inicio, Centro, Centro, Mayor, Mayor, Mayor, clic derecho, Asignar macro y asignarlo a MacroForMort. Muy bien, aquí vamos. Vamos a hacer una prueba. Vea que estamos en la hoja de datos, haga clic en Filtro, ¿qué año queremos? Queremos el 2015. ¿Cómo lo quiero llamar? Quiero llamarlo 2015, está bien. ¡Y BAM! Ahí está hecho. Así de rápido, así de rápido es esto.
Ahora, dado que los datos originales de Mort no tenían títulos, tal vez estos datos no deberían tener títulos. Así que vamos a Alt + F11, aquí mismo queremos borrar el rango de criterios. También haremos Rows (1) .Delete. Muy bien, la próxima vez que estemos en esto, se eliminarán esos encabezados. Y simplemente … En lugar de ejecutar todo rápidamente, echemos un vistazo aquí con el 2014. Así que seleccionaré una celda en los Datos, Alt + F11, y quiero correr hasta el punto en el que hacemos el filtro avanzado. Entonces podemos mirar y ver qué está haciendo toda la macro aquí. Entonces, haremos clic en Ejecutar, y quiero obtener 2014. 2014, está bien. Y entonces, presione F8, estamos a punto de hacer el filtro avanzado. Podemos volver a Excel aquí y ver qué sucedió.
Lo primero que sucedió. Ahora, lo primero que sucedió es que agregamos una nueva fila temporal con los títulos. Inserté esta hoja de trabajo, construí un rango de criterios con un encabezado y en qué año ingresaron, elegimos los campos que queremos hacer y luego de vuelta en VBA, ejecutaré la siguiente línea de códigos, eso es F8 que hace el filtro avanzado allí mismo . Es increíblemente rápido y verás que eso nos ha traído todos los récords. A partir de ahí, es solo un poco de limpieza, elimine esto, elimine esto. Volveré a los datos y eliminaré la Fila 1 y estaremos listos para comenzar. Así que dejaré correr el resto de eso, eliminaré ese punto de interrupción, ¿de acuerdo? Entonces está el VBA. Para mí, esta es la forma más rápida, la forma más rápida de hacerlo.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Muy bien, bueno, ahí lo tienes. Quiero agradecer a Mort por enviar esa pregunta. Quiero agradecerle por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2060.xlsm
