Planning d’affectation des équipes chantier Excel

Ce que ce tableau aide à suivre

• Affecter nominativement un intervenant à une date, un créneau, un chantier, une zone et une tâche.
• Repérer les chevauchements horaires et les affectations placées pendant une indisponibilité enregistrée.
• Lire, pour la Date de pilotage, le nombre d’affectations actives, les heures planifiées et les alertes à traiter.
• Vérifier qu’une zone est active pour le chantier sélectionné et que la date se situe dans sa période déclarée.

Contenu du classeur

Commencer à l’utiliser

1. Réglez la période de travail

Dans Paramètres, renseignez Date de pilotage, Début de journée, Fin de journée, Date minimale de saisie et Date maximale de saisie. Ces valeurs déterminent la date résumée en haut de Synthèse et les bornes admises pour les dates et horaires.

2. Remplacez les données de démonstration

Dans Intervenants, renseignez ID intervenant, Nom affiché, Équipe, Métier et Actif. Dans Chantiers_Zones, saisissez ID chantier, Chantier, Zone, Actif, Date début et Date fin. Conservez les formules de Clé ainsi que les formules des colonnes G à I de Paramètres.

3. Enregistrez les absences connues

Dans Indisponibilités, choisissez ID intervenant, puis renseignez Date début, Heure début, Date fin, Heure fin et Motif. Vérifiez notamment les indisponibilités couvrant plusieurs jours.

4. Saisissez puis contrôlez les affectations

Dans Synthèse, complétez dans l’ordre ID affectation, Date, Heure début, Heure fin, ID intervenant, ID chantier, Zone, Tâche, Statut, Priorité et Commentaire. Ne remplacez pas les calculs de Durée (h), Nom affiché, Équipe, Chantier, Conflit horaire, Indisponibilité, Cohérence chantier-zone, Période chantier et Alerte globale. Avant diffusion, filtrez Alerte globale et traitez chaque résultat différent de OK.

Contenu du classeur et rôle de chaque feuille

Le fichier repose sur cinq feuilles complémentaires :

• Synthèse reçoit jusqu’à 200 affectations, des lignes 7 à 206. Les cellules de saisie décrivent le créneau et l’intervention ; les autres colonnes calculent la durée, les libellés et les contrôles.
• Intervenants contient jusqu’à 100 fiches avec ID intervenant, Nom affiché, Équipe, Métier et Actif.
• Chantiers_Zones contient jusqu’à 100 associations entre ID chantier et Zone, avec Chantier, Actif, Date début et Date fin. La Clé est calculée à partir de l’identifiant et de la zone.
• Indisponibilités enregistre jusqu’à 100 périodes d’absence ou de non-disponibilité.
• Paramètres fixe la Date de pilotage, la plage journalière, les dates de saisie autorisées ainsi que les listes de Statuts et de Priorités.

Les valeurs présentes à l’ouverture sont des données de démonstration. Remplacez-les par vos identifiants et périodes, sans écraser les colonnes calculées.

Calcul des durées, indicateurs et alertes

Durée (h) applique la logique =IF(OR(C7="",D7=""),"",IF(D7>C7,(D7-C7)*24,"")) : le résultat reste vide si une heure manque ou si Heure fin n’est pas postérieure à Heure début. Une ligne de 08:00 à 12:00 produit donc 4,00 h.

Les indicateurs de Synthèse dépendent de Date de pilotage. COUNTIFS compte les Affectations actives non Annulée, SUMIFS additionne leurs durées et un autre COUNTIFS dénombre les lignes dont Alerte globale diffère de OK. Si Date de pilotage est vide, ces indicateurs restent vides. S’il n’existe aucune affectation à cette date, Affectations actives renvoie 0, tandis que Heures planifiées et Alertes à traiter restent vides.

XLOOKUP récupère Nom affiché, Équipe et Chantier. IFERROR transforme un identifiant non trouvé en « ID inconnu » ou « CHANTIER INCONNU ». Lorsque l’identifiant source est vide, la formule extérieure IF laisse également le résultat vide. Dans Paramètres, IFERROR renvoie une cellule vide une fois la liste des enregistrements actifs épuisée : ce vide ne prouve donc pas qu’une personne est disponible.

Conflit horaire utilise COUNTIFS pour repérer deux créneaux qui se recouvrent le même jour pour le même ID intervenant. Indisponibilité emploie SUMPRODUCT pour comparer les intervalles. Cohérence chantier-zone vérifie une association marquée Oui, tandis que Période chantier contrôle que Date est comprise entre Date début et Date fin, bornes incluses.

Cas pratique : traiter un chevauchement de créneaux

Dans les données de démonstration, AFF-001 affecte INT-001 le 08/09/2026 de 08:00 à 12:00 sur DEMO-A, Zone Nord, pour la tâche Installation. AFF-002 utilise le même ID intervenant le même jour de 10:00 à 14:00.

Les deux durées calculées sont de 4,00 h. Tant que AFF-002 n’est pas Annulée, les créneaux se recouvrent entre 10:00 et 12:00 : Conflit horaire doit afficher DOUBLE RÉSERVATION. Si Date de pilotage reste fixée au 07/09/2026, ces lignes du 08/09/2026 ne sont pas reprises dans les indicateurs supérieurs.

