Como copiar e colar referências de células absolutas temporariamente como referências relativas no Excel?

Question

No Excel (2007), quando as células que contêm referências absolutas (por exemplo: $A$3 ) são copiadas, a referência absoluta permanece a mesma. Isso é por design e a razão de usar referências absolutas.

Problema - No entanto, às vezes eu quero copiar um bloco de células (que contêm referências absolutas e provavelmente também relativas), e colá-las com as referências absolutas deslocadas corretamente para o novo bloco. Ou seja, eu quero que as referências absolutas se comportem como referências relativas ao copiar, mas ainda sejam referências absolutas no resultado final copiado.

Exemplo - Na captura de tela do exemplo, quero copiar o bloco A2: B3 para baixo. Quando copiado, eu basicamente quero ter a fórmula em B3 ( =$A$3 ) alterada para que se refira à célula à esquerda dela, por exemplo, se tornando =$A$11 quando copiada para B11, como na parte inferior da captura de tela. .

Solução alternativa - Encontrei uma solução alternativa para isso:

1. fazendo uma cópia de toda a planilha (guia da planilha ctrl-drag para o novo local),
2. então cortando (ctrl-X) o bloco relevante de células da nova planilha
3. colando (ctrl-v) na planilha original.
4. finalmente, excluindo a nova planilha temporária (clique com o botão direito do mouse na guia da planilha e exclua).

Pergunta - Mas isso é muitas ações para o meu gosto. Existe uma maneira mais fácil (talvez alguma opção oculta de Colar Especial)?

    

Answer 1

você sempre pode tentar escrever uma macro para isso. excel tem uma ótima ferramenta de gravação de macro que você pode usar também, e então apenas executa conforme necessário (desde que você faça algumas mudanças primeiro no programa)

dim firstLetter as String 
dim secondLetter as String 
dim firstNumber as integer 
dim secondNumber as integer 
dim firstReference as string 
dim secondReference as string 
dim contents as string 
firstLetter = inputbox("Where's the first column? (it's letter)") 
firstNumber = inputbox("And what's the first row? (just the number)") 
secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") 
secondNumber = inputbox("And what row? (the number.)") 
contents = range(firstletter + cstr(firstnumber)).formula 
range(secondletter + cstr(secondnumber)).formula = contents 

    

Answer 2

Um trabalho que tenho usado é:

• crie o bloco de células que você deseja colar, o que inclui todos os seus referenciais absolutos (no exemplo, o bloco intitulado "Bloco original")

• crie a planilha que você gostaria de ver no final copiando e colando em qualquer formação de estilo que desejar. Então, para dar continuidade ao seu exemplo, você usaria o 'Bloco original' e copiaria o mesmo, digamos, 10 vezes abaixo disso, que seria o layout final da planilha. (Você notará que ainda tem o mesmo problema com a célula absoluta que faz referência à (s) célula (s) do bloco original.)

• Pegue e destaque toda a planilha e CUT e copie para uma nova planilha

Você notará então que todas as células foram vinculadas a seus absolutos absolutos que você desejou em sua nova e melhorada planilha.

Boa sorte :)

    

Answer 3

Se eu tentar seguir as etapas de recortar e colar de uma nova planilha, constato que todas as referências em fórmulas permanecem fixas ao copiar para o novo local, incluindo referências relativas. De fato, no Excel 2010 eu acho que depois de cortar e colar fórmulas, a primeira linha e coluna contêm links para as folhas antigas, mas outras linhas e colunas referenciam a nova folha que se parece com um bug ??

Se você deseja copiar um bloco de fórmulas mantendo todas as referências iguais, pressione Ctrl + '(backquote) para mostrar fórmulas e copie e cole clicando no ícone no painel de tarefas Área de transferência (ative usando a seta pequena seção da área de transferência da Aba Inicial). Se não é isso que você está tentando alcançar, um exemplo simples ajudaria.

    

Answer 4

Eu tenho tentado essa macro (armazenada em Personal.xlsb e ligada à tecla de atalho) para converter referências em absolutas antes de copiar.

Sub ToAbsolute()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Sub ToRelative()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlRelative, c)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

    

Answer 5

O seguinte irá funcionar com menos complexidade, depois escrever a sua própria macro e atingir o resultado final.

Sim, eu sei que não estou usando a referência da célula Absolute, mas como mostrado no exemplo do OP você não precisa dela.

Selecione o intervalo que você deseja copiar

Emseguida,nomenususpensodeColar,escolhaaopçãoFórmulas,conformemostradoaqui

Isso foi testado por mim e funciona no Excel 2007 e 2010. Aproveite:)

    

Answer 6

