jueves, 13 de octubre de 2011

Informe de gráfico dinámico según elemento.

Tiempo atrás expliqué diferentes formas, con y sin macros, de mostrar series de un gráfico con diferentes formatos (ejemplo 1 y ejemplo 2).
En esta ocasión conseguiremos un efecto similar, empleando el Informe de gráfico dinámico.
En concreto se trata de distinguir uno de los elementos de una serie del origen de datos, elegido por nosotros mediante una celda validada.
Nuestros datos de partida están en la Tabla siguiente:


Vemos como se trata de un listado de registros, en los que para cada uno de ellos nos aporta información del producto vendido (pdto1, pdto2 ó pdto3) a un cliente concreto (clienteA, clienteB ó clienteC) por una cantidad.
Queremos obtener un gráfico de barras que muestre resumido el volumen de pedidos, marcando de otro color al cliente seleccionado o elegido por nosotros.
Para ello, comenzamos configurando una Celda validada tipo lista con los tres clientes, por ejemplo en la celda G2.
También añadiremos a nuestra 'Tabla' de datos dos columnas auxiliares (una con los datos del cliente seleccionado y otra para el resto de clientes). Como estamos trabajando con 'Tablas' la fórmula de nuestras columnas auxiliares serán:
importeCliente: =SI(Tabla1[[#Esta fila];[Clientes]]=$G$2;Tabla1[[#Esta fila];[importes]];"")
importeResto: =SI(Tabla1[[#Esta fila];[Clientes]]=$G$2;"";Tabla1[[#Esta fila];[importes]])
éstas son simplemente condicionales que devuelven el importe si el código de 'Clientes' coincide con el valor de la celda G2, es decir, con el cliente seleccionado.


La inclusión de estas columnas auxiliares, quedaron explicadas en entradas anteriores (ver ejemplo 1 y ejemplo 2).
Sin embargo, por la especial configuración exigida en esta ocasión, nos vemos obligados a construir nuestro gráfico, no de manera directa como en otros momentos, si no que emplearemos el Informe de gráfico dinámico.
Construiremos, de todas formas, en primer lugar una Tabla dinámica para visualizar mejor los datos.En esta Tabla dinámica llevaremos el campo 'Producto' al área de Filtro de informe, los campos 'importeCliente' y 'importeResto' al área de Valores, y por último, el campo 'Clientes' al área Rótulos de fila:


Con lo que nos queda la siguiente Tabla dinámica:


Perfecto, ya estamos en disposición de construir nuestro Gráfico dinámico. Desde Herramientas de tabla dinámica > Opciones > Herramientas > Gráfico dinámico, elegiremos Tipo columnas Columna apilada, y obtendríamos:


Le quitamos la 'Leyenda', y seleccionado la serie de color Azul (serie de datos 'importeCliente') le agregamos etiquetas de datos:


Ya casi acabamos... ya tenemos la etiqueta de datos únicamente para la Serie de datos que queremos, esto es para el Cliente elegido; sin embargo, nos aparece un valor cero como etiquetas para los otros clientes... vamos a quitarlos de la vista.
Seleccionaremos dichas etiquetas, y le daremos formato de etiquetas de datos, y desde las Opciones de etiqueta seleccionaremos la posición 'Extremo interno'.


Por último, también desde formato de etiquetas de datos, y desde el menú de 'Número', elegiremos la Categoría personalizada:
#.##0,00;[Rojo]-#.##0,00;[Blanco]#.##0,00
siendo esto definitivo para ocultar los valores de las etiquetas de datos con valor cero, de la Serie 'importeClientes'


Tenemos finalmente, un gráfico dinámico, en función al valor elegido en la celda validada G2:

8 comentarios:

  1. Gracias, tu blog me está salvando la vida.
    En este caso, hay que actualizar la tabla dinámica cada vez que cambiemos de cliente no?
    Gracias de antemano
    Mª Carmen

    ResponderEliminar
    Respuestas
    1. Hola Mª Carmen,
      efectivamente, normalmente al trabajar con Tablas y gráficos dinámicos, al realizar cambios se hace necesario la Actualización de datos.
      Slds cordiales

      Eliminar
  2. Hola buenas, necesito que en una celda predeterminada solo se pueda insertar un valor con un formato en concreto, el formato seria: xx:xx:xx:xx:xx:xx donde x seria del 0 al 9

    Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías generar una validación de datos personalizada en la celda en cuestión, con la fórmula:
      =Y(ESNUMERO(VALOR(SUSTITUIR(A1;":";"")));EXTRAE(A1;3;1)=":";EXTRAE(A1;6;1)=":";EXTRAE(A1;9;1)=":";EXTRAE(A1;12;1)=":";EXTRAE(A1;15;1)=":")

      Saludos

      Eliminar
    2. Que rapido!!! Me viene de perlas, pero me he colado, como son macs de dispositivos electronicos tambien tienen letras,
      ejemplo:
      20:4f:55:80:fc:a1

      Lo siento, tengo que hacerme un un excel para dummies jejejje

      Eliminar
    3. :D
      quítale la parte de ESNUMERO(VALOR(SUSTITUIR(A1;":";""))).. al menos exigirá los divisores (:)

      Slds

      Eliminar
  3. Buenas Ismael,
    acabo de quitar la parte que me comentas quedando la formula asi:

    =Y(;EXTRAE(A1;3;1)=":";EXTRAE(A1;6;1)=":";EXTRAE(A1;9;1)=":";EXTRAE(A1;12;1)=":";EXTRAE(A1;15;1)=":")

    y ahora con letras o sin letras salta error.

    Graciasss


    ResponderEliminar
    Respuestas
    1. sería sin el primer ;
      =Y(EXTRAE(A1;3;1)=":";EXTRAE(A1;6;1)=":";EXTRAE(A1;9;1)=":";EXTRAE(A1;12;1)=":";EXTRAE(A1;15;1)=":")

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.