Corriger les erreurs courantes de copie

La plupart des erreurs de copie ont une même cause fondamentale : une référence s'est décalée alors qu'elle devait rester fixe, ou est restée fixe alors qu'elle devait se décaler.

Comme les références relatives se déplacent lors de la recopie, une formule qui pointe vers une seule cellule partagée, comme un taux de taxe ou un en-tête, s'éloignera de cette cellule à chaque nouvelle ligne. Le résultat est incorrect ou affiche des erreurs.

Cette leçon présente les problèmes courants et explique comment corriger chacun d'eux.

Supposons que E1 contienne un taux de taxe de 0.08 et que vous écriviez =C2*E1 dans D2, puis que vous recopiiez la formule vers le bas. Voici ce qui se passe :

• D2 = =C2*E1 (correct)
• D3 = =C3*E2 (E2 est vide !)
• D4 = =C4*E3 (toujours vide)

La référence E s'est éloignée du taux. Les lignes situées sous la ligne 2 multiplient par zéro et affichent donc 0 partout.

Figez la cellule partagée pour l'empêcher de se décaler. Modifiez la formule en =C2*$E$1 avant de la recopier.

Les signes dollar verrouillent à la fois la colonne E et la ligne 1. Désormais, lorsque vous recopiez la formule vers le bas :

• D3 = =C3*$E$1
• D4 = =C4*$E$1

Chaque ligne multiplie par le bon taux. C continue de se décaler ; $E$1 reste en place.

Si vous copiez une formule vers un emplacement où ses références sortiraient des limites de la feuille, vous obtenez #REF!.

Par exemple, une formule comme =A2-B2 placée dans la colonne A et déplacée vers la gauche n'a aucun emplacement valide vers lequel pointer ; elle renvoie donc #REF!. Cette erreur signifie qu'« une référence n'existe plus ».

Pour corriger le problème, collez la formule à un emplacement où ses références restent dans la grille, ou réécrivez-la pour qu'elle pointe vers des cellules valides.

Une formule de division copiée peut rencontrer des cellules vides et effectuer une division par zéro, ce qui affiche #DIV/0!.

Supposons que =B2/C2 soit recopiée vers le bas d'une colonne dans laquelle certaines cellules C sont vides. La division par une cellule vide déclenche l'erreur.

Entourez la formule de IFERROR pour afficher plutôt une cellule vide ou un zéro : =IFERROR(B2/C2,0). La division s'effectue là où c'est possible et les erreurs sont masquées là où elle ne peut pas s'effectuer.

Une erreur classique consiste à faire glisser la poignée de recopie trop loin et à remplacer des cellules dont vous aviez besoin, ou à coller une formule par-dessus des valeurs saisies.

La solution immédiate est Ctrl+Z (Cmd+Z) pour annuler. Les tableurs conservent un historique d'annulation détaillé ; vous pouvez donc revenir en arrière après plusieurs recopies accidentelles.

Après l'annulation, recommencez la recopie plus soigneusement en arrêtant le glissement à la bonne dernière ligne.

Faire glisser la poignée de recopie copie la mise en forme en même temps que la formule. Cela peut étendre des bordures, des couleurs ou des formats monétaires à des endroits où vous ne le souhaitiez pas.

Pour copier uniquement la formule, utilisez le bouton Options de recopie incrémentée après le glissement et choisissez Recopier sans mise en forme. Lors d'un collage, utilisez Collage spécial et sélectionnez uniquement Formules afin de préserver l'apparence de la destination.

Lorsque quelque chose semble incorrect, votre premier réflexe doit toujours être le même : cliquez sur la cellule problématique et lisez la barre de formule.

Demandez-vous si chaque référence pointe vers l'emplacement prévu. Une constante qui devrait être $E$1 mais qui apparaît comme E5 révèle l'absence d'un verrouillage. Une référence affichant #REF! révèle que la cellule cible a été supprimée. La barre de formule transforme un nombre incorrect et mystérieux en correction évidente.

Pour vérifier toute une feuille en une seule fois, activez le mode Afficher les formules. Dans Excel, appuyez sur Ctrl+` (la touche de l'accent grave) ; dans Google Sheets, choisissez Affichage, puis Afficher les formules.

Chaque cellule affiche sa formule au lieu de son résultat. Vous pouvez ainsi parcourir une colonne et repérer immédiatement la seule ligne dont les références se sont décalées. Appuyez de nouveau sur le raccourci pour revenir à l'affichage normal.

La meilleure correction consiste à éviter entièrement le problème. Avant de faire glisser une formule, examinez chaque référence et décidez si elle doit se déplacer avec chaque ligne ou rester fixe.

Ajoutez des signes dollar à tout ce qui doit rester fixe, comme un taux unique, un total d'en-tête ou un tableau de recherche. Pour les ajouter rapidement, cliquez dans une référence et appuyez sur F4 ; cette touche fait défiler les options de verrouillage. Verrouillez d'abord, recopiez ensuite.

Supposons qu'une colonne de commissions affiche 0 sur chaque ligne sauf la première. Vous cliquez sur la ligne 5 et la barre de formule affiche =C5*E4, alors que le taux se trouve en E1.

Le diagnostic est évident : E1 n'a jamais été verrouillée et s'est donc décalée jusqu'à E4. Corrigez la formule d'origine en =C2*$E$1, puis recopiez la colonne. Chaque ligne affiche désormais la bonne valeur. Un seul signe dollar a réparé toute la colonne.

Vérifiez vos compétences pour résoudre les problèmes de copie.

Vous pouvez désormais réparer les formules qui se dérèglent lors de la copie :

• Une constante qui se décale se corrige en la verrouillant avec des signes dollar, comme $E$1.
• #REF! signifie qu'une référence est sortie de la grille ; #DIV/0! signifie une division par une cellule vide, que IFERROR permet de neutraliser.
• Ctrl+Z annule une mauvaise recopie ; Recopier sans mise en forme préserve les styles.
• Établissez le diagnostic avec la barre de formule ou le mode Afficher les formules, puis verrouillez les références avant de recopier.

Vous avez terminé la leçon « Copier correctement les formules ».