Por José Francisco Martínez Jiménez. Servicio de Gestión Contable. Intervención General de la Junta de Andalucía, Consejería de Economía, Hacienda y Administración Pública
Con el presente artículo, seguimos ofreciendo nuestros consejos prácticos sobre el uso de la tecnología a través de un ejemplo sobre como aprovechar mejor el potencial de la hoja de cálculo, una herramienta informática que habitualmente empleamos en nuestro trabajo.
Tenemos una tabla (ver imagen siguiente) donde figura una tasa a aplicar de acuerdo con los ingresos anuales y el número de hijos de una unidad familiar.
Vamos a ver cómo con las funciones BUSCARV() y COINCIDIR() de LibreOffice CALC se puede automatizar la búsqueda de la tasa correspondiente a aplicar de acuerdo con las variables “ingresos anuales” y “número de hijos”.
Unas consideraciones iniciales:
- Las celdas “B4:B10” contienen los ingresos anuales teniendo en cuenta que cada una de esas cantidades representa una horquilla o rango de ingresos, por ejemplo, la celda “B4” (1), que contiene 0,00€, representa desde 0,00€ a 11.999,99€, es decir, hasta el importe inferior en 0,01€ de la siguiente celda; la celda “B5” (2), que contiene 12.000,00€, representa desde 12.000,00€ a 17.999,99€; y así sucesivamente hasta llegar a la celda “B10” (3) que representa desde 55.000,00€ en adelante.
- Las celdas “C3:F3” contienen el número de hijos representando los valores de la misma manera que los ingresos explicados en el párrafo anterior.
Explicaremos ahora las funciones BUSCARV() y COINCIDIR() antes de usarlas en nuestra hoja de cálculo.
1. BUSCARV()
Esta función la utilizamos cuando necesitamos buscar elementos/valores en una tabla, matriz o rango de celdas.
La sintaxis de esta función, es decir, la forma en que debemos escribirla es:
BUSCARV(qué_buscar; dónde_buscar; columna_a_devolver; coincidencia_aprox_o_exacta)
Los parámetros que necesita esta función son:
- qué_buscar: es el valor que estoy buscando en la tabla/matriz/rango de celdas. Tiene que coincidir con algún valor situado en la PRIMERA columna de la tabla. Puede ser un valor tecleado tal cual o una dirección de una celda.
- dónde_buscar: es un rango o área de celdas de N filas por M columnas (para no complicar la explicación dejaremos las matrices para otro artículo). Normalmente se le asigna un nombre significativo para recordarlo mejor y porque, al asignarle un nombre, el rango de celdas se convierte en un rango de referencia absoluta y así se evitan problemas al copiar y pegar la fórmula. En nuestro ejemplo esa tabla donde busca está formada por el rango de celdas “B4:F10”.
- columna_a_devolver: una vez encontrado el dato que busco me devuelve el valor situado en la MISMA fila donde ha sido encontrado, pero en la COLUMNA que indica este parámetro, teniendo en cuenta siempre que la primera columna del rango es la 1.
- coincidencia_aprox_o_exacta: Este parámetro es opcional, es decir, puede no escribirse, pero en el caso de no aparecer CALC asume que el valor pasado es VERDADERO. Ahora bien, ¿qué diferencia hay entre aproximada (ordenado) y exacta (no ordenado), o entre verdadero y falso? Un valor FALSO, o 0 (cero), le indica que queremos que encuentre el valor EXACTO y, en caso de no encontrarlo, nos devuelva el error #N/D (no disponible, o #N/A dependiendo de la versión de CALC). Con la función ESNOD() se puede atrapar ese error y tratarlo en consecuencia. Un valor VERDADERO, o 1 (uno), le indica que si encuentra el valor exacto lo devuelva pero, en caso de no encontrarlo, que devuelva el valor correspondiente a aquel que no lo sobrepase, es decir, el valor que corresponde a la horquilla de valores deseada.
2. COINCIDIR()
Esta función devuelve la posición relativa de un elemento de un rango de celdas (fila o columa) que coincide con el valor especificado. La primera celda del rango es la posición 1, la segunda es la posición 2 y así sucesivamente.
La sintaxis de esta función es:
COINCIDIR(qué_buscar; dónde_buscar; coincidencia_aprox_o_exacta)
Los parámetros que necesita esta función son:
- qué_buscar: es el valor que estoy buscando en un rango de celdas (fila o columa). Puede ser un valor tecleado tal cual o una dirección de una celda.
- dónde_buscar: es un rango de celdas de N filas por una columna, o bien, de una fila por N También se le puede asignar a este rango un nombre significativo igual que al mismo parámetro de BUSCARV(). En nuestro ejemplo ese rango de celdas donde busca está formada por las celdas “C3:F3”.
- coincidencia_aprox_o_exacta: Este parámetro funciona de la misma manera que el mismo parámetro de BUSCARV().
3. Aplicando las funciones
Por ejemplo (ver imagen siguiente), si en la celda “B13” tecleamos los ingresos a buscar, en la celda “B14” el número de hijos y en “B15” escribimos la fórmula:
=BUSCARV(B13;B4:F10;1+COINCIDIR(B14;C3:F3))
O usando referencias absolutas en el caso de querer copiar la fórmula a otra celda pero sin modificar los rangos de celdas donde buscar:
=BUSCARV(B13;$B$4:$F$10;1+COINCIDIR(B14;$C$3:$F$3))
El resultado que nos muestra, 2%, corresponde a la celda “E7” (fila 7 columna E). Veamos cómo lo ha calculado:
- Como a la función BUSCARV() no le hemos indicado el cuarto parámetro, es decir, el de la coincidencia_aprox_o_exacta, devuelve el valor correspondiente, comparando los ingresos de las celdas “B4:B10”, a aquel que no supere los 28.534,25€ de ingresos y el valor que no lo supera corresponde a 25.000,00€ que está situado en la fila 7 (rango de ingresos de 25.000,00€ a 34.999,99€).
- Como a la función COINCIDIR() no le hemos indicado el tercer parámetro, es decir, el de la coincidencia_aprox_o_exacta, devuelve la posición relativa, en el rango “C3:F3”, del valor correspondiente a aquel que no supere los 4 hijos, es decir, devuelve el valor 3 que es la posición relativa en el rango “C3:F3” correpondiente a 4 hijos (columna “E”). Se le suma “1” para acceder a la columna correcta de la tabla de búsqueda “B4:F10”.
Estos pasos se pueden adaptar para resolver otros problemas parecidos.
Espero que esta información os pueda ser útil en vuestro trabajo diario.
Saludos cordiales.
