Merwyn da Inglaterra enviou esse problema.
Existe uma única fórmula na célula B2 que pode ser copiada transversalmente e para baixo para criar uma tabela de multiplicação?
O objetivo de Merwyn é inserir uma única fórmula em B2, que pode ser facilmente copiada para todas as 144 células para criar uma tabela de referência de multiplicação.
Vamos atacar isso como um novato do Excel e ver em quais armadilhas nos deparamos. A reação inicial de alguém pode ser ter a fórmula em B2 =A2*B1. Esta fórmula produz o resultado correto, mas não é adequada para a atribuição de Merwyn.
Quando você copia esta fórmula da célula B2 para a célula C2, as células referenciadas na fórmula também se movem sobre uma célula. A fórmula que =A2*B1agora existia se torna =C1*B2. Este é um recurso desenvolvido no Microsoft Excel e é chamado de referência de fórmula relativa. Conforme você copia uma fórmula, as referências de célula na fórmula também movem um número correspondente de células para cima e para baixo.
Há momentos em que você não deseja que o Excel exiba esse comportamento e o caso atual é um desses. Para evitar que o Excel altere a referência enquanto copia as células, insira um "$" antes da parte da referência que você deseja congelar. Exemplos:
- $ A1 informa ao Excel que você sempre deseja consultar a coluna A.
- B $ 1 informa ao Excel que você sempre deseja consultar a linha 1.
- $ B $ 1 informa ao Excel que você sempre deseja se referir à célula B1.
Aprender esse código diminuirá drasticamente o tempo necessário para construir planilhas. Em vez de inserir 144 fórmulas, Merwyn pode usar essa forma abreviada para inserir uma única fórmula e copiá-la para todas as células.
Olhando para a fórmula de Merwyn =B1*A2, percebemos que a parte "B1" da expressão deve sempre apontar para um número na linha 1. Isso deve ser reescrito como "B $ 1". A seção "A2" da fórmula deve sempre apontar para um número na coluna A. Isso deve ser reescrito como "$ A2". Portanto, a nova fórmula na célula B2 é =B$1*$A2.
Depois de inserir a fórmula em B2, posso copiá-la e colá-la no intervalo apropriado. O Excel segue minhas instruções e depois de fazer a cópia, encontro as seguintes fórmulas:
- Célula M2 contém
=M$1*$A2 - Célula B13 contém
=B$1*$A13 - Célula M13 contém
=M$1*$A13
Cada um desses tipos de fórmulas tem um nome. As fórmulas sem cifrões são chamadas de fórmulas relativas. As fórmulas em que a linha e a coluna estão bloqueadas com um cifrão são chamadas de fórmulas absolutas. As fórmulas em que a linha ou a coluna estão bloqueadas com um cifrão são chamadas de fórmulas mistas.
Existe um método abreviado para inserir os cifrões. Ao digitar uma fórmula e terminar uma referência de célula, você pode pressionar a tecla para alternar entre os 4 tipos de referência. Digamos que você começou a digitar uma fórmula e digitou =100*G87.
- Pressione F4 e sua fórmula muda para
=100*$G$87 - Pressione F4 novamente e sua fórmula muda para
=100*G$87 - Pressione F4 novamente e sua fórmula muda para
=100*$G87 - Pressione F4 novamente e sua fórmula retorna ao original
=100*G87
Você pode fazer uma pausa durante a entrada da fórmula em cada referência de célula para pressionar F4 até obter o tipo de referência correto. A combinação de teclas para inserir a fórmula de Merwyn acima é =B1*A1.
Um dos meus usos favoritos de referências de fórmulas mistas surge quando tenho uma longa lista de entradas na coluna A. Quando quero um método rápido e sujo para encontrar duplicatas, às vezes insiro esta fórmula na célula B4 e, em seguida, copio:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Observe que o segundo termo da fórmula VLOOKUP usa uma referência absoluta ($ A $ 2) e uma referência mista ($ A3) para descrever o intervalo de pesquisa. Em inglês, estou dizendo ao Excel para pesquisar sempre da célula $ A $ 2 até a célula na coluna A logo acima da célula atual. Assim que o Excel encontra um valor duplicado, o resultado dessa fórmula muda de # N / A! a um valor.
Com o uso criativo do $ em referências de células, você pode ter certeza de que uma única fórmula pode ser copiada para várias células com resultados corretos.
