INDICE DE MASA CORPORAL - 1

En esta primera práctica tendremos que abrir un nuevo libro de Excel, al que llamaremos INDICE DE MASA CORPORAL (IMC) . En la primera hoja, realizaremos los ajustes necesarios para poder calcular el IMC de una persona, a partir de su peso y su altura ( http://es.wikipedia.org/wiki/Indice_de_masa_corporal )

Pasos:

1. En la celda A3 escribimos "Peso (kg)"

2. En la celda A4 escribimos "Estatura (m)"

3. En la celda A6 escribimos "IMC"

4. Introducimos valores en la celda B3 "90", y B4 "1,80"

5. Escribimos en la celda B6 la fórmula "=B3/(B4*B4)" (es decir, relación entre el peso y el cuadrado de la altura)

6. Comprobamos que el IMC es 27,78 (un IMC adecuado debe estar entre 20 y 25)

7. Modificamos el peso y la altura y observamos como varía el IMC.

8. Aplicaremos los colores apropiados a las celdas (color de fondo y color de tinta) y aproximaremos con dos decimales los valores de las celdas B4 y B6.

9. Finalmente, cada uno podrá probar con sus datos personales de peso y altura, cúal es su Indice de Masa Corporal.

10. Antes de guardar el fichero, modifica el nombre de la hoja de calculo actual. En la parte inferior izquierda,modifica el nombre Hoja1 por el nombre de IMC-1.

EVALUACION DE ALUMNOS - 4

Vamos a continuar con nuestra práctica elaborando estadísticas particulares para cada asignatura. Para ello abriremos nuestro fichero de Excel y realizaremos el siguiente trabajo en la Hoja 2.

1. Copiaremos a partir de la celda A3 la siguiente información:

2.  Vamos a introducir las fórmulas de manera que cada columna nos muestre la siguiente información de cada asignatura:

• Alumnos: Número total de alumnos que cursan dicha asignatura (si no tienen nota es que no están matriculados de dicha materia).
• Aprobados: Número total de alumnos que han obtenido una nota mayor o igual a 5.
• Suspensos: Número total de alumnos que han obtenido una nota menor que 5.
• % Suspensos: Porcentaje de alumnos suspensos sobre el total de alumnos matriculados.

Bueno, pues empezamos por la primera asignatura, ING (Inglés).

Colócate en la celda C3 y empieza a escribir la siguiente fórmula "=CONTAR(". Después de abrir el paréntesis pasa a la Hoja1(Notas) y selecciona con el ratón el rango de celdas (D3:D22). Finalmente, no olvides cerrar la fórmula con un paréntesis. El resultado final de la fórmula debe ser "=CONTAR(Notas!D3:D22)".

Fíjate que la expresión Notas! indica que el rango de celdas se obtiene de una hoja distinta a la actual, en este caso, los datos se obtienen de la hoja llamada Notas.

El valor obtenido en la celda debe ser 20, lo cuál indica que todos los alumnos cursan Inglés.

Colócate ahora en la celda D3 y empieza a escribir la siguiente fórmula "=CONTAR.SI(". Al igual que antes, ve a la hoja Notas y selecciona el rango de celdas (D3:D22) y a continuación escribe un punto y coma (;) y la condición, (">=5") y no olvides cerrar el paréntesis; El resultado final de la fórmula debe ser "=CONTAR.SI(Notas!D3:D22;">=5")"

El valor obtenido en la celda debe ser 9, que corresponde al número de alumnos aprobados en Inglés.

Colócate ahora en la ceda E3, para calcular el número de suspensos. La fórmula en este caso la calcularemos a partir de las dos columnas anteriores, "=C3-D3".

El valor obtenido en la celda debe ser 11, que corresponde al número de alumnos suspensos en Inglés.

Finalmente obtenemos el porcentaje de alumnos suspensos, introduciendo la siguiente fórmula en la celda F3: "=E3/C3*100". Debemos obtener un porcentaje del 55% de alumnos suspensos en inglés.

3. Vamos a probar ahora cómo al realizar cualquier variación en las notas de Ingles, los resultados se actualizan de manera automática. Vamos a aprobar el Ingles a la alumna Ana Mier de Cilla. Modifica su nota y ponle un 5. Verfica que el porcentaje de alumnos suspensos en inglés baja al 50%.

Ahora vamos a suponer que un alumno tiene el inglés convalidado, y por tanto no tiene nota. Elimina la nota de Román Calavera Calva y deja su nota en blanco. Observar que el porcentaje de alumnos suspensos sube ahora al 52,6% ya que los cálculos se realizan sobre 19 alumnos en lugar de sobre 20.

4. Ahora tendremos que repetir el mismo proceso para el resto de asignaturas. Lamentablemente no es posible copiar las fórmulas debido a que la distribución de las filas y las columnas están en orden inverso. En la hoja actual las asignaturas están por columnas y en la hoja notas están por filas.

Prueba a realizar los siguientes cambios.

• Modifica la fórmula de la celda C3 por "=CONTAR(Notas!D$3:D$22)". Al poner el símbolo $ delante del número, cuando la fórmula se copie, el rango de los números no variará.
• Copia la fórmula hacia abajo hasta la celda C20.
• Modifica una a una las fórmulas desde la celdas C4 hasta la celda C20 cambiando la letra D por las letras E,F,G,...,U sucesivamente.
• Finalmente deberás obtener un resultado parecido a lo siguiente:

5. Ahora vamos a repetir la misma operación con la columna D.

• Nos colocamos en la celda D3 y modificamos la fórmula por " =CONTAR.SI(Notas!D$3:D$22;">=5")". Al igual que antes, al colocar el símbolo $ delante de los números, éstos no cambiarán cuando se copie la fórmula a otra celda.
• Copia la fórmula hacia abajo hasta la celda D20.
• Modifica una a una las fórmulas desde la celdas D4 hasta la celda D20 cambiando la letra D por las letras E,F,G,...,U sucesivamente.
• Finalmente deberás obtener un resultado parecido a lo siguiente:

6. Finalmente sólo quedaría arrastrar las fórmulas de las celdas E3 y F3 hacia abajo, hasta llegar respectivamente a las celdas E20 y F20. El resultado final debe ser el siguiente:

7. Finalmente modifica el diseño de la hoja para que sea lo más parecido al siguiente modelo:

8. Graba el fichero de Excel pero antes modifica el nombre de la hoja actual (Hoja 2) por Estadísticas

INDICE DE MASA CORPORAL - 2

Vamos a realizar una pequeña modificación sobre el ejercicio anterior. Para ello, abriremos el archivo de Excel llamado INDICE DE MASA CORPORAL y accederemos a la HOJA 2 (pestaña zona inferior izquierda) que deberá estar en blanco.

Pasos:

1. Vamos a copia y pegar, todo el contenido de la hoja IMC-1  a la HOJA 2.
2. Elimina el diseño aplicado en el paso anterior a la celda B6. Debe quedar letra negra sobre fondo blanco.
3. Vamos a utilizar el FORMATO CONDICIONAL, para que la celda que refleja el valor del IMC se ilumine de un color diferente dependiendo de su valor. Estableceremos el siguiente criterio:

- Si el valor está entre 18,5 y 25 el color del IMC será letra blanca sobre fondo naranja.
- Si el valor está por encima de 25 el color del IMC será letra blanca sobre fondo rojo.
- Si el valor esta por debajo de 18,5 el color del IMC será letra blanca sobre fondo negro.

4. Si tienes problemas y te lías con el Formato Condicional, puedes comprobar el estado de las reglas en el submenú "Administrar Reglas". Las reglas deberían ser las siguientes:

5.  Prueba con diferentes valores para estatura y peso y observa como cambia el color del IMC en función del valor que se obtiene como resultado.

6. Finalmente guardaremos nuestro fichero, pero antes modificaremos el nombre de la HOJA 2 y le pondremos de nombre IMC-2.

EVALUACION DE ALUMNOS - 5

Vamos a finalizar nuestra práctica realizando una estadística a partir del número de suspensos que ha obtenido cada alumno. Para ello, sigue los pasos que se indican a continuación:

1. Colócate en la celda H2 y copia el siguiente cuadro:

2. Vamos a introducir las fórmulas necesarias para obtener el número de alumnos han sacado 0 suspensos, 1 suspenso, 2 suspensos, 3 suspensos, 4 suspensos y 5 ó más suspensos, así como el porcentaje de cada situación con respecto a la clase.

Para ello, empezaremos por contar el número de alumnos que han obtenido 0 suspensos. Nos colocamos en la celda I3 y escribimos la fórmula "=CONTAR.SI(" . Justo después de escribir el paréntesis, accedemos a la hoja notas y seleccionamos el rango (C3:C22). Finalizaremos la fórmula creando la condición, en este caso escribiremos  un punto y coma (;) y seguidamente la condición entre comillas "=0". No olvides cerrar el paréntesis. La fórmula debe quedarte como "=CONTAR.SI(Notas!C3:C22;"=0")".

Si todo ha salido bien, en la celda I3 se mostrará el valor 1 que indca que sólo hay un alumno con todas las asignaturas aprobadas.

3. Para poder utilizar la fórmula anterios, vamos a establecer el rango C3:C22 como una refencia absoluta a nivel de fila, para que al copiar la fórmula hacia abajo, el rango permanezca invariable. Para ello, modificaremos la fórmula de la celda I3 sustituyendo C3:C2 por C$3:C$22. Una vez modificada la fórmula, arrastrala hacia abajo copiándola hasta la celda I8. Ahora sólo queda modificar las condiciones de las fórmulas desde las celdas I4:I8. Hazlo y obtendrás el siguiente resultado:

Número Supensos	Número
0 Suspensos	1
1 Suspenso	3
2 Suspensos	8
3 Supensos	4
4 Suspensos	2
5 ó mas Suspensos	2

4.  Vamos ahora a calcular los porcentajes. Empezamos por calcular el porcentaje de alumnos que han sacado 0 suspensos. Para ello tenemos que ver que porcentaje supone el valor de la celda I3 con respecto al total de alumnos. Utilizaremos la siguiente fórmula "=I3/SUMA(I3:I8)*100".

Fíjate en la fórmula, se trata de dividir el valor de I3, en este caso 1, entre la suma desde I3:I8, en este caso 20, que es el número total de alumnos, y luego multiplicar por 100 para calcular el porcentaje. El valor final debe ser el 5%.

 Para el resto de la tabla procederemos de manera parecida al caso anterior. Modificaremos la fórmula para que al copiarla, el rango (I3:I8) no se modifique "=I3/SUMA(I$3:I$8)*100". Luego estiramos la fórmula copiándola hasta la celda J8. El resultado final debe ser el siguiente:

Número Supensos	Número	% Alumnos
0 Suspensos	1	5
1 Suspenso	3	15
2 Suspensos	8	40
3 Supensos	4	20
4 Suspensos	2	10
5 ó mas Suspensos	2	10

5. Terminaremos diseñando un diagrama de sectores con un diseño lo más parecido posible al siguiente:

Para elaborar el diagrama seguiremos los siguientes pasos:

• Seleccionamos el rango de celdas (H3:I8).
• Selecionamos en el Menú Insertar el Gráfico de tipo CIRCULAR 2D.

• A continuación, hacemos click sobre la ventana donde se ha insertado el gráfico y observaremos el menú Diseño que aparece en la parte superior. Escogeremos el Diseño 1.

• Ahora sólo tienes que realizar unos pequeños cambios en el diseño. Elimina el título, modifica los colores según el modelo y coloca las leyendas alrededor del gráfico

6. Para finalizar, aplica a las filas de la tabla los mismos colores que el gráfico de sectores, de forma que coincidan los colores del gráfico con los de la tabla de valores.

Con esto finalizamos la práctica EVALUACION DE ALUMNOS 

INDICE DE MASA CORPORAL - 3

Bueno, ahora vamos a seguir profundizando con nuestra práctica sobre el Indice de Masa Corporal, a través de la creación de una tabla donde podamos buscar el IMC de cualquier persona. Para ello, utilizaremos un sistema de referencia para que a partir del peso y la altura de una persona, podamos visualizar rápidamente en la tabla su IMC.

Para crear la tabla seguiremos los siguientes pasos:

1. Abrimos nuestro fichero de Excel, INDICE DE MASA CORPORAL y nos colocamos en la Hoja 3, que debe aparecer en blanco.

2. Nos colocamos en la celda C3 y escribimos el valor "50". A continuación nos colocamos en la celda D3 y escribimos el valor "55". Posteriormente seleccionamos con el ratón las dos celdas y arrastramos hacia la derecha hasta llegar al valor 120 (observa que se produce un incremento de 5 en 5).

3.Realiza la misma operación para la altura, en este caso empezando por las celdas B4 y B5 y estirando hacia abajo en este caso hasta llegar al valor "2,15".

4. Selecciona con el ratón el rango de celdas desde (C1:Q2) y utiliza la opción de combinar y centrar.

Luego escribe el texto "PESO (KG)" con la fuente Calibri 28 y aplica la justificación centrada. Seguiremos después con la altura. Pare ello, seleccionamos con el ratón el rango de celdas desde (A4:A17) y volvemos a utilizar la opción de combinar y centrar. Escribimos el texto "ALTURA" con la misma fuente anterior y aplicamos la alineación texto vertical.

Finalmente combinamos y centramos las celdas (A18:A19) y escribimos el texto "(M)".

Ya tenemos nuestra tabla preparada para empezar los cáculos.

EVALUACION DE ALUMNOS - 6

Ahora te voy a proponer una serie de ejercicios con estadísticas y gráficos, para ver si has asimilado los conceptos más importantes. Para ello abre el fichero de Excel de EVALUACION DE ALUMNOS y realiza los siguientes ejercicios:

1)  En la nueva hoja, crea un gráfico de barras que muestre el número de suspensos y aprobados de cada asignatura. Algo parecido a lo siguiente:

