Copier des formules sans changement de lien

Problème

Supposons que nous ayons un tableau simple comme celui-ci, dans lequel les montants sont calculés pour chaque mois dans deux villes, puis le total est converti en euros au taux de la cellule jaune J2.

Copier des formules sans changement de lien

Le problème est que si vous copiez la plage D2:D8 avec des formules ailleurs sur la feuille, Microsoft Excel corrigera automatiquement les liens dans ces formules, les déplacera vers un nouvel emplacement et arrêtera de compter :

Copier des formules sans changement de lien

Tâche : copier la plage avec des formules afin que les formules ne changent pas et restent les mêmes, en conservant les résultats du calcul.

Méthode 1. Liens absolus

Comme vous pouvez le voir sur l'image précédente, Excel ne déplace que les liens relatifs. La référence absolue (avec des signes $) à la cellule jaune $J$2 n'a pas bougé. Par conséquent, pour une copie exacte des formules, vous pouvez temporairement convertir toutes les références de toutes les formules en références absolues. Vous devrez sélectionner chaque formule dans la barre de formule et appuyer sur la touche F4:
Copier des formules sans changement de lien
Avec un grand nombre de cellules, cette option disparaît bien sûr - c'est trop laborieux.

Méthode 2 : désactiver temporairement les formules

Pour empêcher les formules de changer lors de la copie, vous devez (temporairement) vous assurer qu'Excel cesse de les traiter comme des formules. Cela peut être fait en remplaçant le signe égal (=) par tout autre caractère qui ne se trouve normalement pas dans les formules, comme un signe dièse (#) ou une paire d'esperluettes (&&) pour le temps de copie. Pour ça:

  1. Sélectionnez la plage avec des formules (dans notre exemple D2:D8)
  2. Cliquez Ctrl + H sur le clavier ou sur un onglet Accueil – Rechercher et sélectionner – Remplacer (Accueil — Rechercher&Sélectionner — Remplacer)

    Copier des formules sans changement de lien

  3. Dans la boîte de dialogue qui apparaît, entrez ce que nous recherchons et ce que nous remplaçons, et dans Paramètres (Options) n'oubliez pas de préciser Portée de la recherche – Formules. Nous pressons Remplace tout (Remplace tout).
  4. Copiez la plage résultante avec les formules désactivées au bon endroit :

    Copier des formules sans changement de lien

  5. remplacer # on = retour en utilisant la même fenêtre, rendant la fonctionnalité aux formules.

Méthode 3 : copier via le bloc-notes

Cette méthode est beaucoup plus rapide et facile.

Appuyez sur le raccourci clavier Ctrl+Á ou bouton Afficher les formules languette formule (Formules — Afficher les formules), pour activer le mode de vérification des formules - au lieu des résultats, les cellules afficheront les formules par lesquelles elles sont calculées :

Copier des formules sans changement de lien

Copiez notre plage D2:D8 et collez-la dans la norme Cahier :

Copier des formules sans changement de lien

Sélectionnez maintenant tout ce qui est collé (Ctrl + A), copiez-le à nouveau dans le presse-papiers (Ctrl + C) et collez-le sur la feuille à l'endroit dont vous avez besoin :

Copier des formules sans changement de lien

Il ne reste plus qu'à appuyer sur le bouton Afficher les formules (Afficher les formules)pour remettre Excel en mode normal.

Remarque : cette méthode échoue parfois sur des tableaux complexes avec des cellules fusionnées, mais dans la grande majorité des cas, elle fonctionne correctement.

Méthode 4. Macro

Si vous devez souvent faire de telles copies de formules sans déplacer les références, il est logique d'utiliser une macro pour cela. Appuyez sur le raccourci clavier Alt + F11 ou bouton Visual Basic languette promoteur (Développeur), insérez un nouveau module via le menu Insertion – Module  et copiez-y le texte de cette macro :

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Sélectionner les cellules avec les formules à copier.", _ "Copier les formules exactement", Default:=Selection.Address, Type := 8) Si copyRange n'est rien, quittez le sous-ensemble pasteRange = Application.InputBox("Maintenant, sélectionnez la plage de collage." & vbCrLf & vbCrLf & _ "La taille de la plage doit être égale à la plage de cellules d'origine " & vbCrLf & _ " à copier." , "Copier les formules exactement", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Les plages de copie et de collage varient en taille !", vbExclamation, "Erreur de copie" Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Vous pouvez utiliser le bouton pour exécuter la macro. Macros languette promoteur (Développeur — Macros) ou raccourci clavier Alt + F8. Après avoir exécuté la macro, elle vous demandera de sélectionner la plage avec les formules d'origine et la plage d'insertion et copiera automatiquement les formules :

Copier des formules sans changement de lien

  • Affichage pratique des formules et des résultats en même temps
  • Pourquoi le style de référence R1C1 est nécessaire dans les formules Excel
  • Comment trouver rapidement toutes les cellules avec des formules
  • Outil pour copier les formules exactes du module complémentaire PLEX

 

Soyez sympa! Laissez un commentaire