Análisis de datos de Excel: visualización de datos

Puede mostrar sus informes de análisis de datos de varias formas en Excel. Sin embargo, si los resultados de su análisis de datos se pueden visualizar como gráficos que resaltan los puntos notables en los datos, su audiencia puede captar rápidamente lo que desea proyectar en los datos. También deja un buen impacto en su estilo de presentación.

En este capítulo, aprenderá a utilizar gráficos de Excel y funciones de formato de Excel en gráficos que le permitirán presentar los resultados de sus análisis de datos con énfasis.

Visualización de datos con gráficos

En Excel, los gráficos se utilizan para hacer una representación gráfica de cualquier conjunto de datos. Un gráfico es una representación visual de los datos, en la que los datos están representados por símbolos como barras en un gráfico de barras o líneas en un gráfico de líneas. Excel le proporciona muchos tipos de gráficos y puede elegir uno que se adapte a sus datos o puede usar la opción Gráficos recomendados de Excel para ver gráficos personalizados para sus datos y seleccionar uno de ellos.

Consulte el Tutorial Gráficos de Excel para obtener más información sobre los tipos de gráficos.

En este capítulo, comprenderá las diferentes técnicas que puede utilizar con los gráficos de Excel para resaltar los resultados del análisis de datos de manera más eficaz.

Crear gráficos combinados

Suponga que tiene las ganancias objetivo y reales para el año fiscal 2015-2016 que obtuvo de diferentes regiones.

Crearemos un gráfico de columnas agrupadas para estos resultados.

Como observa, es difícil visualizar rápidamente la comparación entre los objetivos y los reales en este gráfico. No da un verdadero impacto en sus resultados.

Una mejor forma de distinguir dos tipos de datos para comparar los valores es mediante el uso de gráficos combinados. En Excel 2013 y versiones anteriores, puede usar gráficos combinados para el mismo propósito.

Utilice columnas verticales para los valores objetivo y una línea con marcadores para los valores reales.

• Haga clic en la pestaña DISEÑO debajo de la pestaña HERRAMIENTAS DE GRÁFICOS en la cinta.
• Haga clic en Cambiar tipo de gráfico en el grupo Tipo. Aparece el cuadro de diálogo Cambiar tipo de gráfico.

• Haga clic en Combo.

• Cambie el Tipo de gráfico de la serie Real a Línea con marcadores. La vista previa aparece en Combinación personalizada.

• Haga clic en Aceptar.

Se mostrará su tabla de combinación personalizada.

Como observa en el gráfico, los valores de destino están en columnas y los valores reales están marcados a lo largo de la línea. La visualización de datos ha mejorado ya que también le muestra la tendencia de sus resultados.

Sin embargo, este tipo de representación no funciona bien cuando los rangos de datos de sus dos valores de datos varían significativamente.

Creación de un gráfico combinado con eje secundario

Suponga que tiene los datos sobre la cantidad de unidades de su producto que se enviaron y las ganancias reales para el año fiscal 2015-2016 que obtuvo de diferentes regiones.

Si usa la misma tabla de combinación que antes, obtendrá lo siguiente:

En el gráfico, los datos de No. of Units no es visible ya que los rangos de datos varían significativamente.

En tales casos, puede crear un gráfico de combinación con eje secundario, de modo que el eje primario muestre un rango y el eje secundario muestre el otro.

• Haga clic en la pestaña INSERTAR.
• Haga clic en Combo en el grupo Gráficos.
• Haga clic en Crear gráfico combinado personalizado en la lista desplegable.

Aparece el cuadro de diálogo Insertar gráfico con Combo resaltado.

Para Tipo de gráfico, elija -

• Línea con marcadores para la serie No. de unidades

• Columna agrupada para los beneficios reales de la serie

• Marque la casilla Eje secundario a la derecha del número de serie de unidades y haga clic en Aceptar.

Aparecerá una vista previa de su gráfico en Combinación personalizada.

Su gráfico combinado aparece con el eje secundario.

Puede observar los valores de Beneficios reales en el eje primario y los valores de No. de Unidades en el eje secundario.

Una observación importante en el gráfico anterior es para el tercer trimestre, donde el número de unidades vendidas es mayor, pero las ganancias reales obtenidas son menores. Esto probablemente podría asignarse a los costos de promoción en los que se incurrió para aumentar las ventas. La situación mejora en el cuarto trimestre, con una leve disminución de las ventas y un aumento significativo de las utilidades reales obtenidas.

Eje de categorías y series discriminatorias

Suponga que desea proyectar las ganancias reales obtenidas en los años 2013-2016.

Cree una columna agrupada para estos datos.

Como observa, la visualización de datos no es efectiva ya que no se muestran los años. Puede superar esto cambiando de año a categoría.

