La función DESREF

Vamos con la función DESREF.

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.

· 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

· como resultado.

· Ancho (opcional): El número de columnas que deseamos que nos

· devuelva como resultado.

Imaginemos que podemos dar a Excel un conjunto de instrucciones para ir a una celda determinada.

Tenemos los siguientes datos.

Vamos a partir de la celda ORIGEN, que es la celda D10.

Queremos llegar a estas celdas, con los valores A, B, C, D, y, E.

Vamos a ver cómo nos vamos a mover, cuantas filas, cuantas columnas tenemos que movernos.

Para esto, hay 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.

Obtenemos la letra A.

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 abajo, por lo que dicho número debemos de ponerlo en negativo.

Para llegar a la celda que contiene el valor de D, debemos de movernos 1 columna hacia la izquierda, y, 6 filas hacia arriba, ambos valores deben de ser negativos.

Para llegar a la letra E, debemos de movernos cuatro filas hacia arriba, lo que es valor es negativo, y, una columna a la derecha.

Bien, esto 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 escribimos, =DESREF(A6;3;2), le estamos diciendo que a partir de la celda A6, se mueva tres filas hacia abajo, y, y dos columnas a la derecha.

Nos devuelve el valor 306.

Ahora, vamos a usar el cuarto y quinto argumento, el cuarto argumento es alto, quiere decir, a partir de una celda, el rango hacia abajo, o, hacia arriba que debe de tomar, y, como ultimo argumento es ancho, es decir, numero de columnas, a la derecha, o, a la izquierda que debe de tomar, si usamos la siguiente sintaxis:

=SUMA(DESREF(I6;3;2;2;2))

Le estamos 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.

Si no usamos la función SUMA, DESREF nos devolverá más de un valor, 306, 349, 190, y, 102, por lo que nos devolverá un error, pero como queremos el total, pues, delante ponemos la función SUMA.

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 primer y segundo año, el cual, me comentaba que era una tarea tediosa, si había alguna manera de poder automatizarlo.

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 en la celda C1, 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, donde como punto de partida es la celda C4, que es la primera cantidad, cantidad que corresponde al mes de enero del producto1.

Como argumento filas, ponemos 0.

Como argumento columna, ponemos 0.

Como argumento alto, ponemos 8, que son los productos que tenemos.

Y, como argumento ancho, usamos la función MES aplicada a la función HOY.

Cerramos paréntesis.

=SUMA(DESREF(C4;0;0;8;MES(HOY())))

Obtenemos:

Ahora, hacemos lo mismo, pero para el siguiente año, la sintaxis es la misma lo único que va a cambiar es la celda de partida, en vez de C4 es C15.

=SUMA(DESREF(C15;0;0;8;MES(HOY())))

Y obtenemos:

También, quería saber cuál es el producto más vendido hasta el mes actual, para ello, primero vamos a calcular el valor máximo del resultado de la función DESREF anterior, pero sin la función SUMA, con la siguiente sintaxis, donde, nos movemos cero filas y columnas, como argumento alto, ponemos 8, que son los productos que hay, como argumento ancho, usamos MES junto con HOY.

Cerramos paréntesis.

=MAX(DESREF(C4;0;0;8;MES(HOY())))

La vamos a usar más de una vez, vamos a crear una función personalizada, para ello, seleccionamos la expresión, pulsamos CTRL más C para copiar, pulsamos Escape, vamos a la pestaña de fórmulas, dentro de nombres definidos, hacemos clic en asignar nombre.

Como nombre, ponemos, por ejemplo, resaltar, y en la ventana se refiere a, pegamos la formula con CTRL más V, y, fijamos C4 con F4.

Aceptamos.

Vamos a la celda J25, donde calculamos el valor máximo, y, cambiamos DESREF por el nombre de rango Resaltar.

=MAX(Resaltar)

Si en una celda, escribimos el nombre de rango Resaltar, nos va a devolver los valores de los productos hasta el mes de marzo, hoy es 12/03/2023.

