Cuenta las celdas no vacías de un rango

Número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor

A continuación observa la siguiente tabla (e4.xls). Las celdas en color sombreado contendrán las fórmulas.

Se ha de calcular lo siguiente:

• En la columna E la nota final que será la media de los tres trimestres.
• En la columna F la evaluación en forma de texto. Haremos servir la función =SI para lo siguiente: Si la nota media es de 0 a 4,99, aparecerá la palabra Insuficiente, si la nota media es de 5 a 6,99, aparecerá la palabra Aprobado, si la nota media es de 7 a 9,5, aparecerá la palabra Notable, si la nota media es de 9,6 a 10, aparecerá la palabra Excelente.

• En las celdas inferiores calcular la Mediana, Notas máxima y mínima, y moda
• Finalmente, colocar unas celdas que informarán de:

El número de alumnos que hay
El número de insuficientes, aprobados, notables y excelentes que hay
Qué porcentaje representa cada uno de los anteriores

La solución vendrá representada por:

Comentarios a las fórmulas:

Esta función calcula los pagos periódicos que será necesario realizar sobre un préstamo, a un interés determinado, y en un tiempo x. De esta forma se puede ver cuanto se tiene que pagar mensualmente, o cuanto cobran los bancos de intereses. Esto permitirá jugar con diferentes capitales, años o tipos de interés. La sintaxis de la orden es:

Esta fórmula calculará el pago anualmente. Si se quiere saber los pagos mensuales se tendrá que dividir el interés por 12 y multiplicar el tiempo por 12. Observa:

=PAGO(Interés/12;Tiempo*12;Capital)

Ejercicio (e5.xls): Suponer que se ha de calcular los pagos mensuales y anuales periódicos del siguiente supuesto:

Celda B5: =PAGO(B2;B3;B1)

Celda B6: =PAGO(B2/12;B3*12;B1)

Observar que la fórmula PAGO ofrece un resultado en negativo (rojo). Si se quiere convertir el resultado en un número positivo, se debe encerrar la función dentro de otra función: =ABS(). La función ABS significa absoluto. Un número absoluto de otro número, siempre será positivo. La fórmula en ese caso sería:

=ABS(PAGO(B2/12;B3*12;B1)).

Como ya se ha comentado, en este tipo de hojas se puede probar a cambiar cantidades de las celdas B1,B2 y B3 y comprobar los distintos resultados. A continuación hay un ejemplo (e6.xls) de un supuesto de crédito desglosado mes a mes. En este ejemplo se utiliza una función nueva: =PAGOINT(), que desglosa el interés que se paga de la cantidad mensual. La función =PAGO() muestra lo que se debe pagar, pero no dice cuanto se paga de capital real y cuanto de intereses. La función =PAGOINT() realiza esto último.

Introducir las fórmulas de las dos primeras filas. A partir de la segunda fila, sólo restará copiar las fórmulas hacia abajo. Suponer un crédito de 2.000.000 de pts con un interés del 8,5% en un plazo de 2 años, es decir, 24 meses. Si se observa la primera línea de fórmulas:

A6 Número de mes que se paga

B6 Cálculo del pago mensual con la función =ABS(PAGO($B$2/12;$B$3*12;$B$1))

C6 Se resta la cantidad pagada de los intereses y se obtiene el capital real que se paga =B6-D6

D6 Desglose del interés con la función =ABS(PAGOINT(B2/12;A6;B3;B1;0))

E6 El primer mes tiene acumulado el único pago de capital real =C6

F6 Pendiente queda el capital inicial menos el pagado en el primer pago =B1-E6

En este apartado es interesante utilizar el asistente para funciones que posee Excel.

Ahora se ha de calcular el segundo mes. A partir de ahí, sólo habrá que copiar la fórmula hacia abajo.

Las celdas que cambian en el segundo mes son:

D7 =ABS(PAGOINT($B$2/12;1;$B$3*12;F6)) Calcula el pago sobre el capital pendiente (F6) en vez de sobre el capital inicial como en el primer mes (B1). Convertir las celdas B2 y B3 en absolutas, ya que es mejor copiar la función hacia abajo y solo actualizar la celda F6 a medida que se copia la fórmula.

E7 El acumulado del mes será igual al acumulado del mes anterior más el capital del presente mes. =E6+C7

