jueves, 1 de abril de 2010

Búsqueda matricial de un texto en Excel.

Resolveré un caso interesante de búsqueda matricial, para obtener un valor de texto.
Un usuario preguntaba:
...tengo tres columnas y que si se cumple una condicion en la primera colunma, pase a la segunda columna y al cumplir otra condicion me de el valor que figura en la tercera columna (la tercera columna es de texto).
Esto es en si lo que necesito:
26/02/10 27/02/10 28/02/10
juan = = =
pedro = = =
jose = = =
...

Para este trabajo aplicaremos la función INDICE.
Es importante advertir que esta explicación que daré a continuación es sólo válida para valores cruzadas únicos, no repetidos.
Supongamos que disponemos del listado de datos propuesto:


En primer lugar construiremos la Tabla donde recogeremos los valores de texto de las referencias cruzadas:


Seguidamente daremos forma a la base de nuestra fórmula matricial, que permitirá determinar qué registros cumplen simultáneamente los criterios coincidentes de 'Fecha' y 'Nombre', esto lo haremos con:
($A$2:$A$8=F$1)*($B$2:$B$8=$E2)
así obtendremos un valor 1 para la coincidencia y 0 para el error, el rango matricial que obtenemos de esta fórmula, lo emplearemos dentro de la función COINCIDIR
COINCIDIR(1;($A$2:$A$8=F$1)*($B$2:$B$8=$E2);0)
es decir, buscamos el valor 1 en la matriz resultante de 0 y 1 anteriormente descrita, lo que nos dará una posición de fila.
Es esta posición la que usaré ahora dentro de la función INDICE
INDICE($A$2:$C$8;COINCIDIR(1;($A$2:$A$8=F$1)*($B$2:$B$8=$E2);0);3)
con la que encontraremos dentro de nuestra base de datos, de rango A2:C8, en la columna tercera, i.e., la opción de texto que buscamos, y la fila determinada por la función COINCIDIR anterior.


Por último, para evitar errores antiestéticos, hemos provisto nuestra fórmula de la función ESERROR para discriminar aquellas intersecciones de valores que no devuelve ningún texto.

{=SI(ESERROR(INDICE($A$2:$C$8;COINCIDIR(1;($A$2:$A$8=F$1)*($B$2:$B$8=$E2);0);3));"";INDICE($A$2:$C$8;COINCIDIR(1;($A$2:$A$8=F$1)*($B$2:$B$8=$E2);0);3))}

por supuesto, no olvidar que hay que ejecutarla en forma matricial (Ctrl+Mayus+Enter).

14 comentarios:

  1. Hola!
    Tambien funciona asi:
    =INDICE($C$2:$C$8,COINCIDIR(1,INDICE(($A$2:$A$8=E$1)*($B$2:$B$8=$D2),),),)

    Y no es matricial

    Saludos desde Honduras

    ResponderEliminar
    Respuestas
    1. Funciona!!
      La unica macana de esto es cuando hay mas de una coincidencia.
      En mi caso necesitaria que me arroje la suma de las coincidencias...

      Eliminar
    2. Hola Zanty,
      si buscas la suma de coincidencias, quizá tengas que aplicar la función SUMAR.SI.CONJUNTO, creo que respondería mejor a tu cuestión.
      Slds

      Eliminar
  2. Gracias por el aporte...
    lo probaré.
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      yo lo he intentado de las 2 maneras y no me funciona me da #N/A

      Mike

      Eliminar
    2. Hola,
      tienes que ejecutarla de manera matricial, ya que de lo contrario te dará error.
      Debes escribir la función, tal cual se especifica y al terminar, en lugar de valirdar con Enter, lo haces presionando Ctrl+Mayusc+Enter.
      Slds

      Eliminar
    3. Hola!!
      Probé haciendo lo que dices pero marca error de #¡VALOR!, al parecer no toma como válidos los argumentos $A$2:$A$8=F$1 y $B$2:$B$8=$E2.

      Saludos

      Eliminar
    4. Perdón creo que no me expliqué bien. Al momento de separar la fórmula de probar por separado la fórmula COINCIDIR(1,($A$2:$A$8=F$1)*($B$2:$B$8=$E2),0), marca el error #¡VALOR!, pues no toma como válidos los argumentos $A$2:$A$8=F$1 y $B$2:$B$8=$E2.
      ¡Gracias! Saludos

      Eliminar
    5. Hola lazyros,
      el error debe estar en el tipo de valores que tengas en alguna de las celdas implicadas, ya que la función
      =COINCIDIR(1,($A$2:$A$8=F$1)*($B$2:$B$8=$E2),0)
      es correcta y no da error, esto es, si reconoce el producto matricial condicionado como un rango.
      Si al revisar los valores sigue fallando envíame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
    6. Muchas gracias, se corrigió el problema con lo que indicaste!
      Saludos

      Eliminar
    7. Buenas tardes, no me permite insertar la formula, ya traté con las instrucciones antes mencionadas. Me pregunta "estás tratando de insertar una formula"

      Eliminar
    8. Hola
      debes ejecutarla matricialmente, i.e., presionado Ctrl+Mayusc+Enter en lugar de Enter
      Saludos

      Eliminar
  3. cómo hago para buscar un texto en una matriz donde hay otro listado con celdas de textos, pero que me muestre en frente del texto original si existe o no?

    ResponderEliminar
    Respuestas
    1. Hola Javier, ¿como es´tas?, un gusto saludarte igualmente.
      No me queda claro tu planteamiento, pero echa un vistazo a este post
      http://excelforo.blogspot.com.es/2009/11/funcion-definida-por-el-usuario-doble.html
      quizá te oriente....

      Un cordial saludo

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.