Pour créer une somme dynamique avec Excel, deux méthodes sont possibles.
- La méthode la plus simple c'est d'intégrer vos données dans un Tableau
- L'autre méthode consiste à concevoir une formule plus complexe avec la fonction INDEX
Insérer vos données dans un Tableau
Pour la première technique, il n'y a rien à faire 😁😂 Juste en mettant vos données dans un Tableau, Excel va automatiquement adapter les références des cellules.
En fait, quand vos données sont dans un Tableau, Excel "connait" les références de vos colonnes automatiquement, juste en ce basant sur le nom des colonnes. La mise en oeuvre de cette formule est extrêmement simple
- Insérer vos données dans un Tableau
- Créer une fonction SOMME
- Utilisez comme paramètre le nom de la colonne de votre Tableau
- Les références de la formule dans un Tableau sont de type NomduTableau[NomColonne]
ET C'EST TOUT 😀 L'ajout de toute nouvelles données va automatiquement adapter la dimension du Tableau. Donc votre fonction SOMME additionnera TOUJOURS toutes les cellules de la colonne sans changer la formule initiale.
Somme dynamique avec la fonction INDEX
La seconde technique est moins évidente car elle va jouer sur un paramètre très peu connu de la la fonction INDEX.
Dans sa forme normale, la fonction INDEX s'utilise comme la fonction RECHERCHEV. Mais dans notre situation, nous ne voulons pas retourner une valeur, mais la référence d'une cellule. C'est là toute l'astuce de la fonction INDEX.
- Elle vous permet de retourner une cellule dans un tableau de référence.
- Mais elle retourne également la référence de la cellule où la valeur a été trouvée (cellule A5, A6, A7, .....) 😮
Par exemple, nous allons construire
- Un menu déroulant pour sélectionner une date
- Pour cette date, une valeur est présente en colonne B
- Nous allons construire une fonction SOMME entre la première cellule (B2) et la cellule correspondant à la date sélectionnée.
Position de la date sélectionnée
Nous allons commencé par retourner la position de la date sélectionnée avec la fonction EQUIV. Dans l'animation suivante, vous voyez le résultat de la position de la date sélectionnée.
=EQUIV($D$5;$A$2:$A$13;0)
Intégrer EQUIV dans une fonction INDEX
Ensuite, nous allons intégrer cette information dans la fonction INDEX. Pour récupérer la valeur correspondant à la date sélectionnée, il faut
- Sélectionner la colonne qui contient les données à récupérer (colonne B)
- Inscrire comme 2ème argument, la fonction EQUIV précédente
- Et surtout ne pas oublié d'indiquer le paramètre 0 pour faire une recherche exacte
INDEX retourne une référence
Et c'est maintenant toute l'astuce.
- Pour retourner non pas la valeur 3 mais la référence de la cellule, il n'y a aucune modification à apporter à la formule.
- Il suffit d'intégrer la formule INDEX en tant que partie de la fonction SOMME
- Excel interprétera la fonction INDEX comme référence et plus comme valeur 😉
=SOMME(B2:INDEX($B$2:$B$13;EQUIV($D$5;$A$2:$A$13;0)))
Maintenant, à chaque sélection d'une date, la fonction précédente retourne la somme des valeurs entre la première cellule et celle correspondant à la date sélectionnée.
Les fonctions BYCOL et BYROW
Si vous travaillez avec Excel 365, les fonctions BYROW et BYCOL font des opérations dynamiques en ligne et en colonne. Mais en plus, vous pouvez spécifier le type de calcul, SOMME, MOYENNE, MIN, MAX, ....
19/06/2023 @ 10:47
Bonjour,
j'ai un tableau de notes. Avec la somme , je calcule ma note, puis un pourcentage.
Comment faire évoluer ce % avec le nombre de ligne, ou autrement dit, faire évoluer ce% en fonction du maximal possible ?
J'ai 5 linges, valant 4/4 donc j'ai une note de 20/ 20 donc 100%
si j'ajute une nouvelle note de 1/4, le total possible est de 24, mais mon total est de 21. Comment faire évoluer automatiquement le % ?
D'avance merci,
26/05/2023 @ 09:43
Bonjour
la formule ne marche pas j'ai de mon côté deux index equiv
28/04/2022 @ 22:01
Merci pour ce tutoriel. Ces fonctions sont très intéressantes. J'essaierai de les appliquer au besoin.
23/03/2022 @ 17:58
Bonjour,
J'essaie de faire une somme dynamique avec la fonction car le tableau n'est possible.
Dans mon cas les données sont en colonnes et non en lignes. Dans mon cas, cela fonctionne au mois.
Si je sélectionne le mois d'avril il ne m'additionnera que janvier et avril, mais ne prend pas en compte février et mars (colonnes situées entre les deux)
Merci d'avance pour votre aide
27/12/2021 @ 14:50
Bonjour,
J'ai le rapport mensuel des agents qui répertorie leurs activités journalières et je dois normalement insérer une formule qui pourra me donner la somme de transactions effectuées par chaque agent dans le mois.
Pouvez vous m'aider svp?
21/12/2021 @ 14:52
Bonjour,
je viens de faire la somme dynamique avec la fonction INDEX, et quand j'intègre ma formule INDEX dans la 2ème partie de la fonction somme, Excel interprète toujours la fonction INDEX comme valeur et pas comme référence.
Merci de bien vouloir m'éclairer sur mon problème,
cordialement,
21/12/2021 @ 22:39
Vous pouvez m'écrire votre formule ? Sans elle, je ne peux rien faire