LibreOffice Calc: Casos prácticos (III)

Caso practico de utilización de tablas dinámicas en un caso de presupuesto y otro para buscar filas duplicadas de una forma rápida y visual.

Por Jose Francisco Martinez Jimenez

Servicio de Gestión Contable
Intervención General de la Junta de Andalucía
Consejería de Hacienda y Financiación Europea

Caso práctico n.º 7: Variaciones porcentuales con tabla dinámica.

Se nos pide que calculemos tanto la diferencia en euros como la diferencia porcentual de los créditos, agrupados por funciones, correspondientes a los presupuestos de los ejercicios 2020 y 2021 de la Junta de Andalucía.

Una tabla dinámica, tal y como están distribuidos los datos en la hoja de cálculo (ver Imagen 1), es una manera muy “fácil” de calcular lo que se nos pide. Veámoslo …

Paso 1: Crear la tabla dinámica.

Seleccionamos el rango correspondiente, en este caso el rango A1:C51, y pinchamos en el menú “Insertar → Tabla dinámica…” (ver Imagen 2).

Paso 2. Disponer los campos en la tabla dinámica.

Arrastramos los campos correspondientes al lugar adecuado de la tabla dinámica (ver Imagen 3).

El campo “IMPORTE EUROS” lo hemos arrastrado tres veces, la primera para los créditos, la segunda para la calcular la diferencia en euros y la tercera para calcular la diferencia porcentual.

Marcamos “Filas totales” para que se nos muestre el “Total Resultado”.

Paso 3. Hallar la diferencia en euros de los créditos de 2021 respecto de 2020.

Hacemos doble clic en el campo de datos “Suma – IMPORTE EUROS 1” y en la nueva ventana seleccionamos las opciones que se ven en la Imagen 4. Le estamos diciendo a Calc que nos calcule la diferencia de importes de cada uno de los ejercicios con respecto del ejercicio 2020 (elemento base).

Paso 4. Hallar la diferencia porcentual de los créditos de 2021 respecto de 2020.

Procedemos igual que en el paso anterior pero esta vez con el campo de datos “Suma – IMPORTE EUROS 2” y seleccionamos “% de diferencia de” en tipo de valor mostrado. Imagen 5

Paso 5. Primera aproximación al resultado final.

Al pulsar “Aceptar” en los cuadros de diálogos anteriores se nos muestra el resultado de la tabla dinámica (ver Imagen 6).¿Por qué cada uno de los campos de “IMPORTE EUROS” tiene dos columnas en la tabla dinámica? Porque el campo “EJERCICIO” que hemos colocado en el apartado de “Campos de columna” tiene dos ejercicios solamente, 2020 y 2021. Si, por ejemplo, fueran cinco los ejercicios entonces en lugar de dos columnas habría cinco.

¿Por qué las columnas D y F están en blanco (valor cero)? Porque, como hemos seleccionado anteriormente, el campo base para los cálculos es el ejercicio 2020, entonces:

Columna D: crédito de 2020 – crédito de 2020 = 0
Columna E: crédito de 2021 – crédito de 2020 = el valor que corresponda
Columna F: (crédito de 2020 – crédito de 2020) / crédito de 2020 = 0 %
Columna G: (crédito de 2021 – crédito de 2020) / crédito de 2020 = el % que corresponda

También hemos de tener en cuenta que si el valor del campo base seleccionado para los cálculos es 0 (cero) entonces el resultado de la tabla dinámica mostrará el error #¡VALOR!, ya que la división por cero no está definida (ver División por cero de Wikipedia).

Paso 6. Resultado final.

Como comenté en el punto 10 del artículo LibreOffice Calc: Agrupar fechas y calcular porcentajes en Tabla Dinámica(número 3 de la revista EnRed@2.0):

10. En la tabla dinámica no es conveniente ni a veces posible cambiar los formatos y nombres de las filas y columnas. Lo que debemos hacer es seleccionar la tabla dinámica completa, copiarla y pegarla en una nueva hoja y, en esta última, modificar el formato, es decir, la forma en que vemos esos datos.

