Referência de célula relativa e absoluta e formatação

2024 Autor: Geoffrey Carr | [email protected]. Última modificação: 2023-12-17 10:54

Nesta lição, discutimos referências de células, como copiar ou mover uma fórmula e formatar células. Para começar, vamos esclarecer o que entendemos por referências a células, que sustentam grande parte do poder e da versatilidade de fórmulas e funções. Um entendimento concreto sobre como as referências de células funcionam permitirá que você obtenha o máximo de suas planilhas do Excel!

NAVEGAÇÃO ESCOLAR

Por que você precisa de fórmulas e funções?
Definindo e Criando uma Fórmula
Referência de célula relativa e absoluta e formatação
Funções úteis que você deve conhecer
Pesquisas, gráficos, estatísticas e tabelas dinâmicas

Nota: Vamos supor que você já sabe que uma célula é um dos quadrados da planilha, organizada em colunas e linhas que são referenciadas por letras e números em execução horizontal e verticalmente.

O que é uma referência de célula?

Uma “referência de célula” significa a célula à qual outra célula se refere. Por exemplo, se na célula A1 você tiver = A2. Então A1 refere-se a A2.

Vamos analisar o que dissemos na Lição 2 sobre linhas e colunas para que possamos explorar mais as referências de células.

Células na planilha são referenciadas por linhas e colunas. As colunas são verticais e rotuladas com letras. As linhas são horizontais e rotuladas com números.

A primeira célula na planilha é A1, que significa coluna A, linha 1, B3 refere-se à célula localizada na segunda coluna, terceira linha e assim por diante.

Para fins de aprendizado sobre referências de células, às vezes as escreveremos como linha, coluna, isso não é uma notação válida na planilha e simplesmente serve para tornar as coisas mais claras.

Tipos de referências de células

Existem três tipos de referências de células.

Absolute - Isso significa que a referência da célula permanece a mesma se você copiar ou mover a célula para qualquer outra célula. Isso é feito ancorando a linha e a coluna, para que não seja alterado quando copiado ou movido.

Relativo - Referenciamento relativo significa que o endereço da célula muda conforme você o copia ou move; isto é, a referência da célula é relativa à sua localização.

Misto - Isso significa que você pode optar por ancorar a linha ou a coluna ao copiar ou mover a célula, de modo que uma seja alterada e a outra não. Por exemplo, você poderia ancorar a referência de linha e depois mover uma célula por duas linhas e por quatro colunas, e a referência de linha permanecerá a mesma. Vamos explicar isso mais abaixo.

Referências Relativas

Vamos nos referir ao exemplo anterior - suponha que na célula A1 tenhamos uma fórmula que simplesmente diga = A2. Isso significa que a saída do Excel na célula A1 é o que for inserido na célula A2. Na célula A2, digitamos “A2” para que o Excel exiba o valor “A2” na célula A1.

Agora, suponha que precisamos criar espaço em nossa planilha para mais dados. Precisamos adicionar colunas acima e linhas à esquerda, então temos que mover a célula para baixo e para a direita para liberar espaço.

Conforme você move a célula para a direita, o número da coluna aumenta. Conforme você o move para baixo, o número da linha aumenta. A célula para a qual ele aponta, a referência da célula, também muda. Isso é ilustrado abaixo:

Continuando com o nosso exemplo, e olhando para o gráfico abaixo, se você copiar o conteúdo da célula A1 dois para a direita e quatro para baixo você o moveu para a célula C5.

Nós copiamos a célula duas colunas para a direita e quatro para baixo. Isso significa que mudamos a célula a qual se refere dois e quatro para baixo. A1 = A2 agora é C5 = C6. Em vez de se referir a A2, agora a célula C5 se refere à célula C6.

O valor mostrado é 0 porque a célula C6 está vazia. Na célula C6, digitamos “Eu sou C6” e agora C5 exibe “Eu sou C6”.

Exemplo: fórmula de texto

Vamos tentar outro exemplo. Lembre-se da Lição 2, onde tivemos que dividir um nome completo em primeiro e último nome? O que acontece quando copiamos esta fórmula?

Escreva a fórmula = DIREITA (A3, LEN (A3) - FIND (“,”, A3) - 1) ou copie o texto para a célula C3. Não copie a célula real, apenas o texto, copie o texto, caso contrário, atualizará a referência.

