"Exclure doublon" dans formule nb.si.ens

Résolu

stitchbouck Messages postés 135 Date d'inscription Statut Membre Dernière intervention -
stitchbouck Messages postés 135 Date d'inscription Statut Membre Dernière intervention - 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 !!

A voir également:

Nbval sans doublon
Nbval en anglais - Guide
Doublon photo - Guide
Tirage au sort excel aléatoire sans doublon ✓ - Forum Excel
Supprimer doublon excel - Guide
Tirage au sorte d'un nom dans une liste sans répetition - excel ✓ - Forum Excel

6 réponses

Réponse 1 / 6

yclik Messages postés 3858 Date d'inscription Statut Membre Dernière intervention 1 560

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

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 434

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

Réponse 4 / 6

stitchbouck Messages postés 135 Date d'inscription Statut Membre Dernière intervention 2

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 !!!

stitchbouck Messages postés 135 Date d'inscription Statut Membre Dernière intervention 2

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 !

ALS35 Messages postés 1033 Date d'inscription Statut Membre Dernière intervention 144 > stitchbouck Messages postés 135 Date d'inscription Statut Membre Dernière intervention

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

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 434

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

Réponse 5 / 6

stitchbouck Messages postés 135 Date d'inscription Statut Membre Dernière intervention 2

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

Discussions similaires

Excel : Décompte sans doublons d'une plage

NB.SI.ENS et condition OU

HELP : nb.si. ens sans doublons ?

Nombre de valeurs (sans doubles) correspondant à un critère

SOMMEPROD sans doublons

"NB.SI" cellules non vides

Votre réponse

Discussions similaires