Comment faire pour désactiver Excel automatique de la référence de cellule changer après le copier/coller?

J'ai une énorme feuille de calcul Excel 2003, je travaille sur. Il y a beaucoup de très grandes formules avec beaucoup de références de cellule. Voici un exemple simple.

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

La plupart d'entre eux sont plus compliquées que cela, mais cela donne une bonne idée de ce que je suis en train de travailler avec. Quelques-uns de ces cellules sont des références absolues ($s). Je voudrais être en mesure de copier ces cellules vers un emplacement différent sans les références de cellules en train de changer. Je sais que je peux tout simplement utiliser f4 pour faire les références absolues, mais il y a beaucoup de données, et j'ai peut-être besoin d'utiliser le Remplir plus tard. Est-il possible de désactiver temporairement la référence de la cellule en train de changer au copier-coller/remplir sans en faire des références absolues?

EDIT: je viens de constater que vous pouvez faire cela avec VBA en copiant le contenu de la cellule en tant que texte au lieu d'une formule. J'aimerais ne pas avoir à le faire, mais parce que je veux copier l'ensemble des lignes/colonnes à la fois. Est-il une solution simple, je suis absent?

Excellente question! Peut une boucle en VBA parvenez pas à résoudre votre problème restant à résoudre?

InformationsquelleAutor Bat Masterson | 2011-07-12

54

De http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:
1. Mettre Excel dans la formule de mode d'affichage. La façon la plus simple pour ce faire est d'appuyer sur Ctrl+` (ce caractère est un "à rebours de l'apostrophe," et est généralement sur la même touche que a la ~ (tilde).
2. Sélectionnez la plage à copier.
3. Appuyez sur Ctrl+C
4. Démarrez Le Bloc-Notes Windows
5. Appuyez sur Ctrl+V pour passé les données copiées dans le bloc-notes
6. Dans le bloc-notes, appuyez sur Ctrl+Un suivi de Ctrl+C pour copier le texte
7. Activer Excel et activer la cellule en haut à gauche où vous souhaitez coller les formules. Et, assurez-vous que la feuille de copie est dans la formule de mode d'affichage.
8. Appuyez sur Ctrl+V pour coller.
9. Appuyez sur Ctrl+` pour basculer de la formule de mode d'affichage.
Remarque: Si l'opération de collage vers Excel ne fonctionne pas correctement, les chances sont que vous avez utilisé Excel Texte en Colonnes fonctionnalité récemment, et Excel est d'essayer d'être utile de se rappeler comment vous dernier analysé vos données. Vous avez besoin de tirer jusqu'à la conversion de Texte en Colonnes de l'Assistant. Choisissez l'option Délimité et cliquez sur Suivant. Effacer tous les Délimiteur option coches à l'exception de l'Onglet.

