Formule générique
=SUM(firstcell:INDEX(data,rows,cols))
Sommaire
Pour définir une plage basée sur une valeur dans une autre cellule, vous pouvez utiliser la fonction INDEX. Dans l'exemple illustré, la formule en J7 est:
=SUM(C5:INDEX(data,J5,J6))
où «données» est la plage nommée B5: G9.
Explication
Cette formule repose sur un comportement spécifique d'INDEX - bien qu'il semble que INDEX renvoie la valeur à un emplacement particulier, il renvoie en fait la référence à l'emplacement. Dans la plupart des formules, vous ne remarquerez pas la différence - Excel évalue simplement la référence et renvoie la valeur. Cette formule utilise cette fonctionnalité pour créer une plage dynamique basée sur une entrée de feuille de calcul.
À l'intérieur de la fonction de somme, la première référence est simplement la première cellule de la plage qui couvre toutes les cellules possibles:
=SUM(C5:
Pour obtenir la dernière cellule, nous utilisons INDEX. Ici, nous donnons à INDEX la plage nommée "data", qui est la plage de valeurs maximale possible, ainsi que les valeurs de J5 (lignes) et J6 (colonnes). INDEX ne renvoie pas de plage, il ne renvoie qu'une seule cellule à cet emplacement, E9 dans l'exemple:
INDEX(data,J5,J6) // returns E9
La formule originale est réduite à:
=SUM(C5:E9)
qui renvoie 300, la somme de toutes les valeurs de C5: E9.
La formule dans J8 est presque la même, mais utilise MOYENNE au lieu de SOMME pour calculer une moyenne. Lorsqu'un utilisateur modifie les valeurs dans J5 ou J6, la plage est mise à jour et de nouveaux résultats sont renvoyés.
Alternative avec OFFSET
Vous pouvez créer des formules similaires avec la fonction OFFSET, illustrée ci-dessous:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET est conçu pour renvoyer une plage, les formules sont donc peut-être plus simples à comprendre. Cependant, OFFSET est une fonction volatile et peut entraîner des problèmes de performances lorsqu'il est utilisé dans des feuilles de calcul plus volumineuses et plus complexes.
