Guia Excel Estadistica Aplicada

7/25/2019 Guia Excel Estadistica Aplicada

1/39

UNIVERSIDAD NACIONAL ABIERTAVICERECTORADO ACADMICOREA DE MATEMTICA

ELEMENTOS DE EXCEL

PARA LA ESTADSTICA

Prof. Gilberto Noguera

Caracas, 07 de marzo de 2007


2/39

2

Introduccin

En la actualidad existe software especfico, de variada calidad, desarrolladores, precio y

herramientas, para el tratamiento adecuado y efectivo de datos estadsticos. Sin embargo una serie

de factores que no viene al caso mencionar, as como la inquietud de nuestros participantes en los

cursos correspondientes a las asignaturas Inferencia Estadstica, Estadstica General y Estadstica

Aplicada, nos han motivado para realizar estas breves notas referidas a la hoja de clculo Excel

proporcionada con el software Microsoft Office.

Estas pginas no pretenden constituirse en un manual sobre el uso y potencialidad de Excel, slo

orientaran en los aspectos bsicos para el manejo estadstico de datos. Se recomienda, al lector de

estas notas, que al finalizar la lectura de las mismas proceda a estudiar con la ayuda proporcionada

por Microsoft Excel las caractersticas y uso de las funciones correspondientes a las utilidadesInsertar funciones y Anlisis de datos. La informacin suministrada, por las ayudas, respecto a una

funcin en particular se puede imprimir y de esta forma tener una gua ms detallada de rpida

consulta. Se utiliz la versin Microsoft 2003 y el sistema operativo Xp. Si embargo, las

directrices referidas a Excel son validas para Windows 98 o superior. En esta versin, siguiendo

las sugerencias de los estudiantes UNA del curso 746, slo agregamos una parte referida al uso de

la utilidad Regresin de la herramienta Anlisis de Datos de Excel, aspecto que no se haba tratado

en la versin publicada en el 2006.


3/39


4/39

4

Fig. 3

Si el icono de acceso directo a Excel no est anclado en el men de inicio, debemos buscar este

programa en el computador. Esta bsqueda se puede hacer de varias maneras: activando el motor

de bsqueda representado en el men de inicio con el icono lupa (ver figura 3), revisando el disco

duro, etc.

La forma ms sencilla de hacer la bsqueda es desplegar todos los programas colocando el puntero

del ratn en todo los programas o programas en el men de inicio, luego desplazar el puntero hasta

Microsoft Office y revisar si se encuentra el icono de Excel,

Fig. 4

Nota:La forma en que aparece la primera pantalla, al encender el computador, recibe el nombre

de escritorio. La presentacin puede variar segn la configuracin definida por el usuario.


5/39

5

Si en el cuadro desplegado de programas no se encuentra el icono de Excel, lo ms probable es que

no est instalado el programa.

En este caso es necesario instalar el Excel, si la mquina es personal y no tiene experiencia en la

instalacin solicite ayuda. Si la computadora es propiedad de un Cyber pida al encargado(a) del

mismo que instale el Excel, si la computadora est instalada en el Centro Local o Unidad de

Apoyo, solicite con toda confianza al encargado de informtica la instalacin del software y

la orientacin que considere necesaria.

2.

Revisin de los componentes necesarios para el tratamiento de datos estadsticos.

Una vez comprobada la instalacin del Excel, debemos revisar si los componentes que facilitan el

tratamiento estadstico de datos estn habilitados, para ello abrimos la hoja de clculo de Excel,

debe aparecer una interfaz como la que se muestra en la figura 5.

Fig. 5

Si en la pantalla aparece la hoja de clculo, mostrada en la figura 5, el Excel se ha instalado

correctamente. Sin embargo, debemos asegurarnos que la instalacin ha sido completa, para poder

hacer uso de todas las herramientas de Excel, en particular las referidas a estadsticas.

Haciendo clic en insertar, ubicado en la barra de herramientas, se debe desplegar un men como el

mostrado en la figura 6.


6/39

6

Fig.6

Ahora haciendo clic en funcin, debe mostrarse un cuadro de dialogo llamado insertar funcin

