Sumproduct conditionel

Le
rthompson
Bonjour à toutes et tous

Difficile à expliquer, mais je vais essayer

(Grâce à vous) j'ai des boutons (pas de l'urticaire, mais des déclencheur de
macros)
qui me cachent certaines lignes sous certaines conditions

Dans mes colonnes de M à S j'ai des quantités
Dans la colonne L j'ai des noms
Dans la colonne E j'ai des montants

Sur la ligne 3 j'ai des totaux conditionnels
=SUBTOTAL(109;M5:M350)
L'option 109 me donne le total des lignes affichées

Maintenant je voudrais avoir le montant total de chaque colonne des lignes
affichées
=SUMPRODUCT((Docsolution_Product=N4)*Docsolution_Amount)
Docsolution_Product est la colonne L
Docsolution_Amount est la colonne E
et sur la ligne 4 j'ai mes entêtes de colonne

Ceci fonctionne impeccablement

MAIS

Cela me donne toujours le montant total
Y a-t-il une option comme le 109 qui ferait uniquement le total des lignes
visibles?

Ou faut-il passer par une colonne supplémentaire pour les montant?

D'avance merci et à très bientôt

Rex
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 3
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
rthompson
Le #19132931
Bonjour et merci

MAIS

Je ne comprends pas trop bien

=SOMMEPROD((SOUS.TOTAL(109;
jusqu'ici ça va
après cela je ne comprend plus trop

je te mets mon fichier (loin d'être fini)

http://www.cijoint.fr/cjlink.php?file=cj200904/cijRH2BQ38.xls

Le Sumproduct est sur la feuille Document_Solution en cellule C2 et C3

Ce que je cherche à faire est ceci

Les trois boutons cachent les lignes suivant les dates en B et C 1
ou si c'est payé

Et je voudrais que le montant à payer en C2 corresponde à la période choisie
L'autre reste d'actualité (une facture en retard est une facture en retard)
Je doit me servir du motant à percevoire pour prévoire mon "Cash Flow"

J'espère que je suis suffasement clair pour que tu puisse m'aider

Rex




"MichDenis"
Bonjour RThompson,

En supposant que tu veux faire la somme des lignes visibles d'une plage
de cellule ayant comme nom "Plg" pour les valeurs >7

Il ne reste plus qu'à adapter ta condition selon ton application
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Plg>7))



"rthompson" groupe de discussion :
Bonjour à toutes et tous

Difficile à expliquer, mais je vais essayer

(Grâce à vous) j'ai des boutons (pas de l'urticaire, mais des déclencheur
de
macros)
qui me cachent certaines lignes sous certaines conditions

Dans mes colonnes de M à S j'ai des quantités
Dans la colonne L j'ai des noms
Dans la colonne E j'ai des montants

Sur la ligne 3 j'ai des totaux conditionnels
=SUBTOTAL(109;M5:M350)
L'option 109 me donne le total des lignes affichées

Maintenant je voudrais avoir le montant total de chaque colonne des lignes
affichées
=SUMPRODUCT((Docsolution_Product=N4)*Docsolution_Amount)
Docsolution_Product est la colonne L
Docsolution_Amount est la colonne E
et sur la ligne 4 j'ai mes entêtes de colonne

Ceci fonctionne impeccablement

MAIS

Cela me donne toujours le montant total
Y a-t-il une option comme le 109 qui ferait uniquement le total des lignes
visibles?

Ou faut-il passer par une colonne supplémentaire pour les montant?

D'avance merci et à très bientôt

Rex


MichDenis
Le #19132921
| Je ne comprends pas trop bien

Si c'était l'inverse tu ne poserais pas la question ...;-)

=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Plg>7))

Plg représente la plage de cellules de ta colonne que tu veux addtionner
c'est simplement une plage nommée.

La condition est représentée par Plg >7
En fait, j'aurais dû retenir une autre nom, car elle représente la colonne
dont tu veux poser une condition

La formule pourrait devenir :
Additionner la colonne Plg si la Colonne C >7 et si la colonne D = "toto"
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Colc>7)*(Cold="toto"))

ça vas-tu mieux ?

L'adaptation dans ton fichier, c'est ton affaire !
JB
Le #19132721
Bonjour,

http://boisgontierjacques.free.fr/pages_site/FiltreAuto.htm#SommeZoneFiltre e

JB
http://boisgontierjacques.free.fr


On 17 avr, 11:18, "rthompson" wrote:
Bonjour à toutes et tous

