Tri et comptage en fonction des dates
Bonjour à tous et à toutes,
j'ai un problème urgent qui me tracasse depuis 2 jours concernant les critères d'une formule comptage.
Pour être plus explicite j'ai une base de clients ayant 2 colonnes une date d'ouverture et une date de fermeture sur plusieurs années.
-J'aune première formule qui fonctionne bien car elle en compte les dates 'ouvertures ( =SOMMEPROD((ANNEE($S$2:$S$79946)=ANNEE($C12))*(MOIS($S$2:$S$79946)=MOIS($B$13)))) comme on peut le voir dans le fichier exemple en C13 & C14 si on filtre en colonne S on a bien 1 contrats en Juin 19 et 35 juillet 19
- La grosse difficulté arrive sur la suite car ma formule ne différencie pas les sur les années: Exemple
=SOMMEPROD((ANNEE($S$2:$S$79946)=ANNEE($D12))*(MOIS($S$2:$S$79946)=MOIS($B$14))*(ANNEE($T$2:$T$79946)>=ANNEE($D12))*(MOIS($T$2:$T$79946)<>MOIS($B$14))) me renvoie 22 en d14 au lieu de 30 qui représentent les dates de prise d'effet en juillet et les dates de fin d'effet en juillet et les 2 cellules vides.
en faisant le filtre manuellement je me rends compte que la formule exclue les mois de juillet des autres années d'où l'erreur.
Pouvez vous svp modifier ma formule pour qu'elle fonctionne? qu'elle exclue uniquement le mois et l'année ensemble. Merci d'avance
Bonjour H2so4 et merci pour ta contribution , mais avec un tcd ce n'est pas possible car je dois en horizontale aller jusqu'en Mai 2023 , d'ou l'importance de la formule que j'allais juste ajuster
bonjour,
d'ou l'importance de la formule que j'allais juste ajuster
Le problème c'est que tu ne dis pas ce que cette formule doit calculer. (ou en tout cas je n'ai pas compris)
Bonjour H2so4 et une fois de plus merci pour ta réaction la formule est dans mon méssage initial la voici ci-dessous
La grosse difficulté arrive sur la suite car ma formule ne différencie pas les sur les années: Exemple
=SOMMEPROD((ANNEE($S$2:$S$79946)=ANNEE($D12))*(MOIS($S$2:$S$79946)=MOIS($B$14))*(ANNEE($T$2:$T$79946)>=ANNEE($D12))*(MOIS($T$2:$T$79946)<>MOIS($B$14))) me renvoie 22 en d14 au lieu de 30 qui représentent les dates de prise d'effet en juillet et les dates de fin d'effet en juillet et les 2 cellules vides.
en faisant le filtre manuellement je me rends compte que la formule exclue les mois de juillet des autres années d'où l'erreur.
Pouvez vous svp modifier ma formule pour qu'elle fonctionne? qu'elle exclue uniquement le mois et l'année ensemble. Merci d'avance
re-bonjour,
je ne comprends pas ton tableau. Que veux-tu calculer à l'intersection de D12 et B14 et en quoi le résultat sera-t-il différent du résultat à l'intersection E12 et B14 ? A quoi correspondent les dates en ligne 12 et en colonne B (date de prise d'effet en ligne 12, date de clôture en colonne 14 ou l'inverse, ou autre chose ?)
Indépendamment de ton tableau, que cherches-tu à calculer ?
Le nombre de clients ayant pris effet à un certain mois d'une certaine année et ayant clôturé à un certain mois (quelle que soit l'année, sauf si même année) ?
Le nombre de clients ayant pris effet à un certain mois d'une certaine année et ayant clôturé à un certain mois d'une certaine année ?
Un autre comptage ?
J'espère avoir clarifié ce que je ne comprenais pas.
bonjor le fil,
une proposition, mais cela a l'air de prendre beaucoup de temps, Peut-être une solution en VBA ?
PS. ceci fonctionne avec une version néerlandais, svp changer V1 de "JJMM" en "AAMM"
colonne "00-01" = pour les fins =""
il y a même quelque lignes avec Fin < Prise (ligne 48.700, ligne 48.761, ...
re-bonjour,
Indépendamment de ton tableau, que cherches-tu à calculer ?
Je cherche à calculer de manière automatique la création et le suivi d'un portefeuille de Juin 19 à aujourd'hui.
la création est déterminée sur la colonne C en Verticale ( C13, C14,15,....) et en horizontale le suivi où la tenue du portefeuille (D14,D15,E14,15 etc.)
pour faie ce suivi je me base sur 2 critères la date de prise d'effet et la date de fin d'effet de manière chronologique.
Exemple: en C13 je constate bien qu'en juin 19 j'ai uniquement un contrat de créer qui peut se vérifier si on filtre la colonne S des dates de prises d'effet, en D13 je vais chercher si il a une date de fin d'effet en colonne T ce n'est pas la cas on retrouve toujours un car cela signifie que le contrat est toujours ouvert
je vais prendre un exemple plus parlant:
Exemple 2 : en C14 on a la valeur 35 qui correspond au nombre de contrats créer en Juillet 19 sui se vérifie si filtre sur le critère Juillet 2019 en colonne S
EN D14 j'aimerai avoir une formule qui me ramené le nombre de contrat encore ouvert c'est à dire ayant une date de fin d'effet différente de Juillet 19 c'est à 28 au lieu de 22 comme dans le fichier exemple. car cela veut dire que ces contrats ont pris fin en juillet 19
Merci d'avance et à ton écoute pour plus de détails au besoin
Bonjour BsAlv, la macro ne fonctionne pas. au besoin je pourrais refaire un fichier exemple remplir les 50 premières cellules en dur pour plus de clarté, je donnes plus de détails dans mes échanges avec H2so4. Merci d'avance et bon dimanche
re,
simplement une vérification si mes comptages sont okay dans le TCD.
Comme vous avez 365, on le fera avec de formules au lieu du TCD dans la prochaine version.
Mais c'est aussi un beau excercise pour les spécialistes 365 ou PQ, qui savent le faire en une fois ...
EDIT : nouveau fichier avec formules 365 au lieu du TCD
Bonjour Bart,
Merci pour ta contribution je vais essayer de l'adapter à mon tableau je me rends compte que tu as créé des tables pour le modèle sans TCD ce qui rend un peut difficile ma transformation de la formule car ma base est bien plus grande et a plus de données que celle qui est sur le fiche exemple . Merci encore
re,
ce sont seulement les cellules de la plage A29:B30 qui sont important, excel fait le reste ...
Je vois aussi que la source du TCD était une plage et n'était pas le tableau, c'est pourquoi il manque beaucoup de chiffres.
Bonjour
On a 76 dates de fin d'effet antérieures aux dates de début ce qui donne des erreurs...
78Chris et merci pour ta contribution, du moment ou la formule fonctionne je pourrais changer les dates manuellement dans ma base . le plus important pour moi c'est une formule automatique que je peux ajuster ou rajouter des critères Merci
RE
Je n'utilise pas une formule mais une requête Powerquery : cela nécessite d'éliminer les erreurs en amont (je les ai mis en évidence dans la source et la requête ignore les lignes dont la date de fin est < à la date de début)
Bonjour à tous;
78chris j'ai besoin d'une formule car je pourrais y ajouter d'autres critères discriminant , ton tableau est mais les chiffres sont en durs donc un pu dommage, JE suis toujours dans l'attente de trouver une solution car depuis vendredi et avec toutes vos différentes contributions je n'ai pas pu avancer , je continue à essayer un max de test même si je commence à perdre un peu espoir .
voici la formule qui me donne des résultats les moins faux
=SOMMEPROD((ANNEE(PTF!$F$2:$F$79946)=ANNEE($B13))*(MOIS(PTF!$F$2:$F$79946)=MOIS($B$13))*(ANNEE(PTF!$G$2:$G$79946)>=ANNEE(C12))*(MOIS(PTF!$G$2:$G$79946)<>MOIS(C12)))
Merci
Bonjour
Non les chiffres ne sont pas en dur : une requête exploite dynamiquement des données et s'actualise d'un clic ...
Je doute que sur un tel volume de données SOMMEPROD soit viable mais je te laisse à tes certitudes...
une autre solution (je n'ai pas encore vérifié les chiffres)
Bonjour A tous et merci encore pour vos contributions,
78Chris ce n'est pas une question de certitude , je creuse coté formule car ma base et est énorme et au besoin je pourrais ajouter un critère une autre variable sans refaire le travail de comptage depuis le début. si il fallait unique compter ta solution sera ok ou sinon j'aurais fait le travail manuellement .
Bonjour Bart Je regarde ta solution et je vous reviens dans la matinée merci encore
bonjour, les 2 mathodes ont les mêmes résultats sauf 2 chiffres pour 20-05.
Je n'ai pas vérifié, mais c'est peut-être la faute des 78 dates doutables dans la source, voir "triangle"