Graficos excel

9

Click here to load reader

Transcript of Graficos excel

Page 1: Graficos excel

Javier Fernández Castelo Pág 1 29/04/2012

GRÁFICOS CON EXCEL Tratar con muchos datos no siempre es sinónimo de poder manejar toda esa la información. Podemos tener todos los datos y sin embargo, perdernos en un mar de cifras que nos puede ahogar. Como una imagen sigue valiendo más que mil palabras, a la hora de abordar grandes cantidades de datos, ayuda poder “visualizar” su comportamiento de forma gráfica. De esta forma “veremos” la información de forma general, la tendencia de los datos,... más tarde podremos detenernos en detalles. Excel es una herramienta que permite disponer los datos de forma gráfica de forma que podamos hacernos una idea clara de los datos que vamos a nalizar. La herramienta Gráficos de Excel dispone de una amplia colección de estos gráficos de manera que es difícil que alguno de estos no se adapte a nuestras necesidades o exigencias. Para comprender su funcionamiento, como siempre, vamos a realizar algunos ejemplos paso a paso de forma que podamos iniciarnos en la exploración de sus posibilidades. Imaginemos que tratamos de explicar a nuestros amigos la diferencia que existe entre como ve un trabajador su propia renumeración por el trabajo mediante la nómina y como ve esa misma renumeración el empresario que paga por el trabajo de este operario. Vamos a suponer que el sueldo líquido del trabajador, lo que se va a llevar a casa, son justamente 2.000 €. Para llegar a esa cantidad, el empresario, al confeccionar la nómina, debe haber descontado de la renumeración bruta los gastos e impuestos. A este trabajador solo se le descuenta las cuotas de la Seguridad Social y el IRPF. Sabemos, además, que las cuotas de la S.S . se desglosan en tres partidas, SS por contingencias comunes, SS. Cuota por formación profesional y SS. Cuota por desempleo. (si fuese este trabajador funcionario, la Administración no podría descontarle la cuota por desempleo, al no estar contemplado este para este colectivo). Los porcentajes sobre el salario bruto de cada una de estas partidas es de 4,7% por la cuota de Seg. Social Contingencias comunes, 1,55% por la cuota de desempleo y 0,1 % por la cuota de Formación Profesional. Por otra parte, pongamos que a este trabajador se le retiene en concepto de IRPF el 19% de su salario bruto. Dicho de esta manera, es muy difícil hacerse una idea clara de que se ingresa en casa y que se aporta a las distintas partidas. Excel y programas como él, nos aporta la solución: podemos realizar los cálculos en la hoja de cálculo y con ellos, confeccionar un gráfico que, de forma visual y clara nos refleje de forma muy intuitiva el reparto del que estamos hablando. Comencemos abriendo una hoja de Excel donde vamos a reflejar todos los datos que más arriba hemos comentado:

Page 2: Graficos excel

Javier Fernández Castelo Pág 2 29/04/2012

Como lo que conocemos es el sueldo líquido a percibir por el trabajador, los cálculos tendremos que realizarlos de forma inversa. En la figura vemos el aspecto que va a tener la nómina de un trabajador cualquiera. En la figura vemos que tras los cálculos, el sueldo líquido es 2.000 €, que es el dato del ejemplo. Sabemos que el sueldo líquido, es el que resulta de restar al sueldo bruto todos los descuentos. Si llamamos “X” al sueldo bruto, nos quedará: 2.000 = X – ((X x 0,0470)+(X x 0, 0155) + (X x 0,001)) Realizando las operaciones necesarias,

2.000 = X – 0,2535X => 2.000 = 0,7465 X Donde

X = 2.000 / 0,7465 = 2.679,17 €

Calculado el sueldo bruto, es el momento de introducirlo en nuestra hoja y que esta realice los cálculos. Iremos introduciendo las fórmulas en las celdas correspondientes para que la nómina se confeccione por sí misma, de forma automática.

= B4*1.55%

=B4*0,01%

=B4*19%

En la celda destinada a Abonos totales, sumamos la columna abonos (que solo tiene un valor, el sueldo bruto) =SUMA(B4:B12)

En la celda destinada a descuentos Totales, sumamos la columna descuentos: =SUMA(D5:D12)

Por último, solo resta calcular el

Page 3: Graficos excel

Javier Fernández Castelo Pág 3 29/04/2012

líquido a percibir, que será el resultado de restar ls descuentos de los abonos: =B13-D13

