Merwyn d'Angleterre a envoyé ce problème.
Existe-t-il une formule unique dans la cellule B2 qui peut être copiée de part en part pour créer une table de multiplication?
L'objectif de Merwyn est de saisir une formule unique en B2 qui peut facilement être copiée dans les 144 cellules pour créer une table de référence de multiplication.
Attaquons cela en tant que novice d'Excel et voyons les pièges auxquels nous nous heurtons. Une première réaction pourrait être d'avoir la formule en B2 =A2*B1. Cette formule produit le résultat correct, mais elle ne convient pas à l'affectation de Merwyn.
Lorsque vous copiez cette formule de la cellule B2 vers la cellule C2, les cellules référencées dans la formule se déplacent également sur une cellule. La formule qui était =A2*B1maintenant devient =C1*B2. Il s'agit d'une fonctionnalité conçue dans Microsoft Excel et appelée référence de formule relative. Lorsque vous copiez une formule, les références de cellule dans la formule déplacent également un nombre correspondant de cellules de travers et vers le bas.
Il y a des moments où vous ne souhaitez pas qu'Excel présente ce comportement et le cas actuel en fait partie. Pour empêcher Excel de modifier la référence lors de la copie des cellules, insérez un «$» avant la partie de la référence que vous souhaitez figer. Exemples:
- $ A1 indique à Excel que vous voulez toujours vous référer à la colonne A.
- B $ 1 indique à Excel que vous voulez toujours vous référer à la ligne 1.
- $ B $ 1 indique à Excel que vous voulez toujours faire référence à la cellule B1.
L'apprentissage de ce code réduira considérablement le temps nécessaire pour créer des feuilles de calcul. Plutôt que d'avoir à entrer 144 formules, Merwyn peut utiliser ce raccourci pour entrer une seule formule et la copier dans toutes les cellules.
En regardant la formule de Merwyn =B1*A2, nous nous rendons compte que la partie "B1" de l'expression doit toujours pointer vers un nombre dans la ligne 1. Cela devrait être réécrit comme "B $ 1". La section "A2" de la formule doit toujours pointer vers un nombre dans la colonne A. Cela doit être réécrit comme "$ A2". Ainsi, la nouvelle formule dans la cellule B2 est =B$1*$A2.
Après avoir entré la formule dans B2, je peux la copier et la coller dans la plage appropriée. Excel suit mes instructions et après avoir fait la copie, je trouve les formules suivantes:
- La cellule M2 contient
=M$1*$A2 - La cellule B13 contient
=B$1*$A13 - La cellule M13 contient
=M$1*$A13
Chacun de ces types de formules a un nom. Les formules sans aucun signe dollar sont appelées formules relatives. Les formules dans lesquelles la ligne et la colonne sont verrouillées avec un signe dollar sont appelées formules absolues. Les formules dans lesquelles la ligne ou la colonne sont verrouillées avec un signe dollar sont appelées formules mixtes.
Il existe une méthode abrégée pour saisir les signes dollar. Lorsque vous tapez une formule et terminez une référence de cellule, vous pouvez appuyer sur la touche pour basculer entre les 4 types de référence. Disons que vous avez commencé à taper une formule et que vous avez tapé =100*G87.
- Appuyez sur F4 et votre formule devient
=100*$G$87 - Appuyez à nouveau sur F4 et votre formule devient
=100*G$87 - Appuyez à nouveau sur F4 et votre formule devient
=100*$G87 - Appuyez à nouveau sur F4 et votre formule revient à l'original
=100*G87
Vous pouvez faire une pause pendant la saisie de formule à chaque référence de cellule pour appuyer sur F4 jusqu'à ce que vous obteniez le bon type de référence. Les frappes pour entrer la formule de Merwyn ci-dessus sont =B1*A1.
L'une de mes utilisations préférées des références de formules mixtes apparaît lorsque j'ai une longue liste d'entrées dans la colonne A. Lorsque je veux une méthode rapide et sale pour trouver des doublons, j'entre parfois cette formule dans la cellule B4, puis je la copie:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Notez que le 2ème terme de la formule RECHERCHEV utilise à la fois une référence absolue ($ A $ 2) et une référence mixte ($ A3) pour décrire la plage de recherche. En anglais, je dis à Excel de toujours rechercher de la cellule $ A $ 2 à la cellule de la colonne A juste au-dessus de la cellule actuelle. Dès qu'Excel rencontre une valeur en double, le résultat de cette formule change de # N / A! à une valeur.
Avec une utilisation créative des références de cellule $ in, vous pouvez vous assurer qu'une seule formule peut être copiée dans plusieurs cellules avec des résultats corrects.