Hacemos caso y copiamos la tabla dinámica en otra hoja, eliminamos las columnas D y F (innecesarias) y damos el formato que nos interese (ver Imagen 7) y, si existe algún error, lo modificamos por el valor adecuado (en este ejemplo no hay errores). Y ponemos en práctica lo visto en el artículo LibreOffice Calc: Formato condicional (segunda parte) del número 6 de esta revista para resaltar la máxima y la mínima variación absoluta (ver Imagen 8).

Las posibilidades de formato son innumerables …

Caso práctico n.º 8: Encontrar duplicados, o no, en dos hojas distintas (o rangos).

Es decir, ¿cómo podemos encontrar las filas duplicadas (registros), o no, en dos hojas diferentes (o en dos rangos aunque estén en la misma hoja) comparando el valor de una columna (campo)?

Por ejemplo, tenemos dos hojas con datos de personas (en este ejemplo ficticias, obviamente) y necesitamos saber qué personas de la “Hoja1” se encuentran en la “Hoja2” comparando por sus respectivos NIF.

Paso 1: Dar un nombre a la lista de NIF de la “Hoja2”.

Para que sea más cómodo el trabajar con rangos absolutos de celdas en la fórmula que emplearemos lo mejor es darle un nombre a esos rangos (ver Imagen 10). En este caso, a la lista de los NIF de la “Hoja2” lo llamaremos LISTA_HOJA2 (o el nombre que queramos), es decir:

LISTA_HOJA2 equivale a $Hoja2.$A$2:$A$22

Primero seleccionamos en la hoja correspondiente el rango de celdas que contienen la lista de los NIF, A2:A22, y, segundo, escribimos en el “Cuadro de nombre” el nombre que le vayamos a asignar, en este caso LISTA_HOJA2. Es más fácil recordar este nombre que el rango propiamente dicho ($Hoja2.$A$2:$A$22).

En el menú “Hoja > Intervalos y expresiones con nombre > Gestionar…” podemos añadir, eliminar y modificar los nombres que hayamos creado.

Paso 2: Comparar los NIF de la “Hoja1” con los de la “Hoja2”.

Vamos a usar la función COINCIDIR de esta forma:

COINCIDIR(CeldaABuscar; CeldasDondeBuscar; 0)

En una columna vacía de la “Hoja1”, en este caso la E, es donde vamos a escribir la fórmula de búsqueda (ver Imagen 11):

Celda E2 =COINCIDIR(A2;LISTA_HOJA_2;0)

Esta función nos devolverá la posición relativa (1, 2, 3, …) del NIF buscado en el rango de búsqueda especificado (LISTA_HOJA_2 ). El tercer parámetro es cero para indicar que la búsqueda debe ser exacta. Para más información sobre esta función debemos visitar la Ayuda de Calc.

Y, en el caso de no encontrar el dato buscado, la función COINCIDIR devuelve el valor de error “#N/D” (No Disponible), es decir, los valores duplicados aparecerán con un número y los que no con el valor de error citado (ver Imagen 12).

Pero ¿y si tenemos que comparar por el valor de dos o más campos? Entonces debemos crear un campo índice (en una columna vacía) que sea la unión de esos campos y hacer la búsqueda, tal y como se ha comentado en los párrafos anteriores, por ese campo índice (ver Imagen 13).

Fórmula en la celda C2 de la “Hoja3”:

=TEXTO(A2;”000″) & “-” & TEXTO(B2;”0000″)

Fórmula en la celda D2 de la “Hoja3”:

=COINCIDIR(C2;LISTA_HOJA_4;0)

Si preferimos usar la función BUSCARV en lugar de COINCIDIR entonces escribiríamos:

Celda E2 de la “Hoja1” =BUSCARV(A2;LISTA_HOJA_2;1;0)

Celda D2 de la “Hoja3” =BUSCARV(C2;LISTA_HOJA_4;1;0)

BUSCARV no devuelve la posición relativa (1, 2, 3, …) del NIF buscado como hace la función COINCIDIR, sino que devolverá ese mismo NIF en caso de encontrarlo. En caso contrario, la función BUSCARV devolverá el valor de error “#N/D” (No Disponible).

También podemos usar un filtro (ver Imagen 14) si lo consideramos conveniente.

Este artículo se ha escrito utilizando LibreOffice Writer, Calc e Impress versión 7.1.8 para entornos corporativos.

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.