"Exclure doublon" dans formule nb.si.ens

Résolu/Fermé
stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021 - 27 avril 2021 à 12:20
stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021 - 30 avril 2021 à 16:40
Bonjour,

Dans le fichier anonymisé joint en exemple, je souhaite trouver comment faire pour inclure dans ma formule de départ "nb.si.ens" la notion de doublon et exclure les occurrences répétées. Ou passer par une formule toute autre si nécessaire !

https://www.cjoint.com/c/KDBkqUrRVPD

1er critère
Elle cherche dans la colonne 2 les dates pour renvoyer le nombre d'occurrence par mois.

2ème critère
Elle cherche dans la colonne F si elle est remplie.

3ème critère
Je voudrais exclure les doublons possible en colonne F (pour x occurrences identiques en colonne F, n'en compter qu'1), et là je sèche.

Dans le fichier d'origine, les valeurs sont dans un onglet autre, et cet onglet contient les données "sources", je voudrais ne pas toucher à cet onglet...

Quelqu'un voit-il une solution ?

En vous remerciant !!

6 réponses

yclik Messages postés 2975 Date d'inscription vendredi 25 juillet 2014 Statut Membre Dernière intervention 1 octobre 2022 1 353
27 avril 2021 à 12:44
Bonjour
une piste
en G2
=NB.SI($F$2:$F$13;F2)



en H2 pour février
=SOMMEPROD((MOIS(B2:B13)=2)*(F2:F13<>"")*(G2:G13=1))


mais il 'est pas dit si l'exclusion des doublons est faite dans le mois concerné ou dans toute la colonne
1
Vaucluse Messages postés 26495 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 8 avril 2022 6 274
27 avril 2021 à 12:59
Bonjour (et salut Yclic)
je pense que cette solution va systématiquement éliminer toutes les occurrences multipliées, y compris leur première apparition
Je peux me tromper, mais pour que cela fonctionne il faudrait écrire en G2
=NB.SI($F$2:F2;F2), avec le champ F évolutif de façon à afficher 1 à la première apparition.
On peut aussi écrire
=SI(F2="";"";NB.SI($F$2:F2;F2))
ce qui limitera dans la formule somme
=SOMMEPROD((MOIS(B2:B13)=2)*(G2:G13=1))
A vérifier (suis à la bourre)
crdlmnt
0
ALS35 Messages postés 922 Date d'inscription jeudi 18 juillet 2019 Statut Membre Dernière intervention 30 septembre 2022 97
28 avril 2021 à 14:27
Bonjour à tous,

Une proposition à tester en rajoutant une colonne mois dans tes données. Le choix du mois se fait en N1, le résultat est en N2.
https://www.cjoint.com/c/KDCmy0Y3DN1
Si j'ai bien compris

Cordialement
1
Vaucluse Messages postés 26495 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 8 avril 2022 6 274
Modifié le 30 avril 2021 à 13:15
Bonjour
pas sur d'avoir tout compris. Voyez ce modèle dans lequel sont compté les items du mois sélectionnés:
  • un à chaque fois que F est vide
  • un seulement pour chacune des références dans le même mois

reste à savoir si c'est bien ça que vous voulez? (en tous cas Février affiche bien 4)
crdlmnt
https://mon-partage.fr/f/I4vJd7JF/

1
stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021 1
28 avril 2021 à 12:32
Bonjour et merci !!!

Par contre, je ne comprends pas tout sur le comportement de ces formules.

je fais un jeu de colonne avec la solution d'Yclik (colonne I et J) et un jeu avec la solution de Vaucluse (Colonne G et H).

Pour les deux, je colle en G2 =NB.SI($F$2:F2;F2) et I2=NB.SI($F$2:$F$13;F2)
Je tire ces formules vers le bas. pour la première, elle compare donc F2 à la plage F2:F2 sur la ligne 2 et compare à la fin en ligne 13 la cellule F13 dans une table F2:F13.
Dans le second cas on compare la cellule (F2 pour la ligne 2) à la table F2:F13 directement.
A la fin je compare aussi F13 à une table F2:F130

Ensuite, je colle en H2 =SOMMEPROD((MOIS(B2:B13)=2)*(G2:G13=1)).
Je colle aussi la formule en J3 =SOMMEPROD((MOIS(B2:B13)=2)*(F2:F13<>"")*(I2:I13=1)) (modifiée par rapport à sa colonne de référence "I" au lieu de "G")

