Extraction de donnés (tri et somme) vers un autre tableau Excel

Bonjour,

Je suis actuellement en stage dans une mairie au service comptablité.

Je suis chargé de ventilé les dépenses de tous les comptes 6 vers différents code service mis à place par la mairie, il y a 6 services et 20 sous code services en tout.

Exemple, dans le compte 611 prestation de services, il y a eu x € de dépenses pour l'espace culturel (code service 1099), le tableau ressemble à cela :

Pour se faire, j'ai accès au bilan complet sur excel du compte 6 (plus de 8000 lignes) et les codes services sont déjà assignés à chaque ligne. Comme ceci :

C'est un travail fastidieux car dans le compte 6, il existe une multitude de compte passant du 611 au 6156 au 6338 etc... et chaque sous compte du compte 6 à des dépenses dans différents code service.

Mon idée, est de permettre à la mairie d'automatiser cette tâche pour qu'au final pour les prochaines années, grâce au VBA, le programme trie les codes services dans l'ordre en fonction des comptes (par exemple rassembler toutes les dépenses sur le code service 1099 du compte 611, en faire la somme et extraire la donnée pour la mettre dans le premier tableau à sa case correspondante).

Je ne sais pas si j'ai été assez claire, est-il possible de faire ça ? Est-ce compliqué sans notion en VBA ? Des conseils pour m'éclairer sur la marche à suivre ?

Bonjour

Avec 2019, cela doit être simple à faire avec PowerQuery, intégré à Excel

Ce qui n'est pas très clair dans tes explications et l'image c'est s'il faut ventiler la dépense selon une clé de répartition ou simplement synthétiser

Il faudrait un exemple représentatif du ou des fichiers en expurgeant les données nominatives qu'on comprenne bien d'où tu pars et où tu veux arriver

Bonjour,

Comme l'a dis chris, il existe déjà des outils pouvant synthétiser les dépenses, que ce soit des TCD, PowerQuery, des formules...

Le souci c'est plus de bien visualiser tes fichiers, on voit ta synthèse mais pas le reste, je rejoins chris sur le fait qu'il serait utile d'avoir un fichier avec des données devenues bidons mais gardant les codes et leur structure, ça nous permettra de donner un avis plus précis.

Merci pour vos réponses, je m'occupe de vous faire un exemple détaillé dans la soirée pour que vous y voyez plus claire.

Je comprends tout à fait votre incompréhension, smplement pour répondre à Chris avant d'envoyer mon explication et mon exemple: non ce n'est pas en fonction d'une clé de réparition, c'est simplement une ventilation des dépenses, je me charge de détailler cette explication ce soir.

Me revoilà, je vais tenter d'explication au mieux ce que je souhaite faire.

Voici le fichier Excel avec le tableau ou je souhaite intégrer les dépenses préalablement extraites de l'autre fichier.

Etapes :

1 - Je souhaite supprimer les colonnes inutiles, et ne garder que celle en jaune, à savoir C / G / V. Je souhaite intégrer cette étape dans le VBA car à chaque extraction du logiciel comptable, le fichier Excel aura ce format la. Et pour la ventilation des dépenses il me faut juste C / G / V. "G = liquide" qui correspond au montant de la dépense.

2 - Faire le totale générale de chaque compte (par exemple combien de dépenses totales pour le compte 611 prestation de services). Cette partie correspond à l'encadré en rouge, soit les dépenses réalisées en 2020. Faire le total de tous les comptes n'est pas prévu, car la formule est déjà implanté. L'imputation des comptes en fonction du type de la dépense est dans la colonne "C = compte" dans l'autre fichier.

3- Après avoir fait le total par compte, je souhaiterais ventiler les dépenses du compte en fonction du code service. Sur l'autre fichier, chaque code est stipulé en fonction du type de la dépense sur la ligne. Le code du service est la ligne "G = Axe1".

Les sous-totaux étant déjà programmés, pas besoin de les automatiser.

C'est un projet qui me tient à cœur et qui serait en lien avec mes études l'année prochaine en master d'ingénierie financière (finances, mathématiques, programmation informatique). C'est un travail fastidieux pour eux, qui leur prend beaucoup de temps mais qui est réclamé chaque année par les élus.

Je n'ai aucune connaissance en VBA malheureusement et ce projet m'aiderait à mettre un pied dans le VBA et me servirait d'appui pour mon dossier de candidature au master.

