Introduccion Excel 2007, Apuntes de Estadística

¡Descarga Introduccion Excel 2007 y más Apuntes en PDF de Estadística solo en Docsity!

INTRODUCCIÓN A EXCEL 2007

CURSO DE INTRODUCCIÓN A LA ESTADÍSTICA

Departament d’Estadística i Investigació Operativa

Universitat de València

1.- MANEJO DE DATOS Y FÓRMULAS

Al abrir el programa nos encontramos con una cuadrícula en la que las columnas se denotan con letras (A,B,C,…) y las filas con números (1,2,3,…). En cada casilla podemos escribir texto, números o fórmulas. Excel es una hoja de cálculo , lo que quiere decir que su propósito es precisamente calcular expresiones matemáticas. Así por ejemplo, si en la casilla B2 hemos introducido el número 5, y en la casilla B3 el número 7 y queremos calcular su suma, podemos introducir en la casilla B4 la expresión “=B2+B3” obteniendo el valor de dicha suma. Notad que el símbolo “=” indica que el programa ha de calcular la expresión que viene a continuación y no lo trata como texto sino como una expresión matemática.

En ocasiones podemos ver que se intercala el símbolo “$” junto a la referencia de una celda, por ejemplo“$B$2”. Esto indica que la referencia de la celda es absoluta (con el símbolo $) y no relativa (sin el símbolo $). No afecta al cálculo que se realiza en la celda, sólo actúa cuando copiamos la fórmula en otra celda. Si por ejemplo copiamos la fórmula "=B2+B3" que está en la celda B4 a la celda C4, veremos que el resultado de la copia es "=C2+C3" tal y como muestra la figura siguiente. Este "traslación automática" puede ser muy útil cuando tenemos un conjunto de datos en celdas correlativas y queremos aplicarles a todos la misma fórmula.

Sin embargo, puede que no queramos que alguna parte de la fórmula se traslade al copiar, sino que se mantenga fija. Por ejemplo, si queremos calcular las diferencias respecto a Enero, en Febrero tendremos la expresión “=C4-B4”, pero si la queremos trasladar a Marzo, sin que cambie la referencia a Enero (B4) hemos de poner “=C4-$B4”

3.- Editar el gráfico. Se selecciona con el botón izquierdo del ratón el elemento a editar y al pulsar el botón derecho aparece un menú de opciones relativas a ese elemento.

Las tres gráficas que se incluyen abajo (barras, sectores, líneas) son ejemplos de representación de los mismos datos en formas diferentes. En cada caso, se ha de elegir la que mejor ilustre la información que se desea transmitir.

Nota: Las gráficas representan directamente series de datos ya existentes. Si previamente queremos agruparlos en frecuencias, necesitamos un procedimiento previo antes de representarlos, tal como se explica en las secciones siguientes.

3.- ACCESO AL MÓDULO DE ANÁLISIS DE DATOS

Los procedimientos estadísticos no están en el conjunto de funciones estándar de EXCEL, sino que pertenecen al módulo de Análisis de Datos. Si el módulo está instalado, aparecerá en el extremo derecho del menú de Datos. Si no aparece, se ha de instalar. Pulsando en el icono de Office (esquina superior izquierda) aparece una ventana y en ella, abajo, Opciones de Excel. Se ha de seleccionar Complementos , en la ventana que se abre pulsar en Ir… , y en la lista de Complementos marcar la opción Herramientas para análisis. Al pulsar Aceptar, se instala.

4.- TABLAS DE FRECUENCIAS E HISTOGRAMAS

Cuando se pulsa sobre la opción Análisis de Datos , aparece la lista de procedimientos estadísticos disponibles, en la que está Histograma :

Al pulsar en Histograma , se abre la ventana en la que se ha de indicar los elementos con los que construir, primero la Tabla de Frecuencias y, si se desea, el Histograma.

• Rango de entrada : los datos a tabular y representar
• Rango de clases : al construir la Tabla de Frecuencias, Excel no distingue entre variables discretas o continuas. Si esta casilla se deja vacía, Excel construye sus intervalos, generalmente bastante desafortunados, por lo que esta opción no es recomendable. Es conveniente fijar los valores o los intervalos antes de llamar a este procedimiento. o Variables discretas : Crear una columna auxiliar en la hoja de datos, con los valores que puede tomar la variable o Variables continuas : Decidir previamente los intervalos que se consideren adecuados (calculando mínimo y máximo, decidiendo el número de intervalos, su amplitud y el punto inicial del primer intervalo) e introducir en una columna auxiliar los extremos superiores de los intervalos. Esa columna auxiliar es la que se indica en la casilla de Rango de clases
• Rótulos : para indicar si la primera fila de la columna de datos contiene el nombre de la variable
• Rango de salida : posición en la que se colocarán los resultados. Es conveniente marcar la opción de “En una hoja nueva”
• Crear gráfico : si se marca, además de la tabla de frecuencias, dibuja el Histograma.