F7 Nos queda pendiente el capital pendiente del mes anterior menos el capital que se ha pagado el presente mes. =F6-C7

Ahora sólo queda seleccionar toda la segunda fila y copiarla hacia abajo, hasta la fila 29, donde estará la fila del último mes de pago.

Utilización de Botones de Control

La utilización de los controles en forma de botón agiliza el manejo de las hojas de cálculo. Antes que nada es necesario activar la barra de botones (si no lo está ya). La barra se activa con la opción Ver - Barras de herramientas y activando la casilla Formularios.

En este apartado se va a diseñar una hoja de cálculo de préstamo para un coche. Partiendo de la hoja e7.xls con las fórmulas preparadas:

B1: Aquí se introduce manualmente el precio del coche

B2: La reducción puede ser un adelanto en ptas. del precio total del coche. Se refleja en porcentaje.

B3: Fórmula =B1-(B1*B2), es decir, lo que queda del precio menos el adelanto. Ese será el precio.

B4 y B5: El interés y el número de años a calcular.

B6: Fórmula =ABS(PAGO(B4/12;B5*12;B3)). Calcula el pago mensual tal y como se comentó con anterioridad.

Esta hoja sería válida y podría calcular los pagos periódicos mensuales. Tan sólo será necesario introducir o variar las cantidades del precio, reducción, interés o años. El problema viene cuando en esta misma hoja se puede:

- Introducir cantidades desorbitantes como 1.500.000.000.000.000

- Introducir palabras como "Perro" en celdas numéricas

La finalidad de este ejercicio es crear la misma hoja, pero de una forma más "amigable", sobre todo para los que no dominan mucho Excel. La hoja será más atractiva a la vista, más cómoda de manejar, y además no permitirá introducir cosas ilógicas como las anteriormente expuestas. Para ello se utilizan los controles de diálogo.

Para este ejemplo es necesario introducir una lista de coches con sus correspondientes precios:

Se ha colocado el rango a partir de la columna K. Esto se debe a que cuando se tenga la hoja preparada, este rango "no moleste" y no se vea. Este rango de celdas comienza a la misma altura que el anterior, es decir, en la fila 1. Realizar lo siguiente:

1. Seleccionar el rango entero (desde K1 hasta L6)
2. Acceder al menú Insertar - Nombre - Crear y desactivar la casilla Columna izquierda del cuadro de diálogo que aparece.
3. Aceptar el cuadro de diálogo.

Con esto se le da el nombre Coche a la lista de coches y el de Precio a la lista de precios. Estos nombres servirán más adelante para incluirlos en fórmulas, de forma que no se utilicen rangos como D1:D6, sino el nombre del mismo (Coche).

A continuación se va a crear una barra deslizable que servirá para escoger un coche de la lista.

1. Pulsar un click en el botón (Cuadro combinado desplegable). Si no se encuentra en las barras que se tienen visibles será necesario abrir la barra Diálogo.
2. Trazar un rectángulo desde la celda D2 hasta la celda E2
3. Colocar un título en D1: Coche

El resultado hasta ahora debe ser más o menos el siguiente:

 En este punto es necesario resaltar el hecho de que en este cuadro de diálogo, si se pulsa un click fuera, al volver a colocar el ratón sobre el mismo, aparecerá una mano para posteriormente utilizarlo. Si se quiere editarlo para modificarlo, se ha de pulsar un click manteniendo la tecla de Control del teclado pulsada. Una vez seleccionado, pulsar doble click para acceder a sus propiedades.

• Pulsar doble-click (manteniendo Control pulsada) sobre el cuadro que se acaba de crear y rellenar el cuadro de diálogo con las siguientes opciones:

• Rango de entrada: Coche
• Vincular con la celda: H2
• Líneas de unión: 8

Esto significa que en la opción Rango de entrada se le está diciendo a este cuadro de diálogo que "mire" en el rango que ha sido definido como Coche, es decir: K2:K6 o lo que es lo mismo, los precios. De esta forma, cuando se abra esta lista que se está creando y se escoja un coche, aparecerá un número en la celda H2. Este número será la posición en la lista que se encuentra el coche escogido. Por ejemplo, si se despliega la lista y se escoge el coche Ford, aparecerá en la celda H2 el número 2. Realizar una comprobación. Pulsa un click fuera del cuadro de lista para poder utilizarlo. Cuando salga el dedo, abrir la lista y escoger cualquier coche. Su posición en la lista aparecerá en la celda H2. Esta celda servirá como celda de control para hacer otro cálculo más adelante. De igual forma, si se escribiera un número en la celda H2, el nombre del coche aparecería en la lista desplegable.

