XL 2013 Macros Calcul des variations de la masse salariale entre le mois M-1 et le mois M

[l'o_jpk]

Bonjour à tous.

je suis "opportuniste" dans le vba et souhaiterais de l'aide svp.
Habituellement je récolte des bouts de code à gauche et à droite que je réussis à compiler pour mon usage personnel.... Mais là je sèche...

je dois faire l'analyse mois après mois à compter de fin février, des différentes variations par rubriques et par salariés de mes variables de paye dans l'optique d'avoir une meilleure lecture et une meilleure maîtrise de La masse salariale

J'ai 183 rubriques dont je dois calculer les variations pour une moyenne d'environ 800 salariés concernés. Compte tenu de l'activité, nous avons en permanence de nouveaux entrants dans le staff ainsi que de nombreux sortants d'une paye à l'autre. Aussi, les 183 rubriques ne sont pas forcément utilisées à chaque paye mais leur nombre fixe est de 183. Du coup la séquence (ordre/chronologie/succession) des rubriques peut varier d'un mois à l'autres sur le fichier qui m'est généralement transmis.

Chaque salarié a une clé d'identification unique (le matricule) et chaque rubrique de paye dispose d'un code et d'un libellé qui lui sont propres.

L'idée est que tous les salariés présents (entrants et sortants compris) sur 2 périodes de paye consécutives soient tous listés dans le tableau de calcul des variations avec comme indication 0 pour les valeurs inexistantes sur l'une ou l'autre des périodes concernées tout comme pour les 183 rubriques que contient le système de paye.

Les données du mois M-1 en vert, celles du mois M en jaune et les résultats des soustractions entre les données du mois courant et celles du mois précédent sans remplissage.

J'ai joint les 3 fichiers à titre d'exemple en terme de structure ils reflètent bien la situation ainsi que ce qui est attendu ; ils seront tous les 3 répertoriés dans le même dossier. Je serais ravi de fournir davantage de précisions en cas de nécessité.

 

[gbinforme]

je suis "opportuniste" dans le vba et souhaiterais de l'aide svp.

Pas sûr qu'il faille du vba car avec des formules très simples (INDEX par exemple ou RECHERCHEV) tu dois avoir tes résultats.

 

[l'o_jpk]

Oui en effet , mais vu le volume données et ça commence les calculs; ça rame, puis excel plante... j'ai farfouillé, il semble rame est insuffisante.. car même toutes autres appli fermées, ça finiti tirs par planter

 

[Hasco]

bonjour,

je suis "opportuniste"

Moi aussi, puisqu'en cherchant des cas concrets à traiter par power query, je suis tombé sur votre fil de discussion qui m'a interpellé.
C'est pourquoi je vous propose ici une solution par requête power query, disponible au téléchargement en complément xl2013 sur le site de microsoft :

Download Power Query from Official Microsoft Download Center

Microsoft Power Query pour Excel est un complément Excel qui améliore l'expérience du décisionnel libre-service dans Excel en simplifiant la récupération des données, l'accès et la collaboration.

www.microsoft.com

Dans cette solution, vous avez dans la feuille "Variation", en A2 (nommée: "Libellé_Choisit") une liste de choix contenant les combinaisons de comparaison de fichier M vs M-1

Une fois choisit, vous actualisez la requête et voilà.

Comme votre poste ne le mentionnait pas, j'ai considéré que les codes de rubrique de paie étaient les mêmes d'un mois à l'autre, allant de 100 à 282 soit 183 rubriques.

Pour test, j'ai dupliqué et alléger les fichiers.

Dans la feuille "Paramètres" vous trouverez en cellule B3 nommée "Dossier" une formule donnant le chemin du répertoire dans lequel la requête devra aller chercher les fichiers.

C'est la requête "RQ_Fichiers_Mensuels" qui récupère et filtre les fichiers de ce dossier pour ne conserver QUE ceux dont le nom contient "Journal de paye".
Elle prépare les données des fichiers pour leur interrogation futures.
Entre autre action, elle leur ajoute les colonnes de rubriques éventuellement manquantes.
Son résultat n'est pas afficher dans une feuille.

La requête "RQ_Variations" elle, récupère dans la requête précédente la ligne correspondant au "Libellé_Choisit" par l'utilisateur en A2 de la feuille "Variations"

Elle crée une table pour le mois M et une autre pour le mois M-1
Pour avoir une liste Exhaustive des salariés, j'ai créé une table à cet effet.
La requête "RQ_Variations" fait ensuite une jointure entre la table des Salariés et les données du mois M
les données du mois M-1 sont associées ensuite.
Lors de leurs développement les colonnes de M et M-1 sont préfixées par "M " et "M-1 "
Ensuite les valeurs nulles des colonnes qui manquaient à l'origine sont remplacées par des zéros pour pouvoir être calculées.

Les colonnes de différences entre M et M-1 sont créées, puis toutes ces colonnes (551) sont remises dans le bon ordre par sélection.
Les données sont enfin triées par clé de salarié puis renvoyées.

J'ai du supprimer des lignes de résultat pour faire maigrir le fichier et le charger ici. Dès que la requête sera actualisée (lorsque vous aurez télécharger le complémént Power query) vous retrouverez vos 381 salariés.

P.S. une éventuelle amélioration, assez facile à apporter serait de laisser l'utilisateur choisir quel mois il veut comparer à quel autre.


Cordialement

 

[gbinforme]

Moi aussi, puisqu'en cherchant des cas concrets à traiter par power query, je suis tombé sur votre fil de discussion qui m'a interpellé.

Bonjour,
Je pensais aussi que power query était la solution pour ce type de traitement mais je ne maitrise pas assez cette procédure pour l'appliquer.
Une petite question tout de même : j'ai l'impression que le résultat est incorrect car par exemple ceci devrait être plutôt positif ?

M. Code Rubrique de paie 1 : 100	M-1. Code Rubrique de paie 1 : 100	Différence 1
19 304​	0​	-19 304​

 

[Hasco]

Bonjour,

Il suffit de soustraire dans l'autre sens, ce que je pensais avoir fait
Dans l'étape qui ajoute les colonnes de différence bien-sûr et qui deviendrait :

= List.Accumulate( {0..182},
#"Nuls remplacés par zéros", // table de départ
// fonction qui ajoutera une colonne à la table 'LaTable' à chaque itération de 0 à 182
(LaTable,Index)=> Table.AddColumn(
LaTable, // table à laquelle il faut ajouter la colonne
Colonnes[Différences]{Index}, // nom de la colonne
each // fonction de construction de chaque valeur de colonne
Record.Field(_, Colonnes[Préfixées 1]{Index}) - Record.Field(_, Colonnes[Préfixées 2]{Index}) ))

Je pensais aussi que power query était la solution pour ce type de traitement

Une solution mixte avec TCD pourrait sans-doute le faire (on est jamais sûr de rien) mais c'est par pur envie égoïste de traiter par PQ que je n'ai pas cherché par là.