Comment copier des formules avec le remplissage automatique dans Excel 2016

Si vous avez juste besoin de copier une seule formule dans Excel 2016, utilisez la fonction Remplissage automatique ou les commandes Copier et Coller. Ce type de copie de formule, bien qu'assez courant, ne peut pas être effectué par glisser-déposer.

N'oubliez pas l'option Totaux de l'outil Analyse rapide. Vous pouvez l'utiliser pour créer une ligne ou une colonne de totaux en bas ou à droite d'un tableau de données en un clin d'œil. Sélectionnez simplement le tableau en tant que plage de cellules et cliquez sur le bouton Analyse rapide suivi de Totaux dans sa palette. Cliquez ensuite sur le bouton Somme au début de la palette pour créer des formules qui totalisent les colonnes d'une nouvelle ligne en bas du tableau et/ou sur le bouton Somme à la fin de la palette pour créer des formules qui totalisent les lignes dans un nouveau colonne à l'extrémité droite.

Voici comment utiliser le remplissage automatique pour copier une formule dans une plage de cellules. Dans cette figure, vous pouvez voir la feuille de calcul Mother Goose Enterprises - Ventes 2016 avec toutes les entreprises, mais cette fois avec un seul total mensuel à la ligne 12, qui est en train d'être copié dans la cellule E12.

Copie d'une formule dans une plage de cellules avec AutoFill.

La figure suivante montre la feuille de calcul après avoir fait glisser la poignée de remplissage dans la cellule B12 pour sélectionner la plage de cellules C12:E12 (où cette formule doit être copiée).

La feuille de calcul après avoir copié la formule totalisant les ventes mensuelles (et trimestrielles).

Relativement parlant

La figure montre la feuille de calcul une fois que la formule d'une cellule est copiée dans la plage de cellules C12:E12 et que la cellule B12 est active. Remarquez comment Excel gère la copie des formules. La formule originale dans la cellule B12 est la suivante :

=SOMME(B3:B11)

Lorsque la formule d'origine est copiée dans la cellule C12, Excel modifie légèrement la formule pour qu'elle ressemble à ceci :

=SOMME(C3:C11)

Excel ajuste la référence de colonne en la modifiant de B en C, car vous avez copié de gauche à droite sur les lignes.

Lorsque vous copiez une formule dans une plage de cellules qui s'étend sur les lignes, Excel ajuste les numéros de ligne dans les formules copiées plutôt que les lettres de colonne en fonction de la position de chaque copie. Par exemple, la cellule E3 de la feuille de calcul Mother Goose Enterprises – Ventes 2016 contient la formule suivante :

=SOMME(B3:D3)

Lorsque vous copiez cette formule dans la cellule E4, Excel modifie la copie de la formule comme suit :

=SOMME(B4:D4)

Excel ajuste la référence de ligne pour rester à jour avec la nouvelle position de la ligne 4. Étant donné qu'Excel ajuste les références de cellule dans les copies d'une formule par rapport au sens de la copie, les références de cellule sont appelées références de cellule relatives .

Certaines choses sont absolues !

Toutes les nouvelles formules que vous créez contiennent naturellement des références de cellules relatives, sauf indication contraire. Étant donné que la plupart des copies que vous faites de formules nécessitent des ajustements de leurs références de cellules, vous devez rarement réfléchir à cet arrangement. Ensuite, de temps en temps, vous rencontrez une exception qui appelle à limiter quand et comment les références de cellules sont ajustées dans les copies.

L'une des exceptions les plus courantes est lorsque vous souhaitez comparer une plage de valeurs différentes avec une seule valeur. Cela se produit le plus souvent lorsque vous souhaitez calculer le pourcentage de chaque partie par rapport au total. Par exemple, dans la feuille de calcul Mother Goose Enterprises - Ventes 2016, vous rencontrez cette situation en créant et en copiant une formule qui calcule le pourcentage de chaque total mensuel (dans la plage de cellules B14:D14) du total trimestriel dans la cellule E12.

