O que é erro de derramamento? Como resolver o #spill! Erro no Excel 365

Como todos sabemos. A Microsoft adicionou vários novos recursos ao Excel 365. Um desses recursos é Fórmulas de matriz dinâmica. Geralmente, a fórmula retornaria apenas um único valor como resultado para uma célula. Mas agora, com esse novo recurso em vigor, vários valores podem ser retornados.

Por exemplo, no Excel 2019 e versões anteriores, digamos que você aplique a fórmula = D2: D5 na célula, o resultado seria limitado à primeira célula.

Quando tivemos que aplicar a fórmula em todas as células correspondentes, usamos a notação de matriz (Ctrl+Shift+Enter). No entanto, no Excel 365, não é assim. Quando você aplica a mesma fórmula, automaticamente os valores são derramados em todas as células correspondentes. Consulte a imagem abaixo, para mais detalhes.

A área de células em que o resultado é derramada é chamada Alcance de derramamento. Consulte a imagem abaixo

OBSERVAÇÃO:

• O Derramar está ativado automaticamente com matrizes dinâmicas (atualmente esse recurso é suportado apenas no Excel 365) e o recurso não pode ser desativado.
• O recurso de derramamento é ativado em todas as fórmulas com ou sem funções.

Erros de derramamento são vistos quando a fórmula pretende retornar vários valores, no entanto, os resultados não podem ser colocados nas células. O erro parece o seguinte:

Os possíveis motivos para encontrar o erro #spill são:

• A faixa de derramamento contém algum valor devido aos quais os resultados não podem ser preenchidos nas células
• A faixa de derramamento tem células mescladas.
• Quando folhas antigas (criadas usando o Excel 2016 ou anterior) com fórmulas que apoiam a interseção implícita são abertas no Excel365.
• Quando você aplica a fórmula de matriz dinâmica em uma mesa do Excel.

Se você está vendo o erro #spill no Excel, não se preocupe. Neste artigo, demonstraremos maneiras diferentes de identificar a causa raiz deste problema e também procurar maneiras de corrigir o erro #spill

Índice

• Identifique o que está causando o erro #spill
• Correções a serem seguidas quando o intervalo de derramamento não está em branco
• Corrigir a ser seguido quando a faixa de derramamento fascinam células mescladas
• Corrija a ser seguido quando o intervalo de derramamento na tabela
• Corrigir a ser seguido quando a faixa de derramamento está fora de memória
• Corrigir a ser seguido quando a faixa de derramamento é desconhecida
• Correções a serem seguidas quando a faixa de derramamento é muito grande

Identifique o que está causando o erro #spill

Quando você vê um erro de derramamento, verifique primeiro por que está vendo o erro, para fazê -lo,

Etapa 1: clique na célula que mostra #DERRAMAR! erro

Etapa 2: clique no Ponto de exclamação como mostrado abaixo

Etapa 3: a primeira linha nos diz o que está causando o erro. Por exemplo, neste caso, o erro é visto como o intervalo de derramamento não está em branco

Correções a serem seguidas quando o intervalo de derramamento não está em branco

Siga as correções abaixo quando vir que o A faixa de derramamento não está em branco

Correção 1: Exclua os dados que estão bloqueando a faixa de derramamento

Se já houver alguns dados dentro das células na faixa de derramamento, você verá um erro #Spill na aplicação da fórmula.

Quando você pode ver claramente os dados que estão bloqueando a faixa de derramamento

Considere o exemplo abaixo, quando você aplicar a fórmula = D2: D5 nos dados, um erro de derramamento é lançado como existe Estou aqui dentro da faixa de derramamento.

Para se livrar do erro #spill, basta mover os dados ou excluir os dados do intervalo de derramamento.

Quando os dados bloqueando o intervalo de derramamento estão ocultos

Em alguns casos, os dados que estão bloqueando a faixa de derramamento estão ocultos e não muito óbvios, como visto no caso 1. Considere o exemplo abaixo,

Nesses casos, para encontrar a célula que está bloqueando a faixa de derramamento, siga as etapas abaixo:

Etapa 1: clique na célula que mostra #DERRAMAR! erro

Etapa 2: clique no Ponto de exclamação Como mostrado abaixo, você pode ver que o erro é porque o A faixa de derramamento não está em branco.

Etapa 3: a partir do menu suspenso, clique em Selecione as células obstinadas

Etapa 4: A célula que está bloqueando a faixa de derramamento é destacada como mostrado abaixo

Agora, você sabe qual célula está bloqueando, verifique o que exatamente está causando o problema.

Etapa 5: No exame cuidadoso da célula, você pode ver alguns dados ocultos nas células.

Como visto na imagem acima, há alguns dados. Como a fonte tem uma cor branca, não é fácil reconhecer o bloqueio. Para se livrar do erro, exclua os dados da célula dentro da faixa de derramamento.

Consertar 2: Remova a formatação do número personalizado; ; ; aplicado na célula

