gráficos en excel

20
SERVICIO DE ATENCIÓN AL LECTOR: [email protected] Gráficos 1 ES PROBABLE QUE LA MAYORÍA DE LOS LECTORES SEPAN CÓMO OBTENER GRÁFICOS CON EXCEL . Y HASTA QUE CONOZCAN LAS OPCIONES MÁS EXTRAÑAS . P ERO NO TODO LO QUE SE PUEDE OBTENER EN UN GRÁFICO APARECE EN LAS OPCIONES . E N ESTE CAPÍTULO SE RESPONDE A LOS DESEOS ( APARENTEMENTE ) MÁS DESCABELLADOS DE LOS USUARIOS. DESDE OBTENER UNA PIRÁMIDE POBLACIONAL HASTA DIBUJAR UN TRANSPORTADOR PASANDO POR GRÁFICOS CON ESCALAS DE TIEMPO. Líneas adicionales en un gráfico 12 Pirámide poblacional 13 Un transportador en Excel 16 Gráfico logarítmico 17 La campana de Gauss 19 Escalas de tiempo 21 Obtener la línea de tendencia 23 Acomodar varios gráficos en una planilla 27 Indicadores en gráficos 28 c a p í t u l o u n o

Upload: jorgeingeniero

Post on 23-Jun-2015

8.281 views

Category:

Documents


4 download

TRANSCRIPT

Page 1: Gráficos en Excel

S E RV I C I O D E AT E N C I Ó N A L L E C TO R : l e c t o r e s @ t e c t i m e s . c o m

Gráficos

1ES PROBABLE QUE LA MAYORÍA DE LOS

LECTORES SEPAN CÓMO OBTENER GRÁFICOS

CON EXCEL. Y HASTA QUE CONOZCAN LAS

OPCIONES MÁS EXTRAÑAS. PERO NO TODO

LO QUE SE PUEDE OBTENER EN UN GRÁFICO

APARECE EN LAS OPCIONES. EN ESTE

CAPÍTULO SE RESPONDE A LOS DESEOS

(APARENTEMENTE) MÁS DESCABELLADOS DE

LOS USUARIOS. DESDE OBTENER UNA

PIRÁMIDE POBLACIONAL HASTA DIBUJAR UN

TRANSPORTADOR PASANDO POR GRÁFICOS

CON ESCALAS DE TIEMPO.

Líneas adicionales en un gráfico 12

Pirámide poblacional 13

Un transportador en Excel 16

Gráfico logarítmico 17

La campana de Gauss 19

Escalas de tiempo 21

Obtener la línea de tendencia 23

Acomodar varios gráficos en una planilla 27

Indicadores en gráficos 28

c a p í t u l o u n o

Page 2: Gráficos en Excel

EXCEL XP: 100 RESPUESTAS AVANZADAS

12

LÍNEAS ADICIONALES EN UN GRÁFICO¿Cómo agregar, en un gráfico de líneas, una línea horizontal que

represente el promedio de los valores representados?

Supongamos que tenemos que representar datos de una tabla como la de la Figura 1. En unacolumna auxiliar, calculamos el promedio de los valores con la función PROMEDIO:

Figura 1. Queremos graficar estos valores, junto

con una línea horizontal que señale el promedio.

Colocamos el cursor en la primera columna a la derecha de la tabla, y en la filacorrespondiente al primer valor.

Escribimos la función =PROMEDIO(B$2:B$7).

Extendemos el dato a toda la columna, usando el puntero de estirar. Los signos $en la función mantienen constante el promedio en toda la serie. Como el valor escontinuo, su representación gráfica será una línea horizontal. El gráfico, que apa-rece en la Figura 2, se crea usando las técnicas convencionales.

Figura 2. El gráfico obtenido a partir de la tabla anterior,

con el promedio señalado.

1

Page 3: Gráficos en Excel

Grá

fico

s1

Pirámide poblacional

13

PIRÁMIDE POBLACIONAL Queremos hacer un gráfico del tipo “pirámide poblacional”. Debería

