Este artigo ajudará você a entender todas as causas dos erros #SPILL, bem como as soluções para corrigi-los no Excel 365.
#DERRAMAR! é um novo tipo de erro do Excel que ocorre principalmente quando uma fórmula que produz vários resultados de cálculo tenta exibir suas saídas em um intervalo de derramamento, mas esse intervalo já contém alguns outros dados.
Os dados de bloqueio podem ser qualquer coisa, incluindo valor de texto, células mescladas, um caractere de espaço simples ou mesmo quando não há lugar suficiente para retornar os resultados. A solução é simples, limpe o intervalo de quaisquer dados de bloqueio ou selecione uma matriz vazia de células que não contém nenhum tipo de dados.
O erro de vazamento geralmente ocorre ao calcular fórmulas de matriz dinâmica, porque a fórmula de matriz dinâmica é aquela que produz os resultados em várias células ou em uma matriz. Vamos dar uma olhada em mais detalhes e entender o que desencadeia esse erro no Excel e como resolvê-lo.
O que causa um erro de derramamento?
Desde o lançamento de matrizes dinâmicas em 2018, as fórmulas do Excel podem lidar com vários valores ao mesmo tempo e retornar resultados em mais de uma célula. Matrizes dinâmicas são matrizes redimensionáveis que permitem que as fórmulas retornem vários resultados para um intervalo de células na planilha com base em uma fórmula inserida em uma única célula.
Quando uma fórmula de matriz dinâmica retorna vários resultados, esses resultados transbordam automaticamente para as células vizinhas. Este comportamento é denominado ‘Spill’ no Excel. E o intervalo de células onde os resultados se propagam é chamado de ‘intervalo de vazamento’. O intervalo de derramamento irá expandir ou contrair automaticamente com base nos valores de origem.
Se uma fórmula tentar preencher um intervalo de vazamento com vários resultados, mas for bloqueada por algo nesse intervalo, ocorrerá um erro #SPILL.
O Excel agora tem 9 funções que usam a funcionalidade Dynamic Array para resolver problemas, incluindo:
- SEQÜÊNCIA
- FILTRO
- TRANSPOR
- ORGANIZAR
- ORDENAR POR
- RANDARRAY
- ÚNICO
- XLOOKUP
- XMATCH
As fórmulas de matriz dinâmica estão disponíveis apenas no ‘Excel 365’ e atualmente não são suportadas por nenhum software Excel offline (ou seja, Microsoft Excel 2016, 2019).
Os erros de derramamento não são causados apenas pela obstrução de dados, existem vários motivos pelos quais você pode receber o erro #Spill. Deixe-nos explorar as diferentes situações em que você pode encontrar o #SPILL! erro e como corrigi-los.
A faixa de derramamento não está em branco
Uma das principais causas para o erro de derramamento é que o intervalo de derramamento não está vazio. Por exemplo, se você está tentando exibir 10 resultados, mas se houver dados em qualquer uma das células na área de derramamento, a fórmula retornará um #SPILL! erro.
Exemplo 1:
No exemplo abaixo, inserimos a função TRANSPOSE na célula C2 para converter o intervalo vertical das células (B2: B5) em um intervalo horizontal (C2: F2). Em vez de mudar a coluna para uma linha, o Excel nos mostra o #SPILL! erro.
E quando você clica na célula da fórmula, você verá uma borda azul tracejada indicando a área / intervalo do derramamento (C2: F2) que é necessário para exibir os resultados conforme mostrado abaixo. Além disso, você notará um sinal de aviso amarelo com um ponto de exclamação nele.
Para entender o motivo do erro, clique no ícone de aviso ao lado do erro e veja a mensagem na primeira linha destacada em cinza. Como você pode ver, aqui está escrito ‘O intervalo de derramamento não está em branco’.
O problema aqui é que as células no intervalo de derramamento D2 e E2 possuem caracteres de texto (não vazios), portanto, o erro.
Solução:
A solução é simples: limpe os dados (mova ou exclua) localizados no intervalo do derramamento ou mova a fórmula para outro local onde não haja obstrução.
Assim que você excluir ou mover o bloqueio, o Excel preencherá automaticamente as células com os resultados da fórmula. Aqui, quando limpamos o texto em D2 e E2, a fórmula transpõe a coluna para a linha conforme pretendido.
Exemplo 2:
No exemplo abaixo, embora o intervalo de derramamento pareça vazio, a fórmula ainda mostra o sinal de derramamento! erro. É porque o vazamento não está realmente vazio, ele tem um caractere de espaço invisível em uma das células.
É difícil localizar caracteres de espaço ou qualquer outro caractere invisível escondido no que parece ser células vazias. Para encontrar essas células com dados indesejados, clique no floatie Erro (sinal de alerta) e selecione ‘Selecionar células obstrutivas’ no menu e isso o levará para a célula que contém os dados obstrutivos.
Como você pode ver, na imagem abaixo, a célula E2 possui dois caracteres de espaço. Ao limpar esses dados, você obterá a saída adequada.
Às vezes, o caractere invisível pode ser um texto formatado com a mesma cor de fonte da cor de preenchimento da célula ou um valor de célula personalizado formatado com o código numérico ;;;. Quando você formata um valor de célula personalizado com ;;;, isso oculta qualquer coisa nessa célula, independentemente da cor da fonte ou da célula.
Faixa de derramamento contém células mescladas
Às vezes, o #SPILL! o erro ocorre quando o intervalo de derramamento contém as células mescladas. A fórmula de matriz dinâmica não funciona com células mescladas. Para corrigir isso, tudo o que você precisa fazer é desfazer a mesclagem das células no intervalo intermediário ou mover a fórmula para outro intervalo que não tenha células mescladas.
No exemplo a seguir, mesmo que o intervalo de derramamento esteja vazio (C2: CC8), a fórmula retorna o erro de derramamento. É porque as células C4 e C5 estão mescladas.
Para certificar-se de que as células mescladas são o motivo do erro, clique nosinal de aviso e verifique a causa - ‘Spill range has funded cell’.
Solução:
Para desfazer a mesclagem das células, selecione as células mescladas e, na guia ‘Home’, clique no botão ‘Merge & Center’ e selecione ‘Unmerge Cells’.
Se você tiver dificuldade em localizar as células mescladas em sua planilha grande, clique na opção ‘Selecionar células obstrutivas’ no menu do sinal de aviso para pular para as células mescladas.
Faixa de derramamento na tabela
As fórmulas de matriz espalhada não são suportadas em tabelas do Excel. A fórmula de matriz dinâmica deve ser inserida apenas em uma única célula individual. Se você inserir uma fórmula de matriz derramada em uma tabela ou quando a área de derramamento cair em uma tabela, você obterá o erro de derramamento. Quando isso acontecer, tente converter a tabela para um intervalo normal ou mova a fórmula para fora da tabela.
Por exemplo, quando inserimos a seguinte fórmula de intervalo espalhado em uma tabela do Excel, obteríamos um erro Spill em todas as células da tabela, não apenas na célula da fórmula. É porque o Excel copia automaticamente qualquer fórmula inserida em uma tabela para cada célula na coluna da tabela.
Além disso, você receberá um erro de vazamento quando uma fórmula tentar vazar os resultados em uma tabela. Na captura de tela abaixo, a área de derramamento está dentro da tabela existente, então obtemos um erro de derramamento.
Para confirmar a causa por trás desse erro, clique no sinal de aviso e veja o motivo do erro - ‘Faixa de derramamento na tabela’
Solução:
Para corrigir o erro, você precisará reverter a tabela do Excel de volta ao intervalo. Para fazer isso, clique com o botão direito em qualquer lugar da tabela, clique em ‘Tabela’ e selecione a opção ‘Converter para intervalo’. Alternativamente, você pode clicar com o botão esquerdo em qualquer lugar da tabela, ir para a guia ‘Design da tabela’ e selecionar a opção ‘Converter para intervalo’.
Faixa de derramamento é desconhecida
Se o Excel não foi capaz de estabelecer o tamanho da matriz derramada, ele irá disparar o erro de derramamento. Às vezes, a fórmula permite que uma matriz dinâmica seja redimensionada entre cada passagem de cálculo. Se o tamanho da matriz dinâmica continua mudando durante os cálculos passam e não se equilibra, isso causará o erro #SPILL! Erro.
Este tipo de erro de derramamento é geralmente acionado ao usar funções voláteis, como funções RAND, RANDARRAY, RANDBETWEEN, OFFSET e INDIRECT.
Por exemplo, quando usamos a fórmula abaixo na célula B3, obtemos o erro de derramamento:
= SEQUÊNCIA (RANDBETWEEN (1, 500))No exemplo, a função RANDBETWEEN retorna um inteiro aleatório entre os números 1 e 500, e sua saída muda continuamente. E a função SEQUENCE não sabe quantos valores produzir em uma matriz de derramamento. Portanto, o erro #SPILL.
Você também pode confirmar a causa do erro clicando no sinal de Aviso - ‘Faixa de derramamento é desconhecida’.
Solução:
Para corrigir o erro desta fórmula, sua única opção é usar uma fórmula diferente para o cálculo.
O alcance do derramamento é muito grande
Às vezes, você pode executar uma fórmula que produz um intervalo estendido que é muito grande para a planilha manipular e pode se estender além das bordas da planilha. Quando isso acontecer, você receberá #SPILL! erro. Para corrigir esse problema, você pode tentar fazer referência a um intervalo específico ou uma célula em vez de colunas inteiras ou usar o caractere ‘@’ para permitir a interseção implícita
No exemplo abaixo, estamos tentando calcular 20% dos números de vendas na coluna A e retornar os resultados na coluna B, mas em vez disso, obtemos um erro de derramamento.
A fórmula em B3 calcula 20% do valor em A3, a seguir 20% do valor em A4 e assim por diante. Ele produz mais de um milhão de resultados (1.048.576) e espalha todos eles na coluna B, começando na célula B3, mas chegará ao final da planilha. Não há espaço suficiente para mostrar todas as saídas, como resultado, obtemos um erro #SPILL.
Como você pode ver, a causa desse erro é que o - ‘Faixa de derramamento é muito grande’.
Soluções:
Para resolver esse problema, tente alterar a coluna inteira com um intervalo relevante ou uma referência de célula única ou adicione o operador @ para realizar a interseção implícita.
Fix 1: Você pode tentar referir-se a intervalos em vez de colunas inteiras. Aqui, alteramos todo o intervalo A: A com A3: A11 na fórmula, e a fórmula preencherá automaticamente o intervalo com os resultados.
Fix 2: Substitua a coluna inteira apenas com a referência da célula na mesma linha (A3) e, a seguir, copie a fórmula no intervalo usando a alça de preenchimento.
Fix 3: Você também pode tentar adicionar o operador @ antes da referência para realizar a interseção implícita. Isso exibirá a saída apenas na célula da fórmula.
Em seguida, copie a fórmula da célula B3 para o resto do intervalo.
Observação: Ao editar uma fórmula derramada, você só pode editar a primeira célula na área / intervalo de derramamento. Você pode ver a fórmula em outras células do intervalo de derramamento, mas elas estarão esmaecidas e não podem ser atualizadas.
Fora da memória
Se você executar uma fórmula de matriz espalhada que faz com que o Excel fique sem memória, pode disparar o erro #SPILL. Nessas circunstâncias, tente fazer referência a uma matriz ou intervalo menor.
Não reconhecido / Fallback
Você também pode obter um erro Spill mesmo quando o Excel não reconhece ou não consegue reconciliar a causa do erro. Nesses casos, verifique novamente sua fórmula e certifique-se de que todos os parâmetros das funções estão corretos.
Agora você conhece todas as causas e soluções para o #SPILL! erros no Excel 365.