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 décima clase del curso virtual,
El archivo de esta clase acá >> Clase 10 – Ejercicios – Búsqueda y referencia – Ejercicios
El día de hoy vamos a tener ejercicios sobre el tema de ayer, vamos a tener en cuenta la siguiente situación, es muy poco común pero es tan solo un ejemplo por ahora
- Hay una lista de clientes con datos básicos
- Hay una tabla de visitas de clientes, un cliente puede hacer varias visitas pero cada visita es a una hora diferente
- Hay una tabla de ventas, pero todas las visitas no generan una compra
- Solo hay 2 productos
- el número de la factura es único, pero no es la llave primaria de la tabla
En el archivo puedes encontrar ya el diseño listo para trabajar, acá unas imágenes de las tablas
Ejercicios
Se necesita finalizar 2 informes que se encuentran en el libro de Excel, Uno es el informe de ventas del mes y el otro es el informe de ventas pero por cada cliente, recordemos que en este escenario cada cliente hizo una compra únicamente.
Imágenes de las tablas
Vamos a resolver unos ejercicios y tu harás los demás, empecemos por el ejercicio de la primera fila, el dato que nos están entregando es el código de la factura y se comento que es un código único, entonces si la factura es F002 y necesitamos el ID de Venta podemos usar Indice + Coincidir, en este caso BuscarV no serviría porque son datos que están a la izquierda, puedes probarlo pero vamos con la fórmula óptima para este trabajo
Recordemos de la clase anterior como se debe leer: «¡oye Excel!, necesito que me encuentres el valor de U15 (El código de la factura F002) en la matriz P15:P19 (la columna de factura en la tabla de ventas) y que coincida exactamente (tipo de coincidencia 0, sino lo encuentra trae error), luego de todo eso, necesito que de la matriz M15:R19 (Los datos de la tabla de ventas) me traigas la fila de COINCIDIR (en este caso va a devolver que la encontró en la fila 2) y la columna 1 (Para que traiga el ID de Venta). En la siguiente imagen vemos el resultado
Recordemos que los símbolos $ sirven para fijar las fórmulas, de pronto las podemos utilizar hacia abajo y no queremos que se vayan a desplazar los rangos que hemos programado. Marque con un resaltado normal las casillas que tienen fórmula, continuemos, con el ID de venta puedo traer la descripción de la venta, la fórmula sería:
=INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);5)
En resumidas cuentas dice que busque T15, es decir el ID de venta en la tabla de ventas y que traiga la 5 columna que es el producto, ya vamos entendiendo y se hace un poco más fácil, ahora vamos por la siguiente columna el cliente, en este caso tenemos el ID de venta de la tabla de ventas y el ID de Cliente, pero nos están pidiendo el nombre, entonces ya tenemos que encadenar 2 fórmulas, veamos como lo haría yo:
- Hago una sola búsqueda para que me traiga el ID del cliente
=INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2)
De hecho es igual a la anterior solo que en vez de traer la columna 5 traigo la 2 para que me muestre el ID de cliente
Como ya sabemos que eso en realidad da 1 lo que hacemos es tener presente ese 1 y vamos a construir otra fórmula, dejando esta anotada en otra parte (por lo general yo abro una columna adicional y lo hago ahí y luego cuando acabo la quito, hoy lo vamos a hacer manual) entonces ahora quiero traer el nombre que se encuentra en la tabla de clientes, la fórmula quedaría así:
=INDICE($A$15:$F$20;COINCIDIR(1;$A$15:$A$20;0);2)
Primero coincide el número 1 en la tabla de A15:A20 (tabla de clientes) y ahora de esa misma tabla de clientes traeme la fila que coincide y la columna 2 de nombres
Ahora lo que hacemos es reemplazar el 1 de coincidir por la fórmula que sabemos que nos da ese 1, asi quedará encadenada una fórmula dentro de la otra así:
=INDICE($A$15:$F$20;COINCIDIR(INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2);$A$15:$A$20;0);2)
No se debe copiar con el signo = (igual) ya que solo puede haber uno por fórmula, el resultado nos debe seguir dando Pepito Perez
y todo esto nos sirve si vamos a copiar la fórmula en otra casilla con las mismas condiciones, probemos copiandola en otro ejercicio con ID de Venta, el 5 tiene esta misma condición
Lo pegamos como fórmula para que no se dañe el formato y luego lo resaltamos para indicar que es una fórmula, entonces nos dio como resultado Gonzalo Gonzo, como podemos verificar que esto si sea verdad miremos el ID de venta 3
Vemos que tiene el ID de cliente 3 y si revisamos la tabla de clientes
El cliente con cédula o ID de cliente es Gonzalo Gonzo, por lo que lo hemos realizado de una forma correcta el copiado y pegado. Ahora que nos piden el teléfono y la edad, la verdad es solo copiar la misma fórmula y cambiarle las columnas, pero no copies desde la celda por que se van a desplazar los datos, cópialo de la barra de formulas seleccionándolo con el mouse, o puedes utilizar FORMULATEXTO y luego copiar y pegar como valores quedando así:
=INDICE($A$15:$F$20;COINCIDIR(INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2);$A$15:$A$20;0);6)
=INDICE($A$15:$F$20;COINCIDIR(INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2);$A$15:$A$20;0);3)
Solo tuve que cambiar el último número a 6 para teléfono y 3 para edad en ababas formulas quedando de la siguiente manera
Finalicemos con la hora de la visita, Este campo se encuentra en la tabla de visitas de clientes, y como tenemos el ID de la visita entonces la fórmula es la siguiente
=INDICE($H$15:$K$23;COINCIDIR(T15;$H$15:$H$23;0);4)
Bueno ahora es tu turno para finalizar los ejercicios, ya esta casi resuelto y sirve de práctica, para la segunda tabla solo debes indicar si hizo una compra o no, si la hizo debes traer el total y si no puedes dejarla como #N/A.
Si tuviste dificultades con este ejercicio no dudes en hacer uso de tus servicios adquiridos o en escribirme a curso@ortizvivas.com con tu duda, recomendación, sugerencia o felicitación. ¡Hasta entonces!
Espacio anti trampa
Primero con FORMULA TEXTO las formulas que vimos hoy
| ID Venta | =INDICE($M$15:$R$19;COINCIDIR(U15;$P$15:$P$19;0);1) |
| Factura | #N/A |
| Descripción | =INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);5) |
| Cliente | =INDICE($A$15:$F$20;COINCIDIR(INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2);$A$15:$A$20;0);2) |
| Teléfono | =INDICE($A$15:$F$20;COINCIDIR(INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2);$A$15:$A$20;0);6) |
| Edad | =INDICE($A$15:$F$20;COINCIDIR(INDICE($M$15:$R$19;COINCIDIR(T15;$M$15:$M$19;0);2);$A$15:$A$20;0);3) |
| Hora Visita | =INDICE($H$15:$K$23;COINCIDIR(T15;$H$15:$H$23;0);4) |
Ahora como debe quedar solucionado el ejercicio
y la segunda tabla
Nos vemos en la siguiente clase con más conceptos interesantes, gracias y ¡hasta entonces!
[pwt clase=10]