En H
j'ai le bon résultat, en J3 j'ai 0 (ça confirme ce que craignais VAucluse). MAIS...
J'ai voulu modifier un peu les valeurs contenues en F2:F13, j'ai mis d'autres doublons, j'en ai retiré d'autres (comme si les données vivaient un peu) et là ça part en cacahuète ! j'ai ajouté la valeur "65833" en février, la formule n'aime pas du tout... il y a pour moi 3 occurences et les 2 formules n'en remontent que 2.

Je vous mets le fichier avec ce que j'ai fait... j'ai raté une étape ?

https://www.cjoint.com/c/KDCkDSL7NSD

Merci en tout cas, ça m'a l'air d'être dans la bonne voie !!!
0
stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021 1
28 avril 2021 à 14:41
Et tant que j'en suis à vous prendre du temps là dessus, savez-vous pourquoi "MOIS" ne fonctionne pas dans ma formule d'exemple ? je mets =NB.SI(B2:B13;MOIS(1)) pour avoir les occurrences de janvier, il remonte 0 alors qu'il y en a 6. ça fonctionne si je mets =NB.SI(B2:B13;"<31/01/2021") mais c'est moins pratique à dupliquer...

merci !
0
ALS35 Messages postés 922 Date d'inscription jeudi 18 juillet 2019 Statut Membre Dernière intervention 30 septembre 2022 97 > stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021
28 avril 2021 à 15:41
Bonjour,

Parce que dans ta plage B2:B13 ce sont des dates et non pas des numéros de mois. il faudrait par exemple
=SOMMEPROD((MOIS(B2:B13)=1)*1)
As-tu testé ma proposition du post #4

Cordialement
0
Vaucluse Messages postés 26495 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 8 avril 2022 6 274
28 avril 2021 à 18:58
Le problème vient du fait, non spécifié au départ que vous pouvez avoir le même code sur plusieurs mois, et que le NB.SI compte tous les codes sans s'occuper des mois
voyez ici si cela convient mieux et si non, revenez nous dire ce qui ne convient pas
https://mon-partage.fr/f/cOXIzWYb/
crdlmnt
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021 1
30 avril 2021 à 12:12
Bonjour,

Nickel, les deux fonctionnent à merveille !
Mais il y a donc 2 points auquel je n'ai pas pensé du tout en faisant mon tableau d'exemple, et je vous ai fait perdre votre temps en omettant cela. Je comprendrais parfaitement que vous laissiez tombé ce sujet.

D'abord cette histoire de numéros identiques sur des mois différents : je n'avais pas compris que c'était pour cela que ça me mettait une incohérence. Ca n'est pas incohérent en effet ! si le mois change, ça n'est plus une valeur doublon.
Donc comme vous aviez fait c'est très bien.

Ensuite, et c'est le plus embêtant : la colonne 6 n'est pas toujours pleine. Mon exemple ne l'indiquait nullement.
Chaque ligne est comptée, chaque lignes avec un vide en colonne 6 compte. Mais si la colonne 6 possède un numéro déjà existant dans un même mois, les lignes avec ce numéro en doublon ne doivent valoir qu'une seule ligne.

Comme j'ai du mal à voir ce qui est important je vais tenter d'être exhaustif :

La colonne 1 est forcément pleine, forcément par un numéro, forcément unique mais ne se suit pas obligatoirement (peut être trié par ordre si nécessaire). C'est le numéro de création de ligne, sa valeur n'a donc pas de limite.

La colonne 2 est forcément une date au format déjà indiqué (jj/mm/aaaa hh:mm), forcément rempli mais pas forcément unique.

Les colonnes 3 et 4 sont forcément remplies des mêmes critères "5" et "S".

La colonne 5 n'est pas forcément remplie et contient des caractères alphanumériques variés de longueurs différentes.

La colonne 6 peut être vide ou numérique (uniquement ce choix). Les numéros peuvent se répéter. Comportement erratique : on peut avoir x numéros successifs différents suivit d'un vide de x ligne suivit de x numéro successifs dont x déjà présents plus haut dans la colonne (et/ou qui seront à nouveaux présent plus bas) etc.

Pardon pour le temps que mon ignorance vous a fait perdre !

Sur ce lien j'ai mis à jour l'exemple... le doublon "65833" n'est qu'un exemple.

https://www.cjoint.com/c/KDEke1HbG8D
0
stitchbouck Messages postés 92 Date d'inscription mercredi 12 janvier 2011 Statut Membre Dernière intervention 11 mai 2021 1
Modifié le 30 avril 2021 à 16:41
C'est tout à fait ça et ça fonctionne sur le fichier original.

Un grand merci, et encore navré pour la difficulté des explications...

MERCI à tous/tes !
0