Apprenez à copier une formule dans plusieurs cellules, dans une colonne, dans des cellules non adjacentes, copier des formules avec des références de cellules absolues ou mixtes, etc.
La copie de formules est l'une des tâches les plus courantes et les plus faciles à effectuer dans une feuille de calcul typique qui repose principalement sur des formules. Plutôt que de taper la même formule encore et encore dans Excel, vous pouvez simplement copier et coller facilement une formule d'une cellule à plusieurs cellules.
Après avoir écrit une formule dans Excel, vous pouvez utiliser les commandes copier-coller dans plusieurs cellules, plusieurs cellules non adjacentes ou des colonnes entières. Si vous ne le faites pas correctement, vous vous retrouverez avec ces terribles erreurs # REF et / DIV0. Dans cet article, nous allons vous montrer les différentes méthodes que vous pouvez utiliser pour copier des formules dans Excel.
Comment copier et coller des formules dans Excel
Microsoft Excel propose différentes manières de copier des formules avec des références de cellules relatives, des références de cellules absolues ou des références mixtes.
- Copier la formule d'une cellule à l'autre
- Copier la formule une cellule dans plusieurs cellules
- Copier la formule dans toute la colonne
- Copie de formule sans formatage
- Copier des formules dans des cellules non adjacentes
- Copier des formules sans modifier les références de cellules
Comment copier une formule d'une cellule à une autre dans Excel
Parfois, vous souhaiterez peut-être copier une formule d'une cellule à une autre dans Excel pour éviter de retaper la formule entière à nouveau et gagner du temps en faisant cela.
Disons que nous avons ce tableau :
Il existe peu de méthodes pour copier une formule d'une cellule à une autre.
Tout d'abord, sélectionnez la cellule avec la formule et cliquez avec le bouton droit, et dans le menu contextuel, sélectionnez « Copier » pour copier la formule. Ou vous pouvez utiliser l'option « Copier » dans la section « Presse-papiers » de l'onglet « Accueil ».
Mais vous copiez également des formules en appuyant simplement sur le raccourci clavier Ctrl + C. C'est une méthode plus efficace et plus rapide.
Ensuite, nous allons dans la cellule que nous voulons coller, appuyez sur le raccourci Ctrl + V pour coller la formule. Ou cliquez avec le bouton droit sur la cellule que vous souhaitez coller et cliquez sur les options sous « Options de collage » : soit l'option « Coller (P) » simple, soit l'option « Formule (F) ».
Alternativement, vous pouvez également cliquer sur « Collage spécial » sous les six icônes de collage pour ouvrir la boîte de dialogue « Collage spécial ». Ici, vous avez plusieurs options, y compris les six options de collage du menu contextuel. Sélectionnez « Tous » ou « Formules » dans la section Coller et cliquez sur « OK ».
Maintenant, la cellule avec la formule collée doit avoir les mêmes formules (que celle de la cellule copiée) mais avec des références de cellule différentes. L'adresse de la cellule est auto-ajustée par Excel pour correspondre au numéro de ligne de la cellule collée.
Copier la formule d'une cellule vers plusieurs cellules
La même opération de collage fonctionne de la même manière si nous sélectionnons plusieurs cellules ou une plage de cellules.
Sélectionnez la cellule avec la formule et appuyez sur Ctrl + C pour copier la formule. Ensuite, sélectionnez toutes les cellules où vous souhaitez coller la formule et appuyez sur Ctrl + V pour coller la formule ou utilisez l'une des méthodes de collage ci-dessus pour coller la formule (comme nous l'avons fait pour la cellule unique).
Copier la formule dans une colonne ou une ligne entière
Dans Excel, vous pouvez copier rapidement une formule dans une colonne entière ou une ligne.
Pour copier une formule dans une colonne ou une ligne, commencez par saisir une formule dans une cellule. Ensuite, sélectionnez la cellule de formule (D1) et placez votre curseur sur un petit carré vert dans le coin inférieur droit de la cellule. Au fur et à mesure que vous survolez, le curseur se transforme en un signe plus noir (+), appelé poignée de remplissage. Cliquez et maintenez cette poignée de remplissage et faites-la glisser dans la direction de votre choix (colonne ou ligne) sur les cellules pour copier la formule.
Lorsque vous copiez une formule dans une plage de cellules, les références de cellule de la formule s'ajusteront automatiquement en fonction de l'emplacement relatif des lignes et des colonnes et la formule effectuera des calculs en fonction des valeurs de ces références de cellule (voir ci-dessous).
Dans l'exemple ci-dessus, lorsque la formule dans D1 (=A1*B1)/2) est copiée dans la cellule D2, la référence relative change en fonction de son emplacement (=A2*B2)/2) et ainsi de suite.
De la même manière, vous pouvez faire glisser la formule dans les cellules adjacentes vers la gauche, la droite ou le haut.
Une autre façon de copier la formule dans une colonne entière consiste à double-cliquer sur la poignée de remplissage au lieu de la faire glisser. Lorsque vous double-cliquez sur la poignée de recopie, elle applique immédiatement la formule dans la mesure où il y a des données dans la cellule adjacente.
Copier une formule dans une plage sans copier le formatage
Lorsque vous copiez une formule dans une plage de cellules avec la poignée de remplissage, elle copie également la mise en forme de la cellule source, telle que la couleur de police ou la couleur d'arrière-plan, la devise, le pourcentage, l'heure, etc. (comme indiqué ci-dessous).
Pour éviter de copier la mise en forme de la cellule, faites glisser la poignée de remplissage et cliquez sur « Options de remplissage automatique » dans le coin inférieur droit de la dernière cellule. Ensuite, dans le menu déroulant, sélectionnez « Remplir sans formatage ».
Le résultat:
Copier une formule Excel avec uniquement la mise en forme des nombres
Si vous souhaitez copier la formule avec uniquement la formule et la mise en forme telle que le format de pourcentage, les points décimaux, etc.
Copiez la formule et sélectionnez toutes les cellules dans lesquelles vous souhaitez copier la formule. Dans l'onglet « Accueil », cliquez sur la flèche située sous le bouton « Coller » sur le ruban. Ensuite, cliquez sur l'icône « Formules et formatage des nombres » (l'icône avec % fx) dans la liste déroulante pour coller uniquement la formule et le formatage des nombres.
Cette option ne copie que la mise en forme des formules et des nombres, mais ignore toutes les autres mises en forme des cellules telles que la couleur d'arrière-plan, la couleur de la police, etc.
Copier une formule dans des cellules non adjacentes/non contiguës
Si vous souhaitez copier une formule dans des cellules ou des plages non adjacentes, vous pouvez le faire à l'aide de Ctrl clé.
Sélectionnez la cellule avec la formule et appuyez sur Ctrl + C pour le copier. Ensuite, sélectionnez des cellules/plages non adjacentes tout en maintenant enfoncée la touche Ctrl clé. Puis appuyez Ctrl + V pour coller la formule et appuyez sur Entrer compléter.
Copier des formules sans modifier les références de cellules dans Excel
Lorsqu'une formule est copiée dans une autre cellule, Excel modifie automatiquement les références de cellule pour qu'elles correspondent à son nouvel emplacement. Ces références de cellule utilisent l'emplacement relatif d'une adresse de cellule, elles sont donc appelées référence de cellule relative (sans $). Par exemple, si vous avez la formule "=A1*B1" dans la cellule C1 et que vous copiez cette formule dans la cellule C2, la formule deviendra "=A2*B2". Toutes les méthodes dont nous avons parlé ci-dessus utilisent des références relatives.
Lorsque vous copiez une formule avec des références de cellules relatives, elle modifie automatiquement les références afin que la formule fasse référence aux lignes et colonnes correspondantes. Si vous utilisez des références absolues dans une formule, la même formule est copiée sans modifier les références de cellule.
Lorsque vous placez un signe dollar ($) devant la lettre de la colonne et le numéro de ligne d'une cellule (par exemple, $A$1), cela transforme la cellule en une cellule absolue. Maintenant, peu importe où vous copiez la formule qui contient la référence de cellule absolue, la formule ne le sera jamais. Mais si vous avez une référence de cellule relative ou mixte dans une formule, utilisez l'une des méthodes suivantes pour copier sans modifier les références de cellule.
Copier une formule avec une référence de cellule absolue à l'aide de la méthode copier-coller
Parfois, vous devrez peut-être copier/appliquer la formule exacte dans la colonne, sans modifier les références de cellule. Si vous souhaitez copier ou déplacer une formule exacte avec référence absolue, procédez comme suit :
Tout d'abord, sélectionnez la cellule avec la formule que vous souhaitez copier. Ensuite, cliquez sur la barre de formule, sélectionnez la formule à l'aide de la souris et appuyez sur Ctrl + C pour le copier. Si vous souhaitez déplacer la formule, appuyez sur Ctrl + X pour le couper. Ensuite, appuyez sur le Esc pour quitter la barre de formule.
Alternativement, sélectionnez la cellule avec la formule et appuyez sur F2 ou double-cliquez sur la cellule. Cela mettra la cellule sélectionnée en mode édition. Ensuite, sélectionnez la formule dans la cellule et appuyez sur Ctrl + C pour copier la formule dans la cellule sous forme de texte.
Ensuite, sélectionnez la cellule de destination et appuyez sur Ctrl + V pour coller la formule.
Maintenant, la formule exacte est copiée dans la cellule de destination sans aucun changement de référence de cellule.
Copier des formules avec des références de cellules absolues ou mixtes
Si vous souhaitez déplacer ou copier des formules exactes sans modifier les références de cellules, vous devez remplacer les références relatives aux cellules par des références absolues. Par exemple, l'ajout du signe ($) à la référence de cellule relative (B1) en fait une référence absolue ($B$1), de sorte qu'elle reste statique quel que soit l'endroit où la formule est copiée ou déplacée.
Mais parfois, vous devrez peut-être utiliser des références de cellules mixtes ($B1 ou B$1) en ajoutant un signe dollar ($) devant la lettre de la colonne ou le numéro de ligne pour verrouiller une ligne ou une colonne en place.
Expliquons-nous avec un exemple. Supposons que vous ayez ce tableau qui calcule les économies mensuelles en soustrayant le loyer (B9) des revenus (dans la colonne B) chaque mois.
Dans l'exemple ci-dessous, la formule utilise une référence de cellule absolue (9 USD $) pour verrouiller le montant du loyer à la cellule B9 et une référence de cellule relative à la cellule B2 car elle doit être ajustée pour que chaque ligne corresponde à chaque mois. B9 est une référence de cellule absolue (9 USD $) car vous souhaitez soustraire le même montant de loyer des revenus de chaque mois.
Disons que vous voulez déplacer les soldes de la colonne C à la colonne E. Si vous copiez la formule (par la méthode copier/coller habituelle) de la cellule C2 (=B2-$B$9) deviendra =D2-$B$9 une fois collée dans la cellule E2, vous faites tout faux dans vos calculs !
Dans ce cas, remplacez la référence de cellule relative (B2) par une référence de cellule mixte ($B2) en ajoutant le signe « $ » devant la lettre de la colonne de la formule entrée dans la cellule C2.
Et maintenant, si vous copiez ou déplacez la formule de la cellule C2 vers E2, ou toute autre cellule, et appliquez la formule en bas de la colonne, la référence de la colonne restera la même tandis que le numéro de ligne sera ajusté pour chaque cellule.
Copier Coller des formules Excel sans modifier les références à l'aide du Bloc-notes
Vous pouvez voir toutes les formules de votre feuille de calcul Excel en utilisant les options Afficher la formule. Pour ce faire, allez dans l'onglet « Formules » et sélectionnez « Afficher les formules ».
Alternativement, vous pouvez entrer dans le mode d'affichage de formule en appuyant sur le bouton Ctrl + ` raccourci, qui affiche toutes les formules de votre feuille de calcul. Vous pouvez trouver la touche accent grave (`) dans le coin supérieur gauche de votre clavier sur la rangée avec les touches numériques (sous le ESC et avant la touche numéro 1).
Sélectionnez toutes les cellules avec les formules que vous souhaitez copier et appuyez sur Ctrl + C pour les copier, ou Ctrl + X pour les couper. Ensuite, ouvrez le Bloc-notes et appuyez sur Ctrl + V pour coller les formules dans le bloc-notes.
Ensuite, sélectionnez la formule et copiez(Ctrl + C) depuis le bloc-notes, et collez-le (Ctrl + V) dans la cellule où vous voulez copier la formule exacte. Vous pouvez les copier et les coller un par un ou tous à la fois.
Après avoir collé les formules, désactivez le mode d'affichage des formules en appuyant sur Ctrl + ` ou encore allez dans 'Formules' -> 'Afficher les formules'.
Copiez les formules exactes à l'aide de la fonction Rechercher et remplacer d'Excel
Si vous souhaitez copier une plage de formules exactes, vous pouvez également utiliser l'outil Rechercher et remplacer d'Excel pour le faire.
Sélectionnez toutes les cellules contenant les formules que vous souhaitez copier. Ensuite, allez dans l'onglet « Accueil », cliquez sur « Rechercher et sélectionner » dans le groupe Édition, et sélectionnez l'option « Remplacer », ou appuyez simplement sur Ctrl + H pour ouvrir la boîte de dialogue Rechercher et remplacer.
Dans la boîte de dialogue Rechercher et remplacer, entrez le signe égal (=) dans le champ « Rechercher ». Dans le champ « Remplacer par », entrez un symbole ou un caractère qui ne fait pas déjà partie de vos formules, comme #, ou , etc. Ensuite, cliquez sur le bouton « Remplacer tout ».
Vous obtiendrez un message d'invite indiquant « Nous avons effectué 6 remplacements » (parce que nous avons sélectionné 6 cellules avec des formules). Cliquez ensuite sur « OK » et « Fermer » pour fermer les deux boîtes de dialogue. Cela remplace tous les signes égal à (=) par des signes dièse (#) et transforme les formules en chaînes de texte. Désormais, les références de cellule des formules ne seront pas modifiées lors de la copie.
Maintenant, vous pouvez sélectionner ces cellules, appuyez sur Ctrl + C pour les copier et les coller dans les cellules de destination avec Ctrl + V.
Enfin, vous devez remplacer les signes (#) par des signes (=). Pour ce faire, sélectionnez les deux plages (plage d'origine et copiée) et appuyez sur Ctrl + H pour ouvrir la boîte de dialogue Rechercher et remplacer. Cette fois, tapez le signe dièse (#) dans le champ « Rechercher » et égal au signe (=) dans le champ « Remplacer par », puis cliquez sur le bouton « Remplacer tout ». Cliquez sur « Fermer » pour fermer la boîte de dialogue.
Maintenant, les chaînes de texte sont reconverties en formules et vous obtiendrez ce résultat :
Fait!