Aprovecha la potencia de los nombres de rango dinámicos

Si trabajas habitualmente con Excel, es probable que hayas podido comprobar lo útiles que resultan los nombres de rango a la hora de aportar claridad a una hoja de cálculo.

No obstante, cuando trabajamos con tablas o listas que, normalmente, van creciendo puede parecer que el uso de nombres de rango no es adecuado. Sin embargo, sí que es posible, combinando esta característica con funciones. Así que veámoslo 🙂

El proceso de creación

Vamos a utilizar un ejemplo sencillo que nos permite detectar cómo el nombre de rango se expande automáticamente. Para ello, empezamos escribiendo distintas cantidades numéricas en una hoja de cálculo. Por ejemplo:

Datos para un nombre de rango dinámico

En este caso el punto en partida es sencillo, ya que si en cualquier celda escribimos =SUMA(A1:C3) el resultado será 107. Ahora bien, otra forma de utilizar todo ese conjunto de cantidades es a través de la función DESREF, que nos permite hacer referencia a un rango a partir de una determinada celda; en consecuencia la función =SUMA(DESREF(A1;0;0;3;3)) da el mismo resultado

Ten en cuenta que, en la función DESREF, tras la celda de referencia (en este caso A1), indicamos 0,0 para que no se mueva, y a continuación especificamos que el rango a obtener, debe tener 3 filas de alto y 3 columnas de ancho.

Pues bien, al combinar DESREF con una función que nos permita contar cuántas filas y/o columnas con contenido existen desde la celda que tomamos como referencia, ya  tendremos la solución. Además, si suponemos que se deconoce el tipo de contenido que podemos encontrarnos utilizaremos CONTARA (que cuenta celdas rellenas), por lo que la función para obtener el rango ocupado quedará como:

SUMA(DESREF(A1;0;0;CONTARA(A:A);CONTARA(1:1)))

Si escribimos =SUMA(DESREF(A1;0;0;CONTARA(A:A);CONTARA(1:1))) verás que el resultado sigue siendo el mismo, 107. Pero si añadimos los valores 10, 15 y 32 en la fila 4, verás como esta última fórmula se actualiza automáticamente a 164, mientras que las otras permanecen igual.

Llegados a este punto, sólo nos queda aplicar lo que acabamos de exponer. Por lo tanto, para crear un nombre de rango dinámico que se ajuste en todo momento al contenido real, vamos a la cinta Fórmulas, y en el Administrador de nombres, hacemos clic en Nuevo. Tras asignar el nombre y el ámbito de aplicación, escribimos la función DESREF correspondiente, de la siguiente forma:

Nuevo nombre de rango dinámico

Y ya podrás utilizar el nombre creado en fórmulas con lenguaje natural. Es decir, si ahora escribimos =SUMA(numeros) el resultado que aparecerá, como no podía ser de otra manera, será 164.

Por supuesto, si fuera necesario, sería ideal FIJAR tanto la celda A1, como las referencias a la columna A y la fila 1, todo dependerá de tus necesidades; pero en cualquier caso espero que te haya resultado útil!

Tres técnicas de cálculo para resolver un mismo supuesto en Excel

Para ver la potencia de cálculo que Excel es capaz de desplegar, vamos a plantear en el mismo problema resolviéndolo de tres formas completamente distintas.

El modelo inicial con el que vamos a trabajar es el siguiente y a continuación describiremos tres métodos para resolver el problema y dar contenido a las celdas vacías.
Supuesto de partida para técnicas de calculo

REFERENCIAS ABSOLUTAS

Trabajando de la forma tradicional, para calcular el Subtotal correspondiente al artículo ratón, nos colocamos en D5 y escribimos la expresión =B5*C5 que, después podemos copiar para el resto de artículos, obteniendo el resultado de la siguiente imagen. Sin embargo, para el descuento en euros, comenzaríamos por la fórmula que está en la figura:

Resolución de ejercicio con referencias absolutas

El problema se plantea cuando nos damos cuenta que todos los artículos tienen el mismo descuento; por lo que si copiamos la fórmula hacia abajo tal y como está, saldrán errores de Excel y/o valores erróneos, ya que la referencia correspondiente a B2, luego se convertirá en B3 (vacía) y B4 (con texto).

