Calcul de simultanéités d'alertes et effectifs engagés

[nomex]

Bonjour le Forum,

J'ai souvent parcouru les pages de ce site à la recherche de solutions, partant du principe que d'autres personnes avaient sans doute été confrontées aux mêmes problèmes. Et jusqu'à présent cela a bien fonctionné et m'a permis d'apprendre rapidement notamment des bases de VBA. Cette fois-ci je sèche, aussi j'invoque vos lumières...

Prenons depuis le début. Je travaille dans un centre de secours (ou une caserne de sapeurs-pompiers, si vous préférez...). Entre autres tâches, j'établis des statistiques afin de suivre notre activité opérationnelle. Un des indicateurs qui m'intéressent concerne le taux de simultanéité des interventions, et le nombre de personnes engagées en même temps sur opérations. En gros, pour chaque minute de la journée, je souhaite connaître le nombre X de pompiers engagés sur Y interventions, sachant que mon effectif de garde est composé de Z pompiers.

J'ai adapté une réponse trouvée sur le forum pour un problème de simultanéité d'appels téléphoniques (https://www.excel-downloads.com/thr...mmunications-telephonique-simultanees.163457/). J'obtiens un indicateur intéressant, qui me permet de connaître le temps passé par jour avec X interventions simultanées, mais cela ne me dit pas combien de fois cette situation se répète dans une journée et l'impact que cela représente sur l'effectif de garde... et c'est là que mes connaissances et mon expérience d'Excel sont trop limitées pour réussir à entrevoir le début d'un soupçon de réponse. Je soupçonne du SOMMEPROD quelque part, mais sans plus de conviction...

Je joins un fichier avec des données sur un mois pour vous éclairer sur la démarche.

D'avance merci pour votre aide.

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonsoir

Peut être avec ça.

J'ai utilisé cette méthode il y a trois ou quatre années de cela, mais je ne suis plus trop certain de la formule. Dès que je peux, je reviens vérifier tout cela. Il me semble même qu'il y a peut être moyen de faire sans colonnes intermédiaires.

A vérifier donc.

@ plus

 

[nomex]

Re : Calcul de simultanéités d'alertes et effectifs engagés

YEEEEEEEEEEEEEEESSSSSSSSSSSSSS ! Oui, je sais c'est pas bien de crier !

J'ai vérifié à la main sur l'échantillon du 1er janvier et ça colle. Il me reste à :
1 : décortiquer les formules pour comprendre et intégrer le principe ;
2 : recopier l'effectif de la garde correspondant en regard de chaque cumul d'effectifs engagés pour en extraire le taux de sollicitation de la garde à ce moment précis.

En 2 mots : Merci Maître !

@+

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonsoir

La suite, si cela peut t'aider.

Un petit problème qu'en même : Comme il y a des formules matricielles, cela risque de prendre pas mal de temps de calcul si tu fais un fichier annuel. Dans ce dernier cas, il faudra peut être passer par une macro.

 

[JBOBO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonjour nomex, cisco,

A priori les formules matricielles de l'exemple peuvent etre remplacer par sommeprod, ce qui evite la validation par ctrl+maj+entree. je ne sais pas si ça joue sur la lourdeur du fichier.
on aurait donc en j2

=SOMMEPROD((F$2:F$510<=I2)*(G$2:G$510>=I3);(D$2$510))

ET EN P2

SOMMEPROD((J$2:J$510=N2)*K$2:K$510)

, mais j'avais cherché une solution et sans la réponse de cisco, je n'aurais pas trouvé le sommeprod.

 

[nomex]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonjour Cisco et JBOBO,

Merci de vos contributions respectives. Je testerais cela demain au bureau et je vous tiens au courant.

@+

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonjour à tous, bonjour nomex et JBOBO

@ JBOBO. Merci pour ces deux simplifications. En P2, j'avais effectivement essayé avec

SOMMEPROD(J$2:J$510=N2;K$2:K$510)

mais comme cela ne fonctionne pas, je suis passé par un SOMME(SI... en matriciel. En fait,avec SOMMEPROD, il faut écrire :
soit ta proposition

SOMMEPROD((J$2:J$1020=N2)*K$2:K$1020)

soit

SOMMEPROD((J$2:J$1020=N2)*1;K$2:K$1020)

@ plus

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Rebonjour

Mes propositions en post 2 et 4 n'étaient pas nickel :
* La longueur des plages à utiliser n'était pas toujours bonne. Certaines fois, il faut écrire 1019 ou 1020 au lieu de 510.
* De plus, il me semble qu'il peut y avoir un problème lorsqu'une date+heure se retrouve plusieurs fois dans la liste des débuts-fins d'intervention (cf. cellules en rouge dans le fichier ter en pièce jointe).

Pour résoudre cela, deux possibilités :
* Ne rien écrire en colonne J lorsqu'une date+heure se retrouve deux fois dans la liste (cf. fichier ter en pièce jointe)
* Faire une liste des dates+heures sans doublon en colonne I (Cf. fichier ter suite en pièce jointe). Malheureusement, cela réintroduit une formule matricielle, en colonne I, qui prend beaucoup de temps de calcul.

Sans colonne intermédiaire, on n'aurait pas ce problème !

@ plus

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonjour

Encore une modification à faire. En G2, il faut écrire

SI(B2<=C2;A2+C2;A2+C2+1)

au lieu de

SI(B2<C2;A2+C2;A2+C2+1)

et tirer cette formule ensuite en dessous.

Il faut mettre <= au lieu de <, pour ne pas avoir de mauvaise valeur en colonne G (1 jour en plus alors qu'il ne le faut pas) en cas d'erreur de saisie, comme en ligne 255 (même heure de début et de fin d'intervention).

J'ai corrigé dans les deux fichiers ci-dessus.

@ plus

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Rebonjour

Ci-joint, deux fichiers avec des listes permettant de supprimer certaines colonnes intermédiaires.
* Dans le premier, tout, pour pouvoir comparer les résultats obtenus avec les deux méthodes, avec (en noir) et sans colonne intermédiaire (en rouge) mais en utilisant les noms listeAB, listeAC et listecomplète.
* Dans le second, uniquement avec les noms listeAB, listeAC et listecomplète.

Attention :
*il faut mettre la colonne des durées totales, à droite, au format jj:hh:mm et non en hh:mm (certaines durées sont supérieures à 1 j. Si on utilise le format hh:mm, on n'affiche pas le nombre de jour. 1 jour 3 h et 15 min donnerait uniquement 03:15).
* Dans l'état actuel, les formules avec les listes ne fonctionnent correctement que si les données sont en colonnes A, B et C.

Nomex, pourrais tu nous dire où tu en es, ce qui convient, ne convient pas...?

Je vois au moins une amélioration à faire, à savoir, utiliser des plages dynamiques pour que les formules s'adaptent à la longueur de ton fichier réel. Chaque mois, tu n'auras pas juste 510 lignes. Cela serait qu'en même plus pratique si tu n'avais pas besoin de changer les 510 ou les 1020 dans les formules et les noms, à chaque fois.

@ plus

 

[nomex]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonjour,

Encore merci pour vos contributions. Je viens juste de lire vos messages, ayant été privé d'Internet toute la journée.
Je teste de suite et je vous tiens au courant.

@+

 

[nomex]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Rebonjour,

Le fichier test_simu liste complet.xls me convient tout à fait en l'état. En colonne J, je place l'heure de fin du créneau correspondant à l'effectif engagé (simple copier-coller depuis la colonne I en décalant d'une cellule vers le haut) afin de faciliter la lecture.

Bien vu pour les cas où l'heure de fin d'une alerte correspond à l'heure de début d'une suivante. Ce sont les cas pour lesquels un engin est réengagé sur une opération alors qu'il est en retour vers le centre. Sans modif, l'effectif était en effet compté une fois de trop.

Ligne 255 : ce n'est pas une erreur de saisie. Il s'agit d'une alerte arrivée au centre mais annulée immédiatement, donc pas d'engagement de personnel. Je vais rajouter un tri pour éliminer les cas où Heure Alerte = Heure Fin afin de résoudre le problème.

En exploitation définitive, je pense utiliser un fichier avec un onglet par mois, puis une synthèse de l'année. Effectivement le nombre d'interventions est variable d'un mois sur l'autre et l'utilisation de plages dynamiques semble plus adapté. Je ne l'ai encore jamais utilisée mais c'est bien la fonction DECALER ?

Encore merci de votre aide.
@+

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonsoir

Rebonjour,

Ligne 255 : ce n'est pas une erreur de saisie. Il s'agit d'une alerte arrivée au centre mais annulée immédiatement, donc pas d'engagement de personnel. Je vais rajouter un tri pour éliminer les cas où Heure Alerte = Heure Fin afin de résoudre le problème.

Encore merci de votre aide.
@+

Quand j'écris erreur, c'est une façon de parler... Le tri n'est pas obligatoire avec <= à la place de <. De plus, comme la durée pour ces lignes là est de 00:00, cela ne fausse pas la durée totale (par contre cela faussait le nombre de moments avec tant ou tant d'intervenants).

Et oui, pour ce qui est des plages dynamiques, on peut faire avec la fonction DECALER. Je n'ai pas le temps tout de suite, alors je te laisse chercher un peu

@ plus

 

[nomex]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonsoir

Et oui, pour ce qui est des plages dynamiques, on peut faire avec la fonction DECALER. Je n'ai pas le temps tout de suite, alors je te laisse chercher un peu

@ plus

Eh ben tant mieux, c'est comme ça qu'on apprend ! ... mais pour l'heure pas le temps non plus, je verrais cela demain...

@+

 

[CISCO]

Re : Calcul de simultanéités d'alertes et effectifs engagés

Bonjour

Trois fichiers avec des plages dynamiques, le fichier complet regroupant les résultats des deux autres. A toi de choisir celui qui te convient le mieux .

Pour obtenir des plages dynamiques, on peut utiliser des formules du style :

=DECALER(Données!$M$2;;;EQUIV(9^9;Données!$M:$M;1)-1;1)

, si la plage ne contient que des nombres, des dates...
et

=DECALER(Données!$M$2;;;EQUIV("zzz";Données!$M:$M;1)-1;1)

, si la plage ne contient que du texte.

Sur les fichiers, ne pas oublier, si besoin est, de copier-coller les formules plus vers le bas, en dessous des lignes 510 et 1020 environ.

@ plus