88- Filtro Avanzado en Google Sheets

Si bien en Google Sheets encontramos casi todas las herramientas que presenta la hoja Excel, algunas no están tan visibles o no se ejecutan del mismo modo.

Este sería el caso del Filtro Avanzado. No existe como 'opción'. Sino que debe resolverse de otro modo.

1- Una primera sugerencia, cuando se trate de un filtrado sencillo con solo un par de criterios, es la que se observa en las siguientes imágenes:

- Desde el menú Datos seleccionar 'Nuevo Filtro'.

- Seleccionar la columna a filtrar y desde el desplegable optar por 'La Fórmula personalizada es..'

- Utilizar las funciones propias de Excel para obtener todas las condiciones o criterios para el filtrado.

En este ejemplo se solicitó que solo muestre los productos cuyo precio sea >= 10000. Y con la función Y (and) se agregó un segundo criterio para mostrar solo aquellos productos cuyo nombre comienza con la letra C en adelante. No se mostrarán productos cuyo nombre comience con A y B. 

Observar que en cada argumento, separados por punto y coma, se hace mención al rango completo.

Esta manera de resolver un filtro tiene como desventaja que debemos introducir la fórmula en un espacio reducido, lo que hará que sea una tarea complicada cuando tengamos más argumentos. 

Además, filtrando en cada columna, se van ocultando filas lo que podría hacernos perder información en otro sector de la hoja.

2- Para un correcto Filtro Avanzado las Google Sheets nos presentan la función FILTER desde el menú Insertar, Funcion.

Seleccionamos una celda fuera de la tabla de datos (en la imagen es G6) y allí escribiremos la función con los siguientes argumentos:

=FILTER( rango a filtrar; condición o criterio1; condición o criterio2;....)

Para nuestro ejemplo sería:

        =FILTER(B6:E50;E6:E50 >= 10000;C6:C50 >= "C")

NOTA: podemos escribir la fórmula en la celda seleccionada o en la barra de fórmulas lo que resulta aún más cómodo.

Podemos emular el Filtro Avanzado de Excel, colocando los criterios en celdas o rangos.

Por ejemplo colocaré el primer criterio en la celda E4 y el del nombre en la celda C4. De este modo en lugar de colocar los valores en la fórmula colocaremos la referencia (absoluta) de la celda donde se encuentran.

Aquí para mostrar otra opción modifiqué la segunda condición dejándola en < "D".

=FILTER(B6:E50;E6:E50 >= $E$4;C6:C50< $C$4)

Como podemos observar, esta opción tiene la ventaja de no afectar a nuestra tabla original ni tampoco ocultarnos las filas. Además podemos mover la tabla resultante a otra ubicación u hojas.

Otro ejemplo con rango de criterios donde se establecen 3 condiciones: un rango de precios y nombres que inician con A hasta C. 

La fórmula para este modelo quedaría de este modo:

=FILTER(B6:E50;E6:E50 >= $J$1;E6:E50 <= $J$2;C6:C50< $J$3)

Este tema continuará ...... ;)

Ver video Nº 88 desde aquí.

87- Pasar datos de un ListBox a otros campos y a otros Userforms.

 Al momento de seleccionar algún elemento que tengamos en un control ListBox, dentro de un Userform, podemos optar por pasar cada columna de la lista a controles dentro del mismo formulario o a controles ubicados en otros Userforms. También por supuesto podemos optar por enviarlos a una hoja Excel.

A continuación imágenes y códigos para cada modelo.

CASO 1: pasar algunas columnas del elemento seleccionado a campos dentro del mismo Userform. Aquí se toman los datos de la hoja base de Materiales.

Invocaremos al evento Doble Clic del control Lista. Pasaremos a los diferentes controles del formulario el contenido de algunas columnas de ese elemento seleccionado. La fila seleccionada en la lista se reconoce como ListBox1.ListIndex. Y las columnas inician en el valor 0. 

Por ejemplo, el valor 'Cod' de la fila seleccionada en la imagen, se reconoce como: ListBox1.List(ListBox1.ListIndex, 0)

Ajustar el nombre de cada control que tengan en el formulario.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean

 'si no se selecciona algún elemento de la lista, cancela. 

If ListBox1.ListIndex < 0 Then Exit Sub

‘se guarda en la variable el contenido de la primera columna

dato = ListBox1.List(ListBox1.ListIndex, 0)

