Este curso se encuentra descontinuado, si te gustaría que lo retome puedes usar el formulario de contacto que se encuentra en la derecha –>>
Bienvenidos a esta la novena clase del este el curso virtual
El archivo de esta clase acá >> Clase 9 – Búsqueda y referencia – formulas de Excel – Ejercicio
El día de hoy revisaremos estas fórmulas que usamos al buscar alguna información en nuestros libros de Excel, hagamos un listado de las más útiles pero primero tenemos que hablar de un concepto importante la llave primaria
Entendiendo el concepto de llaves primarias
Este tema se habla mucho en la programación, significa que por cada columna hay un identificador único y no puede repetir en dicha columna, por ejemplo en una lista de ciudadanos sería la Cédula de ciudadanía. Cuando voy a crear algún sistema lo primero que uno hace en el desarrollo como tal es la estructura de las tablas que en esto si se parece al Excel, pero luego en términos generales se busca una llave primaria para cada tabla, para poder hacer referencias en informes y para otros procesos.
¿Se pueden crear tablas sin llaves primarias?, claro que si, en Excel la mayoría de las veces ninguna tabla de información tiene un identificador único claro, en la programación pasa lo mismo y cuando no esta claro, por ejemplo en una lista de visitas de clientes, la cédula se repite en cada visita, por lo que ya no sería una llave primaria entonces se usa un ID de visita por ejemplo.
Cuando se van a buscar datos en las tablas de Excel por lo general se habla de BUSCARV y veremos en el siguiente listado las fórmulas de búsqueda y referencia las que considero de mayor utilidad.
- BUSCAR = Buscar un valor en una columna o fila, en algunas versiones no registra el buscar ya que este se ha implementado para que funcione como emergente en versiones anteriores a Excel 2013
- BUSCARV = Busca un valor en la primera columna de una matriz indicada, devuelve el valor en la columna que le digamos, es el más común pero el menos recomendado, ya hablaremos sobre eso
- BUSCARH = Busca un valor en la primera fila de una matriz, es el inverso a BUSCARV y la verdad no es tan útil pero es bueno saber como funciona por si se presenta algo, pero estamos acostumbrados a una tabla convencional que casi nunca se usa
- INDICE=Es como buscar una dirección, le decimos la fila y la columna y nos trae ese dato
- COINCIDIR = Nos indica en que posición de una columna se encuentra un dato.
- FORMULATEXTO = ¿Recuerdas que cuando queríamos escribir una formula sin que se activara utilizábamos el formato TEXTO o escribíamos empezando por la comilla sencilla? (‘), bueno con esta fórmula le indicamos de que celda que tenga otra fórmula nos escriba en formato TEXTO lo que tiene, sin tener que utilizar la comilla o cambiar el formato, de ahora en adelante utilizare esta fórmula para los archivos
- HIPERVINCULO = Esta formula permite mostrar un texto y enviar a alguna parte del documento al darle clic, a esta celda, también podemos enviarlo a otro libro, a otro tipo de archivo, a una carpeta, una pagina web, etc.
Buenos acá vimos la descripción de cada una pero ahora debemos entender otro concepto
Las llaves foráneas
Ya explicamos cuales serían las llaves primarias para una tabla, estas llaves foráneas lo que hacen es hacernos una referencia de una llave primaria de otra tabla y en términos generales se puede repetir en mi tabla por ser foránea. Espera un momento que me perdí, ¿tienes un ejemplo? , claro que si, dijimos que en una tabla de clientes la llave primaria sería la cédula por que no se repite, bueno, ahora tenemos otra tabla una tabla de visitas, su estructura, o sus columnas si lo hacemos en Excel seria algo como ID visita (Llave primaria de esta tabla), ID Cliente (Llave foránea porque vamos a referenciar un cliente que vino, pero un cliente puede venir varias veces), Hora visita, No productos comprados, Tiempo en la tienda.
Entonces si la llave primaria es ID Visita y la foránea es ID cliente podría suceder algo como esto:
| Informe de visitas a nuestra tienda en ortizvivas.com | |
| ID Visita | ID Cliente |
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
El Número de la visita no se repite, pero el cliente vino varias veces, hasta acá esta claro pero que ventajas tendría yo si solo pongo la llave primaría de todas formas le voy a pedir la cédula cada vez que venga, bueno sí pero cada vez que venga el cliente le vas a preguntar, la cédula, los nombres, los teléfonos, el correo, etc. Si solo le preguntas la cédula haces una venta más rápida y no aburres al cliente por dar un ejemplo.
Veamos con ejemplos como funcionan cada una de estas fórmulas que nombramos arriba, primero tenemos 2 tablas la de visitas y la de clientes
Por lo general no se pone una tabla debajo de la otra porque cuando lleguen más visitas de las que hay en el espacio tendremos que estar corriendo la otra tabla, pero para este ejemplo lo haremos así
La tabla de visitas tiene un ID de cliente que es la llave foránea pero también tiene el nombre y la ciudad del cliente, la idea es que no tengamos que estar llenando estos datos cada vez y en programación esto no se hace sino que después lo que se le muestra al usuario es un informe que se ve como acá, pero como estamos con Excel entonces no hay problema y hoy a modo de ejemplo tampoco pues tenemos un informe y una tabla en un solo paso.
Lo primero que pasaría, suponiendo que tienes una tienda física normal, es que algún sensor podría registrar la siguiente información
Pero aún no tienes la información del cliente, supongamos que va a comprar un producto, lo primero que tienes que hacer es buscar si ya existe, pero en este caso como no tenemos ningún cliente registrado, procedemos a solicitarle los datos básicos
Ahora vamos a relacionar la visita con el cliente
Con Buscar
La función buscar nos ofrece 2 opciones una con tres opciones, o parámetros y una con dos, tengamos en cuenta que el parámetro que esta entre corchetes cuadrados no es obligatorio, en este caso vamos a buscar el nombre del cliente con cédula 1, entonces en ese primer parámetro que nos pregunta valor buscado, le vamos a dar B4, le podríamos escribir el 1 literalmente y funcionaria igual, pero la idea de las fórmulas es reutilizarlas, por lo que vamos a seleccionar la celda.
En el segundo parámetro nos pregunta cual es el vector de comparación (entendiendo que un vector en este caso es la columna de los datos), seleccionamos el rango de A15:A20, si lo dejamos hasta ahí nos va a traer el 1, lo cual no es nada útil, entonces el tercer parámetro es que columna quiere que traiga en la imagen se ve que el rango es B15:B20 entonces veremos que nos ha traído el nombre de Pepito Perez
la opción de abajo funciona solo con 2 parámetros, el primero es lo mismo, pero el segundo pregunta en donde, pero si le decimos que la primera columna, nos trae el 1, que de nuevo no sirve y si seleccionamos toda la tabla nos va a traer el último dato, en este caso el email, por lo que no se va a tener en cuenta.
Con BuscarV
Llenamos los datos que faltaban y Pepito se fue, pero en la tarde regresa entonces tenemos otro registro de visita
Vamos a hacerlo con la función BuscarV
El primer parámetro funciona igual, es que dato voy a buscar, el segundo cambia un poco, por que aunque le indicamos donde debe buscar el solo va a buscar en la primera columna de la izquierda que le estamos indicando, es decir A15:A20. El tercero parámetro al igual que en buscar también es de donde va a traer los datos solo que le indicamos con un número que columna es la que queremos en este caso es la 2.
Luego nos pide un parámetro «ordenado» y la gente por lo general lo deja vació y ya, bueno esto es un problema, porque como esto no es una base de datos, se podrían presentar 2 casos, que no exista o que este repetido, repliquemos el cliente 1 con otros datos solo a modo de ejemplo
En la imagen podemos ver que nuestra primera fórmula ya tuvo un problema, trajo el último que encontró y el segundo no tiene problemas, ahora que pasa si vanos a traer uno que no existe
Nuestra primera fórmula vuelve a fallar ya que trajo a uno más cercano, que era el 1 y ahora estamos buscando un 2 que no existe, por lo que empezamos a ver las fallas de la fórmula Buscar, y BuscarV hasta ahora responde bien pero que pasa si ahora voy a buscar el de quien es el numero 9999999, suponiendo que hay una cantidad inmensa de clientes, BuscarV no nos serviría porque solo funciona buscando datos en la primera columna y no permite traer datos de la izquierda.
BuscarH
Este caso es diferente por lo que plantearemos otro tipo de tabla, en la imagen podemos visualizar el uso de buscarH, donde buscamos sobre la primera fila, encontró «Vendedor 2» y luego fue a la 4 fila que era la de marzo, pero este tipo de tablas casi no se usan y no son muy recomendables. Ademas tendría el mismo problema de BuscarV ya que no podría buscar hacia arriba.
FormulaTexto
Bueno esta función se explica sola en la imagen
Indice y Coincidir
Esta combinación es la recomendada del día y a mi entender es la más poderosa que hay, primero coincidir, su uso es bastante sencillo, Suponiendo que en la tabla clientes ya esta todo bien (las cédulas son únicas), en la imagen vemos el uso de la fórmula coincidir
Coincidimos el ID del cliente en el rango A15:A20 y de forma exacta, el 0 indica que el valor que estamos buscando sea si o si el 1 en este caso, también se puede buscar mayor que o menor que, depende de la necesidad. en este caso nos trajo un 1, es decir se encuentra en la 1 posición del rango que le dimos, en la celda de abajo vanos a usar el indice
A indice le estamos diciendo de está área (A15:F20) tráeme de la primera fila la 3 columna, bueno pero como los datos están escritos en literal no sirve para nada, ya sabemos que la 3 columna es la ciudad siempre, pero no siempre va a ser la primera fila, pongamos otro cliente y otra visita de ese cliente
Si revisamos la fórmula está la combinación de Indice y Coincidir de la que hablábamos, se leería algo así, siempre de adentro hacia afuera
COINCIDIR: Del dato de B6 (ID Cliente = 4) tráeme la posición donde la encuentres en el rango A15:A20
INDICE: de la matriz A15:F20 trae la celda con la fila de COINCIDIR y la columna 3 (ciudad)
ya sabemos que nos trajo Lisboa por que estaba en la posición 3-3 por así decirlo fila 3 columna 3, el 4 solo es la cédula del cliente y si es única no importa si esta ordenado o no, cosa que si nos pide BUSCARV por ejemplo.
Recomendaciones al copiar y pegar
La idea de las fórmulas es siempre poder reutilizarlas pero hay que tener en cuenta algunos detalles, por ejemplo si copiamos la ultima fórmula en la celda de la izquierda ¿que pasaría?
Todas las formulas se desplazaron 1 casilla a la izquierda, en el caso de los clientes como ya era la columna A y no hay nada antes pone #REF! que quiere decir que no esta apuntando a ningún lado por lo que el resultado de la fórmula será #REF! también.
Para editar la fórmula le damos F2 y ahora vamos a fijar los rangos, nos paramos sobre A15 y presionamos la tecla F4, nos damos cuenta que queda $A$15, ahora si copiamos la fórmula se mantendrá el A15 que tenemos escrito, se puede fijar solo la columna es decir $A15, pero si copiamos la formula hacia abajo y a la izquierda se va a desplazar hacia abajo y el rango no comenzará en 15 sino en 16, y también se puede A$15, y pasaría lo mismo pero en columnas, como nuestra tabla de clientes es siempre la misma debemos fijar tanto columnas como filas, como sabemos que en la parte de coincidir se movería a la izquierda si no lo fijamos, lo hacemos solo a nivel de columna, para que cuando la copiemos hacia abajo no se vaya al ID de la visita sino que se quede en el ID del cliente y que ademas no se quede en la fila 6 sino que se desplce a la 7, veamos como quedo en la siguiente imagen
algo ha quedado mal, si bien no nos trajo un error (porque ahora si tiene bien la tabla de clientes), nos esta trayendo a Lisboa y lo que queremos es el nombre del cliente, bueno eso es por que la fórmula aun dice que traigamos la columna 3, y en este caso es la 2, tendremos que modificarla a mano
en el final de la barra de fórmulas vemos que hemos cambiado la columna 2 en la de indice, por lo que ahora si funciona la fórmula, entonces la recomendación es Antes de copiar las fórmulas para toda una columna, verifica antes que funcione bien en la primera, después puedes copiarlas.
Ahora para finalizar una prueba, vino un cliente nuevo a la tienda
Se registra común y corriente la visita, ahora registramos el nuevo cliente
La idea es que no tengamos que organizar la tabla cada que se registra un cliente, ahora si copiamos las fórmulas de la visita 3 en la 4 debería funcionar
Efectivamente funciona de maravilla (al copiar debes buscar la opción pegar como fórmulas, arriba en la izquierda hay un botón que dice pegar y tiene una flecha hacia abajo, ahí esta la opción pegar como fórmula y así queda el formato intacto). Teniendo en cuenta que esta clase tuvo mucho contenido no realizaremos ejercicios el día de hoy y lo dejaremos para el día de mañana.
En la próxima clase veremos ejercicios de referencia y búsqueda
[pwt clase=9]
