Cuadro mixto en excel. Combinando diferentes tipos de gráficos en EXCEL

Creación de gráficos combinados

En los casos en los que necesite mostrar datos de diferentes escalas en un gráfico, resulta útil utilizar gráficos combinados. Un caso típico son los indicadores absolutos (rublos) y relativos (porcentaje). Por ejemplo, el tamaño de las cuentas por cobrar y su participación en las ventas.

Conjunto (o mezclado ) es un gráfico que consta de varias series de datos y utiliza diferentes tipos de gráficos al mismo tiempo (por ejemplo, un histograma y un gráfico). Para crear gráficos combinados, necesita al menos dos series de datos. En el libro se publican ejemplos de diagramas mixtos. Conjunto

Existen algunas restricciones a la hora de crear gráficos combinados:

· es imposible mezclar cualquier tipo de gráfico con tipos volumétricos;

· algunas combinaciones de tipos de gráficos parecen extremadamente insatisfactorias (por ejemplo, una combinación de gráfico y gráfico de radar);

· un gráfico mixto utiliza una única área de trazado, por lo que es imposible crear, por ejemplo, un gráfico combinado que consta de tres gráficos circulares;

· No se admite la combinación de un gráfico y un gráfico de barras: el eje de categorías de un gráfico de barras siempre está dirigido verticalmente y el eje de un gráfico siempre está dirigido horizontalmente.

La figura muestra un gráfico creado con tres series de datos. Además, las temperaturas del aire y del agua se presentan en forma de histograma y la cantidad de precipitación se presenta en un gráfico.

La figura muestra un histograma creado con dos series de datos:

Cambiemos el tipo de gráfico para la segunda serie de datos ( Precipitación) y utilice un eje de valores independiente para esta serie.

1. Seleccione una serie de datos en el diagrama. Precipitación y llamar al menú contextual de la fila.

2. Abre la ventana Formato de serie de datos (Formato de serie de datos) y en la pestaña Opciones fila (Opciones de serie) instalar el interruptor Por auxiliar ejes(Eje secundario).

3. Sin eliminar la selección de la fila, ejecute el comando Trabajar con gráficos– Constructor – Tipo – Cambiar tipo diagramas(Cuadro Herramientas – Diseño – Tipo – Cambiar Cuadro Tipo) .

4. En el cuadro de diálogo Cambiar tipo de gráfico, seleccione el tipo Cronograma (Línea) y haga clic en el botón DE ACUERDO.

Los datos de precipitación ahora se visualizan como segmentos de línea recta, con uno nuevo a la derecha. Eje de valor (ValorAxis).

Es importante entender que el equipo Trabajar con gráficos– Constructor – Tipo – Cambiar tipo diagramas (Cuadro Herramientas – Diseño – Tipo – Cambiar Cuadro Tipo) funciona de manera diferente dependiendo de lo que se seleccione. Si se selecciona una serie de gráficos, el comando cambia el tipo solo de esa serie. Si se selecciona cualquier otro elemento del diagrama, el comando cambia el tipo de todo el diagrama.

Cubrir diagramas (ohverlayCciervos)

La superposición de gráficos se refiere a colocar gráficos como objetos gráficos: Uno encima del otro. Un gráfico superior suele tener un área de gráfico transparente y un área de trazado. Elimina todos los elementos excepto, por ejemplo, marcadores y líneas. La superposición de gráficos es una tarea manual que requiere un posicionamiento preciso de los objetos y la configuración de ejes de valores y categorías.

Para organizar los diagramas "apilados" en el orden apropiado, es necesario seleccionarlos como objetos. Para seleccionar un objeto en lugar de un gráfico, presione Control y haga clic en el diagrama.

Veamos un ejemplo sencillo (hoja Superposición_1 libros Conjunto). Se sabe que una de las limitaciones a la hora de crear gráficos combinados es la imposibilidad de mezclar cualquier tipo de gráfico con tipos volumétricos. El método de superposición de gráficos le permite combinar un gráfico y un histograma volumétrico en una hoja. Sigamos los siguientes pasos:

1. Seleccione un rango en la tabla de datos. A2:B7 y construir un histograma volumétrico con agrupación. Eliminemos la leyenda del gráfico.

2. Seleccione rangos no adyacentes A2:A7 Y C2:C7 y construye una gráfica con marcadores. Eliminemos la leyenda del gráfico.

3. En el segundo gráfico, establezca un relleno transparente para el área del gráfico y el área de trazado del gráfico, y elimine los ejes. Démos formato a las líneas y marcadores del gráfico.

