Estadística con Excel

Pro

f. L

lore

t V

ivia

na

Est

ad

ísti

ca D

esc

rip

tiv

a c

on

Ex

cel

El objetivo de este apunte es que los estudiantes se familiaricen con el software que se usa en el mundo de los negocios. Integrar las hojas de trabajo o el software estadístico en todos los aspectos de un curso de introducción a la estadística permite que el alumno se enfoque en la interpretación de los resultados antes que en los cálculos.

Estadística descriptiva en Excel Prof. Viviana Lloret

1

Índice

Estadística con Excel ....................................................................................... 3

Entorno del programa....................................................................................... 3

Rango ............................................................................................................ 3

Operaciones básicas ......................................................................................... 4

Seleccionar rango ............................................................................................ 4

Asignar nombre a un rango ............................................................................... 5

Operaciones en Excel ....................................................................................... 5

Referencias celdas ........................................................................................... 5

Copiar y pegar fórmulas ................................................................................... 6

Cálculos acumulados ........................................................................................ 6

Funciones en Excel .......................................................................................... 7

FUNCIÓN SUMA ............................................................................................... 7

Sintaxis = SUMA (rango) .................................................................. 7

FUNCIÓN PROMEDIO O MEDIA .......................................................................... 7

Sintaxis =PROMEDIO (rango) ............................................................ 7

FUNCIÓN MÁXIMO ........................................................................................... 7

Sintaxis = MAX (rango) ..................................................................... 7

FUNCIÓN MÍNIMO ............................................................................................ 7

Sintaxis = MIN (rango) ..................................................................... 7

FUNCIÓN CONTAR ........................................................................................... 8

Sintaxis = CONTAR (rango) ............................................................... 8

FUNCIÓN CONTAR.SI ....................................................................................... 8

Sintaxis =CONTAR.SI (RANGO A EVALUAR;”Condición”) ........................ 8

FUNCIÓN MODA .............................................................................................. 8

Sintaxis =MODA (RANGO) ................................................................. 8

FUNCIÓN MEDIANA .......................................................................................... 8

Sintaxis =MEDIANA(RANGO) ............................................................. 8

FUNCIÓN VARIANZA ........................................................................................ 8

Sintaxis =VAR(RANGO) ..................................................................... 8

FUNCIÓN DESVÍO ESTÁNDAR ............................................................................ 8

Sintaxis =DESVESTA (RANGO) ........................................................... 8

Cómo calcular COEFICIENTE DE VARIACIÓN ...................................................... 8

Gráficos ........................................................................................................... 9

Gráficos de columnas ....................................................................................... 9

Gráficos de barras .......................................................................................... 11

Gráficos de líneas .......................................................................................... 12


2

Gráficos de área ............................................................................................ 14

Gráficos circulares ......................................................................................... 16

Histograma ................................................................................................... 19

Ojiva ............................................................................................................ 20

Pasos a seguir en la construcción de un gráfico.................................................. 20

Cómo crear un Histograma ............................................................................. 23


3

Estadística con Excel

Entorno del programa

Básicamente, una hoja de cálculo es un programa que nos permite realizar cálculos

aritméticos, estadísticos, financieros, etc., de manera automática, con una serie de

datos previamente introducidos. Para ello utilizamos una cuadrícula donde en cada

celda de la misma podemos introducir números, letras, fechas, porcentajes, fórmulas y

funciones.

Al iniciar Excel aparece una pantalla inicial como ésta, vamos a ver sus componentes

fundamentales, así conoceremos los nombres de los diferentes elemento. El elemento

más significativo de la nueva interfaz de usuario se denomina cinta de opciones. La

cinta de opciones es la banda a la largo de la parte superior de la ventana del

programa que contiene grupos de comandos.

Como se puede apreciar las filas se designan con números y las columnas con letras, la

intersección de una fila y una columna determina una celda, por ejemplo A1, B5, etc.

Llamamos celda activa a la celda que se encuentra seleccionada, disponible para

cargar valores.

Rango

Celda activa

Filas

Nombre de las hojas

Botón de Office

