Copiar fórmulas e funções no Microsoft Office Excel é um dos recursos mais importantes e utilizados, porque diminui o tempo em que se resolve a base de dados.
O usuário deve montar de maneira adequada na célula de origem a fórmula e/ou função e depois usar o recurso de Autopreenchimento ou cópia para resolver as células de destino.
É fundamental para qualquer usuário de Excel saber utilizar as referências das células para copiar fórmulas e funções.
Existem três tipos de Referências no Excel:
- Relativa;
- Mista;
- Absoluta.
Referência (Cópia) Relativa
Por padrão o Excel utiliza Referência Relativa. Na cópia Relativa, as letras e números são alterados, de acordo com o sentido em que a fórmula ou função é copiada.
Ao utilizarmos a cópia da fórmula para as linhas, os números são alterados. Ao arrastarmos a fórmula para as colunas as letras são alteradas.
Quando for utilizar a cópia, precisa identificar a célula de Origem e Destino.
Célula de Origem
Para identificar a célula de origem da sua cópia, deve-se analisar o que se deseja copiar. Normalmente é a primeira célula que tem a fórmula ou a função, localizada abaixo do título da base de dados.
Selecione a célula e utilize o comando copiar.
Pode ser através da Guia: Página Inicial, Grupo: Área de Transferência, Botão: Copiar ou utilizando o botão direito do mouse e no menu clique em Copiar ou ainda pelo atalho com a combinação das teclas CTRL+C.
Célula de Destino
Identificada a célula de origem, a próxima etapa é identificar a célula de destino, deve-se analisar para onde se deseja copiar a fórmula e/ou função.
Após ter identificado o local de destino, utilize o comando colar.
Pode ser através da Guia: Página Inicial, Grupo: Área de Transferência, Botão: Colar ou utilizando o botão direito do mouse e no menu clique em Colar ou ainda pelo atalho com a combinação das teclas CTRL+V.
Arrastar Fórmulas e Funções
Com frequência essa cópia de fórmulas ou funções pode ser feita com o Autopreenchimento, simplesmente arrastando da célula de origem para as células de destino.
Clique na célula de origem, observe que no canto inferior direito aparecerá um quadrado, ao posicionar o cursor nesse local, o comportamento do cursor altera para um sinal de mais (+) pressione o botão esquerdo do mouse e com o botão pressionado direcione para as células de destino.
Referência Relativa alterando o endereço das linhas
Abaixo temos uma tabela de compras com a descrição do produto, valor unitário, quantidade comprada e subtotal.
Para calcular o Subtotal será multiplicado o Valor pela Qtde e depois será arrastada a cópia para baixo, assim será flexionado o endereço das linhas e resolvido o subtotal de cada produto:
Referência Relativa alterando linha
Referência Relativa alternado o endereço das colunas
Na próxima tabela temos um controle de vendas da Filial 01 nos meses de Janeiro, Fevereiro e Março.
Esse controle mostra o valor unitário do produto e a quantidade de produtos vendidos. Vamos calcular o Total mensal para descobrir o faturamento de cada mês.
Para descobrir o valor mensal vamos multiplicar o valor unitário pela quantidade vendida no mês de Jan, depois iremos arrastar a cópia para o lado direito, flexionado o endereço das colunas, calculando os meses de Fev e Mar:
Referência Relativa alterando o endereço das Colunas
Assista a videoaula com a explicação do uso de Referência Relativa para linhas e colunas
Link da planilha para download:
Em algumas situações a referência relativa é utilizada de maneira intuitiva, pois toda cópia, por si apenas, será considerada Relativa.
O domínio das referências se torna necessário quando existe a necessidade de fixar valores na cópia das fórmulas ou funções.
Referência (Cópia) Mista
Para fixar valores na cópia das fórmulas ou funções utilizaremos referência mista ou absoluta.
A fixação ou congelamento do endereço da célula é representado pelo símbolo cifrão $. Caso apareça um único $ estamos diante de uma Referência Mista.
- $ antes da letra. Exemplo: $A1 – Referência mista com congelamento de coluna.
- $ antes do número. Exemplo: A$1 – Referência mista com congelamento de linha.
Veja o exemplo:
Referência Mista Excel
Na tabela de cálculo de imposto será multiplicado o valor do imóvel pela taxa do IPTU e Condomínio.
Observe que será utilizada a fórmula: =B$6*$B11 na célula B7, depois será arrastada para as linhas e depois para as colunas.
A célula B$6 se refere ao valor do imóvel, quando arrastar a fórmula para baixo deve manter congelada a linha 6 e quando arrastar para o lado deve manter flexível a coluna pois os valores dos imóveis alteram por setor.
A célula $B11 se refere a alíquota do IPTU, quando arrastar a fórmula para baixo deve flexionar a linha para buscar a alíquota do condomínio e quando arrastar para o lado, deve manter congelada a coluna B.
Assista a videoaula com a explicação do uso de Referência Mista com fixação de linha e também fixação de coluna.
Segue a planilha para download:
É comum ter dúvidas na aplicação da referência mista e o domínio só vem com a prática, então faça simulações, baixe a planilha para acompanhar o vídeo e caso o resultado esteja incorreto, abra a célula com a tecla F2 e compare o que precisa ser copiado com a direção.
Referência (Cópia) Absoluta
Utilizada para a fixação absoluta do endereço da célula, com essa referência tanto o endereço da coluna quanto da linha não alteram na cópia.
Caso o endereço da célula exiba $ antes da letra e também $ antes do número, estamos diante de uma referência absoluta. Exemplo: $A$1
Utilizamos referência absoluta quando a informação da célula se repete em todas as fórmulas ao arrastarmos.
Na tabela abaixo faremos a conversão dos preços dos produtos de Real para Dólar. O valor do dólar, que está na célula C3, essa informação se repete para todas as células, a fórmula utilizada na célula H8 será =C8/$C$3
Uso de referência absoluta com congelamento de coluna e linha no Excel
Assista a videoaula com a explicação do uso de Referência Absoluta.
Segue a planilha para downlaod:
Bons estudos! 😉
Faça o download deste post inserindo seu e-mail abaixo