(Fig. 7). Este cuadro de dialogo le permite:

Si conoce el nombre de la funcin, buscar con el motor escribiendo en el cuadro

correspondiente el nombre de la funcin que desea usar, y luego haciendo clic en ir.

Seleccionar la categora de las funciones en que est interesado, en nuestro caso, estadsticas.Para seleccionar estadsticas debe hacer clic en la flecha de despliegue, indicada como 1 en la

figura 7, ubicar, usando la barra de deslizamiento, estadsticas y hacer clic en esta ltima. As

aparecer un cuadro de seleccin de las diferentes funciones estadsticas que posee Excel, en la

figura 7 se indica con el nmero 2. Al seleccionar una funcin, se te informa de manera breve,

sobre los argumentos de entrada requeridos por la funcin as como la salida que produce, en la

figura 7 indicamos este hecho con el nmero 3.

Fig. 7

1

2

34


7/39

7

Si considera insuficiente la informacin sobre la funcin presentada en el cuadro de dialogo

insertar funcin, puede solicitar ayuda haciendo clic en ayuda sobre esta funcinsealado en la

figura 7 con el nmero 4, en este caso se abrir un cuadro informativo al lado derecho de la hoja de

clculo como se muestra en la figura 8. Este cuadro informativo llamado Ayuda de Microsoft

Excel, te proporciona informacin detallada de la funcin que deseas utilizar, as como un ejemplo

de su uso. Esta informacin se puede imprimir haciendo clic en el icono que representa una

impresora, indicado en la figura 8 con el nmero 1. Se recomienda hacer el ejemplo para ver como

funciona en la prctica.

Fig. 8

1

Otra herramienta de inters para los estudios estadsticos que proporciona Excel es el componente

para el anlisis de datos. Si la instalacin de Excel ha sido completa se puede habilitar este

componente, para ello, primero haga clic en herramientas ubicado en la barra de herramientas de

Excel, como se muestra en figura 9, para desplegar las utilidades disponibles.

Fig. 9


8/39

8

Ahora haciendo clic en complementos aparecer la siguiente interfaz,

Fig. 10

Seleccione herramientas para anlisis como se muestra en la figura 11, y haga clic en aceptar.

Fig. 11

Si este componente se ha habilitado correctamente, al hacer clic en herramientas el componentedebe aparecer en el listado de herramientas como lo muestra la figura 12, e indicado con una

flecha.

Fig. 12


9/39

9

Una poderosa herramienta para la transformacin de variables lo constituyen los macros, que son

funciones programadas en Visual Basic. Para revisar si esta herramienta est a nuestra disposicin,

hacemos clic en herramientas ubicada en la barra de herramientas, localizamos Macro y colocando

el puntero en esta etiqueta se desplegar un cuadro con informacin sobre las tareas permitidas, tal

como se muestra en la figura 13.

Fig. 13

Para terminar la revisin y puesta a punto de las herramientas y utilidades proporcionadas por

Excel para el tratamiento de datos en el rea de la estadstica, hagamos clic en Editor de Visual

Basic o presionemos las teclas Alt + F11 para que aparezca el espacio de trabajo como se muestra

en la figura 14.

Fig. 14


10/39

10

Las versatilidad de esta herramienta de programacin es realmente alta, que dominarla en su

totalidad requerira tiempo y esfuerzo, ameritando un curso especfico de varias semanas y quizs

meses. Nuestras necesidades slo se limitan a poder insertar en la hoja de clculo Excel mdulos

que efecten de manera rpida y eficiente clculos sobre la variable o variables que estamos

tratando en un trabajo estadstico, y que de hacerlo slo con las funciones de Excel

proporcionadas por insertar funciones implicara tener que usar varias columnas. Verifiquemos

que contamos con esta utilidad, para ello hagamos clic en insertar (ver figura 15) y luego en

mdulo, si todo esta bien debe aparecer una ventana llamada espacio de trabajo, como se ilustra en

la figura 16.

Fig. 15

Fig. 16

Si se abre este sitio de trabajo nuestro equipo esta bien, slo nos resta resaltar la importancia del

