Archivo utilizado en el vídeo: excelavanzado_buscarv_repetidos.xlsx
La función BUSCARV siempre busca el primer valor que encuentra en el caso de que existan varios repetidos. Vamos a proponer un procedimiento que nos permitirá extraer de una base de datos aquellos registros que indiquemos aunque estén repetidos.
Nota
En el rango W2:Z2 se encuentran las fórmulas aleatorias con las que se han generado los valores de la base de datos.Hoja1
Hoja2
Usamos la siguiente función matricial.=SI.ERROR(INDICE($B$6:$B$24;K.ESIMO.MENOR(SI(C6:C24=0;FILA());FILA()-5)-5);"")
Hoja3
En la Hoja3 se mustran los pasos que permite obtener la fórmula de la Hoja3 de la columan amarilla.
Hoja4
Introducimos una fórmula matricial que extrae los registros que cumplen el criterio. En la celda Q9 vemos la siguiene fórmula matricial.
=SI.ERROR(INDICE(factura;K.ESIMO.MENOR(SI($I$5=Comercial;FILA()-8);FILA()-8);1);"")
Esta fórmula matricial no pertenece únicamente a esa celda, sino que abarca el rango Q9:Q58.
Las fórmulas matriciales requieren tres pasos para establecerlas correctamente.
- Paso 1. Seleccionar el rango donde la fórmula matricial actua. En este caso es el rango Q9;Q58
- Paso 2. Escribir la fórmula matricial
- Paso 3. Validar la fórmula pulsando simultáneamente las tres siguiente teclas: Contro+Shift+Enter
Yo diría MAS QUE EXCELENTE. desde México
ResponderEliminarSR. ADOLFO a. Es usted un genio para explicar los temas con peras y manzanas a los que se nos dificulta. Gracias por sus conocimientos
ResponderEliminarComo siempre muy bien explicado y el ejemplo muy didáctico. Me lo he tragado entero y he aprendido un montón de trucos que no sabía. Lo que todavía no entiendo es por qué Microsoft decidió traducir los nombre de la formulas al Español. Hay alguna manera de que no lo haga por defecto?
ResponderEliminarHola Angel.
ResponderEliminarPor lo que yo se cada paquete Office debes elegirle un único idioma. Por ejemplo, debes elegir entre español o inglés, y son paquetes diferentes. No se puede cambiar de idioma simplemente eligiendo una opción del propio Excel.
Lo que te encuentras en muchas empresas es que han instalado en el mismo equipo tanto la versión en español como la versión en inglés, y en este caso trabajan bien tanto las funciones en español como en inglés. Por ejemplo, BUSCARV es VLOOKUP.
En las funciones complementarias pasa lo mismo, las que se obtienen instalando el complemento "Herramientas para análisis". Frecuentemente se ve que están también las inglesas.
Es algo que en su día estuve buscando para ver si existía una forma sencilla de cambiar entre idiomas, pero no lo encontré, por lo que deduzco que se deben instalar las dos versiones para tener los dos idiomas.
Si existe otra opción, es posible que alguien nos aporte alguna idea.
Un saludo.
Buenos Dias
ResponderEliminarhasta ahora estoy mirando cada uno de los temas y me han parecido espectaculares, que Dios lo bendiga por su conocimiento
Hola buenas,
ResponderEliminarMe funciona todo bien, pero no me detecta el ultimo valor, por ejemplo si hay 8 valores repetidos me detecta solo 7 y en caso de ver solo 1 valor no me lo detecta. no entiendo el porque.
Gracias por tu ayuda
GRACIAS infinitas!!!!!!!!!!
ResponderEliminarMagnífico. Gracias por compartir tanto y tan bien.
ResponderEliminarExcelente!! Excel es apasionante y aún más cuando encuentras alguien que lo hace ver mucho más sencillo de lo que aparenta ser. Muchas gracias!
ResponderEliminarMuchas Gracias Señor Adolfo. Es un chingón.
ResponderEliminarEste comentario ha sido eliminado por el autor.
ResponderEliminarAdolfo buen día, estoy intentando aplicar ésta fórmula =SI.ERROR(INDICE($B$6:$B$24;K.ESIMO.MENOR(SI(C6:C24=0;FILA());FILA()-5)-5);"") porque tengo un ejercicio igualito pero no he sido capaz, cómo me podrías ayudar? muchas gracias
ResponderEliminar=SI(BUSCARV(N5;Q4:R7;2;0);K.ESIMO.MAYOR(R4:R7;0);"0")
COMO HAGO UN BUSCARV, PERO QUE NO ME TOME LOS DATOS O LAS COLUMNAS QUE ESTAN OCULTAS, SE PUEDE,,,,,,GRACIAS
ResponderEliminarHola:
ResponderEliminarme dirijo a ustes para realizarle una consulta que me está volviendo loco. En el tutorial de excelavanzado sobre "buscar V repetidos" uno de los métodos utiliza la siguiente fórmula matricial para resolverlo:
=SI.ERROR(INDICE(factura;K.ESIMO.MENOR(SI($I$5=Comercial;FILA()-8);FILA()-8);1);"")
en su ejemplo funciona a la perfección, en las modificaciones que he realizado (para mi caso) me ha surgido el problema de que la formula no sabe manejar más de 50 registros, a partir de ahí falla y no devuelve datos. Si en la formula de arriba ( $I$5=comercial), la lista "comercial" tiene más de 50 registros, la fórmula matricial no funciona.
Podría darme una solución?
Adiocionalmente a esto he encontrado problemas con los formatos de celdas, parece que si no los pongo con el mismo formato no identifica la fórmula matricial correctamente.
Muchas gracias de antemano y espero que siga poniendo conocimientos al alcace de todos.
Hola Tomas.
EliminarTe he dejando un nuevo vídeo en el apartado correspondiente a la Hoja4. En el vídeo se muestra cómo se han de extender los nombres de rango y cómo validar la fórmula matricial.
Un saludo.
Hola. Primero que nada mil gracias por su blog, me ha ayudado mucho. Tengo una duda, supongamos que nuestra matriz es la siguiente:
ResponderEliminarProducto Producción (ton)
Manzana 1
Manzana 4
Manzana 3
Existe alguna forma de excel busque por ejemplo, cuando encuentre el primer campo llamado Manzana escriba en la columna "x" que llamaremos producción primavera, cuando encuentre el segundo campo Manzana escriba el dato en columna "y" producción verano y cuando encuentre el tercer campo Manzana escriba el dato en la columna "z" producción otoño.
Mil gracias.
Buenos días Sr. Adolfo, muchas gracias por su valioso aporte; simplemente genial y me ha ayudado muchísimo. Mil gracias.
ResponderEliminarBUENAS TARDES PARA BUSCAR REGISTROS REPETIDOS EJEMPLO DIGITO LA CEDULA Y SALE TODA LA INFORMACION QUE NECESITO UTILIZANDO ESTA FORMULA BUSCARV(B6;'BUSCARV(B6;'[BASE GENERAL.xls]BASE'!C1:L200;2;0) LA FORMA SIMPLE PERO SI YO QUIERO TRAER MAS REGISTROS CUANDO DIGITO LA CEDULA QUE DEBO HACER...GRACIAS
ResponderEliminarAlguien me podria pasar el archivo, es q el link de la pagina ya no lo encuentra
ResponderEliminareste es mi correo por favor: eybram14@gmail.com
EliminarHola.
EliminarHe probado ahora mismo el enlace del inicio de este post y va bien.
Te dejo el enlace:
https://www.dropbox.com/s/cl40a13tel8dx7b/excelavanzado_buscarv_repetidos.xlsx?dl=1
Un saludo.
Hola ... Agradezco muchisisisisisimop este ejercicio no saben cuanto me sirvió he estado buscando esa formula por años y no había podido encontrarla en verdad le agradezco mucho .
ResponderEliminar