INDICE DE MASA CORPORAL - 4

Para rellenar la tabla, debemos tener en cuenta que en cada celda hemos de colocar la fórmula correspondiente que relacione el peso y la altura. Como es lógico, existe una manera de realizar este proceso de manera semiautomática, de forma que no tengamos que ir una a una escribiendo todas las fórmulas en cada una de las celdas.

Para ello, vamos a utilizar en nuestras fórmulas referencias absolutas y relativas. Vamos a empezar.

1. Fíjate en la primera fila de la tabla, desde la celda C4 hacia la derecha. Si tuviésemos que escribir una a una las fórmulas del IMC lo haríamos de la siguiente manera (no lo hagas por ahora):

• En C4 escribiríamos "=C3/(B4*B4)"
• En D4 escribiríamos "=D3/(B4*B4)"
• En E4 escribiríamos "=E3/(B4*B4)"
• Y así seguiríamos hasta llegar a la celda Q4.

Si analizas las fórmulas, podrás observar que en todas ellas se repite siempre la segunda parte "(B4*B4)", y que la primera parte va variando, C3, D3, E3,...

Pues bien, podemos aprovechar esta característica para escribir la fórmula sólo una vez y copiarla a toda la fila con lo que la creación de la tabla será mucho más rápida.

Para ello, colócate en C4 y escribe "=C3/($B4*$B4)". Fíjate en el símbolo ($) que antecede a la letra B, que indica que estamos aplicando una referencia absoluta, es decir, que cuando copiemos la fórmula a otra celda de nuestra hoja, esa parte de la fórmula no va a variar.

