Calcul d'une somme selon une valeur dans une autre table
Bonjour,
Je cherche à sommer des valeurs en fonction d'un code dont la valeur à sommer correspondante est présente dans un autre tableau.
Fonctionnellement, voici mon besoin :
Je gère un plannig journalier d'agents qui peuvent travailler chaque jour selon des plages horaires différentiées.
Ces plages horaires sont codifées (dans mon exemple : HBO1, HBO2, HBO3, HBO4). Ces codes de plages horaire donnes lieux à un certain nombre heures travaillées selon des tranches horaires de la journée. Cf. tableau en A1:G6.
Le planning (en I1:U10) présente pour chaque journée trravaillée la liste des agents avec leur plage horaire de travail (Code Plage Horaire).
Ce que je souhaite en finalité, c'est de savoir par tranche horaire le nombre total d'heures travaillées par journée pour l'ensemble des agents (J21 à J25)
En J12 à J18, j'obtiens le bon résultat en utilisant des colonnes de détails qui me permettent de faire les calcul agent par agent. Mais cela alourdi beaucoup mon fichier. (Le fichier joint n'est qu'un exemple. Le fichier cible contient plus d'une centaine d'agents et plusieurs mois de planing)
Pourriez-vous m'indiquer comment écrire une formule (matricielle ou non) qui me permet de m'affranchir des colonnes de détails.
PS : Hormis les lignes de totalisation par tranches horaire et la suppression des colonnes de détail des calculs journaliers, je ne souhaite pas modifier la structure du planning journalier
Merci pour votre aide.
Voir capture d'écran du fichier joint.
La résolution de mon PB est relativement urgent.
SVP. Est-ce qu'un expert pourrait se pencher sur ma demande.
Merci d'avance.
Bonjour MAM78,
je te retourne ton fichier modifié :
⚠ regarde bien toutes les formules en J21:J25 et P21:P25
dhany
@MAM78
je te propose aussi une solution VBA :
cette solution sera plus facile à adapter à ton fichier réel.
(pour chaque jour : une seule formule, à tirer vers le bas)
dhany
Merci pour ces suggestions.
Mais dans mon contexte la liste des plages horaires peut variée être modifiée par le gestionnaire du plannig qui ne maitrise pas forcement les fonctions Excel.
Par conséquence, je souhaite que la formule de calcul fonctionne sans modification de celle-ci lors d'ajouts de codes de plage horaire par le gestionnaire du planning. Mais également si possible d'éviter d'utiliser du code VBA.
Bonsoir MAM78,
autre version :
l'appel de la fonction personnalisée est identique, mais le code VBA est très différent.
LIGNE()-20 revient à ça : c'est bien un nombre de 1 à 5.
cette nouvelle version permet d'ajouter d'autres codes de plage horaire à partir de la ligne 7 et en dessous :
HBO5 ; HBO6 ; HBO7 ; etc... ; bien sûr, il faut compléter les colonnes B à G correspondantes.
⚠ n'oublie pas de lire la remarque en B31
tu a écrit :Mais également si possible d'éviter d'utiliser du code VBA.
je ne vois pas comment faire ça sans VBA !
(ou alors, un spécialiste des formules longues et complexes ?)
dhany
@MAM78 : relis mon post précédent, car je l'ai beaucoup modifié et complété.
bonjour
salut dhany
j'ai donné des indications en MP à MAM78 : conserver les colonnes et les masquer si elles gênent pour la saisie.
et même, vu la taille de son entreprise, de passer à un SGBD (Access....) qui me semble plus indiqué
je resuggère le SGBD quand je lis " être modifiée par le gestionnaire du plannig qui ne maitrise pas forcement les fonctions Excel."
bonne journée à tous
Merci @dhany. Ton code fonctionne très bien. Je vais devoir adapter un peu puisque les codes de plages horaires ne sont pas systématiquement codifiées HBOxx. Je vais probablement utiliser des plages nommées, de cette façon il y aura moins de risques liées a des saisie dans la colonne A. Idem pour le tableau planning et utiliser la fonction decaller pour sélectionner la colonne à traiter.
@jmd. Merci pour ta suggestion, mais je souhaite laisser la possibilité au gestionnaire d'intégrer d'autres fonctionnalités dans le fichier planning et laisser également la possibilité de mettre en forme le tableau (ex. : mise en couleur des codes de plages horaires) à la convenance du gestionnaire. Bref je souhaite rester sur Excel mais s'il n'y a pas de problème de coûts pour une utilisation d'access. Ta suggestion avec des RECHERCHEV m'intéresse beaucoup. Je maitrise assez bien cette fonction, mais je ne vois pas comment l'intégrer dans une formule qui me permettrait de faire le calcul du nombre d'heures journalier par tranches horaire.
Merci encore pour vos riches contributions.
@jmd. Merci pour ta suggestion, mais je souhaite laisser la possibilité au gestionnaire d'intégrer d'autres fonctionnalités dans le fichier planning et laisser également la possibilité de mettre en forme le tableau (ex. : mise en couleur des codes de plages horaires) à la convenance du gestionnaire. Bref je souhaite rester sur Excel mais s'il n'y a pas de problème de coûts pour une utilisation d'access. Ta suggestion avec des RECHERCHEV m'intéresse beaucoup. Je maitrise assez bien cette fonction, mais je ne vois pas comment l'intégrer dans une formule qui me permettrait de faire le calcul du nombre d'heures journalier par tranches horaire.
tout SGBD permet au programmeur d'intégrer des zones de saisie de texte libre, et de mettre à dispo des couleurs. L'utilisateur pourra donc faire ce qu'il fait sous Excel. Mais de manière sécurisée.
ai-je parlé de RECHERCHEV ? car je pense plutôt à des SOMMEPROD (permet d'aller chercher des valeurs dans le tableau de gauche.
note qu'avec Excel, on fait des liaisons entre tableaux, genre SQL ou SGBD, avec des RECHERCHEV. On dit qu'on aplatit les données (to flatten)
Ci-dessous ce que tu m’indiquais en MP.
Désolé d’insister même si j’ai je pense les compétences pour développer cela sous access. je préfère laisser à l’utilisateur la main sous excel (copier/coller de lignes, mise en forme conditionnelles, partage du fichier, intégration de données provenant d’autres sources, ...).
Maintenant, si vous me dites que ce n’est pas possible avec une formule sous excel, je vais me résigner à utiliser du code VBA.
oui, je rappelais que sous Excel on imite les liaisons par des RECHERCHEV. A toutes fins utiles.
mais dans ton cas, as-tu essayé SOMMEPROD ?
j'ai pas le temps ce soir, ni demain
oui mais je n’ai pas trouvé comment faire une jointure de type N to N. Pour le moment j’ai toujours utilisé sommeprod avec du 0 ou 1 to N.