Você precisa copiar uma única fórmula entre linhas e colunas, mas as referências de célula mudam incorretamente. Isso acontece porque as referências relativas padrão se deslocam em todas as direções ao serem copiadas. As referências mistas travam a coluna ou a linha, permitindo construir uma grade com uma fórmula inicial. Este artigo explica como usar o cifrão ($) para criar referências mistas e construir uma tabela de multiplicação completa com uma única fórmula.
Principais Conclusões: Referências Mistas para uma Tabela de Multiplicação
- Referência $A1 (Coluna Travada): A letra da coluna A permanece fixa ao copiar a fórmula horizontalmente, mas o número da linha 1 muda ao copiar verticalmente.
- Referência A$1 (Linha Travada): O número da linha 1 permanece fixo ao copiar a fórmula verticalmente, mas a letra da coluna A muda ao copiar horizontalmente.
- Tecla F4: Percorre os quatro tipos de referência de uma célula selecionada: relativa (A1), absoluta ($A$1) e as duas mistas ($A1, A$1).
Entendendo Referências Mistas de Célula
Uma referência de célula informa ao Excel onde buscar um valor. Por padrão, as referências são relativas, como A1. Ao copiar uma fórmula com A1 uma célula abaixo, ela se torna A2. Ao copiá-la uma célula à direita, torna-se B1. Esse comportamento é útil, mas falha quando você precisa que uma parte da referência permaneça fixa. As referências mistas resolvem isso usando o cifrão ($) para travar a letra da coluna ou o número da linha. Você precisa de um entendimento básico de como inserir fórmulas para usar esse recurso.
Os Dois Tipos de Referência Mista
Uma referência mista é um híbrido entre uma referência relativa e uma absoluta. O cifrão ancora a parte da referência que o precede. Em $A1, o cifrão está antes da letra da coluna ‘A’. Isso significa que a coluna é absoluta e não mudará quando a fórmula for copiada para a esquerda ou direita. O número da linha 1 é relativo e mudará quando a fórmula for copiada para cima ou para baixo. O oposto é verdadeiro para A$1. Aqui, a linha está travada, então o número 1 permanece constante durante a cópia vertical, enquanto a letra da coluna A pode mudar durante a cópia horizontal.
Passos para Construir uma Tabela de Multiplicação com Uma Única Fórmula
O uso clássico de referências mistas é criar uma grade onde uma entrada vem de um cabeçalho de linha e outra de um cabeçalho de coluna. Para uma tabela de multiplicação, você precisa que o número da linha mude ao descer uma coluna e a letra da coluna mude ao atravessar uma linha. Uma única fórmula com as referências mistas corretas pode ser copiada para preencher toda a tabela.
- Configure os cabeçalhos de linha e coluna
Na célula B1, digite o número 1. Na célula C1, digite 2. Selecione ambas as células e arraste a alça de preenchimento para a direita para criar uma linha de cabeçalhos (por exemplo, de 1 a 10). Na célula A2, digite 1. Na célula A3, digite 2. Selecione ambas as células e arraste a alça de preenchimento para baixo para criar uma coluna de cabeçalhos (por exemplo, de 1 a 10). - Insira a fórmula mestre na célula superior esquerda da grade
Clique na célula B2. É aqui que seu primeiro cálculo será feito. Digite um sinal de igual (=) para iniciar a fórmula. - Crie uma referência mista para o cabeçalho da coluna
Com o cursor após o sinal de igual, clique na célula B1 (que contém o cabeçalho da coluna ‘1’). Pressione a tecla F4 no teclado três vezes. Você verá a referência mudar para $B$1, depois B$1, depois $B1. Pare quando aparecer $B1. Isso trava a coluna B, mas permite que a linha mude. - Adicione o operador de multiplicação e uma referência mista para o cabeçalho da linha
Digite um asterisco (*) para multiplicação. Em seguida, clique na célula A2 (que contém o cabeçalho da linha ‘1’). Pressione a tecla F4 duas vezes. A referência mudará de A2 para $A$2, depois para A$2. Pare quando aparecer A$2. Isso trava a linha 2, mas permite que a coluna mude. Sua fórmula completa na célula B2 deve ser =$B1*A$2. - Copie a fórmula para preencher a tabela
Selecione a célula B2. Passe o cursor sobre o pequeno quadrado no canto inferior direito da borda da célula (a alça de preenchimento). Clique e arraste a alça de preenchimento para a direita sobre todos os cabeçalhos de coluna. Em seguida, com toda a primeira linha de fórmulas selecionada, arraste a alça de preenchimento para baixo para preencher todas as linhas. A fórmula única multiplicará corretamente cada cabeçalho de linha por cada cabeçalho de coluna.
Erros Comuns e Limitações
A fórmula retorna um erro #VALOR!
Isso geralmente significa que uma das células de cabeçalho contém texto, não um número. Verifique se os cabeçalhos de linha e coluna são valores numéricos. Uma célula com um apóstrofo antes de um número, como ‘1, é texto. Limpe a célula e redigite o número sem formatação especial.
A tabela exibe zeros ou números incorretos
Isso acontece se as referências mistas estiverem invertidas. Se sua fórmula em B2 for =B$1*$A2, os resultados estarão incorretos porque o elemento errado está travado. A referência do cabeçalho da coluna ($B1) deve ter a coluna travada, e a referência do cabeçalho da linha (A$2) deve ter a linha travada. Reinsira a fórmula com as referências mistas corretas: =$B1*A$2.
Usando referências mistas com referências estruturadas de tabela
Referências mistas não funcionam com referências de coluna de Tabela do Excel, como [@[Coluna1]]. O símbolo @ e as referências estruturadas têm seu próprio comportamento de ancoragem. Para cálculos dentro de uma Tabela, normalmente você usa os nomes das colunas diretamente. Para construir um cálculo em grade, geralmente é mais fácil usar um intervalo de células padrão com referências mistas em vez de converter seus dados em uma Tabela formal.
Comparação entre Referências Relativas, Absolutas e Mistas
| Item | Referência Relativa (A1) | Referência Absoluta ($A$1) | Referência Mista ($A1) | Referência Mista (A$1) |
|---|---|---|---|---|
| Sintaxe | A1 | $A$1 | $A1 | A$1 |
| Comportamento da Coluna ao Copiar | Muda | Permanece fixa | Permanece fixa | Muda |
| Comportamento da Linha ao Copiar | Muda | Permanece fixa | Muda | Permanece fixa |
| Melhor Caso de Uso | Fórmulas simples copiadas em uma direção | Valor constante como taxa de imposto ou custo unitário | Referência a um cabeçalho de coluna em uma grade | Referência a um cabeçalho de linha em uma grade |
| Atalho de Teclado (F4) | Estado padrão | Pressione F4 uma vez | Pressione F4 três vezes | Pressione F4 duas vezes |
Agora você pode usar referências mistas para criar grades dinâmicas como tabelas de multiplicação, matrizes de preços e ferramentas de agendamento. Pratique alterando os cabeçalhos da grade para ver a tabela recalcular instantaneamente. Para uma aplicação mais avançada, tente aninhar uma referência mista dentro de uma função INDEX e CORRESP para criar uma consulta bidirecional que encontra um valor na interseção de uma linha e coluna específicas.