Entonces, vamos a comparar el valor máximo devuelto por Resaltar con los valores devueltos por Resaltar.

=SI(MAX(Resaltar)=Resaltar

En caso de coincidencia, que nos devuelva la fila del rango C4:C11, en caso contrario, que nos devuelve un texto en blanco.

Cerramos paréntesis.

=SI(MAX(Resaltar)=Resaltar;FILA(C4:C11);"")

Esta función nos va a devolver mas de un valor, en donde no haya coincidencia, aparecerá blanco, y, donde hay coincidencia, aparecerá el número de fila.

Para quedarnos solo con el valor, usamos CONCAT, que omitirá las celdas en blanco.

=CONCAT(SI(MAX(Resaltar)=Resaltar;FILA(C4:C11);""))

Usamos INDICE para regresar el valor de fila devuelto en la columna productos, donde omitimos el argumento numero de columna, y, de forma predeterminada nos devolverá una columna.

=INDICE(B:B;CONCAT(SI(MAX(Resaltar)=Resaltar;FILA(C4:C11);"")))

Obtenemos el producto de más ventas.

Para el producto mas vendido para el siguiente año, decir, que el primer año lo esto consiguiendo, extrayendo el año de la fecha actual y restando 1, y, para el siguiente año, es la misma función, pero sin restar 1.

=AÑO(HOY())-1

Como iba diciendo, debemos de calcular el producto más vendido para el siguiente año, la expresión es la misma que hemos usado anteriormente:

=DESREF(Ejemplo3_resuelto!$C$4;0;0;8;MES(HOY()))

Pero, en vez de la celda C4, ponemos la celda C15.

Seguimos los pasos seguidos anteriormente para crear el nombre de rango Resaltar, lo llamamos Resaltar2, y, en la ventana se refiere a, ponemos la expresión.

Aceptamos.

Copiamos la expresión usada para el cálculo del producto más vendido para el primer año, la pegamos en la celda J26, cambiamos el nombre de rango Resaltar por Resaltar2, también, debemos de cambiar el rango en la función FILA, debemos de seleccionar las filas para del año siguiente.

=INDICE(B:B;CONCAT(SI(MAX(Resaltar2)=Resaltar2;FILA(C15:C22);"")))

Aceptamos, y, tenemos el producto mas vendido para el siguiente año.

También, quería que se viera gráficamente las ventas para ambos años, pero de un producto seleccionado.

Añadimos una hoja nueva.

Vamos a realizar un gráfico donde se va a ver las ventas de cada mes hasta el mes actual por un producto seleccionado.

Primero, vamos a crear una lista desplegable a través de una validación de datos, para elegir un producto, para ello, vamos a la pestaña datos, dentro de obtener y transformar datos, hacemos clic en validación de datos.

Desplegamos permitir, seleccionamos lista, en la ventana origen seleccionamos los productos de la hoja Ejemplo3_resuelto, los productos son los mismos para los dos años.

Aceptamos.

Seleccionamos un producto.

Vamos a obtener los valores del producto seleccionado para ambos años, buscaremos la posición del producto seleccionado para el primer año, para ello, usamos COINCIDIR, donde como argumento valor buscado es el producto seleccionado.

=COINCIDIR(G2

Punto y coma, como argumento matriz buscada, seleccionamos los productos para el primer año.

=COINCIDIR(G2;Ejemplo3_resuelto!B4:B11

Punto y coma, como argumento tipo de coincidencia, seleccionamos exacta.

Cerramos paréntesis.

=COINCIDIR(G2;Ejemplo3_resuelto!B4:B11;0)

Ya tenemos el argumento filas de DESREF, pues, ponemos dicha función, como punto de partida es la celda C4 de la hoja Ejemplo3_resuelto.

=DESREF(Ejemplo3_resuelto!C4

Punto y coma, como argumento filas, es la función COINCIDIR, pero debemos de restar 1, en caso contrario, nos devolverá una fila más.

=DESREF(Ejemplo3_resuelto!C4;COINCIDIR(G2;Ejemplo3_resuelto!B4:B11;0)-1;0;1;MES(HOY()))

Punto y coma, como argumento columnas, ponemos 0, como argumento alto, ponemos 1, y, como argumento ancho es la función MES.

=DESREF(Ejemplo3_resuelto!C4;COINCIDIR(G2;Ejemplo3_resuelto!B4:B11;0)-1;0;1;MES(HOY()))

Aceptamos, y, tenemos los valores para el primer año del producto seleccionado.

Para el siguiente año, la expresión es la misma, pero debemos de cambiar C4 por C15, no cambiamos el rango B4:B11, porque la posición de los productos son las mismas en ambos años.

=DESREF(Ejemplo3_resuelto!C15;COINCIDIR(G2;Ejemplo3_resuelto!B4:B11;0)-1;0;1;MES(HOY()))

Aceptamos, y, tenemos las ventas del producto seleccionado para ambos años.

Vamos a graficarlo, para ello, vamos a crear dos nombres de rangos, uno se va a llamar Primer_producto, que contendrá la fórmula que hemos usado para extraer los valores del producto seleccionado para el primer año.

Para ello, vamos a la pestaña de fórmulas, dentro de nombres definidos, hacemos clic en asignar nombre.

Como nombre, ponemos Primer_producto, y, en la ventana se refiere a, pegamos la expresión.

Creamos un segundo nombre de rango para las ventas del segundo año.

Lo siguiente es graficar.

En una celda en blanco, vamos a la pestaña de insertar, dentro de gráficos, desplegamos grafico de líneas o de áreas, y, seleccionamos línea con marcadores.

Se habilitan las pestañas diseño de gráfico, y, formato.

Vamos a la pestaña de diseño de gráfico, hacemos clic en seleccionar datos.

Se abre la ventana seleccionar origen de datos.

Hacemos clic en agregar dentro de Entradas de leyenda (Series).

En la ventana valores de serie, debemos de poner el nombre del libro, seguido de exclamación (¡) y el nombre de rango a usar, si no nos acordamos del nombre de rango usar, podemos pulsar F3, y, seleccionarlo.

Aceptamos por dos veces.

Ya tenemos graficado el primer año.

Seleccionamos la expresión donde calculamos el total para el primer año hasta la fecha actual, fijamos C4.

Repetimos los pasos, y, graficamos el año siguiente.

Ya tenemos nuestros datos graficados.

En leyendas del eje horizontal, en vez de que aparezca 1, 2, y, 3, queremos que aparezcan los nombres de los meses, para ello, vamos a crear una lista con los meses.

Si usamos la siguiente expresión, MES(HOY()), nos devuelve el número de mes, si usamos DESREF, donde como punto de partida, es el nombre meses (M1), y, como argumento filas, ponemos 1, como argumento columnas, ponemos cero, como argumento alto es la función MES, y, como argumento ancho, ponemos cero, obtendremos los meses que deben de aparecer dentro del gráfico.

Fijamos M1.

=DESREF(M1;1;0;MES(HOY());1)

Creamos otro nombre de rango, lo llamamos nombre_meses, y, en la ventana se refiere a, pegamos la expresión.

Aceptamos, y, tenemos los nombres de los meses graficados, pero de forma dinámica.

Hacemos clic con botón alternativo de ratón sobre el eje vertical, donde tenemos los valores, y, seleccionamos dar formato al eje.

Se abre a la derecha la ventana dar formato a eje.

Desplegamos número, en la ventana categoría, seleccionamos moneda, y, en posiciones decimales, ponemos cero.

Ya tenemos la moneda de nuestro país.

Creamos un nuevo título para el grafico.

="Producto seleccionado hasta el mes " & TEXTO(HOY();"mmmm") & " del año " & Ejemplo3_resuelto!B3 & "/" & Ejemplo3_resuelto!B14

Aceptamos, y, tenemos el título de forma dinámica.

Seleccionamos el título, vamos a la barra de fórmulas, y, hacemos clic en la celda O1, y, tenemos el título del gráfico.

Seleccionamos las líneas de división.

Pulsamos suprimir.

Hacemos doble clic en uno de los lados del gráfico, a la derecha se abre la ventana formato del área del gráfico, desplegamos borde, y, seleccionamos sin línea.

Vamos a la pestaña vista, y, desmarcamos líneas de cuadricula.

Redimensionamos el grafico.

Y ya lo tenemos.

También, quería ver graficado de forma clara, la diferencia porcentual entre las ventas hasta el mes actual del producto seleccionado.

En la celda H4, sumamos el nombre de rango Primer_producto, lo restamos a la suma Segundo_producto, y, lo dividimos entre la suma de Primer_producto.

=(SUMA(Primer_producto)-SUMA(Segundo_producto))/SUMA(Primer_producto)

Lo ponemos en formato de porcentaje sin decimales.

Restamos al 100% el resultado obtenido.

=100%-H4

Ya tenemos los datos a graficar.

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 debajo del primero.

Hacemos clic sobre el anillo más pequeño, volvemos a ser clic sobre él, vamos a la pestaña formato, desplegamos relleno de forma, y, seleccionamos un fondo.

Volvemos a desplegar, y, desmarcamos sin relleno.

Le damos formato al siguiente anillo.

Quitamos el borde.

Al lado del grafico de anillo, insertamos una elipse más o menos del mismo tamaño que el centro del grafico de anillo.

Ponemos el mismo formato que el anillo más grande.

Seleccionamos ambos gráficos, seleccionado el primero, dejando pulsada la tecla CTRL y seleccionado el segundo gráfico.

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.

Seleccionamos la diferencia porcentual dentro de la elipse, la centramos, la ponemos en color blanco en negrita, y, aumentamos el tamaño.

Y ya lo tenemos.

Cálculo de acumulados con la función DESREF

Hemos visto varios ejemplos, con distintas funciones, donde hemos calculado acumulados, pues, ahora, vamos a crear un acumulado, con la función DESREF.

Tenemos los siguientes datos, aunque veamos los meses, estos meses corresponden a fechas, que van desde el día 1 de enero del año 2020, hasta el 31 de diciembre del año 2021.

Para que me devuelva el nombre del mes, he seleccionado las celdas, he pulsado, CTRL más 1, para ir a formato de celdas, en la pestaña número, dentro del grupo, categoría, he seleccionado personalizada, y, en la ventana tipo, he puesto “MMMM”, para que devuelva el nombre del mes.

Bien, fijándonos en el modelo, tenemos valores desde el mes de enero hasta diciembre, correspondiente al año 2020, y, a continuación, del mes de enero a diciembre, pero del año 2021.

Con estos datos, queremos crear un acumulado, pero por años, es decir, que cuando termine el acumulado del mes de enero del año 2020, para el mes de enero del año 2021, la cantidad debe de reiniciarse a cero.

Pues, empecemos.

Sabemos que un acumulado, es ir sumando las cantidades superiores, quiere decir, que el primer acumulado, es la primera cantidad, para el segundo acumulado, sería la suma de la primera cantidad más la segunda cantidad.

Para el siguiente acumulado, sería la suma de las tres primeras cantidades, y, así con el resto de las cantidades.

Si en la celda F3, escribimos la siguiente función, donde fijo la referencia D2, para que, al arrastrar, no se actualice.

=SUMA($D$2:D3)

Estoy sumando, desde nada, hasta el valor de la celda D3, es decir, 0 + 340.

Como resultado, obtengo, la cantidad de 340, correspondiente al mes de enero del año 2020.

Voy a arrastrar una vez, y, obtengo la cantidad de 545, que es la suma de las cantidades 340, y, 205, correspondientes a los meses de enero, y, febrero del año 2020.

Si miramos la función, al arrastrar, la celda D2 no se ha actualizado, entonces, ha sumado desde D2 a D4, es decir, está sumando las cantidades, 0, 340, y, 205, con esto tenemos nuestro segundo acumulado.

=SUMA($D$2:D4)

Arrastremos hasta el final.

Ya tenemos nuestro acumulado, pero ¿es lo que queríamos?

La respuesta es no, porque para enero del año 2021, el acumulado debía de haberse reiniciado a cero, y, no lo ha hecho, ha seguido sumando las cantidades, como vemos:

Para el mes de enero de 2021, ha sumado el acumulado del año 2020, más la cantidad de enero del año 2021.

Esto lo podíamos arreglar, poniendo un contador al lado de los meses.

El problema es que, con la función SUMA, no podemos hacerlo, porque debemos de movernos entre celdas, pero, ya conocemos la función DESREF, que si nos va a permitir movernos entre celdas.

Realmente, debemos de empezar desde cero el ejercicio, o, ejemplo.

Sabemos que con la función DESREF, especificamos un punto de partida, un numero de filas y columnas a movernos, y, un alto, y, un ancho.

Si en una celda, escribo la siguiente sintaxis:

=DESREF(D3;0;0;1;1)

Le estoy diciendo, que a partir de la celda D3, se mueva cero filas, y, cero columnas, por lo que se queda en la celda D3, ahora, que tome un alto de 1, también sigue en la misma celda, y, un ancho de 1, en conclusión, le estamos diciendo que se quede en la celda D3.

Si cambio el argumento alto, por 2.

=DESREF(D3;0;0;2;1)

Me devuelve una matriz desbordada, con los dos primeros valores.

Si englobamos la función DESREF, dentro de la función SUMA, tendremos un acumulado.

=SUMA(DESREF(D3;0;0;2;1))

Vemos que nos devuelve 545, que es la suma de las dos primeras cantidades.

Creo, que la primera cantidad, debería de ser 340, que corresponde al mes de enero del año 2020, es decir, al primer valor.

Recordamos, que, en la función SUMA, la primera celda a sumar era una celda donde no había nada, pues, aquí debemos de hacer lo mismo, pero en vez de señalar una celda que no tenga nada, el argumento alto de la función DESREF, lo voy a poner en negativo, que es lo mismo, pero, lo voy a poner en 1.

=SUMA(DESREF(D3;0;0;-1;1))

Vemos que obtenemos la primera cantidad, es lo mismo que hicimos en la función SUMA.

Si arrastro no obtendré el acumulado, porque el argumento alto, siempre va a valer 1, y, queremos que sea dinámico.

Podemos crear un contador al lado de cada mes.

Vamos a cambiar el argumento alto, por el valor de la celda B3, que es donde empieza el contador, lo seguimos dejando en negativo.

=SUMA(DESREF(D3;0;0;-B3;1))

Vemos que tenemos el primar valor, pues, ahora arrastramos, y, tenemos el mismo problema que antes, que no se ha iniciado el acumulado, en el mes de enero del año 2021.

Quiere decir que este contador, aparte, de no ser muy estético, no nos vale, entre otras cosas, porque no es dinámico, porque puede haber más o menos meses.

Pero, si podemos usar el número de mes, para ello, podemos usar la función MES, para ello, en el argumento alto, vamos a poner la función MES sobre la celda C3, que es el primer mes, debe de seguir en negativo.

=SUMA(DESREF(D3;0;0;-MES(C3);1))

Obtenemos la primera cantidad, pues, vamos a arrastrar.

Vemos que ahora el acumulado en el mes de enero del año 2021, toma la cantidad de dicho mes, es decir, se ha reiniciado a cero, porque en esta ocasión, quien manda es el número de mes, y, enero, siempre corresponderá con el número 1.

Si nos fijamos en la función de enero del año 2021, a partir de la celda D15, no se mueve ni filas, ni columnas, y, toma un alto de 1, y, un ancho de 1, por lo que queda en la misma celda.

=SUMA(DESREF(D15;0;0;-MES(C15);1)

Pues, hemos terminado nuestro ejemplo.

Cálculo de precio por producto

Para el siguiente ejemplo, sigo usando el modelo que suelo usar habitualmente, pero con menos columnas.

El modelo está en formato de tabla, y, se llama ventas.

Tenemos seis modelos, donde tenemos el nombre de la provincia, el centro, los productos vendidos en dichos centros, junto con sus precios, dependiendo del centro donde ha sido vendido el producto, el precio varia.

En la tabla ventas, debemos de añadir una nueva columna, con el precio del producto para cada centro comercial.

Por ejemplo, el primer centro se llama Sevilla_C.C. Nervión, pero en la tabla ventas, tenemos una columna con la provincia, y, otra columna con el producto.

Debemos de saber la posición que ocupa la primera provincia, y, centro, en el rango H2:H28, donde se encuentran las provincias, centro, productos, y, precios, para ello, voy a usar la función COINCIDIR, donde como argumento valor buscado, es la unión de provincia, junto con el guion bajo, junto la columna ventas.

Lo hare en una celda fuera de la tabla ventas.

=COINCIDIR(Ventas[@Provincia]&"_"&Ventas[@Centro]

Lo buscamos en el rango H2:H28, con una coincidencia exacta.

Cerramos paréntesis, y, aceptamos.

=COINCIDIR(Ventas[@Provincia]&"_"&Ventas[@Centro];H2:H28;0)

Para:

Obtenemos la posición 1.

Para resolver este ejemplo, vamos a usar la función DESREF, pues, vamos a movernos el número de filas devuelto por la función COINCIDIR, a partir de la celda H1.

=DESREF(H1;COINCIDIR(Ventas[@Provincia]&"_"&Ventas[@Centro];H2:H28;0)

Como argumento numero de columnas, no tenemos que movernos, por lo que pongo punto y coma, y, paso al siguiente argumento que es alto, pues ponemos tres.

=DESREF(H1;COINCIDIR(Ventas[@Provincia]&"_"&Ventas[@Centro];H2:H28;0);;3

Como argumento ancho, ponemos 2, cerramos paréntesis, y, aceptamos.

=DESREF(H1;COINCIDIR(Ventas[@Provincia]&"_"&Ventas[@Centro];H2:H28;0);;3;2)

Obtenemos una matriz desbordada de tres filas de alto, y, dos columnas de ancho, con el primer centro y sus productos.

Lo siguiente es buscar el primer producto de la tabla ventas, en la función DESREF, que nos devuelva la columna 2, con una coincidencia exacta.

=BUSCARV(Ventas[@Producto];DESREF(H1;COINCIDIR(Ventas[@Provincia]&"_"&Ventas[@Centro];H2:H28;0);;3;2);2;FALSO)

Obtenemos el precio del primer producto, ahora, movemos esta función a la celda E2, como está dentro de la tabla, se añade una nueva columna, y, se rellenan todas las celdas, por lo que ya tenemos el precio para cada producto, vendido en cada centro comercial.

Lista desplegable con DESREF

En el siguiente ejemplo, tenemos tres columnas, una con provincias, otra con centros comerciales, y, otra con productos.

Queremos crear una lista desplegable, donde vamos a seleccionar un encabezado, es decir, provincia, centro, o producto, y, una siguiente lista desplegable, donde se rellene con los elementos que pertenezcan al encabezado seleccionado.

Vamos a crear la primera lista, lo haremos en la celda B2, que es fácil, para ello, vamos a la pestaña datos, dentro del grupo, herramientas de datos, hacemos clic en validación de datos.

Se abre la ventana de validación de datos, desplegamos permitir, y, seleccionamos lista.

En la ventana origen, seleccionamos los encabezados.

Aceptamos.

Seleccionamos, por ejemplo, centro.

Pues aquí empieza el ejercicio, ahora, vamos a crear la segunda lista desplegable, con los elementos del centro que hemos seleccionado.

Me voy a colocar en la celda C2.

¿Qué debemos de hacer?

Pues, por ejemplo, si hemos elegido centro en la lista desplegable, quiero decir, que lo primero que debo de hacer, es moverme a dicho encabezado, que sería el encabezado número 2.

Ya conocemos la función COINCIDIR, que nos devuelve la posición de un elemento, dentro de un rango, pues, vamos a buscar en encabezado seleccionado, dentro de los tres encabezados, con una coincidencia exacta.

La sintaxis quedaría:

=COINCIDIR(B2;E1:G1;0)

Vemos que nos devuelve la posición dos, lo siguiente que debemos de hacer, es movernos a dicho encabezado.

=COINCIDIR(I2;E1:G1;0)

Lo siguiente es movernos, a dicho encabezado, sabemos que la a la función DESREF, debemos de indicar un punto de partida, y, numero de filas y columna a movernos, además, tiene otros dos argumentos, que son alto y ancho.

Nosotros vamos a partir de la celda E1.

Nos vamos a mover una fila, que sería el argumento filas, y, una columna, que es el argumento columnas.

=DESREF(E1;1;1)

Vemos que nos devuelve el centro comercial Nervión.

Para hacerlo dinámico, vamos a sustituir el argumento número de columnas de la función DESREF, por la función COINCIDIR.

=DESREF(E1;1;COINCIDIR(I2;E1:G1;0))

Obtenemos aspiradora.

¿Es correcto?

El resultado es correcto, pero no es lo que queremos, porque debería de haber devuelto centro comercial Nervión.

Veamos de donde sale este resultado.

Traduciendo la función DESREF anterior, teniendo seleccionado el encabezado de centro, los valores de los argumentos serian:

=DESREF(E1; 1; 2)

La función DESREF, cuenta un elemento cero, es decir, que el conteo de encabezados para la función DESREF, sería el siguiente:

Como la función COINCIDIR, nos ha devuelto el valor 2, nos ha devuelto el producto, por eso he dicho anteriormente, que el resultado es correcto, para solventar nuestro problema, a la función COINCIDIR, le vamos a restar 1.

=DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1)

Ahora, tenemos el resultado correcto.

Lo siguiente es decirle cuantas filas debe de tomar, y, cada columna tiene un numero de filas diferente, este seria el argumento alto de la función DESREF.

Por ejemplo, vamos a poner como argumento alto, el valor de 15.

=DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;20)

Pues, tenemos el mismo problema que antes, que donde no hay coincidencias, nos poner el valor de cero, y, si creamos una lista desplegable, esos valores no deseados, aparecerán.

La solución del problema es especificar el número de celdas a tomar en cada ocasión.

Y, si, ¿envuelvo la función DESREF, en la función CONTARA?

Deberíamos de contarnos, solo, las celdas ocupadas.

Probemos.

=CONTARA(DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;20))

Vemos que nos devuelve 6, que son los centros comerciales, que hay.

Si elijo producto, vemos que nos devuelve 5, que son los productos que hay.

Esto quiere decir, que ya tengo el argumento alto de la función DESREF, pues, copiemos y lo pegamos en el argumento alto de la función DESREF.

Como argumento ancho, ponemos 1, para que se quede en la misma columna.

=DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;CONTARA(DESREF(E1;1;COINCIDIR(I2;E1:G1;0)-1;20));1)

Aceptamos, y, podemos ver nuestra lista, solo con los elementos que la componen.

Pues en la celda J2, vamos a crear nuestra segunda lista desplegable, pero, primero copiamos la función, seleccionado, y, pulsando CTRL más C.

Dentro de la ventana de validación, en la ventana de origen, pegamos la función, con CTRL más V.

Aceptamos.

Vemos como se ha creado nuestra segunda lista desplegable, sin errores, si probamos, veremos como se rellana, únicamente, con los elementos del encabezado seleccionado.

Terminamos aquí, nuestro ejemplo.

Para el siguiente ejemplo, tenemos tres secciones, y, los productos que corresponden con cada sección.

Vamos a crear una lista desplegable donde seleccionemos una sección, después, crearemos otra lista desplegable, que deberá ser rellena con los productos de la sección seleccionada.

Para la primera lista, vamos a la pestaña de datos, dentro de obtener y transformar datos, hacemos clic en validación de datos, desplegamos permitir, y, seleccionamos lista, en la ventana de origen, seleccionamos las secciones.

Aceptamos, seleccionamos una sección.

Tenemos que saber dónde comienza la sección seleccionada, para ello, vamos a usar COINCIDIR, donde como argumento valor buscado es la sección seleccionada.

=COINCIDIR(H5

Punto y coma, como argumento matriz buscada, es el rango D6:D18.

=COINCIDIR(H5;D6:D18

Punto y coma, como argumento tipo de coincidencia, ponemos exacta.

Cerramos paréntesis.

=COINCIDIR(H5;D6:D18;0)

Aceptamos, y, tenemos la posición de la sección seleccionada.

Lo siguiente es saber cuantos elementos componen dicha sección, para ello, vamos a usar CONTAR.SI, donde como argumento rango es desde D6 a D18.

=CONTAR.SI(D6:D18

Punto y coma, como argumento criterio, es la sección seleccionada.

Cerramos paréntesis.

=CONTAR.SI(D6:D18;H5)

Aceptamos, como tengo seleccionada la seccion2, ha devuelto 4 elementos.

Pus, ya tenemos el argumento fila, y, alto de DESREF, la sintaxis quedaría:

=DESREF(E5;COINCIDIR(H5;D6:D18;0);0;CONTAR.SI(D6:D18;H5))

Vemos como se rellena con los elementos que corresponden a la lista.

Por último, copiamos la expresión, creamos una lista desplegable, y, en la ventana origen, pegamos la expresión.

Y ya tenemos nuestro ejercicio resuelto.

Veamos otro ejemplo, tenemos los meses desde enero a diciembre con unos valores para cada mes.

El modelo está en formato de rango.

Vamos a crear una columna con cada valor de la celda anterior, pero, no debemos de contar el encabezado.

En la celda C2, podemos DESREF, como argumento referencia, seleccionamos la celda A2, como argumento filas, ponemos -1, para que retroceda una fila, y, como argumento columnas, ponemos 1.

Cerramos paréntesis.

=DESREF(A2;-1;1)

Aceptamos, arrastramos, y, vemos que en la celda C2 aparece el encabezado valores, y, debería de aparecer cero.

Para solventarlo vamos a usar el condicional SI, donde como argumento prueba lógica, vamos a preguntar si la fila de la celda actual es mayor a 2, entonces, debe de ejecutar la función DESREF (valor si verdadero), en caso contrario, debe de poner el numero cero (valor si falso).

Cerramos paréntesis.

=SI(FILA()>2;DESREF(A2;-1;1);0)

Arrastramos, y, ya tenemos en cada celda el valor de la celda anterior, y, en la celda C2, vemos que aparece el numero cero.

Pero si queremos calcular la diferencia de ventas del mes de la celda actual menos las ventas del mes anterior, vamos a la celda C2, a la barra de fórmulas, en el argumento valor si verdadero, restamos con otro DESREF, pero como argumento referencia, ponemos la celda A1, como argumento filas ponemos 1, como argumento columnas, ponemos 1, y, cerramos paréntesis.

=SI(FILA()>2;DESREF(A2;-1;1)-DESREF(A1;1;1);0)

Pero, si queremos ver los meses donde hubo más ventas que el mes anterior, preguntamos si la expresión anterior es menor a cero, en ese caso, que devuelva el mes de la celda actual, en caso contrario, que devuelva un texto en blanco.

=SI(SI(FILA()>2;DESREF(A2;-1;1)-DESREF(A1;1;1);0)<0;[@Meses];"")

Miguel Angel Franco