uso de la ayuda correspondiente a Visual Basic, no olvide que en esta parte estamos programando,


11/39

11

al hacer clic en ayuda o teclear F1 se debe desplegar a la derecha el cuadro de Ayuda de Visual

Basicen el encontrar toda informacin requerida para programar en este lenguaje (ver figura 17).

Fig. 17

Ahora estamos seguros de contar con todo lo necesario para empezar a realizar los trabajos

estadsticos bsicos correspondientes a las asignaturas, Inferencia Estadstica, Estadstica General

y Estadstica Aplicada. Puede cerrar todas las ventanas confiado(a) de contar, si no sucede algo

extraordinario, con un paquete adecuado para satisfacer los requerimientos mnimos exigidos en

las asignaturas arriba mencionadas cuyos cdigos son 738, 745 y 746 respectivamente.


12/39

12

PARTE II

En esta parte nos dedicamos, a ilustrar los aspectos bsicos de algunas herramientas de Excel. Para

ello trabajaremos con la siguiente data constituida por 50 casos,

Fig. 18

Variables

Casos u observaciones

Empecemos por la estadstica descriptiva referida a la variable edad, hacemos clic en herramientas

y de seguido clic en anlisis de datos. Seleccionemos Estadstica descriptiva, ver figura 19

Fig. 19

Hacemos clic en aceptar para activar el cuadro de dialogo correspondiente a la utilidad Estadstica

descriptiva.


13/39

13

Fig. 20

Colocamos el puntero del ratn, sobre la columna a analizar, en nuestro ejemplo edad, como se

puede ver en la figura 20, indicamos que la columna tiene un rotulo en la primera fila y

seleccionamos resumen de estadsticas, como se muestra en la siguiente figura.

Fig. 21

Al hacer clic en aceptar, por defecto el programa colocar los resultados en una nueva hoja del

mismo libro, al menos que indiquemos lo contrario, seleccionando rango de salida e indicando en

la ventana correspondiente a partir de que celda se deben colocar los resultados, en muestro caso

G1, como se ilustra en la figura 22.


14/39

14

Fig. 22

Al hacer clic en aceptar obtenemos,

Fig. 23

No entraremos a explicar el significado de cada elemento de los resultados arrojados por Excel, se

entiende que el participante ha estudiado la teora correspondiente, solo indicamos que estos

resultados corresponden a los datos no agrupados. En la prctica se trabaja con datos agrupados,


15/39

15

aprovechemos para ver como funciona el Visual Basic, calcularemos el tamao aproximado de

los intervalos de clase. Para esto insertemos un mdulo tal como se muestra en la figura 24

Fig. 24

Ahora guardemos el mdulo y regresemos al rea de trabajo de Excel, haciendo clic en archivo y

de seguido clic en cerrar y volver a Microsoft Excel.

Ahora haremos uso de la funcin tamintcreada anteriormente, para ello seleccionamos la celda

donde queremos tener el resultado, G18, figura 25.

Fig. 25


16/39

16

insertamos la funcin como se explico en la primera parte, pero seleccionamos Definidas por el

usuario como se muestra en la figura 26

Fig. 26

Al hacer clic en aceptar aparecer un cuadro de dialogo solicitando los argumentos de entrada de

la funcin que se ha llamado (tamint), ver la figura 27, selecciona la celda que contiene el

mximo.

Fig. 27


17/39

17

Luego hacemos clic en la ventana Mi para informar que introduciremos el mnimo,

Fig. 28

Por ltimo introducimos el nmero de observaciones al lado de cuenta en la tabla edad, aceptamos

y obtenemos el resultado, ver figuras 29 y 30.

Fig. 29


18/39

18

Fig. 30

En realidad el uso de Visual Basic se recomienda cuando los clculos son ms complicados o las

transformaciones de los datos de una o ms variables implican, por el nmero de operaciones o

facilidades de Excel, usar ms de una columna para obtener el resultado. Otra forma de obtener el

mismo resultado sin usar el Visual Basic es: seleccionar la celda donde quiere dejar el resultado

