viernes, 9 de noviembre de 2012

Una matricial de Excel para listar coincidencias.

Hace bastante tiempo expliqué la forma matricial de conseguir un listado de valores únicos (ver). En esta ocasión realizaremos un ejercicio similar, donde a partir de un tabla de datos, listaremos los elementos coincidentes a un criterio dado.
Veamos nuestra tabla de datos:


Nuestro objetivo es conseguir listar únicamente los valores de la columna B que coincidan con el critrerio de la columna A dado, en el ejemplo, que sea igual a N:


La fórmula matricial de Excel buscada es:
=SI.ERROR(INDICE(SI($A$1:$A$14="N";$B$1:$B$14;"");K.ESIMO.MENOR(SI(SI($A$1:$A$14="N";$B$1:$B$14;"")="";CONTARA($A$1:$A$14)+FILAS($A$1:$A$14);FILA());FILA()));"")
Recordemos, al ser matricial, se debe ejecutar presionando Ctrl+Mayu+Enter; en este caso sobre el rango seleccionado D1:D14.


Expliquemos las partes de esta fórmula. Lo primero es excluir aquellos elementos que no necesitamos, es decir, los diferentes a N. Esto lo conseguimos con la fórmula:
SI($A$1:$A$14="N";$B$1:$B$14;"")
que nos devolvería un rango, una matriz de valores, sólo con los nombres de la columna B correspondientes a un texto igual a N en la columna A:

Una matricial de Excel para listar coincidencias.



Otra parte de la fórmula evalua ese nuevo rango, de tal forma que devuelve un valor de FILA() en caso de coincidencia y un valor muy alto en caso contrario CONTARA($A$1:$A$14)+FILAS($A$1:$A$14.
SI(SI($A$1:$A$14="N";$B$1:$B$14;"")="";CONTARA($A$1:$A$14)+FILAS($A$1:$A$14);FILA())

Una matricial de Excel para listar coincidencias.



Si anidamos el último rango, con los valores de fila, en una función K.ESIMO.MENOR, obtendremos el rango ordenado de menor a mayor, según los valores devueltos:
K.ESIMO.MENOR(SI(SI($A$1:$A$14="N";$B$1:$B$14;"")="";CONTARA($A$1:$A$14)+FILAS($A$1:$A$14);FILA());FILA()))

Una matricial de Excel para listar coincidencias.



LLegamos al final, puesto que al agregar como argumento de la función INDICE este último rango/matriz obtenido, conseguiremos los nombres de la columna B; con la función INDICE relacionamos fila con nombre:
INDICE(SI($A$1:$A$14="N";$B$1:$B$14;"");K.ESIMO.MENOR(SI(SI($A$1:$A$14="N";$B$1:$B$14;"")="";CONTARA($A$1:$A$14)+FILAS($A$1:$A$14);FILA());FILA()))

Una matricial de Excel para listar coincidencias.



Realmente ya tendríamos lo que buscabamos, pero para eliminar de la vista los errores mostrados, anidamos todo en una función SI.ERROR, finalizando con nuestra matricial inicial:
=SI.ERROR(INDICE(SI($A$1:$A$14="N";$B$1:$B$14;"");K.ESIMO.MENOR(SI(SI($A$1:$A$14="N";$B$1:$B$14;"")="";CONTARA($A$1:$A$14)+FILAS($A$1:$A$14);FILA());FILA()));"")


En este ejercicio de Excel hemos trabajdo con las matriciales como si fueran rangos, con valores variables, según la posición de la fórmula, por lo que es muy importante al ejecutarlas, tener seleccionado el rango destino, es decir, donde esperamos obtener los valores calculados.

3 comentarios:

  1. Buenas!Disculpen, quisiera hacer una consulta, pero nose donde puedo enviar un excel con el ejemplo de la consulta,me podrian ayudar?
    muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola neonlennon,
      puedes enviarlo a
      excelforo@gmail.com
      Slds

      Eliminar
    2. Muchas gracias otra vez, ya le envie mi consulta, hasta luego!

      Eliminar