Compter des noms et prénoms mis sur deux colonnes......
Clademar
Messages postés
10
Date d'inscription
Statut
Membre
Dernière intervention
-
Mike-31 Messages postés 18405 Date d'inscription Statut Contributeur Dernière intervention -
Mike-31 Messages postés 18405 Date d'inscription Statut Contributeur Dernière intervention -
Je m'explique, j'ai un tableau avec une colonne "nom" et une colonne "prénom". Des mêmes noms et prénoms sont répétés plusieurs fois dans ces colonnes ( par exemple j'ai plusieurs fois Donald Duck, Mickey Mouse, etc......). Il s'agit de clients qui sont passés plusieurs fois dans notre établissement. Je désire faire le total des clients que nous avons eus, sans compter les doublons. Je désire avoir une méthode qui ne nécessite pas de rajouter une colonne où les noms sont reportés, si possible. Merci de votre réponse.
A voir également:
- Fusionner deux cellules excel nom prénom
- Fusionner deux cellules excel - Guide
- Fusionner deux tableaux excel - Guide
- Formule excel pour additionner plusieurs cellules - Guide
- Liste déroulante excel - Guide
- Nombre de jours entre deux dates excel - Guide
11 réponses
Bonjour , Clademar , Gyrus ,
Pourquoi avoir retirer le "bonjour" écrit d'office ?
En faisait une simple recherche sur Google , qui m'a pris certainement moins de temps que toi pour écrire ton sujet :
https://support.microsoft.com/en-us/office/count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273?redirectsourcepath=%252ffr-fr%252farticle%252fcompter-des-valeurs-uniques-parmi-des-doublons-7889942d-824e-4469-893c-191d1efde950&ui=en-us&rs=en-us&ad=us
A adapter , celle ci est faite pour 12 lignes.
Cordialement,
Pourquoi avoir retirer le "bonjour" écrit d'office ?
En faisait une simple recherche sur Google , qui m'a pris certainement moins de temps que toi pour écrire ton sujet :
https://support.microsoft.com/en-us/office/count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273?redirectsourcepath=%252ffr-fr%252farticle%252fcompter-des-valeurs-uniques-parmi-des-doublons-7889942d-824e-4469-893c-191d1efde950&ui=en-us&rs=en-us&ad=us
=SOMME(SI(FREQUENCE(EQUIV(A2:A12&B2:B12;A2:A12&B2:B12;0);EQUIV(A2:A12&B2:B12;A2:A12&B2:B12;0))>0;1))
A adapter , celle ci est faite pour 12 lignes.
Cordialement,
Bonjour le fil,
pour ma part j'aurais concatené les données colonne A et B exemple en colonne E qui pourra être masquée avec =A2&B2
et cette formule si on prend soin de limiter la plage à la zone de données si non les cellule comtenant les formules seront comptabilisées
dans le cas de cellule vide ou contenant des formules, utiliser la même formule en remplaçant la borne E8 par une détection de cellule non vide avec formule retournant rien ""
remplacer E2:E8 par E2:INDIRECT(ADRESSE(MAX(LIGNE(E:E)*(E:E<>""));5;4))
ce qui donne
pour ma part j'aurais concatené les données colonne A et B exemple en colonne E qui pourra être masquée avec =A2&B2
et cette formule si on prend soin de limiter la plage à la zone de données si non les cellule comtenant les formules seront comptabilisées
=SOMME(SI(FREQUENCE(EQUIV(E2:E8;E2:E8;0);EQUIV(E2:E8;E2:E8;0))>0;1))
dans le cas de cellule vide ou contenant des formules, utiliser la même formule en remplaçant la borne E8 par une détection de cellule non vide avec formule retournant rien ""
remplacer E2:E8 par E2:INDIRECT(ADRESSE(MAX(LIGNE(E:E)*(E:E<>""));5;4))
ce qui donne
=SOMME(SI(FREQUENCE(EQUIV(E2:INDIRECT(ADRESSE(MAX(LIGNE(E:E)*(E:E<>""));5;4));E2:INDIRECT(ADRESSE(MAX(LIGNE(E:E)*(E:E<>""));5;4));0);EQUIV(E2:INDIRECT(ADRESSE(MAX(LIGNE(E:E)*(E:E<>""));5;4));E2:INDIRECT(ADRESSE(MAX(LIGNE(E:E)*(E:E<>""));5;4));0))>0;1))
Bonjour,
je travaillais également sur la même formule que tu as proposée avec un résultat erroné c'est pour cela que je suis passé par une colonne supplémentaire, c'est d'ailleurs ce que dénonçait Philou que je salue, compte manuelle les données et tu verras par toi même si tu as des lignes vides
Cordialement
je travaillais également sur la même formule que tu as proposée avec un résultat erroné c'est pour cela que je suis passé par une colonne supplémentaire, c'est d'ailleurs ce que dénonçait Philou que je salue, compte manuelle les données et tu verras par toi même si tu as des lignes vides
Cordialement
Re,
Si entre l'adresse de la première et dernière cellule il n'y a pas de cellule vide exemple entre A2 et B10 il est possible d'utiliser cette formule qui sera figée à la plage
=SOMME(SI(FREQUENCE(EQUIV(A2:A10&B2:B10;A2:A10&B2:B10;0);EQUIV(A2:A10&B2:B10;A2:A10&B2:B10;0))>0;1))
si la est appelée a varier il faut remplacer la borne de fin par une recherche de dernière cellule non vide par colonne et donc remplacer A2:A10 par A2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);1;4))
et idem pour B2:B10 par
B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);B:B;1);B:B);2;4))
ou
B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);2;4))
ce qui donne comme formule finie
il est possible de raccourcir la formule et faire plus technique en mettant des formule dans des champs nommé
pour cela activer la cellule destinée à contenir la formule finale et Formule/Gestionnaire de noms/Définir un nom
exemple pour la plage colonne A nommer un champ Col_A et saisir cette formule
=ADRESSE(EQUIV(RECHERCHEV(CAR(255);Sheet2!A:A;1);Sheet2!A:A);1;4)
nommer un nouveau champ pour la plage en colonne B exemple Col_B et saisir cette formule
=ADRESSE(EQUIV(RECHERCHEV(CAR(255);Sheet2!A:A;1);Sheet2!A:A);2;4)
la formule finale à coller dans la cellule active est
et le tour est directement joué
A+
Mike-31
Pas savoir n'est pas un échec, l'échec est le refus d'apprendre.
Si entre l'adresse de la première et dernière cellule il n'y a pas de cellule vide exemple entre A2 et B10 il est possible d'utiliser cette formule qui sera figée à la plage
=SOMME(SI(FREQUENCE(EQUIV(A2:A10&B2:B10;A2:A10&B2:B10;0);EQUIV(A2:A10&B2:B10;A2:A10&B2:B10;0))>0;1))
si la est appelée a varier il faut remplacer la borne de fin par une recherche de dernière cellule non vide par colonne et donc remplacer A2:A10 par A2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);1;4))
et idem pour B2:B10 par
B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);B:B;1);B:B);2;4))
ou
B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);2;4))
ce qui donne comme formule finie
=SOMME(SI(FREQUENCE(EQUIV(A2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);1;4))&B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);2;4));A2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);1;4))&B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);2;4));0);EQUIV(A2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);1;4))&B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);2;4));A2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);1;4))&B2:INDIRECT(ADRESSE(EQUIV(RECHERCHEV(CAR(255);A:A;1);A:A);2;4));0))>0;1))
il est possible de raccourcir la formule et faire plus technique en mettant des formule dans des champs nommé
pour cela activer la cellule destinée à contenir la formule finale et Formule/Gestionnaire de noms/Définir un nom
exemple pour la plage colonne A nommer un champ Col_A et saisir cette formule
=ADRESSE(EQUIV(RECHERCHEV(CAR(255);Sheet2!A:A;1);Sheet2!A:A);1;4)
nommer un nouveau champ pour la plage en colonne B exemple Col_B et saisir cette formule
=ADRESSE(EQUIV(RECHERCHEV(CAR(255);Sheet2!A:A;1);Sheet2!A:A);2;4)
la formule finale à coller dans la cellule active est
=SOMME(SI(FREQUENCE(EQUIV(A2:INDIRECT(Col_A)&B2:INDIRECT(Col_B);A2:INDIRECT(Col_A)&B2:INDIRECT(Col_B);0);EQUIV(A2:INDIRECT(Col_A)&B2:INDIRECT(Col_B);A2:INDIRECT(Col_A)&B2:INDIRECT(Col_B);0))>0;1))
et le tour est directement joué
A+
Mike-31
Pas savoir n'est pas un échec, l'échec est le refus d'apprendre.
Re,
il est possible de faire plus court et plus simple, à la formule qui prend en compte les cellules vides
=SOMME(SI(FREQUENCE(EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0);EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0))>0;1))
il suffit d'ajouter une conditionnelle sur les cellules vides
SI(NB.VIDE(A2:A100)>0;1;0)
et soustraire la conditionnelle cellule vide à la formule ce qui donne
au passage heureusement que la discussion nous a passionnée plus qu'au demandeur Clademar
il est possible de faire plus court et plus simple, à la formule qui prend en compte les cellules vides
=SOMME(SI(FREQUENCE(EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0);EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0))>0;1))
il suffit d'ajouter une conditionnelle sur les cellules vides
SI(NB.VIDE(A2:A100)>0;1;0)
et soustraire la conditionnelle cellule vide à la formule ce qui donne
=SOMME(SI(FREQUENCE(EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0);EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0))>0;1))-SI(NB.VIDE(A2:A100)>0;1;0)
au passage heureusement que la discussion nous a passionnée plus qu'au demandeur Clademar
Bonjour tout le monde,
Mais ça me passionne énormément, je suis juste un peu (beaucoup) dépassée par ces formules. Je suis rentrée du travail seulement à 19H00, mais j'ai essayé plusieurs formules et la formule ultra longue de Mike-31 fonctionne. Je vais essayé les autres. Déjà un grand merci à vous tous
Mais ça me passionne énormément, je suis juste un peu (beaucoup) dépassée par ces formules. Je suis rentrée du travail seulement à 19H00, mais j'ai essayé plusieurs formules et la formule ultra longue de Mike-31 fonctionne. Je vais essayé les autres. Déjà un grand merci à vous tous
Bonjour Mike,
J'ai essayé cette formule
=SOMME(SI(FREQUENCE(EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0);EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0))>0;1))-SI(NB.VIDE(A2:A100)>0;1;0)
Elle fonctionne et est bien plus courte. Un grand merci encore
Vu que vous êtes des as, je me permets de vous poser encore un problème. En fait j'ai une troisième colonnes où il est ajouté si les clients font un long séjour ou un court séjour. Est-il possible de mettre une deuxième conditions à la première formule pour que j'aie un total des clients "long séjour" et un total des clients "court séjour",
Merci
PS: par contre je ne sais pas où je dois aller pour marquer ma première demande comme résolue
J'ai essayé cette formule
=SOMME(SI(FREQUENCE(EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0);EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0))>0;1))-SI(NB.VIDE(A2:A100)>0;1;0)
Elle fonctionne et est bien plus courte. Un grand merci encore
Vu que vous êtes des as, je me permets de vous poser encore un problème. En fait j'ai une troisième colonnes où il est ajouté si les clients font un long séjour ou un court séjour. Est-il possible de mettre une deuxième conditions à la première formule pour que j'aie un total des clients "long séjour" et un total des clients "court séjour",
Merci
PS: par contre je ne sais pas où je dois aller pour marquer ma première demande comme résolue
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Re,
Si j'ai bien compris ta nouvelle demande, tu veux savoir combien tu as eu de
"long séjour" et "court séjour" pour chaque client,
et est il possible que certains clients puissent avoir un court séjour et un long
Si j'ai bien compris ta nouvelle demande, tu veux savoir combien tu as eu de
"long séjour" et "court séjour" pour chaque client,
et est il possible que certains clients puissent avoir un court séjour et un long
Re,
Oui c'est possible, mais j'aimerai aussi que si des clients viennent plusieurs fois en long séjour ou court séjour, ils ne soient comptés qu'une fois par catégorie.
Merci et bonne journée
Oui c'est possible, mais j'aimerai aussi que si des clients viennent plusieurs fois en long séjour ou court séjour, ils ne soient comptés qu'une fois par catégorie.
Merci et bonne journée
Re,
Je ne t'ai pas oublié, seulement un peu boosté?
alors j'en reviens à mes colonnes intermédiaires qui simplifiera tout tes calculs
ces colonnes pourront être masquées, fait une concaténation de tes cellules Nom et Prénom avec un espace, dans mon exemple j'ai pris la colonne D avec cette simple formule
en E2 cette formule matricielle
et incrémente vers le bas, il est possible d'éviter les formules de la colonne E avec des formule plus compliquées en F et G2 à toi de voir
ensuite pour ne pas utiliser des formules compliquées et compter tes fréquences évoquées précédemment utilise cette formule matricielle
et pour ta dernière demande tu peux faire un tableau de deux colonnes pour extraire sans doublon et cellule vide les noms et prénom, exemple en F2 cette simple formule
en G2 cette formule
ensuite pour compter les séjours qui par exemple sont zen colonne C tu colles en H2 cette formule
et éviter les zéro dans le cas de cellule critère vide
en I2
et tu incrémentes ces formules vers le bas
A+
Mike-31
Pas savoir n'est pas un échec, l'échec est le refus d'apprendre.
Je ne t'ai pas oublié, seulement un peu boosté?
alors j'en reviens à mes colonnes intermédiaires qui simplifiera tout tes calculs
ces colonnes pourront être masquées, fait une concaténation de tes cellules Nom et Prénom avec un espace, dans mon exemple j'ai pris la colonne D avec cette simple formule
=A2&" "&B2et incrémente vers le bas
en E2 cette formule matricielle
=INDEX(D$1:D$100;MIN(SI(NB.SI(E$1:E1;D$1:D$100)=0;SI(D$1:D$100<>"";LIGNE(D$1:D$100)))))&""
et incrémente vers le bas, il est possible d'éviter les formules de la colonne E avec des formule plus compliquées en F et G2 à toi de voir
ensuite pour ne pas utiliser des formules compliquées et compter tes fréquences évoquées précédemment utilise cette formule matricielle
=SOMME((FREQUENCE(SI(D2:D100="";"";EQUIV(D2:D100;D2:D100;0));LIGNE(D2:D100))>0)*1)
et pour ta dernière demande tu peux faire un tableau de deux colonnes pour extraire sans doublon et cellule vide les noms et prénom, exemple en F2 cette simple formule
=SIERREUR(GAUCHE($E2;CHERCHE(" ";$E2;1)-1);"")
en G2 cette formule
=SIERREUR(DROITE(E2;NBCAR(E2)-NBCAR(GAUCHE(E2;CHERCHE(" ";E2))));"")
ensuite pour compter les séjours qui par exemple sont zen colonne C tu colles en H2 cette formule
=SOMMEPROD((A2:A20=F2)*(B2:B20=G2)*(C2:C20="Long séjour"))
et éviter les zéro dans le cas de cellule critère vide
=SI(F2="";"";SOMMEPROD((A2:A20=F2)*(B2:B20=G2)*(C2:C20="Long séjour")))
en I2
=SI(F2="";"";SOMMEPROD((A2:A20=F2)*(B2:B20=G2)*(C2:C20="Court séjour")))
et tu incrémentes ces formules vers le bas
A+
Mike-31
Pas savoir n'est pas un échec, l'échec est le refus d'apprendre.
Bonjour Mike,
J'ai mis du temps, mais je faisais du baby-sitting pour mes petits-enfants.
Je te remercie de tes réponses, mais malheureusement ça ne fonctionne pas, car j'ai besoin de cette formule pour mon travail et mon patron ne veut pas que je reporte des noms et prénoms dans une autre colonnes.......Donc il me faut vraiment une formules qui me calcule le nombre de clients en court séjour et le nombre de client en long séjour, en sachant que si un client fait plusieurs courts séjours ou plusieurs longs séjours, il ne doit être compté qu'une fois par catégorie. Vraiment pas facile !
Un grand merci et amicalement
Clademar
J'ai mis du temps, mais je faisais du baby-sitting pour mes petits-enfants.
Je te remercie de tes réponses, mais malheureusement ça ne fonctionne pas, car j'ai besoin de cette formule pour mon travail et mon patron ne veut pas que je reporte des noms et prénoms dans une autre colonnes.......Donc il me faut vraiment une formules qui me calcule le nombre de clients en court séjour et le nombre de client en long séjour, en sachant que si un client fait plusieurs courts séjours ou plusieurs longs séjours, il ne doit être compté qu'une fois par catégorie. Vraiment pas facile !
Un grand merci et amicalement
Clademar
Re,
il est gentil ton boss de demander l'impossible ou de faire compliqué lorsqu'on peut faire simple
ce problème peut se traiter que par VBA ou formule matricielle, à savoir que les formules matricielles utilisent beaucoup de ressource et il est conseillé de les utiliser sur des plages limitées si on ne souhaite pas voir ralentir les mises à jour de formules
mais enfin regarde comme cela "formule matricielle"
A+
Mike-31
Pas savoir n'est pas un échec, l'échec est le refus d'apprendre.
il est gentil ton boss de demander l'impossible ou de faire compliqué lorsqu'on peut faire simple
ce problème peut se traiter que par VBA ou formule matricielle, à savoir que les formules matricielles utilisent beaucoup de ressource et il est conseillé de les utiliser sur des plages limitées si on ne souhaite pas voir ralentir les mises à jour de formules
mais enfin regarde comme cela "formule matricielle"
=NB(1/FREQUENCE(SI((C2:C100="Long séjour")*(A2:A100<>"");EQUIV(A2:A100&B2:B100;A2:A100&B2:B100;0));LIGNE(INDIRECT("1:"&LIGNES(A2:A100)))))
A+
Mike-31
Pas savoir n'est pas un échec, l'échec est le refus d'apprendre.
Salut Mike,
J'ai essayé ta formule, malheureusement ça me donne "'0" comme résultat et avec mon exemple je devrais arriver à "35". Je pense que c'est impossible de trouver une formule qui fonctionne.
Merci quand même pour ton aide.
A+
Clademar
J'ai essayé ta formule, malheureusement ça me donne "'0" comme résultat et avec mon exemple je devrais arriver à "35". Je pense que c'est impossible de trouver une formule qui fonctionne.
Merci quand même pour ton aide.
A+
Clademar
Re,
je viens de retester et pas de problème tu dois faire une erreur ou tu ne déclare pas la formule correctement en matricielle
https://www.cjoint.com/c/FIosbQL81Dj
je viens de retester et pas de problème tu dois faire une erreur ou tu ne déclare pas la formule correctement en matricielle
https://www.cjoint.com/c/FIosbQL81Dj