Hojas de cálculo: Caso práctico 1 (primera parte)

Caso práctico 1

Nos vemos en la necesidad de solicitar a una entidad bancaria un préstamo de 60.000 euros para realizar unas reformas en nuestra vivienda. El banco nos proporciona el dinero a un interés anual fijo del 3%.

Se pide: Calcular la cantidad mensual que deberemos pagar al banco si deseamos devolver el préstamo más los intereses, para los siguientes periodos de tiempo:

- 1 año.
- 3 años.
- 5 años.

Solución: La hoja de cálculo con los datos de nuestro problema podría ser la que mostramos en la Figura 8.2. En ella podemos observar la cantidad solicitada para el préstamo en la celda C2, el interés fijo anual que nos cobra el banco en la C3, y los tres periodos de tiempo en las celdas B9, B10 y B11, respectivamente.

Las soluciones, que obtendremos utilizando la función PAGO(...), estarán situadas en las celdas D9, D10 y D11. Antes de usar la función estudiemos detenidamente su sintaxis y el significado de sus argumentos.

Sintaxis

PAGO(tasa;nper;va;vf;tipo)

En todas las funciones, los argumentos en negrita indican que son obligatorios y los otros que son opcionales.



Fig. 8.2. Hoja de cálculo de ejemplo.

Funciones financieras, matemáticas y trigonométricas

Funciones financieras

Existen un total de 55 funciones financieras predeterminadas, que realizan toda clase de cálculos relacionados con valores bursátiles, amortizaciones, préstamos, inversiones, letras del tesoro, etcétera.

Aunque el mundo de las finanzas pueda parecer un poco alejado de nuestra vida cotidiana, es posible que en el futuro nos veamos en la necesidad de realizar alguno de estos cálculos, bien sea para conocer la cuota de una hipoteca, los intereses a pagar por un préstamo, etcétera.

Comenzaremos viendo un sencillo ejemplo de utilización de la función PAGO(...), que calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.

Lo haremos mediante el primer caso práctico de la unidad.

Funciones matemáticas y trigonométricas

El grupo de las funciones matemáticas y trigonométricas está compuesto por un total de 60 funciones predeterminadas.

Las que no se pueden considerar trigonométricas realizan operaciones como logaritmo, potencia, redondeo, valor absoluto, máximo común divisor y mínimo común múltiplo, división entera, operaciones con matrices (producto, determinante, inversa, etc.), raíz cuadrada, números aleatorios, y un largo etcétera que seguro nos puede ayudar a la hora de realizar cálculos con las celdas.

De entre las segundas, las trigonométricas, disponemos de un gran número de funciones que calculan las típicas operaciones con ángulos, como por ejemplo el seno, coseno, tangente, etc., valor del número Pi, y funciones de conversión de grados a radianes y viceversa.

Aprenderemos a utilizar algunas de estas funciones en el Caso práctico 2.

Uso de fórmulas complejas

Uso de fórmulas complejas, funciones, expresiones y formato condicional

En la anterior unidad didáctica aprendimos qué son las fórmulas, para qué se pueden utilizar, de qué elementos pueden estar compuestas, cómo se introducen, etc., y también comentamos que Excel dispone de un gran número de funciones estándar que permiten realizar toda clase de cálculos. Es en esta unidad, dedicada al uso avanzado de Excel, donde aprenderemos a usar algunas de estas funciones, así como autilizar el formato condicional que permite aplicar diferentes formatos a las celdas en función del valor que contienen. Y si no tenemos suficiente con el gran conjunto de funciones estándar de Excel, los usuarios aventajados pueden crear sus propias funciones personalizadas, programándolas con el lenguaje Visual Basic para Aplicaciones, integrado en el propio Excel. Trataremos este tema en el punto 8.5 de la unidad, dedicado a las macros y la programación básica.

A. Funciones y expresiones

Excel ofrece al usuario un total de 329 funciones de hoja de cálculo predeterminadas, agrupadas en las siguientes categorías: financieras, matemáticas y trigonométricas, fecha y hora, estadísticas, búsqueda y referencia, base de datos, texto y datos, lógicas, información e ingeniería. Por suerte para nosotros, podemos simplificar su utilización mediante el Asistente de inserción de funciones, que podemos activar haciendo clic en el botón Insertar función de la barra de fórmulas, seleccionando la opción de menú Insertar - Fórmula, o tecleando a la vez Mayús+F3, estando situados en la celda deseada.

Según la definición que ofrece la ayuda de Excel, las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas.

La estructura de una función comienza por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por puntos y coma, y un paréntesis de cierre. Los argumentos de una función pueden ser números, texto, valores lógicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. El argumento que se designe deberá generar un valor válido para el mismo. Los argumentos pueden ser también constantes, fórmulas u otras funciones.

Los argumentos de una función pueden ser a la vez funciones. Cuando sucede esto hablamos de funciones anidadas. Excel soporta un máximo de siete niveles de anidamiento.

