Répartir une somme au prorata de la somme attribuée par service

[KIM]

Bonjour à vous toutes et tous,
Votre aide précieuse dans le temps est inoubliable. Je reviens vers vous après un bon moment pour demander votre aide dans l'automatisation de la répartition d'une dépense générales au prorata des dépenses de chaque service. Dans le tableau ci-joint, onglet SOURCE, J'ai par département (colA), les dépenses engagées par service (colD). Les dépenses sans service (cellule vide colD) sont des dépenses générales. Je dois répartir ces dépenses générales par service, au prorata des dépenses de chaque service du département (cf onglet DEPREP).
Le calcul n'est pas compliqué. Mais quand on a +30000lignes et +400 services il faut passer du temps. Est-il possible d'automatiser le calcul des dépenses communes, engagées et réparties par service et par département?
Merci de votre aide.
KIM

 

[Misange]

Re : Répartir une somme au prorata de la somme attribuée par service

Bonjour

Un essai en PJ avec un TCD + un tableau issu de celui-ci.

 

[Modeste geedee]

Re : Répartir une somme au prorata de la somme attribuée par service

Bonsour®

sans VBA et moyennant quelques colonnes complémentaires...

Utilisation de tableaux et tableau croisé dynamique

ne pas oublier d'actualiser lorsque ajout de données (actuellement 4000 lignes)

oupssss... bonjour Flo

 

[Dranreb]

Re : Répartir une somme au prorata de la somme attribuée par service

Bopnjour.

Une autre approche à l'aide d'une nouvelle pièce de mon OutIdx…
J'ai considéré que la colonne OPER était une sorte de sous-département
J'ai mis le résultat à coté de l'exemple.

P.S. Re-joint pour corriger une petite erreur et ajouter formules SOMME.

 

[KIM]

Re : Répartir une somme au prorata de la somme attribuée par service

Bonjour le fil,
Je n'ai pas pu répondre à vos propositions car je n'ai pas reçu de notifications du serveur excel-downloads.com. Veuillez bien m'excuser.
Je recherche une solution vba car je dois intégrer ce calcul dans une chaîne dans laquelle j'ai déjà des macros de traitement de données.
@Dranreb : Merci pour ton code vba. Est-il possible de m'expliquer comment l'utiliser? Le tableau à traiter possède plus de colonnes et les données ne sont pas dans les colonnes de mon exemple, mais le principe est le même. Comment appliquer ainsi ton code sur mon tableau d'origine, colonnes à paramétrer, etc...?
@Modeste Geedee : J'ai bien compris les colonnes complémentaires , OK, Est-il possible de ne pas passer par du TCD et créer mon tableau final avec du vba? Merci d'avance pour ton aide.
@Misange : Merci pour ton tableau TCD. J'aurai du mal à l'intégrer dans ma chaine de traitement. Y-a-t-il possibilité de faire du vba? Merci d'avoir pris le temps de me répondre.

Merci de votre aide.
KIM

 

[Misange]

Re : Répartir une somme au prorata de la somme attribuée par service

Bonjour

Si tu nous donnes une idée de ce qu'est ta chaîne de traitement et ton code, ce sera plus facile de t'aider. Le TCD peut être lancé par VBA si nécessaire, ou préparé à l'avance et simplement actualisé par VBA (c'est plus simple).

 

[Dranreb]

Re : Répartir une somme au prorata de la somme attribuée par service

Bonsoir.

Pour mon truc, coté récupération, c'est très simple, tout est concentré dans cette expression :

GroupOrg(Feuil1.Range(Feuil1.[E5], Feuil1.Cells(60000, "A").End(xlUp)), 1, 3, 4)

Elle représente une collection organisée hiérarchiquement selon les colonnes 1, 3 et 4 d'une plage de la feuille Excel SOURCE connue de VBA comme étant l'objet WorkSheet Feuil1. Cette plage est définie comme allant des colonnes de A à E (ou de E à A, ce qui est pareil) et des lignes allant de la 5 jusqu'à la derrière renseignée de la colonne A. Au niveau de boucle le plus interne, Détail contient un tableau d'une dimension reprenant les valeurs de la ligne. Détail(5) représente donc la dépense engagée, colonne E.

 

[KIM]

Re : Répartir une somme au prorata de la somme attribuée par service

Bonsoir le fil,
Merci pour votre retour,
@Misange : Le fichier final à traiter est une compilation de plusieurs fichiers avec des tables de correspondances pour remplir les cases vides. Je vais reprendre un état réél et le mettre à disposition. J'aimerai trouver un code simple à modifier car les fichiers peuvent changer de struucture mais le principe est tujours le même.
@Danreb, Est-il possible, si le temps disponible te le permets de me commenter le code de la routine : Worksheet change car J'ai du mal à suivre le déroulement du code pour adapter le calul et la présentation du résultat à mon fichier final. Est-ce 1, 3 et 4 dans Grouporg représente les col Departement, OPER et SERVICES.
Merci d'avance pour votre aide
KIM

 

[Dranreb]

Re : Répartir une somme au prorata de la somme attribuée par service

C'est qu'à moi ça me parait tellement évident que je ne sais même pas quoi mettre comme commentaire.
Si, à la rigueur devant chacun des deux For Each Oper In Déptmt.Contenu, respectivement:

Rem. —— Calcul totaux du département engagés par services et commun à répartir pour ceux à blanc.
Rem. —— Calcul et production lignes tableaux pour chaque service.

À part cela la raison de mettre .Id ou .Contenu derrière les variables déclarées As SsGroup trouve son explication dans le minuscule module de classe de ce nom, et d'autres explications figurent aussi dans la Sub GroupOrg du module MClassement.

Est-ce 1, 3 et 4 dans Grouporg représente les col Departement, OPER et SERVICES.

Mais oui ! Vous voyez bien que vous avez compris !