viernes, 8 de agosto de 2014

BUSCARV para valores repetidos

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.
  1. Paso 1. Seleccionar el rango donde la fórmula matricial actua. En este caso es el rango Q9;Q58
  2. Paso 2. Escribir la fórmula matricial
  3. Paso 3. Validar la fórmula pulsando simultáneamente las tres siguiente teclas: Contro+Shift+Enter


17 comentarios:

  1. Yo diría MAS QUE EXCELENTE. desde México

    ResponderEliminar
  2. SR. ADOLFO a. Es usted un genio para explicar los temas con peras y manzanas a los que se nos dificulta. Gracias por sus conocimientos

    ResponderEliminar
  3. Como 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?

    ResponderEliminar
  4. Hola Angel.
    Por 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.

    ResponderEliminar
  5. Buenos Dias

    hasta ahora estoy mirando cada uno de los temas y me han parecido espectaculares, que Dios lo bendiga por su conocimiento

    ResponderEliminar
  6. Hola buenas,

    Me 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

    ResponderEliminar
  7. Magnífico. Gracias por compartir tanto y tan bien.

    ResponderEliminar
  8. Excelente!! 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!

    ResponderEliminar
  9. Muchas Gracias Señor Adolfo. Es un chingón.

    ResponderEliminar
  10. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  11. Adolfo 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

    =SI(BUSCARV(N5;Q4:R7;2;0);K.ESIMO.MAYOR(R4:R7;0);"0")

    ResponderEliminar
  12. COMO HAGO UN BUSCARV, PERO QUE NO ME TOME LOS DATOS O LAS COLUMNAS QUE ESTAN OCULTAS, SE PUEDE,,,,,,GRACIAS

    ResponderEliminar
  13. Hola:

    me 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.

    ResponderEliminar
    Respuestas
    1. Hola Tomas.
      Te 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.

      Eliminar
  14. Hola. Primero que nada mil gracias por su blog, me ha ayudado mucho. Tengo una duda, supongamos que nuestra matriz es la siguiente:
    Producto 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.

    ResponderEliminar