Difficile à expliquer, mais je vais essayer

(Grâce à vous) j'ai des boutons (pas de l'urticaire, mais des décle ncheur de
macros)
qui me cachent certaines lignes sous certaines conditions

Dans mes colonnes de M à S j'ai des quantités
Dans la colonne L j'ai des noms
Dans la colonne E j'ai des montants

Sur la ligne 3 j'ai des totaux conditionnels
    =SUBTOTAL(109;M5:M350)
          L'option 109 me donne le total des lignes affichées

Maintenant je voudrais avoir le montant total de chaque colonne des ligne s
affichées
    =SUMPRODUCT((Docsolution_Product=N4)*Docsolution_Amount)
          Docsolution_Product est la colonne L
          Docsolution_Amount est la colonne E
          et sur la ligne 4 j'ai mes entêtes de colonne

Ceci fonctionne impeccablement

MAIS

Cela me donne toujours le montant total
Y a-t-il une option comme le 109 qui ferait uniquement le total des ligne s
visibles?

Ou faut-il passer par une colonne supplémentaire pour les montant?

D'avance merci et à très bientôt

Rex


rthompson
Le #19132901
Bonjour et merci

Le fichier joint n'était pas pour que tu fasse mon boulot
mais plutôt pour mieux comprendre ma demande
J'ai jamais été trop costaud pour expliquer

Voici ou j'en suis maintenant
Cela réagit!! mais le montant n'est pas correcte
Donc je dois avoir mal placé un truc

=SUMPRODUCT((SUBTOTAL(109;Docsolution_Cash_Situation)*E1)*Docsolution_Amount)

Docsolution_Cash_Situation est la zone en colonne A qui
défini si le payement est fait, à faire ou en retard
En E1 la condition
Docsolution_Amount la colonne des montants

Et ceci me donne un chiffre de plus de 800.000 alors qu'il devrait être de
45.000

As-tu une idée de ce que je fais de mal

A bientôt et merci

Rex


"MichDenis" ON$
| Je ne comprends pas trop bien

Si c'était l'inverse tu ne poserais pas la question ...;-)

=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Plg>7))

Plg représente la plage de cellules de ta colonne que tu veux addtionner
c'est simplement une plage nommée.

La condition est représentée par Plg >7
En fait, j'aurais dû retenir une autre nom, car elle représente la colonne
dont tu veux poser une condition

La formule pourrait devenir :
Additionner la colonne Plg si la Colonne C >7 et si la colonne D = "toto"
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Colc>7)*(Cold="toto"))

ça vas-tu mieux ?

L'adaptation dans ton fichier, c'est ton affaire !


rthompson
Le #19133671
Bonjour JB et merci

Décidément je dois être nul ou fatigué

J'arrive à un résultat totalement faux
Comme quand j'utilise la solution de Michdenis

J'ai adapté ta proposition comme ceci

=SUMPRODUCT((SUBTOTAL(109;Docsolution_Amount))*(Docsolution_Cash_Situationá))

Docsolution_Cash_Situation
est la zone en colonne A qui défini si le payement
est fait, à faire ou en retard
Ceci est représenté par 0 1 ou 2
En E1
la condition j'y mets 0 1 ou 2
Docsolution_Amount
la colonne des montants


Je mets en E1 si je cherche les retartadaires 2

Et le résultat est totaalement faux

Ou est l'erreur?

Merci et à très bientôt

Rex

PS
Si ce n'est pas clair, j'ai mis le fichier joint dans ma réponse à Michdenis




"JB"
Bonjour,

http://boisgontierjacques.free.fr/pages_site/FiltreAuto.htm#SommeZoneFiltree

JB
http://boisgontierjacques.free.fr


On 17 avr, 11:18, "rthompson" wrote:
Bonjour à toutes et tous

Difficile à expliquer, mais je vais essayer

(Grâce à vous) j'ai des boutons (pas de l'urticaire, mais des déclencheur
de
macros)
qui me cachent certaines lignes sous certaines conditions

Dans mes colonnes de M à S j'ai des quantités
Dans la colonne L j'ai des noms
Dans la colonne E j'ai des montants

Sur la ligne 3 j'ai des totaux conditionnels
=SUBTOTAL(109;M5:M350)
L'option 109 me donne le total des lignes affichées

Maintenant je voudrais avoir le montant total de chaque colonne des lignes
affichées
=SUMPRODUCT((Docsolution_Product=N4)*Docsolution_Amount)
Docsolution_Product est la colonne L
Docsolution_Amount est la colonne E
et sur la ligne 4 j'ai mes entêtes de colonne

