jueves, 11 de octubre de 2012

Formato condicional en una tabla dinámica de Excel.

Veremos hoy una característica poco conocida del Formato condicional, o más bien de las Tablas dinámicas en Excel.
Es posible aplicar Formato condicional asociándolo a campos de una tabla dinámica.

Partiremos de un origen de datos en una hoja de cálculo de Excel, convertida en Tabla, por comodidad, a partir de la cual hemos construido una tabla dinámica. Quedando como podemos ver en la imagen:



El paso siguiente consiste asignar un formato condicional que agregue un icono según el Importe de las ventas y el Comercial. Para ello seleccionaremos el campo 'Suma de Importe', y desde la Ficha Inicio > Estilos > Formato Condicional > Conjunto de iconos > 5 flechas (de color):

Formato condicional en una tabla dinámica de Excel 2010.


Como era de esperar al haber seleccionado el campo 'Suma de Importe' y haber aplicado un formato condicional sobre él, se muestran inmediatamente los iconos seleccionados.
Lo importante viene ahora, y es que aparece en la selección de celdas, un botón de opciones de formato condicional:

Formato condicional en una tabla dinámica de Excel 2010.


En estas tres opciones es donde está la utilidad de la herramienta, ya que podremos optar por asociar el Formato condicional dado bien a:
  • las celdas seleccionadas
  • al campo marcado, 'Suma de Importe' en nuestro ejemplo.
  • o bien a Todas la celdas que muestran valores de 'Suma de Importe' para 'Comercial'
Nosotros marcaremos la última opción:
Todas la celdas que muestran valores de 'Suma de Importe' para 'Comercial'
ya que nuestro objetivo es asociar la variabilidad de los iconos siguiendo esa regla precisamente, esto es, según la importancia de las ventas ('Importes') entre todos los 'Comerciales'.
Tras aplicar la Regla, podemos acceder al Administrador de reglas, seleccionar la regla creada y Editarla, para ver lo especial de esta edición:

Formato condicional en una tabla dinámica de Excel 2010.



El resultado es el siguiente:

Formato condicional en una tabla dinámica de Excel 2010.



Mientras mantengamos los campos 'Importe' y 'Comercial' (los dos campos asociados mediante la regla de formato condicional) los iconos configurados se mostrarán en la tabla dinámica de Excel, adaptándose al dinamismo de ésta. Es decir, añadiendo los iconos al cruce de los campos Comercial-Importe:

Formato condicional en una tabla dinámica de Excel 2010.


Pero cuidado, por que si sacamos de la tabla alguno de estos campos, la regla se eliminará.

