José Francisco Martínez Jiménez, del Servicio de Gestión Contable de la Intervención General de la Junta de Andalucía, perteneciente a la Consejería de Hacienda, Industria y Energía, nos vuelve a enviar un artículo muy interesante sobre LibreOffice Calc, en el que incluye tres casos prácticos:
Caso práctico n.º 1: Búsqueda de derecha a izquierda en una tabla.
Gracias a la función BUSCARV( ), que ya tuvimos oportunidad de ver en el artículo LibreOffice Calc: búsqueda en una tabla con dos variables del número 2 (julio-octubre 2018) de esta revista, podemos realizar búsquedas de izquierda a derecha; por ejemplo, dada la tabla de la imagen 1, conociendo el valor del DNI podemos saber el valor del resto de campos de la siguiente manera (imagen 2):
- Celda J2 (APELLIDOS Y NOMBRE): =BUSCARV(I2;$A$2:$G$585;2;0)
- Celda K2 (TOTAL IMPORTE): =BUSCARV(I2;$A$2:$G$585;6;0)
- Celda L2 (PROVINCIA): =BUSCARV(I2;$A$2:$G$585;7;0)
La columna DNI es la número 1 del rango de búsqueda y la PROVINCIA es la columna número 7. El último parámetro de la función BUSCARV es 0 (cero o FALSO( )) para indicar que debe hacer una búsqueda secuencial exacta (ver el caso práctico n.º 2), no como en el artículo citado anteriormente que era una búsqueda por rangos ordenados de importes (cuarto parámetro igual a 1, VERDADERO( ), o bien, no existe).
(El valor #N/D significa que el DNI buscado no existe en la tabla, es decir, está No Disponible).
Esta búsqueda se realiza de izquierda a derecha ya que buscamos por DNI (BUSCARV siempre busca por la primera columna de la tabla), pero ¿y si el dato que tenemos para buscar es el CDG CLIENTE (tercera columna)? Entonces tenemos que hacer primero una búsqueda de derecha a izquierda, antes de hacerla de izquierda a derecha, con la ayuda de las funciones BUSCARV( ), INDICE( ) y COINCIDIR( ), esta última también vista en el mismo artículo citado del n.º 2 de la revista.
La función INDICE( ), en la manera que la vamos a usar ahora, tiene esta sintaxis:
=INDICE(rango_de_celdas; posición_relativa_en_el_rango)
Devuelve el valor de la celda perteneciente al rango_de_celdas cuya posición en el rango sea el especificado por la posición_relativa_en_el_rango. Lo veremos mejor con un ejemplo:
En la celda C5 figura la fórmula:
=INDICE($A$2:$A$10;4)
Devuelve el valor “89352794P” correspon-diente a la celda A5 que es la que ocupa la posición 4 relativa al rango especificado de A2:A10. La celda A2 sería la número 1 y la celda A10 la número 9.
El signo $ (dólar) delante de una fila o columna convierte la referencia en absoluta, es decir, no cambia al copiarla a otra celda.
Aplicando lo anterior, si queremos que nos devuelva el DNI correspondiente al CDG CLIENTE que buscamos escribiremos la fórmula de la siguiente manera (imagen 4):
¿Qué devuelve COINCIDIR(H2; $C$2:$C$10; 0)? Devuelve un número (4) que es la posición relativa en el rango especificado (C2:C10) del valor buscado (H2), realizando una búsqueda secuencial exacta (0, FALSO( )). Si devolviera el valor #N/D significaría que el CDG CLIENTE buscado no existe en el rango especificado.
Ya solo nos queda modificar las fórmulas de la imagen 2 para obtener los valores adecuados conociendo el CDG CLIENTE en lugar del DNI (imagen 5):
- Celda J2 (DNI):
=BUSCARV(INDICE($A$2:$A$585;COINCIDIR(I2;$C$2:$C$585;0));$A$2:$G$585;1;0)
- Celda K2 (APELLIDOS Y NOMBRE)
=BUSCARV(INDICE($A$2:$A$585;COINCIDIR(I2;$C$2:$C$585;0));$A$2:$G$585;2;0)
- Celda L2 (TOTAL IMPORTE)
=BUSCARV(INDICE($A$2:$A$585;COINCIDIR(I2;$C$2:$C$585;0));$A$2:$G$585;6;0)
- Celda M2 (PROVINCIA)
=BUSCARV(INDICE($A$2:$A$585;COINCIDIR(I2;$C$2:$C$585;0));$A$2:$G$585;7;0)
Hay que tener especial cuidado con el valor #N/D ya que puede darse cuando el dato que estemos buscando esté guardado como texto y lo busquemos como número o viceversa, aunque parezca lo mismo a nuestros ojos para el ordenador no es igual un número que ese mismo número en formato texto. Nos damos cuenta al mirar la celda, si es un número aparecerá alineado a la derecha, mientras que si es un texto aparecerá alineado a la izquierda (imagen 6).
Podríamos pensar, «¡qué forma de complicarse la vida!, se mueve la columna de CDG CLIENTE a la primera posición del rango y se usa BUSCARV sin necesidad de INDICE ni COINCIDIR». Desde luego, siempre que sea posible, es la solución más fácil pero «¿y si el archivo nos lo ha enviado otra Unidad Administrativa y está protegido con contraseña, o bien, no se nos permite modificar el orden de las columnas?». Siempre es interesante conocer otros caminos …
Caso práctico n.º 2: Cómo agilizar las búsquedas en tablas de cientos o miles de registros.
Es posible, dependiendo del trabajo a desempeñar, que necesitemos buscar cientos, miles o decenas de miles de registros en otra tabla (que puede estar en otra hoja) con, también, cientos, miles o decenas de miles de registros (ya sabéis, en una hoja de cálculo los registros son las filas y los campos son las columnas). La búsqueda tiene que ser una búsqueda exacta, es decir, el campo que estamos buscando (casi el 100% de las veces es algún campo de identificación única personal: DNI, NIF, código acreedor, …) debe coincidir exactamente con el de la tabla donde se busca.
Hasta ahora usábamos la función:
=BUSCARV(celda_a_buscar; rango_donde_buscar; columna_a_devolver; FALSO())
El último parámetro FALSO( ), también se puede sustituir por 0 (cero), le indica a CALC que haga una búsqueda secuencial exacta por todos y cada uno de los registros de la tabla donde busca hasta encontrarlo. Y, en el caso de no encontrarlo, devuelve #N/D (No Disponible).
¿Qué significa eso de búsqueda secuencial exacta? Significa que BUSCARV tiene que hacer un número indefinido de comparaciones, una por una, hasta encontrar el dato que busca y puede que llegue al final y no encuentre tampoco el valor. Si la tabla tiene 10.000 registros y, por ejemplo, no figura el dato que buscamos entonces habrá hecho 10.000 comparaciones. Eso consume mucho tiempo y, en mi caso concreto, el tiempo que tarda en una búsqueda de 40.000 registros en otra tabla de 50.000 registros es de más de 5 minutos. En otro ordenador más lento he tenido que esperar hasta ¡20 minutos! en unas tablas con más 70.000 registros.
Pues bien, podemos agilizar la búsqueda usando la fórmula:
=SI(celda_a_buscar = BUSCARV(celda_a_buscar; rango_donde_buscar; 1; VERDADERO( )); BUSCARV(celda_a_buscar; rango_donde_buscar; columna_a_devolver; VERDADERO( )); NOD())
La comparación
celda_a_buscar = BUSCARV(celda_a_buscar; rango_donde_buscar; 1; VERDADERO( ))
es la que nos indica si ha encontrado una coincidencia exacta. Si es así entonces nos devuelve la columna que necesitamos en
BUSCARV(celda_a_buscar; rango_donde_buscar; columna_a_devolver; VERDADERO( ))
Y si no coinciden entonces nos devuelve
NOD()
que es el valor #N/D. En lugar de NOD( ) podríamos escribir cualquier otro valor que nos interesara.
El último parámetro, VERDADERO( ), que también puede no existir o sustituirse por un 1 (uno), es el que indica a CALC que haga una búsqueda binaria en la tabla donde busca.
¿Qué significa eso de búsqueda binaria (también llamada logarítmica)? Supongamos que queremos buscar una palabra en el diccionario. No se nos ocurre empezar por la primera palabra de la letra A, eso sería una búsqueda secuencial, sino que abrimos el diccionario por la mitad aproximadamente y comprobamos la primera palabra de la página izquierda; si no es la que buscamos (lo más probable) ya sabemos que la palabra, o bien se encuentra en la primera mitad, o bien en la segunda, con lo cual nos hemos quitado el tener que buscar en una mitad del diccionario. En la mitad que debe estar volvemos a hacer lo mismo. Cada vez reducimos a la mitad, o lo que es lo mismo, dividimos entre 2 hasta encontrar o no el valor que buscamos.
Para que esta búsqueda devuelva el resultado correcto debe estar ordenada ascendentemente, igual que un diccionario. ¡Muy importante! Si la tabla no está ordenada ascendentemente por el campo (columna) a buscar, dará resultados extraños y/o erróneos.
En la imagen 7 podemos ver el número máximo de comparaciones necesarias hasta encontrar o no el registro que buscamos dependiendo del número de registros de nuestra tabla.
Obviamente nuestro ordenador tarda muchísimo menos tiempo en hacer 14 comparaciones que 10.000. En el caso concreto citado anteriormente tarda 60 veces menos tiempo.
De algo más de 5 minutos pasó a menos de 5 segundos, aproximadamente (las comparaciones son odiosas).
En la celda E2 figura la fórmula (imagen 8):
=SI(D2=BUSCARV(D2;$A$2:$B$75000;1;VERDADERO());BUSCARV(D2;$A$2:$B$75000;2;VERDADERO());NOD())
Como podemos observar la tabla formada por las columnas A y B está ordenada ascendentemente por la columna A.
Caso práctico n.º 3: Control de plazos en días naturales.
Supongamos que existe un impuesto que a partir de una determinada fecha del hecho causante comienzan a contar unos plazos de:
- presentación voluntaria: por ejemplo, seis meses desde el día siguiente a la fecha del hecho causante,
- presentación fuera de plazo: fechas posteriores a las de presentación voluntaria y anteriores a la de prescripción y,
- prescripción: por ejemplo, cuatro años desde el día siguiente a la fecha fin de la presentación voluntaria.
Sabiendo la fecha del hecho causante, ¿cómo podemos calcular las otras fechas?
Usaremos las siguientes funciones de LibreOffice Calc, además de Datos → Validez que ya vimos en el artículo LibreOffice Calc: Validación de Datos del número 7 de esta revista:
- FECHA(año; mes; día): Calcula una fecha, especificada por año (1583 – 9957), mes (1 – 12) y día (1 – 31). Todos ellos números enteros. Datos extraídos de la Ayuda de LibreOffice Calc.
- AÑO(fecha): Devuelve el año para el valor de fecha especificado (número entero).
- MES(fecha): Devuelve el mes para el valor de fecha especificado. El mes se devuelve como un entero entre 1 y 12.
- DIA(fecha): Devuelve el día para el valor de fecha especificado. El día se devuelve como un entero entre 1 y 31.
- HOY( ): Devuelve la fecha actual del sistema. El valor se actualiza cuando se vuelve a abrir el archivo o se modifican los valores de éste.
- O( ): Devuelve VERDADERO si al menos uno de los argumentos (expresiones lógicas o comparaciones que pueden ser verdaderas o falsas) es VERDADERO. Esta función devuelve el valor FALSO si todos los argumentos tienen el valor lógico FALSO.
- ESBLANCO(celda): Devuelve VERDADERO si la celda está vacía.
- ESNOD(expresión): Devuelve VERDADERO si el resultado de evaluar expresión es el valor #N/D (valor no disponible).
- BUSCARV( ): Ya vista anteriormente.
En la imagen 9 podemos ver las fórmulas que hemos empleado:
En la celda B2 usamos Datos → Validez para que sólo se admitan fechas consideradas correctas:
Igualmente en la celda B3 usamos Datos → Validez:
Veamos la fórmula de la celda B4 (el resto de fórmulas no son complicadas):
=SI(O(ESBLANCO(B2);ESBLANCO(B3));””;SI(ESNOD(BUSCARV(B3;$E$2:$F$4;2;1));
“Fechas incoherentes”;BUSCARV(B3;$E$2:$F$4;2;1)))
Traduciéndola al lenguaje natural:
Si en el caso de que alguna, o las dos celdas B2 / B3 está vacía entonces no se muestra nada (“” se denomina cadena vacía); ahora bien, si están rellenas entonces en el caso de dar error al buscar la fecha de presentación B3 en la tabla de rangos de fechas (E2:F4) se mostrará el texto “Fechas incoherentes” (quiere decir que la fecha de presentación es anterior a la fecha del hecho causante) y si no da error en la búsqueda, es decir, B3 entra dentro de algunos de estos rangos de fechas (02/01/2020 – 02/07/2020: en plazo / 03/07/2020 – 03/07/2024: fuera de plazo / 04/07/2024 en adelante: prescrita) se mostrará la situación de la solicitud.