Ceci fonctionne impeccablement

MAIS

Cela me donne toujours le montant total
Y a-t-il une option comme le 109 qui ferait uniquement le total des lignes
visibles?

Ou faut-il passer par une colonne supplémentaire pour les montant?

D'avance merci et à très bientôt

Rex


rthompson
Le #19133661
SI SI SI

Dans ma fenêtre de formule les {} sont bien là
avant le = et après la dernière )


Donc je ne comprends pas

Quand je change le nombre de ligne affichée, il change de montant
donc il réagit comme il faut
mais le calcul est faussé


J'y comprends rien

Je dois m'absenter pendant trois heures maintenant
je regarde quand je reviens

A bientôt et surtout MERCI

Rex



"MichDenis"
"Sommeprod" est une formule matricielle dont on n'a pas à utiliser Ctrl +
Maj + Enter

Dans cette formule, il y a 3 condition :
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Colc>7)*(Cold="toto"))

Condition A : seulement les lignes visibles et c'est défini par :
SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))

Quand on parle de formule matricielle, on fait référence à un tableau
c'est pourquoi, les éléments dans la formule "Decaler" font référence
à un tableau

plg = chaque valeur de chaque ligne de la plage à additionner

LIGNE(Plg)-MIN(LIGNE(Plg)) = Donne un tableau qui fait référence
à chacune des lignes de la plage, la position des valeurs.

Pour comprendre, dans un fichier vierge, définis une petite plage nommée
inscrit la formule dans une cellule... à tour de rôle, dans la barre de
formule
sélectionne Plg et appuie sur la touche F9, Excel va remplacer Plg par les
valeurs contenue dans la plage. Tu fais la même chose après avoir
sélectionné
LIGNE(Plg)-MIN(LIGNE(Plg) ... ça aide à comprendre

Sous.Total(109...) son job, c'est de retenir dans la matrice seulement
les lignes visibles

Condition B - Colc >7
Une plage nommée Colc (même nombre de ligne que Plg) dont les valeurs sont
plus grandes que 7

Condition C = Cold = "toto"
Une plage nommée "toto" (même nombre de lignes que Plg et Colc dont les
valeurs sont égales à "toto"

Ceci étant, tu ne dois pas mettre tes conditions B ou C dans la section de
la formule "Sous.total" qui
est elle-même une condition de la fonction SommeProd() dans la donne du
problème.




"rthompson" groupe de discussion :
Bonjour et merci

Le fichier joint n'était pas pour que tu fasse mon boulot
mais plutôt pour mieux comprendre ma demande
J'ai jamais été trop costaud pour expliquer

Voici ou j'en suis maintenant
Cela réagit!! mais le montant n'est pas correcte
Donc je dois avoir mal placé un truc


=SUMPRODUCT((SUBTOTAL(109;Docsolution_Cash_Situation)*E1)*Docsolution_Amount)

Docsolution_Cash_Situation est la zone en colonne A qui
défini si le payement est fait, à faire ou en retard
En E1 la condition
Docsolution_Amount la colonne des montants

Et ceci me donne un chiffre de plus de 800.000 alors qu'il devrait être
de
45.000

As-tu une idée de ce que je fais de mal

A bientôt et merci

Rex


"MichDenis" ON$
| Je ne comprends pas trop bien

Si c'était l'inverse tu ne poserais pas la question ...;-)

=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Plg>7))

Plg représente la plage de cellules de ta colonne que tu veux addtionner
c'est simplement une plage nommée.

La condition est représentée par Plg >7
En fait, j'aurais dû retenir une autre nom, car elle représente la
colonne
dont tu veux poser une condition

La formule pourrait devenir :
Additionner la colonne Plg si la Colonne C >7 et si la colonne D = "toto"
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Colc>7)*(Cold="toto"))

ça vas-tu mieux ?

L'adaptation dans ton fichier, c'est ton affaire !




Jacquouille
Le #19134331
Salut Major

Décidément, c'est le climat belge qui bouche les neurones.

Perso, je ne comprends pas la présence d'un SOMMEPROD (SP, pour plus de
facilités) dans un tableau filtré, puisque tu utilises la fonction
SOUS.TOTAL.
Quant à ton option 109, elle m'est inconnue. J'en prends à témoin le livre
de John Walkenbach, Formules et fonction d'Excel 2000, page 240, où il
utilise l'indice 3, pour comptabiliser le nombre, soit le nombre de lignes
affichées.
Le filtre masque les lignes dont la cel ne correspond pas à la condition
émise dans sa colonne. Pourquoi dès lors vouloir utiliser SP qui, lui aussi,
répond à une (ou +) condition?

