Segmentaciones como alternativa a los menús desplegables condicionales

A partir de Excel 2013 para Windows, podemos usar segmentaciones para filtrar datos de tablas. En esta publicación, exploraremos cómo usar Slicers como una alternativa relativamente fácil a los menús desplegables condicionales.

Objetivo

Cuando queremos permitir que un usuario seleccione una opción de una lista de elementos, a menudo consideramos usar la función Validación de datos para configurar un menú desplegable con la lista de opciones. Cuando hay una segunda celda de entrada cuyas opciones dependen de la opción seleccionada en el menú desplegable principal, a menudo consideramos usar Validación de datos para crear también un menú desplegable secundario. Usamos algunas fórmulas mágicas para crear la lista desplegable secundaria de opciones. Este tipo de escenario se ilustra a continuación.

Desafortunadamente, la fórmula que define la lista secundaria de opciones puede ser complicada de configurar

Afortunadamente, existe una alternativa

Las cortadoras son una alternativa, como se ilustra a continuación.

Ahora, veamos los pasos para crear las segmentaciones.

Detalles

Para configurar el libro de trabajo, seguiremos los siguientes pasos.

• Almacenar los datos en una tabla.
• Rebanadoras
• Columna de ayuda
• Recuperar datos de la tabla seleccionada

Hagámoslo.

Almacenar los datos en una tabla.

El primer paso es asegurarse de que los datos estén almacenados en una tabla. De lo contrario, puede convertir fácilmente el rango normal en una tabla seleccionando cualquier celda del rango y luego el siguiente icono de la cinta:

• Insertar Tabla

La tabla de datos resultante se muestra a continuación.

Excel nombró automáticamente la tabla Tabla1, pero podríamos cambiar el nombre de la tabla si lo deseamos usando el siguiente campo de la cinta:

• Herramientas de tabla Diseño Nombre de tabla

Nos quedaremos con el nombre predeterminado, Tabla1, y seguiremos avanzando. Con nuestros datos almacenados en una tabla, ahora podemos proceder a insertar los controles del Slicer.

Rebanadoras

A partir de Excel 2013 para Windows, las segmentaciones pueden filtrar tablas. Como queremos permitir que el usuario seleccione un estado, luego una ciudad válida y luego una calle válida, necesitamos configurar tres segmentaciones. Seleccionamos cualquier celda de la tabla y luego el siguiente icono de Cinta:

• Insertar Cortadora

El cuadro de diálogo Insertar segmentaciones resultante nos permite elegir qué columnas nos gustaría filtrar. En nuestro caso, verificamos Estado, Ciudad y Calle, como se muestra a continuación.

Excel inserta los tres controles Slicer en nuestra hoja de trabajo, como se muestra a continuación.

Ahora, podemos usar los comandos estándar de cortar y pegar para mover los Slicers a cualquier otra hoja de trabajo, en nuestro caso, la hoja de trabajo Slicers. Después de alinearlos y cambiar su tamaño, aparecen como se muestra a continuación.

Puede hacer clic en un botón de segmentación y notará que las otras segmentaciones se actualizan en consecuencia para resaltar las opciones disponibles.

Para ocultar las opciones no válidas, por ejemplo, para ocultar ciudades que no están en el estado seleccionado, seleccione City Slicer y luego abra el cuadro de diálogo Slicer Settings seleccionando el siguiente icono de cinta:

• Herramientas de segmentación Opciones Configuración de segmentación

En el cuadro de diálogo, marque la casilla Ocultar elementos sin datos, como se muestra a continuación.

Hacemos esto para los Slicers dependientes, es decir, los City y Street Slicers. Ahora sólo aparecerán combinaciones válidas de opciones.

Hay muchas opciones de formato adicionales, y le recomiendo que juegue con ellas seleccionando un Slicer y luego explorando la pestaña Slicer Tools Options Ribbon. Hay estilos con muchos colores hermosos, opciones de alineación y también puedes controlar el tamaño del botón y el tamaño general del control de segmentación.

