SOMMEPROD - SOUS.TOTAL - DECALER

Bonjour,

Je veux faire une somme de mes valeurs en fonction d'un critère tout en considérant mon filtre (une sorte de sous.total + somme.si). Je ne veux pas passer par un tableau croisé dynamique. J'utilise la formule suivante =SOMMEPROD((SOUS.TOTAL(9;DECALER(E$7;LIGNE($7:$8999)-1;)))*($B$7:$P$8999=$B$1930)).

Ça semble fonctionner super bien cependant je n'ai pas toujours le bon résultat. la formule ne semble pas tenir compte de toutes mes lignes filtrées qui ont la valeur indiquée.

Voir fichier ci-joint. Mes formules se trouvent en D3, E3, F3 et G3. Le résultat de E3 devrait être 30 946.91 et non pas 29 785. J'ai l'impression que les lignes 1902, 1904 et 1904 n'ont pas été calculées par ma formule.

Pouvez-vous m'aider SVP!!!

Merci!

Bonjour,

si tu expliquais ce que tu veux calculer avec des mots plutôt qu'avec une formule qui marche super bien mais qui n'envoie jamais le bon résultat...

Je t'avoue que *($B$7:$P$8999=$B$1930) dans ton sommeprod me perturbe un peu.

Déjà pour $B$1930 tu pourrais mettre une cellule visible qu'on sache que c'est 03/06/2017.