Un peu de soleil dans ce ciel maussade?

--
Bien amicalmement,
"Le vin est au repas ce que le parfum est à la femme."

Jacquouille.

"rthompson" news: Oi2bg%
Bonjour JB et merci

Décidément je dois être nul ou fatigué

J'arrive à un résultat totalement faux
Comme quand j'utilise la solution de Michdenis

J'ai adapté ta proposition comme ceci


=SUMPRODUCT((SUBTOTAL(109;Docsolution_Amount))*(Docsolution_Cash_Situationá))

Docsolution_Cash_Situation
est la zone en colonne A qui défini si le payement
est fait, à faire ou en retard
Ceci est représenté par 0 1 ou 2
En E1
la condition j'y mets 0 1 ou 2
Docsolution_Amount
la colonne des montants


Je mets en E1 si je cherche les retartadaires 2

Et le résultat est totaalement faux

Ou est l'erreur?

Merci et à très bientôt

Rex

PS
Si ce n'est pas clair, j'ai mis le fichier joint dans ma réponse à
Michdenis




"JB"
Bonjour,

http://boisgontierjacques.free.fr/pages_site/FiltreAuto.htm#SommeZoneFiltree

JB
http://boisgontierjacques.free.fr


On 17 avr, 11:18, "rthompson" wrote:
Bonjour à toutes et tous

Difficile à expliquer, mais je vais essayer

(Grâce à vous) j'ai des boutons (pas de l'urticaire, mais des déclencheur
de
macros)
qui me cachent certaines lignes sous certaines conditions

Dans mes colonnes de M à S j'ai des quantités
Dans la colonne L j'ai des noms
Dans la colonne E j'ai des montants

Sur la ligne 3 j'ai des totaux conditionnels
=SUBTOTAL(109;M5:M350)
L'option 109 me donne le total des lignes affichées

Maintenant je voudrais avoir le montant total de chaque colonne des
lignes
affichées
=SUMPRODUCT((Docsolution_Product=N4)*Docsolution_Amount)
Docsolution_Product est la colonne L
Docsolution_Amount est la colonne E
et sur la ligne 4 j'ai mes entêtes de colonne

Ceci fonctionne impeccablement

MAIS

Cela me donne toujours le montant total
Y a-t-il une option comme le 109 qui ferait uniquement le total des
lignes
visibles?

Ou faut-il passer par une colonne supplémentaire pour les montant?

D'avance merci et à très bientôt

Rex






rthompson
Le #19134311
Bonjour et MERCI

Cela fonctionne

Mais (et si tu as le temps) maintenant j'aimerais comprendre certain trucs

SUMPRODUCT
((SUBTOTAL
(109;
Jusqu'ici ça va

Mais pourquoi dois-je utiliser Offset alors que j'utilise un nom dynamique

OFFSET(Docsolution_Amount;

Et à quoi sert Row (puisqu'il ne doit analyser qu'une colonne?

ROW(Docsolution_Amount)-

A quoi sert le MIN?

MIN(ROW(Docsolution_Amount)

Et pourquoi quand je réfère à une cellule E2 et pas au chiffre 2 il bloque?

);;1)))*(Docsolution_Cash_Situation=2))


Mais quoiqu'il en soit ceci fonctionne

Donc un tout grand merci à toi pour ta gentillesse et ta patience

Rex








"MichDenis"
Un exemple à partir de tes données :

=SOMMEPROD((SOUS.TOTAL(109;DECALER(E5:E15;LIGNE(E5:E15)-MIN(LIGNE(E5:E15));;1)))*(A5:A15=2))

et cela fonctionne très bien !



"rthompson" groupe de discussion :
Bonjour et merci

Le fichier joint n'était pas pour que tu fasse mon boulot
mais plutôt pour mieux comprendre ma demande
J'ai jamais été trop costaud pour expliquer

Voici ou j'en suis maintenant
Cela réagit!! mais le montant n'est pas correcte
Donc je dois avoir mal placé un truc


=SUMPRODUCT((SUBTOTAL(109;Docsolution_Cash_Situation)*E1)*Docsolution_Amount)

Docsolution_Cash_Situation est la zone en colonne A qui
défini si le payement est fait, à faire ou en retard
En E1 la condition
Docsolution_Amount la colonne des montants

Et ceci me donne un chiffre de plus de 800.000 alors qu'il devrait être
de
45.000

As-tu une idée de ce que je fais de mal

A bientôt et merci

Rex


"MichDenis" ON$
| Je ne comprends pas trop bien

Si c'était l'inverse tu ne poserais pas la question ...;-)

