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))
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. Le total apparaît dans la cellule B7. Pour ajouter les données de vente du lendemain: 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.
Utilisation de la formule SUMSET OFF pour le total des données de vente
Saisie de la formule SUM OFFET
Ajout des données de vente du lendemain