• Seleccionar la celda B1 y escribir: =INDICE(Precio;H2)

Observar que en la celda aparece el precio del coche escogido en la lista desplegable. Esto es gracias a la función =INDICE. Esta función busca el número que haya en la celda H2 en el rango Precio y devuelve el contenido de ese mismo rango. De esta forma sólo se encontrarán coches de una lista definida con unos precios fijos. Así no hay posibles equivocaciones.

Limitación de la reducción para validar valores

Por desgracia aún es posible introducir un porcentaje inadecuado para la reducción del precio.

• Pulsar un click en la herramienta Control de número y crear un control más o menos como éste:

• Con la tecla de control pulsada, hacer doble click sobre el control recién creado para acceder a sus propiedades.
• Rellenar las casillas con los siguientes datos:

Valor actual: 20
Valor mínimo: 0
Valor máximo: 20
Incremento: 1

• Vincular con la celda: H3
• Aceptar el cuadro y pulsar Esc para quitar la selección del control y poder utilizarlo
• Pulsar sobre las flechas del control recién creado y observar cómo cambia el valor de la celda H3
• Situarse en la celda B2 y escribir: =H3/100. Esto convierte en porcentaje el valor de H3

El control se incrementa sólo con números enteros pero es preciso que la reducción se introduzca como un porcentaje. La división entre 100 de la celda H3 permite que el control use números enteros y permite especificar la reducción como un porcentaje.

Creación de un control que incremente de cinco en cinco

Si se quiere introducir reducciones por ejemplo del 80%, sería preciso ir pulsando la flecha arriba bastantes veces.

• Acceder a las propiedades del control recién creado
• Escribir 100 en el cuadro Valor máximo, un 5 en el cuadro Incremento, y aceptar.
• Pulsar Esc para desactivar el control

Observar que ahora la celda B2 va cambiando de 5 en 5. A partir de aquí es preciso comprobar una amplia variedad de combinaciones de modelos y de porcentajes de reducción.

Limitación del rédito para validar sus valores

El rédito es el tanto por ciento de la reducción. Interesarán porcentajes que vayan variando de cuarto en cuarto y dentro de un rango del 0% al 20% ya que posibilitan porcentajes decimales. Por eso son necesarios más pasos que los que se precisan con el pago de la reducción, y por ello a continuación se va a explicar el uso de una barra de desplazamiento en vez de un control como el anterior.

• Crear una Barra de desplazamiento (que se encuentra dentro de Diálogo) más o menos así:

• Acceder a sus propiedades y modificarlas de la siguiente forma:

Valor mínimo: 0 Valor máximo: 2000

Incremento: 25 Vincular con celda: H5

• Aceptar el cuadro de diálogo y pulsar Esc para quitar la selección
• Seleccionar la celda B4 y escribir en ella: =H5/10000
• Con el botón Aumentar decimales, aumentarla en 2 decimales

Probar ahora la barra de desplazamiento. La celda B4 divide por 100 para cambiar el número a un porcentaje y por otro 100 para poder para aproximar a las centésimas. Ahora sólo falta el control para los años.

• Crear un nuevo Control numérico y colocarlo más o menos así:

• Acceder a sus propiedades y cambiarlas de la siguiente forma:

Valor mínimo: 1 Valor máximo: 6
Incremento: 1 Vincular con la celda: H6

• Seleccionar la celda B6 y escribir =H6.
• Probar este último control y verificar que los años cambian de uno en uno.

El modelo ya está completo. Ya se puede experimentar con varios modelos sin tener que preocuparse de que se puedan escribir entradas que no sean válidas. De hecho, sin tener que escribir nada en el modelo. Una de las ventajas de una interfaz gráfica de usuario es la posibilidad de reducir las opciones para validar valores. Para completarlo se va a establecer un último aspecto:

• Seleccionar las columnas desde la G hasta la J y ocultarlas. El aspecto final será el siguiente:

   Regresar al índice