Você pode editar o conteúdo de uma célula na parte superior de uma planilha na caixa ao lado de onde está escrito "fx". Essa caixa é maior do que a largura de uma célula, por isso é mais fácil editá-la.

Agora nós temos:

Nada complicado, acabamos de escrever uma nova fórmula na célula C3. Agora copie C3 para as células C2 e C4. Observe os resultados abaixo:

Agora temos os primeiros nomes de Alexander Hamilton e Thomas Jefferson.

Use o cursor para destacar as células C2, C3 e C4. Aponte o cursor para a célula B2 e cole o conteúdo. Veja o que aconteceu - recebemos um erro: “#REF”. Por que isso acontece?

Quando copiamos as células da coluna C para a coluna B, atualizamos a referência uma coluna para a esquerda = DIREITA (A2, LEN (A2) - FIND (“,”, A2) - 1).

Ele mudou cada referência para A2 para a coluna à esquerda de A, mas não há coluna à esquerda da coluna A. Portanto, o computador não sabe o que você quer dizer.

A nova fórmula em B2, por exemplo, é = DIREITA (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) e o resultado é #REF:

Copiando uma fórmula para um intervalo de células

Copiar células é muito útil porque você pode escrever uma fórmula e copiá-la para uma área grande e a referência é atualizada.Isso evita ter que editar cada célula para garantir que ela aponte para o local correto.

Por "intervalo" queremos dizer mais de uma célula. Por exemplo, (C1: C10) significa todas as células da célula C1 para a célula C10. Então é uma coluna de células. Outro exemplo (A1: AZ1) é a linha superior da coluna A para a coluna AZ.

Se um intervalo cruzar cinco colunas e dez linhas, você indica o intervalo escrevendo a célula superior esquerda e a inferior direita, por exemplo, A1: E10. Esta é uma área quadrada que cruza linhas e colunas e não apenas parte de uma coluna ou parte de uma linha.

Aqui está um exemplo que ilustra como copiar uma célula para vários locais. Suponha que queremos mostrar nossas despesas projetadas para o mês em uma planilha para que possamos fazer um orçamento. Nós fazemos uma planilha como esta:

Agora copie a fórmula na célula C3 (= B3 + C2) para o restante da coluna para obter um saldo em execução para nosso orçamento. Excel atualiza a referência de célula como você copiá-lo. O resultado é apresentado abaixo:

Como você pode ver, cada nova atualização celular relativo para o novo local, então a célula C4 atualiza sua fórmula para = B4 + C3:

A célula C5 é atualizada para = B5 + C4 e assim por diante:

Referências Absolutas

Uma referência absoluta não muda quando você move ou copia uma célula. Usamos o sinal $ para fazer uma referência absoluta - para lembrar, pense em um sinal de dólar como uma âncora.

Por exemplo, insira a fórmula = $ A $ 1 em qualquer célula. O $ na frente da coluna A significa não alterar a coluna, o $ na frente da linha 1 significa não alterar a coluna quando você copia ou move a célula para qualquer outra célula.

Como você pode ver no exemplo abaixo, na célula B1 temos uma referência relativa = A1.Quando copiamos B1 para as quatro células abaixo dela, a referência relativa = A1 muda para a célula à esquerda, então B2 se torna A2, B3 tornam-se A3, etc. Essas células obviamente não têm valor inserido, então a saída é zero.

No entanto, se usarmos = $ A1 $ 1, como em C1 e copiá-lo para as quatro células abaixo, a referência é absoluta, portanto, ela nunca muda e a saída é sempre igual ao valor na célula A1.

Suponha que você esteja acompanhando seu interesse, como no exemplo abaixo. A fórmula em C4 = B4 * B1 é a “taxa de juros” * “saldo” = “juros por ano”.

Agora, você alterou seu orçamento e economizou mais US $ 2.000 para comprar um fundo mútuo. Suponha que seja um fundo de taxa fixa e pague a mesma taxa de juros. Digite a nova conta e saldo na planilha e, em seguida, copie a fórmula = B4 * B1 da célula C4 para a célula C5.

O novo orçamento é assim:

O novo fundo mútuo ganha US $ 0 em juros por ano, o que não pode ser correto, já que a taxa de juros é claramente de 5%.

O Excel destaca as células às quais uma fórmula faz referência. Você pode ver acima que a referência à taxa de juros (B1) é movida para a célula vazia B2. Deveríamos ter feito a referência a B1 absoluta escrevendo $ B $ 1 usando o sinal de dólares para ancorar a referência de linha e coluna.

Reescreva o primeiro cálculo em C4 para ler = B4 * $ B $ 1 como mostrado abaixo:

Em seguida, copie essa fórmula de C4 para C5. A planilha agora é assim:

Como copiamos a fórmula de uma célula para baixo, ou seja, aumentamos a linha em um, a nova fórmula é = B5 * $ B $ 1. A taxa de juros do fundo mútuo é calculada corretamente agora, porque a taxa de juros está ancorada na célula B1.

Este é um bom exemplo de quando você poderia usar um "nome" para se referir a uma célula. Um nome é uma referência absoluta. Por exemplo, para atribuir o nome "taxa de juros" à célula B1, clique com o botão direito do mouse na célula e selecione "definir nome".

Os nomes podem se referir a uma célula ou a um intervalo, e você pode usar um nome em uma fórmula, por exemplo = interest rate * 8 é a mesma coisa que escrever = $ B $ 1 * 8.

Referências Mistas

Referências misturadas são quando ou a fila ou coluna está ancorada.

Por exemplo, suponha que você seja um fazendeiro fazendo um orçamento. Você também possui uma loja de feeds e vende sementes. Você vai plantar milho, soja e alfafa. A planilha abaixo mostra o custo por acre. O "custo por acre" = "preço por libra" * "libras de sementes por acre" - é isso que lhe custará plantar um acre.

Insira o custo por acre como = $ B2 * C2 na célula D2. Você está dizendo que quer ancorar o preço por coluna de libra. Em seguida, copie essa fórmula para as outras linhas na mesma coluna:

Agora você quer saber o valor do seu inventário de sementes. Você precisa do preço por libra e do número de libras no estoque para saber o valor do estoque.

Adicionamos duas colunas: “libra de semente no estoque” e depois “valor do estoque”. Agora, copie a célula D2 para F4 e observe que a referência de linha na primeira parte da fórmula original ($ B2) é atualizada para linha 4 mas a coluna permanece fixa porque o $ ancora para "B."

Essa é uma referência mista porque a coluna é absoluta e a linha é relativa.

Referências Circulares

Uma referência circular é quando uma fórmula se refere a si mesma.

Por exemplo, você não pode escrever c3 = c3 + 1. Esse tipo de cálculo é chamado de “iteração”, o que significa que ele se repete. O Excel não suporta iteração porque calcula tudo apenas uma vez.

Se você tentar fazer isso, digitando SUM (B1: B5) na célula B5:

O Excel apenas informa que você tem uma referência circular na parte inferior da tela para que você não perceba. Se você tiver uma referência circular e fechar uma planilha e abri-la novamente, o Excel informará em uma janela pop-up se há uma referência circular.

Se você tiver uma referência circular, toda vez que abrir a planilha, o Excel informará, com essa janela pop-up, que há uma referência circular.

Referências a outras planilhas

Uma "pasta de trabalho" é uma coleção de "planilhas". Simplificando, isso significa que você pode ter várias planilhas (planilhas) no mesmo arquivo do Excel (pasta de trabalho). Como você pode ver no exemplo abaixo, nossa pasta de trabalho de exemplo tem muitas planilhas (em vermelho).

Planilhas por padrão são chamadas Sheet1, Sheet2 e assim por diante. Você cria um novo clicando no botão “+” na parte inferior da tela do Excel.

Você pode alterar o nome da planilha para algo útil como "empréstimo" ou "orçamento" clicando com o botão direito do mouse na guia da planilha mostrada na parte inferior da tela do programa Excel, selecionando renomear e digitando um novo nome.

Ou você pode simplesmente clicar duas vezes na aba e renomeá-la.

A sintaxe de uma referência de planilha é = worksheet! Cell. Você pode usar esse tipo de referência quando o mesmo valor é usado em duas planilhas, exemplos disso podem ser: