Répartir automatiquement les salariés sur des postes

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Répartir automatiquement les salariés sur différents postes de travail

Dans cette formation Excel, nous proposons de mettre en oeuvre des astuces de calcul intéressantes pour répartir aléatoirement les salariés sur différents postes de l'usine. L'intérêt est double. Les salariés changent régulièrement de ligne et d'équipe. Pour cela, nous disposons de 5 postes et de 30 employés référencés dans un tableau. A chaque génération, les équipes doivent être formées automatiquement et réparties sur les 5 lignes.



La capture ci-dessus illustre une partie de l'application finalisée. Il s'agit du résultat obtenu après un clic sur le bouton Répartir. Tous les employés ont été ventilés aléatoirement sur les 5 lignes. Les équipes et le travail changent donc régulièrement.

Source et présentation de la problématique
Dans un premier temps, nous proposons de récupérer un classeur Excel offrant la structure adéquate pour recevoir la base de nos travaux.

• Télécharger le classeur repartition-automatique-salaries-postes.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué d'une seule feuille nommée Répartition_postes. Dans le tableau situé entre les colonnes B et G, il énumère les salariés de l'entreprise, de la ligne 5 à la ligne 34. Il y a donc 30 personnes à ventiler automatiquement sur les 5 lignes de production. Ces 5 postes sont représentés entre les colonnes I et M, sur la ligne 4 pour les entêtes. Il s'agit donc de distribuer les employés dans chacune de ces colonnes pour créer des équipes uniformément réparties.

Dans les formations précédentes, nous avions apporté la solution pour générer une grande quantité de nombres aléatoires uniques. Et nous avions exploité ces résultats pour réaliser des tris aléatoires sur les tableaux.

Ici, il s'agit bien de générer des valeurs aléatoires. Elles doivent être comprises entre 1 et 5, pour représenter les 5 postes. Mais elles ne doivent pas être uniques, bien au contraire. Il en faut 6 par chiffre car nous disposons de 30 salariés à répartir sur 5 lignes de production (6*5). L'enjeu majeur est d'être en mesure de prélever 6 fois le même chiffre généré aléatoirement, dans l'ordre où ils apparaissent et ce, pour les 5 chiffres.

Générer des nombres aléatoires
Nous l'avons appris, c'est la fonction Excel Alea.Entre.Bornes qui permet de produire des nombres entiers aléatoires, selon la syntaxe suivante :

=Alea.Entre.Bornes(valeur_inf ; valeur_sup)

Elle n'attend donc que deux paramètres. Il s'agit des bornes entre lesquelles ces nombres doivent être générés. Dans notre cas, la valeur inférieure est 1 tandis que la valeur supérieure est 5. Nous représenterons ainsi les 5 lignes de production.

Il s'agit donc de tirer la poignée de la formule sur plusieurs lignes afin de répéter le calcul. Comme ces chiffres se génèrent précisément de façon aléatoire, rien n'empêche une valeur d'apparaître souvent. Pour nous assurer de produire 6 fois au moins chacun des chiffres, nous choisirons de répliquer le calcul sur plusieurs dizaines voire centaines de lignes. Statistiquement, nous devons ainsi les obtenir tous. Il s'agira alors de repérer seulement les 6 premiers par chiffre.

Des calculs intermédiaires doivent être commis. Nous proposons de les construire à partir de la colonne O. Ces dernières pourront ainsi être masquées plus facilement à l'issue.

