Skip to main content

Formule Excel SUM et OFFSET

How to use SUM and OFFSET Excel functions (Mars 2024)

How to use SUM and OFFSET Excel functions (Mars 2024)
Anonim

Si votre feuille de calcul Excel inclut des calculs basés sur une plage de cellules changeante, l'utilisation conjointe des fonctions SUM et OFFSET dans une formule SUM OFFSET simplifie la tâche de mise à jour des calculs.

Créer une plage dynamique avec les fonctions SUM et OFFSET

Si vous utilisez des calculs sur une période de temps qui change continuellement, telle que le total des ventes du mois, la fonction OFFSET d'Excel vous permet de configurer une plage dynamique qui change à mesure que les chiffres des ventes de chaque jour sont ajoutés.

En soi, la fonction SOMME permet généralement l'insertion de nouvelles cellules de données dans la plage en cours de sommation.

Une exception se produit lorsque les données sont insérées dans la cellule où la fonction est actuellement située.

Dans l'exemple ci-dessus, les nouveaux chiffres des ventes de chaque jour sont ajoutés au bas de la liste, ce qui oblige le total à décaler continuellement d'une cellule à chaque ajout de nouvelles données.

Si la fonction SOMME était utilisée seule pour additionner les données, il serait nécessaire de modifier la plage de cellules utilisée comme argument de la fonction chaque fois que de nouvelles données étaient ajoutées.

En utilisant simultanément les fonctions SUM et OFFSET, la plage totalisée devient dynamique. En d'autres termes, cela change pour accueillir 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 chaque nouvelle cellule est ajoutée.

Syntaxe et arguments

Reportez-vous à l'image ci-dessus pour suivre ce didacticiel.

Dans cette formule, la fonction SUM est utilisée pour additionner la plage de données fournie en tant qu'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 sert à créer un point de terminaison dynamique pour la plage de données totalisée par la formule. Pour ce faire, définissez le point final de la plage sur une cellule au-dessus de l'emplacement de la formule.

La syntaxe de la formule:

= SUM (Début de la plage: OFFSET (référence, lignes, colonnes))

  • Début de gamme: Le point de départ pour la plage de cellules qui sera totalisée par la fonction SOMME est requis. Dans l'exemple d'image, il s'agit de la cellule B2.
  • Référence: La référence de cellule requise utilisée pour calculer le point final de la plage est située à de nombreuses lignes et colonnes. Dans l'exemple d'image, l'argument Référence est la référence de cellule pour la formule elle-même, car vous souhaitez toujours que la plage se termine par une cellule au-dessus de la formule.
  • Rangées: Le nombre de lignes au-dessus ou au-dessous de l'argument Référence utilisé dans le calcul du décalage est requis. Cette valeur peut être positive, négative ou définie sur zéro. Si l'emplacement de l'offset est supérieur à la Référence argument, la valeur est négative. S'il est en dessous, 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 Référence, la valeur de cet argument est donc négative (-1).
  • Cols: Le nombre de colonnes à gauche ou à droite de l'argument Référence utilisé dans le calcul du décalage est requis. Cette valeur peut être positive, négative ou définie sur zéro. Si l'emplacement de l'offset est à gauche de l'argument Reference, cette valeur est négative. Si à droite, l'argument de Cols est positif. Dans cet exemple, les données en cours de totalisation se trouvent dans la même colonne que la formule. La valeur de cet argument est donc zéro.

Utilisation de la formule SUMSET OFF pour le total des données de vente

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é entrée dans la cellule B6 et a totalisé les données de vente sur quatre jours.

L'étape suivante consiste à déplacer la formule SOMME OFFSET d'une rangée 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 vers la ligne 7.

À la suite de ce déplacement, Excel met automatiquement à jour l'argument Référence en cellule.B7 et ajoute la celluleB6 à la gamme résumée par la formule.

Saisie de la formule SUM OFFET

  1. Cliquez sur la cellule B6, qui est l'emplacement où les résultats de la formule seront initialement affichés.
  2. Clique sur leFormules onglet du menu du ruban.
  3. ChoisirMath & Trigdu ruban pour ouvrir la liste déroulante des fonctions.
  4. Cliquer surSOMME dans la liste pour faire apparaître la boîte de dialogue de la fonction.
  5. Dans la boîte de dialogue, cliquez sur le boutonNuméro 1ligne.
  6. Cliquez sur 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.
  7. Dans la boîte de dialogue, cliquez sur le boutonNuméro 2ligne.
  8. Entrez la fonction OFFSET suivante: OFFSET (B6, -1,0) pour former le point de terminaison dynamique de la formule.
  9. Cliquez sur D'accord pour compléter la fonction et fermer la boîte de dialogue.

Le total apparaît dans la cellule B7.

Ajout des données de vente du lendemain

Pour ajouter les données de vente du lendemain:

  1. Cliquez avec le bouton droit sur l'en-tête de la ligne 6 pour ouvrir le menu contextuel.
  2. Dans le menu, cliquez surInsérer insérer une nouvelle ligne dans la feuille de calcul.
  3. Par conséquent, la formule SOMME OFFSET passe à la cellule B7 et la ligne 6 est maintenant vide.
  4. Cliquez sur la cellule A6.
  5. Entrez le numéro5 pour indiquer que le total des ventes du cinquième jour est en cours de saisie.
  6. Cliquez sur la cellule B6.
  7. Tapez le nombre$1458.25 et appuyez sur leEntrer touche du clavier.

La cellule B7 met à jour le nouveau total de$7137.40.

Lorsque vous cliquez sur la cellule B7, la formule mise à jour= SUM (B2: OFFSET (B7, -1,0)) apparaît dans la barre de formule.

La fonction OFFSET a deux arguments optionnels: Hauteur et Largeur , qui n'ont pas été utilisés dans cet exemple.

Ces arguments peuvent être utilisés pour indiquer à la fonction OFFSET la forme de la sortie en fonction du nombre élevé de lignes et de colonnes.

En omettant ces arguments, la fonction utilise par défaut les hauteur et largeur de l'argument Référence, qui, dans cet exemple, a une rangée de haut et une largeur de colonne.