Previsión en Excel

La función DESREF en Excel sirve para devolvernos una referencia o un rango a partir de una celda o un rango cualquiera de Excel. Además podemos indicar el número de filas o columnas del rango devuelto.

Argumentos de la función DESREF en Excel.

La función DESREF en Excel admite cinco argumentos de los cuales tres de ellos son obligatorios y los dos últimos opcionales.

• Referencia: Es la posición a partir de la que nos desplazamos para recuperar el rango deseado.
• Filas: A partir de “la referencia” establecida como argumento anterior nos desplazaremos tantas filas como aquí definamos.
• Columnas: A partir de “la referencia” establecida como argumento anterior nos desplazaremos tantas columnas como aquí definamos.
• Alto: Establece el número de filas que tendrá el rango a recuperar.
• Ancho: Establece el número de columnas que tendrá el rango a recuperar.

Ejemplo de uso de la función DESREF en Excel.

La función DESREF en Excel es muy interesante para devolver rangos a partir de una determinada posición. Así por ejemplo podremos devolver un valor concreto de una rango indicando un desplazamiento de filas y columnas desde una posición concreta.

En el ejemplo nos posicionamos en la celda A4. A partir de esa celda nos desplazamos una fila y una columna hasta llegar a la celda B5. Finalmente indicamos que queremos recuperar una matriz de una fila por una columna que se concreta en una celda la celda B5 cuyo valor es 5.

Sin embargo la función DESREF en Excel cobra especial interés cuando trabaja con otras funciones. Por ejemplo con la función SUMA.

Sabemos que en una celda solamente podemos devolver una valor. Así que apartir de los mismos datos utilizados en el ejemplo anterior vamos a ver como sumar la matrir completa a partir de la posición A4.

Nos hemos posicionado en A4 y hemos definido un desplazamiento de 0 filas y de 0 columnas. Sin embargo le decimos que queremos que nos devuelva un rango de 3 filas y 3 columnas, precisamente, la información que deseamos sumar.

Esta matriz resultante es el argumento de entrada de la función suma. Se suman los 9 datos para conseguir el resultado final 45.

Estos ejemplos son didácticos para poder comprender como actúa la función pero poco útiles en el entorno de la oficina.

No obstante existe un concepto “la búsqueda dinámica” que nos permite buscar un determinado valor dentro de un listado. Este listado puede ampliarse y reducirse y nuestra búsqueda dinámica seguirá funcionando. Para lograr este comportamiento es preciso combinar tres funciones muy poderosas de Excel. La función DESREF, la función BUSCARV y la función CONTARA o alguna de sus variantes. A continuación presentamos un ejemplo de búsqueda dinámica.

Ejemplo de uso de la función DESREF en Excel dentro de la función BUSCARV búsquedas dinámicas.

Partimos de un ejemplo muy sencillo. Un listado donde controlamos el estado de stock de una frutería. La frutería cuenta en su almacén con cajas adicionales de diferentes frutas y verduras. Por ejemplo, 1 caja de tomates, 2 cajas de lechugas…

Vamos a configurar en la celda D2 una búsqueda de por ejemplo “Plátanos”. Entonces lo definimos como BUSCARV(D2; $A$1:$B$6; 2; FALSO)

La problemática que se plantea es la siguiente. ¿Qué ocurre si entra un nuevo producto en el almacén? Por ejemplo “Cerezas”. Ampliamos el listado de stock de almacén. Incrementamos las 10 cajas. Pero la búsqueda que habíamos parametrizado no funciona. Tenemos como resultado un #N/A. ¿Por qué? Bueno si nos fijamos en nuestra matriz de búsqueda el rango A1:B6 ya no es válido ahora el rango correcto será A1:B7.

Pero también podría ocurrir que las ventas fuesen muy elevadas y hubiésemos vendido parcialmente parte de los elementos del almacén y entonces la matriz de búsqueda podría ser A1:B4

Entonces ¿Cómo podemos hacer que el rango de la matriz de búsqueda se ajuste al número de elementos de la matriz de búsqueda? Lo vamos a conseguir gracias a la combinación de tres funciones. BUSCARV, DESREF y CONTARA. Pero veámoslo paso a paso.

1. Partimos de la función de BUSQUEDA y el primer argumento será el producto del cuál queremos consultar el estado del STOCK.

BUSCARV( “Sandía”…

2. El segundo argumento de nuestra función BUSCARV es un rango o matriz de datos. Sabemos que la función DESREF es capaz de devolver un rango o matriz así haremos uso de ella en este punto.

BUSCARV(“Sandía”; DESREF(A1;0;0;

Nos hemos posicionado en la primera celda del rango y no nos hemos desplazado ni filas ni columnas puesto que nos interesa precisamente el rango que comienza en A1:B?

3. Seguimos dentro de la función DESREF y tenemos que definir en los dos argumentos que faltan el número de filas y número de columnas que queremos recuperar. Entonces utilizaremos la tercera función la función CONTARA para averiguar cuantos datos hay en la columna A que contiene los productos.

BUSCARV(“Sandía”; DESREF(A1;0;0;CONTARA(A:A);2)

Además decimos el número de columnas que queremos recuperar que para este ejemplo son 2 y así lo hemos indicado.

4. Ahora solo nos resta volver a nuestra función de búsqueda y rellenar los argumentos restantes. El indicar de columnas IC y tipo de coincidencia “Exacta”.

BUSCARV(“Sandía”; DESREF(A1;0;0;CONTARA(A:A);2);2;FALSO)

De esta manera si añadimos un nuevo producto la búsqueda seguirá funcionando correctamente.

Función DESREF de Excel en Inglés.

La función en inglés se puede escribir como

=OFSSET(reference; rows; cols;[height]; [width])