Para fazer cálculos precisos no Excel, é essencial entender como funcionam os diferentes tipos de referências de células.
Referências A1 vs. R1C1
As planilhas do Excel contêm muitas células e (por padrão) cada célula é identificada por seu carta de coluna seguido por seu número de linha. Isso é conhecido como referência de estilo A1. Exemplos: A1, B4, C6
Estilo de referência A1
Opcionalmente, você pode mudar para Modo de Referência R1C1 para se referir a uma linha de célula e número da coluna. Em vez de se referir à célula A1, você faria referência a R1C1 (linha 1, coluna 1). A célula C4 seria referida como R4C3.
Estilo de referência R1C1
A referência no estilo R1C1 é extremamente incomum no Excel. A menos que você tenha um bom motivo, provavelmente deve manter o modo de referência padrão do estilo A1. No entanto, se você usar o VBA, provavelmente encontrará esse estilo de referência.
Mudar para o estilo de referência R1C1
Para mudar o estilo de referência, vá para Arquivo> Opção> Fórmula. Marque a caixa ao lado de Estilo de referência R1C1.
Faixas Nomeadas
Um dos recursos mais subutilizados do Excel é o recurso Intervalos nomeados. Em vez de se referir a uma célula (ou grupo de células) por sua localização de célula (ex B3 ou R3C2), você pode nomear esse intervalo e simplesmente fazer referência ao nome do intervalo em suas fórmulas.
Para nomear um intervalo:
- Selecione a célula ou células que deseja nomear
- Clique dentro da caixa de nome do intervalo
- Digite o nome desejado
- Pressione Enter
Agora você pode fazer referência à célula A1 digitando = range_name1. Isso é muito útil ao trabalhar com grandes pastas de trabalho com várias planilhas.
Alcance de Células
Ao usar as funções integradas do Excel, pode ser necessário referenciar intervalos de células. Os intervalos de células aparecem assim ‘A1: B3’. Esta referência se refere a todas as células entre A1 e B3: células A1, A2, A3, B1, B2, B3.
Para selecionar um intervalo de células ao inserir uma fórmula:
- Digite o intervalo (separe o intervalo inicial e final com um ponto e vírgula)
- Use o mouse para clicar na primeira referência de célula, mantenha o botão do mouse pressionado e arraste o intervalo desejado.
- Mantenha pressionada a tecla shift e navegue com as teclas de seta para selecionar seu alcance
Referências absolutas (congeladas) e relativas
Ao inserir referências de células em fórmulas, você pode usar referências relativas ou absolutas (congeladas). As referências relativas se moverão proporcionalmente quando você copiar a fórmula para uma nova célula. As referências absolutas permanecerão inalteradas. Vejamos alguns exemplos:
Referência Relativa
Uma referência relativa no Excel se parece com isto
= A1
Quando você copia e cola uma fórmula com referências relativas, as referências relativas se movem proporcionalmente. A menos que você especifique o contrário, suas referências de célula serão relativas (descongeladas) por padrão.
Exemplo: se você copiar ‘= A1’ uma linha abaixo, a referência muda para ‘= A2’.
Referências celulares absolutas (congeladas)
Se você não quiser que suas referências de célula se movam ao copiar uma fórmula, você pode “congelar” suas referências de célula adicionando cifrões ($ s) na frente da referência que deseja congelar. Agora, ao copiar e colar a fórmula, a referência da célula permanecerá inalterada. Você pode optar por congelar a referência de linha, referência de coluna ou ambas.
A1: Nada está congelado
$ A1: a coluna está congelada, mas a linha não está congelada
A $ 1: a linha está congelada, mas a coluna não está congelada
$ A $ 1: a linha e a coluna estão congeladas
Atalho de referência absoluta
Adicionar manualmente cifrões ($ s) em suas fórmulas não é muito prático. Em vez disso, ao criar sua fórmula, use a tecla F4 para alternar entre referências de células absolutas / relativas.
Exemplo de referência de célula absoluta
Quando você realmente precisa congelar uma referência de célula? Um exemplo comum é quando você tem células de entrada que são referenciadas com frequência. No exemplo abaixo, queremos calcular o imposto sobre vendas para cada quantidade de itens de menu. O imposto sobre vendas é constante em todos os itens, portanto, faremos referência à célula do imposto sobre vendas repetidamente.
Para saber o total do imposto sobre vendas, insira a fórmula ‘= (B3 * C3) * $ C $ 1’ na coluna D e copie a fórmula para baixo.
Referência mista
Você pode ter ouvido falar de referências de células mistas. Uma referência mista ocorre quando a referência de linha ou coluna está bloqueada (mas não ambas).
Referência mista
Lembre-se de que, usando a tecla “F4”, você pode percorrer suas referências de células absolutas e relativas.
Referências de célula - Inserindo e excluindo linhas / colunas
Você pode estar se perguntando o que acontece com as referências de célula quando você insere ou exclui linhas / colunas?
A referência da célula será atualizada automaticamente para se referir à célula original. Este é o caso independentemente de a referência de célula estar congelada.
Referências 3D
Às vezes, você pode precisar trabalhar com várias planilhas com padrões de dados idênticos. O Excel permite que você consulte várias planilhas de uma vez, sem a necessidade de inserir manualmente cada planilha. Você pode fazer referência a um intervalo de planilhas da mesma forma que faria referência a um intervalo de células. O exemplo 'Folha1: Folha5! A1' faria referência às células A1 em todas as folhas da Folha1 à Folha5.
Vejamos um exemplo:
Você deseja somar o total de unidades vendidas de cada produto em todas as lojas. Cada loja possui sua própria planilha e todas as planilhas possuem formato idêntico. Você pode criar uma fórmula semelhante a esta:
Isso não é muito difícil com apenas quatro planilhas, mas e se você tivesse 40 planilhas? Você realmente deseja adicionar manualmente cada referência de célula?
Em vez disso, você pode usar uma referência 3D para fazer referência a várias folhas de uma vez com facilidade (semelhante a como você pode fazer referência a um intervalo de células).
Tome cuidado! A ordem das suas planilhas é importante. Se você mover outra folha entre as folhas referenciadas (StoreA e StoreD), essa folha será incluída. Por outro lado, se você mover uma folha para fora do intervalo de folhas (antes de StoreA ou depois de StoreD), ela não será mais incluída.
Referência de célula circular
Uma referência de célula circular é quando uma célula se refere a si mesma. Por exemplo, se o resultado da célula B1 for usado como uma entrada para a célula B1, uma referência circular será criada. A célula não precisa se referir diretamente a si mesma. Pode haver etapas intermediárias.
Exemplo:
Nesse caso, a fórmula para a célula B2 é “A2 + A3 + B2”. Como você está na célula B2, você não pode usar B2 na equação. Isso acionaria a referência circular e o valor na célula “B2” será automaticamente definido como “0”.
Normalmente, as referências circulares são resultado de um erro do usuário, mas há circunstâncias em que você pode querer usar uma referência circular. O principal exemplo de uso de uma referência circular é calcular valores iterativamente. Para fazer isso, você precisa ir para Arquivo> Opções> Fórmulas e Cálculo Iterativo Habilitado:
Referências Externas
Às vezes, ao calcular dados, você pode precisar consultar dados fora de sua pasta de trabalho. Isso é chamado de referência externa (link).
Para selecionar uma referência externa ao criar uma fórmula, navegue até a pasta de trabalho externa e selecione sua referência como faria normalmente.
Para navegar para a outra pasta de trabalho, você pode usar o atalho CTRL + TAB ou ir para Exibir> Alternar janelas.
Depois de selecionar a célula, você verá uma referência externa semelhante a esta:
Observe que o nome da pasta de trabalho está entre colchetes [].
Depois de fechar a pasta de trabalho referenciada, a referência mostrará o local do arquivo:
Ao reabrir a pasta de trabalho que contém o link externo, você será solicitado a ativar a atualização automática de links. Se você fizer isso, o Excel abrirá o valor de referência com o valor atual da pasta de trabalho. Mesmo se estiver fechado! Tome cuidado! Você pode ou não querer isso.
Intervalos nomeados e referências externas
O que acontece com sua referência de célula externa quando linhas / colunas são adicionadas ou excluídas da pasta de trabalho de referência? Se ambas as pastas de trabalho estiverem abertas, as referências de célula serão atualizadas automaticamente. No entanto, se ambas as pastas de trabalho não estiverem abertas, as referências de célula não serão atualizadas e não serão mais válidas. Essa é uma grande preocupação ao vincular a pastas de trabalho externas. Muitos erros são causados por isso.
Se você vincular a pastas de trabalho externas, deve nomear a referência de célula com um intervalo nomeado (consulte a seção anterior para obter mais informações). Agora sua fórmula se referirá ao intervalo nomeado, não importa quais mudanças ocorram na pasta de trabalho externa.
