viernes, 16 de julio de 2010

Gráficos de Excel: Trazar celdas vacías como...

Unos días atrás un usuario realizó una consulta vía email, quería saber si era posible que al dibujar una gráfica sobre los valores de una tabla de datos, aquellos valores vacíos no distorsionaran su gráfico.
La respuesta es sí, es posible; existe una herramienta dentro de los gráficos que permite tratar las celdas vacías de tres maneras posibles:
  1. como Rangos

  2. como Ceros

  3. como Conectar puntos de datos con línea, lo que siempre se había llamado Interpolar.


¿Cómo llegamos a seleccionar una u otra opción?, pues depende de si trabajamos con Excel 2003 o Excel 2007. Si aún trabajamos con Excel 2003, deberemos navegar al menú Herramientas > Opciones > Pestaña Gráfico
donde seleccionaremos Trazar celdas vacías como: Interpolar

Gráficos de Excel: Trazar celdas vacías como...



Si trabajamos con Excel 2007 con el gráfico activo, buscaremos la opción de 'Seleccionar datos', y dentro de ella el botón de Celdas ocultas y vacías, marcando la opción de conectar puntos de datos con línea:

Gráficos de Excel: Trazar celdas vacías como...



Pondré un ejemplo, supongamos una tabla de datos de la primera quincena de julio de 2010, sobre el que hemos construido nuestro gráfico de líneas:

Gráficos de Excel: Trazar celdas vacías como...



Como podemos ver existen saltos en la continuidad de nuestra línea, que repercuten negativamente en la interpretación de los datos. Por ello mediante lo comentado más arriba vamos a Interpolar o Conectar los puntos de datos, activamos la opción:

Gráficos de Excel: Trazar celdas vacías como...


Sólo nos queda ver el resultado final...

Gráficos de Excel: Trazar celdas vacías como...

