martes, 7 de abril de 2009

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 notas inventadas 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 la celda 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 ayudan a 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 una pequeñ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 un nú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 la esquina 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 la fó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 ir entre 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 Insuficientes
Nº de Suficientes
Nº 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 H2 hasta 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 haya celdas 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 si tenemos 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 cada alumno. 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 que sea, 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 nos molestará 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 alumno que 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 resultado de 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íamos hecho 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. Y luego 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.