29 comentarios:

  1. Buenas tardes. Muy bueno el post. Tengo una consulta, yo uso el excel 2010 y no me sale la opcion "boton de opciones de formato" en el extremo inferior derecho. Te agradecería si me ayudas a encontrarlo.

    ResponderEliminar
    Respuestas
    1. Hola!!
      bueno, el botón de opciones de formato únicamente aparece al aplicar el formato condicional por primera vez... Pero para configurarlo a posteriori sólo tienes que ir al Administrador de reglas del Formato condicional. Ahi te aparecerán todas las reglas, por lo que tendrás que buscar esa particular que deseas modificar, y Editarla.
      La ventana diálogo que te aparecerá, en su parte superior, tiene esas tres opciones (ver tercera imagen de este mismo post).
      Slds

      Eliminar
  2. Muchas Gracias, muy clara la explicación, me fue de gran utilidad.

    ResponderEliminar
  3. Hola... Tengo una consulta: dispongo de una Tabla Dinámica que toma los datos de otra hoja (importes de facturas). A la derecha de estos datos tengo una fórmula (una resta), donde uno de los restandos es la columna de Total General. El problemilla es que dicha columna va modificando su posición a medida que aparecen nuevos meses (tras actualizar la Tabla Dinámica), teniendo que modificar la fórmula manualmente. Gracias en cualquier caso.

    ResponderEliminar
    Respuestas
    1. Hola!!
      para poder trabajar con datos concretos dentro de una tabla dinámica, independientemente de en que celda se encuentren tras actualizar, debes tener activo en las Opciones de tabla dinámica el item Generar GetPivotData.
      Asi podrás relacionar con el dato que busques, esté donde esté..
      Espero te sirva.
      Slds

      Eliminar
    2. Hola!!

      En que parte aparece el Generar GetPivotData.???

      Eliminar
    3. Hola,
      en la cinta de opciones de la barra de herramientas de la tabla dinámica, un desplegable a la izquierda de la cinta, despliegas Opciones de tabla y verás la opción Generar GetPivotData
      Saludos

      Eliminar
  4. Por desgracia, mi versión de Excel 2003 no parece incluir esa opción... Pero te agradezco igualmente el aporte. Muy interesante. Gracias

    ResponderEliminar
    Respuestas
    1. Hola...
      no recuerdo muy bien, pero en Excel 2003, creo que si existe la misma opción.. Estará en otrositio.
      Slds

      Eliminar
  5. Cuales son las características de una tabal dinámica ?

    ResponderEliminar
    Respuestas
    1. Hola, que tal?, espero te encuentre igualemtne bien..

      Una tabla dinámica es una herramienta diseñada para agrupar y resumir información proveniente de un origen en forma de Tabla, además el diseño y configuración es cambiante, dinámica según determine el usuario.

      Espero te sirva como definición.

      Saludos

      Eliminar
  6. hola queria hacer una pregunta, intento marcar con un condicional en la tabla dinamica marcando distintas fechas (desde el 1-5 al 31-7 rojo, desde 1-8 al 31-10 amarillo el tema es que no puedo aplicarlo con iconos y cuando lo hago con el condicional y elijo otra geografica en la tabla se pierde el formato, como lo puedo resolver? muchisimas gracias

    ResponderEliminar
    Respuestas
    1. Hola Paula,
      no termino de entender bien el problema... pero parece que el problema es que no estés definiendo correctamente la regla del formato condicional ???

      Respecto a perder el FC al cambiar la estructura de campos, es normal, ya que el FC está asociado (normalmente) exactamente al cruce de campos que existiera en ese momento.. si cambias un campo por otro, perderás el FC.

      Si quieres envíame el fichero a
      excelforo@gmail.com

      con la explicación e intento darte una respuesta.
      Saludos

      Eliminar
  7. Buenas tardes,

    Me ha servido mucho tu post. Tengo un problema que no sé solventar relacionado con el tema y quizá puedas ayudarme.

    He aplicado los formatos condicionales en mis tablas dinámicas. Ha funcionado perfectamente tal cual explicas en el post; sin embargo, estos formatos no se han visto reflejados en en los gráficos dinámicos que tengo asociados a las tabla dinámicas. Esperaba que existiera este reflejo ya que tengo activada la casilla de Formato "vinculado al origen".

    Muchas gracias y un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      tal cosa no es posible.. el gráfico dinámico (o cualquier gráfico normal) no admitirá nunca (hasta la fecha) formatos condicionales con conjunto de iconos.
      Saludos

      Eliminar
    2. Muchas gracias por responder tan rápido y disculpa que en la primera ocasión no lo aclarase correctamente. Mi intención en el gráfico es poder mostrar indistintamente los datos en unidades o en miles de unidades (no es necesario el conjunto de iconos). Los formatos que cambio en la tabla dinámica y no tienen su reflejo en el gráfico son "#.##0" y "#.##0."
      Saludos

      Eliminar
    3. Hola,
      ten presente que un gráfico representa el valor que hay en la celda (sea o no de una tabla dinámica), que cambies el formato a redondeado a miles ("#.##0."), por tanto no afectará al gráfico.
      Deberías modificarlo en las opciones del eje del gráfico.
      Saludos

      Eliminar
  8. Estimado,

    mis tablas dinamicas borran los condicionales al actualizar, te a pasado? uso excel 2010

    ResponderEliminar
    Respuestas
    1. Hola Jonathan,
      no me ha ocurrido...
      ¿mantienes el campo con el formato condicional siempre en el tabla dinámica o a veces lo quitas y vuelves a meter?
      En ocasiones, el tema de formatos se ve afectado al realizar la segunda acción.
      Un saludo

      Eliminar
    2. A mi me ocurre lo mismo, al actualizar se borra, y eso que mantengo el campo. Saben solucionarlo?
      Gracias.

      Eliminar
    3. Hola que tal,
      un placer saludarte igualmente.
      Al crear un FC dentro de una tabla dinámica se asocia a un campo/s concretos, y mientras estén activos en la TD ese formato condicional se mantiene...
      ¿que versión de Excel manejas?
      Saludos

      Eliminar
    4. Hola,
      Excel 2016.
      El problema es que no se mantenía al actualizar la tabla.
      Creo que finalmente encontré la solución. Los formatos condicionales se han de crear como "nueva regla" i "...a todas las celdas según sus valores" . De esta manera no los cambia al actualizar la tabla. Pero ojo, si tenes que modificar alguno de los creados, bórralo y vuélvelo a crear, si lo editas, al actualizar la tabla, borrará todos los formatos anteriores.

      Espero sea de utilidad.

      Eliminar
    5. Muchas gracias por compartir solución!!

      Eliminar
    6. Gracias Tres me sirvió lo comentado para Excel 2010

      Eliminar
  9. Hola buen día, Una pregunta, tengo una tabla dianmica creada y ya tiene el formato condicional, al momento de agregar información a la base de datos de la tabla y actualizar la Pivot la regla del formato condicional se elimina, ¿existe alguna manera de que la condición se mantenga?

    ResponderEliminar
    Respuestas
    1. Hola,
      echa un vistazo al comentario anterior... (el 30nov2017 por Tres)..
      parece dar la solución
      Saludos

      Eliminar
  10. HOLA! tengo una tabla dinámica a la cual le quiero poner la condicional, tengo 5 columnas, la primera numero de parte, segunda descripción, tercera precio, cuarta punto de re-orden y quinta existencia. Quiero poner un formato condicional que aplique a todas las celdas de la columna existencia que si: existencia menor o igual a punto de re-orden color rojo y si: existencia mayor a punto de re-orden color verde, es como un inventario, pero solo he podido ir poniendo de celda por celda, y tengo mas de 10 mil artículos en existencia en mi base de datos, espero su ayuda saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      tienes que construir tu formato condicional desde Nueva regla, habiendo seleccionado el campo en la tabla dinámica a formatear, y en el campo editable incluir la fórmula que te referencie al otro campo.
      Por ejemplo te sitúas en C3:C20 y aplicaas formato condicional sobre B:
      =$c3>$B3
      siendo la columna C existencia y B re-orden
      e igualmente para el resto de condiciones
      Saludos

      Eliminar

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