Formulas Condicionales, Búsqueda y Referencia que deberías Conocer

Excel es sin duda. Una de las herramientas más predilectas Utilizadas por muchas empresas para la generación de reportes Creación de facturas Entre muchas otras funcionalidades para el desarrollo de sus propias actividades. En el siguiente artículo veremos algunas fórmulas y funciones que nos ayudarán a crear hojas de cálculo para la realización de cálculos y condiciones automáticos.

Antes de ver algunas de estas fórmulas y funciones, vamos a definir que son estas, empezando que una fórmula es un código que introducimos a una celda de Excel para que realice un cálculo y nos entregue un resultado; mientras que una función es una operación preestablecida por Excel que realiza un cálculo sobre uno o más valores determinados en un orden.

Función buscarv

Una de las formulas más usadas de las hojas de cálculo, ya que con esta podemos encontrar valores o elementos en una tabla o en un rango de datos, con ella no solo podemos hacer consultas en diferentes hojas del mismo libro, sino que también podemos buscar estos elementos en diferentes libros. Su aplicación es la siguiente:

                =BUSCARV(celda o valor o texto a buscar, matriz o rango de datos donde se encuentran los valores a buscar, el número de la columna dentro de la matriz donde se encuentra el valor que se desea devolver, coincidencia del valor buscado)

Ejemplo:

Se tienen las ventas de los puntos de venta de una cadena, en ellas se tienen los códigos de cada uno y se desea traer el nombre de estos. En una matriz que se encuentra en el mismo libro se encuentran los nombres con su respectivo código así:

Para traer los valores en nuestra hoja utilizaremos la formula de la siguiente manera:

=BUSCARV(A2;Datos!$A$2:$D$11;2;0). Donde A2 contiene el valor que se desea buscar; Datos!$A$2:$D$11 es el rango del valor; 2 hace referencia a la columna que contiene el valor a traer y por último 0 representa la coincidencia que deseamos en este caso debe ser exacta.

Es importante entender que el signo pesos $ se utiliza para fijar las filas y columnas, esto quiere decir que si copiamos la formula en celdas adyacentes el rango de la matriz no cambiará.

Función coincidir

Con esta formula podemos traer la posición relativa dentro de una matriz con la coincidencia exacta, es decir es decir que, si tenemos en una fila o columna los siguientes datos 12, 30, 50, y deseamos traer la posición del valor 50 al usar la formula nos arrojara como resultado 3.

=COINCIDIR(50,A1:A3,0) Donde 50 es el valor buscado, A1:A3 es el rango de consulta y 0 es el tipo de coincidencia. En este caso como el valor buscado se encuentra en la tercera posición nuestra hoja de calculo nos arrojará como resultado 3

Función si.error

Si.ERROR es una formula que nos ayuda devolver un valor diferente a los errores que puedan salir en la ejecución de fórmulas de cálculos o valores no encontrados en fórmulas de búsqueda y referencia. La hoja de calculo nos muestra los errores de la siguiente manera #DIV/0!, #VALOR o #N/A si en nuestras hojas tenemos estos resultados y creamos una tabla dinámica, los cálculos automáticos que esta realiza nos mostrará este mismo error, con el uso de esta fórmula lo podemos eliminar.

Uso: SI.ERROR(operación, valor a reemplazar si hay error)

Ejemplo: Tenemos datos así 0, 2, 3 y deseamos dividir el valor máximo (3) sobre el valor mínimo (0); está operación matemática nos arrojaría error ya que todo número dividido en cero es indefinido como se muestra en la imagen

Para corregir este error aplicaremos la formula de la siguiente manera:

Como podemos observa luego de realizar la operación, ingresamos el valor cero (0), haciendo referencia que si la operación tiene como resultado error nos dará este valor.

Función si

Si es una de las formulas más usadas ya que con ella se puede devolver un resultado si se cumple o no una condición

Uso: SI(Prueba lógica, valor si se cumple la prueba lógica, valor si no se cumple la prueba lógica)

Ejemplo:

Se tienen las calificaciones de 5 estudiantes y se desea conocer quienes aprobaron la materia, si su calificación es mayor o igual a 3 aprobó si no reprobó. Para ellos usaremos la formula de la siguiente manera:

Donde B2>=3 es nuestra prueba lógica que quiere decir que si el valor que se encuentra en la celda B2 es mayor o igual a 3 entonces, “Aprobó” este nuestro valor verdadero si se cumple la condición, “Reprobó” hace referencia al valor falso es decir el valor si no se cumple la condición)

Para este ejercicio es importante tener en cuenta que los valores Aprobó (Verdadero) como Reprobó deben ir entre comillas por tratarse de caracteres alfabéticos y no numéricos.

Formulas Anidadas

Cuando se inserta una función como uno de los argumentos dentro de otra función en Excel, se le conoce como funciones de anidamiento. Por ejemplo anidaremos tres de las funciones explicadas anteriormente SI.ERROR, BUSCARV y COINCIDIR. Un dato que debemos tener en cuenta es que en las hojas de calculo de Excel solo podremos anidar hasta 64 funciones.