Con esos cálculos, tendremos calculada esta sencilla nómina que servirá de plantilla para otras similares, necesitando como único dato el importe bruto del sueldo. Todo lo demás se calculará automáticamente. Podemos ver el en imagen de la derecha el aspecto y valores que tendrá la nómina finalizada.

Pero, cuanto representan esos porcentajes de descuentos sobre el total de la nómina?

Podemos “verlo” gráficamente si empleamos un gráfico.

Antes de crear el gráfico, vamos a situar los datos de forma cómoda para crear el gráfico. A la derecha de la Nomina, creamos una columna con los conceptos y otra con las cantidades, tal como vemos en la imagen lateral.

Hay muchos tipos de gráficos en Excel y cada uno de ellos, se adecua a un tipo de información. Podemos elegir entre varios que serán igualmente válidos pero para este ejemplo, creo que escogiendo un grafico tipo “Circular” se ve mejor aquello que queremos “visualizar”. Pulsaremos sobre el control “Asistente para Gráficos” de la barra de herramientas o del menú “Insertar” con lo que se abre un cuadro de diálogo, el primero del asistente.

Para nuestro propósito, seleccionamos el tipo “Circular” y en el subgrupo, elegimos el que tiene efectos 3D. Entre todos los gráficos del subtipo, la única diferencia que existe es la presentación: depende de cómo te guste, así elegirás uno u otro. Una vez seleccionado el tipo de gráfico deseado, pulsamos el botón “Siguiente” haciendo aparecer un nuevo cuadro de diálogo. En esta ocasión el cuadro nos informa de cual ha

sido el rango escogido para tomar como datos para la creación del gráfico. Si este rango seleccionado no fuera el deseado, entonces podemos modificarlo en este cuadro. También se nos da la opción de establecer las series en las filas o en las columnas de la tabla de datos. Para el ejemplo que nos ocupa, las series de tatos están claramente en las columnas concepto e importes. Es la opción que el asistente de Excel ha seleccionado por defecto. En este cuadro se empieza a vislumbrar el aspecto del gráfico que estamos creando. Cuando las opciones de este cuadro son conformes, pulsamos nuevamente en el botón “Siguiente”

Page 4: Graficos excel

Javier Fernández Castelo Pág 4 29/04/2012

En el nuevo cuadro que aparece en este asistente, podemos introducir un Título para nuestro gráfico , así como determinar si se mostrará una leyenda y donde y también como se mostrarán los rótulos de datos, si así lo deseamos. En nuestro gráfico, indicaremos al asistente que queremos que los valores de los datos se muestren en forma de porcentaje indicándoselo, tal como muestra la figura en la pestaña del asistente reservada para ello. Volvemos a pulsar el botón “Siguiente” y pasamos a un nuevo

paso del asistente. En este cuadro, simplemente se nos informa que el gráfico ya está terminado y se pregunta donde queremos insertarlo. Las opciones son o bien en la hoja en que están los datos que estamos manejando o bien en una hoja nueva, dispuesta unicamente para ese gráfico. Por el momento, podemos decidir que el gráfico se inserte en la hoja de los datos. De este modo podemos “ver” la información de forma gráfica y al tiempo, estudiar los datos con detalle. Pulsando en el botón “Finalizar”, surgirá el gráfico en la hoja. El aspecto que tendrá será similar al mostrado en la imagen inferior.

De esta manera, ahora podemos ver de un simple vistazo las proporciones de cada partida en el desglose de la nómina.

Page 5: Graficos excel

Javier Fernández Castelo Pág 5 29/04/2012

Si lo que queremos es analizar lo mismo, la nómina de un trabajador, pero desde el punto de vista del empresario que la paga, veremos que el gráfico no va a quedar igual, ni mucho menos... El empresario, a la vez que el trabajador aporta un porcentaje para cuotas de la Seguridad Social, aporta otro por los mismos conceptos: Por Contingencias Comunes, 23,60% del bruto, por Formación Profesional 0,60% y la cuota por desempleo a cargo de la empresa es de 5,50%, lo que suma un pago de 29,7% del sueldo bruto del trabajador. Si esto lo trasladamos a las columnas utilizadas más arriba, podemos crear un nuevo gráfico donde cada uno que saque sus propias conclusiones.

Page 6: Graficos excel

Javier Fernández Castelo Pág 6 29/04/2012

