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

 

[ROGER2327]

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

Bonjour à tous !

Voilà un sujet qui fait cogiter, apparemment ! Je vais tenter d'y apporter une réponse possible sous forme d'un classeur de test.​

Pour des raisons de taille de fichier, j'ai écrit une procédure d'initialisation qui ne devrait être utilisée qu'en phase de test. Si le classeur doit être utilisé "en vrai", il conviendra de supprimer le Module1 ou, au moins, de neutraliser son contenu.​

MODE D'EMPLOI​

​

• Décompactez 040_Test fonction XL-4.zip et conservez-le en cas de fausse manœuvre.
• Ouvrez 040_Test fonction XL-4.xls et exécutez la procédure DEBALLAGE : Le tableau de la Feuil2 est chargé de données de test que l'on peut renouveler à volonté en utilisant la touche F9, pour autant que l'option de recalcul automatique soit activée (Outils/Options.../Calcul/Automatique).
• La Feuil1 fournit la collecte des informations et une représentation graphique de ces informations. Le tracé gris transcrit les données des colonnes D et E de la Feuil2 tandis que le tracé orange transcrit les données des colonnes B et C.
  Pour l'usage "normal", il suffira de supprimer les formules des susdites colonnes et de les remplir avec les données horaires convenables.
• La colonne F de la Feuil2 contient un codage des jours de circulation des différents véhicules ; 1 représente lundi, 2, mardi, ...7, dimanche. 167 signifie donc que le véhicule circule lundi, samedi et dimanche ; 176, 671, etc. signifient la même chose.
• Pour obtenir le recensement d'un jour donné sur la Feuil1, il suffit d'indiquer ce jour (1, 2, ...7) en tête de la colonne F.
• La colonne G de la Feuil2 prend la valeur de la cellule F1 dans les lignes requises pour le jour correspondant : un petit coup de filtre automatique permet alors de ne voir que les données du jour correspondant.
• La colonne H est parfaitement inutile. Elle contient en l'état ce qui pourrait être des numéros minéralogiques, sans doublon. Les curieux peuvent aller voir la procédure A_COD...

Note : J'ai admis qu'un véhicule au départ à, par exemple, 10 h 00 est comptabilisé comme roulant à 10 h 00, alors qu'un autre arivant à 10 h 00 n'est pas comptabilisé. C'est affaire de pure convention, et il n'est pas difficile d'en adopter une autre.​

J'espère que ces commentaires sont suffisants pour comprendre le fonctionnement du classeur. Si je me trompe, n'hésitez pas à me le faire savoir.​

Bonne nuit !
ROGER2327

 

[JCGL]

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

Bonjour à tous,

Joli travail !

J'aime beaucoup le choix du jour (ou des jours).

Au plaisir de te lire
A+ à tous

 

[olive03]

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

bonjour Roger2327, et merci de vous pencher sur mon sujet.
je ne dois pas savoir faire votre procédure, ou bien je ne comprends pas tout (le niveau est trop élevé pour moi !)
qu'entendez-vous par

j'ai écrit une procédure d'initialisation qui ne devrait être utilisée qu'en phase de test. Si le classeur doit être utilisé "en vrai", il conviendra de supprimer le Module1 ou, au moins, de neutraliser son contenu.

moi après avoir dézippé votre fichier j'ai unclasseur avec 2 feuilles :
feuil 1 avec des données et fonctions en A1 : C3 et le graphique vierge
feuil 2 avec des données et fonctions en A1 : H2 uniquement

est-ce que j'ai toutes les données et ou fonction après le ZIP ???

exécutez la procédure DEBALLAGE : Le tableau de la Feuil2 est chargé de données de test que l'on peut renouveler à volonté en utilisant la touche F9, pour autant que l'option de recalcul automatique soit activée (Outils/Options.../Calcul/Automatique).