poder hacerse en Excel, pero ninguno de los tipos de gráfico parece servir.

La Figura 3 muestra la pirámide a la que se refiere la pregunta. Se usa en los censos para mos-trar la distribución de la población por sexo y por distintos grupos de edad.

Figura 3. La longitud de las barras indica la cantidad

de personas para cada grupo de edad. Hacia la izquierda,

las mujeres, y hacia la derecha, los hombres.

Para obtener este gráfico primero tenemos que escribir los datos en una tabla de tres columnasy tantas filas como grupos de edad:■ En la primera columna escribimos los grupos de edad.■ En la segunda columna escribimos la cantidad de mujeres, pero con signo negativo.■ En la tercera columna escribimos la cantidad de hombres.

Figura 4. Así armamos la tabla para el gráfico de la Figura 3.

Una vez completada la tabla, creamos el gráfico de la forma habitual:

Seleccionamos la tabla.

2

Page 4: Gráficos en Excel

Tomamos las opciones Insertar/Gráfico, o hacemos un clic en el Asistente para gráficos.

En el primer paso, seleccionamos Barras como tipo de gráfico.

Completamos a gusto las demás opciones del asistente.

Hacemos un clic en Finalizar.

El resultado no es exactamente la pirámide buscada. Hace falta realizar un ajuste:

Apoyamos el puntero del mouse sobre cualquiera de las barras.

Hacemos un clic con el botón derecho.

Del menú contextual que aparece, tomamos la opción Formato de serie de datos.Aparece un menú con fichas.

Seleccionamos la ficha Opciones.

En Superposición, colocamos 100.

En Ancho del rango, ingresamos 0.

Hacemos un clic en Aceptar.

Las opciones de la Figura 5 hacen que las dos barras correspondientes a un grupo de edad que-den a la misma altura. También podemos hacer que todos los valores del eje horizontal aparez-can como positivos:

Apoyamos el puntero del mouse sobre cualquier valor del eje horizontal.

Hacemos un clic con el botón derecho.

EXCEL XP: 100 RESPUESTAS AVANZADAS

14

Figura 5. Estas opciones le dan su forma

característica a la pirámide poblacional.

Page 5: Gráficos en Excel

NOMBRE DE LA HOJA

La hoja insertada para el libro recibe elnombre Gráfico, seguido de un nú-mero de orden. Este nombre puede sercambiado luego, haciendo doble clicsobre la ficha de la hoja.

Del menú contextual que aparece, tomamos la opción Formato de ejes. Se abriráun menú con fichas.

Abrimos la ficha Número.

En el campo Categorías, seleccionamos Personalizada.

En Tipo, escribimos 0;0. Es decir, dos ceros separados por punto y coma, como enla Figura 6.

Hacemos un clic en Aceptar.

Figura 6. Este formato personalizado hace los valores del gráfico

aparezcan sin su signo, aunque sean negativos.

Pirámide poblacional

15

Grá

fico

s1

Page 6: Gráficos en Excel

UN TRANSPORTADOR EN EXCEL ¿Cómo puedo hacer una Grilla circular que permita señalar ángulos,

como en un transportador?

Lo más fácil es crear un gráfico de torta con, porejemplo, 36 porciones. Cada porción corresponde a10°. El gráfico se crea a partir de una tabla como lade la Figura 7.

La tabla continúa hasta la fila 36. El valor que se coloca en la columna B no importa, siempreque sea el mismo para todas las celdas, de modo que todas las porciones tengan el mismo ta-maño. La creación del gráfico tiene algunos trucos:

Colocamos el cursor en cualquier celda de la tabla.

Tomamos las opciones Insertar/Gráfico (o hacemos un clic en el Asistente paragráficos).

En el primer paso, indicamos Gráfico circular.

En el segundo, seleccionamos la ficha Serie.

Hacemos un clic en Serie1.

Hacemos un clic en Quitar.

Hacemos un clic en Rótulos de las categorías.

Seleccionamos el rango A1:A36, donde están los valores de grados.