Como dijimos al comienzo, para cada tipo de información, podemos emplear un o unos gráficos diferentes. Si en vez de querer ver gráficamente el desglose de una nómina, lo que necesitamos es observar la variación entre las nóminas de una persona durante varios meses, o las diferencias entre nóminas del mismo periodo de varias personas, quizás sea más interesante utilizar un gráfico diferente. Veamos el ejemplo de las nóminas de un operario durante un año. Tal como mostramos en la imagen inferior, confeccionamos una tabla de datos con la información de todo el año. Del mismo modo que en el ejemplo anterior, acudimos al asistente para gráficos y comenzamos realizando los mismos pasos, a excepción de que ahora el tipo de gráfico seleccionado será el tipo Área y el subgrupo cualquiera de las áreas apiladas A continuación se puede ver la tabla de valores donde al campo Líquido de julio y diciembre se le ha sumado la paga extra y se le ha descontado el 19% del valor de esta paga. Más abajo, el gráfico resultante.

En el siguiente ejemplo, queremos representar el seno de un ángulo. Si lo que queremos es visualizar un gráfico trigonométrico, lo que tendremos que hacer es utilizar el tipo de grafico “Dispersión”. Para ello, tomamos valores de los grados de 10 en 10 y los escribimos en una columna. En una celda aparte, calculamos el valor de conversión necesario para que pueda trabajar la función SENO() este valor será calculado de la siguiente manera: =PI()/180 Con el valor de conversión calculado en una celda, introducimos la fórmula del seno de un ángulo en otra columna, contigua a la que contiene los grados. La fórmula del seno para el primer ángulo, que se encuentra en la celda A6 quedará: =SENO(A6*$B$2), donde B2 es la celda que contiene el valor de conversión y los signos “$” indican que siempre hacemos referencia a esa celda cuando copiemos la fórmula a toda la columna. Copiamos pues la fórmula a la columna y tenemos ya la relacción entre el ángulo y su seno. Es hora de crear el gráfico: Pulsamos sobre el asistente para gráficos y seleccionamos el tipo XYDispersión. El subtipo seleccionado será el de dispersión con puntos de datos conectados por líneas suavizadas

Page 7: Graficos excel

Javier Fernández Castelo Pág 7 29/04/2012

Pulsando “Siguiente”, aparece el cuadro de diálogo que permite seleccionar el rango de datos del gráfico: seleccionamos en este el rango $A$5:$B$42 Pulsamos “Siguiente” e introducimos un título: “Función Seno”. De nuevo sobre “siguiente” y por fin aparece el cuadro final: le indicamos que queremos el gráfico sobre la hoja de datos.. Ya tenemos el gráfico que queríamos. Ahora, podemos modificar algunos parámetros para que quede mejor presentado. Por ejemplo, la línea de “Grados” llega hasta 400. nosotros solo tenemos datos hasta 350. esto lo vamos a modificar.

Pulsamos sobre uno de los valores del eje de las X con el botón derecho del ratón. En el menú flotante, aparece la entrada, “Formato de ejes. Pulsando sobre esa entrada, aparece el cuadro de diálogo de la imagen de la izquierda y en este le indicamos que el valor Máximo, que ahora figura 400, queremos que sea 360. pulsamos “Aceptar”. A continuación pulsamos

con el botón derecho del ratón sobre un valor del eje de las Y’s. Aparece el menú contextual flotante con la misma entrada otra vez: “Formato de ejes. En esta ocasión, modificamos Mínimo y Máximo y en vez de -1,5 y 1,5escribimos -1.y 1. Pulsamos “Aceptar” y ya lo tenemos como queremos. Por último, hacer hincapié en este tema sobre un término que aparece en el asistente: las series. Hemos visto que Excel toma series bien sea de filas o de columnas, y que por lo general no tenemos que modificar la elección. Puede ser que en un grafico ya construido, queramos incluir la representación de otro valor que aún no está contemplado. En este caso, lo que debemos tener es otra fila o columna con los nuevos valores.

Imaginemos que necesitamos ampliar la información obtenida de forma gráfica del ejemplo anterior. Ahora queremos incluir en el gráfico la curva de la función Coseno. Para esto, junto a los datos ya establecidos en la hoja en el rango A5:B42, vamos a rellenar el rango C5:C42 con los valore calculados del coseno. En la primera celda de datos, la celda C6, insertamos la fórmula de la función Coseno : =COSENO(A6*$B$2), siendo A6 la primera celda que contiene el ángulo y B2 el valor calculado de conversión. Calculada la columna, nombramos su cabecera con el título “Coseno”

