In questi giorni ho avuto modo di analizzare un file Excel che presentava particolari problemi di performance nonostante la cpu e la memoria sul sistema non erano particolarmente utilizzate.
Per una serie di indicazioni sulle performance in Excel è possibile fare riferimento a questo articolo Excel performance – Improving calculation performance | Microsoft Learn. Nel caso che ho avuto modo di analizzare il file conteneva un gran numero di formattazioni condizionali e molte di queste erano basate su una formula e non solo sul valore presente in una cella.
Quando la formattazione condizionale è fatta basandosi sulla valutazione di una formula e tramite la semplice valutazione del valore di una cella lo smart recalculation engine in Excel non riesce ad ottimizzare il tempo di ricalcolo, come indicato in Excel performance – Improving calculation performance | Microsoft Learn:
The smart recalculation engine in Excel tries to minimize calculation time by continuously tracking both the precedents and dependencies for each formula (the cells referenced by the formula) and any changes that were made since the last calculation. At the next recalculation, Excel recalculates only the following:
• Cells, formulas, values, or names that have changed or are flagged as needing recalculation.
• Cells dependent on other cells, formulas, names, or values that need recalculation.
• Volatile functions and visible conditional formats.
Excel continues calculating cells that depend on previously calculated cells even if the value of the previously calculated cell does not change when it is calculated.
Because you change only part of the input data or a few formulas between calculations in most cases, this smart recalculation usually takes only a fraction of the time that a full calculation of all the formulas would take.
In manual calculation mode, you can trigger this smart recalculation by pressing F9. You can force a full calculation of all the formulas by pressing Ctrl+Alt+F9, or you can force a complete rebuild of the dependencies and a full calculation by pressing Shift+Ctrl+Alt+F9.
Un modo per evitare di incorrere in questa problematica consentendo al recalculation engine in Excel di intervenire è inserire le formule in celle di appoggio e valutare nella formattazione condizione il valore delle celle di appoggio.
Su tale problematica è possibile reperire in Internet varie analisi come ad esempio il seguente articolo This Trick Speeds up Complex Conditional Formatting in Excel scritto da un consulente norvegese che riporta di aver riscontrato in casi reali che una formattazione condizionale non ottimizzata può impattare sulle performance dei fogli Excel fino al 90%:
“So, we have been able to reduce the time consumption by more then 66%! Important to remember: This is taken from a real-world example delivered to one of our clients. It has to be taken into account that a lot of the remaining 543 ms is the time necessary to evaluate the look up functions towards the data table in the background. These were strictly necessary to fulfil the clients’ requirements. If we had isolated the issue solely to the conditional formatting, the timesaving might have been even higher, likely surpassing 90%.”
Un’altra analisi interessante è quella di Charles Williams (Microsoft Excel MVP dal 2009) disponibile ai seguenti link:
- Exploring Conditional Format Performance Part 1: What’s slow, whats buggy and whats faster! | Excel and UDF Performance Stuff
- Exploring Conditional Format Performance Part 2: What’s slow, whats buggy and whats faster! | Excel and UDF Performance Stuff
- Exploring Conditional Format Performance Part 3: What’s slow, whats buggy and whats faster! | Excel and UDF Performance Stuff
Charles Williams nella parte 3 indica che l’elaborazione della formattazione condizionale è single-threaded, di conseguenza può utilizzare un solo core alla volta e non può avvantaggiarsi del fatto che la CPU disponga di più core. Questo implica che la CPU non subisce un carico eccessivo durante l’elaborazione della formattazione condizionale, ma anche che l’elaborazione non trarrà alcun vantaggio da CPU più potenti.
Conclusioni
Il modo in cui di struttura un foglio Excel può determinare le sue performance e ciò è particolarmente vero per quanto riguarda la formattazione condizionale in cui è sconsigliabile utilizzare formule soprattutto se complesse, ma utilizzare nel caso celle di appoggio in cui inserire le formule necessarie. Sempre in ottica di ottimizzazione della formattazione condizionale è importante impostare correttamente l’odine di valutazione delle regole di formattazione condizionale e l’opzione “Interrompi se vera” sulle regole di formattazione condizionale per fare in modo di evitare la valutazione di regole di formattazione condizionale quando non è necessario. A a riguardo si veda la sezione Gestire le regole di formattazione condizionale – Definizione dei casi in cui interrompere le regole di valutazione tramite la casella di controllo Interrompi se Vera in Usare la formattazione condizionale per evidenziare informazioni in Excel – Supporto tecnico Microsoft:
Definizione dei casi in cui interrompere le regole di valutazione tramite la casella di controllo Interrompi se Vera
Per garantire la compatibilità con le versioni precedenti, selezionare la casella di controllo Interrompi se Vera nella finestra di dialogo Gestisci regole per simulare l’aspetto della formattazione condizionale nelle versioni precedenti di Excel che non supportano più di tre regole di formattazione condizionale o più regole applicate allo stesso intervallo.
Ad esempio, se si hanno più di tre regole di formattazione condizionale per un intervallo di celle e si usa una versione precedente di Excel, questa versione di Excel:
- Verranno valutate soltanto le prime tre regole.
- Verrà applicata la prima regola vera nell’ordine di precedenza.
- Verranno ignorate le regole successive nell’ordine di precedenza se sono vere.
È possibile selezionare o deselezionare la casella di controllo Interrompi se Vera per modificare il comportamento predefinito:
Per valutare solo la prima regola, selezionare la casella di controllo Interrompi se vera per la prima regola.
Per valutare solo la prima e la seconda regola, selezionare la casella di controllo Interrompi se Vera per la seconda regola.
Non è possibile selezionare o deselezionare la casella di controllo Interrompi se Vera se la regola esegue la formattazione usando una barra dei dati, una scala dei colori o un set di icone.