Crear un filtro manual con validación de datos y formulas - 2da parte

Continuaremos con los pasos para crear un filtro manual con validación de datos y formulas. Si no han visto la primera parte de esta publicación, les recomiendo que lo hagan antes de continuar con estos pasos. La primera parte se encuentra en el siguiente enlace.

Paso 2: Crear una columna auxiliar en nuestra tabla de datos.

En la hoja "Tabla" de nuestro archivo añadiremos una columna con el nombre Indice.

Colocaremos en esa nueva columna dos funciones: 

La función SI(  ) y la función FILA(  )

Entonces en la celda H2 colocaremos la siguiente función:

=SI(A2=DatoBuscado;FILA();"")

Recordemos que en la hoja "Resumen", a la celda C2 la llamamos DatoBuscado (esto lo hicimos en la primera parte de esta publicación).
Esa función la copiamos en todas las celdas de la columna H, y cada vez que seleccionemos algún cliente en la hoja "Resumen", la columna indice nos mostrará la fila en que se encuentran dichos clientes.Veamos:

En la hoja "Resumen"

En la hoja "Tabla"

Como observamos en la hoja "Resumen" seleccionamos el cliente "BAGELMENS DE COSTA RICA S.A."; y es por ello que en la hoja "Tabla", en la columna Indice, nos muestra los números de filas donde encontramos dicho cliente; y en los que no lo encuentra los deja en blanco ("").

Paso 3: Traer los datos a la hoja Resumen.
Este es el paso mas complejo, pues utilizaremos varias funciones. Les recomiendo que antes de continuar le den una repasada a las siguientes funciones:
Función SI(  )
Función FILA(  )
Función K.ESIMO.MENOR(  )
Función INDICE(  )
Función SIERROR (  )
Función CONSULTAV(  )

Si haces clic en los enlaces, veras la explicación de cada una de esas funciones.

Ahora si... como diría el pollito: "Vamos al grano"

Inicialmente seleccionamos un cliente:

En la columna A

Nos posicionamos en la celda A6,  escribimos la siguiente función:

=K.ESIMO.MENOR(Tabla!$H$2:$H$401;FILA(Tabla!H2)-1)

En el primer caso esta función nos devolverá el primer valor menor que encuentre en la columna H de la hoja "Tabla". y conforme lo copiemos hacia abajo nos devolverá el segundo valor menor de la columna H, luego el tercero y así sucesivamente.

Como podemos observar nos devuelve los números del 51 al 54, y luego nos devuelve error, esto debido a que en nuestra tabla solo tenemos 4 veces el cliente "CORPORACIÓN DE JUEGOS" y justamente se encuentran en esas filas:

Ahora usaremos la función INDICE, donde le indicaremos que de la columna B, de la hoja "Tabla", nos devuelva el valor N que nos indique la función K.ESIMO MENOR, es decir:

=INDICE(Tabla!$B$2:$B$401;K.ESIMO.MENOR(...))

Ya la función K.ESIMO.MENOR, la habíamos definido, por lo tanto la formula sería la siguiente:

=INDICE(Tabla!$B$2:$B$401;K.ESIMO.MENOR(Tabla!$H$2:$H$401;FILA(Tabla!H2)-1))

Como pueden observar, aun nos da error en algunas filas, por lo que usaremos la función SIERROR para evitar que nos de el error. Le diremos que en lugar del error nos muestre la celda en blanco. (utilizando la doble comilla "").

=SIERROR(INDICE(...);"")

=SIERROR(INDICE(Tabla!$B$2:$B$401;K.ESIMO.MENOR(Tabla!$H$2:$H$401;FILA(Tabla!H2)-1));"")

De esta forma cada vez que seleccionemos un cliente, en la hoja "Resumen" se mostrará los números de facturas que le corresponden.

En la columna B

Luego de la función compleja que acabamos de crear en la columna A, ahora viene una mas difícil, mentira!!!!, ahora viene una función mas sencilla.

Hablamos de la función SI, e indicamos lo siguiente:

=SI(A6="";"";DatoBuscado)

Donde indicamos si en la celda A6 esta vacía, que nos coloque la celda B6 vacía también, sino que nos devuelva el valor de la celda DatoBuscado.

En la columna C

En esta columna utilizaremos la función CONSULTAV, donde el valor a buscar será el número de factura (celda A6), dentro de la tabla que se encuentra en la hoja "Tabla" y nos devuelva la dirección.

=CONSULTAV(A6;Tabla!$B$2:$G$401;2;0)

Observemos que no estamos seleccionando toda la tabla de la hoja "Tabla", sino a partir de la columna B en adelante, pues es en esa columna donde tiene los números de factura.

Ademas, observamos que en algunos casos no da error #N/A, para evitar ese error, incluiremos la función CONSULTAV(...) dentro de la función SI, de la siguiente manera.:

=SI(A6="";"";CONSULTAV(A6;Tabla!$B$2:$G$401;2;0))

En las columnas D, E, F, G

En estas columnas utilizaremos la misma función que utilizamos en la columna C, con la diferencia que dentro de la función CONSULTAV cambiamos el número de columna que deseamos que nos devuelvan.

De esta manera, tendremos lo siguiente:

En la columna C: =SI(A6="";"";CONSULTAV(A6;Tabla!$B$2:$G$401;2;0))

En la columna D: =SI(A6="";"";CONSULTAV(A6;Tabla!$B$2:$G$401;3;0))

En la columna E: =SI(A6="";"";CONSULTAV(A6;Tabla!$B$2:$G$401;4;0))

En la columna F: =SI(A6="";"";CONSULTAV(A6;Tabla!$B$2:$G$401;5;0))

En la columna G: =SI(A6="";"";CONSULTAV(A6;Tabla!$B$2:$G$401;6;0))

Y FINALMENTE TERMINAMOS!!!!

El resultado será el siguiente:

Cada vez que cambiemos de cliente, la tabla automáticamente se actualizará.

Espero que les sirva.

Saludos
Ing. Enrique Neciosup Morales

enrique@expertosenexcelpc.com