Si queremos que, al copiar una fórmula, alguna de las celdas no se vean modificadas será necesario fijarlas, escribiendo un signo $ delante de la letra y otro delante del número, por lo que la expresión pasaría a ser =D5*$B$2.

Truqui: si te da coraje pulsar May + 4, con el curso en B2 puedes pulsar la tecla F4 y Excel pondrá automáticamente los $ necesarios.

Al copiar esta vez la fórmula, el resultado sería el correcto y el mismo procedimiento tendríamos que repetirlo para la cuota de IVA, de forma que el resultado final será el siguiente:

Ejercicio resuelto mediante referencias absolutas

NOMBRES DE RANGO

Una opción al método anterior para evitar el uso de referencias absolutas, es emplear nombres de rango, que consiste en asignar un nombre a la/s celda/s que nos interesen, de la siguiente forma.

Comenzamos por colocarnos en B10, y a continuación hacemos clic en el cuadro de nombres (el primer apartado de la barra de fórmulas, donde precisamente pone B10, ahora sólo debemos escribir el nombre (sin espacios, caracteres extraños…) y pulsar Intro.

Para nuestro ejemplo, hemos asignado un nombre a las celdas B10 (dto) y E10 (iva). Sabrás que los nombres están bien asignados, si al hacer clic en la lista desplegable del cuadro de nombres, aparecen los que hayas creado.

Nombres de rango disponibles en una hoja de cálculoAhora, ya podemos escribir las expresiones para hallar el descuento en euros y la cuota de IVA, utilizando lo que Excel denomina como lenguaje natural. De forma que ahora la fórmula que tenemos que escribir en E13 quedará como =D13*dto y para G13, escribiremos =F13*iva. Estas dos expresiones podrán copiarse para las filas inferiores, y el resultado que conseguiremos será el mismo que en el caso anterior.

Utilizando nombres de rango en fórmulasSi te equivocas al asignar un nombre, en Insertar/Nombre/Definir podrás modificar la celda a la que hace referencia o, en caso, de error en el propio nombre, eliminarlo e incluso volverlo a crear.

CÁLCULOS MATRICIALES

Aún nos queda un tercer método, que una vez conocido y practicado con asiduidad nos permite desarrollar velozmente nuestros modelos: los cálculos matriciales.

A diferencia de las formas anteriores, las expresiones con matrices se pueden emplear en multitud de ocasiones, casi siempre que tengamos un grupo de celdas con el mismo tipo de cálculo, por lo que nuestro ejemplo anterior resulta perfecto como muestra.

Cuan vamos a trabajar con matrices, resulta fundamental seguir los pasos correctamente:

  1. Seleccionar todas las celdas que van a tener un resultado
  2. Sin hacer clic en ninguna parte escribir la fórmula con todas las celdas implicadas
  3. En lugar de Intro, pulsaremos Control + May + Intro al mismo tiempo.
  4. Si el proceso se ha hecho correctamente, en la barra de fórmula, la expresión aparecerá escrita entre llaves.

Vamos a ver cómo obtenemos los resultados para Subtotal siguiendo este método. Comenzamos por seleccionar todas las celdas que al final queremos que tengan uno de los subtotales:

Para una formula matricial en primer lugar hay que seleccionar las celdas

A continuación, empezamos a escribir la expresión, en nuestro caso:

  • Escribimos el signo igual
  • Seleccionamos todas las cantidades
  • Escribimos el signo de la multiplicación
  • Seleccionamos todos los precios

Composición de un cálculo matricial

Tras pulsar Control +May + Intro, obtendremos el resultado correspondiente. De forma similar a ésta, procedemos para averiguar los descuentos para nuestros artículos:

Ejemplo de cálculo de vector por constante

Siguiendo con esta forma de trabajar, daríamos contenido a toda la hoja progresivamente y a diferencia de los métodos anteriores, sin tener que copiar las fórmulas.

Únicamente debes tener en cuenta un detalle, en caso de que tengas que modificar/eliminar la fórmula, tendrás que seleccionar todas las celdas implicadas, realizar los cambios y volver a fijar con Control +May + Intro.

Si lo deseas, puedes descargarte el libro opstrabajo.xls, con todos los métodos desarrollados.