creación de hoja de calificaciones con excel 2007 · presentación del curso excel pertenece al...

Creación de hoja decalificaciones con Excel 2007Autor: Xiskya Valladares

Descubre miles de cursos como éste en www.mailxmail.com 1

mailxmail - Cursos para compartir lo que sabes

http://www.mailxmail.com



Presentación del curso

Excel pertenece al paquete de ofimática Office de Microsoft, este programa esuno de los más utilizado tanto por empresas, instituciones y público en general.Este curso está dirigido a profesores interesados en crear su propia hoja decálculo de calificaciones, con medias y estadísticas automáticas.

A lo largo de los capítulos de este curso podrás saber introducir fórmulas, creargráficos estadísticos, dar formato a tus hojas de cálculo, dar formato condicionala las hojas y otras funciones que te serán de gran ayuda para organizar y crearhojas de calificaciones en Excel 2007.






1. Preparando la estructura de cada hoja dellibro

Preparamos las hojas de cálculo que va a contener nuestro libro de calificaciones.Para ello ponemos nombre a cada hoja (situado en la parte inferior izquierda)haciendo doble clic encima del nombre que aparece por defecto que suele ser"Hoja1, Hoja2, Hoja3".

Ilustración 1

Si necesitamos crear más hojas porque tenemos más de tres grupos de clase,hacemos clic encima del icono señalado con la flecha roja en la ilustración 1. Demanera que creamos tantas hojas como grupos de clase tengamos.

Ilustración 2

Si todas las hojas van a necesitar los mismos cálculos, basta que construya una yluego esa la copie las veces que quiera y sólo le cambio los nombres o notas.

Si cada hoja lleva cálculos distintos, por ejemplos: si las notas de secundariatienen una ponderación distinta a las de bachillerato, entonces no copio lashojas, sino que las creo, o copio sólo el formato pero no las fórmulas.

Tengo que tener claro en un papel cuál va a ser la estructura de cada hojaporque esto será lo primero que hagamos.

De manera que queda estructurado así (como en la Ilustración 3):

Columna A: para el número de alumnos

Columna B: para el nombre de los alumnos

Columna C: para la nota de clase

Columna D: para la nota de un examen

Columna E: para la nota de un segundo examen

Hasta aquí en estas columnas es donde introduciremos datos, en las próximasque enumeraré sacaremos automáticamente las medias, el redondeo y la notaque aparecerá en el Boletín de notas.

Columna F: para presentar la media según la ponderación que demos

Columna G: para presentar el redondeo de esa media

Columna H: para presentar la nota del Boletín en letras de manera automática.






Ilustración 3

Una vez teniendo la estructura podemos ajustar el ancho de la columna con eltexto automáticamente. Primero seleccionamos las columnas y luego hacemosclic en "Autoajustar ancho de columna".






2. Introduciendo las fórmulas

Antes de pasar a dar formato a nuestra hoja, vamos a introducir las fórmulas. Para ello lo primero es que en las columnas de notas introduzcamos algunas notasinventadas que nos sirvan sólo como ayuda para ver los resultados de nuestras fórmulas.

a. Haciendo la media de las notas según ponderación Vamos a hacer primero la media de las notas dándole a cada una la ponderación que queramos. Para ello:- nos colocamos en la celda de la media- hacemos clic en la barra de fórmulas- escribimos la fórmula

Ilustración 5

La explicación de la fórmula es completamente matemática:

Ilustración 6 =SUMA((C2*0,2);(D2*0,2);(E2*0,5))

Lo que aparecerá en esa celda F2 será igual a la SUMA de el valor de la celda C2 multiplicado (la multiplicación la expresa el asterisco) por 0,25 (es decir el 20%del total) más (los valores sumados están separados por punto y coma) el valor de la celda D2 multiplicado por 0,25 (es decir el 25% del total) más el valor de lacelda E2 multiplicado por 0,5 (es decir el 50% del total).

Cuando acabo de escribir la fórmula he de llegar al final de la línea y apretar Intro o Enter. Entonces veré el resultado de la operación en la celda. Los colores ayudana entender la operación de la fórmula.

De esta manera tenemos la media. Si ponemos el cursor encima de la celda que contiene la fórmula y hacemos clic en la esquina inferior derecha cuando aparece unapequeña cruz negrita y arrastramos hacia abajo, se copia toda la fórmula para el resto de filas de esa columna.

b. Haciendo los cálculos del redondeo Lo que haremos ahora será redondear el resultado de la media, es decir el valor de la celda F2. Seguiremos los mismos pasos que en la fórmula anterior:

- nos colocamos en la celda del redondeo- hacemos clic en la barra de fórmula- escribimos la fórmula

La fórmula nos permite redondear con el número de decimales que queramos, nosotros le diremos que redondee el valor de F2 a cero decimales para obtener unnúmero entero.

Ilustración 7 De esta manera obtenemos:

Ilustración 8 En este caso no se nota porque el valor de F2 no contenía decimales, pero se notará cuando F2 sí los tenga.

Para que se aplique la misma fórmula a cada uno de los alumnos, recordemos que ponemos el cursor encima de la celda que contiene la fórmula y hacemos clic en laesquina inferior derecha cuando aparece una pequeña cruz negrita (donde marca la flecha roja de la Ilustración 9) y arrastramos hacia abajo, se copia toda lafórmula para el resto de filas de esa columna.

Ilustración 9 c. Convirtiendo la nota a texto automáticamente Ahora vamos a convertir el valor del redondeo, es decir de la celda G2, al texto que será el que aparezca en el Boletín de notas del alumno.

- nos colocamos en la celda del Boletín- hacemos clic en la barra de fórmulas- escribimos la fórmula

Esta vez la fórmula es un poco más complicada porque se trata de condiciones anidadas, pero entendiendo su lógica no resultará difícil.

Esta sería la forma en su modo más simple:

Ilustración 10 Quiere decir:






SI el valor de G2 es menor que (<) 5 entonces (expresado mediante el punto y coma) deberá aparecer el texto "Insuficiente" (las cadenas de texto deben irentre comillas), de lo contrario (expresado con el punto y coma) debe aparecer "Aprobado".

Pero necesitamos que diga más cosas, no basta que sólo nos clasifique en "Insuficientes" y "Aprobados". Queremos que cumpla con estas condiciones:

Si es igual a 5: Suficiente; si es igual a 6: Bien; si es igual a 7:Notable; si es igual a 8: Notable; si es igual a 9:Sobresaliente; si es igual a 10: Sobresaliente.

Para esto aplicaremos la condición anidada, una condición dentro de otra, de este modo:

Ilustración 11 La fórmula es:

=SI(G2<5;"Insuficiente";SI(G2=5;"Suficiente";SI(G2=6;"Bien";SI(G2=7;"Notable";SI(G2=8;"Notable";SI(G2=9;"Sobresaliente";SI(G2=10;"Sobresaliente")))))))

De esta manera, saldrán los valores convertidos en textos según las condiciones que le hemos dado.

d. Contando cuántos alumnos tienen una nota determinada De una lista de notas, queremos saber cuántos alumnos tienen, por ejemplo, un 7, es decir, un Notable. Lo haremos de la siguiente manera:

Como siempre, los pasos van a ser:- nos colocamos en la celda donde queremos que aparezca el resultado- hacemos clic en la barra de fórmulas- escribimos la fórmula

Escribimos debajo de la lista, en una columna, por ejemplo, así:Nº de InsuficientesNº de SuficientesNº de Bien:Nº de Notables:Nº de Sobresalientes:

Ahora en la columna de al lado, pondremos la fórmula para que el programa cuente lo que queremos contar.

Ilustración 12 La fórmula es: =CONTAR.SI(H2:H6; "Insuficiente")

Significa que en la celda C36 vamos a CONTAR.SI en el rango H2:H6 (rango es el conjunto de celdas dentro de las que vamos a contar, es decir, desde la celda H2hasta la celda H6) cuántas veces aparece el texto "Insuficiente".

Esto mismo hacemos para todos los demás valores, cambiando únicamente el "Insuficiente".

De manera que quedaría algo así:

Ilustración 13 Esto es mejor ponerlo debajo de todo el listado de notas, no al lado porque si quieres seguir añadiendo al lado notas de otro trimestre, así puedes hacerlo. e. Contar el total de alumnos Ahora haremos una operación para que nos cuente el total de alumnos existentes dentro de un margen de celda que nosotros le demos, aunque en ese rango hayaceldas vacías, es decir, alumnos no existentes. Lo que haremos será utilizar una fórmula para que sólo cuente las celdas que no estén vacías y de manera que sitenemos que añadir un alumno a mitad de año, no tengamos que retocar esta operación.

