Manipulation des dates avec Excel- temps de présence

Bonjour à tous,

je reviens vers vous avec un nouveau problème :

Je dispose d'un tableau (100000 lignes) avec des dates et heures d'entrée et de sortie de patients dans un service médical (format : 27/02/2015 19:31)

Pas trop de soucis pour manipuler ces dates : durée moyennes de séjour, temps de présence selon d'autres critères, etc ...

je bute par contre sur un problème simple en apparence :

Je veux savoir combien de personnes sont présentes en même temps, heure par heure pour un jour donnée, puis en moyenne selon les mois de l'année, par exemple.

Par exemple : je voudrais une commande pour savoir combien de patients sont présente le 27/02/2015 entre 19h et 20h. Puis je me servirais de ça pour moyenner toutes les périodes de 19 à 20h sur l'année, ou une moyenne de tout les vendredi de 18 à 19h, etc ...

La ou je bute, c'est en fait pour créer un deuxième tableau avec en ligne toutes les heures et en colonne le nombre de patients présents.

Avez-vous des idées ??

Merci encore à la communauté !

PS : Je joint les 1000 premières lignes en Feuille1 à titre d'exemple, le tableau que j’aimerais remplir est en feuille 2

Bonjour,

Formule de base assez simple (utilisation classique de SOMMEPROD)

Tu pourras ensuite faire des regroupements par jour de semaine ou autres...

Les formules dans ce cas permettent d'obtenir rapidement un volume important de données, mais il sera judicieux de fixer les valeurs en éliminant les formules pour que le classeur ne devienne pas rapidement trop lourd (recopie du tableau sur lui-même en valeurs).

NB- Pour le calcul, je reste de partisan de formules, par contre si ensuite il s'agit de transposer ces résultats sous forme de listes (mieux adaptées à des études statistiques), là je pense qu'une macro s'impose pour le faire mieux, sans erreur et plus vite.

Bonne continuation.

Bonjour à tous,

compte tenu que les durées sont quasi-similaires au pas de calcul que tu proposes, il y aura écrêtage

si tu veux avoir une valeur plus pure, tu peux faire le calcul à chaque fois qu'une personne rentre > inclus dans le tableau

tu peux ensuite faire un TCD et voir la moyenne jour par jour et heure par heure

Houlà !

Le problème d'"écrêtage ne se pose pas dans ce type de phénomène !

Mais Steelson fait bien de capter mon attention : La formule que j'ai donné calcule le nombre de personnes dans le "circuit" à intervalles réguliers. Le choix de l'heure par Aytan m'a paru tout à fait judicieux au survol de ses chiffres (la durée moyenne de "séjour" étant visiblement supérieure à 1 heure, un intervalle plus large aurait pu s'avérer tout aussi valable).

Un autre paramètre est cependant à prendre également en considération : le nombre d'entrées dans le "circuit" par unité de temps, qui sera également l'heure.