Je ne cherche pas à ce que l'on me serve sur un plateau ma demande, simplement une orientation, des conseils, seraient déjà précieux pour que je mène à bien ce travail.

Il est vrai que je pourrais le faire via un TCD, mais mon but c'est d'automatiser le plus possible cette tâche fastidieuse.

Je dispose de 5 semaines pour ce projet. Qu'en pensez-vous ?

J'espère avoir été claire dans mes explications.

Bonne soirée.

Bonjour

Je propose

• un prétraitement par PowerQuery (intégré à Excel ) qui élimine les colonnes inutiles, découpe les colonnes compte et axe1 pour en extraire le compte et l'axe hors libellés
• 2 TCD permettant de récupérer l'un le détail, l'autre les totaux
• des formules dans le tableau récapitulatif

Il est possible de faire de ce classeur un modèle en vidant le tableau Compte6 (on pourrait, plutôt qu'un onglet compte6, utiliser directement un fichier externe) et par VBA :

• l'alimenter avec les nouvelle données,
• actualiser tout,
• remplacer les formules de l'onglet ventilation des dépenses par leur résultat et sauvegarder en tant que nouveau classeur

Merci pour votre message détaillé, je comprends maintenant ou je dois aller.

J'ai quelques points à éclaircir si cela ne vous dérange pas :

• un prétraitement par PowerQuery (intégré à Excel ) qui élimine les colonnes inutiles, découpe les colonnes compte et axe1 pour en extraire le compte et l'axe hors libellés

Par Power Query, vous entendez Power Pivot ? Je n'ai jamais utilisé cet outil, je vais me renseigner pour en apprendre davantage.

• 2 TCD permettant de récupérer l'un le détail, l'autre les totaux

- Lorsque je rentre le tableau avec les vrais données à la place de celui que je vous ai envoyé, les montants sur la ligne liquide reste le même dans le tableau de ventilation des dépenses, le TCD ne se modifie pas, est-ce normal ? Ce n'est pas réellement un problème, je pourrais leur expliquer comment faire un TCD, c'est pour moi car je dois en refaire un pour avoir les bonnes valeurs, cela ne se modifie pas automatiquement ?

• des formules dans le tableau récapitulatif

Effectivement, j'ai remarqué que certaines formules n'étaient pas la fonction "=somme" mais une suite d'addition, j'ai modifié cela. C'est bien de cela que vous parlez ?

Ma dernière question concerne le remplissage du tableau de ventilation des dépenses, comment avez-vous fait pour extraire toutes les données ?

RE

PowerQuery et PowerPivot sont deux choses différentes

Le premier est un requêteur intégré à Excel depuis la version 2016,
le second peut combiner des données dans une base cube pour les restituer dans des TCD, intégré à Excel depuis la version 2013,
mais les deux étaient disponibles en add on dès la version 2010

PowerQuery est plus facile à apprendre et c'est un peu le couteau suisse qui va permettre de traiter des tables de données.

Pour actualiser après changement du tableau Compt6 il faut utiliser Données, Actualiser tout : tout TCD et toute requête PowerQuery nécessitent une actualisation

Je parlais de toutes les formules que j'ai ajoutées dans le tableau récapitulatif à base de LIREDONNEESTABCROISDYNAMIQUE, fonction qui va récupérer la bonne information dans les TCD

Merci pour votre message.

Je me renseigne sur Power Query.

J'ai l'impression que tout est faisable en quelques minutes grâce à vous au final, le VBA est-il donc nécessaire ?

Car si au départ nous effectuons des TCD, puis on utilise la formule pour lire les données du TCD pour le tableau de ventilation des dépenses, que reste-t-il pour VBA ?

A quoi sert la formule SERIEUR ?

Bonne journée.

Bonjour

Ajourd'hui Excel a pas mal de possibilités qui évitent d'utiliser VBA (on ne réinvente pas l'eau tiède)

J'ai précisé ce qui pouvait éventuellement être ajouté en VBA pour en faire un outil utilisable chaque année à partir d'un modèle pré-maché et en remplaçant les formules par leur résultat. On peut d'ailleurs supprimer aussi la requête et le TCD lorsque les formules ont été enlevées.

Bonjour,

Est-il possible d'enregistrer un MACRO VBA sans passer par la programmation informatique ?

Je vois qu'il existe une fonction "enregistrer une macro", veuillez m'excuser j'ai un peu de mal à comprendre à quoi le VBA me servirait.

Je suis un peu "largué" avec le langage Excel, j'ai un sacré retard à combler pour l'année prochaine ahah.

Bonjour,

je vois que chris s'occupe bien de vous, je dois avouer ne pas être à l'aise avec Power Query pour le moment, j'ai principalement dû travailler sans pour le moment, pour répondre à la question sur VBA, on peut dire que VBA compense ce que Power Query et Excel ne peuvent pas faire, il m'est arrivé récemment un sujet qui ne pouvait pas être résolu je pense avec cet outil, car les données de bases n'étaient pas propres, beaucoup de nettoyage était à faire pour pouvoir utiliser les données, et ce de façon automatisée.

Il existe en effet un bouton "enregistrer une macro", ça permet de générer du code à partir d'actions que l'on va faire dans le classeur, mais c'est un outil limité qui ne permet pas de tout faire et n'est pas optimal, mais c'est utile pour ceux qui débutent.

Chris pourra sûrement mieux vous renseigner sur le sujet, il a plus d'expérience que moi.

Chris prend le temps d'expliquer, c'est très gentil de sa part.

J'ai seulement un peu de mal avec le vocabulaire qu'il emploie, mais ça viendra avec l'utilisation, en ce moment j'essaie de voir comment fonctionne la formule LIREDONNETCD.

Merci pour votre réponse sur le VBA, donc le mieux serait d'en faire un modèle mais j'ai encore du mal à savoir ou je dois aller.

Peut-être que le projet est trop complexe pour moi, mais j'ai 5 semaines j'espère que ca le fera. :)

RE à tous

Rien n'oblige à aller jusqu'au VBA dans cet exemple. Comme je le disais dans un des posts on pourrait laisser ce qui est dans Compta6 dans un classeur externe (en modifiant un poil la requête)

Chaque année on écrase l'ancien fichier par le nouveau et on actualise : la requête se recalcule, les TCD aussi et les formules suivent.

Rien à toucher dans Excel, juste le fichier à copier sous le même nom au même endroit ou bien on peut prévoir une cellule pour indiquer le chemin et le nom du dossier où la requête doit s'alimenter.

RE

Merci pour cette réponse, je vais donc oublier VBA pour le moment, je comprend ou tu veux en venir avec les TCD.

Cependant je n'arrive pas à utiliser la formule LIRELESDONNEESDUTCD, il y a quelque chose que je dois mal faire.

RE

C'est LIREDONNEESTABCROISDYNAMIQUE

Je les ai déjà placées dans le tableau

Le plus simple on tape = puis on va cliquer dans la cellule du TCD adéquate et ensuite on remplace les valeurs en dur de Compte et Axe pare xemple, par la référence aux cellules qui contiennent l'info afin de pouvoir recopier le formule.

On imbrique toujours dans un SIERREUR au cas où la valeur n'existe pas dans le TCD ou si celui-ci est filtré.

Oui pardon j'avais mis TCD pour aller plus vite.

Ce que je n'arrive pas c'est étiré la formule, même si vous avez déjà tout mis, et je vous remercie énormément pour cela, j'essaie de comprendre tout de même le mécanisme (et ce n'est pas gagné).

Voici un exemple :

J'ai l'impression que vu que vous avez fait une classification au préalable, vous avec juste à prendre le chapitre et le numéro de compte (en rouge et en bleu sur le screen) et que cela vous met automatiquement la valeur. Je me trompe ?

Merci pour votre patience.

RE

Pour les totaux on utilise $A$5 pour la classe et $A6 pour le compte et le 2ème TCD

On ne peut étirer que pour une classe puisqu'il faut adapter $A$5 quand on change de classe

Pour le détail on se base sur F$2 pour l'axe et $A6 pour le compte et le 1er TCD et on peut étirer à droite et vers le bas

Vous arrivez à adapter vos explications quand la personne en face de vous ne comprend pas totalement, vous feriez un très bon professeur (à moins que vous ne l'êtes déjà ?). Grâce à vous, j'ai enfin compris ! Je vous remercie mille fois pour votre patience, ce fut un réel plaisir d'échanger avec vous.

J'ai simplement une dernière question : à un moment, dans la formule vous insérez un "&", quel est son utilité ?

RE

Merci. Oui je donne des cours...

Le tableau de synthèse a le compte en numérique en $A6 et j'ai laissé en l'état, et le TCD en texte : le &"" force Excel à lire $A6 en texte