Elimina el año del encabezado en el rango de datos.

Ahora, el año se considera una categoría y no una serie. Su gráfico se ve de la siguiente manera:

Elementos y estilos de gráficos

Los elementos de gráfico brindan más descripciones a sus gráficos, lo que ayuda a visualizar sus datos de manera más significativa.

• Haga clic en el gráfico

Aparecen tres botones junto a la esquina superior derecha del gráfico:

• Elementos del gráfico
• Estilos de gráficos
• Filtros de gráficos

Para obtener una explicación detallada de estos, consulte el tutorial de gráficos de Excel.

• Haga clic en Elementos del gráfico.
• Haga clic en Etiquetas de datos.

• Haga clic en Estilos de gráfico
• Seleccione un estilo y color que se adapte a sus datos.

Puede utilizar Trendline para mostrar gráficamente las tendencias en los datos. Puede extender una línea de tendencia en un gráfico más allá de los datos reales para predecir valores futuros.

Etiquetas de datos

Excel 2013 y versiones posteriores le ofrecen varias opciones para mostrar etiquetas de datos. Puede elegir una etiqueta de datos, formatearla como desee y luego usar Clonar etiqueta actual para copiar el formato al resto de las etiquetas de datos en el gráfico.

Las etiquetas de datos de un gráfico pueden tener efectos de diferentes formas y tamaños.

También es posible mostrar el contenido de una celda como parte de la etiqueta de datos con Insertar campo de etiqueta de datos.

Diseño rápido

Puede utilizar Diseño rápido para cambiar el diseño general del gráfico rápidamente eligiendo una de las opciones de diseño predefinidas.

• Haga clic en el gráfico.
• Haga clic en la pestaña DISEÑO debajo de HERRAMIENTAS DE GRÁFICOS.
• Haga clic en Diseño rápido.

Se mostrarán diferentes diseños posibles. A medida que avanza en las opciones de diseño, el diseño del gráfico cambia a esa opción en particular.

Seleccione el diseño que le guste. El gráfico se mostrará con el diseño elegido.

Usar imágenes en gráficos de columnas

Puede crear más énfasis en la presentación de datos utilizando una imagen en lugar de columnas.

• Haga clic en una columna en el gráfico de columnas.

• En Formato de serie de datos, haga clic en Rellenar.

• Seleccione Imagen.

• En Insertar imagen de, proporcione el nombre del archivo o, opcionalmente, el portapapeles si había copiado una imagen anteriormente.

La imagen que ha elegido aparecerá en lugar de columnas en el gráfico.

Gráfico de bandas

Es posible que deba presentar los resultados de la encuesta a clientes de un producto de diferentes regiones. Band Chart es adecuado para este propósito. Un gráfico de bandas es un gráfico de líneas con un área sombreada adicional para mostrar los límites superior e inferior de los grupos de datos.

Suponga que los resultados de su encuesta de clientes de las regiones este y oeste, por mes, son:

Aquí, en los datos <50% es Bajo, 50% - 80% es Medio y> 80% es Alto.

Con Band Chart, puede mostrar los resultados de su encuesta de la siguiente manera:

Cree un gráfico de líneas a partir de sus datos.

Cambie el tipo de gráfico a -

• Serie Este y Oeste para alinear con marcadores.
• Serie baja, media y alta a columna apilada.

Su gráfico tiene el siguiente aspecto.

• Haga clic en una de las columnas.
• Cambie el ancho del espacio a 0% en Format Data Series.

Obtendrá bandas en lugar de columnas.

Para que el gráfico sea más presentable:

• Agregar título de gráfico.
• Ajuste el rango del eje vertical.
• Cambie los colores de las bandas a Verde-Amarillo-Rojo.
• Agregue etiquetas a las bandas.

El resultado final es el gráfico de bandas con los límites definidos y los resultados de la encuesta representados en las bandas. Uno puede distinguir rápida y claramente del gráfico que si bien los resultados de la encuesta para la región Oeste son satisfactorios, los de la región Este tienen una disminución en el último trimestre y necesitan atención.

Tabla de termómetro

Cuando tenga que representar un valor objetivo y un valor real, puede crear fácilmente un gráfico de termómetro en Excel que muestre enfáticamente estos valores.

Con el gráfico de termómetro, puede mostrar sus datos de la siguiente manera:

Organice sus datos como se muestra a continuación:

• Seleccione los datos.
• Cree un gráfico de columnas agrupadas.

Como observa, la columna del lado derecho es Target.

• Haga clic en una columna del gráfico.
• Haga clic en Cambiar fila / columna en la cinta.

• Haga clic derecho en la columna de destino.
• Haga clic en Formatear serie de datos.
• Haga clic en Eje secundario.

Como observa, el eje primario y el eje secundario tienen rangos diferentes.

