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

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 de células, que sustentam grande parte do poder e versatilidade de fórmulas e funções. Um entendimento concreto de como as referências de células funcionam permitirá que você obtenha o máximo de suas planilhas do Excel!

NAVEGAÇÃO ESCOLAR

1. Por que você precisa de fórmulas e funções?
2. Definindo e Criando uma Fórmula
3. Referência de célula relativa e absoluta e formatação
4. Funções úteis que você deve conhecer
5. 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 horizontalmente 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 revisar 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 se destina a 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; ou seja, 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, em seguida, mover uma célula para baixo em duas linhas e em 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, ele irá 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 que a largura de uma célula, por isso é mais fácil editá-la.

Agora 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?

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. Então 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. O Excel atualiza a referência da célula conforme você a copia. 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 isso, 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 tornar-se A3, etc. Essas células, obviamente, não têm valor introduzido, 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, 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. Insira a nova conta e saldo na planilha e 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 estar certo, 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 ao B1 absoluto 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" - é o que custará para você 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 estoque de sementes. Você precisa do preço por libra e do número de libras em 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."

Esta é 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:

Uma tela de aviso aparece:

O Excel apenas informa que você tem uma referência circular na parte inferior da tela para que você possa não notar. Se você tiver uma referência circular e fechar uma planilha e abri-la novamente, o Excel informará em uma janela pop-up se você tem 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 você tem 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 de 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 guia e renomeá-la.

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

• Data de hoje
• Taxa de conversão de moeda de dólares para euros
• Tudo o que é relevante para todas as planilhas na pasta de trabalho

Abaixo está um exemplo de planilha “interesse” fazendo referência à planilha “empréstimo”, célula B1.

Se olharmos para a planilha de “empréstimo”, podemos ver a referência ao valor do empréstimo:

A seguir…

Esperamos que você tenha uma compreensão firme das referências de células, incluindo relativa, absoluta e mista. Há certamente muito.

É isso para a lição de hoje, na Lição 4, discutiremos algumas funções úteis que você pode desejar para o uso diário do Excel.