0.3 Tutorial Excel

Dpto. Métodos de Investigación y Diagnóstico en Educación

1


2 Página

0. Introducción 3

I. Procesos Básicos en Excel 4

1. Estructura general del programa y de los ficheros 4

2. Creación y gestión de un fichero de trabajo nuevo 7

3. Importación de Datos desde otras aplicaciones 11

4. Conceptos básicos tratados 11

II. Análisis Estadístico de Datos con Excel 13

1. Diseño de la base de datos 13

2. Análisis de datos con fórmulas y funciones 14

2.1. Uso de fórmulas 14

2.2. Uso de funciones 16

2.2.1. Algunas observaciones sobre Funciones Estadísticas en Excel 20

3. Análisis de Datos con Tablas dinámicas 20

3.1. Ejemplo A: Tablas dinámicas con una sola variable: Distribución de frecuencias y

porcentajes

21

3.2. Ejemplo B: Tablas dinámicas con dos variables cualitativas (tablas de contingencia

de doble entrada)

25

3.3. Ejemplo C: Resúmenes descriptivos para variables cuantitativas e uno o varios

grupos usando Tablas dinámicas

27

4. La herramienta “Análisis de Datos” 30

4.1. Descripción de variables cuantitativas 32

4.1.1. Estadística descriptiva e Histograma 32

4.1.2. Coeficiente de correlación 37

4.2. Test de hipótesis para dos muestras 38

4.2.1. Organización previa de los datos: Autofiltros 38

4.2.2. Prueba F para varianzas de dos muestras 40

4.2.3. Prueba t para dos muestras 41

4.3. Test de hipótesis para más de dos muestras: Análisis de Varianza 42

5. El complemento “EzAnalyze” 48

5.1. Describir 51

5.1.1. Estadística descriptiva 51

5.1.2. Porcentajes 52

5.2. Gráficas 52

5.2.1. Histogramas 53

5.2.2. Desagregación 54

5.3. Avanzado 55

5.3.1. Correlación 55

5.3.2. Pruebas T 56


3 INTRODUCCIÓN

Este documento trata de ser un material de apoyo a las clases que desarrollan el primer bloque de contenidos de la asignatura: Análisis de datos con Excel. Al incluir una parte muy importante de los temas y procedimientos que se tratarán, así como de las actividades y casos prácticos que se llevarán a cabo, esperamos que la materia pueda ser seguida por los alumnos más fácilmente. Con todo, no es un material exhaustivo ni trata de recoger todos los aspectos susceptibles de ser tratados en un curso sobre el tema.

Sobre el bloque de contenidos en sí, cabe decir que la principal razón por la que puede ser muy útil saber cómo utilizar Excel para realizar análisis de datos es su amplia disponibilidad al estar integrado en Office, a diferencia de lo que ocurre con los paquetes estadísticos profesionales (tales como SPSS). De hecho, Excel es muy usado para la introducción y gestión previa de los datos en el análisis estadístico y para el tratamiento descriptivo de la información.

Complementariamente, el alumno debe tener noticia de que este programa presenta algunas claras limitaciones frente a los programas profesionales de análisis estadístico. Estas limitaciones incluyen el hecho de que una parte importante de métodos estadísticos de uso habitual no están disponibles o presentan información sólo parcial, así como la presencia de errores en algunas rutinas de cálculo, información equivocada en algunos menús de ayuda y resultados imprecisos en varios procedimientos. Debido a las limitaciones anteriores se han escritos macros y sobre todo Add-in (aplicaciones accesorias que se acoplan a otra para proporcionar herramientas extra a la primera) que mejoran las prestaciones del programa para el análisis de datos y que en su mayoría son fáciles de obtener.

Por otro lado, Excel tiene algunos puntos fuertes y utilidades muy prácticas para el uso estadístico, entre los que destaca especialmente el trabajo con Tablas Dinámicas, como veremos a lo largo del curso. A modo de conclusión valorativa, podríamos decir que si se trata de hacer un análisis estadístico básico, Excel puede ser una herramienta aconsejable y suficiente en una amplia variedad de casos y puede ser utilizada sin mayores reservas (análisis descriptivos, diferencias de medias, problemas sencillos de Análisis de Varianza...). A medida que las exigencias de análisis aumentan, las limitaciones del programa aumentan igualmente y lo adecuado es tratar los datos con programas específicos de análisis estadístico1. Por lo que se refiere al material, incluye un resumen de los aspectos más relevantes de las exposiciones y también las actividades prácticas que se irán realizando en la clase de modo simultáneo (identificadas con el símbolo �). Termina con la formulación de dos casos prácticos de carácter más global y realista para ser resueltos con Excel y que suponen el empleo integrado en un problema de investigación del conjunto de herramientas y procedimientos anteriores.

1 Para ampliar información sobre los puntos fuertes y débiles de Excel y sobre las valoraciones críticas del mismo para el trabajo estadístico pueden consultarse entre otras las siguientes fuentes: A) University of Reading Statistical Service Centre (2002). Using Excel for Statistics. Tips and Warnings. The University of Reading. Disponible en: www.rdg.ac.uk/ssc/dfid/booklets/xfs.pdf. B) Dennis R. Helsel (2002) Is Microsoft Excel an Adequate Statiscal Package?. Practicalstatistics.com. http://www.practicalstas.com/Pages/excelstas.html. C) Cryer, D.J. (2001). Problems with Using Microsoft Excel for Statistics. Paper presented at Joint Statistical Meetings 2001, Atlanta, GA. Disponible en: www.stat.uiowa.edu/~jcryer/JSMTalk2001.pdf.


4

I. PROCESOS BÁSICOS EN EXCEL 20032

1. ESTRUCTURA GENERAL DEL PROGRAMA Y DE LOS FICHEROS

El entorno de trabajo de Excel es el común a todas las aplicaciones Windows. De este modo, cuando arrancamos el programa, la ventana principal de Excel presenta las barras

y botones típicos de todas ellas: barra de título, barra de menús (desde la que se accede a los distintos submenús disponibles para la gestión de los datos), barras de herramientas e iconos, barra de estado, barras de desplazamiento horizontal y vertical, botón de maximizar, minimizar y cerrar, etc. Además, incluye los elementos propios de una hoja de cálculo, que describiremos brevemente a continuación:

- Cuadro de nombres y Barra de fórmulas. Estos dos elementos están situados inmediatamente

debajo de la barra de herramientas, tal y como se muestra en la figura 1. En el cuadro de nombres, situado a la izquierda, aparece la posición de la celda activa, denominada referencia y, a continuación, en la barra de fórmulas se muestra el contenido de esa celda.

Figura 1

- Ventana de documento: Hojas de cálculo y libros de trabajo

La forma de la ventana de documento de Excel es similar a la de todas las hojas de cálculo, formada por una estructura en cuadrícula de filas y columnas. Cada una de las columnas tiene una letra en la parte superior, que es el nombre o etiqueta de la columna. Por su parte, cada fila tiene un número en la parte izquierda que es el nombre o etiqueta de la fila.

2 Este epígrafe tiene por objetivo presentar de modo esquemático las características básicas del programa con el fin de que aquellos alumnos que no han manejado nunca antes Excel se familiaricen con sus modos básicos de operación. Se trata por tanto únicamente de proporcionar la información mínima necesaria para poder utilizar el programa con el fin de realizar análisis estadísticos básicos. En la bibliografía pueden encontrarse textos para una aproximación monográfica al uso de Excel.


5

La intersección de una fila con una columna forma un rectángulo llamado celda. Cada una de las celdas, por tanto, se denomina por la letra de su columna y el número de su fila (por ejemplo: A6, B9, etc.). Dentro de cada una de las celdas se pueden introducir datos de diverso tipo (texto, fechas, números...) y también fórmulas. La celda sobre la que está situado el cursor se denomina celda activa y es la que está preparada para la introducción de datos. La celda activa aparece siempre resaltada con un recuadro, y su referencia y contenido se muestran en el cuadro de nombres y en la barra de fórmulas respectivamente.

Cuando trabajamos con Excel creamos, damos nombre y guardamos libros de trabajo, que pueden estar compuestos por una o varias hojas de cálculo, cada una de ellas con la estructura básica que acabamos de describir.

Por lo tanto en Excel los documentos se denominan libros de trabajo. Un libro de trabajo es el conjunto de hojas de cálculo que se agrupan en un mismo fichero o archivo. En la parte inferior de la pantalla se muestran las tres pestañas correspondientes a las tres hojas de cálculo que por defecto asigna el programa a cada libro nuevo que abrimos. En la figura 2 se muestran los distintos elementos descritos. Además, podemos ver cómo la hoja activa, la que en ese momento está abierta, presenta la pestaña en fondo blanco, mientras que las restantes aparecen sombreadas en gris.

