Recherche sur 2 tableaux
Bonjour,
Je dois effectué pour ma boite un contrôle de comptes par mois et par cumul entre le budget et la comptabilité.
Le soucis c'est que je dois faire un contrôle de plus 60000 lignes et en passant par des formules le fichier devient inexploitable car très lourd;
Le fichier se compose de trois onglets (Onglet contrôle qui est alimenté par l'onglet BUDGET et COMPTA). voir exemple ci joint.
l'onglet contrôle est rempli en fonction de trois critères : Le n° ETS, le Mois controlé et le N° de compte. Les seuls éléments variables sont le N° ETS et le mois controle
Exemple pour le mois de janvier.
Partie Mensuel
la colonne BUDGET du contrôle mensuel est alimentée par les infos de l'onglet BUDGET se trouvant colonne E (Janvier)
la colonne COMPTA du contrôle mensuel est alimentée par les infos de l'onglet COMPTA se trouvant colonne J (Janvier)
Partie CUMUL
la colonne BUDGET du contrôle cumul est alimentée pour le mois de janvier par les infos de l'onglet BUDGET se trouvant colonne G (Cumul_01)
la colonne COMPTA du contrôle cumul est alimentée par les infos de l'onglet COMPTA se trouvant colonne V (CUMUL).
De plus sur un même Etablissement il peut y avoir plusieurs fois le même numéro de compte. Il faut donc faire la somme de ses numéros de comptes identiques.
Existe un moyen en VBA d'alimenter le contrôle en fonction du numéro de l'établissement et du mois.
J’espère avoir été claire dans la définition de mon problème.
Merci par avance de l'aide que vous pouvez m'apporter
bonjour
tu veux juste comparer les dépenses réelles et les dépenses budgétées par mois
je te propose la solution (un peu déroutante au début, mais trèèèèèèèèèès puissante)
des millions de lignes ne lui font pas peur !
on met les données dans le modèle de données (Power Query puis Power Pivot que tu connais sans doute vu ton Excel 2016 )
on crée une liaison entre les 2 tables par le N° de compte
et on fait un TCD
restera à ajouter les soustractions, et les cumuls
je me suis limité à janv et fév faute de données, ton fichier est maigre
pense qu'on pourra aussi comparer des années entre elles avec ce principe !
Bonjour janakka-horus ,
Pourrais tu me confirmer que tes données compta sortent d'une balance des comptes généraux?,
L'idéal serait de sortir un grand livre des comptes généraux,
Et mettre sous forme de table le budget et le grand livre, ainsi comme le précise @jmd utiliser power pivot et TCD,
Cordialement,
Merci
Pour cette solution mais le fichier comptable est généré automatiquement par le logiciel comptable.
les données se rafraîchissent automatiquement et je ne peux pas transformer le fichier en tableau.
C'est pour cela que j'ai pensé à la solution du VBA.
Merci Massari,
le fichier compta est généré à partir d'une fonction du logiciel comptable (CODA EXCEL).
Sortir un grand livre est trop lourd. Car la société gère plus 800 établissement.
Bonjour à tous
Tu peux faire la synthèse par PowerQuery et PowerPivot dans un autre fichier que celui exporté par la compta : ces deux modules savent traiter des données situées dans un autre classeur.
Ce sera plus efficace que du VBA que tu ne pourras pas non plus stocker dans le classeur s'il est écrasé régulièrement...
Merci
Pour cette solution mais le fichier comptable est généré automatiquement par le logiciel comptable.
les données se rafraîchissent automatiquement et je ne peux pas transformer le fichier en tableau.
C'est pour cela que j'ai pensé à la solution du VBA.
nul besoin de tableau
Power Query peut "nettoyer" la feuille avant de mettre les données à dispo (supprimer des lignes, réorganiser etc)
tu le connais ? je suppose que oui puisque tu connais VBA bien plus complexe.
nul besoin de VBA,
Bonjour jmd,
C'est un ami qui m'a conseillé de passé par VBA, et je dois avouer que je ne sais pas utiliser power pivot et power querry.
Comment alimenté ensuite l'onglet contrôle à savoir que je n'ai pas le droit de modifier la présentation et la mise en page.
Cordialement
Bonjour jmd,
tu a écrit :Power Query peut "nettoyer" la feuille avant de mettre les données à dispo (supprimer des lignes, réorganiser etc)
tu le connais ? je suppose que oui puisque tu connais VBA bien plus complexe.
nul besoin de VBA
c'est très bizarre, mais je crois bien que même si VBA est complètement inutile (comme chacun sait), je crois qu'il est possible de piloter Power Query avec du VBA.
dhany
Re
Bien que mon post n'ait pas retenu ton attention, je complète
C'est un ami qui m'a conseillé de passé par VBA, et je dois avouer que je ne sais pas utiliser power pivot et power querry.
L'ami est sympa mai pourquoi ne donne-t'il pas le code ?
Tu ne connais semble-t-il pas plus VBA que PowerQuery et PowerQuery est plus facile à apprendre que VBA...
Comment alimenté ensuite l'onglet contrôle à savoir que je n'ai pas le droit de modifier la présentation et la mise en page.
Qui a choisi la mise en page et quelle est la raison de cette immuabilité ?
(C'est mon boulot de poser ce genre de question car, en charge d'optimiser le fonctionnement, je cherche toujours à comprendre ce qui est vraiment incontournable dans les méthodes utilisées et ce qui peut évoluer... donc déformation professionnelle...)
Pour info j'avais obtenu cela, dans un classeur indépendant, juste avec PowerQuery et un TCD (en intervertissant juste 2 numéros de compte dans ta source car ton exemple de Control n'est pas cohérent avec les données sources).
On pourrait y ajouter des colonnes si l'exemple n'était pas quasi vide...
Bonjour jmd,
C'est un ami qui m'a conseillé de passé par VBA, et je dois avouer que je ne sais pas utiliser power pivot et power querry.
Comment alimenté ensuite l'onglet contrôle à savoir que je n'ai pas le droit de modifier la présentation et la mise en page.
Cordialement
ton ami connaît VBA, il ne connaît pas Excel.
les données restent dans leur fichier
le contrôle se fera avec un TCD, à toi de présenter au mieux. Il est certain qu'il faut faire joli (les chefs adorent le joli, ça fait passer la pilule si les écarts sont négatifs ! )
as-tu pu regarder PQuery et PPivot ?
c'est très bizarre, mais je crois bien que même si VBA est complètement inutile (comme chacun sait), je crois qu'il est possible de piloter Power Query avec du VBA.
(non, non, c'est juste une info comme ça, en passant au détour du chemin : j't'assure que j'ai pas écrit ça pour te rendre malade, ni pour te faire faire d'horribles cauchemards ! )
salut dhany
avec VBA on peut créer Google !
mais je le déconseille
aujourd'hui, on peut se passer de VBA,, à condition de connaître Excel.
@jmd
un tableur comme Excel permet surtout de faire des calculs ; une base de données comme Access permet surtout de stocker des données, dans un 1er temps ; puis dans un 2ème temps, d'interroger la base de données via des requêtes / formulaires pour faire ressortir les données qui nous intéressent ; exemple : la liste des clients de Paris avec qui on a eu un chiffre d'affaires d'au moins 1 000 € ; chacune de ces applications a ses propres spécificités (quels que soient leurs avantages et inconvénients respectifs).
là où je veux en venir, c'est que Power Query est un logiciel qui permet de transférer les données de l'un à l'autre : soit dans le sens Access vers Excel pour faire des calculs sur les données importées dans Excel ; soit dans le sens Excel vers Access pour exporter et stocker des données d'Excel ; on peut aussi appliquer un filtre pour que seule une partie des données soit concernée, et pas toutes les données dans leur intégralité ; c'est bien ça, n'est-ce pas ?
est-ce qu'il y a une autre utilité de Power Query ou non ? si ça fait juste ce que j'ai décrit, je reconnaît que c'est déjà beaucoup ! mais toi qui connaît très bien Power Query, peut-être peux-tu ajouter une autre utilité que je ne connaît pas, et qui est peut-être apparue sur une version récente alors qu'elle n'existait pas avant ?
vu qu'je n'utilise plus de base de données (seulement Excel, Word, Outlook), Power Query n'a donc pas d'utilité pour moi, il me semble ; mais peut-être que je me trompe ?
question subsidiaire : le Power Query qui a été intégré à Excel 2016 a-t-il une utilité sans connexion avec une base de données externe (Access ou autre) ?
à te lire pour avoir ton avis de spécialiste Power Query.
j'invite Jean-Eric, qui est un autre éminent spécialiste de Power Query, à donner son avis sur la question (s'il le veut bien).
dhany
re dhany
tu fais une erreur : Excel sert à 99,99% à la gestion de données
souvent mono-table, parfois multi-tables (et il faut alors des RECHERCHEV pour retomber sur du mono)
on peut saisir dans Excel (90% des cas sur ce forum) ou ailleurs (Access est rare ici, on trouve des progiciels de toutes sortes qui exportent sous Excel ou csv
dans tous les cas, Power Query permet de récupérer et pré-traiter des données, qui seront ensuite analysées sous Excel dans des TCD par exemple
mieux : PQUery les injecte dans Power BI, c'est tellement mieux. Mais on a un train de retard en France sur ce sujet
PQUery est un ETL dont tu ne peux deviner la puissance.
https://fr.wikipedia.org/wiki/Extract-transform-load
essaye-le
ce sera facile pour toi qui maîtrises la programmation et la logique.
une merveille
sur ton Excel 2007, je ne sais pas
alors sous Power BI Desktop gratuit (à télécharger de toute urgence)
amitiés excelliennes
@jmd
tu a écrit :Excel sert à 99,99% à la gestion de données
oui, d'accord avec ça, je l'ai jamais remis en cause : c'est implicite dans le sens où quand j'ai écrit « Excel permet surtout de faire des calculs », il faut bien qu'il y aie des données pour faire des calculs dessus : c'est évident, bien sûr ! car on saisit des données, et les formules Excel sont sur ces données.
mais là où une base de données comme Access apporte de gros avantages par rapport à Excel (même si c'est un logiciel moins répandu et moins connu), c'est toutes les spécificités d'Access, justement : celles dont j'ai déjà parlé plus haut, qui dépendent des 4 objets d'Access particuliers :
1) toutes les données sont stockées dans des Tables
2) les Requêtes permettent d'interroger la Base de données
3) les Formulaires permettent une présentation agréable (et c'est bien plus simple à réaliser qu'un UserForm Excel, surtout si on utilise « Formulaire instantané ») ; de plus un formulaire permet ce qui a été appelé « une interrogation par l'exemple ».
4) les États permettent d'imprimer les données (là aussi, c'est plus simple à réaliser qu'un UserForm Excel, surtout si on utilise « État instantané »)
avec en plus les Relations car ACCESS est un SGBDR : Système de Gestion de Base de Données Relationnelles (serait-ce que tu apprécies autant Access que le VBA ?
mais si tu tiens absolument à programmer en VBA Access, sache que c'est très possible !
si je n'utilise plus Access, c'est uniquement car en fait, je n'ai aucun fichier perso avec un gros volume de données tel que son utilisation soit réellement justifié ; mais c'est sûr que ce logiciel est très apprécié en entreprises (pas toutes, bien sûr, mais quand même beaucoup !).
si plus tard ça devait arriver que j'ai des fichiers persos avec un gros volume de données, j'utiliserai de nouveau Access car je l'ai conservé ; il me suffira de le réinstaller.
tu a écrit :Power Query permet de récupérer et pré-traiter des données, qui seront ensuite analysées sous Excel dans des TCD par exemple
mieux : PQUery les injecte dans Power BI, c'est tellement mieux.
là, tu confirmes ce que j'ai écrit : PowerQuery n'est utile que conjointement avec une autre application, que ce soit en important des données ou en les exportant ; donc à contrario, PowerQuery n'est pas utile si on utilise que l'application Excel !
il se trouve que je suis très pragmatique : ça ne me dit rien d'utiliser une application dont je n'ai pas l'utilité.
mais toi qui es en entreprise et peut-être même patron de ta boîte, je comprends que tu trouves un intérêt primordial à tous tes outils préférés : TCD, PowerQuery, Power BI Desktop ; et c'est dit très sérieusement, sans aucune ironie : je suis sûr que dans ta situation, je serais moi aussi très probablement tenté ! toujours juste pour info, songe aussi que j'ai jamais eu besoin d'utiliser des fichiers CSV (ni en importation, ni en exportation) ; et avec Word, même si je sais très bien comment faire un publipostage, j'en n'ai jamais eu besoin à titre perso : c'est certainement pas moi qui vais m'amuser à perdre du temps à inonder les boîtes mails de spams et autres pourriels !!!
en fait, en logiciels bureautiques, j'utilise que Excel tout seul (dont VBA), Word tout seul (sans VBA), Outlook tout seul (sans VBA) ; une seule exception : j'ai un classeur Excel pour gérer mon Budget perso, et par VBA, je crée automatiquement une lettre Word qui reprend certains éléments du Budget, pour une demande spéciale au Centre des Impôts.
dans mon ancien pack Office 2003, j'ai à la fois Access et PowerQuery, et quand j'avais utilisé un peu Access, ça m'étais arrivé d'utiliser un peu PowerQuery, pour exporter des données d'Access dans Excel (justement
bien plus tard, quand j'en aurai plus le temps, je t'indiquerai mon avis au sujet des Tableaux structurés et des TCD.
dhany
Re
Je veux pas polémiquer mais expliciter
un tableur comme Excel permet surtout de faire des calculs ; une base de données comme Access permet surtout de stocker des données, dans un 1er temps ; puis dans un 2ème temps, d'interroger la base de données via des requêtes / formulaires pour faire ressortir les données qui nous intéressent
Access n'est pas une base de données mais un système de gestion de base de données relationnelle. C'est la Twingo des SGBDR mais quasi toutes les applications de gestion sont construites autour d'un SGBD : paye, ERP, CRM (pas sur Access mais ORACLE , SQ Serveur ou équivalent mais c'est la même logique).
Power Query est un logiciel qui permet de transférer les données de l'un à l'autre : soit dans le sens Access vers Excel pour faire des calculs sur les données importées dans Excel ; soit dans le sens Excel vers Access pour exporter et stocker des données d'Excel ; on peut aussi appliquer un filtre pour que seule une partie des données soit concernée, et pas toutes les données dans leur intégralité ; c'est bien ça, n'est-ce pas ? est-ce qu'il y a une autre utilité de Power Query ou non ?
Je peux me tromper mais je ne crois pas que PowerQuery fonctionne depuis Access.
PowerQuery accède aux données de toutes sortes de sources, dont Excel (même classeur ou autre(s) classeur(s), peut les combiner, tant en requête de relation qu'en requête union, et faire dessus toutes sortes de traitements y compris des calculs.
Sa logique est un peu différente d'un requêteur SQL mais on peut faire beaucoup de choses et notamment croiser et décroiser des données, ce qui est très puissant.
vu qu'je n'utilise plus de base de données (seulement Excel, Word, Outlook), Power Query n'a donc pas d'utilité pour moi ?...
... le Power Query qui a été intégré à Excel 2016 a-t-il une utilité sans connexion avec une base de données externe (Access ou autre) ?
OUI, OUI et OUI. Je ne l'utilise quasiment que pour Excel... Qu'il soit en add on (2010 et 2013) ou intégré (2016) ne change rien à part quelques aspect de l'interface
Pour en revenir à la question initiale j'ai refait le tableau de contrôle avec très peu d'instructions par rapport au code VBA (j'en fait aussi contrairement à jmd) qu'il faudrait pour traiter ces 2 sources totalement disparates : les noms de mois ne correspondent pas, de même que les autres en-têtes, le principe du cumul n'est pas le même, le tableau COMPTA a un en-tête de fichier au dessus des données...
Re
Bien que mon post n'ait pas retenu ton attention, je complète
C'est un ami qui m'a conseillé de passé par VBA, et je dois avouer que je ne sais pas utiliser power pivot et power querry.
L'ami est sympa mai pourquoi ne donne-t'il pas le code ?
Tu ne connais semble-t-il pas plus VBA que PowerQuery et PowerQuery est plus facile à apprendre que VBA...
Comment alimenté ensuite l'onglet contrôle à savoir que je n'ai pas le droit de modifier la présentation et la mise en page.
Qui a choisi la mise en page et quelle est la raison de cette immuabilité ?
(C'est mon boulot de poser ce genre de question car, en charge d'optimiser le fonctionnement, je cherche toujours à comprendre ce qui est vraiment incontournable dans les méthodes utilisées et ce qui peut évoluer... donc déformation professionnelle...)
Pour info j'avais obtenu cela, dans un classeur indépendant, juste avec PowerQuery et un TCD (en intervertissant juste 2 numéros de compte dans ta source car ton exemple de Contrôle n'est pas cohérent avec les données sources).
On pourrait y ajouter des colonnes si l'exemple n'était pas quasi vide...
AnalysePQ.jpg
Bonjour l'ami en question je lui ai parle au téléphone quand je lui ai exposé mon problème.
La mise en page à été faite par le service financier et il n'aiment pas trop que l'on touche à leur fichier. en fait le fichier est plus long que sur le fichier que j'ai mis en exemple. en fait il se présente de la manière suivante
C'est par mesure de simplification que je vous ai présenté le fichier exemple comme cela. Je crois que finalement je n'aurai pas dû
l'onglet compta est généré automatiquement et st se rafraichit de mème à chaque modification apporté en compta grâce à la fonction CODA EXCEL (il s’agit d'un Add in de notre logiciel de comptabilité CODA).
Je dois dire que l’exemple que tu m'a soumis me conviendrai parfaitement.
Et si tu pouvais m'explique la manière dont tu a procéder je serai ravi de l'apprendre. Car comme je l'ai dit plut haut je ne maitrise pas du tout power querry et pivot
RE
Les colonnes A à I sont réalisables par TCD (sauf la colonne F vide)
(Cela me rappelle un TCD de type compte de résultat fait pour un de mes clients)
Cela nécessite une liste des comptes avec les 4 niveaux de groupes de rattachement.
Cependant les colonnes J à M semblent être saisies, laissant penser que vous gardez un classeur par mois...
Il faudrait donc copier le TCD en valeurs pour ne pas risquer de désynchroniser ces colonnes de celles du TCD
On peut aussi, en partant de ce modèle, le remplir en exploitant par formule INDEX EQUIV une synthèse PowerQuery à plat mais le classeur sera plus lourd.
Concernant ton processus des questions se posent :
Les exports Compta et le Budget sont des fichiers à part ou s'ajoutent à celui de synthèse ?
Si oui à un modèle vide ou déjà rempli ?
Sachant que la synthèse semble limitée à un mois, cela suppose de filtrer les données à traiter chaque mois...
Si les données se complètent au fil des mois, les anciennes peuvent avoir évolué d'un export à l'autre ?
Que ce soit par VBA ou PowerQuery , voire un mix, il faut comprendre ce processus et une liste de correspondance entre les titres de colonnes des 2 source serait utile (en dehors des mois, cumuls et comptes)...
Bonjour Chris,
tu a écrit :Je veux pas polémiquer mais expliciter
même si tu ne m'y a pas invité
désolé, Chris, je t'aurais invité avec plaisir si j'avais su que tu étais intéressé par mon dialogue avec jmd ! aussi, tu as très bien fait de nous rejoindre !
tu a écrit :Access n'est pas une base de données mais un SGBDR
oui, je le sais bien : je l'avais écrit sous le point 4) :
tu a écrit :Je peux me tromper mais je ne crois pas que PowerQuery fonctionne depuis Access.
je n'ai jamais dit cela !
[quote="toujours à propos de PQuery, tu"]OUI, OUI et OUI. Je ne l'utilise quasiment que pour Excel...[/quote]
je crois que tu as plutôt voulu dire : « je l'utilise que dans Excel, pour y importer des données d'une autre source qu'Excel lui-même : par exemple un SGBDR (Access ou autre) » ; comme tu n'as pas décrit d'utilisation de PQuery dans Excel sans utiliser de source externe de données, je pense toujours que PQuery est inutile si on se sert uniquement d'Excel.
en tout cas, c'est sûr à 100 % que pour tout c'que j'fais en VBA, j'ai jamais eu besoin d'utiliser Power Query !
t'as vu, jmd ? c'est d'la magie : abracadabra, j'fais du VBA sans les mains !
dhany
RE
tu a écrit :Access n'est pas une base de données mais un SGBDR
oui, je le sais bien : je l'avais écrit sous le point 4)
Oui j'ai vu que tu avais édité ton message pendant que je rédigeais le mien (les grands esprits se rencontraient, lol
[quote=dhany post_id=684619 time=1535684565 user_id=51200][quote="toujours à propos de PQuery, tu"]OUI, OUI et OUI. Je ne l'utilise quasiment que pour Excel...[/quote]
je crois que tu as plutôt voulu dire : « je l'utilise que dans Excel, pour y importer des données d'une autre source qu'Excel lui-même : par exemple un SGBDR (Access ou autre) »
[/quote]
Non j'ai bien précisé "PowerQuery accède aux données de toutes sortes de sources, dont Excel (même classeur ou autre(s) classeur(s), peut les combiner, tant en requête de relation qu'en requête union, et faire dessus toutes sortes de traitements y compris des calculs"
Excel n'a jamais été au top avec les gros volumes de données : RECHERCHEV, SOMMEPROD et autres ont des limites importantes par rapport à ce que peut faire une relation et rament dès que le volume est important.
Avec PowerQuery on fait, en 4 ou 5 commandes, sans même avoir besoin de connaître le code M, des choses équivalentes à pas mal de lignes VBA.
Pour un utilisateur qui ne connait ni l'un, ni l'autre, l'abord est bien plus facile.
Pratiquant les deux, contrairement à JMD qui se refuse au VBA et à toi qui a une vison très restrictive de PowerQuery (tu as, semble-t-il, utilisé la toute première version serveur), je tire autant que possible le meilleur des deux, comme je le fais dans les mariages Excel et Access par exemple...
Je n'ai aucune action chez Microsoft, ai même participé activement à une pétition à propos de PowerPivot, critique souvent des choix ou politiques MS, mais quand un outil ou une fonctionnalité nouvelle est efficace, je m'en saisis...