Ejemplo 1: en el fichero Smoking.xslx la variable TYPCIG tiene 5 valores posibles (1, 2, 3, 4, 5), que corresponden a las categorías “Normal con filtro”, “Normal sin filtro”, “Light”, “Ultralight” y “Otros”. Para crear la Tabla de Frecuencias deseada, se incluye una columna auxiliar con el

nombre “Int_TYPCIG” y los cinco valores (1, 2, 3, 4, 5).

5.- CÁLCULO DE ESTADÍSTICOS

En la lista de procedimientos del Análisis de Datos , aparece Estadística Descriptiva. En la ventana correspondiente se indica qué variable se quiere estudiar (en Rango de entrada) y se marca la opción Resumen de estadísticos.

Para la variable YRMSK se obtiene:

Esta tabla no contiene cuartiles ni percentiles, pero éstos pueden obtenerse utilizando las funciones de Excel CUARTIL y PERCENTIL. Por ejemplo, para la variable YRMSK el primer cuartil es 16, el tercer cuartil 30,25 y el percentil del 90% es 39.

6.- ANÁLISIS DE LA RELACIÓN ENTRE DOS VARIABLES

En la lista de procedimientos de Análisis de Datos aparece la opción Regresión. En la ventana correspondiente se ha de indicar la variable dependiente Y y la variable independiente X, y se ha de marcar la opción Curva de regresión ajustada para que dibuje la nube de puntos. Asimismo, pueden marcarse otras opciones, como Gráfico de residuales para estudiar la validez del modelo de regresión.

Ejemplo: En el fichero Horas_estudio.xslx aparecen las horas de estudio de 12 estudiantes y la nota obtenida en el examen. Indicando que la variable independiente X es el número de horas estudiadas y la dependiente Y la nota obtenida, el procedimiento calcula:

En la primera tabla aparece el coeficiente de correlación r , y el coeficiente de determinación r^2_._ En la segunda, los coeficientes de la recta de regresión Y=aX+b. El coeficiente b es el que aparece en la fila “Intercepción” y el coeficiente a es el que aparece en la fila “Horas”. En este caso, la recta obtenida sería Y= 0,052X+1,979. También aparecen los errores estándar de las estimaciones y los resultados de los test en los que se contrasta como hipótesis nula si cada coeficiente es igual a 0.

Utilizando la recta obtenida, puede hacerse la estimación del valor de Y que correspondería a un determinado valor de X. Por ejemplo si x=80, y=0,052*80+1,979 = 6,14.

También aparecen a la derecha las gráficas solicitadas. La más importante es la gráfica de dispersión:

Esta gráfica ha sido editada para:

• Eliminar los valores pronosticados (en rosa) asociados a cada valor observado.
• Añadir la recta de regresión, con la opción “Agregar línea de tendencia”, eligiendo “Lineal” y pidiendo que incluya en el gráfico la ecuación de la recta y el r 2 , que ya estaban en las tablas.

El procedimiento Regresión también permite realizar regresión múltiple, en la que hay más de una variable independiente, X 1 , X 2 ,…. Todos los valores de estas variables se pueden incluir en la casilla “Rango X de entrada”, pero se ha de hacer en bloque, lo que obliga a que las variables independientes estén contiguas en la hoja de datos.

7.- INFERENCIA EN UNA POBLACIÓN

Los procedimientos básicos de inferencia son la estimación, puntual y por intervalos de confianza, y el contraste de hipótesis.

7.1.- Intervalos de confianza

Excel no tiene un procedimiento en el que leer directamente la expresión del intervalo de confianza. Sin embargo, proporciona todos los elementos necesarios para su cálculo de forma muy sencilla.

b) Utilización del procedimiento de 2 muestras

En la hoja de datos se crea una columna auxiliar, con el valor que se quiere contrastar repetido tantas veces como el número de datos de la variable. En Análisis de datos se selecciona el procedimiento Prueba t para dos muestras suponiendo varianzas desiguales. Se introducen las dos columnas a comparar, indicando Diferencia hipotética entre medias = 0. Se obtiene la tabla siguiente, en la que aparece el valor del estadístico test y el p-valor.

8.- Análisis de dos muestras

8.1.- Muestras emparejadas

En el fichero Trigliceridos.xlsx aparecen los datos de los niveles de triglicéridos de una muestra de 7 personas, antes y después de un programa de ejercicios de 10 semanas. El interés del contraste es determinar si el ejercicio reduce el nivel de triglicéridos.

