Para la gran mayoría de usuarios de Excel, cuando les viene a la cabeza la palabra “filtrado de datos”, solo el filtro clásico habitual de la pestaña Datos – Filtro (Datos — Filtro):
Tal filtro es algo familiar, sin duda, y en la mayoría de los casos servirá. Sin embargo, hay situaciones en las que necesita filtrar por una gran cantidad de condiciones complejas en varias columnas a la vez. El filtro habitual aquí no es muy conveniente y quiero algo más potente. Tal herramienta podría ser filtro avanzado, sobre todo con un poco de “acabado con lima” (según la tradición).
Base
Para comenzar, inserte algunas líneas vacías sobre su tabla de datos y copie el encabezado de la tabla allí; este será un rango con condiciones (resaltado en amarillo para mayor claridad):
Debe haber al menos una línea vacía entre las celdas amarillas y la tabla original.
Es en las celdas amarillas donde debe ingresar los criterios (condiciones), según los cuales se realizará el filtrado. Por ejemplo, si necesita seleccionar plátanos en el "Auchan" de Moscú en el trimestre III, entonces las condiciones se verán así:
Para filtrar, seleccione cualquier celda en el rango con los datos de origen, abra la pestaña Datos Y haga clic en Adicionalmente (Datos — Avanzado). En la ventana que se abre ya debería estar automáticamente introducido un rango con datos y solo tendremos que especificar el rango de condiciones, es decir A1:I2:
Tenga en cuenta que el rango de condiciones no se puede asignar "con un margen", es decir, no puede seleccionar líneas amarillas vacías adicionales, porque Excel percibe una celda vacía en el rango de condiciones como la ausencia de un criterio, y todo vacío. línea como una solicitud para mostrar todos los datos indiscriminadamente.
Switch Copie el resultado a otra ubicación le permitirá filtrar la lista no allí mismo en esta hoja (como con un filtro normal), sino descargar las filas seleccionadas en otro rango, que luego deberá especificarse en el campo Poner el resultado en el rango. En este caso, no usamos esta función, dejamos Lista de filtros en su lugar y haga clic OK. Las filas seleccionadas se mostrarán en la hoja:
Agregar una macro
"Bueno, ¿dónde está la conveniencia aquí?" pregunta y acertarás. No solo necesita ingresar condiciones en las celdas amarillas con sus manos, sino también abrir un cuadro de diálogo, ingresar rangos allí, presionar OK. Triste, estoy de acuerdo! Pero “todo cambia cuando vienen ©” – ¡macros!
El trabajo con un filtro avanzado se puede acelerar y simplificar en gran medida utilizando una macro simple que ejecutará automáticamente el filtro avanzado cuando se ingresen las condiciones, es decir, cambiando cualquier celda amarilla. Haga clic derecho en la pestaña de la hoja actual y seleccione el comando Texto de origen (Código fuente). En la ventana que se abre copia y pega el siguiente código:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Reanudar siguiente ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Rango("A1").CurrentRegion End If End Sub Este procedimiento se ejecutará automáticamente cuando se cambie cualquier celda de la hoja de trabajo actual. Si la dirección de la celda modificada cae dentro del rango amarillo (A2:I5), esta macro elimina todos los filtros (si los hay) y vuelve a aplicar el filtro extendido a la tabla de datos de origen que comienza con A7, es decir, todo se filtrará instantáneamente, inmediatamente después de ingresar la siguiente condición:
Entonces todo es mucho mejor, ¿verdad? 🙂
Implementación de consultas complejas
Ahora que todo se filtra sobre la marcha, podemos profundizar un poco más en los matices y desmontar los mecanismos de consultas más complejas en el filtro avanzado. Además de ingresar coincidencias exactas, puede usar varios caracteres comodín (* y ?) y signos de desigualdad matemática en una variedad de condiciones para implementar una búsqueda aproximada. El caso del personaje no importa. Para mayor claridad, he resumido todas las opciones posibles en una tabla:
| Criterio | Resultado |
| gr* o gr | Todas las celdas que comienzan con GrIe Groído, Grmono, Granat etc. |
| = cebolla | todas las celdas exactamente y solo con la palabra Arco, es decir, coincidencia exacta |
| *liv* o *liv | celdas que contienen Liv cómo subrayar, es decir ОLivesa, Livep, ConformeLiv etc. |
| =p*v | palabras comenzando con П y terminando con В ie Пla primeraв, Пéterв etc. |
| como | palabras comenzando con А y además contiene СIe Аporсin, Аnanaс, Asai etc. |
| =*s | palabras terminadas en С |
| =???? | todas las celdas con texto de 4 caracteres (letras o números, espacios incluidos) |
| =m??????n | todas las celdas con texto de 8 caracteres comenzando con М y terminando con НIe Мandariн, Мansiedadн etc. |
| =*n??a | Todas las palabras que terminan con А, donde esta la cuarta letra desde el final НIe Mangaнikа, Conformeнozа etc. |
| >=e | todas las palabras que comienzan con Э, Ю or Я |
| <>*o* | todas las palabras que no contienen una letra О |
| <>*vich | todas las palabras excepto las que terminan en VIH (por ejemplo, filtrar mujeres por segundo nombre) |
| = | todas las celdas vacías |
| <> | todas las celdas no vacías |
| > = 5000 | todas las celdas con un valor mayor o igual a 5000 |
| 5 o =5 | todas las celdas con valor 5 |
| > = 3/18/2013 | todas las celdas con fecha posterior al 18 de marzo de 2013 (inclusive) |
Puntos sutiles:
- El signo * significa cualquier número de caracteres y ? – cualquier carácter.
- La lógica en el procesamiento de consultas numéricas y de texto es ligeramente diferente. Entonces, por ejemplo, una celda de condición con el número 5 no significa buscar todos los números que comienzan con cinco, pero una celda de condición con la letra B es igual a B*, es decir, buscará cualquier texto que comience con la letra B.
- Si la consulta de texto no comienza con el signo =, puede poner mentalmente * al final.
- Las fechas deben ingresarse en formato estadounidense mes día-año y mediante fracción (incluso si tiene Excel y configuración regional).
Conectores lógicos AND-OR
Las condiciones escritas en diferentes celdas, pero en la misma línea, se consideran interconectadas por un operador lógico И (Y):
Aquellos. filtrar plátanos para mí en el tercer trimestre, precisamente en Moscú y al mismo tiempo de Auchan.
Si necesita vincular condiciones con un operador lógico OR (OR), entonces solo necesitan ingresarse en diferentes líneas. Por ejemplo, si necesitamos encontrar todos los pedidos del gerente Volina para los melocotones de Moscú y todos los pedidos de cebollas en el tercer trimestre en Samara, esto se puede especificar en un rango de condiciones de la siguiente manera:
Si necesita imponer dos o más condiciones en una columna, simplemente puede duplicar el encabezado de la columna en el rango de criterios e ingresar el segundo, tercero, etc. debajo de él. términos. Entonces, por ejemplo, puede seleccionar todas las transacciones de marzo a mayo:
En general, después de “terminar con un archivo”, un filtro avanzado resulta ser una herramienta bastante decente, en algunos lugares no peor que un autofiltro clásico.
- Superfiltro en macros
- Qué son las macros, dónde y cómo insertar código de macro en Visual Basic
- Tablas inteligentes en Microsoft Excel