Exemple de décision : après vérification des contraintes réelles, déplacez AFF-002 de 12:00 à 16:00 ou affectez un autre ID intervenant. Le premier choix conserve 4,00 h sur cette ligne et supprime le chevauchement, car un créneau commençant exactement à 12:00 ne recouvre pas celui qui se termine à 12:00. Conflit horaire peut alors passer à OK. Alerte globale ne passera toutefois à OK que si les champs requis, l’indisponibilité, la zone, la période du chantier et la plage journalière sont également valides.

Erreurs courantes et corrections sûres

Préservez les formules des colonnes calculées et procédez à partir du symptôme affiché :

• ID inconnu ou CHANTIER INCONNU : l’identifiant saisi n’existe probablement pas dans Intervenants ou Chantiers_Zones. Vérifiez l’orthographe, l’unicité de l’ID et la valeur Actif, puis choisissez l’entrée proposée par la liste. Nom affiché, Équipe ou Chantier doit ensuite se renseigner.
• ZONE INVALIDE : la zone peut être active, mais associée à un autre chantier. Recherchez le couple ID chantier et Zone dans Chantiers_Zones, vérifiez que Actif vaut Oui, puis corrigez l’un des deux champs. Cohérence chantier-zone doit passer à OK.
• HORS PÉRIODE : Date se trouve avant Date début ou après Date fin du chantier retrouvé. Vérifiez les dates de référence avant de déplacer l’affectation ou de corriger la période. Période chantier doit alors afficher OK.
• INDISPONIBLE : une période d’Indisponibilités recoupe le créneau. Contrôlez ID intervenant, dates et heures des deux lignes ; corrigez une erreur de saisie ou réaffectez l’intervention. Le contrôle doit revenir à OK lorsque les intervalles ne se chevauchent plus.
• DOUBLE RÉSERVATION : deux affectations non Annulée du même intervenant se chevauchent à la même date. Filtrez ID intervenant et Date dans Synthèse, puis décalez, réaffectez ou annulez la ligne concernée. Le résultat attendu est OK sur les créneaux devenus distincts.
• HORAIRES INVALIDES ou HORS PLAGE : Heure fin est inférieure ou égale à Heure début, ou le créneau dépasse Début de journée et Fin de journée. Corrigez les heures ; pour un travail passant minuit, utilisez deux lignes datées plutôt qu’un créneau continu. Durée (h) et Alerte globale doivent ensuite se recalculer.

Comparaison avec un plan de charge ou une feuille vide

Ce planning répond à une décision opérationnelle : affecter une personne précise à un créneau précis. Un plan de charge générique sert plutôt à mesurer un besoin de capacité, tandis qu’une feuille vide laisse toute la structure à construire. La différence tient donc moins au format Excel qu’au niveau de décision attendu.

Choisissez ce fichier si vous devez produire des affectations nominatives et contrôler régulièrement leurs incohérences. Préférez un plan de charge si la question porte d’abord sur le volume de ressources nécessaire. Une feuille vide reste plus adaptée si vos créneaux ou règles ne correspondent pas aux champs et contrôles proposés.

Adapter les références et les règles de saisie

Commencez par remplacer les personnes, équipes, métiers, chantiers et zones fictifs. Utilisez des ID stables : les listes et les recherches reposent sur ID intervenant et ID chantier. Une même zone textuelle peut appartenir à plusieurs chantiers, mais chaque association attendue doit disposer de sa propre ligne active dans Chantiers_Zones.

Les réglages fournis autorisent des dates du 01/01/2026 au 31/12/2035 et une journée de 07:00 à 19:00. Vous pouvez modifier ces bornes dans Paramètres avant la saisie. Vérifiez ensuite que les dates déjà renseignées restent comprises dans la nouvelle plage.

Les Statuts proposés sont Prévue, Confirmée, Réalisée et Annulée ; les Priorités sont Basse, Normale et Haute. Le libellé Annulée intervient directement dans les calculs : ne le renommez pas sans revoir les formules et les validations qui l’utilisent. Les cellules G à I de Paramètres alimentent les listes actives et doivent rester calculées.

Limites à connaître avant diffusion

Ce classeur est un support de suivi périodique. Il ne reçoit pas de mise à jour en temps réel, n’envoie aucune notification et ne contient ni macro ni graphique. Les changements ne sont visibles qu’après saisie, recalcul et enregistrement du fichier.

Les plages préconfigurées s’arrêtent à 200 affectations, 100 intervenants, 100 associations chantier-zone et 100 indisponibilités. Comme les plages ne sont pas des tableaux Excel structurés, l’ajout de lignes au-delà de ces limites demande de prolonger avec soin les formules, validations, formats et plages de calcul.

Le champ Métier est informatif : aucune formule ne vérifie son adéquation avec Tâche. Le fichier ne contrôle pas non plus les habilitations, les temps de trajet, les besoins de capacité, la présence constatée ou les obligations réglementaires. Enfin, les recherches utilisent XLOOKUP ; vérifiez que votre version d’Excel reconnaît cette fonction avant d’interpréter un message d’erreur comme un défaut de données.

Questions fréquentes