Calculer le nombre de jour d'utilisation par période calendaire sans doublons

[Tatoufo]

Bonjour,

Je bloque depuis quelques jours sur une formule de calcul que je ne parviens pas à résoudre et fait appel aux bonnes âmes de ce forum.

Je dispose d'une base de données dans laquelle figure des "véhicules" en colonne A et des "dates d'utilisation" en colonne B.
Je souhaite calculer le nombre de jours où chaque véhicule est utilisé, par période calendaire (trimestre), pour parvenir aux résultats figurant dans le tableau de droite.

Les véhicules peuvent être utilisés plusieurs fois dans la même journées, mais je ne souhaite pas comptabiliser plusieurs fois le même véhicule dans ce cas. Certains véhicules peuvent ne pas être utilisés du tout sur la période analysée.

J'ai trouvé une formule qui permet de compter les jours sans doublons, du type =SOMMEPROD((FREQUENCE(B4:B13;B4:B13)>0)*1), mais je n'arrive pas à la faire pour chaque véhicule.

Je ne souhaite pas utiliser un TCD car le bilan automatique ne doit pas changer de format si certains véhicules ne sont pas utilisés. Cette analyse porte sur une base de données de 10 000 lignes, renseignées progressivement au cours de l'année.

Merci par avance pour votre aide.

 

[Celeda]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Bonjour,

Avec plages nommées et sommeprod et frequence et les trimestres identifiés selon le trimestre :

SOMMEPROD((FREQUENCE(($D4=NOMS)*(MOIS(DATES)<4)*(ANNEE(DATES)=2010)*DATES;(NOMS=$D4)*(MOIS(DATES)<4)*(ANNEE(DATES)=2010)*DATES)>0)*1)-1

SOMMEPROD((FREQUENCE(($D4=NOMS)*(MOIS(DATES)>3)*(MOIS(DATES)<7)*(ANNEE(DATES)=2010)*DATES;(NOMS=$D4)*(MOIS(DATES)>3)*(MOIS(DATES)<7)*(ANNEE(DATES)=2010)*DATES)>0)*1)-1

SOMMEPROD((FREQUENCE(($D4=NOMS)*(MOIS(DATES)>6)*(MOIS(DATES)<10)*(ANNEE(DATES)=2010)*DATES;(NOMS=$D4)*(MOIS(DATES)>6)*(MOIS(DATES)<10)*(ANNEE(DATES)=2010)*DATES)>0)*1)-1

SOMMEPROD((FREQUENCE(($D4=NOMS)*(MOIS(DATES)>9)*(ANNEE(DATES)=2010)*DATES;(NOMS=$D4)*(MOIS(DATES)>9)*(ANNEE(DATES)=2010)*DATES)>0)*1)-1

une petite extraction de Sommeprod2 lol!!!mon livre de chevet

 

[Tatoufo]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Absolument génial Barbatruc !!! Je suis stupéfait. C'est simple, logique et efficace.

J'apprend au passage que l'on peut remplacer les références d'une plage par un "NOM" dans la formule et c'est beaucoup plus simple lorsqu'il faut jongler avec différents onglets.

Petite complexité dans mon bazard tout de même ... Les déplacements sont saisis dans des onglets différents. Il y a un onglet pour les déplacements EN AGGLOMERATION et un onglet pour les déplacements EN REGION, car les variables des autres colonnes ne sont pas identiques.

J'ai donc affecté des noms aux plages correspondantes dans chaque onglet.
Dans l'onglet EN AGGLOMERATION c'est "DATESAGGLO" et "VEHICULESAGGLO" et dans l'onglet "EN REGION" c'est "DATESREGION" et "VEHICULESREGION".

J'ai ensuite modifié la formule ainsi : =SOMMEPROD((FREQUENCE(($B53=VEHICULESAGGLO)*(MOIS(DATESAGGLO)<4)*DATESAGGLO;(VEHICULESAGGLO=$B53)*(MOIS(DATESAGGLO)<4)*DATESAGGLO)>0)+(FREQUENCE(($B53=VEHICULESREGION)*(MOIS(DATESREGION)<4)*DATESREGION;(VEHICULESREGION=$B53)*(MOIS(DATESREGION)<4)*DATESREGION)>0)*1)-2

