Feb 02 2023 07:08 AM
Bonjour
Je suis sur un tableau fournisseur.
Je souhaiterais ajouter une colonne ou dedans il y aurait deux choix : "oui" et "non".
Le "oui" serait Si un fournisseur (colonne C) intervient dans plusieurs catégories (colonne E), c'est à dire 3 minimum.
Si le fournisseur intervient dans moins de 3 catégories, alors un "non".
J'ai l'impression que les formules "SI" ne se font que si on cherche des Chiffres et dans mon cas, je cherche à mettre des conditions sur le nombre des différentes catégories par fournisseur.
Auriez vous une idée pour m'aider svp ? Merci
Feb 02 2023 08:26 AM
COUNTIFS works for text fields.
= MAP(Supplier, LAMBDA(s,
COUNTA(UNIQUE(FILTER(Category, Supplier=s)))
))
Counts the number of categories that are listed for each supplier. Using a Lambda function, this could be expressed
= MAP(Supplier, CategoryCountλ)
Putting this together and one gets
= IF(MAP(Supplier, CategoryCountλ)>=3, "Oui", "Non")
Feb 02 2023 08:37 AM
365 seulement
Feb 03 2023 01:00 AM
The Excel is like that so when i write the formula; Neither the Supplier; neither Oui or Non works
Feb 03 2023 01:48 AM
Hopefully this is a step in the right direction.
= IF(MAP(Fournisseur, CategoryCountλ)>=3, "Oui", "Non")
CategoryCountλ
= COUNTA(UNIQUE(FILTER(Category, Fournisseur = s)))
Feb 03 2023 05:49 AM
Thank you so much because i understood your procedure, and i tried to apply it to my work,
For C qnd G columns i used =UNIQUE(B11:B119871) and =UNIQUE(F11:F119871)
For D qnd H columns =NBVAL(UNIQUE(B11:B119871))
COUNTA in french is NBVAL.
So now for the CategoryCountλ i got an error :
Excel does'nt recognize the "categories" word i guess ....
And it is the same for
= IF(MAP(Fournisseur, CategoryCountλ)>=3, "Oui", "Non")
If you can help me to understand this mistake, after i will be good.
Thanks
Feb 03 2023 06:14 AM
Feb 03 2023 02:50 PM
Just to check, I assume you are using Excel 365 since the tool tip seems to accept UNIQUE and offers the French word 'matrice' rather than the English 'array'.
'Categories' would need to defined in Name Manger to reference the green column of your table, just as 'supplier/fournisseur' refers to the blue column. The error you show may be no more than a complaint about the "," (comma) being used as a separator whereas French localisation requires ";" (semi-colon).
Hopefully you will be allowed to post workbooks soon so that I am better able to examine your formulas. My apologies for addressing you in English, regrettably my attempting French would not improve our communication.
Feb 06 2023 06:08 AM
Feb 06 2023 07:36 PM
Feb 08 2023 12:31 AM