La función Desref y sus aplicaciones en Excel - 2a parte

Hola buenos días, buenas tardes o buenas noches, según os pille.

La semana pasada escribimos sobre la increíble función DESREF (aquí dejo la entrada en caso de que no pudieras leerla http://elsabiodeexcel.blogspot.com/2017/08/la-funcion-desref-y-sus-aplicaciones-Excel-1.html) y el feedback que he recibido ha sido muy bueno. Muchas gracias! Se agradece trabajar así cuando veo que los lectores aprecian el esfuerzo hecho (también se puede agradecer de otra manera, mira el final de esta entrada). No olviden que requiere mucho esfuerzo sacar tiempo para investigar y escribir para que vosotros los lectores puedan aprender de una manera gratuita.

Bueno, pues hoy vemos cómo podemos aplicar esta incredible función DESREF.

Aplicaciones de DESREF

1. Modelación Financiera

Aquí tenemos nuevos activos que se agregan en diferentes años y tenemos que aplicar un método de amortización lineal (Amortización fija, es decir, Activo / Vida) cada año. En un ejemplo así es cómo podemos usar DESREF.

• Dado que nuestros activos están completamente agotados cada 3 años sólo queremos sumar los activos de los últimos 3 años
• Utilizamos DESREF
• Referencia o punto de partida - C10 (año actual)
• Filas - No queremos mover ninguna fila
• Columnas - No queremos mover ninguna columna
• Altura - No queremos filas como altura
• Ancho - Aquí se especifica -3. ¿POR QUÉ? Porque queremos hacer una matriz de los últimos 3 años de activos y agregarlos, así que hacemos nuestro desplazamiento en la función SUM
• Ahora solo divida el saldo del activo por 3 (vida del activo) y obtendrá la amortización

Esta es una de las maneras fáciles de hacerlo!

2. Gráficos dinámicos

Como se puede ver en la imagen, cuando se a ñaden nuevos años el gráfico no se actualiza. Veamos cómo podemos resolver este problema usando DESREF.

• Creamos rangos dinámicos usando DESREF para los años
• Referencia - Comenzamos a contar los datos (celdas no vacías) desde $A$19 (fijado)
• Filas: sin filas que desplazarse
• Columnas - No hay columnas que desplazarse
• Altura - Queremos tener todas las celdas no vacías en un rango para el que usamos = CONTARA (En un rango un poco más grande que nuestros datos). Esto va a dar el recuento de las celdas llenas (en nuestro caso 6)
• Ancho - Sin ancho
• El desplazamiento creará una matriz de 6 números. Cuando presione ingresar, el desplazamiento le dará #VALUE! Error (ya que no puede mostrar seis números en una celda) pero está bien
• Creamos de manera similar un rango de Ventas

Ahora se crean dos rangos dinámicos mediante la asignación de nombres

• Simplemente haga clic en Asignar nombre en la pestaña de fórmulas. Dismiss
• Pegue la fórmula en la región Se refiere a (y asegúrate de tener todas las celdas bloqueadas)
• Dar un nombre adecuado
• Repita este proceso para el rango de ventas también. 

Ahora tenemos 2 rangos con nombre. 

Ahora sólo hay que vincular los rangos con nombre al gráfico.

Es importante resaltar que el nombre del archivo (en mi caso Desref.xlsx) ha de incluirse antes de poner el nombre del rango que hemos creado.

Y ahora ya lo tenemos!!!

Una cosa más que añadir!

La función DESREF y su naturaleza volátil: La función DESREF se vuelve a recalcular cada vez que Excel recalcule. Para entendernos bien, esta función mantiene su Excel ocupado ejecutando la fórmula de desplazamiento una y otra vez, lo que puede ser bastante peligroso si se está trabajando con datos pesados o modelos financieros. 

Te ha gustado el artículo? 

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:
1. Puedes dejar una donación pinchando en la siguiente imagen.

2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.

Consultoría Excel / Cursos de Excel

El Sabio de Excel busca colaborades del blog

Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+