comprends pas !!!, j'ai fait une copie incrémentée de A3 de la feuille 1 jusqu'à 23h45, idem pour B3 et C3 jusqu'au niveau de 23h45. Ensuite j'ai rentré mes données (en HH:MM:SS) sur la feuil2 uniquement dans les colonnes D et E, ainsi que le Nbre de véhicules correspondant en colonne A
Le résultat me donne bien un graphique, mais je ne suis pas certain d'avoir tout compris !!!!

Si le classeur doit être utilisé "en vrai", il conviendra de supprimer le Module1 ou, au moins, de neutraliser son contenu.

Vous verrez mieux sur le fichier joint (02 Test onction) ce que donne l'intégration des données, sinon le choix des jours de circulation sera très pratique, quand j'aurai converti les données "jours de circulation" de mon fichier de base, qui lui est exprimé en Q pour quoditien, SA pour samedi, SF DF pour tous les jours sauf dimanche, etc..................
Dans le second fichier, vous trouverez la "transcription" : je pense qu'il est possible de transformer automatiquement un SF SDF LU (sauf samedi, dimanche et lundi) en 2345 ?
Ce fichier plus simple est réalisé à partir de SOMMEPROD (merci JCGL) avec mes véritables données.

 

[ROGER2327]

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

Bonsoir à tous...

...et spécialement à olive03 pour qui je vais essayer d'être plus précis.​

... j'ai écrit une procédure d'initialisation qui ne devrait être utilisée qu'en phase de test.

Je veux dire que pour tester le classeur — et pour vous permettre d'en faire autant — j'ai écrit des formules pour remplacer la saisie manuelle des heures de départ, d'entrée en zone, de sortie de zone et d'arrivée dans les colonnes B à E de la feuille Feuil2. Mais, ayant procédé à des essais sur 1 500 lignes, je ne pouvais vous envoyer le classeur tel quel, sa taille approchant les 500 Ko. Je n'ai donc laissé que les premières lignes de chaque feuille, et j'ai écrit une procédure, quelquefois nommée "macro", pour reconstituer le fichier original. Je l'ai écrite en VBA, i.e. Visual Basic for Application®©. Pour voir ce qu'est cette procédure, il faut ouvrir l'éditeur de macros soit avec la combinaison de touches Alt F11, soit en sélectionnant Outils/Macro/Visual Basic Editor. Mais vous n'avez pas besoin de faire cela pour éxécuter une procédure : il suffit d'utiliser la combinaison de touches Alt F8 (ou Outils/Macro/Macros...) pour afficher la liste des procédures disponibles. Dans la liste, sélectionner DEBALLAGE, puis Exécuter. Le classeur se remplira d'une série de valeurs de test. Vous pourrez alors voir sur la feuille Feuil1 différents exemples

• en utilisant la touche F9 pour renouveler les valeurs de la feuille Feuil2 ;
• en faisant varier de 1 à 7 la valeur de la cellule F1 de la feuille Feuil2.

Pour l'usage "normal", il suffira de supprimer les formules des susdites colonnes et de les remplir avec les données horaires convenables.

Maintenant, cela devrait être clair : lorsqu'on a fini de tester le classeur, on va l'utiliser. Pour ce faire, supprimez les formules des colonnes B à E de la feuille Feuil2 et remplacez-les par vos propres données.​

Voila pour l'essentiel... Mais je ne peux entreprendre un cours sur l'utilisation élémentaire d'Excel®©. Si vous voulez vraiment progresser et comprendre Excel®©, je ne saurais trop vous conseiller de recourir à l'aide fournie par Microsoft®© (la touche F1 est une touche miraculeuse) et à examiner en détail les innombrables contributions de ce site.​

Enfin, pour ce qui est de vos classeurs, je n'aurai pas le temps de les étudier dans les heures qui viennent : à plus tard, donc.​

Bon courage,
ROGER2327

 

[olive03]

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

Merci Roger2327, pour ces explications "élémentaires", le ne suis pas habitué aux macros et VBA, maintenant, je peux enfin admirer votre travail.... bluffant !