[La formule est assez proche de la précédente (mais résultats différents), donc facile à adapter à partir de la première. Je rappelle ces formules :

=SOMMEPROD((HeureEntrée<=Heure0)*(HeureSortie>Heure0)) fournit donc le nombre de personnes dans le "circuit" à Heure0 (on le calcule par intervalles réguliers de une heure.

=SOMMEPROD((HeureEntrée>=Heure0)*(HeureEntrée<Heure1)) fournit le nombre d'entrée entre Heure0 et Heure1 (espacées de une heure).

Ces paramètres sont nécessaires et suffisants pour une étude statistique d'évènements de cet ordre, dont on sait qu'ils ne sont pas ajustables à une loi normale, que la moyenne (arithmétique) n'y revêt pas la même importance et que le mode constitue une mesure significative [NB- il y a 3 mesures de tendance centrale d'une distribution statistique : la moyenne, la médiane et le mode].

Les 2 paramètres calculés permettront de définir si les observations permettent d'ajuster le fonctionnement de l'activité à une loi de Poisson, offrant alors un modèle statistique prévisionnel.

Cordialement

Ferrand

Merci, c'est génial, ça marche ! Ce sont des chiffres parfaitement cohérent

MFerrand a écrit :

Houlà !

Bonjour MFerrand

15% des visites font quand même moins d'une heure !

Après tout dépend de l'objectif ultime

En réalité, même si parfois j'applique un pas de 15mn ou 30mn, je suis curieux de reprendre le sujet en calculant à chaque entrée comme je l'avais fait mais aussi à chaque sortie, bref à chaque mouvement.

Salut Steelson,

Je maintiens : 15% c'est très peu dans ce cas, 85% sont au-dessus et il y en a quelques-uns qui dépassent la journée. Les cas extrêmes n'auront que peu de poids, on aurait pris des intervalles de 2 heures, les résultats les résultats d'étude seraient assez semblables. Mais 1h semble un bon compromis pour affiner éventuellement aux périodes de fréquentation plus dense.

En comptabilisant les entrées par plage horaire, qu'il faut avoir bien sûr, on ne perd personne, et on peut déduire si besoin ceux qui sont entrés et sortis dans l'heure.

On est dans la même problématique que sur les études de files d'attente, pannes de machines, etc. On est sur de petits nombres et je parierai dans ce cas qu'on dégagera au moins 2 périodes distinctes, une où l'approche statistique par la loi de Poisson pourra être validée et une autre où ce sera trop lache, où les fluctuation aléatoires prennent le dessus...

Les fluctuations saisonnières et sur la semaine, comme prévoit de les étudier Aytan sont aussi des éléments importants dans l'analyse de ce type de phénomènes où les objectifs principaux visés sont régulation et optimisation.

Une étude mouvement par mouvement ne t'apprendras rien de plus. Les évènements observés ne sont pas interdépendants et ne se distribuent pas sur une courbe "en cloche". Il faudrait que je réexhume mes cours (de je ne sais où ?) mais je me souviens avoir autrefois trouvé assez passionnant ce type d'études où l'on peut faire des simulations en tenant compte d'analyses comportementales pour distinguer les cas appréhendables statistiquement de ceux qui dérogent aux règles de probabilité présupposées par cette approche...

Bon, rien n'empêche de faire aussi des statistiques globales mais elles n'auront pas le même intérêt, on n'est ni dans la production, ni dans des flux financiers...

A+

Merci pour vos remarques, c'est tout à fait pertinent !

L'idée est d'avoir une vision "instantanée" de la charge du service pour savoir à quels moments de la journée (et de la semaine, de l'année, etc ...) on dépasse la capacité d'accueil, afin de répartir les moyens de façon plus optimisée.

Effectivement, mettre un pas de 15mn, 10 mn, etc ... serait logique, mais j'ai eu peur de surcharger le truc (déjà qu'Excel a mis 10 bonnes minutes à remplir complètement le tableau par heure ...)

Merci encore !

MFerrand a écrit :

On est dans la même problématique que sur les études de files d'attente, pannes de machines, etc. On est sur de petits nombres et je parierai dans ce cas qu'on dégagera au moins 2 périodes distinctes, une où l'approche statistique par la loi de Poisson pourra être validée et une autre où ce sera trop lache, où les fluctuation aléatoires prennent le dessus...

Pannes machine = loi de Weibull (suffisamment souple et paramétrable pour prédire le comportement)

Globalement tu as sans doute raison, mais pour valider complètement le modèle et faire apparaître des facteurs insoupçonnés au début d'une analyse, j'aime bien titiller les chiffres et les retourner dans tous les sens. C'est un peu mon côté fouineur.

Mais dans le but de regarder globalement si la capa permet de passer la charge, on peut aussi en prendre le P95 de la distribution et dans ce cas la finesse que je recherche est superflue.

Sujet et débat intéressant !

Il est vrai que à l'époque de mes cours (qu'il faudra que j'essaie de retrouver en métropole car je ne les ai pas avec moi semble-t-il), tous les calculs étaient supposés manuels et les choix préalables d'échantillonnage pouvaient faire varier la masse de calculs à faire d'un nombre conséquent d'heures. Je m'étais doté d'un avantage indéniable avec une calculette statistique qui me faisait gagner la moitié du temps sur les calculs intermédiaires...

Mais une étude de files d'attente avec prise de données à intervalles de 4 minutes (opérations courantes inférieures à 2 minutes) représentait déjà un bon volume sur une seule journée et un seul guichet...

Ici l'avantage est qu'on dispose de données entrées-sorties enregistrées, et sur ordinateur, qu'on peut donc facilement traiter pour extraire les données utiles. Mais sur un cycle continu de 24 heures, sur 3 ou 4 ans cela fait tout de même un volume considérable.

Je pense que l'intervalle horaire est suffisant dans une première approche et la simple mise en graphique de l'"alimentation" et des "présents" fera apparaître si certaines plages justifient une appréciation sur des intervalles inférieurs à l'heure, d'autant que l'étude ne serait justifiée que si il apparaît justifié de prendre des mesures dans ces intervalles, donc d'être en capacité d'être réactif à cette échelle. Etant sur des chiffres qui excèdent peu 5 entrées à l'heure (en pointe), cela relève plus d'éléments d'environnement ( à savoir si des problèmes récurrents apparaissent à certaines heures qui réclament qu'on recherche la source de difficultés) que des chiffres bruts.

Cordialement

A+ (mais faut que je limite mes réponses, car je n'arrive plus à absorber les mails retours dans un délai raisonnable )

@Aytan :

beau sujet sur lequel j'ai continué à travaillé

une synthèse intéressante sur cet échantillon de valeurs = on remarque une pointe de présents le mardi et jeudi apm particulièrement, il faudrait savoir si c'est l'afflux de personnes, ou bien une réduction de la capacité d'accueil .... en tous cas la manipulation des chiffres est intéressante !

version excel 2007