réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

[olive03]

réaliser des stats sur nbre valeurs comprise dans des périodes horaires

je butte depuis le début d'après midi sur une fonction qui pourrait me permettre d'obtenir un nombre de véhicules en circulation en fonction de leur heure de départ et d'arrivée : sur mon exemple j'ai 20 véhicules qui partent dans des plages horaires différentes et qui arrivent à des horaires différents, leur durée de trajet étant différente (mon projet est d'obtenir une feuille de calcul et un histogramme pour une flotte de 800 véhicules sur une période de 24h, mais le principe est le même sur mon exemple)

d'un fichier que l'on m'a fournit, j'ai deux colonnes avec les heures de départ et d'arrivée exprimées avec 4 chiffres (ex 1650 pour 16h50) : cellules B3:B22 dans mon exemple
Mon objectif final est de connaitre le nombre de véhicules en circulation (donc déjà partis et pas encore arrivés) par tranche horaire (cellules E24:F33 dans mon exemple) et d'en faire un histogramme qui me donnera mes pics de circulation en fonction de l'horaire (quand j'aurai intégré la bonne formule sur mes 800 circulations réelles)

J'ai tenté (en vain sinon je ne vous solliciterai pas) d'y parvenir de deux façons :
calculer d'abord le nombre des départs par tranche horaire, puis le nombre des arrivées, mais la différence ne donne pas le nombre en circulation !!!!
avec les fonctions NB SI mais pas trouvé pour mettre une valeur "comprise entre X et Y", la fonction BDNBVAL mais j'ai jamais réussi à la compléter totalement.
Bref j'ai perdu plus de 2heures avant de lancer une recherche sur Google, et trouver ce forum (je n'avais jamais pensé qu'un forum sur excel aurait pu exister.... mais je n'avais jamais butter sur une formule non plus !!!!)

Merci d'avance pour vos conseils

Olive03

 

[ninbihan]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonsoir Olive,

Peut être avec sommeprod, à tester,


Bonne soirée,

Ninbihan

 

[BERRACHED said]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Salut,olive03

je pourrais seulement te suggéré ses quelques liens :

1- Ce lien n'existe plus
2-Ce lien n'existe plus
3-Comment Faire un histogramme Excel 2007 - Cours video gratuit - Apprendre facile

Cordialement
Edite: Salut ninbihan

 

[Chelmy]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Salut.

Après avoir cogité voilà ce que je te propose. Si j'ai bien compris ton ennoncé ça devrait être pas trop mal.

En fait j'ai organisé ça sous forme de tableau, et après c'est que des combinaisons de fonctions SI, OU et ET.

Bon par contre si t'as 800 véhicules il vaudra peut être mieux mettre les vehicules en lignes et les heures en colonnes, contrairement à ce que j'ai fait.

 

[ROGER2327]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonsoir à tous.

Voici une proposition à adapter.
​

olive03 : Si cette proposition vous intéresse, on peut l'améliorer.​

Cordialement,
ROGER2327

 

[olive03]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

tout d'abord merci à vous tous pour votre intérêt sur ma problématique.
pour Ninbihan, j'avais moi aussi tenté par cette méthode, mais la différence du nombre de véhicules partis par le nombre de ceux arrivés ne donnent pas les bonnes valeurs : par exemple, entre 12 et 13h tu trouves 0 alors qu'il y a 2 véhicules en déplacement.

Pour Berrached said, ma problématique c'est pas trop l'histogramme !, c'est plutôt d'obtenir la fonction ou formule qui me donnera le nombre de véhicules en circulation à un instant T.

Pour Chelmy et Roger 2327, c'est bien ce que je souhaite arriver à faire, mais au vue de votre travail respectif, je sais que je n'y serait pas parvenu.... même pas certain de le reproduire dans une version définitive malgré vos exemples !!!

Comme tu le dis Chelmy, il vaudrait mieux mettre les véhicules en colonnes, c'est le cas dans mon fichier réel (il n'y a pas 800 véhicules mais 800 lignes, les jours de circulation sont différents d'un véhicule à l'autre, et je gère quotidiennement par filtre automatique sur les jours de circulation : il y a une colonne en plus dans laquelle est mentionné les jours de circulation, j'applique un filtre sur le samedi par exemple, et n'apparaissent que les véhicules roulant ce jour)
Pour Roger2327, votre proposition est proche de mes attentes, avec trop de détails, je n'ai besoin que d'une précision au 1/4 d'heure près, et aussi, le graphique en histogramme de Chelmy correspond à ce que je veux au final : les tranches horaires par 1/4 d'heure en abscisse, et le nombre de véhicules en ordonnées, le but étant de voir les "pics" (période de pointe ou je dois mettre du personnel en poste de commandement pouvant répondre aux appels des chauffeurs = mon but final est d'adapter le personnel au poste de commandement en fonction du nombre de véhicules en circulation)

Par contre, je n'ai que un extrait du fichier avec une centaine de véhicules, ne n'ai pas encore fini de retraiter la base que l'on m'a fournit avec les 800 : sur cette feuille, il y a les heures de circulation (départ et arrivée en colonne B et C) qui peuvent être différentes des heures Entrée et Sortie (colonne D et E) qui correspondent aux heures d'entrée et sortie de ma zone géographique sur laquelle mon PC de commandement peut agir (au delà, les véhicules sont pris en charge par un autre PC) donc se sont ces heures qu'il faut prendre en compte, c'est tout mon travail de retraitement de la base en manuel, je dois déterminer l'heure à laquelle un véhicule rentre ou sort de mon champ d'action, ligne par ligne sur une base de 1200 au départ !
Par contre vous remarquerez qu'il manque des données en Entrée ou Sortie, c'est par exemple un véhicule qui sort de mon territoire dès son départ, E=S, comment intégrer ces données dans vos formule : il faut comptabiliser le véhicule pour 1 à un instant donné très bref, la formule intègrera t-elle cela ?

Edit : fichier pas passé trop lourd, j'ai donc supprimé la moitié des lignes (pour rien encore > 50Ko) puis zippé mais après suppression des lignes sans sauvegarde à la maison !!!, heureusement le fichier initial est au bureau

 

[ninbihan]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonsoir,

Ah ben j'avais pas compris comme cela...
Un nouvel essai...


Bonne soirée,

Ninbihan

 

[olive03]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Salut Ninbihan,
j'ai adapté ta fonction dans mon tableau test simplifié, qui permet avec seulement 20 valeurs de contrôler si le résultat correspond aux attentes : en fait, à moins d'avoir mal reproduit ta fonction SOMMEPROD, le résultat n'est pas celui que je recherche.

Dans le fichier joint (TestFonction XL3), ta fonction donne le nombre de véhicule partis sans tenir compte de ceux pas encore arrivés donc encore en circulation (résultats en colonne K). en colonne L j'ai mis les 4 premières valeurs calculées "manuellement" : par exemple en L6, je trouve 3 véhicules en circulation (le N°4 est parti, et les N°1 et N°3 ne sont pas encore arrivés) alors que ta fonction donne 1 véhicule en circulation....
je me trompe ou ai-je mal reporté ta fonction ???

 

[JCGL]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonjour à tous,

En I3 :

=SOMMEPROD(($B$3:$B$22<=G3)*($C$3:$C$22>F3))

Et tirer vers le bas

a+

 

[olive03]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

désolé, mais je ne vois pas la différence, et j'ai bien un écart entre les valeurs affichées, et le nombre de véhicule encore en cirulation :

1) entre 12h et 12h30 : véhicule N°1 parti = 1 véhicule en circulation
2) entre 12h30 et 13h : véhicule N°2 parti, N°1 pas arrivé = 2 en circulation, la fonction donne 1 seul !
3) entre 13h et 13h30 : véhicule N°3 parti, N°1 pas arrivé, N°2 arrivé = 2 en circulation , résultat OK
4) entre 13h30 et 14h : véhicule N°4 parti, N°1 et N°3 pas arrivés = 3 en circulation, la fonction en donne 1

