Excel ha una vasta gamma di utili funzioni integrate, ma ciò non significa che dovresti usarle a casaccio. Una Cose da fare quando Excel è lento Si tratta di controllare alcune formule per assicurarsi di utilizzare queste funzioni in modo efficiente. Anche quelle apparentemente semplici possono avere un impatto significativo sulle prestazioni e causare numerosi problemi.
Sebbene le cause possibili siano molteplici, le formule che utilizzano funzioni variabili, funzioni array e funzioni di ricerca sono le più significative. Spiegherò perché potrebbero rallentare i tuoi fogli di lavoro, insieme ad alcuni suggerimenti su come risolverli per migliorare le prestazioni o quando evitarli del tutto.
Link veloci
Formule contenenti funzioni variabili
C'è una buona ragione per cui i professionisti lo usano con parsimonia.
Se noti che il tuo foglio Excel viene ricalcolato ogni volta che apporti una modifica e questo influisce sulle prestazioni, il problema è probabilmente dovuto alle funzioni variabili. Le funzioni variabili forzano i ricalcoli per mantenere i risultati aggiornati. Lo fanno anche se la modifica non influisce su nessuna delle celle a cui si fa riferimento o sul loro output.
Una funzione variabile comune è NOW, che restituisce la data e l'ora correnti ed è utile per scenari come il calcolo delle scadenze, l'inserimento di timestamp e l'aggiornamento dei dashboard in tempo reale. Un'altra funzione è Funzione INDIRETTA, che utilizza stringhe di testo per creare riferimenti di cella ed è utile, tra le altre cose, per fare riferimento a intervalli dinamici.
Sebbene svolgano un buon lavoro nel mantenere il flusso dinamico, dovrebbero essere utilizzati solo quando necessario. Questo è particolarmente vero per moduli di grandi dimensioni contenenti decine di fogli o migliaia di righe. È inoltre consigliabile utilizzare alternative non volatili quando possibile (ad esempio, inserendo manualmente la data invece di utilizzare la funzione NOW).
Se non puoi evitare le funzioni variabili e rallentano la tua cartella di lavoro, puoi disattivare il ricalcolo automatico. In questo modo, puoi attivare manualmente i ricalcoli invece di doverli eseguire a ogni piccola modifica, ma lo svantaggio è che è facile dimenticarsene. F9 Ricalcola tutti i fogli di lavoro aperti e Maiusc + F9 Ricalcola solo il foglio di lavoro attivo.
Vecchie formule di matrice
L'elaborazione delle formule di matrice non è sempre così semplice.
Per creare formule di matrice, digitare la formula e premere CTRL + MAIUSC + INVIO Invece di solo entraSaprai di averla creata correttamente se la formula verrà automaticamente racchiusa tra parentesi graffe.
Le formule di matrice sono particolarmente utili quando i calcoli coinvolgono più valori, risparmiando il passaggio aggiuntivo di creazione di colonne ausiliarie. Possono anche restituire più risultati.
Le formule di matrice sono una funzionalità obsoleta, soprattutto ora che Microsoft ha introdotto funzioni di matrice dinamica più efficienti in Excel per Microsoft 365. Tuttavia, hanno ancora la loro utilità, soprattutto quando si desidera garantire la compatibilità con le versioni precedenti di Excel (2019 o precedenti). Ecco perché non scompariranno presto.
Sebbene tutti i dati siano disposti ordinatamente su un'unica riga quando si utilizzano formule matriciali, Excel elabora la formula più volte per ottenere i risultati desiderati. Questo può essere dispendioso in termini di elaborazione, soprattutto in fogli Excel di grandi dimensioni.
Per evitare le formule di matrice, potresti prendere in considerazione l'utilizzo di colonne di supporto, che distribuiscono i calcoli su più celle. Se non puoi evitarle, prova a non utilizzarle con le funzioni variabili. Se la compatibilità non è un problema, puoi semplicemente utilizzare funzioni di matrice dinamica ove possibile.
Formule di ricerca
È comodo ma può essere costoso.
Se devi trovare un valore specifico in un foglio Excel, il modo più rapido è utilizzare una formula di ricerca. Le funzioni più comuni utilizzate in queste formule sono CERCA.VERT e CERCA.ORIZZ, che eseguono ricerche rispettivamente su colonne e righe. Ad esempio, è facile trovare il prezzo di un prodotto in un elenco verticale utilizzando il suo nome. VLOOKUP.
Il problema è che queste formule di ricerca possono causare un calo delle prestazioni se devono cercare in set di dati di grandi dimensioni, soprattutto quando i dati da ricercare sono distribuiti su più fogli. Questo diventa un processo che richiede molto tempo e può mettere a dura prova Excel.
A meno che non si utilizzino ampiamente le funzioni di ricerca, questo potrebbe non rappresentare un grosso problema per la versione Microsoft 365 di Excel, poiché crea un indice temporaneo che velocizza le ricerche ricorrenti. È disponibile anche una funzione di ricerca alternativa più efficiente (e versatile) chiamata XLOOKUP, che è possibile utilizzare in alternativa.
Se è necessario utilizzare formule di ricerca nelle versioni precedenti di Excel, è più efficiente utilizzare la formula sullo stesso foglio della matrice di ricerca. È inoltre consigliabile limitare l'ambito della ricerca alle sole celle necessarie (ad esempio, B2: B100)—Evitare di fare riferimento all'intera colonna (ad esempio, B: B).
). Inoltre, usa la corrispondenza approssimativa anziché quella esatta, perché la prima non richiede di cercare in ogni cella per trovare il valore di cui hai bisogno.
Sfrutta la potenza dei file Excel ultraveloci
Nella mia esperienza, questi tre sono i problemi più grandi che gli utenti devono affrontare. È inoltre necessario prestare attenzione alle formule nidificate, in particolare quelle contenenti una logica condizionale complessa. Anche in questo caso, questo potrebbe non rappresentare un problema rilevante in Excel per Microsoft 365, che utilizza indici nascosti per velocizzare le ricerche ricorsive.
Oltre a controllare le formule, assicurati che Excel sia aggiornato, elimina i dati non necessari e rimuovi i componenti aggiuntivi non utilizzati. Qualsiasi cosa tu possa fare per velocizzare il tuo file Excel è un problema in meno da affrontare. Puoi concentrarti sul lavoro invece di perdere altro tempo a ottimizzare.