• Cliquer sur la cellule O5 pour l'activer,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction de génération aléatoire suivie d'une parenthèse, soit Alea.Entre.Bornes(,
• Saisir le chiffre 1 pour définir la borne inférieure,
• Taper un point-virgule (;) pour passer dans l'argument de la borne supérieure,
• Saisir le chiffre 5 pour définir le nombre le plus grand à ne pas dépasser,
• Fermer la parenthèse de la fonction Alea.Entre.Bornes,
• Valider le calcul par le raccourci clavier CTRL + Entrée pour conserver la cellule active,
• Tirer la poignée de la cellule jusqu'à la cellule O300 par exemple,

En répliquant ainsi le calcul, nous générons une grande quantité de chiffres aléatoires compris entre 1 et 5.



Comme vous le constatez, par définition même, il n'y a pas de règle. Dans l'exemple illustré par la capture ci-dessus, en 10 lignes, le chiffre 2 est déjà proposé 4 fois. Ils sont tous apparus. Mais le chiffre 3 par exemple, n'a été généré qu'une seule fois. Or, il nous en faut 6 pour représenter les 6 salariés à affecter sur la ligne 3. Nous avons donc bien fait d'exagérer le mouvement en tirant la poignée sur les lignes du dessous.

Comme vous le savez, à chaque évènement intercepté sur la feuille, Excel ordonne le recalcul automatique de toutes les formules.

• Sélectionner une cellule vide, par exemple P5,
• Enfoncer la touche F2 du clavier pour activer sa saisie,
• Puis, sans ne rien saisir, enfoncer la touche Entrée pour valider la cellule,

Comme vous le constatez, malgré l'absence de modifications, l'événement de la validation a été intercepté. Excel a ordonné le recalcul des formules et tous les nombres aléatoires ont été regénérés. Ce mode de fonctionnement est gênant. Nous souhaitons distribuer les salariés en équipes, seulement au clic sur le bouton Répartir. Comme cette distribution dépend des nombres aléatoires, en l'état, elle se ventilera à chaque événement.

Copier les valeurs sans les formules
L'astuce consiste à créer une photographie à l'instant T de ces nombres générés. Il suffit de réaliser une copie détachée des formules. Pour ce faire, nous proposons d'exploiter la colonne P. Cette action doit intervenir au clic sur le bouton Répartir. Nous devons donc créer une macro reproduisant ces manipulations et la lier au bouton. L'enregistrement d'une macro consiste à simuler les actions strictement nécessaires. Le ruban développeur est nécessaire. La formation pour débuter en VBA Excel rappelle, entre autres, comment l'afficher s'il n'est pas visible dans votre environnement.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code, à gauche dans le ruban, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, la nommer Repartir, sans accents,
• Cliquer sur le bouton Ok pour débuter l'enregistrement,
• Sélectionner tous les nombres aléatoires, soit la plage de cellules O5:O300,
• Les copier à l'aide du raccourci clavier CTRL + C par exemple,
• Sélectionner la première cellule de la plage de destination, soit P5,
• Cliquer sur l'onglet Accueil en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Presse-papiers, à gauche dans le ruban, cliquer sur la flèche du bouton Coller,
• Dans la liste, cliquer sur le bouton Mise en forme des valeurs et de la source (Cf.figure),

• Sélectionner ensuite la toute première cellule de la feuille, soit A1,

Cette action permettra de recentrer l'affichage à l'issue du traitement, soit après le clic sur le bouton. Les actions sont terminées. Nous devons donc stopper l'enregistrement.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code, cliquer sur le bouton Arrêter l'enregistrement,

La macro est désormais créée. Elle est capable de reproduire ces actions sur ordre. Mais pour l'instant elle n'existe que sous forme de code VBA, construit automatiquement par Excel. Nous devons l'attacher au bouton Repartir pour qu'elle s'exécute au clic.

• Cliquer avec le bouton droit de la souris sur le bouton Répartir,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la liste de la boîte de dialogue qui suit, sélectionner la macro Repartir,
• Puis, cliquer sur le bouton Ok pour établir le lien entre les deux,
• Cliquer sur une cellule de la feuille pour désactiver la sélection du bouton,
• Puis, cliquer sur le bouton Repartir pour exécuter la macro,

Cette action commence par ordonner le recalcul de toutes les formules. Ainsi tous les nombres aléatoires de la fonction Alea.Entre.Bornes sont regénérés. Mais entre-temps, la macro a prélevé les anciennes valeurs pour restituer une copie déliée des formules en colonne P. De nouvelles valeurs sont donc proposées sur ordre. C'est ce que nous souhaitons pour l'extraction qui permettra la ventilation.

• Sélectionner une cellule vide, par exemple P2,
• Enfoncer la touche F2 du clavier pour activer sa saisie,
• Puis valider avec la touche Entrée pour générer l'événement du recalcul complet,

Comme vous le constatez, les nombres de la colonne O ont tous été modifiés. Ils sont en effet liés à une formule. En revanche, les valeurs de la colonne P n'ont pas changé. C'est exactement ce que nous cherchions. La distribution des salariés ne doit intervenir que sur un ordre spécifique, celui du clic sur le bouton Répartir. Mais nous n'en sommes qu'au stade des calculs intermédiaires pour l'instant.

Marquer les valeurs à extraire par calcul Excel
Toujours en étape intermédiaire à des fins d'extraction, nous devons désormais exploiter la colonne Q. La solution consiste en une astuce que nous avions mise en oeuvre dans la formation Excel permettant d'éliminer les données de doublons. Il s'agit de vérifier une condition de dénombrement. Si le nombre aléatoire n'a pas encore été généré au moins 6 fois, il doit être marqué par un numéro incrémenté. En effet, il y a 6 salariés à répartir par poste. Dans le cas contraire, la limite est dépassée et il ne doit plus être repéré. Un critère peut être évalué grâce à une fonction Excel Si. Il est basé sur la valeur du dénombrement. Et c'est la fonction Excel Nb.Si qui permet de compter le nombre de fois qu'une occurrence est répétée sur une plage de cellules. Sa syntaxe est la suivante :

=Nb.Si(Plage_de_cellules ; Valeur_à_compter)

La plage de cellules fait partie de l'astuce. Elle doit progresser en même temps que le calcul. Si nous désignons l'intégralité des nombres aléatoires, la valeur retournée sera toujours supérieure à 6. Nous devons donc partir du haut de la plage, figer sa borne supérieure et défiger sa borne inférieure. Ainsi, au fil de la réplication, la plage de cellules pour le dénombrement, progressera en hauteur, afin de considérer les nombres aléatoires suivants. La valeur à compter sur cette plage n'est autre que le nombre aléatoire de la ligne en cours.

Lorsque le décompte est inférieur ou égal à 6, le repérage doit se faire par un numéro incrémenté. Cette solution facilitera l'extraction naturelle de chacune des valeurs marquées. Sur la plage de cellules du calcul, qui progresse de la même façon que celle du décompte, nous exploiterons la fonction Max à laquelle nous ajouterons une unité pour l'incrémentation naturelle à chaque repérage. La fonction Max retourne en effet la plus grande valeur d'une plage. Dans notre cas, il s'agira de la dernière valeur ayant servi de marquage.

• Sélectionner la première cellule pour le calcul du repérage, soit Q5,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit Nb.Si(,
• Désigner la cellule située au-dessus des valeurs aléatoires copiées, soit P4,
• Taper le symbole deux points (:) pour générer la plage P4:P4,
• Figer seulement la première des deux références (Touche F4), ce qui donne : $P$4:P4,

De cette manière, la plage de cellules pour le dénombrement progressera en hauteur, en même temps que le calcul sera répliqué sur les lignes du dessous.

• Après la plage, taper un point-virgule (;) pour passer dans l'argument de la valeur à compter,
• Cliquer sur la première valeur aléatoire à dénombrer, soit la cellule P5,
• Fermer la parenthèse de la fonction Nb.Si,
• Taper le symbole inférieur suivi du chiffre 6 pour le critère à évaluer, soit : <6,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir le nom de la fonction retournant la plus grande valeur d'une plage, suivi d'une parenthèse, soit Max(,
• Désigner la cellule située au-dessus du calcul en cours, soit Q4,
• Taper le symbole deux points (:) pour générer la plage de départ, soit Q4:Q4,
• Figer seulement la première des deux, ce qui donne : $Q$4:Q4,

La valeur maximale attribuée sera repérée sur une plage des cellules qui progresse en même temps que le calcul. Ainsi, à chaque fois qu'un nouveau nombre aléatoire est repéré comme correct, il sera marqué de la valeur numérique suivante. Mais pour cela, nous devons encore réaliser l'incrémentation dans la formule.

• Après la plage, fermer la parenthèse de la fonction Max,
• Ajouter une unité, soit : +1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets, soit : '', pour conserver la cellule vide lorsque le nombre a déjà été repéré au moins 6 fois,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par CTRL + Entrée pour conserver la cellule du résultat active,
• Puis, double cliquer sur la poignée de la cellule pour répliquer la logique du calcul jusqu'en ligne 300,

Comme vous le remarquez, des numéros incrémentés s'inscrivent en effet dans la colonne (Max + 1 sur une plage qui progresse). Mais dès qu'un nombre a déjà été repéré au moins six fois (Nb.Si<6 sur une plage qui progresse), une rupture s'opère. La cellule reste vide. Le nombre n'est plus marqué.



Et si vous cliquez sur le bouton Répartir, comme de nouvelles valeurs aléatoires sont inscrites en colonne P, le repérage s'adapte en colonne Q. La formule que nous avons bâtie est la suivante :

=SI(NB.SI($P$4:P4;P5)<6; MAX($Q$4:Q4)+1; '')

Extraire les nombres aléatoires repérés
Nous devons exploiter ce repérage pour extraire les nombres aléatoires ainsi marqués. Comme ils apparaissent aussi dans un ordre aléatoire à chaque génération, l'attribution d'un numéro de poste à un salarié, sera de fait aléatoire. C'est exactement ce que nous souhaitons pour une ventilation des équipes en toute impartialité.

Ces numéros de poste doivent être importés dans la colonne Répartition (F) du tableau des salariés, dans l'ordre où ils apparaissent. L'extraction doit être bâtie sur la recherche du numéro incrémenté. La fonction Excel Ligne permet de retourner l'indice de ligne d'une cellule passée en paramètre. Si nous lui transmettons la cellule A1 par exemple (Ligne(A1)), elle retourne le chiffre 1. En répliquant le calcul vers le bas, elle retournera le chiffre 2 puis 3 etc... Il s'agit précisément des numéros incrémentés à trouver. Et c'est la fonction Excel Equiv qui doit chercher cette valeur. En effet, cette dernière permet de retourner l'indice de ligne de l'information à extraire. Sa syntaxe est la suivante :

=Equiv(Valeur_Cherchée ; Colonne_de_recherche ; 0)

La valeur cherchée est donc Ligne(A1) pour le premier calcul. La colonne de recherche est la colonne Q. Le dernier argument fixé à 0 indique à la fonction de réaliser une recherche selon une correspondance exacte.

Pour extraire une information, nous avons aussi besoin d'indiquer l'indice de colonne. C'est la fonction Excel Index qui restitue l'information située au croisement d'une ligne et d'une colonne, selon la syntaxe suivante :

=Index(tableau_de_recherche ; Indice_de_ligne ; Indice_de_colonne)

Le tableau de recherche correspond aux deux colonnes P et Q. L'indice de ligne doit être retourné par la fonction Equiv. Nous devons donc imbriquer la fonction Equiv en deuxième argument de la fonction Index. L'indice de colonne est connu. Il suffira d'inscrire le chiffre 1 pour désigner la première colonne (P) de la sélection.

Comme nous le disons à chaque fois, les fonctions d'extraction retournent des erreurs lorsque les recherches sont infructueuses. Pour gérer ce cas potentiel, nous imbriquerons le calcul général dans une fonction SiErreur.

• Sélectionner la première cellule pour la formule d'extraction, soit F5,
• Taper le symbole = pour commencer le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit Index(,
• Sélectionner les colonnes P et Q ou les écrire pour désigner le tableau de recherche, soit P:Q,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir le nom de la fonction retournant l'indice de ligne d'une valeur cherchée, suivi d'une parenthèse, soit Equiv(,
• Saisir la fonction renvoyant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Cliquer sur une cellule située en première ligne de la feuille, par exemple O1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Puis, cliquer sur l'étiquette de la colonne Q pour la désigner intégralement, soit : Q:Q,
• Taper un point-virgule (;) suivi du chiffre 0 pour une recherche exacte, soit ;0,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne de la fonction Index,
• Saisir le chiffre 1 pour désigner la première colonne (P) où se situent les nombres à extraire,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur de la fonction SiErreur,
• Saisir deux guillemets, soit : '', pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Des numéros de poste sont en effet attribués aléatoirement à chacun des salariés. La répartition est équitable sur les 5 postes. Chacun des numéros de ligne est proposé 6 fois sans ne jamais dépasser la valeur 5. Le résultat se dessine peu à peu. Nous sommes déjà en mesure de définir le poste d'un salarié et de son équipe. Si vous cliquez sur le bouton Répartir, comme de nouvelles valeurs aléatoires sont proposées, la distribution des postes change.

La formule que nous avons construite pour réaliser cette extraction, est la suivante :

=SIERREUR(INDEX(P:Q; EQUIV(LIGNE(O1); Q:Q; 0); 1); '')

La figure ci-dessous illustre le résultat obtenu.



Nous devons exploiter ces extractions pour inscrire les noms des salariés dans leurs postes respectifs, soit dans le tableau compris entre les colonnes I et M. Mais un souci surgit. Nous sommes capables d'extraire des informations lorsqu'elles sont repérées par des numéros incrémentés. Nous venons d'en faire la démonstration avec la dernière formule. Et ces numéros de postes attribués consistent en une répétition des valeurs numériques.

Incrémenter les résultats d'une extraction
L'astuce consiste justement à recréer cette incrémentation naturelle, avant de produire le résultat final de la distribution. C'est la raison pour laquelle la colonne Ventilation est proposée. Nous souhaitons concaténer chaque numéro de poste avec le nombre de fois qu'il a déjà été attribué. Pour le poste 1 par exemple, nous produirons ainsi les valeurs 11, 12 puis 13 etc...

La concaténation consiste à assembler ces deux informations grâce au Et commercial (&). La fonction qui permet de compter la répétition est toujours la fonction Nb.Si. Comme précédemment, nous devons l'exercer sur une plage de cellules qui progresse en même temps que le calcul.

• Sélectionner la première cellule du calcul d'incrémentation, soit G5,
• Cliquer ou saisir la cellule du premier numéro de poste attribué, soit F5,
• Enfoncer la touche 1 en haut à gauche du clavier pour inscrire le caractère de concaténation, soit &,
• Saisir la fonction de dénombrement suivie d'une parenthèse, soit Nb.Si(,
• Cliquer sur la cellule F4 pour la désigner,
• Taper le symbole deux points (:) pour générer la plage F4:F4,
• Figer uniquement la borne supérieure avec la touche F4, ce qui donne : $F$4:F4,
• Taper un point-virgule (;) pour passer dans l'argument de la valeur à dénombrer,
• Sélectionner ou saisir le premier numéro de poste soit la cellule F5,
• Fermer la parenthèse de la fonction Nb.Si,
• Incrémenter ce décompte d'une unité, soit : +1,
• Valider la formule par le raccourci CTRL + Entrée pour conserver la cellule active,
• Double cliquer sur la poignée du résultat pour reproduire la logique sur la hauteur du tableau,

Nous obtenons l'incrémentation que nous cherchions à reproduire pour faciliter l'extraction afin de favoriser la répartition des salariés par poste. Sur l'exemple illustré par la capture ci-dessus, le premier poste 4 est marqué par le nombre 41. Le suivant est repéré par le nombre 42 etc... Cette logique va nous permettre de réaliser l'extraction dans le tableau des postes pour ventiler les équipes.

Répartir équitablement les données dans des tableaux Excel
Le principe est bien connu désormais. Dans le tableau des postes, il s'agit d'effectuer la recherche du numéro incrémenté en reproduisant la concaténation, grâce à la fonction Equiv. Retournant l'indice de ligne du salarié à afficher, la fonction Index n'aura plus qu'à extraire le salarié dans sa colonne, pour le restituer dans sa ligne de production. Encore une fois, nous choisissons d'englober cette formule dans une fonction de gestion d'erreur SiErreur. Pour le poste 1, il s'agit de réaliser la recherche du chiffre 1 concaténé à l'incrémentation naturelle de ligne (11, 12, etc...).

• Sélectionner la première cellule du calcul pour la répartition des postes, soit I5,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit Index(,
• Sélectionner ou saisir les références des colonnes de recherche, soit E:G,

En effet, la valeur cherchée est située dans la colonne G. La donnée à extraire est le nom du salarié, inscrit en colonne E.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction retournant la ligne d'une recherche, suivie d'une parenthèse, soit Equiv(,
• Taper le chiffre 1 suivi du symbole de concaténation, soit 1&,
• Saisir la fonction donnant l'indice de ligne d'une cellule, suivie d'une parenthèse, soit Ligne(,
• Cliquer sur une cellule de la première ligne, par exemple I1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Cliquer sur l'étiquette de la colonne G ou taper ses références, soit G:G,
• Taper un point-virgule suivi du chiffre 0, soit ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne de la fonction Index,
• Saisir le chiffre 1 pour désigner la colonne des noms des salariés,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans la branche de la gestion d'erreur de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par CTRL + Entrée,
• Tirer la poignée du résultat sur les cellules du dessous,

Comme vous le constatez, tous les salariés affectés aléatoirement sur ce numéro de poste, sont parfaitement énumérés dans la colonne. La formule d'extraction que nous avons construite est la suivante :

=SIERREUR(INDEX(E:G;EQUIV(1 & LIGNE(I1); G:G;0); 1); '')

Pour les quatre autres postes, il s'agit de répliquer la même formule, en adaptant simplement le chiffre de la concaténation. Pour le poste 2, il ne s'agit plus de 1& mais de 2&, puis 3& pour le suivant etc...



Vous obtenez une répartition équitable et automatique de tous les salariés sur les différentes lignes de production de l'entreprise. A chaque clic sur le bouton Répartir, les affectations et les équipes changent.