Microsoft 365 SOMME PROD de cellule filtré avec que les valeurs uniques

[bibbip35]

Bonsoir à tous

Je cherche une formule pour réaliser une sommeprod combiné a la fonction sous-total mais qui prend que les valeurs uniques après filtre
Je ne suis pas sur finalement que ca soit réalisable mais je tente ma chance sur le forum !!

Dans le fichier ci-joint , je cherche a faire
la somme de la colonne E
Qui a pour critères :
- Valeur colonne B > ou égale à 1
- Valeur colonne F > à 0 et inférieur à 1

Je souhaite que cette formule ne prennent en compte que les valeurs unique selon les critères ci-dessus de la colonne C

J'ai bien reussi a faire une formule qui fonctionne très bien avec les filtres , qui prend en compte les critères et la fonction sous total
Mais la ou ca se complique c'est la notion de valeurs unique combiné aux critères précédent

Merci encore pour votre Aide !!!

Bibbip35

 

[Oliver77]

Bonjour,

Voici un partie de ta formule :
...;--(Programme_Expédition__2[Niv.]>1)*(Programme_Expédition__2[Cloture_OF.Magasin]>0);....

Tu as 2 fois le signe "-" après le 1er point-virgule (";").
Est-ce volontaire ? J'ai vérifié, le résultat n'est pas le même avec un seul '-'...

La valeur unique ??? Dans quelle colonne ?
Peux-tu nous donner un exemple ?
Ou alors coloriser les lignes prises qui donnent 10,4999..


Bon continuation
Oliver77

 

[chris]

Bonjour

Dans la mesure où le tableau est le résultat d'une requête PowerQuery, il serait sans doute plus simple de modifier ou compléter la requête pour ce faire...

 

[bibbip35]

Bonjour à tous

@ Olivier77 , les valeurs unique sont de la colonne C

@ Chris oui le fichier viens bien de powerquery mais peux être filtres après exécution de la requête par l'utilisateur ... je ne vois pas du coup ce power query pourrait m'apporter ?


En fait ce qui me manque principalement je pense est de pouvoir identifier les doublons après filtre ?

Je pense...

Merci

 

[AtTheOne]

Bonjour à tous Bonjour @bibbip35,

Comme tu es sur EXCEL365, la fonction UNIQUE vas te rendre service :

=UNIQUE(Programme_Expédition__2[Ordre de fab];;FAUX)

Placée en H5 elle va te renvoyer directement une liste d'Ordre de Fab unique.
A coté, en G5, tu places la formule SOMME.SI.ENS suivante :

=SOMME.SI.ENS(Programme_Expédition__2[Magasin];
Programme_Expédition__2[Ordre de fab];H5#;
Programme_Expédition__2[Niv.];">=1";
Programme_Expédition__2[Cloture_OF.Magasin];"<1";
Programme_Expédition__2[Cloture_OF.Magasin];">0")

Elle va te renvoyer pour chaque ordre de fab la somme Magasin répondant à tes critères.
>>> Remarque le H5# qui renvoie toute la plage occupée par la formule UNIQUE...

Si tu n'as pas besoin des Ordres de Fab (mais ça m'étonnerait) tu peux avoir la liste de tes sommes directement avec (voir en L5)

=LET(Unq;UNIQUE(Programme_Expédition__2[Ordre de fab];;FAUX) ; SOMME.SI.ENS(Programme_Expédition__2[Magasin];Programme_Expédition__2[Ordre de fab];Unq; Programme_Expédition__2[Niv.];">=1"; Programme_Expédition__2[Cloture_OF.Magasin];">0"; Programme_Expédition__2[Cloture_OF.Magasin];"<1"))