Às vezes, quando uma formatação de número personalizada ; ; ; é aplicado em uma célula, há chances de ver o erro de derramamento. Em tais casos,

Etapa 1: clique na célula que mostra #DERRAMAR! erro

Etapa 2: clique no Ponto de exclamação como mostrado abaixo.

Etapa 3: a partir do menu suspenso, clique em Selecione as células obstinadas

Etapa 4: A célula que está bloqueando a faixa de derramamento é destacada como mostrado abaixo

Etapa 5: Clique com o botão direito do mouse na célula obstinada.

Etapa 6: Escolha Formatar celulas

Etapa 7: Formatar as células da janela abre. Vou ao Número aba

Etapa 8: No painel esquerdo, selecione Personalizado

Etapa 9: do painel lateral direito, altere o tipo; ; ; para Em geral

Etapa 10: clique no OK botão

Corrigir a ser seguido quando a faixa de derramamento fascinam células mescladas

Se você vê que o erro é porque o A faixa de derramamento tem células mescladas como mostrado abaixo,

Etapa 1: Clique em Selecione as células obstinadas do suspensão

Etapa 2: A célula obstruira será destacado

Etapa 3: sob o Lar guia, clique em Mesclar e centro

Etapa 4: do suspenso, selecione Células para não rir

Consertar a ser seguido quando Alcance de derramamento na tabela

As fórmulas de matriz dinâmica não são suportadas em mesas do Excel. Se você vir o erro #spill em uma tabela do Excel, como mostrado abaixo, com a mensagem Alcance de derramamento na tabela,

Etapa 1: selecione a tabela completamente

Etapa 2: clique no Design de mesa guia da barra de menu superior

Etapa 3: Escolha Converter para o intervalo

Etapa 4: você verá uma caixa de diálogo de confirmação surgindo, clique em Sim

Corrigir a ser seguido quando a faixa de derramamento está fora de memória

Quando você está tentando identificar a causa do erro #spill, se você vê que o erro afirma Fora da memória, Então isso ocorre porque a fórmula de matriz dinâmica que você está usando referências de grande alcance, nesses casos, Excel Runs fora da memória causando erro de derramamento. Para superar o erro, pode -se tentar fazer referência a um alcance menor.

Corrigir a ser seguido quando a faixa de derramamento é desconhecida

Este erro é visto quando o tamanho das mudanças de matriz derramado e o Excel não é capaz de estabelecer o tamanho da faixa derramada. Geralmente, quando você usa funções aleatórias como Randarray, Rand ou Randbetween, juntamente com funções dinâmicas de matriz, como sequência, esse erro é visto.

Para entender melhor, considere o exemplo abaixo, digamos que a sequência de funções (Randbetween (1.100)) é usada. Aqui, o randbetween gera um número inteiro aleatório maior ou igual a 1 e menor ou igual a 100. E a sequência gera números seqüenciais (por exemplo, sequência (5) gera 1,2,3,4,5). No entanto, o Randbetween é uma função volátil e continua mudando seu valor toda vez que uma folha de excel é aberta ou alterada. Porque, disso, a função de sequência não será capaz de determinar o tamanho da matriz que deve gerar. Não saberia quantos valores gerar e, portanto, lança um erro de derramamento.

Quando você está identificando a causa do erro, você vê A faixa de derramamento é desconhecida

Para corrigir esse tipo de erro, Tente ter uma fórmula diferente que atenda à sua necessidade.

Correções a serem seguidas quando a faixa de derramamento é muito grande

Digamos que você esteja identificando a causa e você percebe que o erro é visto porque o A faixa de derramamento é muito grande como mostrado abaixo.

Quando a matriz dinâmica não estava em vigor, havia algo chamado interseção implícita no Excel que forçado a retornar um único resultado, mesmo quando a fórmula tinha um potencial de retornar vários resultados. Considere um exemplo, se a fórmula = B: B*5% é aplicado no Excel 2019 ou versões anteriores, com a interseção implícita em vigor, o resultado seria o seguinte:

No entanto, quando a mesma fórmula é usada no Excel 365, você vê o seguinte erro

Para resolver isso, tente as seguintes correções

Correção 1: Aplique interseção implícita usando @ operador

Quando dizemos = B: b, A matriz dinâmica fará referência a toda a coluna B. Em vez disso, podemos forçar o Excel a impor interseção implícita usando @ operador

Mudar a fórmula para =@B: b*5%

Como a interseção implícita é adicionada, a fórmula será aplicada a uma única célula. Para estender a fórmula,

1. Basta clicar no ponto como mostrado abaixo

2. Arraste -o para as células, conforme necessário. Isso aplicará a mesma fórmula a essas células.

Correção 2: em vez de se referir à coluna, consulte o intervalo

Na fórmula, = B: B*5% , Estamos nos referindo à coluna B. Em vez, = B2: B4*5%

Isso é tudo

Esperamos que este artigo tenha sido informativo.

Por favor, goste e comente se você foi capaz de corrigir o problema com os métodos acima.

Obrigado pela leitura.