Este artículo explicará cómo construir una BUSCARV compensada. Es decir, cómo devolver valores que no están en la misma línea que el valor deseado.

problema a resolver

En caso de que tenga una tabla que contenga identificadores comunes a varias filas, o celdas vacías para indicar que el identificador se repite, se recomienda reordenar sus datos.

Para lograr esto, usaremos 3 funciones, ÍNDICE, COINCIDIR y COMPENSACIÓN

Tenemos una tabla (columna A:D) que enumera las ventas de nuestros artículos al final de cada mes. Para mejorar la legibilidad, queremos reordenar nuestros valores en las 2 tablas de las columnas G a J para mostrar las ventas y la facturación por mes.

Como puede ver, en la columna A faltan muchas fechas. Pero ese no es el problema. Incluso si copiamos las fechas en las celdas vacías, nos enfrentamos al mismo problema; No podemos identificar los datos de los elementos B, C y D.

Solución con Power Query

El método descrito a continuación se aplica cuando Power Query no existió. Desde, sin la más mínima fórmula, Usted puede obtener el mismo resultado solo en unos pocos pasos

Construcción de la fórmula.

La idea es encontrar la posición de las fechas (que es nuestro identificador) y leer los datos de abajo desplazando 1, 2 o 3 líneas.

No utilizar la función BUSCARV

En effet, la función BUSCARV Es perfecto para recuperar valores que están en la misma línea que el identificador.

Pero para otras líneas es imposible porque la función BUSCARV devuelve un valor y no un rango.

Prefiere la función ÍNDICE

Entonces debemos basarnos en la función ÍNDICE para construir nuestra investigación porque la función ÍNDICE devuelve un rango de datos.

Para la fórmula G2, escribiremos la siguiente fórmula para devolver el número de artículos vendidos en enero de 2014 para el producto A. Para explicaciones detalladas sobre la función ÍNDICE, puede consultar función ÍNDICE y para la función COINCIDIR.

=INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$49,0),3)

La función se puede entender de la siguiente manera:

• Estamos interesados ​​en el rango de datos A2:D49 (nuestros datos sin la línea de encabezado)
• Buscamos la línea correspondiente a la fecha que nos interesa (función EQUIV).
• Luego indicamos que recuperamos los datos de la 3ra columna (parámetro 3) para devolver el número de artículos vendidos..

Si queremos devolver el volumen de negocios, especificamos 4 para el último parámetro de la función ÍNDICE.

=INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$49,0),4)

Haz una búsqueda poco convencional

Como la función ÍNDICE devuelve un rango de datos (y no un valor como lo hace la función BUSCARV), podremos incluir las 2 fórmulas anteriores en una función SHIFT.

=DESREF(referencia de celda pivote, número de filas, número de columnas)

La función OFFSET devuelve datos relativos a una celda dinámica.

En nuestro ejemplo, para devolver el número de artículos B, debemos desplazarnos una celda hacia abajo desde la búsqueda anterior. Por lo tanto, escribiremos nuestra fórmula final de la siguiente manera para el producto B:

=DECALER(INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$46,0),3),1,0)

Y copiando esta fórmula, el resultado final es: