Questo articolo ti aiuterà a comprendere tutte le cause degli errori #SPILL e le soluzioni per risolverli in Excel 365.
#VERSARE! è un nuovo tipo di errore di Excel che si verifica principalmente quando una formula che produce più risultati di calcolo tenta di visualizzare i suoi output in un intervallo di fuoriuscita ma quell'intervallo contiene già alcuni altri dati.
I dati di blocco potrebbero essere qualsiasi cosa, inclusi valori di testo, celle unite, uno spazio semplice o anche quando non c'è spazio sufficiente per restituire i risultati. La soluzione è semplice, cancellare l'intervallo di eventuali dati di blocco o selezionare una matrice vuota di celle che non contengono alcun tipo di dati al suo interno.
L'errore di dispersione di solito si verifica quando si calcolano le formule di matrice dinamica, perché la formula di matrice dinamica è quella che restituisce i risultati in più celle o in una matrice. Esaminiamo più in dettaglio e capiamo cosa fa scattare questo errore in Excel e come risolverlo.
Cosa causa un errore di versamento?
Dal lancio degli array dinamici nel 2018, le formule di Excel possono gestire più valori alla volta e restituire risultati in più di una cella. Gli array dinamici sono array ridimensionabili che consentono alle formule di restituire più risultati in un intervallo di celle del foglio di lavoro in base a una formula immessa in una singola cella.
Quando una formula di matrice dinamica restituisce più risultati, questi risultati si riversano automaticamente nelle celle adiacenti. Questo comportamento è chiamato "Sversamento" in Excel. E l'intervallo di celle in cui si riversano i risultati è chiamato "intervallo di versamento". L'intervallo di fuoriuscita si espanderà o si contrarrà automaticamente in base ai valori di origine.
Se una formula sta cercando di riempire un intervallo di versamento con più risultati ma è bloccata da qualcosa in quell'intervallo, si verifica un errore #SPILL.
Excel ora ha 9 funzioni che utilizzano la funzionalità Dynamic Array per risolvere i problemi, tra cui:
- SEQUENZA
- FILTRO
- TRASPORRE
- ORDINARE
- ORDINA PER
- RANDARRAY
- UNICO
- XLOOKUP
- XMATCH
Le formule di matrice dinamica sono disponibili solo in "Excel 365" e attualmente non sono supportate da nessuno dei software Excel offline (ad esempio Microsoft Excel 2016, 2019).
Gli errori di versamento non sono causati solo dall'ostruzione dei dati, ma ci sono diversi motivi per cui potresti ricevere l'errore #Spill. Esploriamo le diverse situazioni in cui potresti incontrare il #SPILL! errore e come risolverli.
L'intervallo di fuoriuscita non è vuoto
Una delle cause principali dell'errore di versamento è che l'intervallo di fuoriuscita non è vuoto. Ad esempio, se stai cercando di visualizzare 10 risultati, ma se sono presenti dati in una qualsiasi delle celle nell'area di versamento, la formula restituisce un #SPILL! errore.
Esempio 1:
Nell'esempio seguente, abbiamo inserito la funzione TRANSPOSE nella cella C2 per convertire l'intervallo verticale di celle (B2:B5) in un intervallo orizzontale (C2:F2). Invece di cambiare la colonna in una riga, Excel ci mostra il #SPILL! errore.
E quando fai clic sulla cella della formula, vedrai un bordo blu tratteggiato che indica l'area/intervallo di fuoriuscita (C2: F2) necessario per visualizzare i risultati come mostrato di seguito. Inoltre, noterai un segnale di avvertimento giallo con un punto esclamativo.
Per comprendere il motivo dell'errore, fare clic sull'icona di avviso accanto all'errore e visualizzare il messaggio nella prima riga evidenziato in grigio. Come puoi vedere, qui si dice "L'intervallo di fuoriuscita non è vuoto".
Il problema qui è che le celle nell'intervallo di fuoriuscita D2 ed E2 hanno caratteri di testo (non vuoti), quindi l'errore.
Soluzione:
La soluzione è semplice, cancellare i dati (spostandoli o eliminarli) situati nell'intervallo di fuoriuscita o spostare la formula in un'altra posizione in cui non vi siano ostacoli.
Non appena elimini o sposti il blocco, Excel popolerà automaticamente le celle con i risultati della formula. Qui, quando cancelliamo il testo in D2 ed E2, la formula traspone la colonna in riga come previsto.
Esempio 2:
Nell'esempio seguente, sebbene l'intervallo di versamento appaia vuoto, la formula mostra ancora lo spargimento! errore. È perché la fuoriuscita non è effettivamente vuota, ha uno spazio invisibile in una delle celle.
È difficile individuare i caratteri spazio o qualsiasi altro carattere invisibile nascosto in quelle che sembrano celle vuote. Per trovare tali celle con dati indesiderati, fai clic sul floatie di errore (segnale di avviso) e seleziona "Seleziona celle che ostruiscono" dal menu e ti porterà alla cella che contiene i dati che ostruiscono.
Come puoi vedere, nello screenshot qui sotto, la cella E2 ha due caratteri spazio. Quando cancelli quei dati, otterrai l'output corretto.
A volte, il carattere invisibile potrebbe essere un testo formattato con lo stesso colore del carattere del colore di riempimento della cella o un valore di cella formattato in modo personalizzato con il codice numerico ;;;. Quando formatti in modo personalizzato un valore di cella con ;;;, nasconderà qualsiasi cosa in quella cella, indipendentemente dal colore del carattere o dal colore della cella.
L'intervallo di versamento contiene celle unite
A volte, il #SPILL! l'errore si verifica quando l'intervallo di versamento contiene le celle unite. La formula di matrice dinamica non funziona con le celle unite. Per risolvere questo problema, tutto ciò che devi fare è separare le celle nell'intervallo di versamento o spostare la formula in un altro intervallo che non ha celle unite.
Nell'esempio seguente, anche se l'intervallo di versamento è vuoto (C2:CC8), la formula restituisce l'errore di versamento. È perché le celle C4 e C5 sono unite.
Per assicurarti che le celle unite siano il motivo per cui ricevi l'errore, fai clic susegnale di avvertimento e verifica la causa - "L'intervallo di fuoriuscite ha una cella unita".
Soluzione:
Per separare le celle, seleziona le celle unite, quindi nella scheda "Home", fai clic sul pulsante "Unisci e centra" e seleziona "Separa celle".
Se hai difficoltà a individuare le celle unite nel tuo foglio di calcolo di grandi dimensioni, fai clic sull'opzione "Seleziona celle che ostruiscono" dal menu del segnale di avvertimento per passare alle celle unite.
Intervallo di sversamento nella tabella
Le formule di matrice rovesciata non sono supportate nelle tabelle di Excel. La formula di matrice dinamica deve essere inserita solo in una singola cella. Se si immette una formula di matrice rovesciata in una tabella o quando l'area di fuoriuscita cade in una tabella, si otterrebbe l'errore di versamento. Quando ciò accade, prova a convertire la tabella in un intervallo normale o sposta la formula all'esterno della tabella.
Ad esempio, quando inseriamo la seguente formula di intervallo rovesciato in una tabella di Excel, otterremmo un errore di dispersione in ogni cella della tabella, non solo nella cella della formula. È perché Excel copia automaticamente qualsiasi formula immessa in una tabella in ogni cella nella colonna della tabella.
Inoltre, riceverai un errore di versamento quando una formula tenta di versare i risultati in una tabella. Nello screenshot seguente, l'area di versamento rientra nella tabella esistente, quindi otteniamo un errore di versamento.
Per confermare la causa di questo errore, fare clic sul segnale di avviso e visualizzare il motivo dell'errore: "Intervallo di versamento nella tabella"
Soluzione:
Per correggere l'errore, dovrai ripristinare la tabella di Excel nell'intervallo. Per farlo, fai clic con il pulsante destro del mouse in un punto qualsiasi della tabella, fai clic su "Tabella", quindi seleziona l'opzione "Converti in intervallo". In alternativa, puoi fare clic con il pulsante sinistro del mouse in qualsiasi punto della tabella, quindi andare alla scheda "Progettazione tabella" e selezionare l'opzione "Converti in intervallo".
L'intervallo di fuoriuscita è sconosciuto
Se Excel non è stato in grado di stabilire la dimensione dell'array versato, attiverà l'errore di versamento. A volte, la formula consente di ridimensionare un array dinamico tra ogni passaggio di calcolo. Se la dimensione dell'array dinamico continua a cambiare durante i calcoli e non si bilancia, causerà il #SPILL! Errore.
Questo tipo di errore di dispersione viene solitamente attivato quando si utilizzano funzioni volatili come le funzioni RAND, RANDARRAY, RANDBETWEEN, OFFSET e INDIRETTO.
Ad esempio, quando utilizziamo la formula seguente nella cella B3, otteniamo l'errore di versamento:
=SEQUENZA(CASUALE TRA(1, 500))Nell'esempio, la funzione RANDBETWEEN restituisce un numero intero casuale compreso tra 1 e 500 e il suo output cambia continuamente. E la funzione SEQUENCE non sa quanti valori produrre in una matrice di spill. Quindi, l'errore #SPILL.
Puoi anche confermare la causa dell'errore facendo clic sul segnale di avviso - "L'intervallo di fuoriuscita è sconosciuto".
Soluzione:
Per correggere l'errore di questa formula, l'unica scelta è utilizzare una formula diversa per il calcolo.
L'intervallo di fuoriuscita è troppo grande
A volte è possibile eseguire una formula che genera un intervallo rovesciato che è troppo grande per essere gestito dal foglio di lavoro e può estendersi oltre i bordi del foglio di lavoro. Quando ciò accade potresti ottenere #SPILL! errore. Per risolvere questo problema, puoi provare a fare riferimento a un intervallo specifico o a una cella anziché a intere colonne o utilizzando il carattere "@" per abilitare l'intersezione implicita
Nell'esempio seguente, stiamo cercando di calcolare il 20% dei numeri di vendita nella colonna A e restituire i risultati nella colonna B, ma invece otteniamo un errore di versamento.
La formula in B3 calcola il 20% del valore in A3, quindi il 20% del valore in A4 e così via. Produce oltre un milione di risultati (1,048,576) e li riversa tutti nella colonna B a partire dalla cella B3, ma raggiungerà la fine del foglio di lavoro. Non c'è abbastanza spazio per mostrare tutti gli output, di conseguenza, otteniamo un errore #SPILL.
Come puoi vedere, la causa di questo errore è che - "L'intervallo di fuoriuscita è troppo grande".
Soluzioni:
Per risolvere questo problema, prova a modificare l'intera colonna con un intervallo pertinente o un riferimento a cella singola oppure aggiungi l'operatore @ per eseguire l'intersezione implicita.
Correzione 1: puoi provare a fare riferimento a intervalli anziché a intere colonne. Qui, cambiamo l'intero intervallo A: A con A3: A11 nella formula e la formula popolerà automaticamente l'intervallo con i risultati.
Correzione 2: Sostituisci l'intera colonna solo con il riferimento di cella sulla stessa riga (A3), quindi copia la formula lungo l'intervallo utilizzando il quadratino di riempimento.
Correzione 3: Puoi anche provare ad aggiungere l'operatore @ prima del riferimento per eseguire l'intersezione implicita. Questo visualizzerà l'output solo nella cella della formula.
Quindi, copia la formula dalla cella B3 al resto dell'intervallo.
Nota: Quando si modifica una formula rovesciata, è possibile modificare solo la prima cella nell'area/intervallo di versamento. Puoi vedere la formula in altre celle dell'intervallo di versamento, ma saranno disattivate e non possono essere aggiornate.
Fuori dalla memoria
Se si esegue una formula di matrice rovesciata che causa l'esaurimento della memoria di Excel, è possibile che venga generato l'errore #SPILL. In tali circostanze, prova a fare riferimento a un array o un intervallo più piccolo.
Non riconosciuto/Ripiego
È inoltre possibile ottenere un errore di versamento anche quando Excel non riconosce o non riesce a conciliare la causa dell'errore. In questi casi, ricontrolla la tua formula e assicurati che tutti i parametri delle funzioni siano corretti.
Ora conosci tutte le cause e le soluzioni per #SPILL! errori in excel 365.