Disattivazione di #SPILL! Errori (Microsoft Excel)

L’azienda di Jan è stata recentemente aggiornata a Office 365 ProPlus. Con esso è arrivato un #SPILL! errore durante l’utilizzo di CERCA.VERT, che utilizza un LOTTO. Ora non è in grado di utilizzare CERCA.VERT con un filtro. Jan sa che sarebbe facile ordinare e / o rimuovere il # N / D proveniente dal precedente CERCA.VERT, ma questo errore gli sta causando molto tempo e impegno extra. Si chiede se ci sia un modo per disattivare questa “funzionalità”.

In un passato molto prossimo, Microsoft ha cambiato il modo in cui calcola i fogli di lavoro.

Questo è stato un enorme cambiamento, e potresti averlo letto altrove. Ecco un articolo che fornisce alcune ottime informazioni sul cambiamento:

L’articolo è piuttosto lungo; ti consigliamo di dedicare un po ‘di tempo per digerire le informazioni che contiene. Se crei molte formule in Excel, ti consigliamo di farlo, tuttavia: la modifica al programma significa che _ devi_ capirlo, alla fine.

In sostanza, Microsoft ha eliminato il concetto di funzioni di matrice (anche se funzioneranno ancora), consentendo invece a quasi tutte le funzioni, incluso CERCA.VERT, di restituire una matrice di valori. Se l’array di valori restituiti non si adatta allo spazio disponibile, ottieni il nuovo #SPILL! errore.

Onestamente, la risposta non è disabilitare #SPILL! errori; non c’è davvero modo di farlo. La risposta è capire cosa sta facendo Excel mentre calcola e quindi modificare le formule di conseguenza.

Diamo un’occhiata a un esempio. Supponiamo che tu abbia un foglio di lavoro che elenca gli articoli e i relativi prezzi in un semplice set di dati a due colonne. Quindi, a destra di questo, inserisci alcuni articoli e utilizza una funzione CERCA.VERT per restituire i prezzi associati a ciascuno di quegli articoli. Quando apri questa cartella di lavoro in una versione precedente di Excel (2019 o precedente), ottieni ottimi risultati. (Vedi figura 1.)

Figura 1. Una semplice formula CERCA.VERT in Excel 2010.

Questa schermata è stata scattata utilizzando un sistema Excel 2010, ma funzionerebbe allo stesso modo se la guardassi in Excel 2016 o anche in Excel 2019. Nota in questo esempio che la formula CERCA.VERT nella cella F2 (mostrata nella barra della formula perché la cella F2 è selezionato) viene copiato nell’intervallo F2: F8. Si ottengono i risultati desiderati perché la funzione CERCA.VERT restituisce un singolo valore dalla tabella.

Adesso vediamo cosa succede se crei la stessa cartella di lavoro, utilizzando le stesse formule, nella versione di Excel fornita con Office 365. In questo caso vedrai degli errori. (Vedi figura 2.)

Figura 2. La stessa semplice formula CERCA.VERT nell’ultima versione di Excel.

Nota il #SPILL! errori. Questo errore si verifica perché la formula CERCA.VERT può ora restituire più di un singolo valore. Infatti, quando si utilizza un intervallo di celle nel primo parametro per CERCA.VERT, ora restituirà un valore per ogni cella in quell’intervallo. Pertanto, l’utilizzo dell’intervallo E2: E8 per il primo parametro significa che CERCA.VERT restituisce 7 valori. In altre parole, restituisce automaticamente un array di valori. Se non è possibile visualizzare tutti questi valori, viene visualizzato il messaggio #SPILL! errore. Ecco perché stai vedendo il #SPILL! errore nelle celle F2: F7; ci sono cose sotto di loro che impediscono la visualizzazione di tutti i valori restituiti in quelle formule. Non vedi l’errore nella cella F8 perché non c’è niente sotto quella cella che ferma la visualizzazione.

Quindi, come si risolve questo problema? In realtà ci sono tre modi per risolverlo.

In questo particolare esempio, il modo più semplice sarebbe eliminare semplicemente tutto nelle celle F3: F8. Ciò consente alla formula nella cella F2 di “fuoriuscire”

correttamente al resto delle celle sottostanti.

Il secondo approccio sarebbe quello di cambiare la formula nella cella F2, quindi assomiglia a questo:

Notare l’uso del simbolo @ subito prima del primo parametro. Questo dice a Excel che vuoi che la formula CERCA.VERT restituisca solo un singolo valore. L’altro modo per modificare la formula sarebbe farlo apparire così nella cella F2:

Ora puoi copiare entrambe le formule dalla cella F2 all’intero intervallo di F2: F8 e non avrai problemi. Perché? Perché (di nuovo) CERCA.VERT in queste istanze restituisce solo un singolo valore, non una matrice di valori.

Il risultato è che il modo migliore per cambiare le tue formule è (1)

assicurati che non ci sia nulla che blocchi la visualizzazione dell’intero array di valori che stai richiedendo a CERCA.VERT per restituire o (2) modifica il primo parametro in modo che faccia riferimento a una singola cella.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (13750) si applica a Microsoft Excel Excel in Office 365.