Cuando creemos una fórmula que contenga una función, el asistente de inserción de funciones nos ayudará a introducir las funciones de la hoja de cálculo. A medida que se introduzca una función en la fórmula, el asistente irá mostrando el nombre de la función, cada uno de sus argumentos, una descripción de la función y de cada argumento, el resultado actual de la función y el resultado actual de toda la fórmula. También tenemos la posibilidad de insertar las funciones directamente mediante el teclado. En este caso, tras teclear el paréntesis de apertura, aparece una ayuda contextual que nos muestra los diferentes argumentos de la función y en negrita el que hemos de introducir en cada instante (véase la Figura 8.1).

Las fórmulas permiten utilizar funciones para realizar sus cálculos, pero además ofrecen la posibilidad de combinarlas con otros operandos y operadores formando lo que se conoce con el nombre de expresión. Ya estudiamos en la Unidad 7 un ejemplo de expresión que combinaba funciones, referencias a celdas y valores constantes. Además, mostramos en sendas tablas los principales operadores aritméticos y de comparación que permiten la construcción de expresiones, así como la prioridad de cada uno de ellos. También comentamos que, cuando las operaciones efectuadas en las expresiones no están delimitadas mediante paréntesis, el orden de evaluación de las mismas es de izquierda a derecha. A continuación efectuaremos un repaso de cada una de las categorías de funciones, mostrando el uso de alguna de ellas mediante casos prácticos.



Fig. 8.1. Ayuda contextual de la función financiera NPER.

Un ejercicio práctico

Veamos a continuación un ejercicios práctico para potenciar lo aprendido en las dos lecciones anteriores.

1.Escribe la siguiente lista de datos:

Observa que el contenido de las celdas A2 y A6 sobrepasan el ancho de la columna. No te preocupes por ello. En estas primeras prácticas seguramente verás alguna opción algo avanzada y que no entenderás demasiado. No te preocupes por ello, pues ahora sólo se trata de familiarizarse con el modo de trabajo de Excel.

Operaciones con hojas de Excel.- Un libro de trabajo consta de varias hojas. Inicialmente, Excel XP nos permite trabajar con tres hojas cuyas etiquetas podemos observar en la parte inferior de la hoja en la que estamos trabajando. No obstante, podemos insertar hojas, copiarlas, moverlas, borrarlas, seleccionarlas...

Cambiar el nombre a una hoja.-

1.Pulsa dobre click sobre la etiqueta de la Hoja1 y cuando esté seleccionada, escribe directamente: Listado (Intro para finalizar).

Otro sistema para cambiar el nombre será desde Formato - Hoja - Cambiar nombre, o bien desde el menú contextual (botón derecho del ratón).

Copiar una hoja.-

2.Manteniendo la tecla de CONTROL pulsada, arrastra la pestaña de la hoja Listado una posición hacia su derecha. La hoja mostrará un 2 entre paréntesis:

Otro sistema sería desde Edición - Mover o copiar hoja donde veríamos un cuadro de diálogo en el que se selecciona el libro de trabajo y el lugar donde queremos colocar la hoja.

Mover una hoja.-

3.Arrastra directamente (sin mantener la tecla de CONTROL pulsada), la hoja Listado hacia otra posición.

Insertar una hoja.-

4.Selecciona con un click la hoja Listado

5.Abre el menú Insertar y escoge la opción Hoja

La hoja nueva se inserta adaptando una numeración correlativa:

También podríamos insertarla con el botón derecho del mouse.

Eliminar una hoja.-

6.Selecciona cualquier hoja y pulsa el botón derecho del ratón . Escoge la opción Eliminar. Acepta el mensaje de Excel.

También podríamos eliminarla desde la opción Edición - Eliminar hoja.

Introducción de datos y tipos de datos.- Ya hemos visto cómo escribir datos en las celdas. Vamos a ver ahora qué tipo de datos acepta Excel:

-Valores constantes. Todo tipo de datos que escribamos directamente ya sea texto o números. Este valor no cambia a no ser que lo modifiquemos o borremos.

-Fórmulas. Un valor especial que hace referencia a celdas, nombres, datos, etc, y que producen un resultado. Las fórmulas comienzan siempre con el signo de igual (=). Si modificamos el valor de una celda a la que la fórmula esté haciendo referencia, el resultado de la fórmula varía automáticamente.

Introducción de valores numéricos.- Excel posee para los valores numéricos el formato general, es decir, que podemos escribir un número como 200000 sin separadores de miles (el punto) y Excel lo dejará tal y como lo hemos escrito.

También podemos utilizar signos como:

-El punto (.) para separar los millares

-La coma (,) para expresar decimales

-El signo menos (-) para indicar cantidades negativas. Éstas se pueden indicar también entre paréntesis.

-El signo del porcentaje (%)

Otras consideraciones importantes a la hora de introducir valores numéricos son:

-Las fracciones debemos introducirlas de forma especial ya que, por ejemplo, 4/3 Excel lo tomará como una fecha y colocará en la celda el cuatro de marzo (4-mar). En su lugar introduciremos 1 1/3

-Si el valor no cabe en la celda, se visualizarán los signos ######. Debemos cambiar el ancho de la columna (como veremos más adelante) para poder visualizar todas las cifras.

-Si deseamos introducir un número y que Excel lo tome como un texto, debemos anteponer al número el signo del apóstrofe ('). Ejemplo: 1.996 Ventas anuales