Barra de acceso rápido

Barra de fórmulas

Cinta de opciones

Columnas

Cuadro de nombres


4

Se denomina Rango a un conjunto de celdas. En la imagen inicial se sombreó con

amarillo el rango G10: H11 (está formado por las celdas G10, G11, H10, H11); el E2:

E10 en azul (formado por las celdas E7, E8, E9, E10). El signo que se utiliza para

separar las dos celdas son los dos puntos. Si los rangos son discontinuos, por ejemplo,

necesito nombrar el rango formado por las celdas A1, A2, A3 y C1, C2, C3,

sombreados en verde, se debe escribir (A1:A3; C1:C3).

Operaciones básicas

Para crear un documento nuevo debemos pulsar el botón de Office con lo cual se

desplegará un menú, en el que debemos escoger la opción Nuevo.

Elegimos Libro en blanco y pulsamos el botón Crear.

Para guardar un archivo por primera vez debemos escoger en el menú que se

despliega al accionar el botón de Office la opción Guardar, en la ventana que se abre

debemos seleccionar la ubicación y colocarle un nombre al archivo.

Recordando que, los archivos creados con Excel 2007 no pueden ser abiertos con Excel

2003 es conveniente escoger la opción Guardar como y dentro del conjunto se

opciones que se presentan escoger:

Libro de Excel 97-2003 (Opción destacada en la imagen de la derecha).

Para cerrar el libro actual debemos pulsar la opción Cerrar.

Para salir de la aplicación debemos pulsar la opción Salir de Excel.

Seleccionar celda o rango

Para seleccionar una celda basta con hacer clic en ella, con lo cual quedará activa, lista

para trabajar en ella.

Recordando que, llamamos rango a todo conjunto de celdas, para seleccionar un

rango continuo debemos hacer clic en la primera celda y sin soltar el botón del

mouse, arrastramos el mouse pintando todas las celdas que conforman dicho rango, al

soltar el botón del mouse las celdas quedarán seleccionadas y destacadas con una

tonalidad celeste. En el siguiente ejemplo hemos seleccionado el rango B3:E13.


5

Llamamos rango discontinuo, al rango formado por celdas que se encuentran

separadas por otras celdas que no forman parte de él.

Para seleccionar un rango discontinuo, seleccionamos el primer conjunto de celdas

continuas, pulsamos la tecla CTRL y sin soltar dicha tecla, seleccionamos el resto de

las celdas.

Asignar nombre a un rango

Para indicar un rango escribimos la dirección de la primera celda que conforma el

mismo y la de la última, separadas por dos puntos.

Otro modo de identificar un rango es asignándole un nombre que lo represente,

para ello seleccionamos el rango a nombrar y pulsamos, en la pestaña Fórmulas,

grupo Nombres definidos, el botón Asignar nombre a un rango.

En el cuadro de diálogo que se abre ingresamos un nombre que lo identifique, y por

último aceptamos.

Operaciones en Excel

Las fórmulas en Excel siempre comienzan con un signo igual (=). El signo igual indica a

Excel que los caracteres siguientes constituyen una fórmula. Detrás del signo igual están

los elementos que se van a calcular (los operandos), separados por operadores de cálculo.

Excel calcula la fórmula de izquierda a derecha, según el orden específico de cada operador

de la fórmula.

En la siguiente tabla observamos qué signo ejecuta cada operación:

Referencias celdas

Para referenciar una celda dispuesta en la misma hoja escribimos el nombre de la

columna a la que pertenece y a continuación el número de fila, por ejemplo, la primera

celda pertenece a la columna A y a la fila 1 por lo tanto para referenciar dicha celda

escribimos =A1.

Para referenciar un rango continuo, dispuesto en la misma hoja, escribimos el

nombre de la primera celda que lo compone, dos puntos (:), y a continuación el

Operador Operación

% Porcentaje

^ Potenciación

* Multiplicación

/ División

+ Suma

- Resta


6

nombre de la última celda, por ejemplo, A2:B3. Dicho rango está compuesto por las

celdas A2, A3, B2, B3.