Deux points me chagrinent (Edit fichier 61ko ne passe pas en PJ) :

- alors que j'ai rentré environ 700 véhicules (uniquement en heure d'entrée et heure de sortie), le graphique ainsi que la colonne C de la feuil 1 donne des valeurs négatives jusqu'à 5h : pourtant j'ai bien l'impression qu'il n'y a pas d'erreur dans ma base (copiée/collée), chaque sortie est bien supérieure à l'heure d'entrée, il ne devrait pas y avoir de valeurs négatives (vérif faite par sondage)

- l'idée géniale du choix du jour de circulation ne fonctionne pas comme je pourrais en avoir l'utilité :
en saisissant une valeur en F1, l'idéal serait que tous les véhicule qui ont cette valeur s'affichent, y compris pour une série de jours : par exemple, je souhaite voir quels véhicules seront sur la route du mardi au vendredi, en saisissant 2345, donc il serait bien que tous les véhicules qui circulent seulement en 2 ou en 3 s'affichent aussi, et pas uniquement ceux qui circulent ces 4 jours.
ceux circulant que le mardi et le mercredi doivent aussi compter dans la base.
Aussi, le graphique ne tient pas compte de cette requête, ça serait évidemment l'idéal si le schéma tenait compte de la requête en F1....

Mais surtout chapeau pour cette démonstration, et désolé pour mes méconnaissances dans les macros !
Vous m'avez été d'une aide précieuse, alors que j'ai passé hier toute ma journée à "nettoyer" ma base, je vais intégrer cette dernière dès demain dans votre classeur.
Pour les graphiques en fonction du jour de circulation choisi, j'incorporerai dans votre classeur ma base que j'aurai préalablement triée par filtre automatique depuis mon fichier à moi.

Merci beaucoup pour le temps que vous m'avez tous consacré.

 

[ROGER2327]

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

Bonsoir.

Merci d'avoir porté attention à mon classeur. Je suis bien heureux d'avoir été assez clair. Mais je vais devoir vous décevoir : mon travail ne permet en effet pas de répondre à votre nouvelle problématique de gestion simultanée de plusieurs jours.​

J'ai conçu ce classeur dans l'esprit d'économiser le travail de saisie en décrivant l'ensemble de la flotte de véhicules sur la même feuille Feuil2. Ceci étant fait, je pensais que chaque jour vous deviez renouveler les données. Pour ce faire j'imaginais que vous vidiez la feuille de ses horaires (quitte à en faire préalablement une copie pour servir de base à la saisie de la semaine suivante), puis que vous sélectionniez le jour en F1, et qu'enfin vous entriez les données pour ce seul jour. En effet, la page "graphique" prend en compte toutes les données de la feuille Feuil2 et les données des autres jours que le jour considéré sont susceptible de fausser le résultat.​

On peut certainement faire ce que vous attendez (avec un tableur, on peut tout faire excepté ce qu'on ne peut pas, aurait dit le regretté Bobby Lapointe...). Mais c'est une autre paire de manches, et il faudrait repenser l'ensemble du travail. Je crains de ne pouvoir m'y coller ces jours-ci. Désolé.​

Bon courage pour la suite,
ROGER2327

 

[olive03]

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

Je vais régler ce problème immédiatement en incorporant dans la feuil2, uniquement les données du jours donné, par un filtre sur ma base.

Pas très "professionnel" pour vous tous, mais ce qui compte c'est le résultat, donc j'obtiendrai un graphique du nombre des circulations par requette.

Par contre, pourquoi ces valeurs négatives jusqu'à 5h ???

 

[ROGER2327]

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

A Olive03

Je ne vois pas bien l'origine des valeurs négatives que vous signalez. Je n'ai pas le temps de creuser cette semaine (je suis en plein déménagement), mais j'aimerais que vous me teniez au courant de l'évolution de votre travail. Merci d'avance.

A bientôt,
ROGER2327