Figura 2


6

�� ACTIVIDAD 1: Un vistazo inicial a los menús de Excel

Recorre los distintos menús y las diferentes opciones que ofrece cada uno de ellos.

Observa que unas tienen asociados cuadros de diálogo (se muestran con puntos suspensivos), otras ofrecen un submenú con nuevas opciones (se muestra un pequeño triángulo a la derecha), algunas presentan a la izquierda un icono para indicar que tienen asociado ese símbolo en la barra de herramientas y otras tienen asociada una tecla o combinación de teclas que realizan esa misma función (que se indican a la derecha y son herencia de los antiguos modo de trabajo de los programas anteriores a Windows). La mayor parte de las opciones se presentan en color negro, lo cual significa que están disponibles, mientras que algunas se presentan en sombra para indicar que en ese momento están desactivadas.

�� ACTIVIDAD 2: Inspeccionando la estructura y el contenido de un fichero de Excel

Sobre el fichero introducción.xls realiza las siguientes actividades: 2.1. Celdas activas, referencias y contenido Usa las teclas de movimiento del cursor y observa que se puede activar cualquier celda. Con el ratón también se puede activar una celda, colocando el puntero del ratón sobre ella y pulsando el botón principal (generalmente es el izquierdo). Pruébalo con varias celdas.

Puedes ver que en el Cuadro de nombres aparece la posición de la celda activa denominada referencia de la celda y, a continuación, en la Barra de fórmulas se muestra el contenido de esa celda. Mueve el cursor por las celdas de la hoja y observa cómo varía el contenido del cuadro de nombres, la barra de fórmulas, etc. 2.2. Tipos de datos en una celda

Localiza en la hoja de cálculo inicial: a) Valores constantes (datos que se escribe directamente en una celda, y no cambian a menos que se seleccione la celda y se edite su valor). b) Fórmulas (una secuencia de valores, referencias de celdas, nombres, funciones u operadores que producen un nuevo valor a partir de valores existentes; una fórmula comienza siempre con el signo (=) y su valor puede cambiar cuando cambian otros valores de la hoja de cálculo). 2.3. Hojas en el libro de trabajo Sitúa el puntero del ratón sobre alguna de las pestañas de fondo gris y pulsa el botón. Repítelo con varias hojas. Selecciona de nuevo la hoja inicial.


7 2. CREACIÓN Y GESTIÓN DE UN LIBRO DE TRABAJO NUEVO

Una vez que nos hemos familiarizado con los elementos básicos de la ventana principal de Excel y con sus ficheros pasaremos a tratar algunos aspectos básicos de la creación y de la gestión de los mismos.

2.1. Introducción, edición y borrado de los datos de una celda

Para introducir datos en una celda debemos proceder como sigue:

1. Seleccionamos la celda haciendo clic con el ratón sobre la misma. Esta aparecerá resaltada y su referencia se mostrará en el cuadro de nombres.

2. Escribimos los datos que deseemos introducir. Apreciaremos que, de modo simultáneo a lo que ocurre en la celda activa, en la barra de fórmulas aparecerá lo que escribimos. Además aparecen ahora dos nuevos botones en esta barra, a la izquierda del signo igual, los botones Cancelar (aspa en rojo) e Introducir (signo de verificación en verde), tal y como muestra la figura 3.

3. Finalmente, podemos optar por cualquiera de los siguientes pasos para dejar fijo el valor introducido:

- Presionar Entrar

- Seleccionar con el ratón la siguiente celda donde deseamos introducir datos, o cualquier otra

- Hacer clic en el botón Introducir Datos de la barra de fórmulas

Figura 3

Para corregir el contenido de la celda activa mientras se están introduciendo datos en ella, basta pulsar la tecla Retroceso para borrar los caracteres situados a la izquierda del cursor y continuar introduciendo los datos correctos. Si se desea eliminar todos los valores introducidos en la celda haremos clic sobre el botón cancelar de la barra de fórmulas.

Para editar el contenido de celdas con datos ya introducidos, y en el caso de querer sustiuir todos los valores originales de una celda por otros distintos, basta seleccionarla y escribir los nuevos datos, que reemplazarán automáticamente a los anteriores. Si únicamente deseamos sustituirlos parcialmente, podemos hacer doble clic sobre la celda, de modo que aparecerá el cursor de texto con el que podemos movernos y corregir los valores deseados. También se puede activar la celda, hacer clic sobre la barra de fórmulas y realizar allí las correcciones.

Para borrar completamente el contenido de una celda o conjunto de celdas, en primer lugar se seleccionan las mismas y después se pulsa la tecla Supr o la tecla Retroceso.


8 Para copiar o mover celdas podemos recurrir a las opciones de Copiar, Cortar y Pegar o

bien utilizar el sistema de arrastrado.

2.2. Rangos en la hoja de cálculo

Muchas de las operaciones que se realizan en la hoja de cálculo requieren que previamente se haya seleccionado un rango, puesto que es el modo básico de indicar al programa la ubicación del conjunto de datos o valores con los que se quiere trabajar. Un rango es un conjunto de celdas conexas que forman un rectángulo y puede estar formado por celdas de una sola fila, de una sola columna o de varias filas y columnas contiguas.

La referencia de un rango se compone de las referencias de las celdas superior izquierda e inferior derecha del área rectangular, separándolas por el signo de dos puntos. Por ejemplo: B3:F3, C5:C18, E10:G17, ... El rango formado por la fila 5 se designa como 5:5 y la columna H forma el rango H:H.

Puesto que la selección de rangos constituye una operación básica previa al análisis y la gestión de datos con Excel, conviene tener presente el procedimiento adecuado para cada caso:

- Para seleccionar una celda: Hacer clic en ella

- Para seleccionar un rango o grupo de celdas contiguas: Hacer clic sobre la primera y arrastrar a continuación el ratón hasta cubrir el área deseada.

- Para seleccionar dos o más celdas o rangos no adyacentes: Seleccionar la primera celda o rango, y proceder a seleccionar el segundo y siguientes manteniendo la tecla Ctrl presionada.

- Para seleccionar una columna o fila entera: Hacer clic sobre su encabezado

- Para seleccionar todas las celdas de la hoja: Hacer clic en el botón seleccionar todo, en la esquina superior izquierda de la hoja de cálculo

2.3. Nombres de celdas o rangos En Excel es posible asignar un nombre a una celda o a un rango, para de esta forma: - Desplazarnos a esa celda o rango más fácilmente - Utilizar los nombres en una fórmula - Identificar mejor los rangos (por ejemplo: Total Actitud para denominar a toda una

columna) Para dar un nombre a una celda haremos lo siguiente:

1. Situamos el cursor en la celda a la que deseemos nombrar, por ejemplo, A1 y

hacemos clic en la casilla de los Nombres de celdas:


9 2. Escribimos el nombre deseado, por ejemplo, Primera y pulsamos Intro. La celda ha

recibido un nombre.

Si ahora situamos el cursor en cualquier otra celda, abrimos la lista de nombres y escogemos Primera, el cursor salta a la celda con ese nombre; en nuestro caso, a la celda A1. Asimismo, si seleccionamos un rango entero de celdas, podemos también asignarle un nombre y utilizarlo para desplazarnos a él.

2.4. Definición de la estructura del libro Como adelantamos, por defecto Excel asigna tres hojas a cada nuevo libro creado. Puede

resultar muy útil reunir en un único libro o archivo toda la información sobre un mismo tema, pero organizado en distintas hojas. Por ejemplo, podemos organizar un libro de modo que los datos originales ocupen una hoja denominada datos, y en las siguientes se dipongan los resultados de los análisis bajo el nombre descriptivo, t de Student, correlación, etc. A continuación se describe brevemente cómo cambiar de una hoja a otra, como asignarles un nombre, cómo añadir y eliminar hojas en un libro y cómo mover una hoja a otra posición del libro. - Para cambiar de una hoja a otra, basta hacer clic sobre la pestaña de la hoja que se desea tener

activa - Para sustituir el nombre genérico de las hojas que por defecto asigna el programa (Hoja,

seguido del número de orden que tienen en el libro), hacemos doble clic sobre la pestaña correspondiente y procedemos a escribir el nuevo nombre

- Si se han ocupado todas las hojas del libro y se desea añadir nuevas hojas:

a) Nos situamos con el ratón en la hoja inmediatamente anterior a la que se desea insertar b) En el menú Edición seleccionamos Insertar, Hoja de cálculo. Se insertará una nueva hoja

con el nombre genérico y el número siguiente de las ya creadas dentro del libro - Para eliminar una hoja:

a) Hacemos clic sobre la pestaña correspondiente a la hoja que se desea eliminar b) En el menú Edición se selecciona el comando Eliminar Hoja

- Para mover una hoja basta hacer clic sobre la pestaña de la hoja que se desea mover y arrastrarla para depositarla en la nueva posición manteniendo pulsado el botón izquierdo del ratón.

2.5. Guardar el libro de trabajo


10 Como es habitual en cualquier aplicación Windows, para guardar el archivo de trabajo se

procede como sigue: 1. Ejecutar el comando Guardar, disponible en el menú Archivo 2. Seleccionar la carpeta en la que se desea guardar el libro de trabajo 3. Escribir el nombre que se desea asignar al libro en el cuadro de texto Nombre del archivo 4. Hacer clic sobre el botón Aceptar.

Los ficheros básicos de Excel tiene la forma *.xls, y el programa asigna esta extensión automáticamente a un libro cuando es guardado.

�� ACTIVIDAD 3: Creación de un fichero de datos en Excel.

1. Introduce los datos siguientes en una hoja de Excel Caso ID Sexo Estado Edad Peso Altura

1 EXPED01 1 1 60 70 165 2 EXPED02 1 1 23 80 175 3 EXPED03 1 2 40 70 170 4 EXPED04 1 2 65 62 170 5 EXPED05 2 1 21 69 160 6 EXPED06 2 1 25 65 160 7 EXPED07 2 2 33 50 154 8 EXPED08 2 2 48 60 151

2. Guarda el archivo con el siguiente nombre: actividad3

�� ACTIVIDAD 4: Selección de rangos y asignación de nombre

En el fichero actividad3.xls: 1. Sitúa el puntero del ratón sobre el encabezado de una fila (su número) o de una columna (su letra) y pulsa el botón del ratón. Observa que al hacerlo la fila o columna respectiva queda resaltada. Prueba con diferentes filas y columnas. Realiza la misma operación anterior pero arrastrando, es decir, sin soltar el botón del ratón, después de apretarlo, y desplazándolo. Observa que de esa forma se pueden seleccionar varias filas o columnas. Para quitar la selección basta hacer clic sobre cualquier celda. 2. Realiza la operación de arrastre partiendo de una celda y observa que se resalta el rango que se desee, quedando en modo inverso, es decir, el fondo de las celdas resaltadas cambia de color, exceptuando la celda inicial que se destaca con un marco que resalta los bordes. Selecciona varios rangos simultáneamente utilizando la tecla Control. 3. Selecciona el rango donde están dispuestos los datos correspondientes a la variable Altura y dale como nombre Altura

�� ACTIVIDAD 5: Copiar y mover celdas En el fichero actividad3.xls


11 1. Selecciona la columna donde están dispuestos los datos de la variable Edad, incluido

el rótulo. 2. Accede a la opción Edición – Copiar o bien al botón Copiar

Observa que en la celda aparecen puntos parpadeantes. 3. Selecciona cualquier otra celda vacía de encabezado de columna. 4. Accede a Edición – Pegar o pulsa el botón Pegar

Observa que la zona parpadeante continúa activa. 5. Pulsa la tecla Esc. Repite la operación con la opción Cortar sobre la copia de la variable para trasladarla a otro sitio. Observa que la columna de origen ha desaparecido, es decir, se ha movido. Prueba este otro método para copiar: 1. Sitúa el puntero del ratón en el borde de la columna a copiar 2. Pulsa la tecla de CONTROL y sin soltarla, arrastra la celda a otra posición. Suelta

después. Con este método, si no pulsamos la tecla de control, la celda se movería. Asimismo, podemos copiar o mover un rango de celdas seleccionado con los mismos métodos.

3. IMPORTACIÓN DE DATOS DESDE OTRAS APLICACIONES.

Excel puede importar (y exportar) ficheros de formatos muy diversos. Es de especial interés considerar que:

a) Los ficheros de datos de Excel (*.xls) pueden importarse muy fácilmente en SPSS, de modo que podemos grabar datos en Excel que podemos posteriormente trasformar en ficheros del sistema SPSS (*.sav) para analizarlos con este programa

b) En SPSS pueden grabarse los ficheros de datos directamente en formato Excel, de modo que si grabamos originalmente los datos en SPSS también podemos pasarlos a Excel para analizarlos con este programa.

4. CONCEPTOS CLAVE TRATADOS

� Hoja: Se denomina así a la zona donde estamos trabajando. Cada hoja tiene un nombre identificativo que podemos cambiar. Los nombres de las hojas se pueden observar en la zona inferior de la pantalla.

� Celda: Cuadro individual que forma parte de la hoja. En las celdas introduciremos los datos.

� Columna: Se nombran de la A a la Z y están dispuestas en vertical. Después de la columna Z, nos encontramos con la columna AA,AB,AC... y así hasta la AZ. Seguidamente, comenzaría la BA, BB.. y así hasta la última columna que es la IV. Para el trabajo estadístico, en las columnas habitualmente se disponen las distintas variables que serán objeto de análisis.


12 � Fila: Dispuestas en horizontal, se numeran desde la 1 hasta la 16.384 que es la última.

Para el trabajo estadístico, en las filas habitualmente se disponen los sujetos de los que hemos obtenido medidas en las distints variables.

� Libro de trabajo: Conjunto de hojas. Un libro puede tener varias hojas. Al grabarlo, se crea un fichero con la extensión XLS con todas las hojas que tuviese el libro.

� Rango: Grupo de celdas adyacentes, es decir, que se tocan. Un rango de celdas por ejemplo que va desde la A1 hasta la A5 se reflejaría con el siguiente nombre: A1:A5. El nombre de un rango siempre hará referencia a la primera y a la última celda seleccionadas.


13

II. ANÁLISIS ESTADÍSTICO DE DATOS CON EXCEL

1. DISEÑO DE LA BASE DE DATOS

Para la introducción y gestión de datos en Excel es conveniente adoptar la estructura común que las bases de datos adoptan en los paquetes estadísticos más habituales. Como ya comentamos en la introducción, aunque Excel es una herramienta útil para el análisis estadístico básico, puesto que presenta algunas limitaciones conviene asegurar que los datos pueden ser exportados fácilmente, si lo deseamos, a otras aplicaciones para completar los análisis.

En el diseño de la base de datos conviene tener presente las siguientes indicaciones:

1. Todos los datos deben estar grabados en una sola Hoja y en un solo Libro de Excel

2. El nombre de las variables los ubicaremos en la primera fila de la Hoja. Conviene también que el nombre no exceda de 8 caracteres y que se inicie con una letra (no con un número).

3. Evitaremos introducir otros datos de texto, como títulos o encabezados, en la Hoja.

4. Si disponemos de varios grupos, todos sus datos han de incluirse en la misma Hoja, utilizando una variable que indica la pertenencia a uno u otro grupo (ver figura 4).

5. Conviene grabar todos los datos en formato numérico, y emplear los códigos 1 y 0 si se dispone sólo de dos grupos.

6. Para los valores perdidos conviene dejar la celda en blanco, sin grabar ningún valor.

Como has podido observar, todos los ficheros con los que hemos trabajado hasta ahora presentaban esta estructura.

Caso Genero Test 1 0 12 2 1 14 3 0 23 4 1 17

Estructura estándar (recomendada)

Figura 4

Datos mujeres Caso Test 1 12 3 23 Datos varones Caso Test 2 14 4 17 Estructura de datos no exportable

(a evitar)


14

�� ACTIVIDAD 6: Codificación de instrumentos de recogida de datos para su grabación y diseño de base de datos

1. En el Anexo se reproduce un cuestionario para la recogida inicial de datos de la asignatura, así como un ejemplo de la estructura que puede presentar un libro de códigos. Prepara una libro de códigos para el cuestionario y en un fichero de Excel dispón la estructura de la base de datos. Graba el fichero con el nombre codificación1.xls.

2. Imagina que se hubiera incluido también la siguiente cuestión:

Señala los dos motivos fundamentales por los que has elegido esta asignatura optativa: � Me venía bien para organizar el horario � Me interesa el Análisis de Datos � En general me interesa manejarme bien con los ordenadores � Me parece útil para otras asignaturas de la carrera � Me convenció algún compañero/a

Incluye la codificación de esta pregunta en el libro de códigos y en el fichero cofidicación.xls

2. ANÁLISIS DE DATOS CON FÓRMULAS Y FUNCIONES Para analizar datos en una hoja de cálculo podemos aplicar una fórmula que realice operaciones

