XL 2016 Somme si (sous-total)

[MLe]

Bonjour à tous,
Je souhaite réaliser une somme mais avec deux conditions et un sous total en colonne M
1) Si B7 est différent de S_Total alors = B7
2) Si B7 = S_Total alors somme de la colonne C jusque B = S_total (du bas vers le haut)
Avec le fichier, c'est plus facile à comprendre.
Remarque: on ne peut pas tenir compte de la valeur de la colonne en M pour la somme (j'aurai une équation)
La colonne N donne la réponse souhaitée.
Je pense avoir bien passé une trentaine d'heures et ce n'est pas faute d'avoir chercher.
Merci d'avance pour votre aide.
Maxime

 

[mapomme]

Bonsoir @MLe,

Voir formules en M7 et surtout celle en M8 à recopier vers le bas...

edit: une autre formule -> voir colonne P

 

[MLe]

Un merci très sincère Monsieur le magicien. J'ai tellement cherché pendant des jours … Mais je dois avouer qu'il me reste un petit problème. Normalement, la réponse "M" ou "P" devait remplacer "X" dans la formule (0,8*1/(RACINE(X-1))). Je ne pouvais pas utiliser "M" mais "C"
=SI(B8<>"S_Total";C8;SOMME(C7:C$7)-SOMME.SI(B7:B$7;"S_Total";M7:M$7 ))
Je suis peut-être obligé de tricher avec deux colonnes ? Une que je vais cacher avec la réponse "M" et l'autre : =

(0,8*1/(RACINE(M-1)))​

(0,8 * 1) 1 est une variable

Sinon, dans "=SI(B8<>"S_Total";C8;SOMME(M$7:M7)-2*SOMME.SI(B$7:B7;"S_Total";M$7:M7))" C'est le -2 que j'ai vraiment du mal à comprendre.
Encore un grand merci pour le temps que tu as passé à me répondre. Je te souhaite une belle fin d'année à toi et tes proches.
Maxime

 

[mapomme]

Bonsoir @MLe,

Pas tout compris dans ton dernier message

Peut-être dans le fichier joint. Formule matricielle en P8 à tirer vers le bas. Cette formule n'utilise que les colonnes B et C et non plus la colonne M.

 

[mapomme]

Sinon, dans "=SI(B8<>"S_Total";C8;SOMME(M$7:M7)-2*SOMME.SI(B$7:B7;"S_Total";M$7:M7))" C'est le -2 que j'ai vraiment du mal à comprendre

SOMME(M$7:M7) ==> On fait le total des valeurs de la colonne M (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule). Ce total peut se décomposer comme étant la somme de :

• des valeurs composant le premier sous-total
• du premier sous-total
• des valeurs composant le deuxième sous-total
• du deuxième sous-total
• ...
• ...
• des valeurs composant le dernier sous-total avant la ligne courant
• du dernier sous-total avant la ligne courante
• et enfin des valeurs composant le sous-total qu'on cherche à calculer.

On s’aperçoit qu'on somme :

• le premier sous-total ainsi que les valeurs qui le composent (soit deux fois le premier sous-total)
  
• le deuxième sous-total ainsi que les valeurs qui le composent (soit deux fois le deuxième sous-total)
• ...
• le dernier sous-total avant la ligne courante ainsi que les valeurs qui le composent (soit deux fois le sous-total avant la ligne courante)
• et enfin des valeurs composant le sous-total qu'on cherche à calculer.

Donc qu'on somme :

• deux fois tous les sous-totaux avant le sous-total de la ligne courante
• et enfin des valeurs composant le sous-total qu'on cherche à calculer (notre but !)

Donc pour n'avoir que le dernier sous-total (celui qu'on recherche), il faut enlever du total de la colonne (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule) deux fois la somme des sous-totaux précédents (au-dessus de la ligne de la formule)

En espérant avoir été assez explicite

 

[mdo100]

Bonjour MLe,
Bonjour Ce lien n'existe plus

A tester sur le dernier fichier joint par Ce lien n'existe plus avec les mêmes résultats que la colonne "P".

Bonnes fêtes de fin d'années à toutes et tous.

Cordialement.

 

[mapomme]

Bonjour @mdo100

Je ne vois pas bien ce que votre formule a de radicalement différent de la formule en colonne P (à partir de P8) du fichier du post #2 ?

Joyeuses fêtes

 

[mdo100]

Re Ce lien n'existe plus

Je n'ais pas ouvert le fichier post #2 , je viens de le faire et effectivement il y a peu de différence, si ce n'ai que je commence la formule en "M7"

Milles excuses de ne pas avoir été plus curieux en n'ouvrant que le dernier fichier du post #5.

Bonnes Fêtes

 

[mapomme]

Re @mdo100,

No problemo . J'ai déjà constaté quelque fois la même chose alors que le différence était devant les yeux et m'avait echappée.