Con las segmentaciones en su lugar, ahora es el momento de recuperar los valores de la tabla en celdas. Para hacerlo, comenzaremos configurando una columna auxiliar.

Columna de ayuda

Agregaremos una nueva columna a nuestra tabla que almacena una fórmula auxiliar. La columna de ayuda nos permitirá recuperar fácilmente los valores seleccionados con fórmulas. El propósito de la columna auxiliar es identificar qué filas son visibles y cuáles están ocultas después de aplicar un filtro. Usaremos la función AGREGAR y le indicaremos que ignore las filas ocultas.

Antes de escribir la fórmula de la columna auxiliar, seleccionamos la tabla y borramos los filtros aplicados con el siguiente ícono de cinta:

• Datos Borrar

Luego, creamos una nueva etiqueta de columna como “Visible” y confirmamos que la tabla se expande automáticamente hacia la derecha para incluirla.

En la primera fila de datos de la columna, escribimos la siguiente fórmula:

=AGREGADO(3,5,[@StoreID])

Dónde:

• 3 le dice a la función que use la función CONTAR, que cuenta las celdas que no están en blanco
• 5 le dice a la función que ignore las filas ocultas
• [@StoreID] le dice a la función qué rango contar, la fila actual de la celda StoreID

Esto hará que Excel coloque un 1 en las celdas visibles y un 0 en las celdas ocultas. Actualmente, no hay celdas ocultas porque no se ha aplicado ningún filtro como se muestra a continuación.

Sin embargo, tan pronto como apliquemos un filtro, ya sea manualmente o haciendo clic en un botón Slicer, habrá celdas ocultas. La función AGREGAR devolverá 0 para las celdas en filas ocultas. Esto nos facilita recuperar valores de celda relacionados en función de las opciones seleccionadas con las segmentaciones. Hablando de eso, ahora es el momento de recuperar los valores de la tabla relacionados en algunas celdas.

Recuperar datos de la tabla seleccionada

Nuestro último paso es recuperar los valores de la tabla relacionados. Esto se hace fácilmente con las funciones ÍNDICE/COINCIDIR.

Para recuperar el ID de tienda seleccionado, utilizamos la siguiente fórmula:

=ÍNDICE(Tabla1[IDTienda],COINCIDIR(1,Tabla1[Visible],0))

Dónde:

• Table1[StoreID] es la referencia de tabla estructurada para la columna Store ID.
• MATCH(1,Table1[Visible],0) devuelve el número de posición de la primera fila visible
• Dónde:
  • 1 es el valor a encontrar (1 aparecerá en filas visibles)
  • Tabla1[Visible] es la columna que contiene nuestra fórmula auxiliar
  • 0 significa coincidencia exacta

Podemos configurar fórmulas similares para recuperar los valores relacionados restantes. Por ejemplo, para recuperar el estado seleccionado, utilizamos la siguiente fórmula:

=ÍNDICE(Tabla1[Estado],COINCIDIR(1,Tabla1[Visible],0))

Etcétera.Si aún no ha explorado las funciones ÍNDICE/COINCIDIR, he incluido enlaces a publicaciones de blog relacionadas de Excel University.

El libro de trabajo resultante se muestra a continuación.

Nota: las fórmulas anteriores recuperan los valores de la primera fila coincidente.

Existen muchos beneficios al utilizar el enfoque de Slicers, entre ellos, los Slicers crean automáticamente botones únicos a partir de una columna con valores duplicados, incluyen automáticamente barras de desplazamiento si es necesario y ordenan los botones automáticamente. Este enfoque es excelente si su versión de Excel admite segmentaciones y tablas, y si tiene un único conjunto de celdas de entrada. De lo contrario, es posible que desee consultar la publicación de validación de datos condicional a la que se hace referencia a continuación.

Y ese amigo mío es cómo usar Slicers como alternativa a los controles desplegables condicionales. Si tiene alguna alternativa, compártala publicando un comentario a continuación… ¡gracias!

Recursos adicionales

• Archivo de muestra
• Publicación de validación de datos condicional.
• Publicaciones de índice
• Publicaciones de PARTIDO
• Publicaciones de mesa