Compter nombre de valeurs uniques avec condition

Bonjour à tous et merci d'avance pour votre aide,

je souhaite à l'aide de formules (pas de connaissance VB) compter le nombre de valeurs uniques dans une colonne (R:R en l’occurrence) avec conditions.

• cette colonne fait parti d'un tableau de base de données
• elle comprend les nombres associés aux dates figurant dans une autre colonne
• il n'y a pas de cellule vide (sauf bien sûr sur les lignes en fin de tableau non renseignées). Toutefois les cellules fin de tableau comprennent une formule indiquant le nombre associé à la date renseignée (si bien évidemment une date est renseignée). Je ne pense pas que cela ai une incidence mais autant être complet.

Cette base de données est simple ainsi que sa gestion.

Pour être simple c'est juste un tableau de bord du nombre de dossiers administratifs traité par jour et précisant la catégorie de chaque dossier traité (assurance, immobilier, etc ...)

A chaque fois que je traite un dossier, je note sa référence (dans la continuité du tableau), la date, le type de dossier, etc...

J'ai donc une ligne par référence de dossier et donc plusieurs dates identiques puisque qu' heureusement je traite plus d'un dossier par jour.

Les formules précisées ci-dessus consistent entre autre à reprendre automatiquement la date du dessus si une référence dossier est renseignée. Cela m'évite du copier coller inutile.

J'ai donc dans ce tableau plusieurs fois la même date (nombre de dossiers traités pour chacune de ces dates).

Problématique : je souhaite pouvoir calculer automatiquement à l'aide de formules le nombre de jours travaillés entre 2 dates. Pour se faire il pourrait convenir de compter le nombre de valeurs uniques parmi les données de la colonne associée à colonne dates, conditionné par l'intervalle de 2 dates précisés dans 2 cellules spécifiques. En gros : comptes le nombre de valeurs uniques dans cette colonne parmi celles étant supérieures ou égales à tel chiffre et inférieures ou égales à tel autre.

Avec les formules FREQUENCE et SOMME associés "SOMME((FREQUENCE('dossiers num'!R:R;'dossiers num'!R:R)>0)*1)" j'arrive à compter les valeurs uniques sur l'ensemble de la base. Mais je n'arrive pas à y associer la formule SI pour conditionner mon calcul.

Je vous remercie par avance de votre aide. J'ai envisagé d'autres solutions pour répondre à ma problématique mais pour des raisons "pratiques", je ne peux les mettre en application.

Question subsidiaire : j'ai trouvé la formule précédente sur un forum mais je ne la comprends pas. En décomposant la formule je comprends le principe mais j'ai des résultats différents selon les étapes. Mais le résultat final fonctionne, c'est l'essentiel.

Merci d'avance, en espérant avoir été clair.

Merci,

Stefanopoulos

Bonsoir,

une réponse en fichier joint

la fonction fréquence recherche en premier inférieur ou égal à la première valeur, donc pour ne prendre que la partie égal de la première recherche, la date de début de recherche est diminuée de 1 jour...

voir le fichier

@ bientôt

LouReeD

Bonjour et merci.

Votre fichier image très bien ma problématique. Toutefois la solution proposée n y répond pas. Dans votre exemple il y a bien 19 lignes dont la date correspond a l intervalle mais il n y a que 13 dates uniques. Et c est ce dernier résultat que je souhaite avoir.

Qui plus est on pourrait avoir le même résultat que vous avec la fonction NB.SI.

Merci tout de même pour votre aide.

Stefanopoulos

Bonjour @ tous,

merci de joindre ton fichier pour que l'on puisse t'aider.

@ + +

Bonjour,

Sur la base du fichier de LouReed ...

ci-joint une proposition différente ...

Edit : Salut R@chid ...

Tu as raison ... pas de fichier ... du coup, j'ai piqué celui de LouReed

Bonjour à tous,

Alors là ... je dis chapeau bas ! J'ai trituré dans tous les sens et sa fonctionne à merveille.

Je suis en totale admiration.

Encore merci et chapeau.

Bien cordialement à vous tous,

Stefanopoulos

PS : la question est résolue. Je coche la case appropriée. Toutefois, afin de me coucher une fois de plus moins c..., pourriez vous m'expliquer le fonctionnement de cette formule

"=SOMMEPROD(($B$2:$B$28>=E2)*($B$2:$B$28<=F2)/NB.SI($B$2:$B$28;$B$2:$B$28&""))".

De plus pourquoi le &"".

Je précise également que cette formule fonctionne également très bien avec la totalité de la colonne sélectionnée : =SOMMEPROD((B:B>=E2)*(B:B<=F2)/NB.SI(B:B;B:B&""))

PS2 : c'est bon, j'ai compris. C'est d'une logique implacable. je n'aurais jamais trouvé tout seul. Encore merci et chapeau. Le &"" doit peut être être une signature.

Re,

Merci pour tes félicitations et tes remerciements ...

Si tu es sûr et certain que tes champs sont propres ...tu peux supprimer la concaténation ...

Je te recommande de limiter la plage dans ta formule ...

Bonjour,

