Calcul du nombre d'occurrences différentes entre deux dates
Edit :
J'ai rajouté le fichier oublié en pièce jointe.
Afin d'en réduire la taille, j'en ai expurgé la base de données pour ne garder que celles allant de janvier à mi-juin.
Bonjour à tous,
Je suis travailleur social dans une asbl et je suis en train de faire mes stats pour notre rapport d'activité 2016.
J'essaie de trouver une formule pour calculer le nombre de personnes différentes fréquentant notre service dans un intervalle de dates donné.
Soit :
la colonne E contenant les matricules des personnes que je veux compter.
la cellule J2 contenant la date de début
la cellule K2 contenant la date de fin
Pour l'instant j'ai la formule suivante :
=SOMMEPROD(SI($E$2:$E$999999<>"";(NB.SI($E$2:$E$999999;$E$2:$E$999999)=1))*($G$2:$G$999999>=$J$2)*($G$2:$G$999999<=$K$2)*1)
Le résultat est un 0
Etant une bille en Excell,je me tourne vers vous pour m'aider à trouver une solution.
J'ai pourtant l'impression de ne pas en être très éloigné...
ci-joint mon fichier xlsx.
Merci.
Jonathan.
Bonjour et bienvenu(e),
Ton fichier n'est pas joint à ton message.
Cdlt.
AcBot a écrit :ci-joint mon fichier xlsx.
Raté
Jean-Eric a écrit :Ton fichier n'est pas joint à ton message.
melch a écrit :Raté
En effet !
Erreur de débutant.
J'ai édité le premier message en fonction.
Merci de me l'avoir signalé!
Jonathan.
=SOMMEPROD((G:G>=J2)*(G:G<K2))melch a écrit :=SOMMEPROD((G:G>=J2)*(G:G<K2))
Merci, mais la réponse que j'obtiens lorsque j'applique la formule du 01/01/16 au 31/01/2016 inclus est 3213, alors que si calcule manuellement (=sélection des lignes se trouvant dans l'intervalles de dates et application de [données]>[suppression des doublons] à la colonne E), j'obtiens 861, chiffre qui me semble plus coller à nos impressions de terrain...
Bonjour,
Une autre solution à analyser avec la mise sous forme de tableau et la fonction NB.SI.ENS.
Le 31 novembre n'existe pas !
Cdlt.
Comme dit Jean-Eric le 31/11/2016 n'existe pas !!
Une proposition avec tableau et colonne sup pour ne pas compliquer les formules ...
je trouve 437 personnes différentes en fév 2016
c'est assez complexe car il faut détecter les personnes différentes entre les 2 dates !!
Jean-Eric a écrit :Le 31 novembre n'existe pas !
Je parlais bien du 31 janvier.
AcBot a écrit :(...) lorsque j'applique la formule du 01/01/16 au 31/01/2016 (...)
J'ai fait un coquille dans le titre : il ne faut pas lire "différences", mais "différentes".
Je ne cherche pas à compter le nombre de cellules entre les deux dates, mais bien le nombre de valeurs différentes (ne pas compter les doublons donc) : si une valeur se répète plusieurs fois, elle ne doit être comptée qu'une seule fois.
Jonathan.
A priori c'est bien mon cas !
je corrige ma formule et vérifie ... c'est ok
@Steelson
Merci, ça fonctionne!
C'est plus complexe que je ne l'avais pensé, et j'avoue ne pas très bien comprendre le raisonnement qui a amené à faire une colonne "1/, ni le rôle des cellules J3 et K3. ^^
J'ai juste un petit soucis : après avoir copié/collé l'ensemble de ma base de données, lorsque j'entre certaines dates, j'ai une erreur de type #N/A à la cellule J3, et par conséquent à la cellule L2.
J'en ai identifié la cause : parfois certains débuts de mois tombent un weekend ou un jour férié, où nous sommes fermés, et donc où aucune valeur n'est encodée dans la colonne "date", ni dans la colonne "member".
La formule cherche donc une date qui n'existe pas.
Je peux contourner le problème en encodant la date correspondant au lundi suivant le premier jour du mois (par exemple le 2/5/16, puisque le 1/5/16 tombe un dimanche).
Mais, comme j'aimerais bien que le calcul se fasse de manière entièrement automatisée, ne serait-il pas possible d'intégrer dans la formule le fait d'ignorer une date si elle ne se retrouve pas dans la liste et de prendre en compte la prochaine date dispo?
Merci
ok, je vais réfléchir, de toute façon j'avais le soucis d'améliorer un peu la formule
la colonne 1/fréquence est là pour simplifier la formule (et j'avoue pour moi aussi pour la mettre au point car cela n'a pas été évident), je vais voir si je peux l'intégrer ...
peux-tu me donner juste un bout d'exemple où on trouve #N/A ?
Pour des questions de clarté et de pertinence des infos, j'ai remplacé 1/fréquence par fréquence dans la période
Attention, la formule finale est à valider par Ctrl+Maj+Entrée (formule matricielle inversant ligne à ligne les valeurs de fréquence)
https://www.cjoint.com/c/GAgpqJ1yWLw
AcBot a écrit :'ignorer une date si elle ne se retrouve pas dans la liste et de prendre en compte la prochaine date dispo?
ok cela reste à faire ... un peu de réflexion
Merci Steelson,
Je te renvoie via un lien cjoint le fichier excel contenant la base de donnée 2016 complète et avec la cellule J3 contenant le message d'erreur : https://www.cjoint.com/c/GAgpY7vighq
J'imagine que les deux premiers onglets du classeur ("TCD" et "sans doublon") sont dispensables et que je peux les effacer?
J'ai rajouté un onglet "membre".
Je ne tiens pas à te surcharger de travail, ni à abuser de ton temps et de ta générosité, mais penses-tu qu'il serait possible de calculer sur base de cet onglet le nombre d'hommes et de femmes différents venus entre deux dates.
Le lien à faire entre les deux onglets étant via les colonnes "entrees!E" ("members") et "membres!B" (matricule). Le sexe se trouve quant à lui sur la colonne "membres!G".
En d'autres mots, il faudrait comptabiliser, dans la colonne "membres!G", le nombre de "H" et de "F" pour les matricules ("membres!B") correspondant aux valeurs uniques de la colonne "entrees!E" ("members") présent dans un intervalle de temps >= à "=entrees!J2" et <= à "=entrees!K2".
J'espère que je suis clair... C'est un vrai casse-tête...
Et je me rends bien compte que je joue dans un ligue bien au-delà de mes compétences
Si tu n'as pas le temps, ni l'envie, je comprendrais tout à fait.
Merci pour ce que tu fais déjà
@Steelson : Je n'avais pas vu ton dernier message, sorry. :/
OuiAcBot a écrit :J'imagine que les deux premiers onglets du classeur ("TCD" et "sans doublon") sont dispensables et que je peux les effacer?/
je vais poursuivre ce soir ...
Merci !
Mais pas de surménage, hein?
bonne soirée
Il a fallu décomposer ... (et maintenant décompresser)
Maintenant ... beaucoup plus simple et plus rapide.
Cette fois le TCD est nécessaire et on a d'un seul coup toutes les valeurs souhaitées !
Merci beaucoup Steelson
Je vais devoir m'attaquer aux tcd, je pense. Ça m'a l'air indispensable pour traiter des données complexes...
Pour cette année, j'ai les données nécessaires. Pour l'année prochaine, je vais décortiquer et faire du retro-ingeneering du travail que tu auras fait. ici.
Un grand merci, encore une fois !
jonathan
PS: Je note le sujet comme résolu.