Imaginemos que tenemos 27 alumnos cuyos nombres están colocados en la columna B, mientras que en la columna A tenemos el número correspondiente de cadaalumno. Imaginemos también que no vamos a poder tener más de 35 alumnos. Lo que haremos será que nos cuente las celdas que contienen un número (el quesea, da lo mismo) en el rango que va de la celda A2 hasta la celda A36. De esta manera las celdas vacías, no las contará.

Seguiremos los pasos de siempre:- nos colocamos en la celda donde queremos que aparezca el resultado- hacemos clic en la barra de fórmulas- escribimos la fórmula

Y la fórmula será muy simple:

=CONTAR(A2:A36)

Ilustración 14 A esta fórmula podríamos ponerle la condición de que contara sólo los números 5 por ejemplo, pero como eso no nos interesa, la dejamos así.

La operación podemos colocarla donde queramos, pero sugiero que se ponga debajo del todo, es decir, debajo de la lista de Nº de "Insuficientes", etc. Así no nosmolestará si necesitamos añadir nuevos alumnos en algún momento.

Podríamos haber sumado el total de "Insuficientes"+ Total de "Suficientes"+Total de "Bien", etc. Pero no sería fiable porque podría faltarnos la nota de algún alumnoque no ha asistido a los exámenes, por ejemplo.






Ilustración 15 f. Porcentaje de las notas de clase Ya tenemos todos los datos necesarios para sacar el porcentaje de los resultados de notas de la clase. Lo que haremos ahora es aplicar una simple regla de tres.Seguiremos los pasos de siempre:

- nos colocamos en la celda donde queremos que aparezca el resultado- hacemos clic en la barra de fórmulas- escribimos la fórmula

La fórmula será una regla de tres. Podemos hacerlo de dos maneras:- Contando dentro de la fórmula el total de alumnos- Poniendo el valor de la celda que nos hacía la operación de contar el total de alumnos

Para el primer caso:

Ilustración 16 =(C38*100)/(CONTAR(A2:A36))

Para el segundo caso:

Ilustración 17 =(C38*100)/C62

En el primer casoC38 es la celda que contiene el número de "Insuficientes" multiplicado por 100 (por el 100%) y el valor de la multiplicación dividido por el resultadode contar el total de alumnos.

En el segundo caso, se divide directamente por el valor de la celda C62 que contiene ya el resultado de contar el número de alumnos. Esta operación la habíamoshecho en uno de los pasos anteriores.

De manera que nos queda así:

Ilustración 18 También se puede sacar el porcentaje de las notas numéricas. Para ello, primero hemos de contar cuántos alumnos tienen un 1, cuántos un 2, cuántos un 3, etc. Yluego sacar el porcentaje. Los pasos serían igual a los anteriores.

Como ya lo hemos hecho, sólo pondré un resumen sin tantas especificaciones.

La siguiente fórmula cuenta cuántos alumnos tienen un 1 de nota:

=CONTAR.SI(G2:G35; 1)

Ilustración 19 Para contar los demás, sólo iremos variando ese 1, especificando qué nota debe contar.Una vez que tengo el resultado de contar todas las notas numéricas, paso a convertirlos a porcentajes:

Ilustración 20 Y ahora sólo lo paso a porcentaje igual que hicimos anteriormente, mediante la regla de tres:

=(C47*100)/(CONTAR(A2:A36))

Ilustración 21 Esto mismo para todos los demás. De manera que queda así:






Ilustración 22 Los totales los sacamos aplicando una simple suma de los rangos que queremos sumar.






3. Creando los gráficos estadísticos

Antes de pasar a darle formato, sólo nos queda crear los gráficos estadísticos. Será fácil porque Excell lo haceautomáticamente una vez que tenemos los valores que contendrá el gráfico.

Para ello, primero seleccionamos las celdas que contienen los porcentajes de las notas:

Ilustración 23

Y una vez seleccionadas vamos a la hoja "Insertar" y ahí en la ficha "Gráficos" hacemos clic en el tipo degráfico que nos vaya mejor para presentar nuestros datos:

Ilustración 24

Para nuestro caso seleccionaremos el que dice "Columna" y dentro elegimos el primero de los "cilíndricos"."cilíndricos". Esta selección la podemos cambiar después si no nos gusta.

Ilustración 25

De esta manera el gráfico se crea automáticamente. Para cambiarlo de lugar, lo arrastramos. Y para editarlo,






es decir, para cambiarle el título a la serie y ponerle título al gráfico,

También podemos seleccionar algún Estilo de diseño que nos guste más, etc. Podemos modificarlo segúnlas selecciones que hagamos en "Diseño de gráfico", etc.

