Referência mista no Excel (Sumário)
- Introdução à Referência Mista no Excel
- Exemplos de referência mista no Excel
Introdução à Referência Mista no Excel
A referência é um dos recursos importantes do Excel que ajuda enquanto trabalhamos com operações do Excel usando fórmulas. Referência mista, o próprio nome diz que é uma combinação de referências. A combinação inclui referência relativa e referência absoluta.
Deve-se familiarizar-se com a referência relativa e absoluta para entender e trabalhar com a referência mista. Então, primeiro veremos qual é a referência relativa e o que é uma referência absoluta, como podemos usá-la em fórmulas e tudo será visto com exemplos relacionados.
Exemplos de referência mista no Excel
Aqui estão os exemplos do trabalho de Referência relativa e absoluta com Referência mista no Excel, abaixo.
Você pode baixar este modelo de referência mista do Excel aqui - modelo de referência mista do ExcelExemplo # 1 - Referência relativa
Por padrão, a referência no excel a considerará como referência relativa. Referência não é nada se inserirmos “= A1” na célula B1, então estamos nos referindo A1 em B1. Vamos ver um exemplo para entender a referência relativa.
Considere a captura de tela abaixo dos ganhos e despesas de janeiro a maio.
Agora queremos calcular o dinheiro restante após as despesas pagas com os ganhos. Podemos conseguir isso com a fórmula simples de subtração de despesas dos ganhos. Basta inserir a fórmula ao lado de despesas para encontrar o dinheiro restante a cada mês.
Observe a captura de tela abaixo. Eu inseri a fórmula ao lado das despesas que você pode encontrar na barra de fórmulas.
Se observarmos que a fórmula em D2 é B2-C2, isso significa que D2 está se referindo à combinação de B2 e C2. Quando arrastamos a fórmula para as linhas de fundo, ela será semelhante a D2, ou seja, para D2 tomou referência de B2 e C2 da mesma forma para D3, terá referência de B3 e C3; para D4, referência de B4 e C4 e assim por diante.
Podemos observar as capturas de tela relacionadas por outros meses. A captura de tela abaixo está relacionada ao D3, que se refere a B3 e C3.
Captura de tela relacionada ao D4, que se refere a B4 e C4.
Isso significa que a fórmula sempre considerará a mesma referência e a mesma fórmula que se relaciona a ela. Espero que você tenha entendido o que é uma referência relativa onde podemos usar.
Exemplo # 2 - Referência Absoluta
Considere o mesmo exemplo para entender também a referência absoluta. Vamos considerar na captura de tela acima que temos ganhos e despesas, vamos assumir que fixamos despesas de aluguel, além das despesas disponíveis na tabela.
No excel acima, mencionamos o aluguel em apenas uma célula que é F2. Agora precisamos alterar a fórmula na coluna D para subtrair o aluguel do “dinheiro deixado após as despesas”.
Observe a captura de tela que subtraímos F2 do resultado de B2-C2. Vamos arrastar a fórmula para as outras células também.
Eu arrastei a fórmula, mas ainda assim os resultados não mudam porque, na célula D2, subtraímos F2, conforme explicado anteriormente por padrão, o Excel fará referência relativa; portanto, foi necessário F3 para a fórmula em D3, da mesma forma F4 em D4. Mas não temos nenhum valor nas células F3, F4, F5 e F6, por isso considera essas células como zero e não afetou os resultados.
Como podemos resolver isso sem arrastar o aluguel para as células F3, F4, F5 e F6. Aqui, a referência absoluta ajuda a obter os resultados corretos sem arrastar o aluguel.
É muito simples criar a referência absoluta, basta clicar na fórmula e selecionar a célula F2 e clicar em D2; a referência absoluta será criada.
Se $ for aplicado para o índice de coluna F e o índice de linha 2, a referência absoluta será no nível da célula. Agora arraste a fórmula em D3, D4, D5 e D6 e veja como os resultados mudam.
Observe a fórmula na célula D3: é $ F $ 2 somente depois que arrastamos a fórmula. Como está bloqueado no nível da célula, ele fará referência apenas a F2. Este é o uso de referência absoluta. Isso usará a referência do índice bloqueado.
Exemplo # 3 - Referência Mista
Como mencionado anteriormente na referência mista, usaremos tanto a referência relativa quanto a referência absoluta. Vamos ver como podemos usar os dois usando o mesmo exemplo.
Considere os dados no formato abaixo, conforme mostrado na captura de tela.
Na tabela acima, precisamos encontrar a porcentagem de ganhos de cada mês em relação ao lucro “Total” de 5 meses da mesma forma, porcentagens de despesas e dinheiro deixado após as despesas.
Se ainda não estiver claro, precisamos descobrir 5000 por cento do total de ganhos de 5 meses 32100.
Agora, na tabela abaixo vazia, precisamos encontrar a porcentagem para cada célula.
A fórmula para encontrar a porcentagem é dividida em ganhos de Jan com o total de 32100.
Se você observar, usei a referência absoluta porque todos os ganhos do mês devem ser divididos apenas pelo total. Agora podemos arrastar a fórmula até o mês de maio.
Agora arraste as fórmulas para o fundo também.
Se ainda observarmos que a fórmula para despesas está levando apenas um total de ganhos, mas na verdade deve levar um total de despesas de 15200. Para isso, precisamos criar referência apenas no nível da coluna, não necessária no nível da linha.
Observe que o símbolo da fórmula $ está disponível em N e não no índice de linha 11. Faça as alterações sempre na primeira fórmula e arraste para as outras células.
Em todos os lugares a fórmula está escolhendo corretamente. Se observarmos, executamos a referência absoluta nas operações de linha e a referência relativa nas operações de coluna. Espero que agora esteja claro como executar referências mistas e em quais áreas será útil.
Coisas para lembrar
- Referência mista é uma combinação de referência relativa e referência absoluta.
- Referência relativa é uma referência padrão no Excel enquanto se refere a qualquer célula nas operações do Excel. Nesta outra célula, terá uma referência semelhante à primeira fórmula.
- Uma referência absoluta é outro tipo em que a referência será fixada onde quer que você cole a fórmula.
- Para criar uma referência absoluta, selecione a referência da célula e clique em F4; o símbolo $ será adicionado ao índice de linha e índice de coluna.
- Referência mista é uma combinação de ambos, portanto, nisso podemos aplicar uma referência absoluta apenas ao índice de colunas ou ao índice de linhas.
- $ A1 - significa referência criada para o índice da coluna.
- Uma referência $ 1 - significa criada para o índice de linha.
- $ A $ 1 - significa a referência criada para os índices de linha e coluna.
Artigos recomendados
Este é um guia para Referência mista no Excel. Aqui discutimos como a Referência relativa e absoluta funciona com a Referência mista no Excel, além de exemplos e modelo do Excel para download. Você também pode consultar os seguintes artigos para saber mais -
- Fórmula de DIA no Excel
- Fórmula do tamanho da amostra
- Fórmula HLOOKUP no Excel
- Fórmula VALUE no Excel