Compter sans doublons des Noms + Prénoms

**Friberg59** · 26/10/2016, 18h41

Bonjour,

Je souhaite obtenir une fonction afin de compter le nombre de membres uniques à partir d’une liste avec deux colonnes du type :

Durand Pierre
Dupont Roland
Martin Roland
Durand Pierre
Durand Jean
Martin Roland

Après avoir approfondi avec le tutoriel je n'ai pas trouvé de solution

http://silkyroad.developpez.com/excel/doublons/

Avec le tuto il n'y a pas de souci pour avoir le nombre d’occurrence unique sur le nom

Code :

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

=SOMMEPROD(SI(A1:A10<>"";1/NB.SI(A1:A10;A1:A10)))

Par contre je n'ai pas trouvé pour compter les doublons sur le nom + le prénom

aussi je vous pose la question de savoir s'il est possible de compter les doublons sur plusieurs colonnes ?
et ce directement sans passer par une formule ligne par ligne

Code :

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

=SOMMEPROD(($A$1:$A$10=A1)*($B$1:$B$10=B1))

Merci de votre proposition

Invité · 27/10/2016, 06h50

Bonjour,

Est ce que ce serait totalement "idiot" d'avoir une colonne supplémentaire contenant le couple Nom Prénom ?
Cette colonne pourrait être cachée en mode plan en fonctionnement normal.

Cordialement.

**Friberg59** · 27/10/2016, 08h15

Bonjour Eric,

C’est effectivement la solution en œuvre actuellement mais mon fichier sera utilisé par plusieurs utilisateurs de différents sites et la solution retenue est de l’héberger sur Drive.

D’où la nécessité d’utiliser des Fonctions hors vba et de plus, lors du transfert de fichier, les protections de cellules ne sont pas transmises depuis Excel et restent restreintes sous Drive. Les risques dans ce cas seraient que des utilisateurs suppriment des lignes (ce qui empêche un onglet « miroir » avec des formules) ou qu’ils insèrent des lignes dans la BDD sans recopier les formules.

Avoir une formule "intégrée" serait un plus...

Invité · 27/10/2016, 08h27

Envoyé par Friberg59

D’où la nécessité d’utiliser des Fonctions hors vba et de plus, lors du transfert de fichier, les protections de cellules ne sont pas transmises depuis Excel et restent restreintes sous Drive. Les risques dans ce cas seraient que des utilisateurs suppriment des lignes (ce qui empêche un onglet « miroir » avec des formules) ou qu’ils insèrent des lignes dans la BDD sans recopier les formules.

Cette fonction est-elle nécessaire au fonctionnement du fichier ? Ou est-ce pour du reporting qui pourrait être fait en dehors avec une macro complémentaire .xlam par exemple, à partir d'une extraction du fichier ?

Cordialement.

**Friberg59** · 27/10/2016, 08h49

Merci Eric de ton soutien,

Comme tu l’as deviné j’ai un onglet de reporting. Mais celui-ci est relativement soft et j’ai réussi à trouver des solutions pour tous les items de suivi …sauf cet indicateur de nb d’occurrences hors doublons sur lequel je bute, ce serai dommage de faire un fichier annexe uniquement pour ça

**papouclo** · 27/10/2016, 18h52

Bonjour,
Essayer la formule matricielle:

Code :

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

=SOMME(SI($A$1:$A$10<>"";1/NB.SI.ENS($A$1:$A$10;$A$1:$A$10;$B$1:$B$10;$B$1:$B$10)))

à valider cordialement par Ctrl + Maj + Entrée
Cordialement
Claude

**Friberg59** · 27/10/2016, 20h55

Merci Claude pour cette formule qui va très bien

...juste peut être s'il était possible de prendre en compte le fait que le prénom puisse ne pas être alimenté, en effet dans ce cas la formule renvoie un message d'erreur : #DIV/0

De plus après avoir testé sous Drive la formule ne fonctionne pas correctement et il y a un message d'erreur (dixit) : Erreur La valeur de Le paramètre 2 de la fonction DIVIDE ne peut être égale à zéro.

