Fórmula do Excel: Como corrigir o #SPILL! erro -

Índice

Resumo

Um erro #SPILL ocorre quando um intervalo de derramamento é bloqueado por algo na planilha. A solução geralmente é limpar a faixa de derramamento de quaisquer dados obstrutivos. Veja abaixo mais informações e etapas para resolver.

Explicação

Sobre derramamento e o #SPILL! erro

Com a introdução de Dynamic Arrays no Excel, as fórmulas que retornam vários valores "derramam" esses valores diretamente na planilha. O retângulo que envolve os valores é chamado de "intervalo de derramamento". Quando os dados mudam, o alcance do derramamento se expande ou se contrai conforme necessário. Você pode ver novos valores adicionados ou os valores existentes desaparecerem.

Vídeo: Derramamento e alcance do derramamento

Um erro #SPILL ocorre quando um intervalo de derramamento é bloqueado por algo na planilha. Às vezes, isso é esperado. Por exemplo, você inseriu uma fórmula, esperando que ela se espalhe, mas os dados existentes na planilha estão no caminho. A solução é apenas limpar a faixa de vazamento de quaisquer dados que estejam obstruindo.

Às vezes, entretanto, o erro pode ser inesperado e, portanto, confuso. Leia abaixo como esse erro pode ser causado e o que você pode fazer para resolvê-lo.

O comportamento do derramamento é nativo

É importante entender que o comportamento de derramamento é automático e nativo. No Dynamic Excel (atualmente apenas Office 365 Excel), qualquer fórmula, mesmo uma fórmula simples sem funções, pode gerar resultados. Embora haja maneiras de impedir que uma fórmula retorne vários resultados, o derramamento em si não pode ser desabilitado com uma configuração global.

Da mesma forma, não há nenhuma opção no Excel para "desativar os erros #SPILL. Para corrigir um erro #SPILL, você terá que investigar e resolver a causa raiz do problema.

Fix # 1 - limpe a faixa de derramamento

Este é o caso mais simples de resolver. A fórmula deve conter vários valores, mas em vez disso retorna #SPILL! porque algo está no caminho. Para resolver o erro, selecione qualquer célula no intervalo de derramamento para que você possa ver seus limites. Em seguida, mova os dados de bloqueio para um novo local ou exclua todos os dados. Observe que as células no intervalo de derramamento devem estar vazias, portanto, preste atenção às células que contêm caracteres invisíveis, como espaços.

Na tela abaixo, o "x" está bloqueando a faixa de derramamento:

Quando o "x" é removido, a função UNIQUE exibe os resultados normalmente:

Fix # 2 - add @ character

Antes dos Dynamic Arrays, o Excel silenciosamente aplicava um comportamento denominado "interseção implícita" para garantir que certas fórmulas com potencial para retornar vários resultados retornassem apenas um único resultado. No Excel de matriz não dinâmica, essas fórmulas retornam um resultado de aparência normal sem erros. No entanto, em certos casos, a mesma fórmula inserida no Excel dinâmico pode gerar um erro #SPILL. Por exemplo, na tela abaixo, a célula D5 contém esta fórmula, copiada:

=$B$5:$B$10+3

Esta fórmula não geraria um erro, digamos Excel 2016, porque a interseção implícita impediria que a fórmula retornasse vários resultados. No entanto, no Dynamic Excel, a fórmula retorna automaticamente resultados múltiplos para a planilha, e que colidem uns com os outros, já que a fórmula é copiada de D5: D10.

Uma solução é usar o caractere @ para permitir a interseção implícita como esta:

= @$B$5:$B$10+3

Com essa alteração, cada fórmula retorna um único resultado novamente e o erro #SPILL desaparece.

Observação: isso explica parcialmente por que você pode ver repentinamente o caractere "@" aparecer em fórmulas criadas em versões anteriores do Excel. Isso é feito para manter a compatibilidade. Como as fórmulas em versões anteriores do Excel não podem se espalhar em várias células, o @ é adicionado para garantir o mesmo comportamento quando a fórmula é aberta no Excel dinâmico.