IMPORTANTE: ¿Qué sucede cuando copiamos una fórmula?

• Hacia la derecha o hacia la izquierda: cambian las letras salvo que en la fórmula la letra tenga antes un $ .
• Hacia arriba o hacia abajo: cambian los números salvo que el la fórmula el número tenga antes un $.

2. Vamos a copiar la fórmula hacia la derecha.

Para ello hay que arrastrar hacia la derecha la fórmula a partir de la esquina inferior derecha.

Si todo ha salido bien, en la primera fila aparecerán atomáticamente los valores correspondientes al IMC de una persona de 1,40 de altura en función del peso que pueda tener. Colócate en la celda Q4 y fíjate en lo siguiente: el valor de la celda debe ser "61,2" y la fórmula correspondiente es "=Q3/($B4*$B4)".

3. Ahora tendríamos que repetir el proceso para el resto de las filas. Para ello, colócate en la celda C5 es escribe la fórmula correspondiente "=C3/($B5*$B5)" y luego arrastra la fórmula hacia la derecha. Sigue con el resto de las filas hasta completar la tabla. El resultado final debe ser el siguiente:

Finalmente, hemos seleccionado todo el rango (C4:Q19) y hemos aplicado 1 cifra decimal para toda la tabla y hemos dibujado todos los bordes.