En la imagen podemos ver las primeras filas de los datos ya calculados junto a los que existían previamente. En este momento, vamos a incluir los datos en el gráfico para que nos dibuje en el mismo una segunda representación, sin necesidad de crear un nuevo gráfico. Pulsamos sobre el área del gráfico con el botón derecho del ratón y aparece el menú contextual de la herramienta “Gráfico”. En este menú, se nos va a mostrar una serie de entradas que más o menos coinciden con los pasos del asistente. Lo

Page 8: Graficos excel

Javier Fernández Castelo Pág 8 29/04/2012

podemos ver en esta imagen de la derecha. Seleccionamos la entrada de menú que vamos a necesitar para incluir nuestros datos,: será la entrada “Datos de origen...”. Pulsando sobre ella con el botón izquierdo del ratón, se nos devuelve a un paso previo del asistente de gráficos. Este cuadro de diálogo contendrá los datos de la gráfica anterior, nos mostrará el rango de datos de la curva anterior y si las series las forman filas o columnas. Desplazándonos de la pestaña “Rango de datos” en la que estamos a la pestaña “Serie”, se nos muestra ahora, además de una representación en miniatura de la curva obtenida, el nombre de esa serie, el rango de datos de su eje X y de su eje Y y sendos botones para agregar o quitar series. Pulsamos el botón “Agregar” y enseguida aparece en el cuadro de lista la serie por defecto “Serie2”. A su derecha, vamos a seleccionar el nombre que adoptará definitivamente. Como hemos hecho en otros ejemplos, desplegamos el botón junto al cuadro de texto para poder señalar la celda o rango y elegimos en la hoja de datos la celda cabecera de la columna en cuestión. Esta contiene el título “Coseno”.

Seguidamente, hacemos lo mismo con el cuadro de texto “Valores de X”, y seleccionamos el rango A6:A42, correspondiente a los grados para los que queremos calcular las sucesivas funciones coseno. Por último, procedemos exactamente igual para el cuadro de texto “Valores de Y”. En este caso tomamos el rango de valores coseno calculados: C6:C42 Una vez concluida la acción de introducir estos datos en el cuadro de diálogo, este mostrará la miniatura de la representación gráfica obtenida. Como se parece a lo que esperábamos, damos por concluido la confección del cuadro y pulsamos sobre el botón “Aceptar”.

De forma automática, el gráfico inicial se ha modificado presentando ahora la representación de dos curvas de dos funciones diferentes en el mismo gráfico. De este manera, podemos seguir añadiendo “Series” al gráfico hasta completar nuestras necesidades de representación gráfica. En la figura siguiente, se nos muestra el aspecto final del gráfico creado.

Page 9: Graficos excel

Javier Fernández Castelo Pág 9 29/04/2012

con estos ejemplos se ha querido demostrar la facilidad con la que Excel es capaz de crear representaciones gráficas de los datos mostrados en la hoja u hojas de datos así como la importancia de la elección del tipo de gráfico a la hora de representar distintos tipos o conjuntos de datos. Solo faltaría añadir que, para versiones superiores de Excel, la forma de realizar estos gráficos es la misma. Lo único que debemos tener en cuenta es que cambia la ubicación de las herramientas y el “acabado” final de los gráficos. Ahora son lógicamente mucho más modernos. Para crear un gráfico con Excel 2007, como hemos dicho, operamos de la misma manera pero nos dirigiremos a la pestaña “Insertar” de la barra de herramientas.

Podemos observar que la opción seleccionar tipo de gráfico de los asistentes de versiones anteriores, se encuentra ya desplegada en el centro de la barra de herramientas y seleccionando el tipo de gráfico, se despliegan los subtipos. Si previamente hemos seleccionado el rango de datos, el gráfico elegido se creará con las opciones por defecto que podemos modificar desde el menú contextual que se abre al pulsar sobre el área de gráfico con el botón derecho del ratón. Si no hemos seleccionado el rango de datos constituyente de lo que será el gráfico, aparece un cuadro en blanco. Pulsando el botón derecho del ratón surgirá el cuadro de diálogo de la imagen que cumplimentaremos como hemos visto anteriormente en el apartado de introducción de nuevas series de datos, pero teniendo en cuenta que el formato del cuadro de diálogo es diferente.