J'ai considéré que le -1 de la fin était modifié en -2 car je répète la formule (FREQUENCE...).

Toutefois ma formule ne tient pas compte du fait qu'un même véhicule peut être utilisé en agglomération et en région le même jour, sur deux parcours différents. Ce jour d'utilisation est donc comptabilisé deux fois... ce que je ne souhaite pas.

Il y a forcement une subtilité que je ne vois pas.

Merci encore pour tes lumières.

 

[david84]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Bonsoir,
une seule formule à placer en E4 et à tirer vers la droite et le bas (validation matricielle) :

=SOMME(N(FREQUENCE(SI((LIGNE(INDIRECT("1:"&LIGNES(NOMS)))=EQUIV(NOMS&DATES;NOMS&DATES;0)*(ANNEE(DATES)=2010)*(MOIS(DATES)>=((MOD(COLONNES($A:A);6)-1)*3)+1)*(MOIS(DATES)<=(MOD(COLONNES($A:B);6)-1)*3)*(NOMS=$D4));LIGNE(NOMS));LIGNE(NOMS))>0))

ou :

=SOMME(N(FREQUENCE(SI((ANNEE(DATES)=2010)*(MOIS(DATES)>=((MOD(COLONNES($A:A);6)-1)*3)+1)*(MOIS(DATES)<=(MOD(COLONNES($A:B);6)-1)*3)*(NOMS=$D4);EQUIV(NOMS&DATES;NOMS&DATES;0));EQUIV(NOMS&DATES;NOMS&DATES;0))>0))

A+

 

[Tatoufo]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Merci pour cette réactivité... bon, mais là, je suis un peu largué.

La formule 2 fonctionne parfaitement sur le fichier test, mais lorsque je cherche à l'appliquer sur mon fichier complet avec les différents onglets, je me perds.
Certainement, parce que je ne comprends pas toute la logiques des 6)-1)*3)+1, et ensuite parce que je n'arrive pas à différencier dans les noms de plages "NOMS" et "DATES" ceux qui font référence à l'onglet Agglo et ceux qui font référence à l'onglet Région.

J'ai reformaté le fichier test pour qu'il corresponde réellement à la situation et renommé les plages en fonction, dans chaque onglet.
Dans ce cas, la 308 utilisée le 15/01/10 ne devrait être comptabilisée qu'une seule fois au premier trimestre.

Je sais, c'est navrant, mais merci 1000 fois encore !!

 

[david84]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Re
Le plus simple (et le plus logique à mon avis) est de regrouper l'ensemble des infos dans un tableau bilan et à partir de là, procéder de la même manière (cf fichier onglet test).
A+

 

[Tatoufo]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Je vais effectivement simplifier le traitement de l'information.
Merci pour le coup de main.

Excellente année à toi.

 

[Tatoufo]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

OK, je suis parvenu à rassembler l'ensemble des informations sur une seule feuille et à établir une formule de calcul avec SOMMEPROD et des plages nommées "NOMS" et "DATES", mais la formule ne fonctionne que sur une plage définie (logique !) et indique une valeur d'erreur lorsque la plage comporte des cellules vides. Or les colonnes de synthèse définies par "NOMS" et "DATES" sont susceptible de contenir 10 000 lignes.

J'ai essayé de rentrer une formule dans le nom des plages pour faire varier ces plages en fonction du nombre de ligne renseignées avec une formule de type INDEX($A:$A;EQUIV("*";$A:$A;-1) mais je fais choux blanc.

Quelqu'un a-t-il une meilleure idée ?

 

[david84]

Re : Calculer le nombre de jour d'utilisation par période calendaire sans doublons

Re
Difficile de t'aider sans fichier présentant fidèlement ta problématique.
Plutôt que qu'un discours (même si les explications sont importantes), place ton exemple sur un fichier explicite avec le résultat attendu marqué manuellement.
A+