tales como: suma, multiplicación, división, etc. sobre nuestros datos. Para ello, cuando la barra de fórmulas está activa, o cuando se edite una celda: - Podemos directamente escribir en ella una fórmula utilizando operadores aritméticos (suma,

resta, multiplicación, división...) o de comparación (mayor que, menor que..). - Podemos insertar una función, que es una fórmula especial que ya está escrita, por lo que el uso

de funciones simplifica y acorta el trabajo con fórmulas. Entre las funciones incluidas en Excel hay un capítulo especial dedicado a las Funciones Estadísticas del que trataremos posteriormente.

Por lo tanto hay varias formas de introducir una fórmula en una celda, si bien debemos tener en cuenta que las fórmulas y funciones siempre comienzan con el signo igual (=).

1. La primera de ellas es teclearla directamente. Así, si deseamos hallar el total de las celdas B5 a B7 tendremos que escribir =B5+B6+B7.

2. Otro procedimiento, más práctico y rápido, sería insertar una función, en este caso =SUMA(B5:B7). Para insertar una función además podemos utilizar los cuadros de diálogo del comando Pegar Función que facilitan una guía paso a paso en Excel, lo que nos ayuda a seleccionar la misma entre las disponibles, marcar los argumentos o datos sobre los que queremos trabajar e insertarla en la barra de fórmula correctamente.


15 2.1. Uso de fórmulas

Tomamos como ejemplo un conjunto de datos ficticio. Supuestamente corresponden a los resultados obtenidos por cuatro sujetos en una prueba de selección de 60 pregunta objetivas de opción múltiple que se dividen en 3 bloques de contenido, en cada uno de los cuales se obtiene una puntuación parcial. Para obtener la puntuación total de cada uno de los sujetos se podría proceder como sigue:

1. En la celda correspondiente al total del sujeto 1 escribimos la siguiente expresión:

=B2+C2+D2 y pulsamos Introducir

2. Una vez que en la celda aparece el valor de la operación, copiamos la fórmula en las celdas correspondientes al resto de los sujetos pinchando con el ratón el cuadrado de su esquina inferior derecha y arrastrando mientras mantenemos el botón izquierdo presionado.

3. Al soltar el botón aparecerá realizada la operación suma correspondiente a la celda del total de cada sujeto (figura 5)

Figura 5

De modo similar trabajamos con el resto de fórmulas que permiten operaciones en Excel. Los operadores aritméticos y de comparación disponibles en Excel se presentan en la tabla 1.


16 Operador aritmético

Operación Operador de comparación

Operación

+ Suma > Mayor que - Resta < Menor que * Multiplicación = Igual a / División < > No igual a

<= Menor o igual que ^

Exponenciación >= Mayor o igual que

Tabla 1. Operadores lógicos y de comparación en Excel

�� ACTIVIDAD 7: Insertar y copiar fórmula

A partir de los datos del fichero actividad3.xls, calcula para todos los sujetos su altura en centímetros, insertando y copiando la fórmula adecuada. El resultado debe ser una columna a continuación de la última con el rótulo “cm” donde aparezca el valor correspondiente para cada sujeto.

2.2. Uso de funciones

Como ya habíamos comentado, una función puede considerarse como un acceso directo que se emplea para el trabajo con fórmulas. Excel tiene más de 200 funciones incorporadas, agrupadas en 10 categorías básicas que permiten una localización fácil y rápida (de ingeniería, de texto, estadísticas, financieras, matemáticas y trigonométricas, etc.). En el ejemplo visto en el epígrafe anterior, en vez de escribir =B2+C2+D2 (o quizá una fórmula mucho más larga, donde tenemos que sumar un número elevado de términos) podemos escribir =SUMA(B2:D2) y obtendremos idénticos resultados.

Con el fin de facilitar el trabajo con funciones, de modo que no sea necesario recordar el

formato de una función particular, el comando Pegar función permite crear la misma paso a paso, señala la información obligatoria y opcional que se debe proporcionar e indica como escribirla en el orden correcto. Para activar el comando bien se hace clic en el botón correspondiente de la barra de herramientas (inmediatamente a derecha del botón autosuma y con el icono gráfico de una f con una x como subíndice), bien se selecciona en el menú Insertar la opción Función. El procedimiento comprende dos pantallas: en la primera se elige la función que se desea emplear; en la segunda se procede a introducir los argumentos de la función elegida.

A continuación ilustraremos el procedimiento para insertar funciones tomando como ejemplo el cálculo del promedio (media aritmética) de la puntuación total obtenida por los cuatro sujetos del ejemplo del epígrafe anterior:


17 1. Situamos el cursor en la celda donde ha de aparecer la función. Tras hacer clic en el botón

Insertar función, en la ventana de diálogo Pegar Función seleccionamos en el área Categoría de la función, Estadísticas. En el cuadro Nombre de la Función, entre todas las disponibles seleccionamos Promedio. Podemos observar que en la parte inferior de la ventana se presenta la información básica sobre la función Promedio: su sintaxis, y una breve descripción de la misma (figura 6). Esta información podemos ampliarla en la opción de Ayuda.

Figura 6

2. Una vez seleccionada la opción pulsaremos Aceptar, lo que dará paso a la siguiente ventana o cuadro de diálogo (figura 7).


18

Figura 7

En este momento debemos introducir los argumentos. Se debe seleccionar el cuadro correspondiente al argumento que se quiere introducir (Número 1 en nuestro caso) haciendo clic y a continuación, introducimos tecleando directamente el mismo o minimizamos el cuadro en el botón situado a la izquierda para seleccionarlo con el ratón sobre la hoja de cálculo. En nuestro caso, para introducir el rango E2:E5, donde se situán las puntuaciones totales de cada sujeto, procedemos como sigue:

3. Hacemos clic sobre el botón que minimiza el cuadro de diálogo (en color rojo y en el extremo derecho del cuadro del argumento Número 1)

4. Marcamos con el ratón el rango de interés y volvemos a hacer clic sobre el botón para regresar al cuadro de diálogo

5. Introducido ya el argumento, pulsamos Aceptar. El resultado de la fórmula aparece ya incorporado a la celda correspondiente (Ver figura 8 para el seguimiento de esta secuencia).

De modo análogo al mostrado pueden insertarse el resto de las funciones disponibles en Excel.

�� ACTIVIDAD 8: Insertar y pegar una función

1. A partir de los datos del fichero actividad3.xls calcula la media y la desviación típica de las variables edad, peso y altura en esta muestra.

2. Inspecciona el menú completo de Funciones Estadísticas para hacerte idea del tipo de herramientas disponibles en este menú de Excel.


19

Figura 8


20

2.2.1. Algunas observaciones sobre funciones Estadísticas en Excel

1. Aunque el programa incorpora un número importante de funciones estadísticas y un buen número de ellas pueden ser de gran utilidad para nosotros, muchos de los procedimientos de estadística descriptiva e inferencial incorporados como funciones son mucho más fáciles de obtener usando la Macro automática correspondiente en la Herramienta Análisis de Datos que se explica en un epígrafe posterior.

2. Algunos de los procedimientos, no obstante, no se encuentran disponibles en la Herramienta Análisis de Datos y su obtención es bastante laboriosa. Es el caso de la prueba Chi cuadrado de independencia entre dos variables cualitativas. Esta función requiere introducir, además de la tabla de contingencia original (frecuencias observadas) que debe ser construida por el usuario (utilizando las opciones de Tablas Dinámicas que se explican también posteriormente) la tabla con las frecuencias esperadas o teóricas, que deben ser calculadas íntegramente por el usuario.

3. Frecuentemente usaremos las funciones estadísticas para obtener resúmenes descriptivos básicos y rápidos sobre la misma hoja de datos, al modo de la configuración de datos del archivo introducción.xls que ya se inspeccionó. Es importante en este sentido saber qué desviación típica calcula Excel en las distintas funciones relacionadas con este estadístico:

� DESVESTP: Desviación típica “sesgada”, esto es, estimada dividiendo por N (la que habitualmente obtenemos para caracterizar descriptivamente a una muestra)

� VARP: Varianza “sesgada”. Es el cuadrado de la función DESVESTP.

� DESVEST: Desviación típica “insesgada”, esto es, estimada dividiendo por N-1

� VARP: Varianza “insesgada”. Es el cuadrado de la función DESVEST.

3. ANÁLISIS DE DATOS CON TABLAS DINÁMICAS

El uso de la opción de Tablas Dinámicas que incorpora Excel es muy útil para la organización y el análsis de información cuantitativa. Este tipo de tablas permite resumir y cruzar datos en tablas de una, dos y tres dimensiones, es decir, utilizando una, dos o tres variables, ofreciendo una variedad considerable de estadísticos (frecuencias, porcentajes, media y desviación típica) y gran versatilidad, puesto que son interactivas y permiten ser modificadas con gran facilidad.