Ilustración 26

En el siguiente gráfico elegimos el Diseño de gráfico 9 y el Estilo de diseño que se ve seleccionado en laIlustración 25.

Ilustración 27

Para cambiar los títulos, se hace doble clic dentro de cada uno y se escribe dentro del cuadro tal como se veen el recuadro "Notas".

Para otros diseños del gráfico, se selecciona y botón derecho del ratón y encontraremos otras opciones.






4. Dando formato a la hoja de cálculo

Sólo queda lo más fácil. Darle formato bonito a nuestra hoja de cálculo.

a. Orientación de la página

Lo primero es seleccionar la orientación del papel. En nuestro caso lo pondremos Horizontal.

Ilustración 28

b. Márgenes de la página

A continuación, seleccionamos los márgenes. En nuestro caso, lo personalizaremos así:

Ilustración 29

c. Formato del texto y de las celdas

Y ahora meteremos colores a las filas de los datos. No es aconsejable, aunque quede rápidamentemuy bonito, convertirlo a Tabla. Sino hacerlo manualmente para que las fórmulas no varíen nada.

Los pasos a seguir serán:- Seleccionar las celdas a colorear






- Rellenarlas de color- Dar color al texto y ponerle negrita si hace falta- Marcar los bordes con el color que deseemos.

Ilustración 30

1. Botones para el Texto: negrita, cursiva, subrayado. Y en la línea superior el tipo de fuente(Calibri) y el tamaño (12 pt). La A grande agranda el texto y la pequeña lo hace más pequeño.

2. Botón para rellenar de color las celdas seleccionadas.

3. Botón para dar color al texto

4. Botón para colocar el texto respecto a la altura de la celda.

5. Botón para colocar el texto respecto al ancho de la celda.

6. Botón para ajustar el texto respecto a la celda. Hace que quepa el texto en una celda aunquepara ello tenga que mostrarlo en varias líneas.

7. Botón para unir varias celdas (primero se seleccionan las celdas a unir y luego se hace clic en elbotón).

8. Botón para marcar los bordes de las celdas.

Para usar cualquiera de esos botones, antes hemos de seleccionar las celdas a las que queremosque afecte la acción que producen.

También podemos copiar el formato de dos filas de una vez al resto de las filas. Por ejemplo:damos el color a dos filas (en el ejemplo hemos desenfocado los nombres por protección dedatos), y luego seleccionamos esas dos filas y damos al botón copiar o Ctrl+V.

Ilustración 31

A continuación seleccionamos el resto de las filas y desplegamos el menú del botón Pegar,seleccionamos "Pegado especial" y dentro seleccionamos "Formato" para que sólo nos pegue elformato. Entonces veremos que el formato (color, tipo de texto, etc.) de esas dos filas se copia alresto de las filas de la tabla.






Ilustración 32

Ilustración 33

d. Ajustar el ancho de las columnas

Ahora vamos a ajustar el ancho de las columnas a nuestro gusto. Podemos hacerloautomáticamente o manualmente. Lo más rápido es ajustándolo automáticamente según elcontenido del texto de las celdas. Esto se hace en el botón "Formato" situado en la ficha "Celdas"de la hija "Inicio", en la opción "Autoajustar ancho de columna". Lo podemos ver en laIlustración 34.

Si lo hacemos manualmente, hemos de arrastrar cada columna hasta que nos guste y una vez queajustamos el ancho de una columna, podemos copiarlo al resto de columnas igual que hicimoscon el formato. Primero seleccionamos la columna cuyo ancho nos interesa copiar en otras yluego, en "Pegado especial" tendremos que seleccionar "Ancho de las columnas".






Ilustración 34

e. Ajustar el alto de las filas

El siguiente paso será ajustar el alto de las columnas. Lo primero será seleccionar todas las filascuyo alto queremos modificar y a continuación vamos a hacerlo igual que con el ancho decolumnas.

Automáticamente, se haría así: En el botón "Formato" situado en la ficha "Celdas" de la hija "Inicio", en la opción "Autoajustar alto de filas".

Manualmente, no podemos hacerlo como las columnas. Sino que lo haremos seleccionando lasfilas y luego en el botón "Formato" hacer clic en "Alto de fila" (Ilustración 35) donde nos saldráuna ventana como la Ilustración 36 en la que tendremos que introducir el valor del alto.






Ilustración 35

Al hacer clic en "Alto de fila" nos saldrá una ventana como la de la Ilustración 36 en la que hemosde introducir el número del alto. En nuestro caso hemos puesto 15, no es aconsejable un númeromenor de 15.