En el tercer paso, eliminamos las leyendas y marcamos la opción Nombre de la ca-tegoría como rótulos de datos.

Continuamos con las demás opciones del asistente de la forma habitual.

3

EXCEL XP: 100 RESPUESTAS AVANZADAS

16

Figura 7. Esta tabla permite obtener

una grilla circular graduada de 0 a

360°, como si fuera un transportador.

Page 7: Gráficos en Excel

Luego de utilizar estas opciones, obtenemos el gráfico de la Figura 8.

Figura 8. El gráfico obtenido a partir de la tabla de la Figura 7.

Debemos quitar la Serie1 porque los datos que se grafican son los de la segunda columna. Losde la primera columna, a su vez, se indican como rótulos para que acompañen a las porciones.Podemos mejorar el gráfico coloreando convenientemente las porciones, como se ve en la Fi-gura 8. Eso queda a gusto del lector.

GRÁFICO LOGARÍTMICO ¿Cómo hacer un gráfico en que ambas escalas (x e y) sean logarítmicas?

Excel permite crear distintos tipos de gráficos. Entre ellos están los de escala doble logarítmi-ca, para datos como los de la tabla de la Figura 9.

Figura 9. Estas dos series de datos guardan entre sí una correlación logarítmica.

Primero hay que crear el gráfico de la forma habitual. En el primer paso del asistente, seleccio-namos el tipo XY Dispersión, como vemos en la Figura 10.

4

Gráfico logarítmico

17

Grá

fico

s1

Page 8: Gráficos en Excel

Figura 10. Para crear un gráfico logarítmico como el de la Figura 9,

se comienza eligiendo el tipo de gráfico XY Dispersión.

Una vez que el gráfico está terminado, hacemos el cambio de escala:

Hacemos un clic sobre uno de los ejes. Aparecen los puntos de agarre, que indi-can que el eje ha sido seleccionado.

Tomamos las opciones Formato/Eje seleccionado y abrimos la ficha Escala.

Marcamos la opción Escala logarítmica.

Hacemos un clic en Aceptar.

Luego, hacemos lo mismo con el otro eje. El gráfico quedará, entonces, con ambos ejes en es-cala logarítmica, como se ve en la Figura 11.

CORRELACIONES

En un gráfico logarítmico, algunascorrelaciones no lineales quedan re-presentadas mediante rectas. La pen-diente de la recta es el grado de lacorrelación.

EXCEL XP: 100 RESPUESTAS AVANZADAS

18

Page 9: Gráficos en Excel

Figura 11. El gráfico doble logarítmico,

creado a partir de la tabla de la Figura 9.

Los gráficos en que ambas escalas son logarítmicas (llamados gráficos log-log) tienen muchasaplicaciones técnicas. Por ejemplo, ayudan a descubrir qué tipo de correlación existe entre dosseries de datos experimentales.

LA CAMPANA DE GAUSS En los libros de estadística aparece representada la “campana de Gauss”

para distribuciones normales. ¿Cómo se hace este gráfico?

La Figura 12 muestra el gráfico al que se refiere la pregunta. Se llama Campana de Gauss porsu forma y en honor al matemático alemán Karl Friedrich Gauss (1777-1855).

Figura 12. La “campana de Gauss”. Representa la densidad de probabilidad

según una distribución normal.

La pregunta puede separarse en dos: cómo obtener los valores de esta curva y cómo represen-tarlos gráficamente.

5

La campana de Gauss

19

Grá

fico

s1

Page 10: Gráficos en Excel

La tabla de valores se ve en la Figura 13. Para obtener esta tabla:

En la celda A1, escribimos –3.

En la celda A2, escribimos –2,9.

Seleccionamos el rango A1:A2.

Obtenemos el puntero de estirar (la cruz fina), y estiramos este rango hasta la cel-da A61. En esta celda aparecerá el valor 3.

En la celda B1 escribimos la función =DISTR.NORM(A1;0;1;FALSO).

Estiramos la función anterior hasta B61.

Figura 13. Para obtener los valores de la curva de la Figura 12,

usamos la función DISTR.NORM.

