Filtro avanzado e algo de maxia

Para a gran maioría dos usuarios de Excel, cando a palabra "filtrado de datos" aparece na súa cabeza, só o filtro clásico habitual da pestana Datos - Filtro (Datos — Filtro):

Tal filtro é algo familiar, sen dúbida, e para a maioría dos casos servirá. Non obstante, hai situacións nas que precisa filtrar por un gran número de condicións complexas en varias columnas á vez. O filtro habitual aquí non é moi cómodo e quero algo máis potente. Tal ferramenta podería ser filtro avanzado, sobre todo cun pouco de “acabar con lima” (segundo a tradición).

Base

Para comezar, insira algunhas liñas baleiras enriba da súa táboa de datos e copie alí a cabeceira da táboa: este será un intervalo con condicións (resaltado en amarelo para que quede claro):

Debe haber polo menos unha liña baleira entre as celas amarelas e a táboa orixinal.

É nas celas amarelas onde cómpre introducir os criterios (condicións), segundo os cales se realizará o filtrado. Por exemplo, se necesitas seleccionar bananas no "Auchan" de Moscova no III trimestre, as condicións serán así:

Para filtrar, seleccione calquera cela do intervalo cos datos de orixe, abra a pestana Data E faga clic no botón Ademais (Datos — Avanzado). Na xanela que se abre xa debería introducirse automaticamente un intervalo con datos e só teremos que especificar o intervalo de condicións, é dicir, A1:I2:

Teña en conta que o intervalo de condicións non se pode asignar "cunha marxe", é dicir, non pode seleccionar liñas amarelas baleiras adicionais, porque unha cela baleira no intervalo de condicións é percibida por Excel como a ausencia dun criterio e unha cela baleira completa. liña como unha solicitude para mostrar todos os datos indistintamente.

interruptor Copia o resultado noutro lugar permitirache filtrar a lista non alí mesmo nesta folla (como ocorre cun filtro normal), senón descargar as filas seleccionadas noutro intervalo, que entón deberá especificarse no campo Pon o resultado no rango. Neste caso, non usamos esta función, saímos Lista de filtros no lugar e prema OK. As filas seleccionadas mostraranse na folla:

Engadindo unha macro

"Ben, onde está a conveniencia aquí?" preguntas e terás razón. Non só cómpre introducir condicións nas celas amarelas coas mans, senón tamén abrir unha caixa de diálogo, introducir rangos alí, premer OK. Triste, estou de acordo! Pero "todo cambia cando veñen ©" - macros!

O traballo cun filtro avanzado pódese acelerar e simplificar moito usando unha macro sinxela que executará automaticamente o filtro avanzado cando se introduzan condicións, é dicir, cambiando calquera cela amarela. Fai clic co botón dereito na pestana da folla actual e selecciona o comando Texto de orixe (Código fonte). Na xanela que se abre, copia e pega o seguinte código:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Non é nada, entón se produce un erro Reanudar a seguinte ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Este procedemento executarase automaticamente cando se cambie calquera cela da folla de traballo actual. Se o enderezo da cela modificada cae no intervalo amarelo (A2:I5), entón esta macro elimina todos os filtros (se os hai) e volve aplicar o filtro estendido á táboa de datos de orixe comezando por A7, é dicir, todo filtrarase inmediatamente, inmediatamente. despois de introducir a seguinte condición:

Entón todo é moito mellor, non? 🙂

Implementación de consultas complexas

Agora que todo se está filtrando sobre a marcha, podemos afondar un pouco máis nos matices e desmontar os mecanismos de consultas máis complexas no filtro avanzado. Ademais de introducir coincidencias exactas, pode usar varios caracteres comodín (* e ?) e signos de desigualdade matemática nunha serie de condicións para implementar unha busca aproximada. Non importa o caso do personaxe. Para claridade, resumín todas as opcións posibles nunha táboa:

Puntos sutís:

• O signo * significa calquera número de caracteres e ? - calquera personaxe.
• A lóxica no procesamento de consultas de texto e numéricas é lixeiramente diferente. Así, por exemplo, unha cela de condición co número 5 non significa buscar todos os números que comecen por cinco, senón que unha cela de condición coa letra B é igual a B*, é dicir, buscará calquera texto que comece pola letra B.
• Se a consulta de texto non comeza co signo =, podes poñer mentalmente * ao final.
• As datas deben introducirse no formato de EE. UU. mes-día-ano e a través dunha fracción (aínda que teña Excel e configuración rexional).

Conectivos lóxicos E-OU

As condicións escritas en celas diferentes, pero na mesma liña, considéranse interconectadas por un operador lóxico И (E):

Eses. plátanos filtrados para min no terceiro trimestre, precisamente en Moscova e ao mesmo tempo de Auchan.

Se precisa vincular condicións cun operador lóxico OR (OU), entón só precisan ser introducidos en liñas diferentes. Por exemplo, se necesitamos atopar todos os pedidos do director Volina para pexegos de Moscova e todos os pedidos de cebola no terceiro trimestre en Samara, entón pódese especificar nunha serie de condicións como segue:

Se precisa impoñer dúas ou máis condicións nunha columna, pode simplemente duplicar a cabeceira da columna no rango de criterios e introducir a segunda, terceira, etc. termos. Así, por exemplo, pode seleccionar todas as transaccións de marzo a maio:

En xeral, despois de "acabar cun ficheiro", un filtro avanzado resulta ser unha ferramenta bastante decente, nalgúns lugares non peor que un filtro automático clásico.

• Superfiltro en macros
• Que son as macros, onde e como inserir o código de macros en Visual Basic
• Táboas intelixentes en Microsoft Excel