Exceller

Définir la plage en fonction de la valeur de la cellule

Define Range Based Cell Value

Formule Excel: définir la plage en fonction de la valeur de la celluleFormule générique
= SUM (firstcell: INDEX (data,rows,cols))
Résumé

Pour définir une plage en fonction d'une valeur dans une autre cellule, vous pouvez utiliser le Fonction INDEX . Dans l'exemple illustré, la formule en J7 est:

 
= SUM (C5: INDEX (data,J5,J6))

où 'data' est le plage nommée B5: G9.



Explication

Cette formule repose sur un comportement spécifique d'INDEX - bien qu'il semble que INDEX renvoie le évaluer à un endroit particulier, il renvoie en fait le 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 construire une plage dynamique basée sur l'entrée de feuille de calcul.



comment créer une colonne totale dans Excel

À 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 le 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:



compter si la cellule contient du texte spécifique
 
 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.

comment ajouter une liste déroulante pour exceller

La formule de J8 est presque la même, mais utilise MOYEN 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 le Fonction OFFSET , indiqué 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 un 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.

Auteur Dave Bruns


^