je ne conseille pas la SOMME(SI(;1/(NB.SI())), essayer sur une grande plage pour voir apparaitre un résultat avec la virgule ce qui n'est pas correcte, préférez FRÉQUENCE(SI())..

@ + +

merci R@chid

pourrais tu s'il te plait indiquer ta formule complète sur la base de l'exemple du fichier joint dans mon dernier post ?

T'en remerciant par avance,

Stefanopoulos

Re,

=NB(1/FREQUENCE(SI((B2:B27>=E2)*(B2:B27<=F2);EQUIV(B2:B27;B2:B27;0));LIGNE(B2:B27)-1))

@ valider par Ctrl+Shift+Enter

Même pas 4 secondes sur une plage de 100.000 lignes, avec SOMMEPROD() on ne termine pas le calcul.

tu peux toute fois tester la vitesse sur une plage de 10.000 lignes.

Fais d'abord avec SOMMEPROD() et supprime la formule et refais le calcul avec la mienne.

@ + +

Re,

Sur 26 lignes ... je serai curieux de connaitre la différence de timing ...

10'000 Lignes avec des matricielles ...

Bon Courage ...!!!

merci R@chid mais ça ne marche pas chez moi. ça me donne 1 (voir cellule en rouge dans ma feuille exemple jointe). Peut être pourrais tu y corriger la formule proposée que j'ai peut être mal retranscrite.

Ta remarque est très pertinente d'une part sur les arrondis éventuels et sur la rapidité d'exécution de la formule d'autre part. La base sur laquelle je souhaite appliquer cette formule peut effectivement largement les dépasser (je ne peux pas la joindre : fichier trop gros et trop de choses pour illustrer clairement l'exemple de ce post)

encore merci

Bonsoir @ tous,

=NB(1/FREQUENCE(SI((A22:A41>=A19)*(A22:A41<=B19);EQUIV(A22:A41;A22:A41;0));LIGNE(A22:A41)[surligner=#FF0000]-21))

@ valider par Ctrl+Shift+Enter

-21 : c'est le nombre de lignes avant la première ligne de la base de données.

James007 :

moi quand je veux faire une formule, je me demande toujours si le questionneur a une base de données très limitée ou non, sur cette discussion elle parait que la base va faire plus que mille lignes.

Pour ce qui est vitesse de calcul, c'est un défit de tout le monde, c'est vrai que c'est pas remarquable sur une base de données de 100 lignes, j'ai pu faire des calculs matriciels avec FREQUENCE() et c'était plus rapides qu'avec des codes VBA.

@ + +

Bonjour à tous,

Problème résolu. Je me permets d'attacher à ce post un fichier récap, je l'espère clairement explicatif sur l'ensemble des solutions proposées. Libre aux modérateurs de le supprimer, modifier, etc ...

Je souhaitais faire des remerciements tout particulier à :

- LouReed

• R@chid
• James007

pour leur aide, réactivité, clarté et patience quant à la résolution de mon problème. Je suis en totale admiration de leur "maîtrise" d'Excel.

Petites précisions sur les solutions proposées et qui fonctionnent :

• formule SOMMEPROD : fonctionne pour les petites bases de données et sur les plages de données ne contenant pas de valeur nulle
• formule NB(FREQUENCE(SI())) : semble appropriée pour tous les cas de figure

demande subsidiaire :

pourrait on m'expliquer le fonctionnement de la formule NB(FREQUENCE(SI())) ? Vous en remerciant par avance

Encore merci et chapeau bas !

Stefanopoulos

Bonjour,

Une petite précision :

"Pour être "honnête" Loureed n'a pas fait grand chose ...

Une fois les formules Index Equiv Someprod sorties... je n'y suis plus "

Ceci dit merci tout de même pour vos remerciements, mais il faut que j'approfondisse ces histoire de fonction "artillerie lourde" qui permettent vraiment pas mal de chose !

@ bientôt

LouReeD

Bonjour @ tous,

• Ton souci de division par 0 avec SOMMEPROD() on peut le remédier comme suit :
  

  =SOMME(SIERREUR((A27:A46>=A24)*(A27:A46<=B24)/NB.SI(A27:A46;A27:A46);))

  
  @ valider par Ctrl+Shift+Enter
• pour le -26, préférer dire le nombre de lignes avant la première ligne de la base de données, pour mieux comprendre on cherche justement à renvoyer une suite des entiers dont la valeur min est 1 et la valeur max est le nombre de ligne de la base de données, sinon on peut écrire directement la LIGNE(A27:A46)-26 comme suit LIGNE(INDIRECT("1:"&LIGNES(A27:A46))) ce qui va t'aider d’éviter de changer le -26 si on ajoute par exemple des lignes au dessus de notre base de données.

@ + +

LouReeD a écrit :

Ceci dit merci tout de même pour vos remerciements, mais il faut que j'approfondisse ces histoire de fonction "artillerie lourde" qui permettent vraiment pas mal de chose !

Vous avez joint un fichier exemple imageant parfaitement mon problème. Qui plus est votre fichier était clair et très bien présenté. Il faut savoir apprécier et saluer le travail fourni, d'où la parfaite légitimité d'association à mes remerciements.

Je vous rassure, je n'y comprends rien aux solutions proposées. ça fait trois jours que je "gratte" pour comprendre la solution SOMMEPROD proposée par James007 et qui fonctionne très bien, même si elle semble "limitée" selon les cas de figure comme il l'a lui même précisé.

Et juste quand je la comprends, R@chid sort la solution NB(FREQUENCE(SI())) qui fonctionne tout aussi bien, semble plus appropriée à tous les cas de figure et qui est partie pour me faire passer de bonnes nuits blanches. Merci R@chid! Sympa !

Encore merci,

Stefano

merci R@chid,

Il paraît que de faire travailler ses méninges contribue à éviter Alzheimer. Grâce à toi, je repousse considérablement la potentialité d'en être atteint.