Ocultar valores cero en gráficos de Excel

En uno de los comentarios a mi nota sobre valores cero y vacíos en gráficos de Excel, me consulta una lectora cómo hacer para que estos valores no aparezcan en el gráfico.

Veamos esto con un ejemplo. Supongamos que llevamos una estadística sobre la ciudad de procedencia de turista y la queremos representar con un gráfico de columnas

De Murcia no han llegado turistas, pero de todas maneras aparece en el eje de las categorías (X). Lo que quiere mi lectora es una técnica para que las ciudades de las cuales no llegan turistas no aparezcan en el gráfico.

La solución que propongo en esta nota es usar tablas y gráficos dinámicos. Personalmente no me gustan los gráficos de las tablas dinámicas, pero en este caso nos resultan muy prácticos.

A partir de la tabla de turistas que mostramos más arriba creamos en una nueva hoja una tabla dinámica y su correspondiente gráfico

Por ahora este gráfico se ve tal como el anterior. Arrastramos el campo Turistas (el mismo que hemos puesto en el área de datos) al área de página




Seleccionamos con un clic el campo de página Turistas (en la celda A1) y abrimos el menú de configuración de campo. En la ventanilla Ocultar elementos marcamos el 0 y apretamos Aceptar




A partir de este momento, todas las ciudades con 0 turistas no aparecen en el gráfico




Finalmente podemos ocultar los botones de campo para que el gráfico parezca como un gráfico corriente de Excel

Technorati Tags: MS Excel

Filtro Avanzado en hojas protegidas – una rareza de Excel

Aplicamos Proteger a una hoja de Excel para impedir que el usuario haga cambios o modificaciones en la hoja. Excel nos permite definir excepciones. Por ejemplo, permitir que el usuario pueda usar Autofiltro (a condición que el autofiltro haya sido aplicado antes de proteger la hoja) u Ordenar

Si aplicamos Proteger Hoja sin excepciones, la opción Filtro y Mostrar Todo del menú Datos aparecen deshabilitadas



Si nos fijamos bien veremos que la opción Filtro Avanzado sigue activa. Los programadores de Excel se han olvidado de bloquear esta opción.

Así podemos aplicar Filtro avanzado para filtrar todas las filas donde el valor de la celda en la columna A sea mayor de 2500 a pesar de que la hoja está protegida



Uno de los problemas que surgen es que si bien la opción Filtro Avanzado sigue activa, la opción Mostrar Todo está bloqueada. Es decir, podemos filtrar, pero no podemos quitar el filtro.

Para mostrar las filas ocultas sin tener que cancelar la protección basta con abril el diálogo del Filtro Avanzado y borrar el rango de los criterios.

Microsoft conoce este problema desde la versión 2000, y en la última nota publicado en la base de conocimientos de Excel , Advanced Filter May Function on Protected Worksheet, mencionan que por el momento (noviembre del 2003!) no hay forma de impedir la aplicación de Filtro Avanzado en una hoja protegida.

Una solución posible es proteger todas las celdas y no permitir seleccionar todas las celdas donde pudieran definirse criterios para filtro avanzado. De esta manera, si bien el diálogo del filtro avanzado se abrirá, no habrá posibilidad de definir un rango de criterios, lo que hace que todas las filas queden visibles.

Technorati Tags: MS Excel

Valores vacíos y cero en gráficas de Excel

A pesar que las series de valores de estos gráficos parecen idénticas, los gráficos son distintos

En la primer serie de datos (Valores 1), las celdas A5 y A8 están vacías, es decir, no contienen ni valores ni fórmulas. En cambio la celdas A17 y A20 (serie Valores 2) si bien se las ve vacías, no lo están. La celda A17 contiene la fórmula =SI(A5<>0,A5,"").

Por ese motivo, en el primer gráfico la línea aparece discontinua y en el segundo la línea es continua y quebrada. Esto significa que Excel interpreta el resultado de la fórmula, (“”) como si fuera cero a pesar que en pantalla la celda aparece vacía.

Consideremos la serie Valores 1 y supongamos que queremos obtener una línea continua. Para lograrlo seleccionamos el gráfico y abrimos el menú Herramientas-Opciones. En la pestaña Gráfico seleccionamos la opción “Trazar Celdas vacías como…” --> Interpolar

La línea aparecerá ahora continua



En el caso de Valores 2 dado que las celdas A17 y A20 no están vacías, esta técnica no nos sirve. La solución consiste en cambiar la fórmula que genera los valores de la serie de

=SI(A5<>0,A5,"")

a

=SI(A5<>0,A5,NOD())

La función NOD() todo lo que hace es poner un valor de error #N/A en la celda. El resultado es

Si queremos que los valores #N/A no sean visibles podemos aplicar formato condicional dándole a la fuente el mismo color como el fondo de la celda

Technorati Tags: MS Excel