4. Combine manualmente el gráfico y el histograma volumétrico. Al cambiar el tamaño del objeto “Gráfico”, nos aseguraremos de que los marcadores del gráfico coincidan con la mitad de las columnas del histograma.

5. Después del formato final obtendremos un diagrama superpuesto.

6. Si, después de seleccionar dos diagramas como objetos gráficos, los agrupa, el diagrama superpuesto resultante se copiará y moverá como un solo objeto. Tenga en cuenta que intentar colocar el gráfico en una hoja separada destruirá la superposición.

La siguiente ilustración muestra una superposición de un gráfico circular 3D y un histograma 3D. El marco general da la impresión de que se trata de un solo diagrama. Sin embargo, estos son dos diagramas separados. El marco no les pertenece a ellos, sino a las celdas de la hoja de trabajo. El título es una inscripción que se puede mover libremente. Los datos para dicha superposición se dan en la hoja. Circular _volumétrico libros Conjunto.

Los histogramas volumétricos de Excel no le permiten mostrar filas adicionales en profundidad. La superposición de gráficos supera esta limitación. Basta construir tres histogramas volumétricos con acumulación (para cada año por separado) y combinarlos para “crear” un tercer eje en profundidad. El gráfico le permite comparar visualmente los resultados según tres parámetros: regiones, meses y años. La tabla de datos original está en la hoja de trabajo. tres_volumen libros Conjunto.

Las herramientas estándar de Excel para gráficos circulares le permiten utilizar solo un conjunto de datos. Esta publicación le mostrará cómo crear un gráfico circular basado en dos conjuntos de valores.

Como ejemplo, tomé la población de la Tierra por continente en 1950 y 2000. (consulte la hoja "Población" en el archivo de Excel; eliminé Australia porque su participación es insignificante y el diagrama se vuelve difícil de leer :)). Primero, cree un gráfico circular básico: seleccione el rango A1:C6, vaya a Insertar → Circular → Circular.

Arroz. 1. Crea un gráfico circular normal

Descarga la nota en formato, ejemplos en formato

Haga clic derecho en el gráfico y seleccione Formatear serie de datos en el menú contextual. Seleccione "A lo largo del eje menor" y luego mueva el control deslizante hacia "Separación" hasta algo así como 70% (Figura 2). Los sectores de la misma fila se “separarán”.

Arroz. 2. A lo largo del eje menor

Seleccione sectores individuales secuencialmente (haciendo doble clic lentamente) y cambie su relleno y ubicación, conectando todos los sectores en el centro (Fig. 3).

Arroz. 3. Formato de puntos de fila (sectores individuales)

Formatee todos los sectores para que los colores correspondientes al mismo continente en diferentes filas sean del mismo rango, pero de diferentes intensidades. Complete el cuadro con etiquetas de datos, una leyenda y un título (Figura 4).

Arroz. 4. Gráfico circular con dos conjuntos de datos

El diagrama muestra claramente, por ejemplo, que la participación de Asia ha aumentado del 55,8% al 60,9% en 50 años, mientras que la participación de Europa ha disminuido del 21,8% al 12,1% durante el mismo tiempo.

Si no eres fanático de los gráficos circulares, puedes usar el gráfico de anillos, que en el estándar de Excel funciona con múltiples conjuntos de datos (Fig. 5); consulte también la hoja "Anillo" en el archivo de Excel. Seleccione el área de datos (en nuestro ejemplo es A1:C6) y vaya al menú Insertar – Gráficos – Otros gráficos – Donut:

Arroz. 5. Crea un gráfico de anillos

Lo único que tienes que hacer es editar un poco el diagrama para hacerlo más visual (Fig. 6)

Arroz. 6. Gráfico de anillos

La idea fue vista en el libro de D. Kholey, R. Kholey “Excel 2007. Trucos”.

Un histograma en Excel es una forma de crear un gráfico visual que muestra cambios en varios tipos de datos durante un período de tiempo.

Un histograma es útil para ilustrar y comparar diferentes parámetros. Veamos los tipos de histogramas más populares y aprendamos cómo crearlos.

¿Cómo construir un histograma actualizado?

Disponemos de datos de ventas de diferentes tipos de productos lácteos para cada mes del año 2015.

Construyamos un histograma actualizado que responderá a los cambios realizados en la tabla. Seleccione toda la matriz, incluido el encabezado, y haga clic en la pestaña INSERTAR. Busquemos DIAGRAMAS - HISTOGRAMA y seleccionemos el primer tipo. Se llama HISTOGRAMA CON AGRUPACIÓN.

