Excel ens permet posar noms als rangs de cel·les de manera que els puguem identificar adequadament en usar-los en les nostres fórmules, però en aquesta ocasió et mostraré com fer que aquest nom es refereixi a un grup de cel·les que va en augment.
En definir un nom de rang comencem per seleccionar les dades i posteriorment assignar-los un nom. A la següent imatge pots observar que he assignat el nom mitabla al rang de cel·les A1: A3
Si afegeixo una nova dada per sota del rang definit no s’inclourà de manera automàtica:
Caldria redefinir el nom del rang per incloure la nova cel·la. No obstant això, podem fer que un nom de rang estigui definit per una fórmula i d’aquesta manera actualitzar automàticament les dades que han de ser inclosos.
Rangs dinàmics amb DESREF
Per aconseguir aquest objectiu utilitzarem la funció DESREF que ens permet crear una referència a un rang. Aquesta és la sintaxi de la funció:
DESREF (ref, files, columnes, [alt], [ample])
La sintaxi de la funció DESREF ens indica que el primer argument ha de ser la cel·la “inicial” sobre la qual es basarà la referència. Basant-se les dades de l’exemple anterior, col·locaré com a primer argument la cel A1 que és la primera cel·la amb dades. El segon i tercer argument de la funció ens permeten especificar quantes files i columnes ens mourem de la cel “inicial”. En el nostre exemple no volem moure’ns d’aquesta cel·la, així que aquests paràmetres seran sempre zero.
El quart i cinquè argument són l’alt i ample de la referència que volem crear i aquí és on ve la part interessant perquè volem dir a Excel que desitgem totes les cel·les que tenen un contingut. Per aconseguir la nostra comesa hem d’utilitzar la funció CONTARA, la qual ens ajuda a explicar les cel·les que no estan buides. Per explicar les files que no estan buides utilitzo la següent funció:
= CONTARA ($ A: $ A)
I per explicar les columnes que no estan buides:
= CONTARA ($ 1: $ 1)
Amb els paràmetres ja definits podem dir que farem servir la funció DESREF de la següent manera suposant que les dades es troben a la Full1:
= DESREF (Full1!$A$1;0;0;CONTARA(Full1!$A:$A);CONTARA(Full1!$1:$1))
Aquesta fórmula sempre ens retornarà el rang que inclou les cel·les que tenen un valor i que són adjacents a la cel·la A1.
Edita el nom de rang
Ara només resta modificar la definició del nom de rang mitabla perquè utilitzi aquesta fórmula. Per això he d’anar a la fitxa Fórmules i oprimir el botó Administrador de noms, es mostrarà el nom de rang prèviament definit i en el quadre de text de la part inferior s’haurà reemplaçar la seva definició per la fórmula anterior:
Amb aquesta nova definició del rang mitabla no importaran les files que agreguem ja que sempre seran considerades en el rang. En el següent exemple, pots observar com vaig afegint nous valors a la columna A i són considerats automàticament en la suma de la cel·la D5:
[download id=”1678″]