Generar hojas a partir del filtro de una tabla dinámica de Excel.

Excel tiene muchas funcionalidades que hacen la vida del usuario más fácil; pero no todas son evidentes. Parafraseando a Antoine de Saint-Exupery, podríamos decir que "mucho de lo útil en Excel (lo esencial) es invisible a los ojos".

En este caso nuestro "principito" resultó ser José María Murillo que respondiendo a una consulta en el foro de Excel en castellano en Linkedin, menciona la funcionalidad "mostrar páginas de filtros de informes" en tablas dinámicas.

La situación es la siguiente: tenemos una base de datos con las ventas del año por cliente y por agente de ventas. Creamos una tabla dinámica para resumir las ventas; en el área de filtro usamos el campo "Agente de Ventas" para generar un informe para cada vendedor

Si queremos imprimir un informe para cada vendedor, tendríamos que seleccionar el vendedor, imprimir el informe, seleccionar el próximo vendedor, imprimir el informe y así sucesivamente hasta el último.

En lugar de esta tarea sisífica, podemos usar la funcionalidad "Mostrar páginas…" que se oculta en Herramientas-Analizar-Tabla Dinámica-Opciones (en Opciones hay que asegurarse de hacer clic en el triángulo invertido a la derecha de la opción)

Al seleccionar la opción "Mostrar…" se abre una ventanilla con los campos del filtro (en nuestro ejemplo hay sólo uno)

Al seleccionar el campo, Excel crea automáticamente una hoja para cada valor (agente de ventas en nuestro ejemplo) en el campo del filtro



Ahora podemos imprimir todas las hojas de los agentes en una única operación.

Si queremos enviar los informes por correo electrónico debemos recordar que cada informe refleja todos los datos de la base de datos y el agente puede cambiar el valor del filtro para "espiar" las ventas de sus colegas.

Cámara fotográfica de Excel - otro uso

Una de las herramientas útiles y poco conocidas de Excel es la cámara fotográfica. Esta herramienta nos permite, entre otras cosas, ubicar gráficos y tablas en un dashboard hecho en Excel. Muy útil en particular cuando queremos ubicar tablas de distinto ancho de columnas en una hoja (como en el ejemplo de esta nota sobre dashboards en Excel).

Otra posibilidad es usar la cámara fotográfica de Excel en lugar de la ventana de inspección. Supongamos este ejemplo: en una hoja de un cuaderno tenemos un cuadro de ventas, comisiones y ventas neto y en otra hoja la tabla de los porcentajes de las comisiones en función del volumen de ventas

Ahora supongamos que queremos ver cómo influyen los cambios en los porcentajes de las comisiones en el total neto de las ventas. Como las tablas no están en la misma hoja, tendremos que "saltar" de una hoja a la otra durante el proceso de encontrar el resultado deseado.

Para evitar esto podemos usar, en lugar de la ventana de inspección, la cámara fotográfica de Excel.

Empecemos por poner el icono de la cámara en la barra de acceso rápido



Una vez instalado el icono de la cámara, seleccionamos el rango de la tabla de ventas, hacemos un clic al icono, pasamos a la hoja con la tabla e comisiones y con otro clic copiamos la imagen de la tabla



Como puede verse, cada cambio en la tabla de comisiones se refleja inmediatamente en la imagen de la tabla de ventas.
La ventaja de usar la cámara se hace evidente si vemos como sería el uso de la ventana de inspección para la misma tarea

Validación de datos con criterios múltiples

Un lector me consulta como aplicar validación de datos con más de un criterio. Por ejemplo, que en un rango determinado se pueda ingresar valores entre 1 y 10 y que no haya duplicados.

Antes de mostrar la solución, es importante señalar el punto central que el mecanismo de Validación de Datos funciona con fórmulas que dan como resultado VERDADERO o FALSO. Para que el valor a introducir sea aceptado, la fórmula de la validación debe evaluar a VERDADERO.

En nuestro ejemplo queremos que las dos condiciones se cumplan simultáneamente. Para eso debemos usar la función Y. Si queremos aplicar la validación de datos múltiple en el rango B3:B10 usaremos la fórmula

=Y(CONTAR.SI($B$3:$B$10,B3)<2,Y(B3>=1,B3<=10))

Esta fórmula funciona de la siguiente manera:

CONTAR.SI($B$3:$B$10,B3)<2 evalúa si el valor a introducir aparecerá más de una vez. Si el valor ya aparece en el rango, el resultado de esta fórmula es FALSO.

Y(B3>=1,B3<=10)) verifica que el valor a introducir sea mayor o igual a 1 y menor o igual a 10.

Finalmente, la función Y al principio de la fórmula combina ambos resultados dando VERDADERO solamente si ambas condiciones se cumplen.

Si queremos evitar valores duplicados y los valores permitidos van de 1 a 10 y de 20 a 25, es decir, dos intervalos de números, ampliamos nuestra fórmula con la función O

=Y(CONTAR.SI($B$3:$B$10,B5)<2,O(Y(B5>=1,B5<=10),Y(B5>=20,B5<=25)))

La función O da VERDADERO si alguna de las funciones Y que son sus argumentos da VERDADERO.