Ou, à partir de 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
```
- Il semble que vous pouvez coller dans une feuille qui n'est pas dans la Formule de Vue Mode, il ne fait pas de mal à suivre cela à la lettre même si, comme tout le reste est strictement nécessaire. +1 comme cela a été très utile et facile à suivre. Je ne comprends pas pourquoi Excel suppose que nous voulons faire référence à une cellule dans un autre fichier, mais au moins, il y a un bon travail.
- Cette réponse est merveilleux - je souhaite que je pourrais l'accepter pour l'affiche originale. Je suis un programmeur donc VBA ne m'effraie pas, mais je ne vais pas l'utiliser pour copier et coller des cellules si je peux l'éviter... Il faudrait vraiment un "Coller sans formule de mise à jour" de l'option de construire dans Excel.
- Oui exactement. Je ne sais pas pourquoi ils ne vont pas mettre la Pâte sans formule de mise à jour. Ce qui me dérange le plus, c'est qu'il y a plus de feuille de calcul fournisseurs, MS Office, Google Feuille, ou WPS Office et ils viennent juste de répéter les uns les autres. Je m'ennuie de l'époque où ils ont été innové avec la technologie et de fonctionnalités. Ils ont fait une énorme amélioration à chaque sortie. Mais certains trucs de base sont simplement manquantes.
InformationsquelleAutor Whit Kemmey
22

Une solution très simple consiste à sélectionner la plage que vous souhaitez copier, puis Rechercher et Remplacer (Ctrl + h), la modification des = à un autre symbole qui n'est pas utilisée dans votre formule (par exemple,#) - l'empêchant ainsi d'être un actif de formule.

Ensuite, copiez et collez la plage sélectionnée pour son nouvel emplacement.

Enfin, Rechercher et Remplacer pour modifier # retour à = à la fois l'original et la nouvelle gamme, restituant ainsi à la fois des plages d'être formules.
- Jusqu'à présent, malheureusement, c'est la solution que je suis la plupart du contenu. Il aurait été plus pratique avec un autre raccourci qui a simplement fait ce.
InformationsquelleAutor Alistair Collins
6

Que je suis sur ce site à la recherche d'un moyen facile de copier sans changer les références de la cellule. Mais maintenant, je thnk ma propre solution de contournement est plus simple que la plupart de ces méthodes. Ma méthode repose sur le fait que les modifications apportées au texte des références, mais Déplacer ne fonctionne pas. Voici un exemple simple.

Supposons que vous avez les données brutes dans les colonnes A et B, et une formule dans C (C=A+B) et que vous souhaitez la même formule dans la colonne F, mais la Copie de C à F conduit à F=D+E.
1. Copier le contenu de C à vide temporaire, la colonne, disons R. Les références relatives va changer pour R=P+Q, mais ignorer ce même si elle est considérée comme une erreur.
2. Revenir à C et Déplacer (pas de Copie) à F. Formule devrait être inchangé donc F=A+B.
3. Maintenant, allez à la R et la copier à C. de référence Relative reviendra à C=A+B
4. Suppression temporaire de la formule de R.
5. Bob's your uncle.
Je l'ai fait avec une plage de cellules, donc j'imagine qu'il fonctionne avec pratiquement n'importe quel niveau de complexité. Vous avez juste besoin d'un endroit vide sur le parking de la coiped cellules. Et bien sûr, vous devez vous rappeler où vous les avez laissés.
- La Description n'est pas simple 🙂 Si vous souhaitez copier la ligne 1 (qui renvois à la ligne 2) à la ligne 11, alors: 1. Copie vers la ligne 11, il va de référence de la ligne 12. 2. Couper et coller à la ligne 21, alors il sera toujours de référence de la ligne 12. 3. Copier à partir de 21 à 11, puis il fera référence à la ligne 2, tout aussi nécessaire.
- Il me paraît simple, copie d'un décalage, déplacer l'original, d'une copie de l'offset, les supprimer de l'offset.
- Pour moi c'est le plus simple puisque je ne peux presque le faire les yeux fermés, les mains sur le clavier. De Plus, vous pouvez faire des zones entières ou des lignes en une seule fois: Ctrl+C -> Ctrl+V, puis Ctrl+X -> Ctrl+V, répétez aussi souvent que nécessaire. E. g. pour dupliquer quelques lignes dans une table avec des références à l'extérieur du tableau, c'est le meilleur: il met à jour les références à l'intérieur de la sélection, tout en préservant les références à l'extérieur de la sélection.
InformationsquelleAutor JimH
1

Cette simple astuce fonctionne: Copier et Coller. Ne PAS couper et coller. Après la pâte, puis sélectionnez à nouveau la partie que vous avez copié et allez à MODIFIER, faites glisser vers le bas pour EFFACER et EFFACER le CONTENU.

InformationsquelleAutor Bob R
0

Je pense que vous êtes coincé avec la solution de contournement que vous avez mentionné dans votre montage.

Je voudrais commencer par la conversion de chaque formule dans la feuille de texte à peu près comme ceci:
```
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
```
où la 'ZZZ utilise le ' pour indiquer une valeur de texte et la ZZZ comme une valeur que l'on peut regarder quand on veut convertir le texte à la formule. Évidemment, si l'un de vos cellules de réellement commencer avec le texte ZZZ puis changer le ZZZ valeur de la macro VBA pour quelque chose d'autre

Lors de la réorganisation terminée, je puis convertir le texte d'une formule comme ceci:
```
For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 3) = "ZZZ") Then
        r.Formula = Mid$(r.Formula, 4)
    End If