4, Finalmente graba el archivo de Excel, pero antes cambia el nombre a la Hoja 3 y ponle el nombre IMC-3

INDICE DE MASA CORPORAL - 5

Bueno, ahora vamos a realizar una pequeño ajuste que nos hubiese permitido crear la misma tabla y rellenarla de una manera todavía más rápida. Sigue los siguientes pasos:

1. Selecciona todo el rango de celdas desde (C4:Q19) y presiona el boton SUPRIMIR ó DEL.
2. Escribe la siguiente fórmula en C4 : "=C$3/($B4*$B4)"
3. Estira la fómula hacia la derecha.
4.Selecciona el rango (C4:Q4).
5. Estira todo hacia abajo, desde la esquina inferior derecha de la úlitma celda.

6. Observa que obtienes el mismo resultado, pero de una manera mucho más rápida. El secreto está en incluir en la fórmula inicial el símbolo ($) delante del número 3, de forma que cuando copiamos la 1º fila, las fórmulas se copian hacia la derecha, pero sin variar la referencia absoluta del número 3, que corresponde a la fila donde están los pesos. Por eso cuando estiramos hacia abajo, los calculos se realizan correctamente.

7. Vamos a aplicar ahora FORMATO CONDICIONAL para resaltar los valores de la tabla. Para ello, seleccionaremos el rango del celdas (C4:Q19) y aplicamos las siguientes reglas:

• Si el valor está entre 18,5 y 25 aplicamos letra verde intenso sobre fondo verde.
• Si el valor está por encima de 25 aplicamos letra rojo intenso sobre fondo rojo.
• Si el valor está por debajo de 18,5 aplicamos letra azul intenso sobre fondo azul. 

Ahora podemos observar con facilidad cuál sería el IMC ideal (color verde) para una persona a partir de su altura. Por ejemplo, una persona que mida 1,70 debería mantenerse entre los 55 y 70 Kg de peso. Por encima de los 75 Kg estaríamos hablando de sobrepeso y por debajo de los 50 Kg nos encontraríamos con problemas de infrapeso ( valores establecidos por la OMS)

Con esto terminaría la primera práctica de Excel. Guárdala en tu carpeta personal del servidor.