• Haga clic derecho en el eje principal.
• En las opciones de Formato de eje, en Límites, escriba 0 para Mínimo y 1 para Máximo.
• Repita lo mismo para el eje secundario.

Tanto el eje primario como el eje secundario se establecerán en 0% - 100%. La columna de destino oculta la columna real.

• Haga clic derecho en la columna visible (Destino)
• En Format Data Series, seleccione
  • No hay relleno para FILL
  • Línea continua para BORDER
  • Azul para el color

• En Elementos del gráfico, anule la selección
  • Eje → Horizontal principal
  • Eje → Vertical secundario
  • Gridlines
  • Titulo del gráfico
• En el gráfico, haga clic derecho en Eje vertical principal
• En las opciones de Formato de eje, haga clic en MARCAS
• Para el tipo Mayor, seleccione Interior

• Haga clic derecho en el área del gráfico.
• En las opciones de Formato del área del gráfico, seleccione
  • No hay relleno para FILL
  • No hay línea para BORDER

Cambie el tamaño del área del gráfico para obtener la forma de un termómetro.

Obtuvo su gráfico de termómetro, con el valor real en comparación con el valor objetivo que se muestra. Puede hacer que este gráfico de termómetro sea más impresionante con algún formato.

• Inserte una forma de rectángulo superponiendo la parte rectangular azul en el gráfico.
• En las opciones de Formato de forma, seleccione -
  • Relleno degradado para FILL
  • Lineal para tipo
  • 180 ⁰ para ángulo
• Establezca las paradas de degradado en 0%, 50% y 100%.
• Para el degradado se detiene en 0% y 100%, elija el color negro.
• Para la parada de degradado al 50%, elija el color blanco.

• Inserta una forma ovalada en la parte inferior.
• Formatee la forma con las mismas opciones.

El resultado es el gráfico de termómetro con el que comenzamos.

Gráfico de gantt

Un diagrama de Gantt es un diagrama en el que una serie de líneas horizontales muestra la cantidad de trabajo realizado en ciertos períodos de tiempo en relación con la cantidad de trabajo planificada para esos períodos.

En Excel, puede crear un diagrama de Gantt personalizando un tipo de diagrama de barras apiladas para que represente las tareas, la duración de las tareas y la jerarquía. Un diagrama de Gantt de Excel generalmente usa días como unidad de tiempo a lo largo del eje horizontal.

Considere los siguientes datos donde la columna -

• La tarea representa las tareas en el proyecto.
• El inicio representa el número de días desde la fecha de inicio del proyecto.
• La duración representa la duración de la tarea

Tenga en cuenta que el inicio de cualquier tarea es el inicio de la tarea anterior + duración. Este es el caso cuando las Tareas están en jerarquía.

• Seleccione los datos.
• Crear gráfico de barras apiladas.

• Haga clic con el botón derecho en Iniciar serie.
• En las opciones de Formato de serie de datos, seleccione Sin relleno.

• Haga clic con el botón derecho en Eje de categorías.
• En las opciones de Formato de eje, seleccione Categorías en orden inverso.

• En Elementos del gráfico, anule la selección
  • Legend
  • Gridlines
• Dar formato al eje horizontal para
  • Ajustar el rango
  • Marcas de graduación importantes en intervalos de 5 días
  • Marcas de graduación menores en intervalos de 1 día
• Formatee la serie de datos para que se vea impresionante
• Dar un título de gráfico

Gráfico de cascada

Waterfall Chart es una de las herramientas de visualización más populares utilizadas en pequeñas y grandes empresas. Los gráficos en cascada son ideales para mostrar cómo ha llegado a un valor neto, como el ingreso neto, al desglosar el efecto acumulativo de las contribuciones positivas y negativas.

Excel 2016 proporciona el tipo de gráfico en cascada. Si está utilizando versiones anteriores de Excel, aún puede crear un gráfico de cascada utilizando el gráfico de columnas apiladas.

Las columnas están codificadas por colores para que pueda distinguir rápidamente los números positivos de los negativos. Las columnas de valor inicial y final comienzan en el eje horizontal, mientras que los valores intermedios son columnas flotantes. Debido a este aspecto, los gráficos en cascada también se denominan gráficos puente.

Considere los siguientes datos.

• Prepare los datos para el gráfico de cascada

• Asegúrese de que la columna Flujo de caja neto esté a la izquierda de la Columna de meses (esto se debe a que no incluirá esta columna al crear el gráfico)

• Agregue 2 columnas: aumento y disminución para los flujos de efectivo positivos y negativos respectivamente

• Agregue una columna Inicio: la primera columna del gráfico con el valor inicial en el flujo de efectivo neto

• Agregue una columna Fin: la última columna del gráfico con el valor final en el flujo de caja neto