Next r
```
Un réel inconvénient de cette méthode est que vous ne pouvez pas voir les résultats de toute formule, tout en vous ré-organiser. Vous pouvez constater que lorsque vous convertissez en arrière à partir du texte de la formule que vous avez un tas de #REF erreurs par exemple.

Il pourrait être intéressant de travailler sur cette question dans les stades et les convertir en retour à des formules de temps à autre pour vérifier l'absence de catastrophes ont eu lieu

InformationsquelleAutor barrowc

Il est courant d'utiliser la fonction rechercher/remplacer pour désactiver les formules, tout en effectuant des manipulations. Par exemple, copier avec transposer. La formule est désactivé en plaçant une partie de l'espace réservé (par exemple,"$=") en face de lui. Rechercher remplacer pouvez vous débarrasser de ces une fois la manipulation terminée.

Ce vba simplement automatise le rechercher/remplacer pour la feuille active.

' 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

Pouvez-vous ajouter plus de détails à ce poste s'il vous plaît?

InformationsquelleAutor Chris

Cette macro ne l'ensemble de la tâche.

Sub Absolute_Reference_Copy_Paste()
'By changing "=" in formulas to "#" the content is no longer seen as a formula.
' C+S+e (my keyboard shortcut)

Dim Dummy As Range
Dim FirstSelection As Range
Dim SecondSelection As Range
Dim SheetFirst As Worksheet
Dim SheetSecond As Worksheet

On Error GoTo Whoa

Application.EnableEvents = False

' Set starting selection variable.
Set FirstSelection = Selection
Set SheetFirst = FirstSelection.Worksheet

' Reset the Find function so the scope of the search area is the current worksheet.
Set Dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

' Change "=" to "#" in selection.
Selection.Replace What:="=", Replacement:="#", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Select the area you want to paste the formulas; must be same size as original
  selection and outside of the original selection.
Set SecondSelection = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
Set SheetSecond = SecondSelection.Worksheet

' Copy the original selection and paste it into the newly selected area. The active
  selection remains FirstSelection.
FirstSelection.Copy SecondSelection

' Restore "=" in FirstSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Select SecondSelection.
SheetSecond.Activate
SecondSelection.Select

' Restore "=" in SecondSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Return active selection to the original area: FirstSelection.
SheetFirst.Activate
FirstSelection.Select

Application.EnableEvents = True

Exit Sub

Whoa:
' If something goes wrong after "=" has been changed in FirstSelection, restore "=".
FirstSelection.Select
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Note que vous devez faire correspondre la taille et la forme de la sélection d'origine lorsque vous faites votre sélection.

InformationsquelleAutor Bill Martz

J'ai trouvé cette solution qui automatise @Alistair Collins solution.

Fondamentalement, vous allez changer le = dans une formule à*, puis faire la pâte après cela, vous pourrez le changer en arrière

        Dim cell As Range

msgResult = MsgBox("Yes to lock" & vbNewLine & "No unlock ", vbYesNoCancel + vbQuestion, "Forumula locker")

If msgResult = vbNo Then
    For Each cell In Range("A1:i155")
        If InStr(1, cell.Value, "*") > 0 Then
            cell.Formula = Replace(cell.Formula, "*", "=")
        End If
    Next cell
ElseIf msgResult = vbYes Then
    For Each cell In Range("A1:i155")
        If cell.HasFormula = True Then
            cell.Formula = Replace(cell.Formula, "=", "*")
        End If
    Next cell
End If

InformationsquelleAutor eyadt4

-1

Essayez ceci:
Gauche-cliquez sur l'onglet, la création d'une copie de l'ensemble de la feuille, puis coupez-les cellules que vous voulez garder le même et le coller dans votre feuille de calcul d'origine.

InformationsquelleAutor Lisa
-1

J'ai trouvé une autre solution qui est très simple:
1. Couper le contenu
2. Collez-les dans le nouvel emplacement
3. Copiez le contenu que vous venez de coller dans le nouvel emplacement vous le souhaitez.
4. Annuler le Couper-Coller, mettre le contenu d'origine de retour là où vous les avez.
5. Coller le contenu du presse-papiers à l'emplacement même. Ces matières les références d'origine.

Il ressemble beaucoup, mais il est super rapide avec les raccourcis clavier:
1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

InformationsquelleAutor Isaac
-1

N'ai pas vérifier dans Excel, mais ceci fonctionne dans Libreoffice4:

Chose l'ensemble de la réécriture d'adresses intervient au cours consécutifs

(a1) couper

(a2) coller

Vous devez interrompre le consecutiveness en mettant quelque chose entre les deux:

(b1) couper

(b2) sélectionnez les cellules vides (plus de 1) et faites-la glisser(déplacer) eux

(b3) coller

L'étape (b2) où la cellule qui est sur le point de mettre à jour lui-même s'arrête le suivi. Rapide et simple.
- Je pense que cette réponse n'est pas appropriée, car elle n'a même pas été testé.
- Fjodr, je n'ai pas excel. Pourriez-vous tester? J'attends qu'il fonctionne, et cela sonne comme la meilleure de toutes les solutions proposées. Le plus voté réponse se composent de 9 étapes! Le mien est beaucoup plus facile. Merci d'essayer!
InformationsquelleAutor user1501439
-1

Cliquez sur la cellule que vous souhaitez copier. Dans la barre de formule, mettez en surbrillance la formule.

Appuyez sur Ctrl C.

Appuyez sur la touche echap (pour vous sortir de activement l'édition de cette formule).

Choisir de nouvelles cellules. Ctrl V.

InformationsquelleAutor Melinda Evans
-1

Solution Simple que j'ai utilisé seulement maintenant alors que dans une situation similaire:
1. De faire un double de la feuille de calcul
2. Coupe + Coller à partir de la double feuille de calcul
3. Supprimer dupliquer la feuille de calcul pour nettoyer
Vos références de cellules sont maintenant copiés w/o en cours de modification.

InformationsquelleAutor dire

Vous devez vous connecter pour publier un commentaire.