Mais on se demande pourquoi tester cette date puisque soustotal(9,... ne tient compte que des valeurs affichées et tu as filtré sur cette date (?!?)

Quant à $B$7:$P$8999=, les 15 colonnes me laissent plus que dubitatif...

Avec tes explications on saura peut-être pourquoi tu veux 30 946.91 alors que les 10 cellules visible de E donnent 33946.91 (re ?!?)

Là, personnellement, je n'arrive pas à deviner le besoin exact.

eric

Bonjour Éric,

En effet, tu as raison d'être duplicatif quant aux 15 colonnes, c'est une erreur qui s'est glissée à force de chercher pourquoi ma formule ne fonctionnait pas. Et effectivement, le résultat devrait donner 33946.91.

En fait je cherche à trouver une solution pour visualiser rapidement les volumes qui ont été livrés à la date du 03/06/2017. Je veux donc isoler cette date des autres dates (en D3, E3, F3, G3..). Cependant je souhaite que la personne qui utilisera le fichier puisse par exemple sélectionner un secteur ex "Ga REBEC" et obtenir en D3 les volumes qui ont été livrés à la date du 03/06/2017 et en D5 les volumes que nous prévoyons livrés en 2017.

Je ne sais pas si mon explication va vous aider à mieux me comprendre.

En vous remerciant à l'avance.

Salutations,

et pourquoi ne pas utiliser les TCD ?

Bonjour,

avec un TCD comme proposé par Steelson.

Le segment Secteur agit sur les 2 TCD, le segment Date n'agit que sur celui de droite.

eric

Je ne veux pas utiliser le TCD pour 2 raisons:

1-Je ne souhaite pas rendre mon fichier Excel plus lourd;

2-Je ne souhaite pas avoir besoin de constamment actualiser mon TCD ou vérifier si ma source de donnée est correcte. Je veux rendre ça le plus simple possible car au final, ça ne sera pas moi qui va travailler avec le fichier.

Mais s'il n'y a pas d'autre solution je vais devoir considérer celle-ci.

Merci beaucoup!!!

Tu n'as pas de fonction qui permette de savoir quels filtres ont étés choisis par l'utilisateur pour éventuellement s'en servir dans une formule.

A faire en vba mais perso, là je n'ai pas le temps.

eric

JMOR a écrit :

Je ne veux pas utiliser le TCD pour 2 raisons:

1-Je ne souhaite pas rendre mon fichier Excel plus lourd;

2-Je ne souhaite pas avoir besoin de constamment actualiser mon TCD ou vérifier si ma source de donnée est correcte. Je veux rendre ça le plus simple possible car au final, ça ne sera pas moi qui va travailler avec le fichier.

Tu peux aussi actualiser par VBA lors de la sélection de l'onglet ! => donc automatique (comme une macro du reste !)

La source c'est pas un problème si les données sont en tableau.

Bonjour,

C'est exactement ce que j'ai fait (deux TCD) avec un bouton pour actualiser mes TCD. Mon autre problème c'est que j'ai mis une protection sur certaines cellules de ma feuille. Dans ma macro, je fais donc : enlever ma protection, actualiser mes TCD et remettre ma protection. Cependant, ça semble créer un bug. Lorsque j'enregistre mon excel, j'ai le message suivant: "Actualiser les données externes d’un classeur".

Je ne comprends pas pourquoi?

JMOR a écrit :

Cependant, ça semble créer un bug. Lorsque j'enregistre mon excel, j'ai le message suivant: "Actualiser les données externes d’un classeur".

Pour moi ce n'est pas lié au TCD. Mais aux données elles-mêmes.

Bonjour Steelson,

Effectivement, j'ai retravaillé certaines choses et je n'ai plus le message d'erreur. Ma macro fonctionne donc très bien pour actualiser mes TCD. Merci beaucoup pour les conseils. Je vais y aller comme ça.

Je reste cependant songeuse vis-à-vis la formule que j'avais utilisée au départ.

SOMMEPROD((SOUS.TOTAL(9;DECALER(E$7;LIGNE($7:$8999)-1;)))*($B$7:$P$8999=$B$1930)).

Est-ce que ce genre de formule fonctionne généralement bien?

Merci encore!!!

Non ta formule ne fonctionne pas ...

Ce n'est pas une erreur en soi, mais pourquoi P et pourquoi 8999 puisque tu as un tableau BD ?

$B$7:$P$8999=$B$1930

DECALER(E$7;LIGNE($7:$8999)-1;)

Ce n'est qu'un seul terme ... égal à 0 ! tu décale E7 de 7-1 lignes (LIGNE étant au singulier !!! il ignore 8999)

Et si tu mets LIGNES, cela fait 8999-7 +1 -1 = 8992, égal aussi à 0 !

(SOUS.TOTAL(9;DECALER(E$7;LIGNE($7:$8999)-1;))

revient à faire une somme d'une valeur nulle

SOMMEPROD((SOUS.TOTAL(9____________

mariage contre nature

Bonjour,

Il y a longtemps, j'ai noté cette formule qui associe SOMMEPROD, SOUS.TOTAL et le mode filtre. Elle est inspirée d'une formule de Laurent Longre. Ça pourrait peut-être t'être utile.

En L2, j'ai mis la date choisie.

=SOMMEPROD((SOUS.TOTAL(3;DECALER(D8;LIGNE(A$8:A$1944)-LIGNE(D8);))*($B$8:$B$1944=$L$2))*D$8:D$1944)

A+

Je ne comprends pas car

=LIGNE(A$8:A$1944)-LIGNE(D8)

est égal à 0

Quel intérêt ?

Bonjour,

On est bien d'accord LIGNE(A$8:A$1944)-LIGNE(D8)= 0 mais grâce à la fonction matricielle sommeprod ainsi qu'à DECALER, chaque cellule est analysée de manière individuelle et là ça change tout.

Un exemple sur une plus petite plage

DECALER(A3;ligne(A3:A6)-ligne(A3);)

En matriciel, cela donne

DECALER(A3;{3;4;5;6}-{3};)

Ce qui revient à

DECALER(A3;3-3;4-3;5-3;6-3;)

et donc à

DECALER(A3;{0;1;2;3};)

Ce qui permet d'évaluer chaque ligne.

Après sous.total fait le tri et au final, seules les cellules filtrées sont prises en compte.

Perso, je trouve ça génial.

A+

Merci beaucoup Shakki

Là j'ai appris quelque chose !

Et je retire ce que j'ai dit plus haut par ignorance.

Bonjour,

d'accord et merci à shakki aussi

Si ce n'est que tu as un ; qui s'est malencontreusement inséré devant -Ligne(A3)

Si tu pouvais corriger que les futurs lecteurs se prennent moins la tête

eric

Hou là là tu as raison ! J'ai corrigé.

Merci pour ta vigilance.

Un gros merci pour les explications!!

Ça m'aide à comprendre.

JMOR a écrit :

J'utilise la formule suivante

=SOMMEPROD((SOUS.TOTAL(9;DECALER(E$7;LIGNE($7:$8999)-1;)))*($B$7:$P$8999=$B$1930)).

Ça semble fonctionner super bien cependant je n'ai pas toujours le bon résultat. la formule ne semble pas tenir compte de toutes mes lignes filtrées qui ont la valeur indiquée.

Essaie ceci :

=SOMMEPROD((SOUS.TOTAL(9;DECALER(E$7;LIGNE(A$7:A$1945)-LIGNE(A$7);)))*($B$7:$B$1945=$B$1930))

Le résultat que tu attends n'est pas le bon !