Copiar y pegar fórmulas

Para copiar una fórmula a otras celdas se utiliza,

generalmente, la técnica de arrastrar. Para utilizar dicha

técnica debemos ingresar la fórmula en la primera celda del

rango a calcular, pulsamos ENTER para obtener el resultado.

Luego hacemos clic en ella, posicionamos el mouse donde

muestra la imagen (ángulo inferior derecho de dicha celda), y

cuando éste toma la forma de cruz fina, hacemos clic y arrastramos en la dirección de las

celdas a copiar.

Al copiar una fórmula es indispensable considerar la dirección de la celda que ocupa,

en determinados situaciones, es necesario hacer referencia a una determinada celda y

no deseamos que su dirección varíe al copiar la fórmula. Por ello es necesario indicarle

a Excel que considere siempre, para realizar el cálculo, dicha celda, en otros términos,

que deje “fija” dicha celda.

Para ello debemos agregar delante del identificador de fila y delante del identificador

de columna el signo $, por ejemplo si queremos fijar A4 debemos ingresar $A$4.

Para fijar una celda, estando la celda seleccionada, pulsamos la tecla de función F4.

Cálculos acumulados

En la columna C de la siguiente planilla calcularemos el costo acumulado teniendo en

cuenta los valores ingresados en la columna B.

Para ello en la primera celda C2 debemos ingresar el valor del primer costo (B2), es

decir en C2 escribimos =B2

En C3 debemos ingresar =C2 + B3


7

En C4 debemos ingresar = C3 + B4 y así sucesivamente.

Es decir, en cada celda de la columna C debemos calcular la suma del contenido de la

celda ubicada por encima de dicha celda y la celda ubicada a la izquierda de la misma.

Por tal motivo, una vez ingresada la fórmula en C3 podemos utilizar la técnica de

arrastrar para copiar la fórmula al resto del rango.

Además podemos verificar que el último costo acumulado (C22) coincide con el total

de la columna Costo (B23).

Funciones en Excel

FUNCIÓN SUMA

La función SUMA se utiliza para calcular el total del conjunto de valores ingresados en el

rango especificado.

Sintaxis = SUMA (rango)

FUNCIÓN PROMEDIO O MEDIA

La función promedio calcula el valor medio del conjunto de valores ingresados en el rango

especificado.

Sintaxis =PROMEDIO (rango)

FUNCIÓN MÁXIMO

La función Máximo devuelve el máximo valor del conjunto de valores ingresados en el

rango especificado.

Sintaxis = MAX (rango)

FUNCIÓN MÍNIMO

La función Mínimo devuelve el mínimo valor del conjunto de valores ingresados en el rango

especificado.

Sintaxis = MIN (rango)


8

FUNCIÓN CONTAR

Sintaxis = CONTAR (rango)

Para aplicar esta función el rango debe contener datos numéricos.

FUNCIÓN CONTAR.SI

Sintaxis =CONTAR.SI (RANGO A EVALUAR;”Condición”)

Para contar en el rango A3:A10 celdas que contienen la letra A, debemos ingresar la

función =Contar.si (A3:A10;”A”)

FUNCIÓN MODA

La función Moda devuelve el valor más frecuente o el que más se repite dentro de un

rango

Sintaxis =MODA (RANGO)

FUNCIÓN MEDIANA

La función Mediana devuelve el valor central dentro de un rango

Sintaxis =MEDIANA(RANGO)

FUNCIÓN VARIANZA

La función Varianza devuelve la varianza de una muestra.

Sintaxis =VAR(RANGO)

FUNCIÓN DESVÍO ESTÁNDAR

La función Desvío estándar devuelve la desviación estándar de una muestra.

Sintaxis =DESVESTA (RANGO)

Cómo calcular COEFICIENTE DE VARIACIÓN

El coeficiente de variación es el cociente entre del Desvío Estándard (s) y la media (x).


9

Gráficos

La utilización de gráficos hace más sencilla e inmediata la interpretación de los datos.

A menudo un gráfico nos dice mucho más que una serie de datos clasificados por filas