En Análisis de datos seleccionamos el procedimiento Prueba t para dos muestras emparejadas. Introducimos los datos en la ventana, tal como se muestra en la figura:

y obtenemos la siguiente tabla de resultados, en la que aparece información de cada muestra, el estadístico test y el p-valor.

8.2.- Muestras independientes

Utilizando de nuevo el fichero Smoking.xlsx , supongamos que queremos comparar el consumo

de cigarrillos diarios (CIGDAY) en personas que fuman o no fuman dentro de casa (HOUSE).

Los procedimientos del Análisis de Datos de Excel requieren la información de las dos muestras por separado. Si las dos muestras están en lugares diferenciados de la hoja de datos, puede seleccionarse directamente el procedimiento a utilizar. Si, como en nuestro caso, los valores de

las dos muestras, la de valores deCIGDAY de personas conHOUSE=0 y la de valores de

CIGDAY de personas con HOUSE=1, están mezcladas en la misma columna, tenemos

previamente que separarlas, ordenando por valores deHOUSE. Al hacerlo, las 388 primeras

observaciones corresponden aHOUSE=0 y las restantes aHOUSE=1.

Existen 2 procedimientos para contrastar la igualdad de medias de dos muestras independientes, que dependen de que las varianzas de las poblaciones sean iguales o no. Como normalmente dichas varianzas no se conocen, puede acudirse a un test previo de igualdad de varianzas. En Análisis de Datos seleccionamos Prueba F para varianzas de dos muestras. En la ventana correspondiente introducimos la información de las muestras y obtenemos:

Como el p-valor= 0,0007 es muy pequeño, rechazamos la igualdad de varianzas. Por tanto, para comparar las medias (que es nuestro objetivo), seleccionamos el procedimiento Prueba t para dos muestras suponiendo varianzas desiguales , introducimos la información de las muestras, Diferencia hipotética entre las medias = 0 , y obtenemos la tabla de resultados siguiente, con información sobre las muestras, el estadístico test y el p-valor:

observadas ya se tienen en forma de tabla, podemos ir directamente al cálculo de las frecuencias esperadas. Si no es así, y los datos están sin agrupar por categorías, necesitamos crear dicha tabla utilizando el procedimiento de Tablas dinámicas, dentro de la opción Insertar del menú principal. Previamente, hemos de crear una variable auxiliar que valga 1 en

todos los casos,V_muda. Hecho esto vamos a Insertar , y elegimos Tabla Dinámica. En la

ventana correspondiente, en la casilla Tabla o rango , podemos seleccionar la hoja completa de datos y aparecerá una tabla vacía y a la derecha la lista de variables que se pueden utilizar

para definir las filas y columnas de la tabla. SeleccionamosCOND y la arrastramos hasta el

campo de Filas de la tabla (aparecerá el nombre de la variable y sus valores,0, 1, 2, definiendo

las filas). A continuación, seleccionamos la variable auxiliar, V_muda, y la arrastramos al campo

de los Datos. Aparecerá la tabla de los 608 casos clasificados por el valor de la variableCOND.

Ahora ya podemos realizar el resto de cálculos. En una columna introducimos las probabilidades de la hipótesis nula para cada valor (0,3333; 0,3333; 0,3333). Calcular las frecuencias esperada se consigue multiplicando las probabilidades por el total de observaciones, 608. Para calcular el estadístico test χ^2 , calculamos para cada casilla (Frec.observada- frec.esperada)^2 /frec.esperada y sumamos. El p-valor se obtiene con la función de Excel DISTR.CHI

La tabla, con todos los cálculos, sería:

10.3.- Tablas de contingencia

Supongamos que queremos contrastar si el tipo de cigarrillo que se fuma, TYPCIG, es

independiente del sexo,GENDER. Nuevamente,Excel no dispone de un método estadístico que

resuelva directamente este contraste, por lo que nos tendremos que apoyar en sus funciones para realizar los cálculos.

Como en el caso anterior, si las frecuencias observadas no están tabuladas, se puede crear la tabla utilizando el procedimiento de tablas dinámicas. En este caso, arrastramos la variable

TYPCIG al campo de las Filas,GENDER al campo de las Columnas yV_muda al de los Datos.

Con ello tenemos la tabla de frecuencias observadas. La de frecuencias esperadas se obtiene utilizando para cada casilla la expresión: (total fila)x(total columna)/total general. Para calcular el estadístico test χ^2 , calculamos (Frec.observada- frec.esperada)^2 /frec.esperada en cada casilla y sumamos. El p-valor se obtiene con la función de Excel DISTR.CHI