Cependant, si j'ai bien compris la question de Mle, Mle désirerait une formule sans utiliser la colonne M. C'était pour cela que j'ai pondu une formule matricielle (je suis allé à la facilité). Si tu as une formule non matricielle pour le faire, je suis preneur (je pense que c'est possible - et sans doute évident - mais pas le temps de m'y pencher pour l'instant)

 

[mdo100]

Re Ce lien n'existe plus

Sans utiliser la colonne "M" dans ce cas pourquoi pas:

En "P7"

=SI($B7="S_Total";SOMME($P$6:$P6)-2*SOMME.SI($B$6:$B6;"S_Total";$P$6:$P6);$C7)

Tirer vers le bas autant que nécessaire.

Cordialement.

 

[mapomme]

Re @mdo100,

En fait je me suis mal exprimé . Une formule dans la colonne P qui n'utilise pas la colonne P.

 

[MLe]

Bonjour Ce lien n'existe plus et mdo100,
Merciii pour vos réponses. En effet, il faut une formule qui n'utilise pas la colonne dans laquelle on l'encode.
La réponse de la formule donnera un nombre ?. Ce nombre doit être intégré dans la formule (0,8*1/(RACINE(?-1))) :
M14 =
? = C14 si B14 est différent de "S_Total
sinon
? = la somme de C11:C13 car B10 = S_Total
M14 = (0,8*1/(RACINE(?-1)))
Avec le fichier en pièce jointe, cela sera certainement plus clair.
Cela est peut-être impossible à résoudre !
Belle journée !

 

[mdo100]

Re MLe,

Je n'ais pas trouvé mieux que la formule matricielle de Ce lien n'existe plusce qui répond au post#11

Donc si j'ai compris et avec la formule de Ce lien n'existe plus adaptée à la colonne "M"

En "M7" ta formule

=(0,8*$A$1/(RACINE(C7-1)))

Et en "M8"

=SI(SI(B8="S_Total";SOMME(C$7:C8)-SIERREUR(SOMME(DECALER(C$7;0;0;MAX( (B$7:B7="S_Total")*(LIGNE(B$7:B7)-LIGNE(B$7)+1));1));0);C8)>1;(0,8*$A$1/(RACINE(SI(B8="S_Total";SOMME(C$7:C8)-SIERREUR(SOMME(DECALER(C$7;0;0;MAX( (B$7:B7="S_Total")*(LIGNE(B$7:B7)-LIGNE(B$7)+1));1));0);C8)-1)));1)

Formule matricielle a valider avec les touches ctrl + maj + entrée
Et tirer vers le bas autant que nécessaire.
Voir dans le fichier joint:

Cordialement.

 

[MLe]

Oh mon dieu ... Cela fonctionne C'est exactement cela. J'avoue ne pas comprendre la formule mais je vais essayer de passer quelques heures pour la digérer.
Merci à mdo100 et à Ce lien n'existe plus pour l'aide. Sans vous, je n'aurais certainement pas eu assez d'une vie pour trouver une solution. Merci à tous ces pros qui "Excel" dans les formules et qui donnent un peu de leur temps et leur savoir pour nous éviter de devenir fou à chercher pendant quelques nuits LA formule magique.
Belle fin d'année à vous et meilleurs vœux pour l'année 2019 !
Maxime

 

[MLe]

SOMME(M$7:M7) ==> On fait le total des valeurs de la colonne M (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule). Ce total peut se décomposer comme étant la somme de :

• des valeurs composant le premier sous-total
• du premier sous-total
• des valeurs composant le deuxième sous-total
• du deuxième sous-total
• ...
• ...
• des valeurs composant le dernier sous-total avant la ligne courant
• du dernier sous-total avant la ligne courante
• et enfin des valeurs composant le sous-total qu'on cherche à calculer.

On s’aperçoit qu'on somme :

• le premier sous-total ainsi que les valeurs qui le composent (soit deux fois le premier sous-total)
  
• le deuxième sous-total ainsi que les valeurs qui le composent (soit deux fois le deuxième sous-total)
• ...
• le dernier sous-total avant la ligne courante ainsi que les valeurs qui le composent (soit deux fois le sous-total avant la ligne courante)
• et enfin des valeurs composant le sous-total qu'on cherche à calculer.

Donc qu'on somme :

• deux fois tous les sous-totaux avant le sous-total de la ligne courante
• et enfin des valeurs composant le sous-total qu'on cherche à calculer (notre but !)

Donc pour n'avoir que le dernier sous-total (celui qu'on recherche), il faut enlever du total de la colonne (depuis le haut de la colonne jusqu'à la cellule juste au dessus de la cellule de la formule) deux fois la somme des sous-totaux précédents (au-dessus de la ligne de la formule)

En espérant avoir été assez explicite

Merci pour ces explications. C’est très clair et j’ai plaisir à comprendre. Je n’ai pas eu l’intelligence de remarquer que la somme de la colonne = deux fois le premier sous-total + deux fois le deuxième sous-total ... + le dernier sous-total (ce que l’on cherche)
Rrrr c’est frustrant de ne pas y avoir pensé ...mais il faut l’accepter.
Encore merci