Copiar fórmulas sen desprazamento de ligazón

Problema

Supoñamos que temos unha táboa sinxela coma esta, na que se calculan as cantidades para cada mes en dúas cidades, e despois o total se converte en euros ao ritmo da cela amarela J2.

O problema é que se copias o rango D2:D8 con fórmulas noutro lugar da folla, Microsoft Excel corrixirá automaticamente as ligazóns destas fórmulas, movéndoas a un novo lugar e deixará de contar:

Tarefa: copia o rango con fórmulas para que as fórmulas non cambien e sigan igual, mantendo os resultados do cálculo.

Método 1. Ligazóns absolutas

Método 2: desactivar temporalmente as fórmulas

Para evitar que as fórmulas cambien ao copialas, cómpre asegurarse (temporalmente) de que Excel deixe de tratalas como fórmulas. Isto pódese facer substituíndo o signo igual (=) por calquera outro carácter que non se atope normalmente nas fórmulas, como un signo hash (#) ou un par de ampersand (&&) para o tempo de copia. Para isto:

1. Seleccione o rango con fórmulas (no noso exemplo D2:D8)
2. prema Ctrl + H no teclado ou nunha pestana Inicio - Buscar e seleccionar - Substituír (Inicio — Buscar e seleccionar — Substituír)

   

3. No cadro de diálogo que aparece, introduza o que buscamos e o que substituímos, e en Parámetros (Opcións) non esquezas aclarar Ámbito de busca: fórmulas. Prememos Substitúe todo (Substituír todo).
4. Copia o intervalo resultante coas fórmulas desactivadas no lugar correcto:

   

5. Substituír # on = de volta usando a mesma xanela, devolvendo a funcionalidade ás fórmulas.

Método 3: copia a través do Bloc de notas

Este método é moito máis rápido e sinxelo.

Preme o atallo do teclado Ctrl+Ё ou botón Mostrar fórmulas aba fórmula (Fórmulas — Mostrar fórmulas), para activar o modo de verificación de fórmulas: en lugar dos resultados, as celas mostrarán as fórmulas polas que se calculan:

Copia o noso rango D2:D8 e pégao no estándar Caderno:

Agora selecciona todo o pegado (Ctrl + A), cópiao de novo no portapapeis (Ctrl + C) e pégao na folla no lugar que necesites:

Só queda premer o botón Mostrar fórmulas (Mostrar fórmulas)para devolver Excel ao modo normal.

Nota: este método ás veces falla en táboas complexas con celas combinadas, pero na gran maioría dos casos funciona ben.

Método 4. Macro

Se moitas veces ten que facer tal copia de fórmulas sen cambiar as referencias, entón ten sentido usar unha macro para iso. Preme o atallo do teclado Alt + F11 ou botón Visual Basic aba revelador (Desenvolvedor), insira un novo módulo a través do menú Inserir – Módulo  e copia alí o texto desta macro:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range En caso de erro Reanudar Seguinte Establecer copyRange = Application.InputBox("Seleccione celas con fórmulas para copiar.", _ "Copiar fórmulas exactamente", Predeterminado:=Selection.Address, Type := 8) Se copyRange non é nada, entón sae Sub Set pasteRange = Application.InputBox ("Agora selecciona o intervalo de pegado." & vbCrLf & vbCrLf & _ "O intervalo debe ser igual en tamaño ao intervalo de celas " & vbCrLf & _ " orixinal. para copiar." , "Copiar fórmulas exactamente", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Copiar e pegar intervalos varían en tamaño!", vbExclamation, "Erro de copia" Exit Sub End If If pasteRange Non é nada, Saír Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Podes usar o botón para executar a macro. Macros aba revelador (Desenvolvedor - Macros) ou atallo de teclado Alt + F8. Despois de executar a macro, pediralle que seleccione o intervalo coas fórmulas orixinais e o intervalo de inserción e copiará as fórmulas automaticamente:

• Visualización cómoda de fórmulas e resultados ao mesmo tempo
• Por que se necesita o estilo de referencia R1C1 nas fórmulas de Excel
• Como atopar rapidamente todas as celas con fórmulas
• Ferramenta para copiar fórmulas exactas do complemento PLEX