Uma maneira de resolver esse problema é usar apenas referências relativas e seguir o procedimento abaixo para copiar as referências relativas de maneira a tratá-las como absolutas. As fontes para essa resposta incluem estouro de pilha e esta página .

1. Coloque o Excel no modo de visualização de fórmula. A maneira mais fácil de fazer isso é pressionar Ctrl + '(esse caractere é um "apóstrofo inverso" e geralmente está na mesma tecla que tem o ~ (til).
2. Selecione o intervalo para copiar.
3. Pressione Ctrl + C
4. Iniciar o bloco de notas do Windows
5. Pressione Ctrl + V para passar os dados copiados no Bloco de Notas
6. No Bloco de Notas, pressione Ctrl + A seguido por Ctrl + C para copiar o texto (em alguns casos, descobri que precisava voltar ao Excel e limpar a seleção existente antes de usar Ctrl + C no Bloco de Notas)
7. Ative o Excel e ative a célula superior esquerda onde deseja cole as fórmulas. E certifique-se de que a folha para a qual você está copiando está no modo de visualização de fórmula.
8. Pressione Ctrl + V para colar.
9. Pressione Ctrl + 'para sair do modo de visualização de fórmula.

Answer 7

Eu tive um problema semelhante. Eu tenho uma planilha muito grande com milhares de fórmulas, algumas com colunas absolutas e outras com coluna e linha absoluta. Eu queria fazer uma cópia lado a lado na mesma planilha, em vez de fazer uma cópia em uma nova planilha. Eu precisava copiar todas as fórmulas e fazer com que o Excel tratasse referências absolutas como referências relativas.

O Excel alterará todos os endereços como se fossem relativos ao inserir uma coluna à esquerda do intervalo de células com as fórmulas.

1. Faça uma nova folha ao lado da folha original. Esta será a folha com o produto final.

2. Vá para a folha original. Realce as colunas com as fórmulas. Copiar.

3. Vá para a nova planilha e destaque as mesmas colunas. Colar.

4. Vá para a folha original. Insira o número de colunas conforme seu alcance é amplo. Por exemplo, meu alcance tem 20 colunas de largura. Então, inseri 20 colunas à esquerda do meu intervalo, movendo todas as minhas fórmulas e alterando todas as referências como se fossem relativas.

5. Realce as novas colunas. Copiar.

6. Vá para a nova planilha. Realce exatamente as mesmas colunas na nova planilha. Colar.

Answer 8

Eu encontrei o workaroud para duplicação.

Problema: Duplique a seleção da célula inicial B2 e coloque-a na célula inicial P2.

1. Crie uma nova planilha.
2. Na página original, copie tudo o que deseja duplicar e cole-o na nova planilha na mesma célula inicial (se o retângulo selecionado começar em B2, cole-o na nova planilha B2)
3. Mova essa seleção na nova planilha na nova célula inicial correta (mova-a para a célula inicial P2).
4. Copiar seleção.
5. Cole a seleção na folha original na nova célula inicial direita (P2).

Answer 9

Acabei de encontrar uma solução parcial. Não é tão geral quanto a solução alternativa de planilha e intervalo de cópia copiada no OP, mas pode ser muito mais rápido se você precisar copiar algo rapidamente. O que todos nós realmente queremos aqui é uma maneira de desativar temporariamente as referências absolutas enquanto copiamos. Poderia haver uma opção para permitir que você segure o alt ao colar para ignorar todos os $, como se eles simplesmente não estivessem lá, mas na verdade eles ainda são assim que a pasta é concluída.

Então, com isso em mente, copiar um bloco de células enquanto permite que as referências absolutas sejam atualizadas:

1. Realce o intervalo de células que você deseja copiar
2. "Substitua-tudo" pelo $ com uma letra que ainda faz dele uma referência válida.

Devido ao número total de colunas, XFD # é a última referência de célula válida. Certifique-se de que as novas referências geradas não se sobreponham a uma referência real no seu intervalo de células ou que a referência seja quebrada na última etapa. Escolher uma carta de uma coluna que você nunca referencia no seu bloco de células a ser copiado garantirá que você não quebra nenhuma fórmula. Por exemplo, se você substituiu $ por 'h', $ b $ 42 se tornaria hbh42, que ainda é uma referência válida que agora pode ser copiada e será atualizada automaticamente!

3. Finalmente, converta o caractere temporário ('h' no exemplo acima) de volta para $ e pronto!

Você pode tentar substituir o $ por 2 letras em alguns casos, mas isso tem vários problemas, por isso seria mais fácil encontrar uma substituição de 1 letra que não se sobreponha a uma referência real.