del clculo, digitar el smbolo = o el smbolo +, y luego escribir la frmula de manera especfica es

decir, dando los valores involucrados en el clculo, ver figura 31, 32 y 33.

Fig. 31


19/39

19

Fig. 32

Fig. 33

Es claro que el clculo del tamao del intervalo de clase debe hacerse una sola vez. Conocido el

tamao del intervalo que asumiremos como 6, procedemos a determinar los lmites superiores de


20/39

20

cada intervalo, para ello seleccionamos la celda y la columna donde ubicaremos las clases, luego

procedemos como ilustran las figuras 34 y 35

Fig. 34

Al presionar enter, se obtiene el lmite superior de la primera clase, si dividimos el rango entre el

tamao del intervalo de clase, o simplemente el tamao de clase, tenemos el nmero de intervalos

o clases necesarios para agrupar los datos, en este ejemplo 42/6=7, as que procedemos a

determinar los lmites superiores de las clases restantes en este caso 6, ver figura 35.

Fig. 35

Ahora podemos obtener la informacin requerida para hacer un histograma, o un diagrama de

torta. Para ello habilitamos la herramienta anlisis de datos, figura 36, y seleccionamos

histograma.


21/39

21

Fig. 36

Al hacer clic donde se indica, aparecer la siguiente interfaz, ver figura 37

Fig. 37

Seleccionamos la columna que estamos analizando (edad), y luego cerramos (x)


22/39

22

Fig. 38

Fig. 39

Hacemos clic dentro de la ventana Rango de clases, y seleccionamos las clases como se muestra

en la siguiente figura.


23/39

23

Fig. 40

Podamos haber seleccionado Crear grfico pero, es nuestra opinin que didcticamente es ms

conveniente utilizar las herramientas grficas de Excel. Al aceptar en una hoja nueva tendremos el

siguiente resultado.

Fig. 41

Editamos los resultados anteriores de manera que su presentacin sea la siguiente,


24/39

24

Fig. 42

Para obtener la grfica del histograma procedemos a seleccionar como se puede ver en la siguiente

figura, y habilitamos las utilidades grficas haciendo clic en el icono correspondiente.

Fig. 43

Hacemos clic en siguiente,

Fig. 44


25/39

25

Hacemos clic en siguiente,

Fig. 45

Colocamos los ttulos, en las casillas correspondientes y haciendo clic en siguiente obtenemos. Fig. 46

Por ltimo terminamos de editar el grfico; primero unamos las barras, colocamos el puntero sobre

una barra y hacemos clic con botn derecho del ratn,


26/39

26

Fig. 47

Hacemos clic en formato de serie de datos,

Fig. 48

Este cuadro le permite editar lo referente a la serie de datos (barras), hacemos clic en opciones y

disminuimos a cero el ancho del rango.


27/39

27

Fig. 49

Al aceptar obtenemos,

Fig. 50


28/39

28

Podemos terminar de editar la grfica haciendo clic con el botn derecho del ratn, en los

elementos del grfico que deseamos modificar, se abrir un cuadro de dialogo indicando las

posibilidades de cambios a realizar, selecciona lo que deseas y presiona aceptar.

Fig. 51

Recordemos que todos los objetos creados en Microsoft Excel se pueden copiar y pegar en un

documento Microsoft Word, por otro lado si un grfico requiere una edicin que vaya ms all de

las facilidades de Excel, puede utilizar el software Dibujo Paint. Ahora recrearemos el uso de

Visual Basic para un caso ms complicado que el dado anteriormente, suponga que debe

transforma los datos contenidos en las columnas C y E, para obtener una nueva variable cuya

ecuacin es2

3

( )edad tiempoVar

edad

+=

5

Entonces como ya sabemos, abrimos la estacin de trabajo de Visual Basic,


29/39

29

Fig. 52

Cerramos, volvemos a Excel, e insertamos la funcin.

Fig. 53

Fig. 54


30/39

30

Como indicamos la primera vez que usamos, esta herramienta se introduce los datos (argumentos)

y al aceptar obtenemos,

Fig. 55