Ejemplo: Se quiere conocer la calificación de Natalia Contreras en las materias de Matemáticas y Ciencias Sociales para ellos realizaremos la consulta en la matriz Calificaciones

Para traer el valor de la calificación de cada materia utilizaremos la función anidada de la siguiente forma:

=SI.ERROR(BUSCARV($A$9;$A$2:$E$6;COINCIDIR(C$8;$A$1:$E$1;0);0);”Los resultados de la materia no han sido cargados al sistema”)

En este caso realizamos un cambio para la función BUSCARV y la función COINCIDIR, en la primera el indicador de columna lo cambiamos por COINCIDIR, y esta misma ahora aplicamos como referencia a la celda donde se encuentra el nombre de la materia que se busca y columna que tiene los resultados.

Para este ejemplo la función BUSCARV se convierte en la operación del SI.ERROR; ahora la función COINCIDIR se convierte en el indicador de columna para BUSCARV. Finalmente definimos la observación “Los resultados de la materia no han sido cargados al sistema” como el valor a obtener si en la ejecución de la combinación entre BUSCARV y COINCIDIR resulta como error (Columna D de la imagen). Ente ejemplo Natalia Contreras ya contaba con calificación en la materia de Matemáticas resultado que se trae a la columna “C”, pero en la matriz no existe la materia Ciencias Sociales por lo que el resultado para la columna “D” es el valor que la función reemplazará por el error que se vería.

Función concatenar

Esta función se usa para la unión de varios elementos (textos) o celdas en uno mismo, cabe resaltar que no podemos seleccionar un rango de celdas para unirlas, se deben seleccionar una a la vez. En ocasiones se suele utilizar para crear una llave única dentro de una base de datos sobre la cual luego se aplicarán funciones de búsqueda y referencia o funciones condicionales, como lo veremos en los siguientes ejemplos:

Ejemplo 1: Union de textos en varias celdas

Como vemos en la imagen tenemos un texto que se encuentra en el rango A1 hasta K1, concatenaremos el texto aplicando la formula de la siguiente manera:

=CONCATENAR(A1;” “;B1;” “;C1;” “;D1;” “;E1;” “;F1;” “;G1;” “;H1;” “;I1;” “;J1;” “;K1)

El indicador a cada celda es el texto que estamos uniendo en uno solo, para el ejemplo se agregan comillas mas un espacio para separar las palabras entre dentro del único texto, obteniendo como resultado lo siguiente

Si no aplicamos el espacio veríamos el texto así:

Ejemplo 2:

Ahora veremos un ejemplo del uso de la función para crear una llave única dentro de una base de datos, para ello tenemos una matriz así:

En este caso vemos la necesidad de crear un código único por tienda ya que si se llega a aplicar la función BUSCARV para saber cuantas unidades de Cerveza Aguila Light lata 330 ml se vendieron el día 02 de Septiembre de 2022, nos veremos en aprietos ya que la lógica que aplica la función es la búsqueda de un único valor y no la combinación de varios criterios.

Realizando un pequeño cambio a la matriz insertando una columna en “A” para crear aquí nuestra llave podríamos obtener lo siguiente

Ahora en la Celda A2 insertamos nuestra formula así:

Dejando en primer lugar el código del articulo vendido, en segundo el código de la tienda y en tercer lugar la fecha de venta. Una vez hecho esto podríamos crear una tabla base de consulta como en la siguiente imagen:

En las celdas D12, D13 y D14 encontramos los criterios de nuestra llave; en la celda D16 tenemos nuestra llave y finalmente en la celda D18 aplicamos la función BUSCARV cuyo valor a buscar será la celda D18.

Para finalizar las funciones que vimos en este articulo se pueden utilizar en las versiones de Excel 2010, 2013 y 2019; aunque para la versión 2019 podemos reemplazar la función BUSCARV por la BUSCARX ya que su aplicación y modificación en la lógica de consulta nos ofrece una mayor utilidad.

Autor: Juan David Poveda Avendaño

Editor: Carlos Pinzón Romero

Codigo: UCHIG2-3

Universidad: Universidad Central

Fuentes

CustomGuide, (2022). Funciones Anidadas 2022. CustomGuide. https://www.customguide.com/es/excel/funciones-anidadas#:~:text=Cuando%20se%20inserta%20una%20funci%C3%B3n,l%C3%B3gica%20en%20un%20promedio%20calculado.

Sanz JL. (2020). Lifestyle 2020. El País Economía. https://cincodias.elpais.com/cincodias/2020/03/10/lifestyle/1583838928_923176.html

U ECCI (2022). Universidad ECCI 2022. Excel Intermedio https://www.ecci.edu.co/programas/excel-intermedio/

IE Group – Excel. (15 de Julio de 2020). Como Utilizar la función BUSCARV en EXCEL de una hoja a otra [Video]. YouTube. https://www.youtube.com/watch?v=r6ysxMyAOy8&ab_channel=IEGroup-Excel