Voici le code de ta formule retraduit lors du transfert sous Drive

Code :

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

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF($A$1:$A$10<>"";1/COUNTIFS($A$1:$A$10;$A$1:$A$10;$B$1:$B$10;$B$1:$B$10)))); 1; 1)

merci de ton analyse

**Zekraoui_Jakani** · 27/10/2016, 22h24

Si ta liste commence en A1, sélectionne tous les noms et donne à cette plage le nom de "MaListe" (*)
En B1, entre cette formule que tu copies jusqu'au dernier nom :

Code :

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

=SUMPRODUCT(--ISNUMBER(FIND(MaListe;A1)))

(*) = Un nom de plage peut aussi s'écrire par la formule "=OFFSET($A1;;;COUNTA($A:$A)-1;1)", dans ce cas-ci.

**papouclo** · 28/10/2016, 09h25

Re,
Utiliser la formule matricielle :

Code :

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

=SOMME(SI($A$1:$A$10<>"";SI($B$1:$B$10<>"";1/NB.SI.ENS($A$1:$A$10;$A$1:$A$10;$B$1:$B$10;$B$1:$B$10);1/NB.SI($A$1:$A$10;$A$1:$A$10))))

toujours à valider par Ctrl + Maj + Entrée
Cordialement
Claude

**Friberg59** · 28/10/2016, 19h46

Merci Claude

Cette formule fonctionne à merveille

Malheureusement le passage sous Drive est toujours aussi difficile et j'ai le même message : "Erreur La valeur de Le paramètre 2 de la fonction DIVIDE ne peut être égale à zéro."

Code :

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

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF($A$1:$A$10<>"";IF($B$1:$B$10<>"";1/COUNTIFS($A$1:$A$10;$A$1:$A$10;$B$1:$B$10;$B$1:$B$10);1/COUNTIF($A$1:$A$10;$A$1:$A$10))))); 1; 1)

as-tu une idée ?

**NVCfrm** · 29/10/2016, 00h52

Envoyé par Friberg59

...
aussi je vous pose la question de savoir s'il est possible de compter les doublons sur plusieurs colonnes ?
et ce directement sans passer par une formule ligne par ligne

Code :

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

=SOMMEPROD(($A$1:$A$10=A1)*($B$1:$B$10=B1))

...

Salut, la réponse est oui.

Code :

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

=SOMME(SI(FREQUENCE(EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0);EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0));1))

Edit:
Oups! Je crois avoir lu un peu trop vite et répondu un peu trop vite.
S'il s'agit de compter les doublons, (ce que je ne suis plus sûr de ce que ça veut dire, on m'excusera ma compréhension approx du français

), mettre >1 devant la condition Si(...>1;1)

**Friberg59** · 29/10/2016, 09h52

Bonjour Ousmane,

Ta proposition donne un résultat supérieur au résultat réel sous Excel, par ailleurs sous Drive le résultat reste figé à la valeur 1 (même si aucune donnée dans la plage)

voici la traduction de ta formule sous Drive

Code :

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

=SUM(IF(FREQUENCY(MATCH(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0);MATCH(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0));1))

**NVCfrm** · 29/10/2016, 12h33

Salut Friberg59
Je dois t'avouer tout de suite que j'ai des doutes sur le fait d'avoir compris ce que tu veux faire.
Reste à confirmer que je me trompes sur ceci :
Tu veux compter des occurrences uniques dans une plage de deux colonnes identifiés par Nom et Prenom.
Je ne crois pas que ta difficulté au stade où tu te trouves est de compter les doublons dans une des deux seules colonnes, mais dans les deux colonnes combinées sans avoir crées une colonne supplémentaire de données. Est ce correct ?

Voilà la même formule en modifiant juste le critère de la condition te permet de savoir :
1 - Combien d'éléments existe dans ta liste en ignorant les doublons.
2 - Combien d'éléments existe en doubles dans ta liste.
3 - Combien d'éléments n'existent pas en double dans ta liste.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Cas 1 :
=SOMME(SI(FREQUENCE(
      EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0);EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0));1))
      
Cas 2:
=SOMME(SI(FREQUENCE(
      EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0);EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0))>1;1))

cas 3:
=SOMME(SI(FREQUENCE(
      EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0);EQUIV(INDEX(A1:A10&B1:B10;;1);INDEX(A1:A10&B1:B10;;1);0))=1;1))

Les fonctions imbriquées renvoyant des tableaux, il serait peut-être préférable de valider l'entrée sous forme matricielle. e.g. CTRL+MAJ+ENTREE

Il n'y aurait pas eu le besoin de traiter les 2 colonnes ensemble, la formule pourrait être allégé en enlevant l'imbrication de la fonction INDEX.

Code :

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

=SOMME(SI(FREQUENCE(EQUIV(A1:A10;A1:A10;0);EQUIV(A1:A10;A1:A10;0));1))

remarque que l'absence de critère du SI ici, implique tout nombre différent de 0.
En espérant que ça aidera à tirer ton épingle du jeu.

Edit :
Je viens d'essayer sur Drive. Je confirmes qu'il faut que la formule soit en entrée matricielle. En essayant pour de vrai la formule, j'ai vu que ça pouvait être allégé comme suit (pourquoi j'ai pensé qu'il fallait une imbrication d'INDEX ?):

Code :

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

=ArrayFormula(SUM(IF(FREQUENCY(MATCH(A1:A10&B1:B10;;A1:A10&B1:B10;0);MATCH(A1:A10&B1:B10;;A1:A10&B1:B10;0));1)))

A toi le choix du critère de la condition SI.

**Friberg59** · 29/10/2016, 15h04

Re bonjour Ousmane et merci

Tu as très bien reformulé mon besoin

Reste que sur tes nouvelles propositions (comme pour la 1ère) le résultat est systématiquement supérieur de +1 (sur les cas n°1 et 2, le 3 donne le bon résultat)

Suite à ton Edit j’ai reformulé ta proposition en Excel et je me suis permis de retirer des « ; » qui manifestement étaient resté en trop.

Voici ce que cela donne au format Excel

Code :

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

{=SOMME(SI(FREQUENCE(EQUIV(A1:A10&B1:B10;A1:A10&B1:B10;0);EQUIV(A1:A10&B1:B10;A1:A10&B1:B10;0));1))-1}

…est-bon ?
en tout cas ça marche sous Excel et Drive

**NVCfrm** · 29/10/2016, 15h26

Envoyé par Friberg59

...
Suite à ton Edit j’ai reformulé ta proposition en Excel et je me suis permis de retirer des « ; » qui manifestement étaient resté en trop.

Voici ce que cela donne au format Excel

Code :

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

{=SOMME(SI(FREQUENCE(EQUIV(A1:A10&B1:B10;A1:A10&B1:B10;0);EQUIV(A1:A10&B1:B10;A1:A10&B1:B10;0));1))-1}

…est-bon ?
en tout cas ça marche sous Excel et Drive

Merci pour le signalement d'erreur de transcription, mais ...
Oui il y a un mais sur je cites:

le résultat est systématiquement supérieur de +1 (sur les cas n°1 et 2, le 3 donne le bon résultat)

, que tu as traité en faisant une soustraction.
Je me demandes comment et pourquoi. Peux tu poster la liste ici, afin que je découvres le pourquoi de cette bizarrerie ?

**Friberg59** · 31/10/2016, 15h38

Bonjour Ousmane,

Voici un fichier avec une liste d'exemple pour les 3 cas que tu as pu recenser

Code :

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

1
2
3
SOMME(SI(FREQUENCE(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0);EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0));1))
SOMME(SI(FREQUENCE(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0);EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0))>1;1))
SOMME(SI(FREQUENCE(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0);EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0))=1;1))

Pour faciliter la lecture j'ai ajouté dans ce fichier une ligne de titre et mis la MFC proposée dans le tuto cité ci-dessus, puis j'ai compté "manuellement" les différents cas ...sans erreur j'espère !!!

bien à toi
Claude

**NVCfrm** · 01/11/2016, 01h23

bjr,
en effet c'est à cause des cellules vides.
Essaies d'influer sur ce comportement dans les options Excel, je crois quelque part dans l'onglet avancées ou formules, changer la valeur de quelque chose comme "Afficher zéro les cellules vides comme Nulles ou Zéro".

Peut-être que ça ne marchera pas.

Tu as la possibilité par voies de formules toujours, d'exclure les cellules vides.

Code :

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

=SOMME(SI(FREQUENCE(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0);EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0));1))-SOMME((A2:A11&B2:B11="")*1)

Pourquoi la troisième formule renvoi juste et pas les autres ?
(je parles avec moi même)
L'intrigue reste entière. N'ayant d'Office sous la main, je viens de tester sur Calc de LibreOffice et sur Sheets de Drive.
Je constates que DriveSheets a une très mauvaise implémentation des formules de tableau.

J'ai compris !

Petit Défi aux excelliens avertis. A partir de son fichier expliquez pourquoi ?

-------------
Edit
Le cas des vides ne peut se traiter qu'ainsi:

Tu as la possibilité par voies de formules toujours, d'exclure les cellules vides.

Code :

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

=SOMME(SI(FREQUENCE(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0);EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0));1))-SOMME((A2:A11&B2:B11="")*1)

sera donc fausse dans différents cas de figure.

En voyant tes données, j'ai failli croire à un bug d'Excel dans le traitement du cas 3, alors que c'est la formule qui est buggé

par rapport au besoin d'ignorer les lignes vides.
Même avec ta soustraction, les résultats pourront te faire sursauter, à cause de la possibilité d'une ligne vide.
Il n'y avait pas de sens qu'elle soit vrai pour la troisième et fausse avec les deux premières. Quand on sait que la même formule employée est la même chose partout, à un détail près sur le critère. La réponse ne pouvait être que dans tes données. Logique non?

Je ne vais pas te faire l'injure à ce niveau, de t'écrire la syntaxe du comment adapter la formule mais juste te guider.

FREQUENCE ignore les valeurs nulles ou zéros. Le tableau transmis à FREQUENCE par EQUIV ne contient pas de valeur nulle.
Forcer la transmission de valeurs nulles pour les lignes vides dans le tableau transmis à FREQUENCE permet d'écarter le compte de celles-ci.
quelque chose comme FREQUENCE(SI(((xx="")*1);"";EQUIV(...));...

**Friberg59** · 01/11/2016, 16h25

Merci pour l'exercice pratique qui n'a pas été de tout repos (satanés paranthèses !!!) ...mais quand même plus facile pour moi du fait que tu m'ai aiguillé

Voici le résultat auquel je suis arrivé avec pour mémoire le détail des 3 compteurs :
1 - Combien d'éléments existent dans la liste en ignorant les doublons.
2 - Combien d'éléments existent en double dans la liste.
3 - Combien d'éléments n'existent pas en double dans la liste.

Code :

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

1
2
3
=SOMME(SI(FREQUENCE(SI(((A2:A11="")*1);"";(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0)));EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0));1))
=SOMME(SI(FREQUENCE(SI(((A2:A11="")*1);"";(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0)));EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0))>1;1))
=SOMME(SI(FREQUENCE(SI(((A2:A11="")*1);"";(EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0)));EQUIV(A2:A11&B2:B11;A2:A11&B2:B11;0))=1;1))

Du coup si quelqu'un est intéressé je remets en pj le fichier d'exemple avec les formules qui vont bien
...et qui plus est fonctionnent parfaitement sous Drive

Un grand merci à toi Ousmane pour ton expertise et ta ténacité pour trouver la solution ainsi qu'aux autres contributeurs
claude

Compter sans doublons des Noms + Prénoms [XL-2010]

Excel

Discussions similaires

Partager

Partager