Por José Francisco Martínez Jiménez
Funcionario jubilado.
Servicio de Gestión Contable
Intervención General de la Junta de Andalucía
Consejería de Hacienda y Financiación Europea
Es muy conveniente haber leído antes todos los artículos relacionados con LibreOffice Calc publicados en el apartado Tecnología práctica de esta revista.
Aquí puedes descargarte las hojas de calculo para practicar estos ejercicios:
Caso práctico nº 13: Cálculo de intereses (interés legal del dinero)
El Banco de España explica que: “El interés legal se aplica como indemnización de daños y perjuicios cuando el deudor deja de pagar y no se ha pactado un tipo determinado. También sirve de referencia para normas legales o pactos en contratos. Desde el año 1987 es fijado para cada año por la Ley de Presupuestos Generales del Estado”.
¿Cómo podemos ayudarnos de LibreOffice Calc para saber el importe total a pagar/cobrar si conocemos el importe inicial (capital) y la fecha de inicio y la fecha final, fechas en que comienza y termina el cálculo de intereses? Ver Imagen 1.
En este caso práctico usaremos la fórmula del interés simple (ver imágenes 2 y 3) dependiendo de si el año es bisiesto o no:
Siendo:
- C: Capital o importe inicial.
- r: Porcentaje anual del tipo de interés (expresado como número, no con formato de porcentaje).
- t: Tiempo expresado en días (días que se calculan restando a la fecha final, más reciente, la fecha inicial, más antigua).
- I: Intereses a calcular.
Paso 1: Crear una tabla con fechas y porcentajes a aplicar.
En una hoja, a la que llamaremos “TABLAS”, crearemos una tabla como la de la Imagen 4. Las dos columnas imprescindibles son la B y la C, es decir, la fecha (columna B) a partir de la cual hay que aplicar el porcentaje (columna C) del interés legal del dinero publicado en los Presupuestos Generales del Estado (PGE). El resto de columnas es información adicional que no usaremos en ningún cálculo.
Paso 2: Crear la hoja “DATOS”.
Ahora, en una hoja a la que llamaremos “DATOS” (cada cual puede poner los nombres que crea más conveniente a las hojas, obviamente), crearemos la “interfaz de usuario”, es decir, lo que se ve y cómo se ve (colores, tipos de letra, etc.), cuáles van a ser las celdas en las que vamos a introducir los datos necesarios, cuáles serán las celdas que nos devuelvan los resultados de las fórmulas, … Un ejemplo es la Imagen 5. Las celdas B5, C5 y D5 (con fondo blanco) serán las encargadas de recibir los datos de entrada. El resto contendrán fórmulas o información adicional.
¿Por qué los resultados parciales van de la fila 10 a la 45, ambas inclusive, es decir, 36 filas y no más o menos filas? Pues porque en la hoja “TABLAS” el número de filas con los tipos de interés es 36 y, por tanto, ese es el número máximo de filas que se pueden usar. Si añadimos una nueva fila con un nuevo tipo de interés (y su fecha asociada) tendremos que añadir otra en la hoja “DATOS” y copiar y pegar las fórmulas de las celdas de la fila anterior.
En principio vamos a necesitar cuatro variables: capital, tipo de interés anual, fecha de inicio y fecha final (con estas fechas se calcularán los días de acuerdo con la fórmula del interés simple, ver imágenes 2 y 3). Ahora bien, el tipo de interés anual lo tenemos en la hoja “TABLAS”, por lo tanto, sólo necesitaremos tres celdas para los datos de entrada. Ahora nos toca pensar en la restricciones (requisitos a cumplir) de las tres variables que nos quedan, es decir, qué conjunto de valores se consideran válidos para poder efectuar los cálculos:
- Capital: No tiene sentido un capital negativo o cero, por tanto, el valor del capital debe ser mayor que cero (Capital > 0).
- Fecha Inicial: Si, en la hoja “TABLAS”, la primera fecha es 01/01/1990, entonces esta fecha inicial no puede ser anterior a aquélla y, también, tiene que ser igual o anterior al día de hoy (Fecha Inicial >= 01/01/1990 Y Fecha Inicial <= Fecha de HOY).
- Fecha Final: Tiene que ser más reciente que la fecha inicial o, como mínimo igual, pero nunca puede ser anterior y, también, tiene que ser igual o anterior al día de hoy (Fecha Final >= Fecha Inicial Y Fecha Final <= Fecha de HOY).
Y en LibreOffice Calc ¿cómo podemos hacer lo anterior? Efectivamente, haciendo uso de la validación de datos (menú Datos → Validez…). Ver imágenes 6, 7 y 8.
Enlace a: Número 7 Revista EnRed@2.0: LibreOffice Calc: Validación de Datos
Nota: HOY( ) es una función sin argumentos que devuelve la fecha actual del sistema operativo de nuestro ordenador.
Pero, como el que tiene boca se equivoca y si es humano también con las manos, tenemos que especificar una restricción más y es que no se muestren los resultados hasta que todas las celdas estén rellenas y sus datos correctos, es decir, que pertenezcan a sus conjuntos de datos válidos respectivos . Las celdas C7:F7 se han combinado, por tanto, a todos los efectos es como si fuera la celda C7. La fórmula de esta última restricción es (debemos esperar al Paso 4 para escribirla en la hoja):
=SI(SUMA.PRODUCTO(ESBLANCO(B5:D5))>0;"Cumplimente los datos de entrada: Capital y/o Fecha Inicial y/o Fecha Final";SI(FECHA_INICIAL>FECHA_FINAL;"La FECHA FINAL debe ser igual o posterior a la FECHA INICIAL";"Correcto"))
La segunda función SI( ) de esta fórmula es también necesaria porque, a veces, se cumplimentan los datos sin llevar un orden lógico (capital, fecha inicial, fecha final) y se comienza al revés, (fecha final, fecha inicial, capital). Si el resultado final es “Correcto” entonces se mostrarán los resultados de los cálculos.
Paso 3: Crear nombres para celdas y rangos a utilizar en las fórmulas.
Para determinadas celdas y rangos es preferible asignarles un nombre que siempre será más ilustrativo que, por ejemplo, $TABLAS.$B$2:$C$37. Podemos hacer uso del menú Hoja → Intervalos y expresiones con nombre → Gestionar… Ctrl+F3 para ir añadiendo los nombres que queramos otorgarles a las distintas celdas o intervalos de celdas (ver Imagen 9). En el caso que nos ocupa los podemos ver en la Imagen 10.
Por ejemplo, en las fórmulas en las que intervenga la celda B5 de la hoja “DATOS”, usaremos el nombre previamente definido de “CAPITAL” que es mucho más fácil de entender. Debemos recordar que cuando se le asigna un nombre a una celda o rango, automáticamente se convierte su referencia a absoluta, es decir, siempre hace referencia a la misma celda aunque se copie y pegue.
Paso 4: Crear las fórmulas.
Hasta aquí todo ha sido pensar y actuar en el diseño (colores, tipos de letra, formatos numéricos, de fecha, etc.) y en los requisitos que se han de cumplir antes de comenzar con los cálculos. Ahora viene lo que nos cuesta más a todos, el crear las fórmulas necesarias para obtener el resultado buscado. Veamos …
- Celda B10:
=SI(CONTROL<>"Correcto"; ""; FECHA_INICIAL)
Traducción: Si el resultado de la celda CONTROL, que es la C7, es distinto de “Correcto” entonces muestra NADA (eso es lo que significa las comillas “”); en caso contrario, es decir si es igual a “Correcto” entonces muestra la FECHA_INICIAL, que es la celda C5). - Celda B11:
=SI(O(CONTROL<>"Correcto";B10=""); ""; SI(ESERR(INDICE(L_FECHAS;COINCIDIR(FECHA_INICIAL;L_FECHAS)+FILA()-10)); ""; SI(INDICE(L_FECHAS;COINCIDIR(FECHA_INICIAL;L_FECHAS)+FILA()-10)>FECHA_FINAL; ""; INDICE(L_FECHAS;COINCIDIR(FECHA_INICIAL;L_FECHAS)+FILA()-10))))
- Celdas B12:B45: Copiamos la celda B11 y la pegamos en todo este rango.
Enlace a: Número 8 Revista EnRed@2.0: Caso práctico n.º 1 (para las funciones INDICE y COINCIDIR).
Debemos consultar frecuentemente la Ayuda de LibreOffice Calc para comprender mejor todas las funciones y funcionalidades de las aplicaciones que componen LibreOffice.
Tal vez lo más complicado de entender sea: ¿Por qué al resultado de COINCIDIR( ) se le suma la FILA( ) y se le resta 10? Porque “FILA( ) – 10” funciona como un contador (1, 2, 3, 4, 5, …,) para la función INDICE( ). Empezamos en la celda B11. La fila es 11 que al restarle 10 nos da 1. En la celda B12 la fila es 12 que al restarle 10 nos da 2. En B13 nos dará 3 y así sucesivamente. Este contador nos devuelve la fila que necesitamos en la función INDICE( ). Si en lugar de empezar en la B11 hubiéramos empezado en la B15 entonces habría que restar 14 en lugar de 10.
- Celda C10:
=SI(Y(B11="";B10=""); ""; SI(B11="";FECHA_FINAL; B11-1)). Como B11 es una fecha, al restarle 1 nos devuelve el día anterior, es decir, si B11 es 01/01/2023 entonces C10 es 31/12/2022. - Celdas C11:C45: Copiamos la celda C10 y la pegamos en todo este rango.
- Celda D10:
=SI.ND(BUSCARV(B10; T_FECHA_INTERES;2); "").
Enlace a: Número 2 Revista EnRed@2.0: LibreOffice Calc: Búsqueda en una tabla con dos variables.
Enlace a: Número 8 Revista EnRed@2.0: Caso práctico nº 2: Cómo agilizar las búsquedas en tablas …
- Celdas D11:D45: Copiamos la celda D10 y la pegamos en todo este rango.
- Celda E10:
=SI.ERROR(C10-B10+1; "")
Si al calcular C10-B10+1 nos da cualquier error entonces muestra NADA (“”) pero si no da error entonces muestra el resultado del cálculo. - Celdas E11:E45: Copiamos la celda E10 y la pegamos en todo este rango.
- Celda F10:
=SI.ERROR(REDONDEAR(CAPITAL*D10*E10/SI(ESAÑOBISIESTO(B10); 36600; 36500); 2); "")Como podemos observar, el denominador de la fórmula del interés simple utiliza los días del año dependiendo si ese año es bisiesto o no. - Celdas F11:F45: Copiamos la celda F10 y la pegamos en todo este rango.
- Celda E5 (total intereses):
=SI(CONTROL<>"Correcto"; ""; SUMA.PRODUCTO(F10:F45;E10:E45<>"")) - Celda F5 (capital más intereses):
=SI(CONTROL<>"Correcto"; ""; CAPITAL+INTERESES)
Por último, las celdas B7:F7 tienen un formato condicional (ver Imagen 11 ) para que, si todos los requisitos se cumplen se hagan “invisibles” (sólo tenemos que especificar el mismo color para los datos que para el fondo de la celda).
Este caso práctico no deja de ser un recurso más o menos didáctico. Ahora está en vuestras manos aplicar el contenido y adecuar las hojas de cálculo a vuestras necesidades. Por mucho que se lea sobre hojas de cálculo, si no se practica, no sirve de nada y pronto se olvida.
Caso práctico nº 14: Ocultar/mostrar columnas/filas rápidamente.
Tenemos una tabla como la de la Imagen 12 (Cuenta General – Resumen Estado Liquidación Presupuesto de Gastos por Capítulos – Ejercicio 2021) y necesitamos ocultar y mostrar rápidamente tanto algunas columnas como algunas filas (ver Imagen 13).
Podríamos ir columna por columna y fila por fila y seleccionar el menú Formato → Columnas → Ocultar, o bien, Formato → Filas → Ocultar y, posteriormente, Mostrar (ver imágenes 14 y 15). Esta forma es bastante tediosa. Veamos cómo hacerlo de una manera más cómoda …
Paso 1: Columnas.
Seleccionamos, por grupos consecutivos, las columnas que queremos ocultar. En primer lugar las columnas B y C (CRÉDITO INICIAL y MODIFICA. CRÉDITO) y pinchamos en el menú Datos → Grupo y esquema → Agrupar… F12(este <F12> es un atajo de teclado, si pulsamos la tecla <F12> hace el mismo efecto que la opción de menú anterior, ver Imagen 16).
En segundo lugar seleccionamos la columna E (GASTOS COMPROMETI) y pulsamos <F12> y por último, seleccionamos la columna H (REMANENTE CRÉDITO) y pulsamos <F12> otra vez.
Vemos que nos aparecen estos números y símbolos (ver Imagen 17). Son los denominados símbolos de esquema.
Paso 2: Filas.
Ahora hacemos lo mismo (seleccionar y pulsar <F12>) pero con las filas 3 a 7 (OPERACIONES CORRIENTES), 9 y 10 (OPERACIONES DE CAPITAL) y 12 y 13 (OPERACIONES FINANCIERAS). Ver Imagen 18.
Paso 3: Símbolos de esquema.
Cuando se nos muestra un símbolo negativo (-) es porque se muestran las filas/columnas que abarca ese símbolo negativo. Si se mostrara un símbolo positivo (+) es porque están ocultas esas filas/columnas (ver Imagen 19). Simplemente tenemos que pulsar en los símbolos que queramos para mostrar/ocultar las filas/columnas que no, o sí, necesitemos ver.
Si pinchamos en el símbolo con el número 1 perteneciente a las filas se ocultan todas las filas previamente agrupadas bajo un mismo símbolo de esquema. Si lo hacemos en el 1 correspondiente a las columnas se ocultan todas las columnas previamente agrupadas bajo un mismo símbolo de esquema (ver Imagen 20). El número 2 realiza la operación contraria, es decir, nos muestra todas las filas/columnas (ver Imagen 21) anteriormente ocultadas.
Y si lo que queremos es desagrupar (quitar el esquema) de unas determinadas filas/columnas, primero las seleccionamos y acto seguido pulsamos a la vez <Ctrl>+<F12>, o seleccionamos el menú Datos → Grupo y esquema → Desagrupar… Ctrl+F12.
Este artículo se ha escrito utilizando LibreOffice Writer, Calc e Impress versión 7.4.6 para entornos corporativos.
Saludos cordiales.
Si te ha gustado este artículo, encontrarás más contenidos interesantes en nuestra sección “Tecnología práctica”.
Y no dejes de ver qué tenemos publicado en los distintos números de EnRed@2.0.