El proceso general para crear una Tabla dinámica es el siguiente:

1. Hacer clic en cualquier celda de la base de datos

2. En el menú Datos, seleccionar Informe de tablas y gráficos dinámicos

3. Seguir las instrucciones del Asistente para tablas y gráficos dinámicos (figura 9)


21

Figura 9

A continuación desarrollamos tres ejemplos concretos de creación de tablas dinámicas a partir de una base de datos sobre precios de matricula de instituciones de educación superior públicas y privadas de los Estados Unidos. Tenemos un listado de 46 universidades, y para cada una de ellas hemos grabado el coste, el tipo de centro (público o privado), el contexto en el que se ubica (urbano, suburbano, rural) y su perfil o tipo de titulaciones que ofrece.

3.1. Ejemplo A. Tabla dinámica con una sola variable: Distribuciones de frecuencias o Porcentajes

Queremos obtener la distribución de los centros universitarios atendiendo a su carácter público o privado. Para ellos, y tras indicar en el paso 1 que nuestro datos están en una base de datos de Excel, se nos presentará la pantalla reproducida en la figura 10.

Figura 10

Hemos de marcar la base de datos completa donde están nuestros datos, incluidos lo rótulos o encabezados de columnas, minimizando para ello el cuadro de diálogo en el botón rojo situado a la derecha de la ventana Rango(figura 11).


22

Figura 11

Una vez que hemos proporcionado el rango de entrada, indicamos que en nuestro caso queremos que la tabla se ubique en una Hoja de Cálculo nueva y distinta a donde están los Datos. A continuación pulsaremos el botón Diseño para especificar el tipo y características de nuestra tabla (figura 12)

Figura 12

En nuestro caso sólo está implicada una variable, Tipo (de centro). Por ello


23 arrastraremos con el ratón el botón de campo correspondiente a esta variable tanto a la posición de FILA como a la de DATOS (figura 13).

Figura 13

Por defecto el estadístico de resumen es Contar (es decir, frecuencia). Haciendo doble clic sobre este botón podemos cambiarlo si así lo deseamos (figura 14).

Figura 14

En el botón Opciones, además, podemos elegir cómo queremos que se muestre el dato. En nuestro caso solicitaremos que nos muestre porcentajes de cada categoría (público/privado) con respecto al total de centros (figura 15).


24

Figura 15

El resultado que ofrece el programa se muestra en la figura 16. Además, vemos que al generar la tabla dinámica el programa presenta la Barra de Herramientas de Tabla Dinámica. Actuando sobre los botones de la misma podemos modificar su diseño y también obtener automáticamente un gráfico a partir de los datos de la misma.

Figura 16

De especial interés son los botones que dan acceso al Asistente para Gráficos y al Asistente


25 para Tablas Dinámicas (señalados en la figura 16). Pulsando sobre el primero Excel genera automáticamente un gráfico en una hoja nueva a partir de los datos existentes en la tabla, gráfico que por defecto será de barras y que podemos modificar como deseemos. En el Asesor para Tablas Dinámicas podemos redefinir y cambiar la tabla original.

�� ACTIVIDAD 9. Distribución de frecuencias y representación gráfica para una variable cualitativa usando Informes de Tablas y Gráficos Dinámicos

Los datos del ejemplo anterior están grabados en el fichero tablas.xls.

1. Replica los resultados que se han mostrado en el ejemplo, añadiendo como resultado un gráfico de sectores que represente la distribución de los centros de la muestra atendiendo a su carácter público o privado. El resultado final del gráfico debe ser el que se muestra en la figura 17

2. Actúa sobre el botón de campo Tipo de la Tabla que has generado. Prueba a marcar y demarcar alguna categoría y observa cómo se modifica la tabla.

Total Centros

Privado67%

Público33%

Figura 17

3.2. Ejemplo B. Tablas dinámicas con dos variables cualitativas (tablas de contingencia de doble entrada)

Para construir una tabla de contingencia de doble entrada (considerando dos variables conjuntamente) realizamos un proceso similar al descrito anteriormente. De este modo, si quisiéramos obtener la composición de la muestra de centros atendiendo simultáneamente a su carácter público o privado y a su perfil (titulaciones impartidas), en el diseño deberíamos especificarlo como muestra la figura 18.


26

Figura 18

El resultado obtenido sería en este caso el mostrado en la figura 19. Si deseamos que los datos ofrecidos se presenten en la forma de porcentajes, en la Barra de Herramientas de Tablas dinámicas podemos cambiar la especificación inicial y solicitar datos en forma de porcentajes de filas, columnas o total.

Figura 19


27

�� ACTIVIDAD 10. Diseño de tablas dinámicas de contingencia (doble entrada) para variables cualitativas usando Tablas dinámicas

Con los datos del fichero tablas.xls.

1. Replica los resultados que se han mostrado en el ejemplo

2. Actúa sobre los botones de campo Tipo y Perfil de la Tabla que has generado. Prueba a marcar y demarcar alguna categoría y observa cómo se modifica la tabla.

3.3. Ejemplo C. Resúmenes descriptivos para variables cuantitativas en uno o varios grupos usando Tablas dinámicas

Suponemos ahora que deseamos obtener un informe descriptivo básico de la variable Coste en el conjunto de la muestra.

Para ello podemos construir una Tabla Dinámica en la que disponemos en el área de DATOS de la ventana Diseño la variable Coste. Por defecto el programa incluirá como estadístico la Suma. Haciendo doble clic sobre el botón de campo podemos modificarlo, seleccionando otros diversos (figura 20) como el Promedio (media aritmética).

Figura 20

Como habitualmente deseamos más de un estadístico de resumen, conducimos al área de datos la variable Coste tantas veces como estadísticos queramos obtener, repitiendo las operaciones anteriores para seleccionar la desviación típica, el máximo y el mínimo (figura 21).


28

Figura 21

El resultado que obtendremos es el reproducido en la figura 22.

Figura 22

Para obtener un informe descriptivo comparado del Coste de la enseñanza en las universidades públicas y privadas, bastaría trasladar el Botón de Campo Tipo a las COLUMNAS, como muestra la figura 23.


29

Figura 23

El resultado obtenido en este supuesto es el mostrado en la figura 24.

Figura 24

�� ACTIVIDAD 10. Resúmenes descriptivos usando Informes de Tablas Dinámicos

Replica las dos tablas dinámicas que se han mostrado en el ejemplo con los datos del fichero tablas.xls


30 4. LA HERRAMIENTA “ANÁLISIS DE DATOS”

Microsoft Excel incorpora un Complemento o Macro automática que incluye un conjunto de herramientas para el análisis de datos (denominado Herramientas para análisis), lo que permite ahorrar pasos en el desarrollo de análisis estadísticos. Cuando utilizamos una de estas herramientas, proporcionamos los datos y parámetros para cada análisis; la herramienta utilizará las funciones de macros estadísticas correspondientes y, a continuación, mostrará los resultados en una tabla de resultados. Algunas herramientas generan gráficos además de tablas de resultados.

La secuencia general de uso de las Herramientas para análisis es el siguiente:

1. En el menú Herramientas, seleccionar Análisis de datos (figura 25).

Figura 25

2. En el cuadro Herramientas para análisis, hacer clic en la herramienta que deseemos utilizar

(figura 26)

3. Introducir el rango de entrada, el rango de salida y, a continuación, seleccionar los parámetros necesarios y las opciones deseadas. Estas especificaciones variarán de un análisis a otro y las cometaremos específicamente para cada caso.


31

Figura 26

En ocasiones la instalación básica no incorpora este Complemento. En este caso, debemos

proceder como sigue:

1. En el menú Herramientas, seleccionar Complementos.

2. Activar la casilla de verificación Herramientas para análisis como muestra la figura 273

figura 27

Nos centraremos aquí en algunas de las opciones más habituales, que se indican a continuación agrupadas conforme a su función:

1. Descripción de variables cuantitativas

• Estadística Descriptiva e Histograma

• Coeficiente de Correlación

2. Test de hipótesis para dos muestras: igualdad de varianzas y medias

3 Si “Herramientas para análisis” no aparece en la lista del cuadro de diálogo Complementos, debemos hacer clic en Examinar y buscar la unidad, la carpeta y el nombre de archivo del complemento o macro automática de las Herramientas para análisis que normalmente estará ubicado en la carpeta Microsoft Office\Office\Library\Análisis; o bien, ejecutar el programa de instalación si no estuviera instalado.


32 • Prueba F para varianzas de dos muestras

• Prueba t para dos muestras

o suponiendo varianzas iguales

o suponiendo varianzas desiguales

o emparejadas

3. Test de hipótesis para más de dos muestras: Análisis de varianza.

• de un factor (muestras independientes)