Je pense que cette fonction donne bien le nombre par tranche horaire mais ne tient pas compte des tranches antérieures dans lesquelles des valeurs sont encore à considérer.

 

[JCGL]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonjour à tous,

Cela tombe bien car je crois que cela correspond à la formule...

Jette un oeil

A+

 

[JCGL]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonjour à tous,

N'apporte rien au fil pour la résolution

Ah ben sur ce coup là, je n'édite pas et je n'hésite pas...

SALUT TATIAK
Ravi de te lire, aussi, ici

A++ mon ami (Roi des Images et du Eee,arf la seringue tactile..., et grateux de première)
A+ à tous

 

[chris]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonjour

Je suis d'accord avec JCGL que je salue.
Ton problème est lié au recouvrement.
Tu devrais avoir des heures de fin de type 1229 et 1259 ou bien des heures de début de type 1301 et 1331 car il y ambiguïté dans le comptage pour ceux qui partent ou arrivent pile à l'heure de début ou fin de tranche.
Dans ce cas modifier légèrement la formule
=SOMMEPROD(($B$3:$B$22<=G3)*($C$3:$C$22>=F3))

 

[olive03]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Cela tombe bien car je crois que cela correspond à la formule...

oui mais pas à la mienne.....

effectivement JCGL, ta formule donne les bons résultats, mais je ne trouvais pas les mêmes.... alors en observant de près la cellule I3 de ton test et du mien, je viens de trouver le "bug" : j'avais intégré l'ensemble des cellules des colonnes B et C dans les 2 conditions !!! "=SOMMEPROD(($B$3:$C$22<=G3)*($B$3:$C$22>F3))" pour "=SOMMEPROD(($B$3:$B$22<=G3)*($C$3:$C$22>F3))" subtile erreur !

et là c'est OK, c'est bien une erreur de "retranscription", tu aurais pu me le dire directement....

Merci pour ça, j'essaye de l'intégrer dans mon "gros fichier" dès demain, et je vous tiens au courant.

 

[JCGL]

Re : réaliser un histogramme sur nbre valeurs comprise dans des périodes horaires

Bonjour à tous,
Salut Chris ,

et là c'est OK, c'est bien une erreur de "retranscription", tu aurais pu me le dire directement....

Tu crois que nous déposons des codes ou formules sans avoir testé...
Ne retranscrit pas sert toi du Copier / Coller

Si tu avais demandé une explication, tu l'aurais eu (il est vrai que j'aurais pu t'informer aussi)

Au plaisir de te lire
A+