Recibimos un histograma cuyo tamaño de campo se puede cambiar. Este diagrama muestra claramente, por ejemplo, que las mayores ventas fueron de leche en noviembre y las menores de nata en junio.

Si hacemos cambios en la tabla, apariencia el histograma también cambiará. Por ejemplo, en lugar de 1400 en enero para el kéfir, pongamos 4000. Vemos cómo la columna verde voló hacia arriba.

histograma apilado

Ahora veamos cómo construir un histograma apilado en Excel. Otro tipo de histograma que te permite mostrar datos como porcentaje. Se construye exactamente de la misma forma, pero se elige un tipo diferente.

Nos sale un diagrama donde se puede ver que, por ejemplo, en enero se vendió más leche que kéfir o nata. Y en agosto, en comparación con otros lácteos, se vendió poca leche. Etcétera.

Los histogramas en Excel se pueden modificar. Así, si hacemos clic derecho en una zona vacía del diagrama y seleccionamos CAMBIAR TIPO, podremos modificarlo un poco. Cambiemos el tipo de nuestro histograma de apilado a normalizado. El resultado será el mismo gráfico, pero el eje Y mostrará las proporciones en términos porcentuales.

De manera similar, puedes realizar otros cambios en el histograma, que es lo que hicimos nosotros:

• cambió la fuente a Arial y cambió su color a violeta;
• hizo un subrayado con una línea de puntos;
• movió la leyenda un poco más arriba;
• Se agregaron títulos a las columnas.

¿Cómo combinar un histograma y una gráfica en Excel?

Algunos conjuntos de datos requieren la construcción de diagramas más complejos que combinen varios tipos de datos. Por ejemplo, un histograma y una gráfica.

Veamos un ejemplo. Primero, agreguemos otra fila a la tabla con los datos, donde se registran los ingresos de cada mes. Está indicado en rublos.

Ahora modifiquemos el diagrama existente. Haga clic derecho en un espacio vacío y seleccione SELECCIONAR DATOS. Aparecerá un campo pidiéndole que seleccione un intervalo diferente. Volvemos a seleccionar toda la tabla, pero esta vez incluyendo la fila con ingresos.

Excel amplió automáticamente el rango de valores a lo largo del eje Y, por lo que los datos sobre el número de ventas permanecieron en la parte inferior en forma de columnas invisibles.

Pero tal histograma es incorrecto, porque en un diagrama tenemos números en rublos y equivalentes cuantitativos (rublos y litros). Por lo tanto es necesario hacer cambios. Movamos los datos de ingresos al lado derecho. Haga clic derecho en las columnas moradas, seleccione FORMATO DE SERIE DE DATOS y seleccione EJE AUXILIAR.

Vemos que el horario ha cambiado inmediatamente. Ahora la barra de ingresos violeta tiene su propia área de valor (a la derecha).

Pero esto todavía no es muy conveniente, porque las columnas casi se fusionan. Por tanto, realizaremos una acción adicional más: hacer clic derecho en las columnas moradas y seleccionar CAMBIAR TIPO DE GRÁFICO PARA SERIE. Aparecerá una ventana en la que seleccionamos un gráfico, el primer tipo.

Obtenemos un diagrama muy claro, que es una combinación de un histograma y un gráfico. Vemos que los ingresos máximos fueron en enero y noviembre, y los mínimos en agosto.

En Excel, puede mostrar los resultados de los cálculos en forma de cuadro o gráfico, dándoles mayor claridad y, para comparar, a veces es necesario crear dos gráficos uno al lado del otro. Veremos más a fondo cómo construir dos gráficos en Excel en un campo.

Comencemos con el hecho de que no todos los tipos de gráficos en Excel pueden mostrar exactamente el resultado que esperamos. Por ejemplo, existen resultados de cálculo para varias funciones basándose en los mismos datos iniciales. Si construye un histograma o gráfico regular a partir de estos datos, durante la construcción no se tendrán en cuenta los datos originales, sino solo su cantidad, entre los cuales se especificarán intervalos iguales.

Seleccionamos dos columnas de resultados de cálculo y construimos un histograma regular.

Ahora intentemos agregar otro histograma a los existentes con la misma cantidad de resultados de cálculo. Para agregar un gráfico en Excel, active el gráfico existente seleccionándolo y en la pestaña que aparece "Constructor" elegir "Seleccionar datos". En la ventana que aparece en la sección "Elementos de leyenda" haga clic en agregar y especifique celdas "Nombre de fila:" Y "Valores:" en la hoja, que serán los valores de cálculo de la función "j".