• de dos factores con una muestra por grupo (muestras relacionadas)

• de dos factores con más de una muestra por grupo (diseño factorial)

�� ACTIVIDADES DEL BLOQUE “ANÁLISIS DE DATOS”

Cada uno de los procedimientos que se incluyen en este apartado se describe a continuación con un ejemplo. Los datos correspondientes a cada uno de ellos están grabados en el fichero Excel que se indica en el epígrafe. Los alumnos deben replicar el proceso descrito en cada caso.

4.1. Descripción de variables cuantitativas

4.1.1. Estadística Descriptiva e Histograma (�� descriptiva.xls)

Estas dos opciones del menú Análisis de datos son útiles y adecuadas para realizar informes descriptivos completos de una variable cuantitativa. La opción Estadística Descriptiva facilita de modo automático un resumen numérico completo de la variable (estadísticos de tendencia central y dispersión) mientras que la opción Histograma permite elaborar este tipo de gráfico a partir de una distribución de frecuencias agrupadas. En este último caso, sin embargo, frecuentemente tendremos que construirla parcialmente nosotros mismos. Llevaremos a cabo a continuación ambos procesos utilizando la variable Medida.

El primer paso es, lógicamente, seleccionar en el menú Herramientas la opción Análisis de Datos, y dentro de ella, Estadística Descriptiva. Se abrirá el cuadro de diálogo correspondiente, donde procederemos a especificar los distintos parámetros tal y como muestra la figura 28.


33

Figura 28

El rango de entrada lo marcaremos minimizando esta ventana y seleccionando toda la columna que incluye los datos de la variable Medida, incluido el rótulo. Como opción de salida indicaremos en este caso que los resultados se “coloquen” en una hoja del libro distinta a la de Datos y que lleve por nombre “Descriptivo Medida”. Una vez seleccionados los elementos básicos del informe (los que habitualmente son de interés), procedemos a Aceptar. Los resultados ofrecidos por el programa a partir de las indicaciones anteriores son los reproducidos en la figura 29.

Figura 29


34 Para completar el informe deseamos obtener una representación gráfica de la distribución de

la variable. Para ello seleccionamos en el menú Datos de nuevo Análisis de datos, y a continuación Histograma. Aparecerá el cuadro de diálogo que se muestra en la figura 30. Con esta opción podemos obtener:

a) Una distribución de frecuencias agrupada en intervalos, denominados clases en Excel b) Un gráfico asociado a la distribución de frecuencias (opción Crear Gráfico)

Figura 30

Si nos limitamos a indicar el rango de entrada (común al de la operación anterior) y el de salida (quizá lo más práctico es disponer el gráfico junto con el informe descriptivo que elaboramos anteriormente), el programa fija automáticamente las clases (límites de los intervalos de la distribución de frecuencias). Este resultado automático habitualmente habrá que desecharle. Veamos por qué. En nuestro caso el resultado ofrecido por el programa es el que muestra la tabla 2.

Clase Frecuencia 39 1

60,3333333 9 81,6666667 20

103 10 124,333333 2 145,666667 1

y mayor... 2

Tabla 2

Vemos que los límites de los intervalos son muy poco convencionales y que la distribución es poco matizada precisamente en el centro. Por ello suele ser más útil introducir en el rango de clases los límites fijados por el usuario. En nuestro ejemplo, y puesto que el Rango de la variable es igual a 128 (ver figura 29) y las medidas de tendencia central se sitúan entre 64 y 77 (pocos casos presumiblemente por encima de 100-110), podemos elaborar como clases para nuestro ejemplo las de la tabla 3 , copiándolas en la hoja Descriptivo Medida junto al informe.

Clases


35 39 49 59 69 79 89 99 109 119 129

Tabla 3

En el cuadro de diálogo Histograma, entonces, introducimos el rango en el que hemos dispuesto las clases y pulsamos Aceptar. El resultado de la distribución de frecuencias y el correspondiente Histograma, que aparece como un objeto incrustado en la propia hoja de Cálculo se muestra en la figura 31.

Histograma

01020

39 59 79 99 119

ym

ayo

Clases

Fre

cuen

cia

Frecuencia

Figura 31

El gráfico originalmente ofrecido por defecto en la opción Histograma habitualmente debe ser formateado y modificado para que presente un aspecto adecuado.

Como todos los gráficos en Excel, seleccionando los distintos elementos del gráfico con el botón izquierdo del ratón (títulos, leyenda, serie de datos, ejes, etc.) podemos modificarle. Así, por ejemplo, realmente un histograma es una representación estadística de una variable cuantitativa continua y por lo mismo presenta convencionalmente las barras contiguas. Para modificar el gráfico actual con este fin, seleccionamos con el ratón las barras y haciendo clic en el botón derecho del ratón aparecerá un menú contextual donde podemos seleccionar Formato de la serie de datos (cuadro de diálogo en figura 32).

Clases Frecuencia 39 1 49 2 59 6 69 8 79 13 89 6 99 4 109 1 119 0 129 1

y mayor... 3


36

Figura 32

Si en la opción correspondiente indicamos un Ancho de rango igual a cero, el resultado es el ensanche automático de cada barra. La figura 33 muestra un posible aspecto final del Histograma para la distribución de la variable Medida.

Medida

0

2

4

6

8

10

12

14

39 49 59 69 79 89 99 109

119

129

y may

or...

Fre

cuen

cia

Figura 33

4.1.2. Coeficiente de correlación (��Correlaciones y dos muestras.xls)


37 Esta opción es útil y adecuada para obtener coeficientes de correlación de Pearson entre dos

