jueves, 5 de enero de 2017

Buscar en Columnas Alternas en Excel

Un lector consultaba por la manera de Buscar en Columnas Alternas en Excel:
[...]Mi problema es el siguiente:
Tengo una tabla Excel con varias columnas, lo que pasa que una columna se llama de una manera y otra de otra. Ej:
Columna A: porcentaje 
Columna B: litros
Columna C: porcentaje
Columna D: litros 
Y así sucesivamente hasta la columna R
Es decir, que por decirlo de alguna manera las impares se llaman porcentaje, y las pares se llaman litros.
Cada una de las columnas, consta de 54 filas. Lo que buscaba es algo que, al poner en A56 un número que aparece en las columnas impares, en otra celda me de cómo resultado lo que hay en las columnas pares donde coincida con lo que pone en la columna anterior. 
A1: 10
B1: 110 litros
C1: 20
D1: 220 litros
E1: 100
F1: 1000 litros
G1: 200
H1: 2000 litros
Y así sucesivamente[...]


Esta sería nuestra situación:



En la celda G5 disponemos el valor o porcentaje buscado, insertando en H5 la fórmula matricial necesaria para recuperar los litros correspondientes:
=DESREF(INDICE(A2:H2;1;COINCIDIR(G5;A2:H2*ES.IMPAR(COLUMNA(A2:H2));0));0;1)
(no olvides validarla presionando Ctrl+Mayusc+Enter en lugar de Enter).


Si descomponemos nuestra fórmula matricial, todo comienza dentro de la función COINCIDIR, cuando creamos el rango 'virtual':
ES.IMPAR(COLUMNA(A2:H2))
que nos devolvería un conjunto de valores (V y F) como el siguiente:
{VERDADERO\FALSO\VERDADERO\FALSO\VERDADERO\FALSO\VERDADERO\FALSO}
es decir, identifica con VERDADERO aquellas columnas 'impares', sobre las cuales centramos la búsqueda del porcentaje deseado.
Al realizar el producto de este rango por los valores de A2:H2 conseguimos el rango:
{10\0\20\0\100\0\200\0}

Sobre esos datos, con COINCIDIR, buscamos el valor buscado de la celda G5... el cual nos devolverá una posición que emplearemos como número de columna en la función INDICE.

Nos aprovecharemos de la virtud de la función INDICE con la que podemos utilizarla como referencia (y no solo para recuperar el valor de la celda correspondiente); para nuestro ejemplo, la fórmula:
INDICE(A2:H2;1;COINCIDIR(G5;A2:H2*ES.IMPAR(COLUMNA(A2:H2));0))
devolvería el equivalente a la referencia E2.


Esta referencia la emplearemos dentro de la función DESREF como primer argumento, esto es, como ancla... a partir de la cual recuperamos la celda que se encuentra 0 filas a derecha o izquierda (o sea, la misma fila), y una columna a la derecha:
=DESREF(E2;0;1)
En definitiva, una celda a la derecha de la que localizamos con la búsqueda...


Con esta fórmula conseguimos lo que deseábamos, centrar la búsqueda exclusivamente en las columnas impares (columnas alternas), sin peligro que se detuviera la búsqueda o coincidencia en las columnas pares o de 'litros' a recuperar.

4 comentarios:

  1. Perfecto!!!!. Siento haber tardado tanto en dar las gracias.

    ResponderEliminar
  2. Buenas noches, me gustaría saber como hacer lo mismo si los datos están en mas filas, es decir, un rango por ejemplo de A3:X52, en vez de ser solo como en el ejemplo de A2:H2.
    Gracias.

    ResponderEliminar
    Respuestas
    1. Creo que para este caso, lo mejor sería desarrollar una macro...
      hacerlo con fórmula parece algo más complicado.
      Una macro que realice una búsqueda (con el método Find) sobre las columnas impares seleccionadas, parece lo más simple.
      Saludos

      Eliminar
    2. Ok, muchas gracias Ismael

      Eliminar