y columnas.

Si comparamos una tabla de datos con un gráfico creado a partir de ella, podremos

comprobar que esto es cierto: mirar el gráfico es una forma mucho más rápida de

conocer la información más relevante contenida en unos datos.

Gráficos de columnas

Este tipo de gráfico es útil para mostrar cambios de datos en un período de tiempo o

para ilustrar comparaciones entre elementos.

En los gráficos de columnas, las categorías normalmente se organizan en el eje

horizontal y los valores en el eje vertical.

Subtipos de gráficos de columna

Columnas agrupadas: Los gráficos de columnas agrupadas comparan valores

entre categorías, muestra valores en rectángulos verticales en 2D.


10

5,4 6,5

9,9

2,5

0,5 3,1

1,5

0,8

1

0,2 0,8

0

2

4

6

8

10

12

14

granos Aceite Subproductos

Exportaciones a marzo de 2011

Resto

Girasol

Maíz

Trigo

Soja

0

20

40

60

80

100

Mercadogeneral

Mercado deservicios

MercadoIndustrial

Evolución de las compensaciones y beneficios en 2011

Significativamentemenor

Escasamente menor

Igual

Escasamente mayor

Significativamentemayor

Columnas apiladas: Los gráficos de columnas apiladas muestran la relación

de elementos individuales con el conjunto, comparando la contribución de cada

valor con un total entre categorías.

Columna 100% apilada: Este tipo de gráfico compara el porcentaje que cada

valor aporta al total de distintas categorías.


11

2488

2017

1746

1647

1466

2300

1239

1209

1076

1048

Chevrolet Corsa Classic

Peugot 207 Compact

Volkwagen Gol Power

Volkwagen Gol Trend

Ford Ecosport

Toyota Hilux

VW Suan

Renault Kangoo/Express

Renault Clio

Renault Sandero

0 500 1000 1500 2000 2500 3000

Patentamiento de vehículos

Volumen

Los mismos subtipos pueden verse en profundidad y el subtipo 1 en tres ejes (Las

columnas se van una delante de las otras).

Gráficos de barras

Los gráficos de barras muestran comparaciones entre elementos individuales.

Los gráficos de barras tienen los siguientes subtipos de gráfico:

Barra agrupada: Los gráficos de barras agrupadas comparan valores entre

categorías. En un gráfico de barras agrupadas, las categorías se suelen

organizar a lo largo del eje vertical, mientras que los valores lo hacen a lo largo

del horizontal.


12

2

0

4

21

22

18

58

61

57

5

3

6

13

14

12

0 20 40 60 80 100 120

Mercado general

Mercado de servicios

Mercado Industrial

Evolución de las compensaciones y beneficios en 2011

Significativamente mayor

Escasamente mayor

Igual

Escasamente menor

Significativamente menor

5,4

6,5

9,9

2,5

0,5

3,1

1,5

0,8

1

0,2

0,8

0% 20% 40% 60% 80% 100%

granos

Aceite

Subproductos


Soja

Trigo

Maíz

Girasol

Resto

Barra apilada Los gráficos de barras apiladas muestran la relación de

elementos individuales con el conjunto.

Barras 100% apiladas Este tipo de gráfico compara el porcentaje con que

cada valor contribuye a un total entre categorías.

Gráficos de líneas

Los gráficos de línea los podemos utilizar para mostrar datos continuos en el tiempo,

establecidos frente a una escala común y, por tanto, son ideales para mostrar

tendencias en datos a intervalos iguales. En un gráfico de líneas, los datos de categoría


13

Año 1995 Año 2000 Año 2005 Año 2010

Series1 500 680 790 935

500

680

790

935

400

500

600

700

800

900

1000

Can

tid

ad d

e a

rrib

os

en

mill

ore

s

Arribos internacionales

se distribuyen uniformemente en el eje horizontal y todos los datos de valor se

distribuyen uniformemente en el eje vertical.

Los gráficos de líneas tienen los siguientes subtipos de gráfico:

Línea: Ya sea que se muestren con marcadores (para indicar valores de datos

individuales) o sin ellos, los gráficos de líneas son útiles para mostrar

tendencias en el tiempo o categorías ordenadas.


14

0,00%

10,00%

20,00%

30,00%

40,00%

50,00%

60,00%

70,00%

80,00%

90,00%

100,00%

Ventas 2009

Ventas 2008

0

2

4

6

8

10

12

14

Soja Trigo Maíz Girasol Resto


Subproductos

Aceite

granos

Línea apilada: Este tipo de gráfico presenta la tendencia del aporte de cada

valor a lo largo del tiempo o entre categorías.

Línea 100% apilada: Este tipo de gráfico presenta la tendencia del aporte

porcentual de cada valor en el tiempo o entre categorías.

Los tres subtipos pueden verse con marcadores y el subtipo 1 en 3D.

Gráficos de área

Este tipo de gráfico muestra las tendencias de los valores a lo largo del tiempo o entre

categoría. Al mostrar la suma de los valores trazados, los gráficos de áreas también

muestran la relación de las partes con el todo.


15


0

2

4

6

8

10

12

14


Resto

Girasol

Maíz

Trigo

Soja

Los gráficos de área tienen los siguientes subtipos de gráfico:

Área apilada Este tipo de gráfico presenta la tendencia del aporte de cada

valor a lo largo del tiempo o entre categorías.

Área 100% apilada Este tipo de gráfico presenta la tendencia del aporte

porcentual de cada valor a lo largo del tiempo o entre categorías.


16

0%

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%



Resto

Girasol

Maíz

Trigo

Soja

Público

Nivel general60

70

80

90

100

110

120

2001 2002 2003 2004 2005 2006 2007 2008 2009 2010

Evolución Salarios reales

Público Privado no registrado Nivel general Privado registrado

Gráficos circulares

En un gráfico circular podemos representar datos contenidos en una columna o una

fila de una hoja de cálculo. Los gráficos circulares muestran el tamaño de los

elementos de una serie de datos, en proporción a la suma de los elementos. Los

puntos de datos de un gráfico circular se muestran como porcentajes del total del

gráfico circular.


17

Los gráficos circulares presentan los siguientes subtipos de gráfico:

Circular: Los gráficos circulares muestran la contribución de cada valor a un total

con un formato 2D o 3D. Podemos extraer manualmente sectores de un gráfico

circular para destacarlos.


18

48%

19% 17%

9%

7%

16%

Las cinco economías más grandes

EEUU China Japón Alemania Francia

Circular con subgráfico circular y circular con subgráfico de barras Los

gráficos circulares con subgráfico circular o subgráfico de barras son gráficos

circulares con valores definidos por el usuario que se extraen del gráfico circular

principal y se combinan en un gráfico secundario, circular o de barras apiladas.

Estos tipos de gráficos son útiles cuando deseamos que los sectores pequeños del

gráfico circular principal se distingan más fácilmente.

Circular con subgráfico de barras


19

Circular seccionado Los gráficos circulares seccionados muestran la contribución

de cada valor a un total, al mismo tiempo que destacan valores individuales.

Los gráficos circulares seccionados se pueden mostrar en formato 3D. Si deseamos

extraer los sectores manualmente, debemos utilizar un gráfico circular o un gráfico

circular 3D.

Histograma

Un Histograma es un gráfico de barras para representar datos numéricos agrupados

en intervalos de clase, en los que las frecuencias o porcentajes de cada grupo de datos

están representados por barras individuales. La variable estudiada se representa en el

eje x mientras que las frecuencias o los porcentajes se representan en el eje y.


20

Ojiva

El polígono de porcentaje acumulado u ojiva muestra la variable de interés a lo largo

del eje x y los porcentajes acumulados en el eje y

Pasos a seguir en la construcción de un gráfico

1. Seleccionamos el rango de datos a graficar, luego pulsamos la pestaña

Insertar, Grupo Gráficos, en la cinta de opciones:

2. Escogemos el tipo de gráfico pedido, en este caso elegimos Columnas (Paso I

del Asistente) y el subtipo.