o más variables cuantitativas. Para ello seleccionaremos en Análisis de Datos la opción correspondiente a Coeficiente de correlación y proporcionaremos los parámetros solicitados en el cuadro de diálogo. En nuestro ejemplo obtendremos la matriz de correlaciones del conjunto de variables cuantitativas de la hoja de Datos (v1 a v10). Es suficiente indicar el rango de entrada, la opción y la opción de salida (en nuestro caso una nueva hoja con nombre “correlaciones”, así como la opción rótulos puesto que los incluimos en la selección del rango de entrada (Figura 34).

Una vez que hemos pulsado Aceptar, Excel dispone la matriz en la hoja indicada. Una visualización adecuada es la mostrada en la tabla 4. Pero como habitualmente el programa ofrece un exceso de posiciones decimales, este parámetro puede cambiarse seleccionando en el menú Formato, la opción Celdas e indicar un número “más razonable” de decimales para mostrar los resultados (ver figura 35).

Figura 34

v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v1 1,000 v2 0,008 1,000 v3 0,169 0,201 1,000 v4 0,237 0,233 0,745 1,000 v5 0,507 0,132 0,155 0,246 1,000 v6 0,277 0,092 0,299 0,504 0,348 1,000 v7 0,260 0,356 0,270 0,446 0,350 0,460 1,000 v8 0,069 0,179 0,116 0,234 0,326 0,389 0,289 1,000 v9 0,337 -0,005 0,295 0,320 0,243 0,445 0,260 0,354 1,000 v10 0,340 0,238 0,377 0,376 0,210 0,275 0,233 0,323 0,400 1,000

Tabla 4

Un aspecto importante que se debe tener en cuenta es que para obtener coeficientes de correlación los rangos de las dos o más variables con las que se quiere calcular deben ser adyacentes, por lo que puede ser necesario cortar y pegar columnas si la base de datos lo requiere. Una limitación mayor que esta, no obstante, es el hecho de que el programa no proporcione la significación asociada al coeficiente de correlación, lo que exige consultar tablas de distribución de r de Pearson para determinar la misma.


38

Figura 35

4.2. Test de hipótesis para dos muestras (��Correlaciones y dos muestras.xls)

4.2.1. Organización previa de los datos: Autofiltros

Para poder llevar a cabo contrastes de hipótesis que suponen comparación de grupos los datos en Excel deben estar organizados de modo similar a como los disponemos para el cálculo manual. Es decir, si como es habitual la base de datos está organizada en forma de lista o de matriz variables/sujetos, primero debemos preparar los datos y luego solicitar los análisis de interés (prueba F, t, Análisis de Varianza).

Para organizar los datos de modo más o menos automático y evitar errores, se pueden usar varios procedimientos. Exponemos aquí el correspondiente al uso de la función Autofiltro, que permite seleccionar fácilmente subconjuntos de datos. Una vez seleccionados los datos podemos cortar y pegar la información relativa a cada subgrupo y organizar convenientemente los rangos de entrada. Los pasos a seguir para organizar, a modo de ejemplo, los datos de varones y mujeres para ser comparados en la variable V1 serían los siguientes:

1. En el menú Datos seleccionamos Filtro y entre las distintas opciones disponibles la correspondiente a Autofiltro. Para realizar esta operación debe estar seleccionada cualquier celda de la base de datos (figura 36).


39

Figura 36

2. Al habilitar la opción Autofiltro en el encabezado de cada columna (variable) aparecerá un botón de flecha que da acceso a un menú que permite seleccionar sólo los casos que presentan un valor determinado de esa variable (figura 37). En el ejemplo, si seleccionamos el valor 0 sólo se mostrarán los datos de la base para los varones.

Figura 37

3. Una vez que hemos seleccionado los datos de los varones, copiamos y pegamos los resultados obtenidos en V1 en una hoja nueva. A continuación repetimos la operación para las mujeres (valor 1 en la variable sexo). Cuando cambiemos el título de los rótulos el resultado debe ser el mostrado en la figura 38.


40

Figura 38

Una vez que tenemos los datos organizados conforme a esta estructura o una similar podemos pasar a realizar las distintas pruebas de contreste de hipótesis en Excel, incluidas las referidas al Análisis de Varianza.

4.2.2. Prueba F para varianzas de dos muestras

Para comprobar el supuesto de homocedasticidad entre las varianzas de dos muestras, procedemos como sigue (el ejemplo se desarrolla para las submuestras de varones y mujeres en V1).

1. En el menú Herramientas seleccionamos Análisis de Datos y a continuación Prueba F para varianza de dos muestras. En la ventana de diálogo correspondiente indicamos los rangos para la variable 1 (valores en V1 de los varones) y para la variable 2 (valores en V1 para las mujeres). Tras indicar dónde deseamos que se incorporen los resultados (en nuestro caso en una hoja nueva) pulsamos aceptar (figura 39).


41

Figura 39

Los resultados obtenidos por el programa se muestran en la tabla 5.

Prueba F para varianzas de dos muestras

varón mujer Media 4,6 4,3 Varianza 0,989473684 0,747368421 Observaciones 20 20 Grados de libertad 19 19 F 1,323943662 P(F<=f) una cola 0,273379879 Valor crítico para F (una cola) 2,16824958

Tabla 5

4.2.3. Prueba t para dos muestras

Disponemos de tres dos opciones distintas para realizar una prueba t que incluya muestras independientes, cuya selección dependerá de que consideremos las varianzas de las muestras iguales o no estadísticamente. Además, si contamos con muestras relacionadas deberemos usar la opción correspondiente a muestras emparejadas.

En los tres casos el procedimiento es básicamente idéntico y el cuadro de diálogo presenta los mismos elementos. Ejemplificamos a continuación el análisis necesario para comparar las medias de varones y mujeres en la V1 asumiendo varianzas iguales.

1. Seleccionamos en el menú Análisis de Datos la opción adecuada

2. En el cuadro de diálogo indicamos en el apartado de rango de entrada la disposición de los datos para los varones (variable 1) y para las mujeres (variable 2). Además como diferencia hipotética entre las medias introducimos “0”. Dispondremos los datos en una hoja nueva (figura 40)


42

Figura 40

3. Los resultados ofrecidos por el programa se reproducen en la tabla 6.

Prueba t para dos muestras suponiendo varianzas iguales

varón mujer Media 4,6 4,3 Varianza 0,98947368 0,74736842 Observaciones 20 20 Varianza agrupada 0,86842105 Diferencia hipotética de las medias 0 Grados de libertad 38 Estadístico t 1,01801947 P(T<=t) una cola 0,15755336 Valor crítico de t (una cola) 1,68595307 P(T<=t) dos colas 0,31510673 Valor crítico de t (dos colas) 2,02439423

Tabla 6

4.3. Test de hipótesis para más de dos muestras: Análisis de Varianza (�� ANVA.xls)

Las opciones disponibles en el programa son:

a) Análisis de varianza de un factor (muestras independientes)

b) Análisis de varianza de dos factores con una muestra por grupo (muestras relacionadas)

c) Análisis de varianza de dos factores con más de una muestra por grupo (diseño factorial)

A continuación se ejemplifica el procedimiento y los resultados ofrecidos en cada caso. 4.3.1. Análisis de varianza de un factor (muestras independientes)


43

1. Se disponen los datos en la forma habitual para el cálculo manual (también se pueden disponer en filas) (tabla 7)

G1 G2 G3 12 18 6 18 17 4 16 16 14 8 18 4 6 12 6 12 17 12 10 10 14

Tabla 7

2. Seleccionada la opción se indican los rangos de entrada (si se incluye una primera fila de etiquetas se debe indicar rótulos en la primera fila) y de salida. También es posible variar el α de trabajo (figura 41).

Figura 41

3. El resultado ofrecido, para el ejemplo anterior, es el que muestra la tabla 8:


44

Análisis de varianza de un factor

RESUMEN Grupos Cuenta Suma Promedio Varianza

G1 7 82 11,7142857 17,9047619 G2 7 108 15,4285714 9,95238095 G3 7 60 8,57142857 20,952381

ANÁLISIS DE VARIANZA Origen de

las variaciones

Suma de cuadrados

Grados de libertad

Promedio de los

cuadrados

F Probabilidad Valor crítico para F

Entre grupos 164,952381 2 82,4761905 5,06926829 0,01793655 3,55456109 Dentro de los grupos

292,857143 18 16,2698413

Total 457,809524 20

Tabla 8

4.3.2. Análisis de varianza de dos factores con una sola muestra por grupo (muestras

relacionadas)

1. Se disponen los datos como es habitual para el cálculo manual (tabla 9)

Sujetos A B C D 1 10 6 8 7 2 4 5 3 4 3 8 4 7 4 4 3 4 2 2 5 6 8 6 7 6 9 7 8 7

Tabla 9

2. Seleccionada la opción, se indican los rangos de entrada (si se incluyen como en el ejemplo etiquetas para las filas y las columnas se debe indicar rótulos), y de salida. (figura 42).


45

figura 42

3. El resultado ofrecido por el programa es el presentado en la tabla 10 Análisis de varianza de dos factores con una sola muestra por grupo

RESUMEN Cuenta Suma Promedio Varianza

1 4 31 7,75 2,91666667 2 4 16 4 0,66666667 3 4 23 5,75 4,25 4 4 11 2,75 0,91666667 5 4 27 6,75 0,91666667 6 4 31 7,75 0,91666667

A 6 40 6,66666667 7,86666667 B 6 34 5,66666667 2,66666667 C 6 34 5,66666667 6,66666667 D 6 31 5,16666667 4,56666667


las variaciones

Suma de cuadrados

Grados de libertad

Promedio de los

cuadrados


Filas 84,2083333 5 16,8416667 10,2588832 0,00020119 2,9012952 Columnas 7,125 3 2,375 1,44670051 0,26879848 3,28738281 Error 24,625 15 1,64166667

Total 115,958333 23

Tabla 10 4.3.3. Análisis de varianza de dos factores con varias muestras por grupo (ANOVA factorial)

1. Se disponen los datos como es habitual para el cálculo manual (tabla 11)

a1 a2 A3 b1 6 4 4 4 1 2


46 2 5 2 6 2 1 2 3 1

B2 8 6 3 3 6 1 7 2 1 5 3 2 2 8 3

B3 7 9 6 6 4 4 9 8 3 8 4 8 5 5 4

B4 9 7 6 6 8 5 8 4 7 8 7 9 9 4 8

Tabla 11

2. En la ventana de diálogo se marcan los rangos de entrada (incluidas las etiquetas deseadas) y de salida. Además debe especificarse en número de filas por muestra (se trata en la práctica de indicar cuántas observaciones hay en cada combinación, dispuestas efectivamente en filas). En nuestro ejemplo es 5 (figura 43)

Figura 43

3. El resultado ofrecido, para nuestro ejemplo, es el reproducido en la tabla 12.


47

Análisis de varianza de dos factores con varias muestras por grupo

RESUMEN a1 a2 A3 Total

b1 Cuenta 5 5 5 15 Suma 20 15 10 45 Promedio 4 3 2 3 Varianza 4 2,5 1,5 3

B2

Cuenta 5 5 5 15 Suma 25 25 10 60 Promedio 5 5 2 4 Varianza 6,5 6 1 6

B3

Cuenta 5 5 5 15 Suma 35 30 25 90 Promedio 7 6 5 6 Varianza 2,5 5,5 4 4,14285714

B4

Cuenta 5 5 5 15 Suma 40 30 35 105 Promedio 8 6 7 7 Varianza 1,5 3,5 2,5 2,85714286

Total

Cuenta 20 20 20 Suma 120 100 80 Promedio 6 5 4 Varianza 5,68421053 5,26315789 6,63157895


las variaciones