Ilustración 36

f. Bordes de las celdas

Para seleccionar el tipo de borde y el color de la línea de los bordes, vamos a "Formato" y elúltimo del Menú que se despliega dice: "Formato de celdas". Hacemos clic ahí y nos vamos a lapestaña "Bordes". Ahí seleccionaremos todo lo referente a bordes según nuestro gusto onecesidades. Podemos verlo en la Ilustración siguiente.






Ilustración 37

1. Deberíamos hacer clic en el botón 1 y 2 para que nos muestre los bordes externos e internos delas celdas seleccionadas.2. Botón para mostrar bordes externos. 3. Botón para mostrar bordes internos.4. Desplegable para seleccionar el color de la línea de los bordes.5. Estilo de la línea del borde de las celdas.6. Al finalizar, hacer clic en Aceptar.






5. Duplicando una hoja de cálculo

Si tenemos varios grupos en que las ponderaciones de las notas son iguales y lasfórmulas no tienen por qué cambiar sustancialmente, no hace falta hacer denuevo todo para crear otra hoja igual. Puedo copiar la hoja de cálculos ysimplemente cambiar el nombre de los alumnos y si varía el número de alumnos,hacer los pequeños cambios pertinentes en las fórmulas.

Para copiar una hoja de cálculo, sólo hacer clic sobre el nombre de la hoja quedeseo copiar con el botón derecho del ratón y luego clic donde dice "Mover ocopiar". Selecciono ahí la hoja que deseo copiar y marco la casilla de Crear unacopia. Luego Aceptar.

Ilustración 38

Después sólo será necesario cambiar el nombre a la nueva hoja que aparecerá.






6. Dando formato condicional a las notas

Para que nos salgan automáticamente en rojo los Insuficientes, vamos a hacerlopor separado los números y los textos.

a. Para presentar en rojo los números menores de 5:

Seleccionamos las celdas que contienen notas numéricas, hacemos clic en "Formato Condicional" que está en la ficha "Estilos" de la hoja "Inicio". Ahí en "Resaltar reglas de celdas" seleccionamos la opción "Menor que" tal como seve en la siguiente Ilustración. Y luego nos aparecerá una ventana para meter losdatos.

Ilustración 39

En la ventana que nos aparecerá ponemos "menor que" 5 y personalizamos elformato de la celda:






Ilustración 40

Cuando hacemos clic en "Formato personalizado" se nos abre una ventanadonde especificaremos que aparezca en rojo y negrita las notas inferiores a 5.Luego sólo habrá que hacer clic en Aceptar en las dos ventanas abiertas.

Ilustración 41

Vamos a hacer lo mismo con las notas en texto. Sólo que como no se trata denúmeros, en "Resaltar reglas de celda", elegiremos "Texto que contiene".






Lo primero será seleccionar las notas en texto (Insuficiente, Notable, etc.) yluego hacemos clic en "Formato Condicional" que está en la ficha "Estilos" dela hoja "Inicio". Ahí en "Resaltar reglas de celdas" seleccionamos la opción "Texto que contiene". Se nos abrirá una ventana igual que antes, ahípondremos "Insuficiente" y esta vez, para variar, podemos dejar el formato quesale predeterminado "Relleno rojo claro con texto rojo oscuro".

Ilustración 42

Tal como podemos ver en la siguiente Ilustración:






Ilustración 43






7. Otras posibilidades

Ya serían motivo de otro Tutorial, pero al menos enumeraré otras opcionesposibles que se me ocurren para nuestro Libro de Calificaciones:

a.Bloquear las columnas que contienen las fórmulas para que no las borremos sinquerer en un descuido.b.Proteger con contraseña nuestro libro de Calificaciones para que nadie más quenosotros pueda modificarlo.c.Colocar cabecera y pie de página con Títulos, subtítulos, datos de la clase y delprofesor, número de página, etc.d.Crear una hoja de resumen del año a partir de las hojas trimestrales con suscorrespondientes gráficos.e.Crear un gráfico comparativo de todos nuestros grupos de clase para podercomparar los resultados de nuestra asignatura en todos los grupos que tenemos.

Espero os animéis a probar de hacerlo. Si tenéis alguna sugerencia o duda, nodudéis en escribirme a: xiskya[arroba]gmail.com Con gusto os responderé lomás pronto que pueda.






creación de hoja de calificaciones con excel 2007 · presentación del curso excel pertenece al...

Documents