21

Inmediatamente después de elegir el tipo de gráfico, el mismo se mostrará en la hoja

de cálculo actual, y en la cinta de opciones se mostrarán nuevos botones que nos

permitirán modificar dicho gráfico.

Otro modo de insertar gráficos es pulsando la flecha destacada, en el grupo Gráficos,

la cual inicia el cuadro de diálogo Insertar gráfico.


22

En dicho cuadro podremos seleccionar el tipo de gráfico y el subtipo a insertar.

Inmediatamente después de elegir el tipo de gráfico, el mismo se mostrará en la hoja

de cálculo actual, y en la cinta de opciones se mostrarán nuevos botones que nos

permitirán modificar dicho gráfico.

Modificar un gráfico

Una vez confeccionado el gráfico, se puede cambiar su tipo, la asignación de datos de

origen, volver a ver las opciones de gráfico que se configuraron al momento de su

creación y la ubicación del mismo. Dichas opciones se encuentran disponibles en la

cinta de opciones Herramientas de gráficos, pestañas: Diseño, Presentación y

Formato.

Pestaña diseño

En primer lugar encontramos la pestaña Diseño, incluida en la Barra

Herramientas de gráfico, desde la cual tendremos disponibles los siguientes

grupos


23

Pestaña Presentación

En segundo lugar encontramos la pestaña Presentación, incluida en la Barra

Herramientas de gráfico, desde la cual tendremos disponibles los siguientes grupos:

Pestaña Formato

La pestaña Formato, incluida en la Barra Herramientas de gráfico, contiene los

siguientes grupos.

Cómo crear un Histograma

Para crear un Histograma en primer lugar debemos agregar, en la cinta de opciones, la

opción Análisis, para lo cual:

1. Pulsamos sobre el botón de Office y en el menú que se despliega pulsamos

sobre la opción Opciones de Excel.

2. Hacemos clic en Complementos, y luego en Herramientas para análisis-

VBA.

3. Seguidamente pulsamos sobre el botón Ir.


24

4. En la ventana que se muestra pulsamos sobre Herramientas para análisis y

aceptamos.


25

En la pestaña Datos se mostrará el nuevo botón Análisis.

Trabajaremos con el archivo Estadística descriptiva con Excel, Hoja2.

Para trabajar más cómodamente le asignamos al rango que contiene todos los valores

observados (A10:A59) un nombre, por ejemplo, “datos” (Pestaña Fórmulas, Asignar

nombre a rango, Definir nombre). Con dicho conjunto de datos crearemos diez

intervalos de clase.

Luego calculamos los siguientes datos (a la derecha se muestran las fórmulas

utilizadas):

D E

10 Máximo 97 =max(datos)

11 Mínimo 8 =min(datos)

12 rango 89 =D10-D11

13 Nº de clases 10

14 Amplitud intervalo 8,9 =E12/E13

Luego calculamos el límite inferior y superior de cada clase (a la derecha se muestran

las fórmulas utilizadas):

D E

Límite inferior Límite superior Fórmulas utilizadas en cada caso

18 8 16,9 =E11 =D18+$E$14

19 16,9 25,8 =E18 =D19+$E$14

20 25,8 34,7 =E19 =D20+$E$14

21 34,7 43,6 …. ……

22 43,6 52,5

23 52,5 61,4

24 61,4 70,3

25 70,3 79,2

26 79,2 88,1

27 88,1 97

Luego elegimos la pestaña Datos, botón Análisis y en la ventana que se abre

tildamos la opción Histograma:

Arrastramos la fórmula al resto de las celdas


26

Completamos los cuadros del siguiente modo:

La tabla de frecuencias y el histograma se mostrarán del siguiente modo:

Rango que contiene los valores correspondientes al límite superior de todas las clases

Rango que contiene el total de observaciones, en este caso le asignamos como nombre “datos”

Celda a partir de la cual se mostrará la tabla de frecuencias

Tildamos las opciones Porcentaje acumulado y Crear Gráfico

Estadística con Excel

Documents

Transcript of Estadística con Excel