una - Deshabilitar cambio automático de referencia de celda de Excel después de copiar/pegar

referencia circular en excel 2013

Question

Tengo una enorme hoja de cálculo de Excel 2003 en la que estoy trabajando. Hay muchas fórmulas muy grandes con muchas referencias celulares. Aquí hay un ejemplo simple.

=''Sheet''!AC69+''Sheet''!AC52+''Sheet''!AC53)*$D$3+''Sheet''!AC49

La mayoría de ellos son más complicados que eso, pero esto da una buena idea de con qué estoy trabajando. Pocas de estas referencias de celda son absolutas ($ s). Me gustaría poder copiar estas celdas en una ubicación diferente sin cambiar las referencias de celda. Sé que puedo usar f4 para hacer que las referencias sean absolutas, pero hay una gran cantidad de datos y es posible que necesite usar Fill más tarde. ¿Hay alguna forma de desactivar temporalmente el cambio de referencia de celda en copy-paste / fill sin hacer que las referencias sean absolutas?

EDITAR: Acabo de descubrir que puede hacer esto con VBA al copiar el contenido de la celda como texto en lugar de una fórmula. Sin embargo, me gustaría no tener que hacer esto porque quiero copiar filas / columnas enteras a la vez. ¿Hay una solución simple que me falta?

Answer 1

Creo que está atascado con la solución que mencionó en su edición.

Comenzaría por convertir cada fórmula en la hoja para enviar un texto más o menos así:

Dim r As Range

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 1) = "=") Then
        r.Formula = "''ZZZ" & r.Formula
    End If
Next r

donde el ''ZZZ usa el '' para indicar un valor de texto y el ZZZ como un valor que podemos buscar cuando queremos convertir el texto nuevamente en una fórmula. Obviamente si alguna de sus celdas realmente comienza con el texto ZZZ entonces cambie el valor de ZZZ en la macro de VBA a otra cosa

Cuando se complete la reorganización, yo convertiría el texto a una fórmula como esta:

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 3) = "ZZZ") Then
        r.Formula = Mid$(r.Formula, 4)
    End If
Next r

Una desventaja real de este método es que no puede ver los resultados de ninguna fórmula mientras está reorganizando. Puede encontrar que cuando convierte desde texto a fórmula, tiene una serie de errores #REF , por ejemplo.

Podría ser beneficioso trabajar en esto en etapas y volver a convertir en fórmulas cada cierto tiempo para verificar que no se hayan producido catástrofes.

Answer 2

Desde http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2 :

1. Ponga Excel en modo de vista de fórmula. La forma más fácil de hacer esto es presionar Ctrl + `(ese carácter es un" apóstrofo hacia atrás ", y generalmente está en la misma clave que tiene el ~ (tilde).
2. Seleccione el rango para copiar.
3. Presiona Ctrl + C
4. Inicie el Bloc de notas de Windows
5. Presiona Ctrl + V para pasar los datos copiados al Bloc de notas
6. En el Bloc de notas, presione Ctrl + A seguido de Ctrl + C para copiar el texto
7. Active Excel y active la celda superior izquierda donde desea pegar las fórmulas. Y asegúrese de que la hoja en la que está copiando esté en modo de vista de fórmula.
8. Presione Ctrl + V para pegar.
9. Presione Ctrl + `para alternar fuera del modo de vista de fórmula.

Nota: Si la operación de pegar nuevamente a Excel no funciona correctamente, es probable que haya utilizado la característica de Excel Text-to-Columns recientemente, y Excel está tratando de ser útil al recordar cómo analizó sus datos por última vez. Debe iniciar el Asistente para convertir texto en columnas. Elija la opción Delimitado y haga clic en Siguiente. Borre todas las marcas de opción de Delimitador excepto Tab.

O bien, desde http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/ :

If you''re a VBA programmer, you can simply execute the following code: 
With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Answer 3

Encontré otra solución que es muy simple: 1. Cortar los contenidos 2. Pegarlos en la nueva ubicación 3. Copie los contenidos que acaba de pegar en la nueva ubicación que desea. 4. Deshaga la operación de cortar y pegar, colocando los contenidos originales donde los obtuvo. 5. Pegue los contenidos del portapapeles en la misma ubicación. Estos contenidos tendrán las referencias originales.

Parece mucho, pero es superrápido con atajos de teclado: 1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

Answer 4

