Crear un Filtro de Lista Desplegable en Excel y Google Sheets

Este tutorial muestra cómo crear un filtro de lista desplegable en Excel y Google Sheets.

 

 

Puede utilizar una lista desplegable para extraer filas de datos que coincidan con la entrada de la lista desplegable y devolver estas filas a un área separada de la hoja de cálculo.

Hay tres etapas principales para completar esta acción.

1. Crear una lista única de elementos que aparecerán en la lista desplegable, ya que los datos pueden contener elementos repetidos.
2. Cree la lista desplegable para filtrar los datos.
3. Crear el filtro utilizando columnas de ayuda que contengan las fórmulas necesarias para extraer los datos.

Crear una Lista Única

Para la primera etapa, necesita una lista de elementos únicos para el campo sobre el que se va a filtrar (en este caso, productos).

1. Resalte los elementos que desea que aparezcan en la lista desplegable.

 

2. A continuación, copie y pegue la lista en otra zona de la hoja de cálculo.
3. Elimine los duplicados. Seleccione la lista pegada y, a continuación, en la cinta de opciones, vaya a Datos > Herramientas de datos > Eliminar duplicados.

 

4. Haz clic en Aceptar y, a continuación, vuelve a hacer clic en Aceptar para eliminar los duplicados y regresar a Excel. Ahora, hay una lista única para el desplegable.

 

Crear la Lista Desplegable

Ahora, para la segunda etapa, cree la lista desplegable para el filtro.

1. Seleccione la celda en la que desea colocar la lista desplegable y, a continuación, en la cinta de opciones, vaya a Datos > Herramientas de datos > Validación de datos.

 

2. En la ventana Validación de datos, seleccione Lista en el menú desplegable Permitir y haga clic en la flecha situada junto a la casilla Fuente.

 

3. Seleccione el rango de celdas que contienen los elementos únicos creados anteriormente y pulse INTRO.

 

4. Haga clic en Aceptar para confirmar y salir de la ventana Validación de datos.

 

Ahora puede seleccionar en la lista desplegable.

 

Nota: Puede añadir su propio mensaje de error para la validación de datos. También puede añadir un mensaje de entrada a las celdas con validación de datos para proporcionar información sobre qué valores están permitidos.

Extraer Datos con Fórmulas

Para la tercera y última etapa, añada columnas de fórmulas que actuarán como filtros junto a la lista desplegable.

1. En primer lugar, cree columnas «Auxiliares» en la tabla de datos. A la derecha de la tabla de datos, inserte tres columnas: Auxiliar1, Auxiliar2 y Auxiliar3.

 

2. Selecciona la primera celda de la columna Auxiliar1 y escribe la siguiente fórmula FILAS:

=FILAS($B$3:B3)

A continuación, copia la fórmula en las filas restantes de la columna del ayudante.

 

Esta fórmula asigna un número a cada fila de la tabla de datos, empezando por la primera fila de datos. Observa que no se corresponde con el número de fila en Excel.

3. Selecciona la primera celda de la columna Auxiliar2 y escribe la fórmula:

=SI(C3=$J$3;F3;"")

Copie la fórmula en el resto de filas de la columna auxiliar.

 

Esta fórmula devuelve el número de fila de la columna Auxiliar1 si el valor de la lista desplegable (J3) es igual al valor de la misma fila de la columna C (C3).

4. Ahora selecciona la primera celda de la columna Auxiliar3 y escribe la siguiente fórmula utilizando las funciones SI.ERROR y K.ESIMO.MENOR:

=SI.ERROR(K.ESIMO.MENOR($G$3:$G$20;F3);"")

A continuación, copia la fórmula en las filas restantes de la columna del ayudante.

 

Esta fórmula devuelve el 1er, 2do, 3er, etc. número más pequeño de la columna Auxiliar2 en función del número de fila de la columna Auxiliar1.

5. Ahora que ha creado las columnas auxiliares, cree la fórmula en la tabla de resultados del filtro.
   Haz clic en la primera celda de la tabla de resultados del filtro (por ejemplo, L3) y escribe la siguiente fórmula utilizando las funciones SI.ERROR, INDICE y COLUMNAS:

=SI.ERROR(INDICE($B$3:$E$20;$H3;COLUMNAS($L$3:L3));"")

A continuación, copie esta fórmula para rellenar el resto de las celdas de la tabla de resultados del filtro.

 

Dependiendo del valor que haya seleccionado en la lista desplegable, los resultados del filtro deberían mostrar todos los resultados para ese valor.
En este ejemplo, tiene una lista de pedidos de productos y ha filtrado para mostrar los pedidos en los que el producto de la lista desplegable seleccionado es Camara Web.

6. Cambie la selección en la lista desplegable para mostrar una lista diferente de pedidos.

 

Nota: Para que la hoja de cálculo sea más agradable estéticamente y para proteger las fórmulas de las columnas de ayuda, oculte las columnas auxiliares. Haga clic con el botón derecho en las columnas y, a continuación, haga clic en Ocultar.

 

Crear un Filtro Desplegable en Google Sheets

El filtro desplegable funciona igual en Google Sheets que en Excel. Sigue los pasos descritos anteriormente para crear la lista desplegable y tres columnas de ayuda con las mismas fórmulas utilizadas para Excel.

Sin embargo, al crear las fórmulas de la tabla de resultados del filtro final, hay una ligera diferencia:

=SI($H3<>"";INDICE($B$3:$E$20;$H3;COLUMNAS($L$3:L3));"")

Cree una sentencia SI para comprobar si el valor de la columna Auxiliar3 está ahí. Si el valor está ahí, puedes ejecutar la fórmula INDICE, pero si el valor no está ahí, devuelve un espacio en blanco.