Tu écris une formule, elle fonctionne parfaitement sur la première ligne. Tu la recopies vers le bas, et là, une partie de la colonne se remplit correctement pendant que l’autre affiche des #N/A. Rien n’a changé dans tes données, et pourtant la formule ne trouve plus rien.
Le coupable n’est presque jamais la fonction. C’est l’absence de référence absolue, autrement dit les fameux dollars ($) que tout le monde a déjà vus dans une formule Excel sans forcément savoir ce qu’ils font.
Dans ce tuto, on part d’un cas réel, une RECHERCHEX qui remplit une colonne depuis une autre feuille, et on regarde exactement ce qui se casse quand on la recopie. Tu vas comprendre le mécanisme, le corriger avec une seule touche du clavier, et savoir quand figer une ligne, une colonne, ou les deux. Si tu débutes sur le tableur, garde sous la main le guide Excel débutant.
- Reconnaître une référence relative et une référence absolue
- Comprendre pourquoi une formule recopiée renvoie des #N/A
- Figer une plage en une touche avec F4
- Utiliser les 4 états de F4 : tout, la ligne, la colonne, rien
- Savoir quoi figer selon que tu recopies vers le bas ou vers la droite
Le principe en une phrase
Par défaut, Excel considère toutes les références comme relatives : si tu déplaces la formule d’une ligne vers le bas, toutes ses références descendent d’une ligne avec elle. Le dollar transforme une référence en référence absolue : elle reste figée, quoi que tu fasses de la formule.
L’image la plus juste, c’est la punaise. Sans dollar, ta plage flotte et suit la formule. Avec les dollars, tu la punaises sur la feuille et elle ne bouge plus.
Le point de départ : une RECHERCHEX qui marche à moitié
On a deux tableaux dans le même classeur. D’un côté une base article de 1225 lignes avec le code article, le libellé, le code barre et le fournisseur. De l’autre, un tableau de stock par entrepôt reçu d’un contact, dans lequel il faut compléter le libellé de chaque article. La clé commune est le code article.
=RECHERCHEX(A2;'INFORMATIONS PRODUITS'!A2:A1225;'INFORMATIONS PRODUITS'!D2:D1225)Sur la première cellule, la formule renvoie bien le bon libellé. On double clique en bas à droite de la cellule pour la recopier sur toute la colonne, et le résultat est bancal : une bonne partie des lignes est remplie, mais des #N/A apparaissent au milieu.
Premier réflexe raisonnable : se demander si ces codes articles existent bien dans la base. On en cherche un, et il est bien là. Ce n’est donc pas un problème de données, c’est un problème de formule.
Pourquoi la formule se décale quand tu la recopies
Pour comprendre, il suffit de cliquer sur une cellule en erreur et de lire sa formule. Sur la ligne 199, elle ne cherche plus dans la plage A2:A1225 mais dans A199:A1422.
=RECHERCHEX(A199;'INFORMATIONS PRODUITS'!A199:A1422;'INFORMATIONS PRODUITS'!D199:D1422)
C’est logique du point de vue d’Excel : la formule est descendue de 197 lignes, donc toutes ses références sont descendues de 197 lignes. Le problème, c’est que la base article, elle, commence toujours en ligne 2. Tout ce qui se trouve au-dessus de la ligne 199 n’est plus regardé, et les articles qui s’y trouvaient deviennent introuvables.
Plus tu descends, pire c’est. Arrivé en bas de la colonne, la plage de recherche démarre à la ligne 932 et se termine bien après la fin réelle du tableau. La formule cherche dans une zone qui ne contient presque plus de données.
La correction : la touche F4
Il faut donc dire à Excel que la plage de recherche ne doit pas bouger. On revient dans la formule de la première cellule, on sélectionne la plage de recherche dans la barre de formule, et on appuie sur la touche F4 du clavier. Les dollars apparaissent tout seuls.
On fait la même chose sur le tableau renvoyé, deuxième plage de la fonction, et on valide avec Entrée. La formule finale ressemble à ça.
=RECHERCHEX(A2;'INFORMATIONS PRODUITS'!$A$2:$A$1225;'INFORMATIONS PRODUITS'!$D$2:$D$1225)Le résultat : plus aucune erreur
Il ne reste qu’à recopier la formule corrigée sur toute la colonne, toujours avec un double clic en bas à droite de la cellule. Cette fois, tous les libellés remontent et les #N/A ont disparu.
Si tu cliques maintenant sur n’importe quelle cellule de la colonne, la partie $A$2:$A$1225 est identique partout. Seule la valeur cherchée, A2 puis A3 puis A4, continue de suivre la ligne, et c’est exactement ce qu’on veut.
Les 4 états de la touche F4
F4 ne se contente pas d’ajouter ou d’enlever les dollars. Chaque appui fait défiler un état différent, dans cet ordre.
- A2 : rien n’est figé, c’est l’état de départ.
- $A$2 : la colonne et la ligne sont figées.
- A$2 : seule la ligne est figée.
- $A2 : seule la colonne est figée.
Un cinquième appui te ramène au point de départ, sans dollar. Le repère est simple : le dollar fige ce qui le suit. Devant la lettre, il bloque la colonne. Devant le chiffre, il bloque la ligne.
Tu peux évidemment taper les dollars toi-même en te positionnant devant la lettre ou devant le chiffre. F4 est juste beaucoup plus rapide, comme la plupart des raccourcis Excel indispensables.
Faut-il figer la ligne, la colonne, ou les deux ?
Ça dépend uniquement du sens dans lequel tu recopies ta formule.
- Tu recopies vers le bas : ce sont les lignes qui se décalent, donc ce sont les lignes qu’il faut figer.
- Tu recopies vers la droite : ce sont les colonnes qui se décalent, donc ce sont les colonnes qu’il faut figer.
- Tu recopies dans les deux sens, typiquement une grille de calcul : fige tout.
Dans notre exemple, la formule ne part que vers le bas. Figer uniquement les lignes suffit donc, et le résultat est strictement le même.
La règle simple à retenir
Dans 90 à 95% des cas, tu n’as pas besoin de réfléchir au figeage partiel. Le réflexe qui te sortira de la quasi totalité des situations tient en trois gestes : clique dans la barre de formule sur la plage concernée, appuie une fois sur F4, valide.
Tout est figé, la formule se recopie sans jamais se décaler, et tu passes à autre chose. Les cas où tu voudras figer uniquement la ligne ou uniquement la colonne arriveront plus tard, sur des formules recopiées dans les deux sens. À ce moment là, tu seras déjà à l’aise avec les dollars.
Pour aller plus loin
Les dollars deviennent un réflexe dès que tes formules travaillent sur des plages. Si tes recherches renvoient encore des erreurs une fois la plage figée, c’est cette fois que le code cherché est réellement absent de la base, et tu peux les masquer proprement avec la fonction SIERREUR. Pour aller plus loin sur la recherche elle-même, regarde INDEX et EQUIV, et pour les fonctions de calcul conditionnel qui réclament elles aussi des plages figées, SOMME.SI, MOYENNE.SI et NB.SI.
Attention enfin à ne pas confondre deux choses qui portent le même nom. Figer une plage dans une formule avec les dollars n’a rien à voir avec figer une ligne à l’écran pour garder les en têtes visibles quand tu fais défiler. Ce second sens, ce sont les volets, et tout est expliqué dans figer une ligne ou une colonne dans Excel.
Questions fréquentes
C’est quoi une référence absolue dans Excel ?
Une référence absolue est une référence de cellule que tu bloques avec le signe dollar, par exemple $A$2. Quand tu recopies la formule dans d’autres cellules, cette référence ne bouge pas. Une référence relative (A2, sans dollar) se décale au contraire à chaque recopie.
À quoi servent les dollars dans une formule Excel ?
Les dollars servent à figer une référence pour qu’elle ne se décale pas quand tu recopies la formule. C’est indispensable quand une partie de ta formule doit toujours pointer au même endroit, comme la plage de recherche d’une RECHERCHEV ou d’une RECHERCHEX.
Comment mettre les dollars automatiquement dans Excel ?
Clique dans la barre de formule sur la référence à figer, puis appuie sur la touche F4 de ton clavier. Excel ajoute les dollars pour toi. Sur certains portables, il faut faire Fn + F4.
Pourquoi ma RECHERCHEV renvoie #N/A quand je la recopie ?
Dans la majorité des cas, la plage de recherche n’est pas figée. En recopiant vers le bas, elle glisse d’autant de lignes et finit par ne plus couvrir le début de ta base, donc Excel ne trouve plus les valeurs. Fige la plage avec F4 et les erreurs disparaissent.
Quelle différence entre $A$2, A$2 et $A2 ?
$A$2 fige la colonne et la ligne. A$2 fige uniquement la ligne, la colonne peut se décaler vers la droite. $A2 fige uniquement la colonne, la ligne peut se décaler vers le bas. La touche F4 fait défiler ces trois états, puis revient à A2 sans aucun dollar.
Faut-il toujours tout figer avec $A$2 ?
Dans la grande majorité des cas, oui. Sélectionne la plage dans la barre de formule, appuie une fois sur F4 et tu es tranquille. Le figeage partiel (ligne seule ou colonne seule) sert dans des cas plus avancés, typiquement une formule recopiée à la fois vers le bas et vers la droite.
