Somme d'un nombre variable de cellule

Le sondage de SVM m’a donnée une idée d’un nouvel article.
Imaginons que vous ayez à faire des statistiques sur les gouts des enfants (peu importe l’exemple). Vous obtenez un tableau comme celui-ci.

Le but de cette manipulation est de regrouper certaines colonnes pour obtenir, par exemple quel pourcentage d’enfant n’aime pas vraiment tel plat.

Facile direz vous. Et, présenté comme cela, vous avez raison ; une simple somme dans chaque colonne de regroupement suffi. Mais...

... en regardant mon tableau de plus prêt, vous découvrez une liste déroulante.

Changez la valeur de cette colonne et les deux colonnes de regroupement s’adaptent à la demande.

Pas de magie, seulement l’utilisation de la fonction Decaler().

La cellule de choix contenant la liste déroulant est nommée SeparL.
La liste déroulante est obtenue par l’entremise de Données, Validation
Critère de validation : Autoriser Liste
Source : ListeChoix
ou ListeChoix est le nom donné à l’ensemble des entêtes des colonnes de résultat à l’exception de la dernière colonne.
La cellule d’à coté, nommée SeparN, calcule le rang du choix
=EQUIV(SeparL;ListeChoix;0)

Maintenant que tout est en place, voyons notre formule miracle.

Dans la cellule H6 (en haut à gauche des regroupements) inscrivons :
=SOMME(DECALER($B6;0;0;1;SeparN))

Cette formule fait la somme d’un champ qui est calculé…
Commençons par la cellule B6,
on se décale de 0 ligne, puis de 0 colonnes ce qui donne toujours B6
sur une hauteur de 1 (toujours B6)
sur une largeur de SeparN, et c’est là le truc ;
    si SeparN = 1 : toujours B6
    si SeparN = 2 : B6:C6
    si SeparN = 3 : B6:D6

Pigé ?

Téléchargez donc la feuille de calcul afin de jouer avec et mieux comprendre Téléchargement Decaler.xls.

La seconde formule est à peine plus compliquée puisqu’il faut que le champ commence à la cellule qui suit celle que prend en compte la première formule.
Ce qui donne :

=SOMME(DECALER($B6;0;SeparN;1;5-SeparN))

Décalage de 0 ligne mais SeparN colonnes
Taille 1 ligne et le nombre de colonnes total (5) moins SeparN pour la largeur.

Si mes explications ne sont pas suffisantes, n’hésitez pas à me le faire remarquer.

Comments

[Rédigé par : Marie-Hélène]

Avec le fichier joint, les fonctions deviennent limpides ! Bravo et merci pour ce site et ces formules magiques !

[Rédigé par : OG]

Bonjour,

Mon expérience est que l'utilisation INTENSIVE de la fonction DECALER() accroît le temps de traitement (et peut-être la taille fichier, mais je n'en suis pas sûr).

Je préfère depuis longtemps utiliser INDEX() et rendre variable les N° de colonnes (comme dans l'exemple) ou de lignes...

[Rédigé par : Laurent]

Merci pour ces formules qui vont m'aider, justement je devais trouver une solution pour faire des statistiques.