Tutorial de Excel para Tecnicas Cuantitativas, Apuntes de Psicología

¡Descarga Tutorial de Excel para Tecnicas Cuantitativas y más Apuntes en PDF de Psicología solo en Docsity! Tutorial de Excel XP 1 de 19 Tutorial de Excel XP Albert Fornieles, Josep M. Domènech, Albert Bonillo Contenidos 1. Presentación ............................................................................................................................................................ 2 2. Introducción de datos y gestión de hojas .............................................................................................................. 3 3. Introducir fórmulas y funciones ............................................................................................................................ 6 4. Trabajar con varias hojas .................................................................................................................................... 10 5. Creación de gráficos ............................................................................................................................................. 15 6. Estilo y formato en una hoja de cálculo. Exportación de objetos..................................................................... 18 Tutorial de Excel XP 2 de 19 1. Presentación Excel es la hoja de cálculo de Microsoft Office. Una hoja de cálculo es un programa informático que permite manipular datos numéricos y alfanuméricos que se introducen en las casillas de una cuadrícula organizada en filas y columnas. Una casilla puede incluir un gráfico o un valor calculado a partir de una fórmula que utiliza datos de otras casillas. Este gráfico o valor calculado se actualiza automáticamente cuando se modifica el contenido de alguna de las casillas implicadas en su cálculo. El objetivo de este tutorial es aprender a utilizar la hoja de cálculo Excel mediante un trabajo dirigido que consiste en diseñar una hoja de cálculo para introducir un listado de alumnos, recoger datos de interés para el profesor y generar algunas estadísticas y gráficos. El modo de trabajar con este tutorial es imprimirlo y, con el ordenador al lado, realizar las tareas que se proponen. Tened en cuenta que las imágenes pueden variar ligeramente en función de la versión de Excel y de los programas y dispositivos que tengáis instalados en vuestro ordenador. Tutorial de Excel XP 5 de 19 9) Nos comunican que hemos de añadir bajo el alumno 4 (LPR) un nuevo alumno. Para hacerlo, hay que insertar una fila entre los alumnos 4 y 5: seleccionad la fila 6, haced clic con el botón derecho del ratón y elegid la opción Insertar (también podéis utilizar el menú Insertar | Filas): 10) Completad la fila 6 que está vacía con la información del alumno JFK: 11) Una vez introducidos los datos de este alumno, deberéis volver a utilizar la herramienta Autocompletar para arreglar la numeración de la columna Caso (podéis ver el punto 7). 12) Ahora nos comunican que se trata de una confusión y hay que dar de baja a este alumno. Hemos de eliminar la fila. Seleccionad la fila correspondiente a JFK, haced clic con el botón derecho del ratón y elegid la opción Eliminar (también podéis utilizar el menú Edición | Eliminar). 13) Arreglad de nuevo la numeración de la columna Caso con la herramienta Autocompletar. 14) Nos hemos equivocado cuando hemos escrito las iniciales de un apellido. En el primer caso hemos escrito AJD y es ADJ. Haced doble clic sobre la casilla para editarla y modificad las iniciales. 15) Debéis cambiar, en la columna Sexo, “M" por “chico" y “F" por “chica". Para hacerlo, seleccionad la columna, abrid el menú Edición | Reemplazar..., definid el cambio de “M" por “chico" y pulsad el botón Reemplazar todos. Definid el cambio de “F" por “chica" y pulsad el botón Reemplazar todos. 16) Salid de Excel con el menú Archivo | Cerrar. Confirmad que queréis guardar el libro con el nombre “Trabajo dirigido de Excel.xls". Tutorial de Excel XP 6 de 19 3. Introducir fórmulas y funciones El objetivo de este apartado es aprender a introducir las fórmulas y funciones más básicas que nos pueden ayudar a la hora de generar variables, categorizar datos, realizar estadísticas de los alumnos, etc. 1) Abrid el fichero “Trabajo dirigido de Excel.xls". 2) Calculad la nota total del idioma sumando las notas de teoría y conversación. Insertaremos una columna detrás de Idioma (C) y daremos a la casilla J1 la etiqueta Total idioma. Para hacerlo, debéis seleccionar la columna Valoración, hacer clic con el botón derecho del ratón y elegir la opción Insertar. Una vez escrita la etiqueta, debéis seleccionar la columna y abrir el menú Formato | Columna | Autoajustar a la selección. 3) Para calcular la nota total del idioma como resultado de sumar la teoría y la conversación, es necesario seleccionar la casilla J2 y escribir la fórmula: =H2+I2 (con Excel es necesario poner siempre el signo “=" delante de una fórmula o de una función). Cuando salgamos de la casilla dará el resultado de la operación J2 = H2 + I2 → 5 + 4 = 9. 4) Copiad esta fórmula para todos los alumnos. Para hacerlo, volveremos a utilizar la herramienta Autocompletar (también se podría hacer con Copiar y Pegar): seleccionad la casilla J2 y arrastrad el punto de la esquina inferior derecha hasta la casilla J15. Ahora tendremos para todos los alumnos las notas del idioma, obtenidas de la suma entre la prueba escrita y la de conversación. 5) ¿Qué ha hecho la herramienta Autocompletar cuando ha copiado la fórmula de la casilla J2 hasta la J15? Si, por ejemplo, hacemos clic sobre la casilla J5 veremos que su contenido es: J5 = H5+I5. Esto significa que Excel interpreta las referencias a las casillas H2, I2 y J2 de la fórmula inicial J2 = H2+I2 como referencias relativas: cuando arrastramos desde la fila 2 hasta la 15, los índices de fila de las referencias a las casillas de la fórmula cambian paralelamente. 6) Calculad la media de las notas de Mates, Sociales y Total idioma. Insertaremos una columna detrás de Total idioma (seleccionad la columna Valoración, haced clic con el botón derecho del ratón y elegid la opción Insertar). Poned en la casilla K1 la etiqueta Media notas. Una vez escrita la etiqueta seleccionad la columna y elegid: Formato | Columna | Autoajustar a la selección. Tutorial de Excel XP 7 de 19 7) Buscad en la ayuda de Excel la función PROMEDIO (pulsad F1 o abrid el menú ? | Ayuda de Microsoft Excel). Podéis escribir directamente en la casilla K2 la siguiente función estadística: =PROMEDIO(F2:G2;J2). Para escribir la función con el cuadro de diálogo, se marca la casilla K2 y se aprieta el botón fx de la barra de herramientas (o el menú Insertar | fx Función...). En la lista desplegable seleccionad la categoría Estadísticas y marcad la función PROMEDIO; en la zona inferior del cuadro aparece la sintaxis y una breve descripción de la función. Pulsad Aceptar para abrir el cuadro de la función. Poned el cursor sobre la casilla F2 y arrastradlo hasta la G2; en el cuadro de texto Número 1 encontraréis escrito: F2:G2 (que quiere decir de la casilla F2 hasta la G2). Ahora haced clic sobre el cuadro de texto Número 2 y sobre la casilla J2; en el cuadro de texto Número 2 encontraréis escrito J2. Pulsad Aceptar. En la casilla K2 encontraréis un 9, que es la media de las casillas F2, G2 y J2: (9 + 9 + 9) / 3. Tutorial de Excel XP 10 de 19 4. Trabajar con varias hojas El objetivo de este apartado es aprender a introducir fórmulas y funciones en la segunda hoja (Resumen) del mismo libro para generar las siguientes estadísticas a partir de los datos e informaciones contenidas en la primera hoja (Datos): 1) Abrid el fichero “Trabajo dirigido de Excel.xls". 2) Visualizad en la misma pantalla las dos hojas para facilitar el trabajo. Abrid el menú Ventana | Nueva ventana (aparentemente no pasa nada). Abrid el menú Ventana | Organizar... y elegid la opción Horizontal. Activad en la parte superior la hoja Resumen y en la parte inferior la hoja Datos. 3) Estadística del número de alumnos. Pondremos la etiqueta Número de alumnos en negrita en la casilla A1 de la hoja Resumen. En la casilla B1 escribiremos la función CONTAR, que cuenta el número de casillas completadas con un número. Para contar el número de alumnos transferiremos a CONTAR el rango de casillas con el número de Caso porque no hay ninguna vacía. Marcad la casilla B1 y pulsad el botón fx de la barra de herramientas. Seleccionad la categoría Estadísticas, elegid la función CONTAR y pulsad Aceptar para abrir su cuadro de diálogo: el cursor está situado en el cuadro de texto Ref1, haced clic sobre la casilla A2 de la hoja Datos y arrastrad hasta la casilla A15 para escribir este rango de casillas (Dades!A2:A15) en el cuadro Ref1. Pulsad Aceptar y se escribirá la función =CONTAR(Datos!A2:A15). Trabajar con más de una hoja de cálculo implica que la definición del rango (A2:A15) debe incluir también el nombre de la hoja donde se encuentran las casillas (Datos!): Tutorial de Excel XP 11 de 19 4) Excel permite representar un rango de casillas con un nombre. Así, podemos definir con el nombre Sexo el rango de las casillas (D2:D15) de la hoja Datos que contienen el sexo de los 14 alumnos. Para hacerlo, marcad las casillas (abrid el menú Insertar | Nombre | Definir). Escribid el nombre de este rango en el cuadro de texto: Nombres en el libro: Sexo. Salid pulsando Aceptar. 5) Estadística de la variable Sexo. En primer lugar, completaremos las etiquetas. La etiqueta de la casilla A3 coincide con el contenido de la casilla D1 de la hoja Datos (porque es el nombre de la variable registrada en la columna D). Una buena estrategia es copiarla de esta casilla con una fórmula. Para hacerlo, poneos en la casilla A3 y escribid =Datos!D1 (o escribid “=" y haced clic sobre la casilla D1 de la hoja Datos). Si pulsáis Intro, aparece la etiqueta Sexo en la casilla A3. El resto de etiquetas (N, Porcentaje, Chicos, Chicas y TOTAL) se escriben directamente en la casilla. 6) Contad cuántos chicos y chicas hay. Para ello utilizaremos la función CONTAR.SI y también el nombre Sexo, que representa el rango de casillas Datos!D2:D15 que contienen el sexo de los alumnos. Poneos en la casilla B4 y escribid =CONTAR.SI(Sexe; “chico"). Para escribir la función con el cuadro de diálogo, se marca la casilla B4 y se pulsa el botón fx de la barra de herramientas. Seleccionad la categoría Estadísticas y elegid la función CONTAR.SI. Repetid la operación con las chicas (podéis copiar la fórmula de los chicos y sustituir “Chico" por “Chica"). 7) Calculad el TOTAL de chicos y chicas. Poneos en la casilla B6 y escribid la fórmula =B4+B5. 8) Calculad el porcentaje de chicos y chicas. Poneos en la casilla C4 y calculad la proporción de chicos dividiendo el número de chicos por el total con la fórmula: =B4/B$6. (El “$” ante el índice de fila indica que B$6 es una referencia mixta y al copiar la fórmula en todo el rango de las categorías de Sexo, siempre dividiremos por el contenido de la casilla B6). La fórmula da 0,5. Poneos en la casilla C4, abrid el menú Formato | Celdas... y en la pestaña Número asignadle formato de porcentaje con 1 decimal. Ahora copiad la fórmula para calcular el porcentaje de chicas y total (arrastrad el punto de la casilla C4 hasta la C6 y obtendréis la distribución del Sexo en porcentajes): 9) Estadística de notas medias. Empezaremos por completar las etiquetas de las variables que contienen las notas. Las etiquetas de las casillas B8 a H8 coinciden con el contenido de las casillas E1 a K1 de la hoja Datos. Copiadlas de estas casillas con fórmulas. Para hacerlo, poneos en la casilla B8 y escribid =Datos!E1 (o escribid “=" y haced clic sobre la casilla E1 de la hoja Datos). Al pulsar Intro aparece la etiqueta QI en la casilla B8. Ahora copiad la fórmula de la casilla B8 en las otras casillas arrastrando el punto de la casilla B8 hasta la H8. Finalmente, poned en la casilla A8 la etiqueta Notas medias en negrita. Tutorial de Excel XP 12 de 19 10) Para calcular las medias deberemos aplicar la función PROMEDIO a los rangos de las casillas de la hoja Datos que contienen las diferentes notas. Asignad a cada uno de estos rangos de casillas el mismo nombre de la columna; para ello elegid el menú Insertar | Nombre | Definir. Pero tened en cuenta que estos nombres no pueden tener blancos ni símbolos; por ello sustituiremos los blancos y los paréntesis por el guión bajo: Datos!E2:E15 → QI Datos!F2:F15 → Mates Datos!G2:G15 → Sociales Datos!H2:H15 → Idioma_T Datos!I2:I15 → Idioma_C Datos!J2:J15 → Total_idioma Datos!K2:J15 → Media_notas Datos!M2:M15 → Valoración 11) Calculad la media de todas las notas de la hoja Datos con la función PROMEDIO (también podéis calcular otros estadísticos con otras funciones, como la desviación estándar con la función DESVEST). Podéis escribir en la casilla B9 la siguiente función estadística =PROMEDIO(QI). Para escribirla con el cuadro de diálogo, debéis marcar la casilla B9, pulsar el botón fx de la barra de herramientas, seleccionar la categoría Estadísticas, elegir la función PROMEDIO y pulsar Aceptar para abrir el cuadro de diálogo de PROMEDIO. En el cuadro de texto Número 1 debéis escribir el nombre QI (que representa el rango de casillas Datos!E2:E15) y pulsar Aceptar. En la casilla B9 encontraréis 101,29, que es la media de las casillas Datos!E2:E15. Ahora copiad la función de la casilla B9 en las otras casillas con la herramienta Autocompletar arrastrando el punto de la casilla B9 hasta la casilla H9: en todas las casillas encontraréis la media 101,29... del QI. Para obtener las medias de las notas debéis marcar cada casilla y en la función =PROMEDIO(QI) sustituir QI por el nombre del rango correspondiente. Si no os habéis equivocado, encontraréis las siguientes medias: 12) Estadística de la Suficiencia. Para empezar rellenaremos las etiquetas. Poned en la casilla A11 la etiqueta Suficiencia copiándola de la casilla M1 de la hoja Datos con una fórmula. Poned N y Porcentaje en las casillas B11 y C11 copiándolos con una fórmula de B3 y C3. Las etiquetas “Apto” y “No Apto” de las calificaciones. Finalmente, poned TOTAL en la casilla A17 copiándolo con una fórmula de A6. Tutorial de Excel XP 15 de 19 5. Creación de gráficos El objetivo de este apartado es aprender a crear gráficos a partir de los datos obtenidos en el apartado anterior. 1) Abrid el fichero “Trabajo dirigido de Excel.xls" e id a la hoja Resumen. 2) Insertad una nueva hoja de cálculo. Volveremos a tener nuevamente tres hojas en el libro. Para hacerlo, hay que situarse sobre el área de los nombres de la hoja, hacer clic con el botón derecho del ratón y elegir la opción Insertar | Hoja de Cálculo. Tendremos una nueva hoja denominada Hoja1 situada entre las hojas Datos y Resumen. Haced clic con el botón derecho sobre Hoja1, elegid Mover o Copiar... y después la opción Mover al final. Cambiadle el nombre y ponedle Gráficos. 3) Gráfico de barras de la distribución de calificaciones. Se quiere construir un gráfico igual al de la imagen siguiente: Para facilitar la definición del gráfico, seleccionad las casillas con la distribución de los valores que queréis representar (A12:A13 i C12:C13 de la hoja Resumen) y abrid el menú Insertar | Gráfico... o hacer clic sobre el icono de la barra de herramientas . Se abrirá el asistente de gráficos de Excel reproducido en la imagen de la derecha. Elegid un gráfico de tipo Columnas y subtipo Columna agrupada. Pulsad el botón Siguiente > Tutorial de Excel XP 16 de 19 La imagen presenta el paso 2 del asistente. De manera automática identifica que queremos representar la serie de datos en columnas. Abrid la pestaña Serie. En el cuadro de texto Valores encontraréis escrito el rango de casillas con los porcentajes que se deben representar. Situad el cursor en el cuadro de texto Rótulos del eje de categorías (X) y seleccionad las casillas A12:A13 de la hoja Resumen para utilizar las calificaciones como etiquetas para rotular las barras del gráfico. Pulsad el botón Siguiente >. La imagen de la derecha presenta el paso 3 del asistente. Escribid en el cuadro de texto Título del gráfico el título “Distribución de las calificaciones". Abrid la pestaña Leyenda y dejad desactivada la casilla de verificación Mostrar leyenda. Pulsad el botón Siguiente >. La imagen de la derecha presenta el paso 4 y último del asistente. Permite decidir si queréis situar el gráfico en una nueva hoja o dejarlo como un objeto en la hoja actual (Resumen). Dejadlo en la hoja actual y salid del asistente pulsando el botón Finalizar. Tutorial de Excel XP 17 de 19 4) Gráfico de barras de las notas medias. Se quiere construir un gráfico para representar las notas medias de Mates, Social e Idioma, idéntico al de la imagen de la derecha. Para construirlo únicamente daremos unas pocas indicaciones: Id a la hoja Resumen, haced clic sobre la media de Mates y abrid al asistente de gráficos. Copiad el título del gráfico de la casilla A8. Para insertar referencias de casillas separadas en los cuadros de texto Valores y Rótulos, debéis marcarlas pulsando Ctrl. En el paso 4 debéis indicar que queréis situar el gráfico en la hoja Gráficos. 5) Relación entre las notas de teoría y conversación de la asignatura Idioma. Id a la hoja Datos, seleccionad el rango H2:I15 y con el asistente de gráficos pedidle un gráfico XY (Dispersión). Poned título al gráfico y a los ejes X,Y. En el paso 4 debéis indicar lo que queréis situar en la hoja Gráficos. Una vez construido, haced clic con el botón derecho sobre los puntos del gráfico y pedidle la Línea de tendencia. Se abrirá un cuadro de diálogo, que permite indicar que se incluya la ecuación de regresión y el coeficiente de determinación R2. Haced clic con el botón derecho sobre las escalas de los ejes X e Y, y elegid la opción Formato de ejes...; se abrirá un cuadro de diálogo que permite indicar qué marcas secundarias queréis cada 0.5 puntos. El gráfico resultante será similar al reproducido en la siguiente imagen (pero situado en la hoja Gráficos): 6) Moved los gráficos en la hoja Gráficos. Id a la hoja Datos y Resumen, seleccionad los gráficos creados (uno a uno) y elegid la opción Cortar del menú Edición. Volved a la hoja Gráficos y pegadlos con la opción Pegar del menú Edición. Arrastradlo hasta el ángulo superior izquierdo de la hoja. 7) Guardad el fichero pulsando el icono correspondiente.