• Agregue una columna flotante, que admita las columnas intermedias

• Calcule los valores de estas columnas de la siguiente manera

• En la columna Flotante, inserte una fila al principio y al final. Coloque n valor arbitrario 50000. Esto solo para tener algo de espacio a la izquierda y a la derecha del gráfico.

Los datos serán los siguientes.

• Seleccione las celdas C2: H18 (Excluir la columna de flujo de efectivo neto)
• Crear gráfico de columnas apiladas

• Haga clic derecho en Float Series.
• Haga clic en Dar formato a serie de datos.
• En las opciones de Formato de serie de datos, seleccione Sin relleno.

• Haga clic derecho en Negative Series.
• Seleccione Color de relleno como rojo.

• Haga clic derecho en Positive Series.
• Seleccione Color de relleno como verde.

• Haga clic derecho en Start Series.
• Seleccione Color de relleno como gris.
• Haga clic derecho en Finalizar serie.
• Seleccione Color de relleno como gris.
• Eliminar la leyenda.

• Haga clic derecho en cualquier Serie
• En las opciones de Formato de serie de datos, seleccione Ancho de espacio como 10% en Opciones de serie

Dar el título del gráfico. Se mostrará el gráfico de cascada.

Minigráficos

Los minigráficos son gráficos diminutos colocados en celdas individuales, cada una de las cuales representa una fila de datos en su selección. Proporcionan una forma rápida de ver las tendencias.

Puede agregar minigráficos con la herramienta Análisis rápido.

• Seleccione los datos para los que desea agregar minigráficos.
• Mantenga una columna vacía al lado derecho de los datos para los minigráficos.

El botón Análisis rápido aparece en la parte inferior derecha de los datos seleccionados.

• Haga clic en el botón Análisis rápido . Aparece la barra de herramientas de análisis rápido con varias opciones.

Hacer clic SPARKLINES. Las opciones de gráficos que se muestran se basan en los datos y pueden variar.

Hacer clic Line. Se muestra un gráfico de líneas para cada fila en la columna a la derecha de los datos.

Gráficos dinámicos

Los gráficos dinámicos se utilizan para resumir gráficamente datos y explorar datos complicados.

Un gráfico dinámico muestra series de datos, categorías y ejes de gráficos de la misma manera que lo hace un gráfico estándar. Además, también le brinda controles de filtrado interactivos directamente en el gráfico para que pueda analizar rápidamente un subconjunto de sus datos.

Los gráficos dinámicos son útiles cuando tiene datos en una tabla dinámica enorme o muchos datos de hojas de trabajo complejas que incluyen texto y números. Un gráfico dinámico puede ayudarlo a comprender estos datos.

Puede crear un gráfico dinámico desde

• Una tabla dinámica.
• Una tabla de datos independiente sin tabla dinámica.

Gráfico dinámico de tabla dinámica

Para crear un gráfico dinámico, siga los pasos que se indican a continuación:

• Haga clic en la tabla dinámica.
• Haga clic en ANALIZAR debajo de HERRAMIENTAS PIVOTABLES en la cinta.
• Haga clic en PivotChart. Aparece el cuadro de diálogo Insertar gráfico.

Seleccione Columna agrupada en la opción Columna.

Haga clic en Aceptar. Se muestra el gráfico dinámico.

El gráfico dinámico tiene tres filtros: Región, Vendedor y Mes.

• Haga clic en la opción Control de filtro de región. Aparece el cuadro de búsqueda con la lista de todas las regiones. Aparecen casillas de verificación junto a Regiones.

• Seleccione las opciones Este y Sur.

Los datos filtrados aparecen tanto en el gráfico dinámico como en la tabla dinámica.

Gráfico dinámico sin tabla dinámica

Puede crear un gráfico dinámico independiente sin crear una tabla dinámica.

• Haga clic en la tabla de datos.
• Haga clic en la pestaña Insertar.
• Haga clic en Gráfico dinámico en el grupo Gráficos. Aparece la ventana Crear gráfico dinámico.
• Seleccione la Tabla / Rango.
• Seleccione la ubicación donde desea que se coloque el gráfico dinámico.

Puede elegir una celda en la propia hoja de trabajo existente o en una nueva hoja de trabajo. Haga clic en Aceptar.

Un gráfico dinámico y una tabla dinámica vacíos aparecen junto con la lista de campos del gráfico dinámico para crear el gráfico dinámico.

• Elija los campos que se agregarán al gráfico dinámico

• Organice los campos arrastrándolos a FILTROS, LEYENDA (SERIE), EJE (CATEGORÍAS) y VALORES

• Utilice los controles de filtro en el gráfico dinámico para seleccionar los datos que se colocarán en el gráfico dinámico

Excel creará automáticamente una tabla dinámica acoplada.