Es común usar Buscar / Reemplazar para deshabilitar fórmulas mientras se realizan manipulaciones. Por ejemplo, copia con transposición. La fórmula está desactivada al colocar un marcador de posición (por ejemplo, "$ =") delante de él. Buscar reemplazar puede deshacerse de estos una vez que la manipulación se haya completado.

Esta vba simplemente automatiza el hallazgo / reemplazo de la hoja activa.

'' toggle forumlas on active sheet as active/ inactive
'' by use of "$=" prefix

Sub toggle_active_formulas()
    Dim current_calc_method As String
    initial_calc_method = Application.Calculation
    Application.Calculation = xlCalculationManual
    Dim predominant As Integer
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        If c.HasFormula Then
            predominant = predominant + 1
        ElseIf "$=" = Left(c.Value, 2) Then
            predominant = predominant - 1
        End If
    Next c
    If predominant > 0 Then
        For Each c In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If c.HasFormula Then
                c.Value = "$" & c.Formula
            End If
        Next c
    Else
        For Each c In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If "$=" = Left(c.Value, 2) Then
                c.Formula = Right(c.Value, Len(c.Value) - 1)
            End If
        Next c
    End If
    Application.Calculation = initial_calc_method
End Sub

Answer 5

Este truco simple funciona: copiar y pegar. NO corte y pegue. Después de pegar, vuelva a seleccionar la pieza que copió y vaya a EDITAR, deslice hacia abajo para BORRAR y BORRE CONTENIDOS.

Answer 6

Haga clic en la celda que desea copiar. En la barra de fórmulas, resalta la fórmula.

Presione Ctrl C

Presione escape (para eliminar la edición activa de esa fórmula).

Elija una nueva celda. Ctrl V.

Answer 7

Llegué a este sitio buscando una forma fácil de copiar sin cambiar las referencias de celda. Pero ahora creo que mi propia solución es más simple que la mayoría de estos métodos. Mi método se basa en el hecho de que Copiar cambia las referencias, pero Mover no lo hace. Aquí hay un ejemplo simple.

Supongamos que tiene datos brutos en las columnas A y B, y una fórmula en C (p. Ej., C = A + B) y quiere la misma fórmula en la columna F, pero copiar de C a F conduce a F = D + E.

1. Copie los contenidos de C a cualquier columna temporal vacía, digamos R. Las referencias relativas cambiarán a R = P + Q pero ignorará esto incluso si está marcado como un error.
2. Regrese a C y muévase (no lo copie) a F. La fórmula no debe cambiarse, así que F = A + B.
3. Ahora vaya a R y cópielo de nuevo a C. La referencia relativa volverá a C = A + B
4. Eliminar la fórmula temporal en R.
5. Bob es tu tio.

Lo hice con una variedad de celdas, así que me imagino que funcionaría prácticamente con cualquier nivel de complejidad. Solo necesita un área vacía para estacionar las celdas protegidas. Y, por supuesto, debes recordar dónde los dejaste.

Answer 8

No he marcado en Excel, pero esto funciona en Libreoffice4:

Todo el asunto de la reescritura de direcciones viene durante consecutiva
(a1) corte
(a2) pegar

Debe interrumpir la secuencia al poner algo intermedio:
(b1) corte
(b2) seleccione algunas celdas vacías (más de 1) y arrástrelas (muévalas)
(b3) pegar

El paso (b2) es donde la celda que está a punto de actualizarse detiene el rastreo. Rápido y simple.

Answer 9

Pruebe esto: haga clic con el botón izquierdo en la pestaña, haga una copia de la hoja completa, luego corte las celdas que desea mantener igual y péguelas en su hoja de trabajo original.

Answer 10

Solución simple que utilicé ahora mismo en una situación similar:

1. Haz un duplicado de la hoja de trabajo
2. Cortar + Pegar desde la hoja de trabajo duplicada
3. Eliminar hoja de trabajo duplicada para limpiar

Sus referencias de celda ahora se copian sin que se modifiquen.

Answer 11

Una solución muy simple es seleccionar el rango que desea copiar, luego Buscar y Reemplazar ( Ctrl + h ), cambiando = a otro símbolo que no se usa en su fórmula (por ejemplo, # ), impidiendo así que sea una fórmula activa.

Luego, copie y pegue el rango seleccionado a su nueva ubicación.

Finalmente, Buscar y Reemplazar para cambiar # nuevo a = en el rango original y nuevo, restaurando ambos rangos para que sean nuevamente fórmulas.