Ahora veamos cómo se verá nuestro gráfico si agregamos otro a los histogramas existentes, que tiene casi el doble de valores. Agreguemos los valores de la función a la gráfica. "k".

Como puede ver, hay valores agregados mucho más recientemente y son tan pequeños que son prácticamente invisibles en el histograma.

Si cambiamos el tipo de gráfico de histograma a gráfico normal, el resultado en nuestro caso será más claro.

Si utiliza un diagrama de dispersión para crear gráficos en Excel, los gráficos resultantes tendrán en cuenta no solo los resultados del cálculo, sino también los datos originales, es decir. Habrá una relación clara entre las cantidades.

Para crear un gráfico de puntos, seleccione una columna de valores iniciales y un par de columnas de resultados para dos funciones diferentes. en la pestaña "Insertar" seleccione un diagrama de dispersión con curvas suaves.

Para agregar otro gráfico, seleccione los existentes y en la pestaña "Constructor" prensa "Seleccionar datos".

En una nueva ventana en la columna "Elementos de leyenda" prensa "Agregar", e indique las celdas para "Nombre de fila:", "Valores X:" Y "Valores Y:". Agreguemos la función de esta manera. "j" al gráfico.

Normalmente, los gráficos utilizan un estilo, como un gráfico de columnas, de barras o circular. Si su gráfico tiene más de una serie de datos, puede mostrar diferentes estilos en su gráfico, creando un gráfico mixto.

En la Fig. La figura 136.1 muestra un diagrama muy adecuado para la conversión a mixto. El gráfico ilustra la temperatura y precipitación promedio por mes. Pero hay un problema. Los dos rangos de datos difieren claramente en magnitud, por lo que las barras de precipitación apenas son visibles en el gráfico.

En la Fig. La figura 136.2 es el diagrama después de convertirlo en un diagrama mixto. Los valores de precipitación ahora se representan como una línea y se utiliza el segundo eje vertical desde la derecha. La mayoría de ustedes estarán de acuerdo en que este gráfico es una mejora significativa con respecto al original. Puede usarlo al analizar el costo de los productos en una joyería en Vologda.

Puede buscar el comando Gráficos mixtos en la cinta de Excel todo el día y aún así no lo encontrará. Si ya creó su gráfico (que tiene al menos dos series de datos), puede convertirlo fácilmente en un gráfico mixto. Sigue estos pasos:

1. Haga clic en las filas que desea cambiar.
2. Seleccionar Trabajar con gráficos Tipo de diseñador Cambiar tipo de gráfico.
3. En el cuadro de diálogo Cambiar tipo de gráfico, seleccione el icono que representa el tipo de gráfico para la serie seleccionada.
4. Si desea utilizar un segundo eje vertical para la serie convertida, seleccione la serie y haga clic en Ctrl+1 para abrir un cuadro de diálogo Formato de serie de datos; ir a la sección Parámetros de la serie y coloque el interruptor construir una fila posicionar A lo largo del eje menor.

Diagrama en la Fig. 136.1 era un gráfico de barras estándar con dos filas. Unos pocos clics y una de las series (Precipitación en este ejemplo) se convierte de columnas a una línea. También agregué un segundo eje y etiqueté los ejes para que quedara claro qué escala se estaba usando para qué serie. Para agregar títulos de ejes, ejecute el comando Trabajar con títulos de ejes de diseño de gráficos.

En la Fig. La figura 136.3 muestra otro diagrama mixto. Combina un gráfico de columnas y de barras, y ambas series utilizan el mismo eje vertical. En este caso, mostrar los objetivos de ventas mensuales como un gráfico de barras facilita encontrar los meses en los que se cumplió el objetivo.

En la Fig. La figura 136.4 demuestra hasta dónde se puede llegar con diagramas mixtos. Este gráfico combina cinco tipos de gráficos diferentes: circular, de área, de barra (barra), de barra y de dispersión (XY). No puedo imaginar en qué situación sería apropiado un diagrama así, pero es una demostración bastante interesante.

En algunos casos, no es posible mezclar tipos de gráficos. Por ejemplo, no puede crear un gráfico mixto que incluya un gráfico de burbujas y uno de superficie (3D). Si selecciona un tipo de gráfico incompatible para una serie, Excel se lo notificará.