Você pode ter inserido uma fórmula e visto ela preencher automaticamente várias células abaixo ou à direita. Esse comportamento é causado pelo mecanismo de matriz dinâmica do Excel. Uma fórmula de transbordamento calcula um resultado grande demais para uma única célula. Este artigo explica como as fórmulas de transbordamento funcionam e como usá-las de forma eficaz.
Principais Conclusões: Fundamentos das Fórmulas de Transbordamento no Excel
- O intervalo de transbordamento: O grupo de células preenchidas automaticamente com os resultados de uma única fórmula.
- O erro #SPILL!: Aparece quando algo bloqueia o intervalo de transbordamento calculado de ser escrito.
- Funções de matriz dinâmica: Funções como FILTER, SORT e UNIQUE são projetadas para retornar resultados de transbordamento.
O Que São Fórmulas de Transbordamento e Matrizes Dinâmicas?
Fórmulas de transbordamento são um recurso central das matrizes dinâmicas do Excel. Quando uma fórmula retorna vários valores, ela transborda esses resultados para células vizinhas. A célula original contém a fórmula mestre, e as células preenchidas formam o intervalo de transbordamento. Esse comportamento é automático e não requer configuração especial como Ctrl+Shift+Enter usado em versões antigas.
O recurso é alimentado por um novo conjunto de mecanismos de cálculo. Qualquer função que possa retornar uma matriz de valores pode potencialmente transbordar. Isso inclui novas funções como SEQUENCE e XLOOKUP, bem como funções tradicionais usadas de novas maneiras. O intervalo de transbordamento é dinâmico e se expande ou contrai com base na saída da fórmula.
Pré-requisitos para Usar Fórmulas de Transbordamento
Você precisa de uma versão compatível do Excel. As matrizes dinâmicas estão disponíveis no Microsoft 365 e no Excel 2021 para Windows e Mac. Também estão no Excel para a web. O recurso não está disponível no Excel 2019 ou versões perpétuas anteriores. Sua pasta de trabalho deve ser salva no formato .xlsx para preservar o comportamento de transbordamento.
Passos para Criar e Gerenciar Fórmulas de Transbordamento
Você pode criar uma fórmula de transbordamento usando qualquer função que retorne vários resultados. Os passos a seguir mostram um exemplo comum.
- Insira uma função de matriz dinâmica
Selecione uma única célula, como A1. Digite =SEQUENCE(5) e pressione Enter. A fórmula gera os números de 1 a 5, transbordando para baixo da célula A1 para A2:A5. - Referencie todo o intervalo de transbordamento
Para usar os resultados em outro lugar, referencie a célula superior esquerda do intervalo de transbordamento com um símbolo de hash. Na célula C1, digite =SUM(A1#). O # informa ao Excel para somar todo o intervalo de transbordamento de A1, que é A1:A5. - Edite ou limpe a fórmula de transbordamento
Você só pode editar a fórmula na célula original com a borda azul. Clique nessa célula, altere a fórmula na barra de fórmulas e pressione Enter. O intervalo de transbordamento é atualizado automaticamente. Para excluir os resultados, exclua a fórmula apenas da célula original.
Usando Transbordamento com Fórmulas Tradicionais
Você também pode criar comportamento de transbordamento sem novas funções. Por exemplo, selecione a célula D1 e digite =A1:A5*2. Essa fórmula multiplica cada valor no intervalo A1:A5 por 2. Como a operação é realizada em uma matriz, ela transborda cinco resultados para D1:D5. Isso substitui a necessidade de fórmulas de matriz antigas.
Erros Comuns e Resolução de Erros de Transbordamento
O Excel Mostra um Erro #SPILL!
Este é o problema mais comum. O erro significa que algo está bloqueando o intervalo de transbordamento previsto. Clique no ícone de erro ao lado da célula para obter detalhes. A causa usual é uma célula não vazia dentro da área de transbordamento, como texto, um número ou uma fórmula. Limpe a célula obstrutora para resolver o erro.
Intervalo de Transbordamento Muito Grande
Se uma fórmula tentar transbordar além da borda da planilha, você receberá um erro #SPILL!. Por exemplo, =SEQUENCE(1000) na célula A1 de uma planilha com apenas 500 linhas abaixo falhará. Mova a fórmula para um local com linhas e colunas vazias suficientes para acomodar todos os resultados.
Intervalo de Transbordamento Referencia uma Tabela
As Tabelas do Excel têm referências estruturadas que não suportam intervalos de transbordamento dinâmicos. Se sua fórmula referenciar uma coluna de Tabela e transbordar, ela pode ser convertida em um intervalo estático. Para melhores resultados, converta sua Tabela em um intervalo normal primeiro usando Design de Tabela > Converter em Intervalo.
Fórmulas de Transbordamento vs. Fórmulas de Matriz Legadas
| Item | Fórmulas de Transbordamento (Matrizes Dinâmicas) | Fórmulas de Matriz Legadas (CSE) |
|---|---|---|
| Método de Inserção | Pressione Enter normalmente | Exigia Ctrl+Shift+Enter |
| Localização do Resultado | Transborda para várias células automaticamente | Confinado a um intervalo multicélulas pré-selecionado |
| Editabilidade | Edite apenas a célula da fórmula mestre | Deve editar todo o intervalo da matriz como uma unidade |
| Indicação de Erro | Erro #SPILL! com diagnósticos auxiliares | Erros de fórmula genéricos sem orientação específica |
| Suporte a Funções | Todas as novas funções de matriz dinâmica, além de muitas antigas | Limitado a funções que suportavam entrada CSE |
As fórmulas de transbordamento são atualizadas automaticamente quando os dados de origem mudam. Você não pode editar células individuais dentro de um intervalo de transbordamento. Para uso avançado, combine a referência de intervalo de transbordamento A1# com funções como INDEX para extrair valores específicos. Experimente usar a função UNIQUE em uma lista para ver as fórmulas de transbordamento removerem duplicatas dinamicamente.