Somme de données sur une plage variable

[llobica]

Bonsoir à tous,

Voici un problème qui je pense pour les utilisateurs avertis semblera très simple...

Pour illustrer, je vous présente le cas en PJ.

Voici mon cas :
- En ligne 1 : condition (soit 0 soit 1)
- En ligne 2 : données numériques

Lorsqu'en ligne 1 j'ai la valeur/condition 1:
je souhaite faire la somme des données de la ligne 2 jusqu'à la (colonne-1) pour laquelle en ligne 1 j'ai la condition 1.

Par exemple :
- en F1 et en I1 on a la valeur 1
- J'attends en F3 le résultat =somme(F1:H1)

Pour faire cela de manière automatique, j'avais pensé à une formule type :
=SOMME(F2:RECHERCHEH(1;G1:M2;2;FAUX))
Cela m'aurait permis dans un premier temps de faire la somme des valeurs comprises et incluses entre 2 colonnes avec la condition ligne 1=1. Après j'aurai encore dû trouver comment soustraire la valeur de la dernière colonne..
Mais cela ne fonctionne pas, on ne peut définir une plage de donnée de cette manière avec la fonction somme...

Auriez-vous une solution ?

En vous remerciant par avance de l'aide que vous pourrez m'apporter,
Bien cordialement,

llobica

 

[mapomme]

Re : Somme de données sur une plage variable

Bonsoir llobica ,

Errata voir message suivant.

 

[mapomme]

Re : Somme de données sur une plage variable

Bonsoir llobica ,

Essayer plutôt cette formule en B3 à tirer vers la droite:

=SI(B1=1;SOMME(DECALER(B2;0;0;1; SIERREUR( EQUIV(1;C1:$XFD1;0);1) ));"")

 

[llobica]

Re : Somme de données sur une plage variable

Bonsoir mapomme,

Merci beaucoup pour votre retour !
En effet cela fonctionne comme je le souhaitais !

Par contre la formule est un peu complexe pour moi (je n'ai jamais utilisé la fonction DECALER auparavant), pouvons-nous reprendre les différents éléments ?
Si l'on se place en F3 :
- La fonction DECALER
*permet de fixer la référence en F2
*0 permet de rester sur la même ligne
*0 permet de rester sur la même colonne
*1 permet de définir la hauteur : à quoi correspond exactement ce paramètre ?
*Pour définir la largeur en nombre de colonne :
~On utilise la fonction EQUIV
°en recherchant la valeur 1
°dans la plage G1:$XFD1 (soit à partir de l colonne suivante)
°

0 : renvoie la valeur exacte ?
~Si la fonction EQUIV tombe en erreur, on renvoie la valeur 1 : pourquoi ?

En français : Cela va faire la somme à partir de la référence F2 en décalant les colonnes jusqu'à retrouver la valeur 1.

Je n'arrive pas à comprendre comment vous arrivez à ne pas prendre en compte dans la somme la valeur en ligne 2 pour laquelle on retrouve la condition 1 en ligne 1....

Cordialement,

llobica

 

[mapomme]

Re : Somme de données sur une plage variable

(re) Bonsoir llobica,

Vous avez tout juste . Quelques précisions (on travaille sur la formule en F3):

[...] 1 permet de définir la hauteur : à quoi correspond exactement ce paramètre ? [...]

La hauteur est le nombre de lignes qu'on désire pour la plage résultat de la fonction DECALER. Comme ici nous ne faisons la somme que sur une ligne unique (ligne 2), la hauteur est 1 (ligne).

[...] 0 : renvoie la valeur exacte ? [...]

C'est exact. EQUIV va renvoyer le rang du premier chiffre 1 qu'il va trouver dans la plage G1:$XFD1.
S'il ne trouve pas de valeur 1, alors EQUIV renvoie la valeur d'erreur #N/A

[...] Si la fonction EQUIV tombe en erreur, on renvoie la valeur 1 : pourquoi ? [...]

Si EQUIV renvoie une erreur, alors il n'y a plus aucune valeur 1 à droite de F1. On a donc uniquement à sommer la cellule F2.

DECALER(F2,0,0,1,1) fait bien référence à la cellule F2 et uniquement à la cellule F2.
==> on décale F2 de 0 ligne et 0 colonne -> On retombe sur F2
==> on redimensionne F2 à une ligne en hauteur et une colonne en largeur -> On retombe encore sur F2.

[...] Je n'arrive pas à comprendre comment vous arrivez à ne pas prendre en compte dans la somme la valeur en ligne 2 pour laquelle on retrouve la condition 1 en ligne 1... [...]

Soit la formule en F3:
=SI(F1=1;SOMME(DECALER(F2;0;0;1; SIERREUR( EQUIV(1;G1:$XFD1;0);1) ));"")

La valeur 1 suivante est en colonne I. EQUIV va renvoyer le rang de cette valeur au sein de la plage G1:$XFD1. EQUIV va donc renvoyer 3.
Mais la fonction DECALER agit sur la cellule F2. On s'aperçoit que si on redimensionne F2 d'une largeur de 3, alors on englobe seulement les cellules de F2 à H2. C'est bien ce qu'on désire i.e. ne pas prendre la valeur de la colonne I.

En fait la recherche (par EQUIV) donne 3 mais à partir de la colonne G alors que la fonction DECALER agit non pas à partir de la colonne G mais de la colonne F (cellule F2). Cela explique pourquoi on ne prend pas la cellule correspondant au 1 suivant dans la somme.

[...] En français : Cela va faire la somme à partir de la référence F2 en décalant les colonnes jusqu'à retrouver la valeur 1. [...]

On pourrait donc préciser:
En français : Cela va faire la somme à partir de la référence F2 en décalant les colonnes jusqu'à retrouver la valeur 1, en excluant la colonne de la valeur 1 suivante.

Si ce n'est pas clair, me le signaler,

cordialement,

 

[llobica]

Re : Somme de données sur une plage variable

Re-bonsoir mapomme,

Un grand merci pour ces explications !
Tout cela est maintenant parfaitement clair pour moi et cela m'a permis d'apprendre de nouvelles choses.

Vous souhaitant une bonne soirée,
Bien cordialement,

llobica