La función DISTR.NORM lleva cuatro argumentos entre los paréntesis:■ El primero es el valor para el cual se quiere obtener la probabilidad.■ El segundo es el valor medio de la distribución.■ El tercero es el desvío estándar.■ El último es el valor lógico FALSO, para calcular la densidad de probabilidad, o VERDADE-

RO, para la probabilidad acumulada.

Ahora podemos graficar esta tabla:

Seleccionamos el rango A1:B61.

Tomamos las opciones Insertar/Gráfico (o hacemos un clic en el botón Asistentepara gráficos).

En el primer paso del asistente, indicamos tipo de gráfico XY (Dispersión) y algu-no de los subtipos de la derecha, como se ve en la Figura 14.

EXCEL XP: 100 RESPUESTAS AVANZADAS

20

Page 11: Gráficos en Excel

Continuamos con los siguientes pasos del asistente, o hacemos un clic en Termi-nar para aceptar el resto de las opciones.

Figura 14. El tipo y el subtipo de gráfico que hay que especificar

para obtener la “campana de Gauss” de la Figura 12.

ESCALAS DE TIEMPO Hice un gráfico donde la escala del eje horizontal son horas.

¿Cómo hago ahora para que las divisiones sobre el eje horizontal aparezcan marcadascada dos horas, por ejemplo?

La Figura 15 muestra un gráfico similar al ejemplo de la pregunta. Es un gráfico del tipo XYDispersión, creado a partir de la tabla de la izquierda.

Figura 15. Un gráfico con escala de tiempos. Las graduaciones

en el eje quedarían mejor si fueran horas enteras.

6

Escalas de tiempo

21

Grá

fico

s1

Page 12: Gráficos en Excel

RECURSOS EN LÍNEA

Algunos de los gráficos representadosen este capítulo, junto con sus respec-tivas planillas, pueden obtenerse ennuestro sitio onweb.tectimes.com.

Excel establece automáticamente una cierta escala sobre el eje horizontal. Si lo que se desea esque las marcas en el eje aparezcan a las 2.00, las 4.00, etc., sólo hay que indicar los parámetrosde este eje en formato de hora:

Hacemos un clic sobre el eje horizontal. Debe aparecer un punto en cada extre-mo de éste.

Tomamos las opciones Formato/Eje seleccionado. Aparece un menú con varias fi-chas que controlan las características del eje.

Seleccionamos la ficha Escala (Figura 16).

Donde dice Unidad mayor, escribimos 02:00. Es decir, dos horas.

Hacemos un clic en Aceptar.

Figura 16. En esta ficha indicamos los parámetros de la escala de tiempos.

EXCEL XP: 100 RESPUESTAS AVANZADAS

22

Page 13: Gráficos en Excel

Así, las divisiones aparecerán sobre el eje, como en la Figura 17.

Figura 17. Éste es el mismo gráfico de la Figura 16,

pero con la escala corregida como se indica en el texto.

OBTENER LA LÍNEA DE TENDENCIATengo una planilla con dos columnas que contienen datos

experimentales y correlacionados linealmente. Con estos datos creo un gráfico dedispersión que muestra una nube de puntos. Ahora quiero que en el gráfico aparezca también la línea de tendencia que mejor se ajuste a esos puntos. Sé que tengo que usar la función ESTIMACION.LINEAL, pero no sé cómo hacerlo.

La planilla a la que se refiere la pregunta aparece en la Figura 18. Muestra la producción ob-tenida en un proceso industrial, para distintas cantidades de cierto aditivo.

Figura 18. Los datos de las columnas A y B están relacionados.

Suponemos que los datos de la columna B tienen que estar relacionados de alguna manera. Unaforma de comprobar esta correlación es en un gráfico. Un gráfico como el de la Figura 19.

7

Obtener la línea de tendencia

23

Grá

fico

s1

Page 14: Gráficos en Excel

Figura 19. Este gráfico se obtiene a partir de los valores de la planilla de la Figura 16.

Es un gráfico del tipo “Dispersión XY”.

