Veremos hoy un ejercicio típico de oposiciones a las administraciones públicas; se trata de emplear una doble búsqueda, con la función BUSCAR y BUSCARV. Son dos funciones muy conocidas, pero en este caso combinaremos ambas, añadiéndole algo de valor al usar Nombres definidos.
El objetivo es conseguir un Precio por Curso de Excel, a partir de una Tabla llamada 'TablaPrecios', y asociarla al alumno matriculado, conociendo el curso al que pertenece en base a una marca en otra tabla de referencias cruzadas. Veamos el planteamiento y solución en la imagen siguiente:
La meta a lograr es completar el rango E18:E27, obteniendo el curso en el que se encuentra matriculado el alumno en la Tabla D2:K12, para posteriormente determina el Precio del curso en la TablaPrecios.
Para facilitar el trabajo hemos asignado a ciertos rangos de nuestra hoja los siguientes nombres definidos:
Alumno001 =Hoja1!$E$3:$K$3
Alumno002 =Hoja1!$E$4:$K$4
Alumno003 =Hoja1!$E$5:$K$5
Alumno004 =Hoja1!$E$6:$K$6
Alumno005 =Hoja1!$E$7:$K$7
Alumno006 =Hoja1!$E$8:$K$8
Alumno007 =Hoja1!$E$9:$K$9
Alumno008 =Hoja1!$E$10:$K$10
Alumno009 =Hoja1!$E$11:$K$11
Alumno010 =Hoja1!$E$12:$K$12
La fórmula buscada es para E12 (luego bastará arrastar hacia abajo):
=BUSCARV(BUSCAR("xx";INDIRECTO($D18);$E$2:$K$2);TablaPrecios;2;0)
La explicación es sencilla, ya que replica los pasos comentados. Primero buscamos para cada alumno el curso que le corresponde en el rango D2:K12, mediante la función BUSCAR:
BUSCAR("xx";INDIRECTO($D18);$E$2:$K$2)
Con esta función encontramos el valor 'xx' en el rango correspondiente al alumno en cuestión; rango que determinamos mediante la función INDIRECTO. BUSCAR encuentra en el vector comparción del rango definido y devuelve del Vector resultado en E2:K2 el nombre del curso.
En la imagen anterior podemos ver el resultado en la columna auxiliar G17:G27 (o una variante con la función INDICE y COINCIDIR en el rango J17:J27).
Una vez conseguido el nombre del curso correspondiente a cada alumno, es fácil alcanzar el Precio de cada curso, ya que es suficiente aplicar este nombre en una función BUSCARV, para que encuentre en la primera columna de la matriz de búsqueda (para nosotros la TablaPrecios) y retornar el Precio que corresponda.
=BUSCARV(Nombre Curso;TablaPrecios;2;0)
Como podemos comprobar el resultado es válido tal como esperabamos.
El objetivo es conseguir un Precio por Curso de Excel, a partir de una Tabla llamada 'TablaPrecios', y asociarla al alumno matriculado, conociendo el curso al que pertenece en base a una marca en otra tabla de referencias cruzadas. Veamos el planteamiento y solución en la imagen siguiente:
La meta a lograr es completar el rango E18:E27, obteniendo el curso en el que se encuentra matriculado el alumno en la Tabla D2:K12, para posteriormente determina el Precio del curso en la TablaPrecios.
Para facilitar el trabajo hemos asignado a ciertos rangos de nuestra hoja los siguientes nombres definidos:
Alumno001 =Hoja1!$E$3:$K$3
Alumno002 =Hoja1!$E$4:$K$4
Alumno003 =Hoja1!$E$5:$K$5
Alumno004 =Hoja1!$E$6:$K$6
Alumno005 =Hoja1!$E$7:$K$7
Alumno006 =Hoja1!$E$8:$K$8
Alumno007 =Hoja1!$E$9:$K$9
Alumno008 =Hoja1!$E$10:$K$10
Alumno009 =Hoja1!$E$11:$K$11
Alumno010 =Hoja1!$E$12:$K$12
La fórmula buscada es para E12 (luego bastará arrastar hacia abajo):
=BUSCARV(BUSCAR("xx";INDIRECTO($D18);$E$2:$K$2);TablaPrecios;2;0)
La explicación es sencilla, ya que replica los pasos comentados. Primero buscamos para cada alumno el curso que le corresponde en el rango D2:K12, mediante la función BUSCAR:
BUSCAR("xx";INDIRECTO($D18);$E$2:$K$2)
Con esta función encontramos el valor 'xx' en el rango correspondiente al alumno en cuestión; rango que determinamos mediante la función INDIRECTO. BUSCAR encuentra en el vector comparción del rango definido y devuelve del Vector resultado en E2:K2 el nombre del curso.
En la imagen anterior podemos ver el resultado en la columna auxiliar G17:G27 (o una variante con la función INDICE y COINCIDIR en el rango J17:J27).
Una vez conseguido el nombre del curso correspondiente a cada alumno, es fácil alcanzar el Precio de cada curso, ya que es suficiente aplicar este nombre en una función BUSCARV, para que encuentre en la primera columna de la matriz de búsqueda (para nosotros la TablaPrecios) y retornar el Precio que corresponda.
=BUSCARV(Nombre Curso;TablaPrecios;2;0)
Como podemos comprobar el resultado es válido tal como esperabamos.
Hola Excelforo,
ResponderEliminarQue pasa si alumno matriculado lleva dos o más materias debería sumarse pero solo me muestra el último registro, como sería la fórmula para este caso.
De antemano gracias
Sldo.
Ana
Hola Ana,
Eliminarel caso que planteas es algo más complejo, ya que no bastaría una simple búsqueda (que sólo devuelve un valor).
El ejemplo sólo trabaja para un alumno por una asignatura.
En el caso queplanteas lo más sencillo sería construir una columnas auxiliares obteniendo el precio de cada curos matriculado, para acabar sumándo el importe de ambos cursos.
De todas formas pensaré una forma más 'limpia'.
Slds
Hola Ismael,
ResponderEliminarMe planteas que lo más sencillo es construir unas columnas auxiliares obteniendo el precio de cada cursos matriculado, me gustaría que me ayudarás un poco más a plantearlo.
de antemano muchas gracias
Slds
Ana.
Hola Ana,
Eliminarsiguiendo los rangos del ejemplo del post.
Añadirías en L3:R12 una fórmula para obtener para cada curso matriculado por alumno (con xx) su precio, por ejemplo:
=SI(E3="xx";BUSCARV(L$2;TablaPrecios;2;0);"")
y arrastras al resto del rango.
Terminamos con una nueva columna en S3:S12 con la suma del rango anterior:
=SUMA(L3:R3)
y ya puedes aplicar una búsqueda estándar en E18:E27, con un BUSCARV o un BUSCAR.
Slds
Hola ismael,
ResponderEliminarTe agradezco por tu ayuda, me sirvió los datos que me proporcionaste en el rango E18:E27, he aplicado BUSVARV y me da el resultado deseado.
solo por curiosidad como aplicaría con la función BUSCAR, ya que me recomienda utilizar buscarv o un buscar.
te agradezco
Slds
Ana
Hola Ana,
Eliminarbueno, con BUSCAR el único requisito es que el vector de búsqueda debe estar ordenado.. si se cumple esa condición la fórmula sería:
=SI(E3="xx";BUSCAR(L$2;$A$2:$A$8;$B$2:$B$8);"")
Slds cordiales!
Hola Excelforo,
ResponderEliminarleyendo los comentarios que hace Ana,y tú le responde, De todas formas pensaré una forma más 'limpia',más limpia te refiere a no utilizar columnas auxiliares.
Si es así como seria la única fórmula para el rango E18:E27.
Gracias por tú aporte
Aldo
Hola Aldo y también Ana,
Eliminarbueno, pues pensando un poco he encontrado esta solución, sin emplear columnas auxiliares, directamente en el rango E18:E27 usaríamos las matriciales:
=SUMAPRODUCTO((INDIRECTO(D18)="xx")*1;TRANSPONER(TablaPrecios[Precio:]))
ejecutada presionando Ctrl+Mayus+Enter en vez de sólo Enter.
Luego copiar para cada alumno.
Ojo porque requiere que la TablaPrecios esté en el mismo orden que en la Tabla de Alumnos(E2:K2)!!!!
Slds cordiales