=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Plg>7))

Plg représente la plage de cellules de ta colonne que tu veux addtionner
c'est simplement une plage nommée.

La condition est représentée par Plg >7
En fait, j'aurais dû retenir une autre nom, car elle représente la
colonne
dont tu veux poser une condition

La formule pourrait devenir :
Additionner la colonne Plg si la Colonne C >7 et si la colonne D = "toto"
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Colc>7)*(Cold="toto"))

ça vas-tu mieux ?

L'adaptation dans ton fichier, c'est ton affaire !




Jacquouille
Le #19134301
Bonjour Denis
Je ne connais pas l'argument 109 pour la fonction Sous.Total.
Ce 109, quel goût ça a et ça se mange comment? -)
Merci et bon WE
Jacques

--
Bien amicalmement,
"Le vin est au repas ce que le parfum est à la femme."

Jacquouille.

"MichDenis"
Un exemple à partir de tes données :

=SOMMEPROD((SOUS.TOTAL(109;DECALER(E5:E15;LIGNE(E5:E15)-MIN(LIGNE(E5:E15));;1)))*(A5:A15=2))

et cela fonctionne très bien !



"rthompson" groupe de discussion :
Bonjour et merci

Le fichier joint n'était pas pour que tu fasse mon boulot
mais plutôt pour mieux comprendre ma demande
J'ai jamais été trop costaud pour expliquer

Voici ou j'en suis maintenant
Cela réagit!! mais le montant n'est pas correcte
Donc je dois avoir mal placé un truc


=SUMPRODUCT((SUBTOTAL(109;Docsolution_Cash_Situation)*E1)*Docsolution_Amount)

Docsolution_Cash_Situation est la zone en colonne A qui
défini si le payement est fait, à faire ou en retard
En E1 la condition
Docsolution_Amount la colonne des montants

Et ceci me donne un chiffre de plus de 800.000 alors qu'il devrait être
de
45.000

As-tu une idée de ce que je fais de mal

A bientôt et merci

Rex


"MichDenis" ON$
| Je ne comprends pas trop bien

Si c'était l'inverse tu ne poserais pas la question ...;-)

=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Plg>7))

Plg représente la plage de cellules de ta colonne que tu veux addtionner
c'est simplement une plage nommée.

La condition est représentée par Plg >7
En fait, j'aurais dû retenir une autre nom, car elle représente la
colonne
dont tu veux poser une condition

La formule pourrait devenir :
Additionner la colonne Plg si la Colonne C >7 et si la colonne D = "toto"
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Plg;LIGNE(Plg)-MIN(LIGNE(Plg));;1)))*(Colc>7)*(Cold="toto"))

ça vas-tu mieux ?

L'adaptation dans ton fichier, c'est ton affaire !




isabelle
Le #19134761
salutatous,

je dirais plutot à partir de la version xl2003,

isabelle

MichDenis a écrit :
Bonjour,

Je crois que c'est à partir de la version 2002, on a ajouté
les mêmes fonctions à Sous.Total() mais pour des lignes
masquées sans que ces lignes masquées le soient à la
suite d'un filtre.

à chacune des fonctions que tu connais déjà, tu ajoutes 100
D'ou Sous.Total(109,Plage)
9 = additionner plage filtrée
109 = addtionner plage dont les lignes sont visibles (sans usage de filtre)





"Jacquouille" Salut Major

Décidément, c'est le climat belge qui bouche les neurones.

Perso, je ne comprends pas la présence d'un SOMMEPROD (SP, pour plus de
facilités) dans un tableau filtré, puisque tu utilises la fonction
SOUS.TOTAL.
Quant à ton option 109, elle m'est inconnue. J'en prends à témoin le livre
de John Walkenbach, Formules et fonction d'Excel 2000, page 240, où il
utilise l'indice 3, pour comptabiliser le nombre, soit le nombre de lignes
affichées.
Le filtre masque les lignes dont la cel ne correspond pas à la condition
émise dans sa colonne. Pourquoi dès lors vouloir utiliser SP qui, lui aussi,
répond à une (ou +) condition?

Un peu de soleil dans ce ciel maussade?




Publicité
Poster une réponse
Anonyme