21 comentarios:

  1. Pues ami no me funciona, y no se por que se sigue la linea a cero :(

    ResponderEliminar
  2. Lo que hace este opción es reemplazar el espacio vacio por un valor para darle continuidad a la línea del gráfico (en el caso de 'conectar puntos de datos con línea'), y asignarle un valor cero si es la opción seleccionada.
    Si no te funciona revisa los valores del gráfico y cómo lo has construido, ya que esta herramienta no tiene 'efectos escondidos'.
    Un saludo

    ResponderEliminar
  3. Que bueno me fue de mucha ayuda

    ResponderEliminar
  4. EXELENTE!!! JUSTO LO Q ESTABA BUSCANDO ME SIRVIO MUCHISIMO, GRACIAS!!!!

    ResponderEliminar
  5. Que pasa si tengo en la celda texto como n/a (No Aplica? la grafica igual se distorciona. :S

    ResponderEliminar
  6. Hola Luís,
    efectivamente los errores también distorsionan los gráficos (al igual que no teniendo valor), sin embargo, siempre podremos evitar ese error en el origen de datos mediante alguna función del tipo =SI.ERROR(valor; valor si error)
    o =SI(ESERROR(valor);valor si error; valor si no)
    es decir, evitaremos los errores antes de crear el gráfico.
    Espero te sirva.
    Slds

    ResponderEliminar
  7. En mi caso los datos de las celdas ocultas no se ven en la grafica. Sera porque las ocultan ejecutando una macro que he creado?. Incluso si oculto las pestañas donde esta la data, dejan de verse en los gráficos.
    Mi archivo es un *.xlsm.
    que puedo hacer para que se vean los graficos de datos en celdas ocultas por mi macro?

    ResponderEliminar
  8. Hola Gerard,
    para mostrar datos ocultos en un gráfico, deberás ir a Seleccionar datos y en el cuadro diálogo de Seleccionar origen de datos, presionar el botón de Celdas ocultas y vacías, lo que te abrirá una ventana de Configuración, desde la que marcar la opción de Mostrar datos en filas y columnas ocultas.
    En principio esto debería funcionarte, si tienes una macro que afecta este punto, habría que ver la macro.
    Prueba primero con lo comentado y me dices que tal.
    Un saludo

    ResponderEliminar
  9. Sr.(ta) ExcelForo:Gracias por responder, ahora,
    -Estas opciones ya las he marcado antes de consultar.
    -Respecto a la macro, esta solo ejecuta filtros para que mi archivo no se vea tan complejo y la Gerencia lo pueda ver mas resumido.
    -Es posible poder enviar mi archivo a algun mail?
    -Aca uno de los filtros:
    Selection.EntireColumn.Hidden = False

    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

    ActiveSheet.Range("$A$4:$BI$458").AutoFilter Field:=2, Criteria1:="RESUMEN"

    Columns("A:C").Select
    Selection.EntireColumn.Hidden = True

    Range("G456").Select

    ResponderEliminar
  10. Hola Gerard,
    envíame el fichero a
    excelforo@gmail.com
    le echaré un vistazo
    Un saludo

    ResponderEliminar
  11. Hola,

    he creado un gráfico que se está basando en una tabla que está creando valores. Lo que pasa es que no hay valores dentro de todas las celdas, sino hay celdas cuyo contenido es vacío ("") usado la fórmula: =SI(T3="CINE";Z3;"")
    Entonces en el gráfico todos los puntos cuya celda resultante es vacía ("") me demuestra con un punto en la eje de valor zero, cuando realmente quiere que no se demuestre nada allí, porque no hay valor.

    Cuál es el truco?

    GRACIAS

    ResponderEliminar
    Respuestas
    1. Hola,
      supongo que ya has probado lo explicado en este mismo post; Trazar valores como; si es así, y por algún motivo no funciona (aunque siempre funciona correctamente), prueba cambiando tu fórmula por:
      =SI(T3="CINE";Z3;NOD())
      Un saludo

      Eliminar
    2. La función NOD() funciona. Muchas gracias!!

      Eliminar
  12. buen dia,

    resulta que quiero graficar unas fechas ( meses), ejem, enero, marzo, mayo, julio, y cada mes tiene un valor mayor a 100, cuabdo lo grafico, me aparecen los meses de febrero, abril y junio y se grafican con un valor de cero,,, no se si me puedan ayudar, ya que no necesito esos valores en las graficas.

    ResponderEliminar
    Respuestas
    1. Hola,
      ¿qué tipo de gráfico tienes?
      supongo tu origen será (más o menos) una primera columnas con los meses indicados (en texto enero, marzo, mayo, etc... saltando-no existen-febrero, abril, etc) y otra columna al lado con una Cantidad.
      Excel al construir un gráfico sobre ese origen directamente obviará los meses inexistentes; salvo que el campo Meses,sean realmente fechas con algún formato tipo 'mmmm', en cuyo caso efectivamente te aparecerán todos los meses, y valor cero en aquellos que no haya dato.
      La solución sería en este caso en seleccionar las etiquetas del eje X y Dar formato a eje... > Opciones del eje > sección Tipo de eje: Eje de texto

      Espero te sirva.
      Slds

      Eliminar
  13. Buenos días Ismael,
    En mi caso se trata de un gráfico que compara precios semanales de varios proveedores. En muchos casos no hay ningún precio esa semana. He creado un resumen en el que le pido que me devuelva el valor de la celda cuando esta sea mayor que 0 (=SI(B14>0;B14;"")), y nada en caso contrario, pero parece que el gráfico interpreta que eso es 0 y no me conecta los puntos... Se te ocurre alguna solución?
    Gracias!

    ResponderEliminar
    Respuestas
    1. Hola, una solución habitual al usar gráficos es forzar un error para los puntos que no queremos representar:
      =SI(B14>0;B14;NOD())

      Espero te sirva.
      Slds cordiales

      Eliminar
  14. hola pero donde esta la opcion seleccionar datos en el excel 2008 para mac?

    ResponderEliminar
    Respuestas
    1. Hola Francisco,
      las funcionalidades e interfase de Mac es bastante diferente al de Windows, por lo que a veces cuesta compatibilizar explicaciones.
      Echa un vistazo a este link de MicroSoft
      http://office.microsoft.com/es-es/mac-excel-help/modificar-los-datos-en-un-grafico-HA102927334.aspx

      Al final del articulo despliega las opciones para Excel Mac.

      Saludos

      Eliminar
  15. Hola!
    Y si tenes valores cero y no queres que aparezcan pq no es que el valor es cero, sino q, por ejemplo, en un mes no hubo actividad? Como puedo hacer para que la linea vaya del mes (o día como en tu ejemplo) que tuvo actividad al siguiente que tuvo actividad sin bajar al cero? Yo sólo conozco la opción de poner #N/A en la celda, pq desde la opciones de "Celdas ocultas o vacías" no encuentro la opción esa, todas me bajan hasta el cero? Vos tenes idea? Saludos y desde ya mil gracias!!

    ResponderEliminar
    Respuestas
    1. Hola,
      pues habría que incluir en la fórmula lo que comentas
      =SI(operación=0;NOD();operación)
      siendo la operación la que devuelve un valor a representar en el gráfico o cero, reemplazado con el error #N/A.. con lo que ya podrías configurarlo...

      También
      =SI(operación=0;"";operación)

      Saludos

      Eliminar