Discussion :

[pastis.vi]

Bonjour à tous !

J'ai trouvé plein de discussions déjà ouvertes sur le sujet mais aucune ne répond à la complexité de mon problème.

J'ai deux colonnes avec des chiffres. Je souhaiterai multiplier, sur chaque ligne, la cellule de la colonne A et celle de la colonne B puis additionner ces multiplications. Sachant que j'ai un filtre sur une 3ieme colonne.

Voir l'exemple dans le fichier joint.

Merci beaucoup de votre aide !

[Nako_lito]

par ce que tu n'as rien a faire de plus que ton =sommeprod

pas besoin de faire des multiplication des colonnes, note le comme ça : =sommeProd(B3:B91;C3:C91).

Ensuite, si tu veux le temps en heure, tu fais =SOMMEPROD(B3:B91;C3:C91) / 60

[pastis.vi]

Bonjour Nako_Lito,

Je me suis mal exprimé. Le problème qui se pose c'est que je souhaite que le résultat soit adapté en fonction de mon filtre... donc un sommeprod tout simple ne fonctionne pas.
Un sous.total est forcément nécessaire mais je ne sais pas comment écrire la formule.

Merci

[Nako_lito]

Mets ça dans la cellule E2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(SOUS.TOTAL(102;B3:B91);SOUS.TOTAL(102;C3:C91))

[Nako_lito]

La fonction sous totale prend pour premier paramètre qqc qu'ils appèllent "No. Fonction", terme barbare pour dire le type de donnée qu'il prend en compte ou non dans son calcul.
Dans mon exemple, j'ai mis 102, qui correspond à : "Récupère la valeur numérique et ignore les valeurs masquées".

Les autre paramètre désigne quelle plage de cellule doit couvrir la fonction. Ici, du B3 au B91. Tu fais une seconde fois sur la colonne C et tu mets le tout dans la méthode sommeprod et excel s'occupe du reste.

Tu devais sans doute galérer avec les paramètre et les parenthèses.

[pastis.vi]

Rebonjour Nako,

La formule que tu proposes ne donne pas le bon résultat. Je pense que ça vient du fait qu'Excel prend en compte le nombre de cellules non vides dans la plage de valeurs sélectionnée en ce qui concerne le No. Fonction 102 ...

Je ne vois pas quel paramètre pourrait fonctionner avec une formule de ce type

[Nako_lito]

J'ai mis ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOUS.TOTAL(9;B3:B22) * SOUS.TOTAL(9;C3:C22)

et ça a l'air de fonctionner

[pastis.vi]

Ça donne toujours un mauvais résultat...

Mais à mon avis c'est pas possible de faire ça tout simplement. Je pense qu'il faut passer par un DECALER mais je ne sais absolument pas comment utiliser cette fonction..

[Nako_lito]

ca fonctionnait pour moi. Je ne vois pas ce que je peux faire de plus.

Pour la fonction décaler, reporte toi à l'aide d'excel: ici
En gros, par rapport a la cellule ou se trouve la fonction, tu fais référence à une autre cellule par rapport a ses coordonnées.

Si tu fais un =DECALER(C3;2;3;1;1), tu fais référence à la cellule F5, tu te décale de 2 vers la droite et 3 vers le bas et tu prend les cellules sur 1 de haut et 1 de droite, donc uniquement F5. Si tu avais mis 2 dans les deux derniers arguments, tu aurais pris les 4 cellules adjacentes de F5, donc la plage F5:J9.

[pastis.vi]

Tu arrivais à avoir 161,6833 (valeur affichée en F1 de mon fichier exemple), sans filtre, avec ta formule? Parce que si t'avais un résultat différent c'est que la formule n'est pas correcte

Merci pour les infos sur la fonction DECALER !

[Nako_lito]

passe par une macro sinon, ça prend pas beaucoup plus de temps a faire qu'a chercher une solution

[Nako_lito]

Nomme deux plage : colonneB pour B3:B91 et colonneC : C3:C91

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((SOUS.TOTAL(109;DECALER(colonneB;LIGNE(colonneB)-MIN(LIGNE(colonneB));;1)))*(colonneC)) / 60

sans filtre tu obtiens 161.683333 et quand tu filtre, ton résultat change pour ne prendre que les lignes affichées.

[pastis.vi]

C'est parfait ! Merci Nako !!

Je voulais éviter d'avoir à passer par une macro pour mon fichier; et ta formule me convient très bien !

[olra72]

Bonjour,

J'ai un tableau (Tableau1) avec une colonne "Num" et d'autres colonnes qui me servent à filtrer (ex. Etat, Date).

J'ajoute en fin de tableau une colonne "s109" avec dedans la formule "=SOUS.TOTAL(109;[@Num])".

Cette cellule contient donc exactement la même chose que la colonne "Num" si la ligne est visible, 0 si la ligne est filtrée ... ou masquée pour quelque raison que ce soit.

Ensuite, au lieu de faire des "SOMMEPROD(Tableau1[Num]*(Tableau1[Etat]="En Cours"))", il suffit de faire des "SOMMEPROD(Tableau1[s109]*(Tableau1[Etat]="En Cours"))", etc.

Pas bête, hein ?

... sauf qu'il faut compter avec les bizarreries MicroSoft : si la colonne "s109" fait partie du même tableau (ie. Tableau1), cela met le bouzin dans la gestion des filtres (essayez, vous verrez !). Apparemment, il suffit de créer plus loin un second tableau séparé sur les mêmes lignes (ie. Tableau2) avec la formule "=SOUS.TOTAL(109;Tableau1[@Num])" et, là, les filtres fonctionnent encore et le SOMMEPROD aussi.

La persévérance, il n'y a que ça !

Amicalement,
Olivier

PS: bizarrerie restante, essayez une "ligne des totaux" dans Tableau2, vous serez surpris ! Par contre, un SOMME tout à fait classique fonctionne