Recientemente me han preguntado varias veces sobre la misma cuestión:
¿Cómo realizar filtros avanzados tomando rangos de criterios variables, elegidos según una celda validada?
Y esto es precisamente, mediante una sencilla macro, lo que vamos a realizar hoy.
Partimos del siguiente planteamiento:
Importante el rango de trabajo, la 'base de datos' está en A1:D21.
Los rangos de criterios opcionales, a los que hemos asignado Nombres Definidos son:
Filtro_1 =Hoja1!$I$2:$J$4
Filtro_2 =Hoja1!$I$8:$I$11
Filtro_3 =Hoja1!$I$15:$K$16
Y en la celda F1 de la hoja hemos creado una Celda validada tipo Lista, la cual nos despliega tres opciones: Filtro_1;Filtro_2;Filtro_3, siendo éstos, precisamente, los Nombres definidos asociados a los rangos de criterios a aplicar a nuestra base de datos.
La idea, por tanto, es clara. Queremos que según seleccionemos algún valor en la celda F1 se aplique el filtro correspondiente.
Un dato importante para el tercero de los criterios, en el que hemos incluido un filtro de fechas, es que las he construido de la siguiente forma:
=">="&FECHA(2016;1;1)
="<="&FECHA(2016;3;31) el motivo es por que de otra forma la programación no las entendería.
Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de la hoja de trabajo desde el editor de VB; empleando el evento _Change (que nos asegura la ejecución del procedimiento al variar el dato de la celda F1:
Podemos probar variando el dato de F1, y comprobaremos cómo se aplica el filtro correspondiente...
¿Cómo realizar filtros avanzados tomando rangos de criterios variables, elegidos según una celda validada?
Y esto es precisamente, mediante una sencilla macro, lo que vamos a realizar hoy.
Partimos del siguiente planteamiento:
Importante el rango de trabajo, la 'base de datos' está en A1:D21.
Los rangos de criterios opcionales, a los que hemos asignado Nombres Definidos son:
Filtro_1 =Hoja1!$I$2:$J$4
Filtro_2 =Hoja1!$I$8:$I$11
Filtro_3 =Hoja1!$I$15:$K$16
Y en la celda F1 de la hoja hemos creado una Celda validada tipo Lista, la cual nos despliega tres opciones: Filtro_1;Filtro_2;Filtro_3, siendo éstos, precisamente, los Nombres definidos asociados a los rangos de criterios a aplicar a nuestra base de datos.
La idea, por tanto, es clara. Queremos que según seleccionemos algún valor en la celda F1 se aplique el filtro correspondiente.
Un dato importante para el tercero de los criterios, en el que hemos incluido un filtro de fechas, es que las he construido de la siguiente forma:
=">="&FECHA(2016;1;1)
="<="&FECHA(2016;3;31) el motivo es por que de otra forma la programación no las entendería.
Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de la hoja de trabajo desde el editor de VB; empleando el evento _Change (que nos asegura la ejecución del procedimiento al variar el dato de la celda F1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
'La celda activa SÍ cruza con las celdas del rango
'eliminamos cualquier filtro existente en el rango filtrado
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
'recuperamos lo escrito en la celda F1
'para luego tratarlo como nombre definido
NombreDefinido = "" & Range("F1").Value & ""
'aplicamos el filtro avanzado... con el rango de criterios
'el Nombre seleccionado en F1
Range("A1:D21").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Hoja1.Range(NombreDefinido), _
Unique:=False
End If
End SubPodemos probar variando el dato de F1, y comprobaremos cómo se aplica el filtro correspondiente...
Que debo hacer para que esta macro se ejecute
ResponderEliminarHola claudio,
Eliminarque tal estás?, un placer saludarte igualmente
Solo debes hacer lo que se indica:
insertamos el siguiente código dentro de la ventana de código de la hoja de trabajo desde el editor de VB
al ser un evento se ejecutará sola cuando cambie algo en la hoja...
UN cordial saludo
hola ismael romero, por favor tu ayuda con este caso, deseo hacer un filtro a traves de una macro y de la filtracion que se haga, reemplazar la columna siguiente por otro dato, es decir todo el rango filtrado por otro texto.
Eliminarslds
cristian rodriguez
Hola Cristian
Eliminarprueba a seleccionar ese rango y usar el método .Replace
Sería lo más simple
Saludos
Buen dia,
ResponderEliminarGracias, otra consulta si quiero filtrar una tabla dinámica desde una celda pero desde otra hoja que debo hacer.
Hola Claudio,
Eliminaraplicar el valor de la celda como criterio del filtro en la tabla dinámica...
Busca en la categoría de macros del blog, encontrarás algún ejemplo similar a lo que describes
Saludos
Gracias,
ResponderEliminarTengo otra consulta tengo una tabla dinamica y quiero filtrarla por el cambio de dos celdas es posible con una macro.
tengo esto
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
'En base al campo Region de la Tabla dinámica1:
With PivotTables("Tabla_dinámica2").PivotFields("ORIGEN")
'Limpiar todos los filtros
.ClearAllFilters
'Filtrar por el valor de la celda F1
On Error Resume Next
.CurrentPage = Range("F1").Value
End With
End If
end sub
Loc ual me sirve para una celda pero dos celdas como lo haria.
Agradezco sus comentarios
Hola Claudio
Eliminareso es algo más elaborado
puedes echar un vistazo a este post
https://excelforo.blogspot.com/2016/03/vba-lanzar-macro-cuando-cambia-el-valor.html
Te dará la pista...
Saludos
Gracias,
EliminarPero lo que quiero si es posible en que al modificar la fila F1 Y F2 a la vez haga el filtrado con el codigo que envie.
Saludos.
Hola!
Eliminara la vez no podrás cambiar las celdas correspondientes...
seguro cambias una primero y después otra.
Añade en el evento que se describe un control sobre una u otra
Saludos
Hola Ismael, mi duda es si se podrian filtar datos como si fueran rangos, me explico, Si por ejemplo me dan un valor en una celda, digamos 34567 y en la columna M Y L esta el inicio de este rango y el Final, por lo que, tendria que poder devolver la informacion de este rango la fila que contenga el inicio del rango y el final. Esto es posible? Te doy un ejemplo mas sencillo, en la celda B5 se tiene el valor de 5 y en la columna c y D representan el inicio del rango y el final, C2 seria 1 y D2 10 por lo que B5 si pertenece a este rango, de antemano gracias y saludos
ResponderEliminarHola,
Eliminarcon filtros avanzados (con o sin fórmulas) parece posible
=Y(B5>C2;B5<D2)
esa fórmula, dentro del rango de criterios, te filtraría lo registros que cumplieran
Revisa la categoría del blog de filtros, y verás algún ejemplo
Saludos
Hola buena tarde; tengo filtro según el valor de una celda en tres tablas dinámicas ubicadas en la misma hoja, pero cuando en una o varias de ellas no encuentra un valor asociado al filtro me arroja todos los datos de la tabla. Hay alguna forma de que en este caso pueda arrojar la tabla vacía o en blanco?
ResponderEliminarHola!
Eliminarentiendo estás gestionándolo con una macro que aplica filtro según el valor de la celda...
Se me ocurre que antes de aplicar el filtro verifique si existe el valor o no en esa TD... y condicionar la aplicación del filtro a su existencia...
Espero te oriente la idea.
Saludos
Hola,
ResponderEliminarquiero filtrar por dos campos, pero que este en uno O en otro.
(OR)
es posible?
gracias.
Hola,
Eliminaren tu rango de criterios debajo del campo1 pones el criterio y debajo del campo2 en OTRA FILA el mismo criterio
Saludos