Esta función nos devuelve una referencia a un rango de celdas que ha sido desplazado respecto a otra referencia que hemos especificado es complicado explicarlo, pero la función DESREF es uno de esos tesoros escondidos de Excel, nos permite obtener un valor a partir de un punto de partida, de una referencia, de un origen, de tal manera, que especificándole las filas y columnas que nos queremos mover, obtenemos un valor, también te puede permitir obtener un rango.
Sintaxis
· Ref. (obligatorio): La referencia a la celda o rango donde se iniciará el
· desplazamiento.
· Filas (obligatorio): El número de filas a moverse. Si el valor es positivo
· se moverá hacia abajo y si es negativo se moverá hacia arriba.
· Columnas (obligatorio): El número de columnas a moverse. Si el valor
· es positivo se moverá hacia la derecha y si es negativo se moverá a la
· izquierda.
· Alto (opcional): El número de filas que deseamos que nos devuelva.
· Ancho (opcional): El número de columnas que deseamos que nos
Imaginemos que podemos dar a Excel un conjunto de instrucciones para ir a una celda determinada:
Vamos a ver algunos ejemplos, si yo estoy en la celda O12.
Y quiero llegar a estas celdas, con los valores A,B,C, y D.
Vamos a ver cómo nos vamos a mover, cuantas filas, cuantas columnas tenemos que mover.
Para esto, algunas reglas que debemos tener en cuenta.
En Excel, los desplazamientos hacia abajo y hacia la derecha son positivos, y los desplazamientos hacia la izquierda y hacia arriba son negativos.
Cuando usemos funciones en Excel, lo primero que tenemos que indicar son las filas y después las columnas.
Para llegar desde la celda ORIGEN a la celda que contiene el valor de A, debo de moverme desde ORIGEN, dos filas hacia abajo y dos columnas a la derecha.
Para llegar a la celda que contiene el valor B, debemos de movernos 5 filas hacia abajo y una columna hacia la derecha.
Bien, ahora para llegar a la celda con el valor C, debo de moverme dos filas a la izquierda y dos columnas arriba.
Y, por último, para llegar a la celda que contiene el valor de D, debo de moverme, 1 columna hacia la izquierda, y, 6 filas hacia arriba.
Esta es la función DESREF en su mínima expresión.
Ahora, veamos otro ejemplo, donde voy a usar el cuarto y quinto argumento, es decir, alto y ancho, lo que me va a devolver más de un valor.
Tengo los siguientes datos.
Si escribo, =DESREF(A6;3;2), le estoy diciendo que a partir de la celda A6, se mueva tres filas hacia abajo, y, y dos columnas a la derecha.
Me devuelve el valor 306.
Ahora, vamos a usar el cuarto y quinto argumento, si uso la siguiente sintaxis:
=SUMA(DESREF(I6;3;2;2;2))
Le estoy diciendo igual que antes, que se mueva desde I6, tres filas, y, dos columnas, una vez en el destino, que tome un alto de dos, y, un ancho de dos también.
Es decir, que como resultado me va a devolver los valores 306, 349, 190, y, 102, es decir, una matriz desbordada, pero queremos el total, pues, delante ponemos la función SUMA.
=SUMA(DESREF(I6;3;2;2;2))
Decir, que cuando especificamos el tercer y cuarto argumento, a diferencia de cuando especificamos los argumentos fila y columna, empieza a contar desde la celda donde esta, no en la siguiente.
Bien, veamos otro ejemplo, este ejemplo es un caso real de un alumno mío.
Teniendo los siguientes datos.
El manualmente, calculaba la diferencia de ventas hasta el mes actual entre el año 2020, y, 2021, el cual el me comentaba que era una tarea tediosa, si había alguna manera de poder automatizarlo.
Yo pensé que con la función DESREF, era probable que se pudiera hacer.
Lo primero que debía de saber, es cuál es el mes actual para poder hacer las comparaciones, eso lo pude hacer con la función MES sobre la fecha actual, de la siguiente manera:
=MES(HOY())
Ya tengo el número de mes, como los datos están en horizontal, quiere decir que de la función DESREF debo de usar el argumento ancho para tomar los valores hasta el mes actual.
Me dijo que quería saber el total de todos los productos hasta el mes actual, por lo que también me hacía falta el argumento alto de la función DESREF, que en este caso, es un número fijo.
Entonces, si huso la siguiente sintaxis:
=SUMA(DESREF(C4;0;0;8;MES(HOY())))
Le estoy diciendo que a partir de la celda C4, se mueva cero filas, y, cero columnas, por lo que se queda en la fila donde esta, es decir, en C4, ahora que coja un alto de 8 filas, que son los productos que hay, y, un ancho que lo indica la función MES, en este caso, tres, y, que sume el resultado.
Como resultado obtengo el valor:
Ahora, hacemos lo mismo pero para el año 2021, la sintaxis es la misma lo único que va a cambiar es la celda de partida.
=SUMA(DESREF(C15;0;0;8;MES(HOY())))
Y obtengo:
A simple vista, podemos ver hasta el mes actual hay más ventas en 2021 que en 2020, pero como la tenía que presentar, quería que se viera gráficamente.
Entonces, en una hoja nueva lo haremos.
Primero, vamos a realizar un gráfico donde se va a ver las ventas de cada mes hasta el mes actual, para ello, debemos de crear nombres de rangos.
Crearemos un nombre de rango que me seleccione las ventas hasta el mes actual del año 2020.
Para graficar todos los productos, pensé, que voy a crear un modelo nuevo al lado, solo con los valores hasta el mes actual.
La fórmula para el primer producto es:
=DESREF(C4;0;0;1;MES(HOY()))
Como no tengo que fijar nada, porque necesito que se actualice C4, para que vaya tomando el siguiente producto, pues arrastro la fórmula, y, ya tengo las ventas de cada producto hasta el mes actual.
Para el segundo año, la fórmula es la misma, solo debemos de cambiar la celda origen.
=DESREF(C15;0;0;1;MES(HOY()))
Bien, ya tenemos los datos para graficar.
Este hombre lo que quiere es comparar las ventas del producto que elija del año 2021 respecto al año 2021.
Primero voy a crear una validación de datos en A1, para seleccionar el producto.
Para mostrar las ventas hasta el mes actual, uso la siguiente sintaxis:
=DESREF(DESREF3!C3;COINCIDIR($B$1;DESREF3!$B$4:$B$11;0);0;1;MES(HOY()))
¿Por qué uso la función COINCIIDR en el argumento fila de la función DESREF?
Al seleccionar un producto, esa debe de ser la fila de partida, y, COINCIDIR nos resuelve el problema.
Entonces, le estamos diciendo que a partir de la celda C3, se mueva el número de filas que devuelve COINCIDIR, que se mueva cero columnas, que tome un alto de una fila, porque lo vamos a ser en esa fila, y, un ancho que lo decide la función MES.
Al pulsar enter, obtenemos una matriz derramada con los valores hasta el mes actual.
Para obtener los valores del año 2021, la fórmula es la misma, pero solo tenemos que cambiar la celda de partida.
Ya tenemos las ventas del producto seleccionado hasta el mes actual.
Ahora, vamos a graficarlo, para ello, vamos a crear dos nombres de rangos, uno se va a llamar producto2020, que contendrá la fórmula que hemos usado para extraer los valores de 2020.
Y otro, lo vamos a llamar producto2021, que contendrá la fórmula que hemos creado para las cantidades de 2021.
Debemos de no olvidar fijas las celdas en absoluta para crear los nombres de rangos, sino al usarlos en otra ubicación se actualizarán las celdas, y, los valores no serán los correctos.
Lo siguiente va a ser insertar un gráfico, pero en blanco, en este caso lo voy a insertar de líneas.
Una vez insertado el grafico, se habilita la pestaña diseño de gráfico, hacemos clic en seleccionar datos, y, clic en agregar.
En valores de la serie, ponemos:
Y como nombre de la serie:
Aceptamos, y, agregamos la segunda serie:
Ya lo tenemos graficado, le voy a quitar las líneas de división, el título, y, el eje vertical.
Lo siguiente que voy a hacer es crear un nombre de rango para poner el titulo con los meses hasta el actual.
La función quedaría:
=DESREF(C3;0;0;1;MES(HOY()))
La copio, y, voy a crear un nombre de rango, que se va a llamar, por ejemplo, meses.
Selecciono el grafico, y, hago clic en seleccionar datos.
En la ventana que se abre hago clic en editar del eje horizontal, y, pongo lo siguiente.
Esta etiqueta también es ya dinámica, se va a rellenar hasta el mes actual.
Voy a quitar las líneas de división a la hoja, y, el borde al gráfico.
Le voy a cambiar el formato a los meses.
Selecciono de nuevo el grafico, dentro de la pestaña diseño de gráfico, despliego agregar elemento al gráfico, y, elijo líneas de unión.
Selecciono las líneas de unión y le doy un color sólido, por ejemplo, azul.
Ahora, tenemos que representar la diferencia porcentual entre el año 2021 sobre el año 2020.
Como ya tenemos los nombres de rangos, que nos devuelven las cantidades hasta el mes actual, que son producto2020 y producto2021, pues la operación a realizar para calcular el porcentaje es la siguiente:
=(SUMA(producto2021)-SUMA(Producto2020))/SUMA(producto2021)
Con esto ya tenemos la diferencia porcentual de 2021 sobre el año 2020 hasta el mes actual.
Pero, primero en otra celda, vamos a calcular la diferencia del 100% menos la diferencia porcentual que hemos obtenido.
Ahora, vamos a graficarlo con un gráfico de anillo, para ello, seleccionamos ambas cantidades, vamos a la pestaña insertar, e, insertamos el grafico de anillo.
Quitamos título, leyenda, y, borde.
Redimensionamos el grafico para que quede al lado del otro.
Hacemos clic sobre el anillo más pequeño, volvemos a ser clic sobre él, y, le damos un fondo.
Ahora, al lado del grafico de anillo, insertamos una elipse más o menos del mismo tamaño que el centro del grafico de anillo.
Le ponemos el mismo formato que el anillo más grande.
Seleccionamos ambos gráficos.
Vamos a la pestaña formato de forma.
Desplegamos alinear, y, elegimos alinear verticalmente, y, horizontalmente.
Desplegamos agrupar, y, elegimos agrupar, con esto conseguimos que las dos formas se hagan una.
Seleccionamos la elipse, y, en la barra de fórmulas, escribimos el signo igual, y, señalamos la celda con la diferencia porcentual, y, aceptamos.
Le cambiamos el formato centrándola, y, cambiándole el color.
Pues ya lo tenemos, si vamos eligiendo un producto, veremos cómo se actualiza el grafico mostrando la diferencia porcentual gráficamente.
Alineamos los dos gráficos para una mejor presentación.
Este hombre, por lo menos, triunfo con esta presentación.
También, podemos hacer que en la hoja DESREF3, del encabezado donde aparecen los nombres de los meses, se ponga de otro color cuando sea igual al mes actual, para ello, seleccionamos el encabezado del año 2020, desplegamos formato condicional, y, elegimos nueva regla, y, escribimos lo siguiente:
En mi caso le voy a poner una letra blanca negrita, sobre negro.
También, podría saber cuál es el producto más vendido hasta el mes actual, para ello, primero voy a calcular el valor máximo del resultado de la función DESREF, con la siguiente sintaxis:
=MAX(DESREF(C4;0;0;8;MES(HOY())))
Ahora, debo de comparar el resultado de la función MAX con los valores devueltos por la función DESREF, y, si hay coincidencia, pues que me devuelva la fila, en caso contrario, pues que me ponga un texto en blanco, para ello, uso la siguiente sintaxis:
Pero antes, como la siguiente función la voy a usar más de una vez, que es la siguiente:
=DESREF(C4;0;0;8;MES(HOY())))
Pues, voy a crear una función personalizada, para ello, vamos a la pestaña de fórmulas, hacemos clic en asignar nombre, le ponemos por ejemplo, resaltar, y en se refiere a, pegamos la formula.
Entonces, para saber cuál es el valor máximo, puedo usar la siguiente sintaxis:
=MAX(resaltar)
Vemos que hemos usado el nombre de rango que acabamos de crear.
Bien, con esta función que es con la que hemos creado el nombre de rango.
=DESREF(C4;0;0;8;MES(HOY())))
Me va a devolver los valores del año 2020 hasta el mes actual.
Quiere decir que si uso la siguiente sintaxis:
=SI(MAX(DESREF(C4;0;0;8;MES(HOY())))=DESREF(C4;0;0;8;MES(HOY()));FILA(C4:C11);"")
Pero, claro, no me lo devuelve en la primera celda, para ello, puedo usar delante la función MAX, de la siguiente manera:
=MAX(SI(MAX(DESREF(C4;0;0;8;MES(HOY())))=DESREF(C4;0;0;8;MES(HOY()));FILA(C4:C11);""))
Ya por último, con la función índice, vamos a ir a la fila que nos ha devuelto.
=INDICE(B:B;MAX(SI(MAX(DESREF(C4;0;0;8;MES(HOY())))=DESREF(C4;0;0;8;MES(HOY()));FILA(C4:C11);"")))
Pues ya tenemos el producto más vendido de 2020 hasta el mes actual, para el año 2021,es la misma sintaxis pero cambiamos la celda de partida.
Veamos otro ejemplo de desref con modelos de coches.
Vamos a ver cómo crear un gráfico dinámico, tenemos los siguientes datos.
Insertamos un gráfico.
Lo que queremos es que el grafico me muestre la información respecto a la siguiente cantidad.
Si pongo el valor 2, por ejemplo, debería de mostrarme enero y febrero solamente.
Para construir esto, debemos de tener dos rangos usando la función DESREF, esto lo hacemos de la siguiente manera.
Vamos a crear un rango, que se va a llamar EtiquetasDinamicas., entramos a Formulas – Asignar nombre.
En esta parte de aquí.
Vamos a definir como se compone ese rango, usando la función DESREF, seria, estando en la celda B3, bajo cero filas, cero columnas, pero tomo un rango de este tamaño.
Y, además, con un ancho de una columna.
Vamos a ver como se comprueba esto, vamos a Formulas – Administrador de nombres.
Nos colocamos aquí.
Pinchamos en.
Vemos que, al pinchar en la serie, se selecciona el rango en la hoja de cálculo, por lo que sería dinámico, como tenemos puesto el número 9 en Cantidad, se seleccionan los primeros nueve meses.
Ahora, nos falta el rango dinámico de los valores, volvemos a Formulas- Asignar nombre, le vamos a llamar RangoDinamico.
Utilizaremos la siguiente función.
En este caso el origen es C3.
El resto de la fórmula es igual a la anterior.
Lo único que nos queda es modificar el grafico, para ello, nos colocamos en el gráfico, entramos a Diseño – Modificar datos.
Donde tenemos las series, vamos a modificarla, el nombre está bien, pero la serie de datos hace referencia a un rango fijo, entonces, voy a borrar ese rango.
Voy a colocar el RangoDinamico que hemos creado, pulsamos <F3>, y, aparecerán los nombres de rangos que hay creado.
Elegimos RangoDinamico.
Aceptamos.
Ahora, vamos con las etiquetas, y, editamos.
Borramos la parte fija, y, nos traemos el nombre de rango llamado EtiquetasDinamicas.
Aceptamos.
Aceptamos de nuevo.
Ahora, por ejemplo, si elegimos el valor de cinco, veremos que el grafico se actualiza, y, solo aparecerán los cinco primeros meses.
Ahora, tenemos un gráfico dinámico, utilizando como base la función DESREF.
Opmerkingen