Formule de calcul Excel SUM et OFFSET

Si votre feuille de calcul Excel comprend des calculs basés sur une gamme de cellules changeantes, utilisez les fonctions SOMME et DECALER dans une formule SOMME DECALER pour simplifier la tâche de mise à jour des calculs.

Créer une gamme dynamique avec les fonctions SOMME et DECALER

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 établir une gamme dynamique qui change au fur et à mesure que les chiffres de vente de chaque jour sont ajoutés.

En soi, la fonction SUM peut généralement permettre l’insertion de nouvelles cellules de données dans l’intervalle à additionner. 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, ce qui oblige à réduire continuellement le total d’une cellule à chaque fois que de nouvelles données sont ajoutées.

Si seule la fonction SUM 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 seront ajoutées.

En utilisant ensemble les fonctions SOMME et DECALER, la plage totalisée devient dynamique et change pour s’adapter à de 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 à mesure que de nouvelles cellules sont ajoutées.

Syntaxe et arguments

Dans cette formule, la fonction SUM est utilisée pour totaliser l’ensemble des données fournies comme argument. Le point de départ de cette plage est statique et est identifié comme la référence de la cellule au premier nombre à totaliser par la formule.

La fonction OFFSET est imbriquée dans la fonction SUM et crée un point final dynamique à la gamme de données totalisées par la formule. Cela est réalisé en fixant le point final de la plage à une cellule au-dessus de l’emplacement de la formule.

La syntaxe de la formule est :

=SUM(Range Start:OFFSET(Reference,Rows,Cols))

Les arguments sont les suivants :

• • Début de la gamme: Le point de départ de l’éventail des cellules qui seront totalisées par la fonction SUM. Dans cet exemple, le point de départ est la cellule B2.
  • Référence: La référence de la cellule requise utilisée pour calculer le point final de la gamme. Dans l’exemple, l’argument Référence est la référence de la cellule pour la formule car l’intervalle se termine une cellule au-dessus de la formule.
  • Lignes: Le nombre de lignes au-dessus ou au-dessous de l’argument de référence utilisé pour le calcul du décalage est requis. Cette valeur peut être positive, négative, ou fixée à zéro. Si l’emplacement du décalage est au-dessus de l’argument Reference, la valeur est négative. Si le décalage est inférieur, l’argument Rows 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 Reference, la valeur de l’argument est donc négative (-1).
  • Cols: Le nombre de colonnes à gauche ou à droite de l’argument Référence utilisé pour calculer le décalage. Cette valeur peut être positive, négative, ou fixée à zéro. Si l’emplacement du décalage est à gauche de l’argument Reference, 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.

Utiliser la formule SUM OFFSET pour les données sur les ventes totales

Cet exemple utilise une formule SUM OFFSET pour obtenir le total des chiffres de vente quotidiens indiqués dans la colonne B de la feuille de calcul. Au départ, la formule a été saisie dans la cellule B6 et a totalisé les données de ventes pour quatre jours.

L’étape suivante consiste à déplacer la formule SUM OFFSET vers le bas d’une ligne pour faire place au total des ventes du cinquième jour. Pour ce faire, on insère une nouvelle ligne 6, qui déplace la formule à la ligne 7.

À la suite de ce déplacement, Excel met automatiquement à jour l’argument de référence dans la cellule B7 et ajoute la cellule B6 à l’intervalle additionné par la formule.

• Sélectionnez une cellule B6qui est l’endroit où les résultats de la formule seront initialement affichés.
• Sélectionnez le Formules onglet du ruban.
•  Choisissez Math & Trig.
• Sélectionnez SOMME.
•  Dans le Fonction Arguments placez le curseur dans la boîte de dialogue Numéro 1 zone de texte.
• Dans la feuille de travail, sélectionnez la cellule B2 pour saisir cette référence de cellule dans la boîte de dialogue. Cet emplacement est le point final statique de la formule.
• Dans le Fonction Arguments placez le curseur dans la boîte de dialogue Numéro 2 zone de texte.
• Saisissez COMPENSATION(B6,-1,0). Cette fonction OFFSET constitue le point final dynamique de la formule.
• Sélectionnez OK pour terminer la fonction et fermer la boîte de dialogue. Le total apparaît dans la cellule B6.

Ajouter les données de vente du lendemain

Pour ajouter les données de vente du lendemain :

• Cliquez avec le bouton droit de la souris sur l’en-tête de la ligne 6.
• Sélectionnez Insérer pour insérer une nouvelle ligne dans la feuille de calcul. La formule SUM OFFSET se déplace d’une ligne vers le bas dans la cellule B7 et la ligne 6 est maintenant vide.
• Sélectionnez une cellule A6 et entrez le numéro 5 pour indiquer que le total des ventes du cinquième jour est saisi.
• Sélectionnez une cellule B6, entrez $1458.25puis appuyez sur Saisissez.
• La cellule B7 passe au nouveau total de 7137,40 $.
• Lorsque vous sélectionnez la cellule B7, la formule mise à jour apparaît dans la barre de formule.

=SUM(B2:OFFSET(B7,-1,0))

La fonction OFFSET a deux arguments facultatifs : 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 ligne de haut et d’une colonne de large.