Existe en Calc una categoría de funciones denominada funciones de hoja de cálculo, que ofrecen un importante conjunto de funciones para obtener referencias e información sobre la ubicación y contenido de celdas, rangos, filas o columnas dentro de la hoja de cálculo.
En otros programas de hoja de cálculo, esta categoría de funciones se denomina como funciones de búsqueda y referencia.
Función BUSCARV() y BUSCARH()
La función BUSCARV (V por Vertical) nos permite buscar un valor en la primera columna de una tabla de datos y obtener el contenido de otra columna de la tabla en la misma fila del valor buscado.
La función BUSCARH (H por horizontal) funciona de forma análoga pero en tablas orientadas en filas en vez de en columnas. Es decir, se hace la búsqueda en la primera fila y se obtiene el valor de otra fila en la misma columna.
En ambos casos, podemos elegir entre que la búsqueda sea exacta o aproximada, en cuyo caso se exige que esté ordenada la primera columna o fila, según se trate de BUSCARV o BUSCARH.
Vamos a detallar el funcionamiento de BUSCARV, teniendo presente que la información ofrecida es perfectamente aplicable a BUSCARH siempre que tengamos los datos en filas.
BUSCARV busca un valor específico en la primera columna y devuelve el valor correspondiente a la misma fila desde otra columna.
Sintaxis básica:
=BUSCARV(Dato_buscado;Rango_donde_buscar;Número_columna;Búsqueda_aproximada)
donde:
- Dato_buscado es el valor que se desea localizar en la primera columna del Rango_donde_buscar.
- Rango_donde_buscar es la tabla de datos en cuya primera columna se efectuará la búsqueda.
- Número_columna es el número de columna del rango que contiene el valor que se desea obtener (es el número de columna dentro del rango, indiferentemente de que se trate de la columna A, la Z o cualquier otra)
- Búsqueda_aproximada es un argumento opcional; si se omite o es 1 o VERDADERO, la búsqueda será aproximada y se ubicará la fila que contenga el mayor valor menor al buscado, lo cual exige la la tabla esté ordenada por su primera columna; si se especifica 0 o FALSO la búsqueda será exacta y si no tiene éxito devolverá el valor de error #N/D (No disponible),
Hemos comentado al tratar la función SI que un exceso de funciones SI anidadas podía reemplazarse por una función BUSCARV. Veamos un ejemplo del caso.
Se trata de obtener las calificaciones de unos alumnos en función de su nota final. En el rango E7:F10 se ha establecido una tabla con los diferentes tramos de calificación (se han añadido títulos para facilitar su comprensión pero no son relevantes a efectos de la fórmula). He aquí la solución,
Dada la complejidad de la fórmula vamos a detallar su comportamiento. Con el primer SI comprobamos si la nota es inferior a 5, en cuyo caso se determina que es un Suspenso; si no, en el segundo SI comprobamos si la nota es inferior a 7, en cuyo caso se trata de un Aprobado; si no se cumple ninguna de las anteriores, todavía hay dos posibilidades, que sea Notable o Excelente, por lo que volvemos a utilizar un tercer SI que comprueba si la nota es menor a 9, en cuyo caso se trata de un Notable; como ya no quedan más posibilidades, no hace falta efectuar ninguna comprobación más, pues si no es Suspenso, ni Aprobado, ni Notable, tan sólo puede ser Excelente.
La fórmula resultaría más legible si se hubiesen escrito directamente las notas de corte y calificaciones dentro de la misma, pero si en un momento determinado se decide que en vez de calificar como Suspenso se califique como Insuficiente, o que la nota de corte del aprobado sea el 6 en vez del 5, no tendríamos más remedio que reescribir la fórmula. Tal como lo hemos hecho, cambiando los valores en la tabla de tramos de calificación, la fórmula seguirá funcionando a la perfección.
Es comprensible pensar que es muy fácil cometer errores al escribir una fórmula como ésta, y que además resultará difícil localizarlos.
Veamos como resolver el mismo problema con la función BUSCARV.
Podemos comprobar la simplicidad y elegancia de esta otra solución. BUSCARV busca la nota dentro de la primera columna del rango con los tramos de calificación y devuelve el contenido de la columna 2 del rango. Al tratarse de una búsqueda aproximada (hemos omitido el cuarto argumento), si no encuentra la nota, se queda en el tramo anterior; por ejemplo buscando la nota 3, como no la encuentra se queda en el tramo del 0, si busca un 8 se queda en el tramo del 7, etc.
Para este caso nos ha sido muy útil el uso de la búsqueda aproximada. También sería el caso de crear un modelo de hoja de cálculo para, por ejemplo, la declaración de la renta, cuya base liquidable está organizada por tramos.
Pero no siempre será lo más apropiado. Imagínemos un sistema de facturación donde hemos de emitir una factura al cliente 300 y al no encontrarlo, Calc le emitiese la factura al cliente 290. Para casos así, necesitaremos el uso del cuarto argumento para que el comportamiento de BUSCARV sea el correspondiente al de una búsqueda exacta.
Veamos el siguiente ejemplo. Se trata de una pequeña lista de datos y un formulario. En el formulario, al introducir un Id de contacto en la celda sombreada en amarillo, se debe mostrar correctamente el Nombre, Ciudad e Importe correspondiente a ese Id.
En principio, usando el BUSCARV como habíamos hecho hasta ahora, funciona aparentemente bien.
Pero si introducimos un Id inexistente, nos mostrará un dato incorrecto; en este caso al no encontrar el Id 8, nos muestra los datos correspondientes al Id 7.
Modifiquemos la fórmula añadiendo un cuarto argumento 0 a la función BUSCARV y nos mostrará el error #N/D advirtiendo de la inexistencia del Id 8.
Naturalmente, si el Id introducido es correcto, la fórmula mostrará los datos correctos.
Idea
El caso del ejemplo funcionará correctamente aunque los datos estén en un libro diferente, ¡incluso cerrado!.
Podemos comprobarlo realizando los siguientes pasos:
- Reproduzciremos en un documento nuevo el ejemplo mostrado en una hoja denominada DATOS.
- Moveremos los datos del formulario a una hoja diferente dentro del mismo documento denominada FORMULARIO.
- Moveremos la hoja FORMULARIO a un libro nuevo.
- Guardaremos el documento con la hoja DATOS y lo cerraremos.
- En el libro con la hoja FORMULARIO, probaremos diferentes valores para la celda con el Id.
Observaremos que la fórmula sigue funcionando a la perfección.
Hemos visto que BUSCARV se busca en la primera columna (o en la primera fila si se trata de BUSCARH), pero ¿qué ocurre si el dato que necesitamos buscar está en otra columna?
Por ejemplo, si en el ejemplo anterior, hemos de buscar un Nombre para encontrar su Id.
Con lo que conocemos hasta el momento no tendríamos más remedio que modificar la tabla cambiando el orden de las columnas.
Pero eso no será preciso gracias a otras funciones de hoja de cálculo como ÍNDICE y COINCIDIR. Vamos a explorarlas.
Función ÍNDICE()
ÍNDICE devuelve una referencia a una celda o un intervalo dentro de un rango en base a su posición de fila o columna.
Sintaxis básica:
=ÍNDICE(Rango;Número_fila;Número_columna;Número_área)
donde
- Rango es el rango de referencia, que puede ser múltiple, es decir, compuesto por varias áreas, en cuyo caso deberá ir entre paréntesis.
- Número_fila es un argumento opcional que indica el número de fila dentro del rango. Si cero, devuelve todas las filas.
- Número_columna es un argumento opcional que indica el número de columna dentro del rango. Si cero, devuelve todas las columnas.
- Número_área es un argumento opcional que indica el número de área si el Rango es múltiple.
En el siguiente ejemplo, se muestra el contenido de la segunda fila y tercera columna del rango de datos.
Si el rango es una sola fila o columna, el uso es trivial. En el ejemplo siguiente, se muestra el cuarto elemento de la lista de nombres.
Un uso más sofisticado, dado que la función ÍNDICE realmente devuelve una referencia, sería calcular el promedio de la cuarta columna del rango.
Función COINCIDIR()
A diferencia de la anterior, COINCIDIR nos informa de la posición de un dato dentro de un rango (fila o columna).
COINCIDIR devuelve la posición relativa dentro de un rango formado por una sola fila o columna.
Sintaxis básica:
=COINCIDIR(Dato_buscado;Rango;Tipo)
donde
- Dato_buscado es el dato que se busca. Admite expresiones regulares si el Tipo es cero.
- Rango es la fila o columna donde se desea encontrar el Dato_buscado.
- Tipo es un argumento opcional que especifica el tipo de búsqueda:
- Omitido o 1: se considera que Rango está ordenado ascendentemente y la búsqueda será aproximada, devolviendo la posición del mayor elemento menor o igual al buscado.
- 0: efectuará una búsqueda exacta, admitiendo expresiones regulares en el Dato_buscado.
- -1: asume que Rango está ordenado descendentemente y la búsqueda será aproximada, devolviendo la posición del último elemento mayor o igual al buscado.
En el siguiente ejemplo se busca la posición de Carmen dentro de la lista de nombres. Obsérvese el uso de expresiones regulares en el dato buscado.
Ahora, combinando el uso de COINCIDIR y ÍNDICE podemos superar la limitación de BUSCARV que restringe la búsqueda a la primera columna.
En el ejemplo, buscamos el Nombre Pep.* y obtenemos el Id correspondiente.
Mediante COINCIDIR se encuentra la posición relativa del nombre buscado dentro de la lista de nombres, y el resultado es el argumento que se pasa a ÍNDICE para que devuelva el Id correspondiente.
Hemos visto la "magia" que podemos hacer combinando las diferentes funciones que Calc pone a nuestra disposición.
Una vez más recomendamos una exploración en profundidad de otras funciones que no hemos tratado en estos apartados.
Todo el tiempo invertido en ello, será revertido con creces cuando lo apliquemos a nuestro trabajo.