Table des matières
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.
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 :
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
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:
- Sélectionnez la plage avec des formules (dans notre exemple D2:D8)
- Cliquez Ctrl + H sur le clavier ou sur un onglet Accueil – Rechercher et sélectionner – Remplacer (Accueil — Rechercher&Sélectionner — Remplacer)
- 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).
- Copiez la plage résultante avec les formules désactivées au bon endroit :
- 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 :
Copiez notre plage D2:D8 et collez-la dans la norme Cahier :
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 :
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 :
- 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