Supposons que vous souhaitiez saisir ces formules dans la ligne 14 de la feuille de calcul Mother Goose Enterprises - Ventes 2016, en commençant par la cellule B14. La formule de la cellule B14 pour calculer le pourcentage du total des ventes de janvier au premier trimestre est très simple :

=B12/E12

Cette formule divise le total des ventes de janvier dans la cellule B12 par le total trimestriel dans E12 (quoi de plus simple ?). Regardez, cependant, ce qui se passerait si vous faisiez glisser la poignée de remplissage d'une cellule vers la droite pour copier cette formule dans la cellule C14 :

=C12/F12

L'ajustement de la première référence de cellule de B12 à C12 est exactement ce que le médecin a ordonné. Cependant, le réglage de la deuxième référence de cellule de E12 à F12 est une catastrophe. Non seulement vous ne calculez pas le pourcentage des ventes de février dans la cellule C12 par rapport aux ventes du premier trimestre de E12, mais vous vous retrouvez également avec l'un de ces horribles #DIV/0 ! erreurs dans la cellule C14.

Pour empêcher Excel d'ajuster une référence de cellule dans une formule dans toutes les copies que vous effectuez, convertissez la référence de cellule de relative en absolue. Pour ce faire, appuyez sur la touche de fonction F4, après avoir mis Excel en mode Édition (F2). Excel indique que vous rendez la référence de cellule absolue en plaçant des signes dollar devant la lettre de la colonne et le numéro de la ligne. Par exemple, dans cette figure, la cellule B14 contient la formule correcte à copier dans la plage de cellules C14:D14 :

Copie de la formule de calcul du ratio des ventes mensuelles aux ventes trimestrielles avec une référence de cellule absolue.

=B12/$E$12

Regardez la feuille de calcul une fois cette formule copiée dans la plage C14:D14 avec la poignée de remplissage et la cellule C14 est sélectionnée (voir la figure suivante). Notez que la barre de formule indique que cette cellule contient la formule suivante :

La feuille de calcul après avoir copié la formule avec la référence de cellule absolue.

=C12/$E$12

Comme E12 a été remplacé par $E$12 dans la formule d'origine, toutes les copies ont cette même référence absolue (non changeante).

Si vous faites une gaffe et copiez une formule où une ou plusieurs références de cellules auraient dû être absolues mais que vous les avez toutes laissées relatives, modifiez la formule d'origine comme suit :

Double-cliquez sur la cellule avec la formule ou appuyez sur F2 pour la modifier.

Placez le point d'insertion quelque part sur la référence que vous souhaitez convertir en absolu.

Appuyez sur F4.

Lorsque vous avez terminé l'édition, cliquez sur le bouton Entrer dans la barre de formule, puis copiez la formule dans la plage de cellules en désordre avec la poignée de remplissage.

Assurez-vous d'appuyer une seule fois sur F4 pour changer une référence de cellule en complètement absolue comme je l'ai décrit plus tôt. Si vous appuyez une deuxième fois sur la touche de fonction F4, vous obtenez une référence dite mixte, où seule la partie ligne est absolue et la partie colonne est relative (comme dans E$12). Si vous appuyez à nouveau sur F4, Excel propose un autre type de référence mixte, où la partie colonne est absolue et la partie ligne est relative (comme dans $E12). Si vous continuez et appuyez à nouveau sur F4, Excel redéfinit la référence de cellule en une référence complètement relative (comme dans E12).

Une fois que vous êtes revenu à votre point de départ, vous pouvez continuer à utiliser F4 pour parcourir à nouveau ce même ensemble de modifications de référence de cellule.

Si vous utilisez Excel 2016 sur un appareil sans accès à un clavier physique avec des touches de fonction (comme une tablette à écran tactile), le seul moyen de convertir les adresses de cellules dans vos formules de relatives à absolues ou à une forme d'adresse mixte est d'ouvrir le clavier tactile et utilisez-le, ajoutez les signes dollar avant la lettre de la colonne et/ou le numéro de ligne dans l'adresse de cellule appropriée sur la barre de formule.