Formules Excel SUM et OFFSET

Formules Excel SUM et OFFSET

Si votre feuille de calcul Excel contient des calculs basés sur des plages de cellules changeantes, 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 à l’aide des fonctions SUM et OFFSET

Si vous utilisez un calcul qui change dans le temps (par exemple, pour déterminer les ventes du mois en cours), utilisez la fonction OFFSET dans Excel pour définir une plage dynamique qui change à mesure que les chiffres des ventes augmentent chaque jour.

En elle-même, la fonction SOMME peut souvent insérer de nouvelles cellules de données dans la plage additionnée. Une exception se produit lors de l’insertion de données dans la cellule où se trouve actuellement la fonction.

Dans l’exemple ci-dessous, de nouvelles données de ventes pour chaque jour sont ajoutées au bas de la liste, forçant le total à descendre d’une cellule chaque fois que de nouvelles données sont ajoutées.

Pour suivre ce didacticiel, ouvrez une feuille de calcul Excel vierge et entrez des 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 des données dans les mêmes cellules.

Si vous utilisez uniquement la fonction SOMME pour résumer les données, vous devrez modifier la plage de cellules utilisée comme paramètre de la fonction chaque fois que vous ajouterez de nouvelles données.

En utilisant les fonctions SUM et OFFSET ensemble, la plage du total devient dynamique et change pour s’adapter à la nouvelle cellule de données. L’ajout de nouvelles cellules de données ne pose pas de problème car la plage continue de s’ajuster à chaque nouvelle cellule ajoutée.

Syntaxe et paramètres

Dans cette formule, la fonction SOMME est utilisée pour résumer la plage de données fournies en tant qu’argument. Le point de départ de cette plage est statique et identifié comme une référence de cellule au premier nombre à additionner par la formule.

La fonction OFFSET est imbriquée dans la fonction SOMME et crée un point de terminaison dynamique pour la plage de données que la formule résume. Ceci est réalisé en définissant le point final de la plage à une cellule au-dessus de la position de la formule.

La syntaxe de la formule est :

=SOMME(début de la plage :décalage(référence,ligne,colonne))

Les arguments sont :

• début de gamme: point de départ de la plage de cellules à additionner par la fonction SOMME. Dans cet exemple, le point de départ est la cellule B2.
• faire référence à: la référence de cellule nécessaire pour calculer les extrémités de la plage. Dans l’exemple, le paramètre Référence est la référence de cellule de la formule car la plage se termine une cellule au-dessus de la formule.
• Ligne: Le nombre de lignes au-dessus ou au-dessous du paramètre de référence utilisé lors du calcul du décalage est requis. La valeur peut être positive, négative ou définie sur zéro. La valeur est négative si la position de décalage est supérieure au paramètre de référence. Le paramètre Lignes est positif si le décalage est inférieur. Si le décalage est sur la même ligne, l’argument est zéro. Dans cet exemple, le décalage commence une ligne au-dessus du paramètre Référence, donc la valeur de ce paramètre est moins un (-1).
• Coles: nombre de colonnes à gauche ou à droite du paramètre de référence utilisé pour calculer le décalage. La valeur peut être positive, négative ou définie sur zéro. La valeur est négative si la position de décalage est à gauche du paramètre de référence. Le paramètre Cols est positif si le décalage est vers la droite. Dans cet exemple, les données résumées se trouvent dans la même colonne que la formule, donc la valeur de ce paramètre est zéro.

Utilisez la formule SUM OFFSET pour calculer le total des ventes

Cet exemple utilise la formule SUM OFFSET pour renvoyer le nombre total de données de ventes quotidiennes répertoriées dans la colonne B de la feuille de calcul. Initialement, la formule est entrée dans la cellule B6 et quatre jours de données de ventes sont agrégés.

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. Cela se fait en insérant une nouvelle ligne 6, qui déplace la formule à la ligne 7.

À la suite du déplacement, Excel met automatiquement à jour le paramètre de référence dans la cellule B7 et ajoute la cellule B6 à la plage que la formule additionne.

1. sélectionner une cellule B6où les résultats de la formule sont initialement affichés.

2. choisir formule Onglets du ruban.

3. choisir Mathématiques et Trigonométrie.

4. choisir et.

5. à l’intérieur paramètre de fonction boîte de dialogue, placez le curseur sur la numéro 1 zone de texte.

6. Dans la feuille de calcul, sélectionnez la cellule B2 Saisissez cette référence de cellule dans la boîte de dialogue. Cet emplacement est le point de terminaison statique de la formule.

7. à l’intérieur paramètre de fonction boîte de dialogue, placez le curseur sur la numéro 2 zone de texte.

8. Entrer décalage(B6,-1,0)Cette fonction OFFSET forme le point final dynamique de la formule.

9. choisir D’ACCORD Terminez la fonction et fermez la boîte de dialogue. Le total apparaît dans la cellule B6.

Ajouter des données de ventes pour le lendemain

Ajoutez les données de ventes pour le lendemain :

1. Cliquez avec le bouton droit sur l’en-tête de la ligne 6.

2. choisir insérer Insérez une nouvelle ligne dans la feuille de calcul. La formule SUM OFFSET descend d’une ligne vers la cellule B7, la ligne 6 est maintenant vide.

3. sélectionner une cellule A6 et entrez le numéro 5 Indique que le total des ventes du cinquième jour est entré.

4. sélectionner une cellule B6Entrer 1458,25 $puis appuyez Entrer.

5. La cellule B7 est mise à jour avec un nouveau total de 7137,40 $.

Lorsque vous sélectionnez la cellule B7, la formule mise à jour apparaît dans la barre de formule.

=SOMME(B2:DECALAGE(B7,-1,0))

La fonction OFFSET a deux paramètres optionnels : Hauteur et Largeur, Non utilisé dans cet exemple. Ces arguments indiquent à la fonction OFFSET la forme de la sortie en fonction du nombre de lignes et de colonnes.

En omettant ces paramètres, la fonction utilise la hauteur et la largeur des paramètres de référence, qui dans ce cas ont une hauteur d’une ligne et une largeur de colonne.

Merci de nous en informer!

Recevez les dernières nouvelles technologiques publiées quotidiennement

abonnement

Dites-nous pourquoi !

D’autres détails ne sont pas assez difficiles à comprendre