Si votre feuille de calcul Excel comprend des calculs basés sur une plage de cellules changeante, utilisez les fonctions SUM et OFFSET ensemble dans une formule SUM OFFSET pour simplifier la tâche de mise à jour des calculs.
Les instructions de cet article s'appliquent à Excel pour Microsoft 365, Excel 2019, Excel 2016, Excel 2013 et Excel 2010.
Créer une plage dynamique avec les fonctions SUM et OFFSET
Si vous utilisez des calculs pour une période de temps qui change continuellement - comme la détermination des ventes pour le mois - utilisez la fonction OFFSET dans Excel pour configurer une plage dynamique qui change à mesure que les chiffres des ventes de chaque jour sont ajoutés.
En soi, la fonction SOMME peut généralement permettre l'insertion de nouvelles cellules de données dans la plage additionnée. Une exception se produit lorsque les données sont insérées dans la cellule où se trouve actuellement la fonction.
Dans l'exemple ci-dessous, les nouveaux chiffres de vente pour chaque jour sont ajoutés au bas de la liste, forçant le total à descendre continuellement d'une cellule à chaque fois que les nouvelles données sont ajoutées.
Pour suivre ce didacticiel, ouvrez une feuille de calcul Excel vierge et entrez les exemples de données. Votre feuille de calcul n'a pas besoin d'être formatée comme dans l'exemple, mais assurez-vous d'entrer les données dans les mêmes cellules.
Si seule la fonction SOMME est utilisée pour totaliser les données, la plage de cellules utilisée comme argument de la fonction devra être modifiée à chaque fois que de nouvelles données sont ajoutées.
En utilisant les fonctions SUM et OFFSET ensemble, la plage totalisée devient dynamique et change pour s'adapter aux nouvelles cellules de données. L'ajout de nouvelles cellules de données ne pose pas de problème car la plage continue de s'ajuster au fur et à mesure que chaque nouvelle cellule est ajoutée.
Syntaxe et Arguments
Dans cette formule, la fonction SOMME est utilisée pour totaliser la plage de données fournies comme argument. Le point de départ de cette plage est statique et est identifié comme la référence de cellule au premier nombre totalisé par la formule.
La fonction OFFSET est imbriquée dans la fonction SOMME et crée un point final dynamique pour la plage de données totalisées par la formule. Ceci est accompli en définissant le point final de la plage sur une cellule au-dessus de l'emplacement de la formule.
La syntaxe de la formule est:
=SOMME(Début de la plage:OFFSET(Référence, Lignes, Cols))
Les arguments sont:
- Range Start: le point de départ de la plage de cellules qui sera totalisée par la fonction SOMME. Dans cet exemple, le point de départ est la cellule B2.
- Reference: la référence de cellule requise utilisée pour calculer le point de terminaison de la plage. Dans l'exemple, l'argument Référence est la référence de cellule pour la formule car la plage se termine une cellule au-dessus de la formule.
- Rows: le nombre de lignes au-dessus ou au-dessous de l'argument de référence utilisé pour calculer le décalage est requis. Cette valeur peut être positive, négative ou définie sur zéro. Si l'emplacement de décalage est au-dessus de l'argument Référence, la valeur est négative. Si le décalage est inférieur, l'argument Lignes est positif. Si le décalage est situé dans la même ligne, l'argument est zéro. Dans cet exemple, le décalage commence une ligne au-dessus de l'argument Référence, donc la valeur de l'argument est moins un (-1).
- Cols: le nombre de colonnes à gauche ou à droite de l'argument de référence utilisé pour calculer le décalage. Cette valeur peut être positive, négative ou définie sur zéro. Si l'emplacement du décalage se trouve à gauche de l'argument Référence, cette valeur est négative. Si le décalage est à droite, l'argument Cols est positif. Dans cet exemple, les données totalisées se trouvent dans la même colonne que la formule, donc la valeur de cet argument est zéro.
Utilisez la formule SUM OFFSET pour les données de ventes totales
Cet exemple utilise une formule SUM OFFSET pour renvoyer le total des chiffres de vente quotidiens répertoriés dans la colonne B de la feuille de calcul. Initialement, la formule a été saisie dans la cellule B6 et a totalisé les données de vente pendant quatre jours.
L'étape suivante consiste à déplacer la formule SUM OFFSET d'une ligne vers le bas pour faire de la place pour le total des ventes du cinquième jour. Ceci est accompli en insérant une nouvelle ligne 6, qui déplace la formule à la ligne 7.
À la suite du déplacement, Excel met automatiquement à jour l'argument Référence de la cellule B7 et ajoute la cellule B6 à la plage additionnée par la formule.
- Sélectionner la cellule B6, qui est l'emplacement où les résultats de la formule s'afficheront initialement.
-
Sélectionnez l'onglet Formules du ruban.
Image -
Choose Math & Trig.
Image -
Sélectionner SOMME.
Image -
Dans la boîte de dialogue Function Arguments, placez le curseur dans la zone de texte Number1.
-
Dans la feuille de calcul, sélectionnez la cellule B2 pour entrer cette référence de cellule dans la boîte de dialogue. Cet emplacement est le point de terminaison statique de la formule.
Image - Dans la boîte de dialogue Function Arguments, placez le curseur dans la zone de texte Number2.
-
Entrez OFFSET(B6, -1, 0). Cette fonction OFFSET constitue le point final dynamique de la formule.
Image -
Sélectionnez OK pour terminer la fonction et fermer la boîte de dialogue. Le total apparaît dans la cellule B6.
Image
Ajouter les données de ventes du jour suivant
Pour ajouter les données de ventes du lendemain:
- Cliquez avec le bouton droit sur l'en-tête de la ligne 6.
-
Sélectionnez Insert pour insérer une nouvelle ligne dans la feuille de calcul. La formule SUM OFFSET descend d'une ligne vers la cellule B7 et la ligne 6 est maintenant vide.
Image - Sélectionnez la cellule A6 et entrez le nombre 5 pour indiquer que le total des ventes pour le cinquième jour est entré.
-
Sélectionnez la cellule B6, entrez $1458.25, puis appuyez sur Entrée.
Image - Cellule B7 mise à jour vers le nouveau total de 7 137,40 $.
Lorsque vous sélectionnez la cellule B7, la formule mise à jour apparaît dans la barre de formule.
=SOMME(B2:OFFSET(B7, -1, 0))
La fonction DECALER a deux arguments optionnels: Hauteur et Largeur, qui n'ont pas été utilisés dans cet exemple. Ces arguments indiquent à la fonction OFFSET la forme de la sortie en termes de nombre de lignes et de colonnes.
En omettant ces arguments, la fonction utilise à la place la hauteur et la largeur de l'argument Référence, qui, dans cet exemple, est d'une hauteur d'une ligne et d'une largeur de colonne.