Ahora seleccionamos y copiamos la celda F2, colocamos el puntero en F3, y manteniendo el botn

izquierdo presionado arrastramos sin soltar hasta F51(figura 56).

Fig. 56

Soltamos el botn y pegamos digitando Control + v, el resultado es el clculo para todas las

observaciones de Var.

Fig. 57


31/39

31

Como comentamos arriba, esto no cubre todas las utilidades de Excel y mucho menos las

potencialidades Visual Basic, sin embargo se han tocado de alguna manera las tcnicas bsicas

para aproximarnos al trabajo mnimo que se puede hacer al analizar datos estadsticos.


32/39

32

PARTE III

En esta parte ilustramos, de manera prctica, el uso de la utilidad Regresin de la herramienta

Anlisis de datos, de la hoja de clculo Excel. Para ello utilizaremos una data pequea, en la

confianza de que el lector deducir de lo tratado anteriormente que la hoja de clculo Excel es

capaz de procesar informacin ms amplia que la del siguiente ejemplo.

La figura 58, muestra una data que consta de cuatro variables y 20 observaciones.

Fig. 58

En el cuadro de texto en las filas 22 a 25 muestra los modelos que se ajustaran a la datasuministrada. Para iniciar el proceso haga clic en herramientas ubicada en la barra de la

aplicacin Excel, debe aparecer una lista tal como se muestra en la figura 59,Fig. 59


33/39

33

De esta lista seleccione y haga clic en anlisis de datos, y aparecer un cuadro de dialogo como elque se muestra en la figura 60,

Fig. 60

Seleccione Regresin y haga clic en aceptar, emerger un cuadro de dialogo, observedetenidamente la figura 61.

Fig. 61


34/39

34

Donde dice Rango Y de entrada se debe seleccionar todos la informacin que corresponde a lavariable independiente o explicada, en el ejemplo, como se ha indicado en la parte II, se arrastrael cursor desde la celda A1 hasta la celda A21 correspondientes a la columna A, aparecer quehas seleccionado $A$1:$A$21. Haciendo clic en donde dice Rango X de entrada, luegocolocando el cursor en B1 y seleccionando todas las variables independientes Y, Z y W hasta lafila 21, aparece en la ventana correspondiente, la siguiente informacin $B$1:$E$21 indicandoque has seleccionado toda la informacin correspondiente a las variables independientes o

explicativas del modelo X = aY+bZ+cW +d.Como se han incluido los encabezados, X, Y, Z y W se debe indicar que se tomaron los rtulos,observe que el modelo que pretendemos estudiar tiene el trmino independiente diferente de ceropor tal motivo no se activa la casilla Constante igual a cero, en nivel de confianza si no se dicenada en contrario se trabaja por defecto al 95%, por lo tanto no se selecciona la casillacorrespondiente a esta informacin. En Opciones de salida se recomienda seleccionar hoja nueva,que en algunos casos esta por defecto, si indicamos en el cuadro de dilogo que queremos comoresultados: Residuos, Residuos estndares, Grfico de residuales, Curva de regresin ajustada yGrfico de probabilidad normal, es preferible que sobre informacin, la cual podemos borrar, aque falte. Ahora hacemos clic en aceptar y obtenemos un cuadro resumen con todos losestadstico bsicos referidos a la regresin y un conjunto de grficos de los cuales escogemos los

de inters. En la figura 62 se muestra las grficas resultantes en la hoja 4.

Fig. 62


35/39

35

En esta misma hoja aparecen los resultados numricos correspondientes al modelo, y quepresentamos a continuacin, figura 63.

Fig. 63

Resumen

Estadsticas de la regresin

Coefi cie nte de co rr el ac in ml tipl e 1Coeficiente de determinacin R^2 1

R^2 ajustado 0.941176471

Error tpico 2.38621E-15

Observaciones 20

ANLISIS DE VARIANZA

Grados de li bertad Suma de cuadrados Promedio de l os cuadrados F Valor c rti co de F

Regresin 3 920.6167597 306.8722532 8.0841E+31 1.1981E-249

Residuos 17 9.67979E-29 5.69399E-30

Total 20 920.6167597

Coeficientes Error tpico Estadstico t Probabilidad Inferior 95% Superior 95% Inferior 95.0% Superior 95.0%

Intercepcin -20 6.77413E-15 -2.95241E+15 5.5738E-254 -20 -20 -20 -20

Y 0 0 65535 #NUM! 0 0 0 0

Z 5 1.19758E-15 4.17508E+15 1.5412E-256 5 5 5 5

W 0 1.47349E-15 0 1 -3.10879E-15 3.10879E-15 -3.10879E-15 3.10879E-15

Anlisis de los residuales Resultados de datos de probabilidad

Observacin Pronstico X Residuos Residuos estndares Percentil X

1 5.046540727 -6.21725E-15 -1.169104844 2.5 5.046540727

2 18.2923368 -3.55271E-15 -0.668059911 7.5 5.048066652

3 16.60847804 0 0 12.5 8.675954466

4 10.37125767 -3.55271E-15 -0.668059911 17.5 9.74181341

5 16.84194464 -7.10543E-15 -1.336119822 22.5 9.971465194

6 9.971465194 -7.10543E-15 -1.336119822 27.5 10.37125767

7 28.00485244 -3.55271E-15 -0.668059911 32.5 13.99838252

8 13.99838252 -3.55271E-15 -0.668059911 37.5 14.18759728

9 26.15466781 -3.55271E-15 -0.668059911 42.5 16.60847804

10 20.52171392 -3.55271E-15 -0.668059911 47.5 16.83507797

11 8.675954466 -8.88178E-15 -1.670149777 52.5 16.84194464

12 26.67500839 -7.10543E-15 -1.336119822 57.5 17.0639668

13 24.10611286 0 0 62.5 18.2923368

14 18.58149968 -3.55271E-15 -0.668059911 67.5 18.58149968

15 14.18759728 -5.32907E-15 -1.002089866 72.5 20.52171392

16 9.74181341 -3.55271E-15 -0.668059911 77.5 23.45530564

17 5.048066652 -5.32907E-15 -1.002089866 82.5 24.10611286

18 23.45530564 -3.55271E-15 -0.668059911 87.5 26.15466781

19 17.0639668 -3.55271E-15 -0.668059911 92.5 26.67500839

20 16.83507797 -1.06581E-14 -2.004179733 97.5 28.00485244

Slo queda, como pueden ver, interpretar los resultados dentro del marco terico correspondiente.Para finalizar expondremos una forma de transformar variables, cuado la misma es sencilla, sinnecesidad de usar el Visual Basic.El segundo modelo que se pide analizar es: X=aY+bZ

2+cW. Como el Excel slo permite en la

herramienta Anlisis de datos (Regresin) trabajar con columnas adyacentes, transformamos enla hoja 1 la variable o variables de inters tal como se muestra en las siguientes figuras.


36/39

36

Fig. 64

Fig. 65


37/39

37

Fig. 66

Ahora en una nueva hoja (hoja 2) procedemos a copiar las columnas de inters como son: A, B, Ey D, quedando registrada la informacin de esta manera,

Fig. 67


38/39

38

Procedemos desde esta hoja (hoja2) igual que cuando se trabajo el primer modelo, tomando laprevisin de activar la casilla Constante igual cero. Los resultados numricos se muestran acontinuacin,

Fig. 68

Igual que en el primer caso slo queda interpretar los resultados, la mquina hace todos losclculos.

Se recomienda revisar la ayuda (help) de Excel si es necesario complementar la informacin aqusuministrada, o si requiere el uso de otras utilidades como por ejemplo pruebas de hiptesis,generacin de nmeros aleatorios, anlisis de varianza etc.

Cualquier sugerencia que sirva para mejorar este material se puede realizar a travs del [email protected]
mailto:[email protected]:[email protected]


39/39

BIBLIOGRAFA

Levin Richard, Rubin David. Estadstica para Administracin y Economa, Sptima edicin,Editorial PEARSON Prentice Hall. Mxico.

Ayuda del software Excel.

Documents

Guia Excel Estadistica Aplicada