‘se lo busca en la hoja Base de Materiales, previamente llamada como ‘homa’ (*)

Set busco = homa.Range("B:B").Find(dato, LookIn:=xlValues, lookat:=xlWhole)

‘si el dato fue encontrado, se pasan algunas columnas a la hoja

If Not busco Is Nothing Then

    TextBox9 = homa.Range("C" & busco.Row)

    TextBox8 = homa.Range("B" & busco.Row)

    TextBox3 = homa.Range("D" & busco.Row)

    TextBox5 = homa.Range("E" & busco.Row)

End If

‘se posiciona en el primer control a rellenar por parte del usuario

ComboBox1.SetFocus

‘se quita la selección a la lista.

ListBox1.ListIndex = -1

End Sub

(*) En el evento Initialize, se declaró una variable (pública) llamada 'homa' para guardar el nombre de la hoja base de Materiales.

CASO 2: pasar algunos campos del elemento seleccionado a controles dentro de otro Userform. 

En el Userform de la izquierda se observa una lupa que tendrá asociada la subrutina que llama al formulario de la derecha. Ajustar los nombres del control imagen y del segundo formulario.

Private Sub Image1_Click()     'botón que llama al segundo formulario.
UserFactura.Show
End Sub

En el segundo Userform (UserFactura) se programó también el evento Doble Clic. Y en este modelo, se pasó cada columna del elemento seleccionado a los controles del primer Userform (UF_UsoMaterial). Ajustar todos los nombres a los que correspondan en vuestros modelos.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

‘se controla que esté seleccionado un elemento de la lista

If ListBox1.ListIndex >= 0 Then

     ‘se pasan algunas columnas de la lista a los controles del primer UF

    With UF_UsoMaterial

        .TextBox8 = ListBox1.List(ListBox1.ListIndex, 0)

        .TextBox9 = ListBox1.List(ListBox1.ListIndex, 1)   

        .TextBox3 = ListBox1.List(ListBox1.ListIndex, 2)

        .TextBox5 = ListBox1.List(ListBox1.ListIndex, 3)

    'revisar qué otros campos debieran pasarse al otro formulario

    '....

    'se cierra este uf quedando en el de Factura. Se quita el filtro a la hoja.

    If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

    'se cierra este segundo formulario para volver al primero.

    Unload Me

End If

End Sub

Nota: en el libro se incluye la macro de búsqueda adaptada del ejemplo del video Nº 36: Filtro dinámico.

ATENCIÓN: es importante que los 2 userforms tengan el mismo valor en su propiedad ShowModal. 

CASO 3: pasar algunos campos del elemento seleccionado a una segunda hoja Excel. 

Siguiendo el ejemplo anterior, para pasar elementos seleccionados de una lista a una hoja simplemente se cambiará el destino reemplazando el nombre del primer formulario y su campo por el de la fila y columna en la hoja destino (en el ejemplo es SALIDAS).

Aquí se utilizó el evento Click aunque bien podría ser también el DoubleClick.

IMPORTANTE: No se deben desarrollar los 2 eventos en un mismo Userform. Ya que al intentar presionar doble click ya se ejecuta el simple click ;)

Ejemplo:

Private Sub ListBox1_Click()

‘se controla que esté seleccionado un elemento de la lista

If ListBox1.ListIndex >= 0 Then

‘se pasan algunas columnas de la lista a una hoja resumen de Salidas

    '-------------- OPCIONAL: PASAR A UNA HOJA DE SALIDAS

    X = Sheets("SALIDAS").Range("a" & Rows.Count).End(xlUp).Row + 1

    With Sheets("SALIDAS")

        .Range("A" & X) = ListBox1.List(ListBox1.ListIndex, 0)

        .Range("B" & X) = ListBox1.List(ListBox1.ListIndex, 1)

        .Range("C" & X) = ListBox1.List(ListBox1.ListIndex, 2)

        .Range("D" & X) = ListBox1.List(ListBox1.ListIndex, 3)

    End With

    '----------------------------------------------------------------------

‘otros pases

    Unload Me

End If

End Sub

Descargar el libro de ejemplo desde aquí o solicitarlo al correo cibersoft.arg@gmail.com

Ver video Nº 87 desde aquí.

Otros videos relacionados: 

      Nº 36: Filtro dinámico desde un Userform.

      Nº 39: RowSource. Actualizar desplegables desde otro formulario.

      Nº 9:  Macros compartidas.