Gestire l'errore SPILL

Quando una formula con matrice dinamica non riesce a disporre l'intero risultato, Excel mostra #SPILL! nella cella iniziale. Di solito la formula è corretta: il problema è che qualcosa ostacola l'area in cui devono essere inseriti i risultati.

È come cercare di parcheggiare un autobus in uno spazio già occupato da un'auto. L'autobus non ha problemi, ma il posto è bloccato.

La causa più comune è che una o più celle dell'intervallo di espansione contengano già dei dati. Se =UNIQUE(A2:A12) deve riempire C1:C3, ma C2 contiene un valore estraneo, l'espansione viene bloccata.

La soluzione è semplice: selezionate la cella iniziale, osservate il contorno tratteggiato dell'espansione e cancellate le celle non vuote al suo interno.

Fate clic sulla cella che mostra #SPILL!. Excel traccia un bordo tratteggiato intorno all'area che il risultato dovrebbe occupare. La causa del problema è qualsiasi cella non vuota all'interno di quel bordo.

Potete anche fare clic sul triangolo di avviso accanto alla cella e scegliere Seleziona celle di ostruzione, per passare direttamente alle celle responsabili e cancellarne il contenuto.

Un intervallo di espansione non può sovrapporsi a celle unite. Anche una cella unita vuota conta come ostacolo, perché l'espansione richiede celle separate e singole da riempire.

Se il risultato ricade su una barra del titolo o un'etichetta composta da celle unite, separatele (scheda Home, comando Unisci e centra) e la formula verrà espansa correttamente.

L'espansione non riesce anche quando oltrepasserebbe il bordo del foglio di lavoro o entrerebbe in una tabella. Ad esempio, una formula vicino all'ultima riga che deve espandersi verso il basso non ha spazio disponibile.

Spostate la formula più in alto o in una colonna con spazio sottostante. Assicuratevi che nella direzione dell'espansione siano presenti tante celle vuote quante ne richiede il risultato.

Le tabelle di Excel (create con Ctrl+T) non consentono alle matrici dinamiche di espandersi al loro interno, perché una tabella prevede che ogni cella sia modificabile.

Se la formula che si espande si trova in una colonna della tabella, verrà visualizzato #SPILL!. Convertite la tabella in un normale intervallo (Struttura tabella, Converti in intervallo) oppure spostate la formula fuori dalla tabella.

Riferire una formula che si espande a un'intera colonna può causare #SPILL!, perché il risultato diventa enorme e potrebbe entrare in conflitto con altri contenuti o superare i limiti.

Ad esempio, =A:A*2 tenta di espandersi su un milione di righe. Limitate il riferimento ai dati effettivi, ad esempio =A2:A100*2, così l'espansione avrà dimensioni gestibili e definite.

Passi il mouse sul triangolo di avviso in una cella #SPILL! ed Excel Le spiega esattamente il motivo. I messaggi più comuni includono L'intervallo di espansione non è vuoto, L'intervallo di espansione contiene celle unite e L'intervallo di espansione è troppo grande.

Legga sempre prima questo suggerimento. La conduce direttamente alla causa, senza dover procedere per tentativi, e ogni messaggio corrisponde a una delle soluzioni appena apprese.

È importante ricordare che un errore #SPILL! raramente significa che la logica della formula è errata. Il calcolo è riuscito; è fallito soltanto il posizionamento.

Una volta rimossa l'ostruzione, la stessa formula, senza alcuna modifica, si espande correttamente. Lo confronti con #VALUE! o #REF!, che di solito indicano un problema reale all'interno della formula.

Inserisce =SORT(UNIQUE(A2:A20)) in D1 e ottiene #SPILL!. Seleziona D1, vede il contorno tratteggiato che copre D1:D5 e nota una vecchia nota in D3.

Elimina il contenuto di D3. Immediatamente la formula espande l'elenco ordinato dei valori distinti verso il basso, nell'intervallo D1:D5. Non è stato necessario modificare affatto la formula.

Un po' di pianificazione evita la maggior parte degli errori di espansione. Riservi alle formule di matrice dinamica una colonna o un'area vuota in cui potersi espandere e tenga etichette e note lontane dalla direzione di espansione.

Quando fa riferimento a un'altra espansione, utilizzi l'operatore #, come in =SUM(D1#), in modo che le formule si adattino quando gli intervalli cambiano dimensione. Crei i risultati in uno spazio libero e aperto: #SPILL! comparirà raramente.

Verifichi la Sua comprensione dell'errore SPILL.

Ha imparato che #SPILL! è un problema di posizionamento, non un errore logico:

• Nella maggior parte dei casi, una cella dell'intervallo di espansione non è vuota.
• Le celle unite e le Tabelle impediscono l'espansione.
• Eviti i riferimenti a colonne intere, che rendono il risultato troppo grande.
• Utilizzi Seleziona celle di ostruzione e il suggerimento per trovare la causa.

Il corso su matrici dinamiche ed espansione è completato. Ora può restituire molti risultati da un'unica formula e correggere le espansioni con sicurezza.