Este gráfico se obtiene de la manera habitual, indicando, en el primer paso del asistente, grá-fico del tipo Dispersión XY, como en la Figura 20.

Figura 20. El tipo y el subtipo que hay que indicarle al Asistente

para obtener el gráfico de la Figura 19.

En el gráfico de la Figura 19 los puntos están más o menos alineados a lo largo de una recta.Esto sugiere que la correlación entre las dos series de datos es lineal. Excel tiene un comandopara obtener rápidamente la recta que señala esta correlación:

Apoyando el puntero sobre cualquiera de los puntos del gráfico, hacemos un cliccon el botón derecho del mouse. Aparece el menú contextual de la Figura 21.

EXCEL XP: 100 RESPUESTAS AVANZADAS

24

Page 15: Gráficos en Excel

Tomamos la opción Agregar línea de tendencia. Aparece el cuadro de la Figura 22,que ofrece distintos tipos de línea de tendencia.

Hacemos un clic en la opción Lineal, porque ésa es la línea de tendencia que bus-camos.

Hacemos un clic en Aceptar.

Figura 21. En este menú contextual tomamos

la opción Agregar línea de tendencia. Aparecerá el cuadro de la Figura 22.

Figura 22. Las opciones para la obtención de la línea de tendencia.

Aparecerá entonces en el gráfico la línea de tendencia, como en la Figura 23.

Obtener la línea tendencia

25

Grá

fico

s1

Page 16: Gráficos en Excel

Figura 23. El gráfico de la Figura 19, con su línea de tendencia.

El cuadro de la Figura 23 incluye la ficha Opciones, donde le podemos pedir a Excel que nosmuestre la ecuación de esta línea de tendencia. Para eso marcamos la opción Presentar ecua-ción en el gráfico, como se ve en la Figura 24.

Figura 24. Marcando la opción Presentar ecuación en el gráfico,

Excel nos mostrará la ecuación de la línea de tendencia.

Como lo dice la pregunta, también podemos obtener los parámetros de la línea de tendenciarecta con la función ESTIMACION.LINEAL. Estos parámetros son lo que en matemática llama-mos pendiente y ordenada al origen. Hay que actuar con cierto cuidado, ya que ésta es una función matricial:

Seleccionamos un rango de dos columnas de ancho y una fila de alto.

Escribimos =ESTIMACION.LINEAL(B2:B8;A2:A8;VERDADERO).

EXCEL XP: 100 RESPUESTAS AVANZADAS

26

Page 17: Gráficos en Excel

Apretamos la combinación Control+Shift+Enter.

La sintaxis de esta función es: =ESTIMACION.LINEAL(rangoY;rangoX;constante).

■ rangoY es el rango de valores correspondiente a la variable dependiente. En el ejemplo, laproducción obtenida.

■ rangoX es el rango de valores correspondiente a la variable independiente. En el ejemplo, lacantidad de aditivo.

■ constante es un valor lógico que, cuando vale VERDADERO (como en el ejemplo), indicaque debe calcularse la ordenada al origen. Si vale FALSO (o si se omite), la ordenada al ori-gen se considera igual a cero.

El resultado aparece en la Figura 25. El primer valor es la pendiente, y el segundo, la ordena-da al origen.

Figura 25. Los valores devueltos por la función ESTIMACION.LINEAL.

Se comienza seleccionando un rango de dos celdas porque se quiere obtener dos parámetros.Y se le da entrada a la función mediante la combinación Control+Shift+Enter porque es unafunción matricial.Excel dispone de otras funciones que permiten hacer este tipo de ajustes. Consúltese para eso lasección de funciones estadísticas en el segundo volumen de la Guía de funciones de Excel.

ACOMODAR VARIOS GRÁFICOS EN UNA PLANILLA¿Cómo acomodar cuatro gráficos en una misma planilla, de forma que

queden todos del mismo tamaño y perfectamente alineados?

La manera más simple de obtener gráficos de un tamaño determinado es crearlos de la formahabitual y, luego, ajustarlos usando la grilla como guía:

Apoyamos el puntero del mouse sobre los puntos de agarre que aparecen en lasesquinas y en los lados del gráfico.

8

Acomodar varios gráficos en una planilla

27

Grá

fico

s1

Page 18: Gráficos en Excel

Presionamos el botón izquierdo del mouse y lo mantenemos apretado.

Sin soltar el botón, pulsamos la tecla Alt y la mantenemos apretada.

Movemos el puntero para cambiar el tamaño. Éste se mo-verá “en saltos” y coincidirá con la grilla, como en la Fi-gura 26.

Usamos el procedimiento anterior actuando sobre todas las esquinas hasta obtener el tamañoadecuado. De esta forma podemos hacer que el gráfico quede, por ejemplo, con diez filas dealto y tres columnas de ancho.Este método funciona con cualquier objeto aplicado sobre la planilla: imágenes, autoformas,figuras geométricas, etc.

INDICADORES EN GRÁFICOSTengo un gráfico con globitos que marcan puntos específicos de la

curva. ¿Cómo hago para que estos globitos acompañen al punto cuando cambio losvalores o la escala?

En un gráfico pueden aplicarse flechas, globitos o distintos tipos de marcadores para señalaralgún punto de interés, como se ve en la Figura 27.

Figura 27. En este gráfico se ha aplicado una autoforma para señalar

y destacar uno de los puntos de la línea.

9

EXCEL XP: 100 RESPUESTAS AVANZADAS

28

Figura 26. Si arrastramos el mouse mientras mantenemos

pulsada la tecla Alt, los puntos de agarre se moverán en saltos,

siguiendo la grilla de la planilla.

Page 19: Gráficos en Excel

Las opciones de gráfico no incluyen la posibilidad de fijar el marcador al punto. Pero podemosrecurrir a un truco consistente en usar como marcadores los rótulos de los gráficos:

Seleccionamos el gráfico (con un clic).

Tomamos las opciones Gráfico/Opciones de gráfico. Aparece un menú con fichas.

Seleccionamos la ficha Rótulos de datos.

Marcamos la opción Valor, como se ve en la Figura 28.

Hacemos clic en Aceptar.

Figura 28. Marcando esta opción, aplicamos rótulos junto a cada punto.

Luego, podremos usar los rótulos como marcadores.

Esto coloca un rótulo junto a cada punto. Ahora podemos eliminar todos los rótulos, exceptoel correspondiente al punto que queremos destacar.

Hacemos un clic en el primer rótulo que eliminare-mos. Aparecerán puntos de agarre junto a todos losrótulos.

Hacemos un segundo clic sobre el rótulo. Los pun-tos de agarre quedarán solamente en el rótulo se-leccionado, como en la Figura 29.

Pulsamos la tecla Suprimir (o Delete).

Indicadores en gráficos

29

Grá

fico

s1

Figura 29. Dos clics

separados sobre el rótulo

permiten seleccionarlo para

luego eliminarlo. Los puntos

de agarre indican que sólo se

ha seleccionado ese rótulo.

Page 20: Gráficos en Excel

FORMATOS DE INDICADORES

Al indicador de la Figura 30, como atodo rótulo en general, se le puedenaplicar opciones de formato: tipogra-fía, tamaño, color, etc.

Hacemos lo mismo con los demás rótulos, excepto con el que quedará como marcador. Este ró-tulo restante puede ser editado de modo de reemplazar el valor mostrado por cualquier texto:

Seleccionamos el rótulo con dos clics.

Hacemos un tercer clic sobre el rótulo. Aparecerá el cursor titilante que nos per-mitirá borrar el contenido actual y escribir el texto deseado.

Hacemos un clic fuera del gráfico para deseleccionar el rótulo.

Figura 30. Este marcador es, en realidad, el rótulo de datos modificado.

Acompañará al punto señalado si cambian los valores del gráfico o de la escala.

El rótulo quedará ahora como en la Figura 30. Este marcador acompañará al punto si se mo-difican los valores o la escala.

EXCEL XP: 100 RESPUESTAS AVANZADAS

30