L'avantage des fonctions matricielles dynamiques c'est qu'on les tape dans une seule cellule et qu'elles se développent toutes seules, on peux ensuite faire référence à la plage qu'elles occupe en ajoutant un # juste après l'adresse (cf H5# plus haut)

Voir le fichier joint

Amicalement
Alain

 

[bibbip35]

Bonjour Alain

Merci pour ton retour
Mais je n'arrive pas bien a comprendre le fichier que tu m'a envoyé
de plus çà n’élimine pas sauf erreur de ma part les valeurs en double
et je ne vois pas de variation après le filtre

Merci encore pour votre aide !
Bibbip35

 

[bibbip35]

Bonjour à tous

J'ai trouvé cette formule qui pourrait répondre à mon besoin sauf que j'aurrais vous lu quel travaille sur les doublon de la colonne c

=SOMME(SI(1*(FREQUENCE(SI(SOUS.TOTAL(9;DECALER(E5;LIGNE(E5:E3524)-LIGNE(E5);;1));EQUIV(E5:E3524;E5:E3524;0));LIGNE(INDIRECT("1:"&LIGNES(E5:E3524))))>0);E5:E3524))

Aurriez-vous peut-être une idée ?

Bibbip35

 

[AtTheOne]

Bonjour à tous, bonjour @bibbip35

1ère question as-tu vraiment Microsoft 365 ?

2ième question cherches-tu à avoir :

• une liste sans doublon de tous les "Ordre de fab" ?
• une liste des "Ordre de fab" qui n'apparaissent qu'une seule fois dans ta liste ?

Si tu as bien MS365

pour le 1er cas (liste sans doublon) utilise la fonction :​

=UNIQUE(Programme_Expédition__2[Ordre de fab];;FAUX)​

Pour le 2ème cas utilise​

=UNIQUE(Programme_Expédition__2[Ordre de fab];;VRAI)​

Cette fonction saisie en H5, ou en K5 de renverra selon le cas la liste désirée. Tu la saisie une seule fois, elle s'étend d'elle-même sur la plage nécessaire.

Maintenant pour le SOMME.SI.ENS
H5# et K5# faisant référence à la plage occupée par les 2 formules précédentes.

Pour la liste sans doublon :
=SOMME.SI.ENS(Programme_Expédition__2[Magasin];
Programme_Expédition__2[Ordre de fab];H5#;
Programme_Expédition__2[Niv.];">=1";
Programme_Expédition__2[Cloture_OF.Magasin];"<1";
Programme_Expédition__2[Cloture_OF.Magasin];">0")

Fait la somme de la colonne Magasin si l'ensemble des 4 conditions suivante sont réunies en même temps :

1. Ordre de Fab = la ligne correspondante dans la plage H5#
2. Niv. >= 1
3. Cloture_OF.Magasin <1
4. Cloture_OF.Magasin >0

Pour la liste des "Ordre de fab" n'apparaissant qu'une seule fois :
=SOMME.SI.ENS(Programme_Expédition__2[Magasin];
Programme_Expédition__2[Ordre de fab];K5#;
Programme_Expédition__2[Niv.];">=1";
Programme_Expédition__2[Cloture_OF.Magasin];"<1";
Programme_Expédition__2[Cloture_OF.Magasin];">0")

Fait la somme de la colonne Magasin si l'ensemble des 4 conditions suivante sont réuniesen même temps :

1. Ordre de Fab = la ligne correspondante dans la plage K5#
2. Niv. >= 1
3. Cloture_OF.Magasin <1
4. Cloture_OF.Magasin >0

(Les formules qui donnent le résultat directement sont anecdotiques)

Regarde si ça te convient
Amicalement
Alain

 

[AtTheOne]

Bonjour à tous

J'ai trouvé cette formule qui pourrait répondre à mon besoin sauf que j'aurrais vous lu quel travaille sur les doublon de la colonne c

=SOMME(SI(1*(FREQUENCE(SI(SOUS.TOTAL(9;DECALER(E5;LIGNE(E5:E3524)-LIGNE(E5);;1));EQUIV(E5:E3524;E5:E3524;0));LIGNE(INDIRECT("1:"&LIGNES(E5:E3524))))>0);E5:E3524))

Aurriez-vous peut-être une idée ?

Bibbip35

Re-Bonjour, nos posts se sont croisés ..

Cherches-tu a faire la somme globale de la colonne magasin pour tous les "Ordre de Fab" qui répondent à tes critères ou à avoir cette somme pour chaque 'Ordre de Fab" qui répond à tes critères ?

A bientôt
Alain

 

[bibbip35]

Excuse moi Alain je pense que je n'ai pas été très clair
C'est bien la somme que je cherche a obtenir

Sinon je te confirme que j’ai bien OFFICE365

 

[AtTheOne]

Re-Bonjour
Si tu veux la somme pour les "Ordre de Fab" qui n'apparaissent qu'une seule fois essaye cette formule :

=SOMMEPROD(Programme_Expédition__2[Magasin];
--(NB.SI(Programme_Expédition__2[Ordre de fab];Programme_Expédition__2[Ordre de fab])=1);
--(Programme_Expédition__2[Niv.]>=1);
--(Programme_Expédition__2[Cloture_OF.Magasin]>0);
--(Programme_Expédition__2[Cloture_OF.Magasin]<1))

(les -- sont là pour convertir les valeurs logiques VRAI, FAUX en nombre 1, 0

Sinon tu peux la saisir aussi de cette façon

=SOMMEPROD(Programme_Expédition__2[Magasin]*
(NB.SI(Programme_Expédition__2[Ordre de fab];Programme_Expédition__2[Ordre de fab])=1)*
(Programme_Expédition__2[Niv.]>=1)*
(Programme_Expédition__2[Cloture_OF.Magasin]>0)*
(Programme_Expédition__2[Cloture_OF.Magasin]<1))

(Là la conversion se fait via les multiplications)
Chaque sous-calcul des conditions renvoie un tableau de VRAI et de FAUX converti en 1 ou 0
La colonne Magasin renvoie les valeurs à cumuler.
La multiplication renvoie un tableau des valeurs de Magasin quand toutes les conditions sont vraies et de 0 si au moins une des conditions est fausse.
C'est la somme de ces valeurs que renvoie SOMMEPROD.

Voir fichier joint
Amicalement
Alain

 

[bibbip35]

Bonjour Alain

Désolé pour mon retour tardif.

C'est impeccable votre proposition ... Ca marche parfaitement !!!

Merci encore

Bibbip35