Funzioni volatili e loro uso nei nomi definiti

Question

Si sa che le funzioni volatili hanno molti pregi ma anche un difetto. Vengono cioè ricalcolate ogni volta che si modifica una cella anche se questa non è richiamata nella funzione stessa, provocando un appesantimento nelle operazioni di ricalcolo in presenza di consistenti mole di dati.

Io uso spesso una di queste funzioni, più precisamente la funzione SCARTO(...), riferita a nomi definiti per circoscrivere intervalli o matrici variabili.

Mi sorge quindi una domanda:

la funzione viene ricalcolata come sopra anche quando inserita in nomi definiti?

Oppure viene calcolata una tantum all'apertura del file e l'intervallo viene memorizzato da Excel così da essere utilizzato successivamente senza subire ulteriori ricalcoli?

Faccio un esempio banale:

supponiamo di avere un intervallo di dati A1:a10 (numeri da 1 a 10).

Se volessi calcolarne la somma scriverei, supponiamo in B1: =SOMMA(A1:A10), però questa formula mi precluderebbe la possibilità di aggiungere ulteriori righe di dati.

Se volessi prevedere la possibilità di scrivere dati fino a riga 100 senza necessità di modificare la formula, dovrei scrivere =SOMMA(SCARTO($A$1;;;CONTA.NUMERI($A$1:$A$100))).

In questo modo però sarei penalizzato nelle operazioni di ricalcolo dalla funzione volatile SCARTO(...).

Se invece la riferissi a un nome definito, per es. Addizione, e scrivessi la formula così: =SOMMA(Addizione), avrei anche in questo caso il problema del ricalcolo?

Grazie. Saluti.

Answer 1

Ciao Paolo,

Si sa che le funzioni volatili hanno molti pregi ma anche un difetto. Vengono cioè ricalcolate ogni volta che si modifica una cella anche se questa non è richiamata nella funzione stessa, provocando un appesantimento nelle operazioni di ricalcolo in presenza di consistenti mole di dati.

Io uso spesso una di queste funzioni, più precisamente la funzione SCARTO(...), riferita a nomi definiti per circoscrivere intervalli o matrici variabili.

Mi sorge quindi una domanda:

la funzione viene ricalcolata come sopra anche quando inserita in nomi definiti?

Oppure viene calcolata una tantum all'apertura del file e l'intervallo viene memorizzato da Excel così da essere utilizzato successivamente senza subire ulteriori ricalcoli?

Faccio un esempio banale:

supponiamo di avere un intervallo di dati A1:a10 (numeri da 1 a 10).

Se volessi calcolarne la somma scriverei, supponiamo in B1: =SOMMA(A1:A10), però questa formula mi precluderebbe la possibilità di aggiungere ulteriori righe di dati.

Se volessi prevedere la possibilità di scrivere dati fino a riga 100 senza necessità di modificare la formula, dovrei scrivere =SOMMA(SCARTO($A$1;;;CONTA.NUMERI($A$1:$A$100))).

In questo modo però sarei penalizzato nelle operazioni di ricalcolo dalla funzione volatile SCARTO(...).

Se invece la riferissi a un nome definito, per es. Addizione, e scrivessi la formula così: =SOMMA(Addizione), avrei anche in questo caso il problema del ricalcolo?

Risposta corta ed amara: l'uso di una funzione volatile dentro un nome definito rende una formula che usa il nome volatile.

Più in genere: qualsiasi azione intrapresa per aggiungere, eliminare, cambiare o modificare un nome definito o la sua proprietà si riferisce a provocherà un ricalcolo.

Ho creato un semplice file di Demo approfittando del seguente codice di Charles Williams (http://www.decisionmodels.com/)

'=========>>

Option Explicit

Option Base 1

Option Compare Text

'-----------------------

Public jCalcSeq As Long             ''' calculation sequence counter

'--------->>

Public Function CalcSeqCountRef(theRange As Range) As Variant

'

' COPYRIGHT © DECISION MODELS LIMITED 2000. All rights reserved

'

' increment calculation sequence counter at Full Recalc or when theRange changes

' fixed for false dependency

'

    jCalcSeq = jCalcSeq + 1

    CalcSeqCountRef = jCalcSeq + (theRange = theRange) + 1

    Debug.Print Application.Caller.Formula

End Function

'--------->>

Sub resetcounter()

    jCalcSeq = 0

End Sub

'<<=========

Con questo codice, posso utilizzare la funzione utente CalcSeqCountRef per ricordare il numero di ricalcolazioni della formula in un dato cella.

Quindi, se immetto la formula 

= SOMMA(Addizione)

nella cella B1, posso ricordare i ricalcoli di questa cella immettendo nella cella C1 la formula

     =CalcSeqCountRef(B1)

Per la completezza, ho creato anche il nome definito mySum con la definizione:

      =PI.GRECO()+100

Nella cella I3 ho poi immesso la formula 

      =mySum

e, per ricordare i suoi ricalcoli, nella cella J1 ho immesso la formula

       =CalcSeqCountRef(I1)

Ora, ogni volta che premo F9, il contatore nella cella C1 si aumenta mentre quella nella cella J1 rimane statico.

Salvando il file e riaprendolo, i due contatori avranno il valore 1 , in corrispondenza alla ricolcolazione del file alla sua apertura. 

Ad ogni successivo ricalcolo del foglio, il contatore in C1 si aumenterà in risposta al ricalcolo della la funzione volatile in B1 mentre il contatore in J1 rimarrà statico poichè la funzione in I1 non è volatile.

Potresti scaricare il mio file di prova**Paolo20180213.xlsm**

===

Regards,

Norman

Answer 2

Grazie Fratello.

La "prova" non lascia dubbi sulla volatilità della formula/funzione contenente un  nome definito riferito a formula/funzione volatile.

Peccato! O, come dici tu, "amaro".

Answer 3

Ciao Fratello,

La "prova" non lascia dubbi sulla volatilità della formula/funzione contenente un  nome definito riferito a formula/funzione volatile.

Peccato! O, come dici tu, "amaro".

In questo caso, avrei preferito essermi sbagliato!

Per la precisione, approfitto per precisare che la UDF CalcSeqCountRef restituisce il numero della sequenza dei calcoli, piuttosto che il numero di calcoli, ma ciò non cambia i risultati dimostrati.

===

Regards,

Norman