Suma de cuadrados

Grados de libertad

Promedio de los

cuadrados


Muestra 150 3 50 14,6341463 6,7295E-07 2,79806045 Columnas 40 2 20 5,85365854 0,00531041 3,19072058 Interacción 20 6 3,33333333 0,97560976 0,45198087 2,29459829 Dentro del grupo

164 48 3,41666667

Total 374 59

Tabla 12

El procedimiento efectuado por el programa corresponde a un modelo de efectos fijos. En la tabla de resultados siempre se denomina Muestras a la fuente de variación correspondiente al factor dispuesto en las filas de la tabla (en nuestro ejemplo factor B).


48 5. EL COMPLETO “EZANALYZE”

Microsoft Excel permite instalar un Complemento que incluye un conjunto de herramientas para el análisis de datos (denominado EzAnalyze), con un interface de ventanas muy sencillo. Debemos tener las variables construidas en las columnas y la propia Herramienta arroja una tabla de resultados del análisis solicitado muy similar a la de otros paquetes estadísticos (por ejemplo SPSS).

Este Complemento no se adjunta con el Excel, sino que hay que descargarlo de la siguiente página web: http://www.ezanalyze.com. A continuación se detalla la secuencia a seguir para su descarga e instalación:

4. Entra en la página citada y pincha donde se indica (figura 25).

Figura 25

5. Descarga la versión 2.5 que es la permite trabajar en español. En esta misma página, debajo te pide tu correo electrónico y el lugar que ocupas en la Universidad (alumnos de Universidad). (figura 26). Pregunta si se desea guardar, indica la ruta.


49

Figura 26

6. Abre Excel y en el menú Herramientas elige Complementos y Examinar (figura 27).

Figura 27

7. Busca el archivo descargado y se incluirá automáticamente en la lista de Complementos (figura

28).


50

Figura 28

8. Con un “Clic” en Aceptar el Complemento aparecerá en los botones de del menú principal.

(figura 29)

Figura 29

Nos centraremos aquí en algunas de las opciones más habituales, que se indican a continuación agrupadas como aparece en el programa:

4. Describir

• Estadística Descriptiva

• Porcentajes

5. Gráficas

6. Avanzado

• Correlación

• Pruebas t

Muestra única

Muestras apareadas


51 Muestras independientes

• ANOVA unifactorial

• ANOVA medidas repetidas

• Chi Cuadrado

�� ACTIVIDADES DEL BLOQUE “ANÁLISIS DE DATOS”

Cada uno de los procedimientos que se incluyen en este apartado se describe a continuación con un ejemplo. Los datos correspondientes a cada uno de ellos están grabados en el fichero Excel que se indica en el epígrafe. Los alumnos deben replicar el proceso descrito en cada caso.

Nota importante: para realizar cualquiera de los análisis es imprescindible que la hoja activa sea la de datos y no cualquier otra.

5.1. Describir (�� descriptiva.xls)

5.1.1. Estadística Descriptiva

Estas dos opciones del menú Describir son útiles y adecuadas para realizar informes descriptivos completos de variables. La opción Estadística Descriptiva facilita de modo automático un resumen numérico completo de la variable (estadísticos de tendencia central y dispersión) mientras que la opción Porcentajes permite calcular los porcentajes y las frecuencias de cada variable (más adecuado para variables discretas).

El primer paso es, lógicamente, seleccionar en el menú EzAnalyze la opción Describir, y dentro de ella, Estadística Descriptiva. Se abrirá el cuadro de diálogo correspondiente, donde procederemos a especificar los distintos estadísticos y las variables a analizar, tal y como muestra la figura 30.

Figura 30


52 Una vez seleccionados los elementos básicos del informe (los que habitualmente son de

interés), procedemos a Aceptar. Los resultados ofrecidos por el programa a partir de las indicaciones anteriores se copian automáticamente en una hoja nueva, a la que denomina EZA1, son los reproducidos en la figura 31.

Figura 31

5.1.2. Porcentajes

Para completar el informe descriptivo numérico, podría resultar de interés realizar también un cálculo de porcentajes de la variable discreta Grupo. Para ello, seleccionamos del menú EzAnalyze, describir y porcentajes. De nuevo se nos abre una ventana que nos permite elegir la variable/s a analizar. Una muestra de los resultados, eligiendo la variable Grupo se copia en una hoja nueva EZA2 y se muestra en la figura 32.

Figura 32

5.2. Gráficas

Para completar el informe deseamos obtener una representación gráfica de la distribución de las variables. Esta herramienta nos permite trabajar múltiples opciones, veamos dos de ellas: histograma de cada variable y datos desagregados.


53 5.2.1. Histogramas

Seleccionamos en el menú de Gráficas la opción Histogramas, nos aparecerá una ventana donde debemos elegir las variables que queremos representar gráficamente y el tipo de gráfico, marcamos, como ejemplo, lo que se muestra en la figura 33.

Figura 33

Los resultados que arroja en una hoja nueva, se presentan en la figura 34.

Figura 34

Los gráficos circulares o de sectores suelen representar bien variables discretas de pocas categorías, como es el caso del ejemplo con la variable Grupo, sin embargo, para variables discretas de varias categorías son más apropiados los gráficos de barras así como los gráficos de área lo sin para las variables continuas. Veamos un ejemplo con la variable Medida. Repetimos el proceso anterior, en este caso seleccionando la variable Medida y la gráfica de Áreas. El resultado se muestra en la figura 35.


54

Figura 35

El gráfico originalmente ofrecido por defecto en la opción Histograma habitualmente debe ser formateado y modificado para que presente un aspecto adecuado. Como todos los gráficos en Excel, seleccionando los distintos elementos del gráfico con el botón izquierdo del ratón (títulos, leyenda, serie de datos, ejes, etc.) podemos modificarlo.

5.2.2. Desagregación

En ocasiones resulta muy interesante estudiar una variable continua desagregada o agrupada en las categorías de una variable discreta. En el ejemplo presentado nuestra variable continua será Medida y nuestra variable discreta Grupo.

Seleccionando en el menú de Gráficas la opción Desagregación se nos presenta una ventana como la que aparece en la figura 36, allí seleccionamos las variables y la opción de representación que nos interesa.

Figura 36

Los resultados se guardan en una hoja nueva y muestran en la figura 37.


55

Figura 37

5.3. Avanzado

5.3.1. Correlación (��Correlaciones y dos muestras para EzAnalyze.xls)

Esta opción es útil y adecuada para obtener coeficientes de correlación de Pearson entre dos o más variables cuantitativas. Para ello seleccionaremos en EzAnalyze la opción correspondiente a Correlación y seleccionaremos el par de variables para las que interesa estudiar la correlación (esta es una de las limitaciones del complemento, no permite trabajar de una vez con más de un par de variables). En nuestro ejemplo seleccionaremos v1 y v10 y pulsaremos aceptar.

Los resultados que arroja son tanto numéricos (correlación, N y probabilidad de ocurrencia asociada al estadístico) como gráficos, tal y como puede apreciarse en la figura 38.


56

Figura 38

5.3.2. Pruebas T (��Correlaciones y dos muestras para EzAnalyze.xls)

Para realizar pruebas “t” de Student el EzAnalyze nos proporciona tres opciones: Muestra Única, Muestras apareadas y Muestras independientes.

Si seleccionamos la primera de ellas, debemos indicar el valor de la Media poblacional, supongamos que es 5 y seleccionar la variable a la que queremos hacer el contraste, por ejemplo v1 (figura 39).

Figura 39

Los resultados obtenidos muestran información numérica (Media, desviación típica, valores de t y de p) junto con una breve interpretación sustantiva, así como información gráfica, como puede observarse en la figura 40.


57

Figura 40

Si nuestro interés es hacer un contraste de dos medias en muestras relacionadas debemos elegir la opción de Pruebas T para muestras apareadas. La ventana que aparece nos permite elegir las dos columnas o variables en las que habremos colocado los datos de ambas muestras relacionadas, en nuestro ejemplo V1 y V1bis. Los resultados obtenidos son de características similares a los de la prueba anterior y se muestran en la figura 41.

Figura 41

En el caso de que las muestras sean independientes, se debe seleccionar una variable dependiente continua (variable a contrastar) y otra de agrupación (la que crea las dos muestras independientes), los grupos que crea esta última variable los obtiene automáticamente pulsando el botón obtener grupos, en nuestro caso contrastaremos si existen diferencias significativas en v1 entre los grupos formados por la variable sexo (0: chicos, 1:chicas), ver figura 42.


58

figura 42

Los resultados que se obtienen son similares a los de las pruebas anteriores y se muestran en la figura 43.

figura 43

Documents

0.3 Tutorial Excel