Base de données trop volumineuse
Bonsoir à tous,
Je suis actuellement confronté à un problème majeur que je n'arrive pas à solutionner.
Brièvement, ma feuille excel qui met en forme mes données se base sur des RECHERCHEV qui attaque ma base de données. Celles-ci étant VOLUMINEUSE, le temps de mise à jour excel est insupportable.
Je joins un fichier exemple pour que vous visualisiez la chose.
J'ai essayer d'utiliser d'autres types de fonctions (INDEX, DECALER) sans succès.
Auriez-vous des pistes, car chaque mois la base croit davantage !!!!
J'ai pensé à autre chose, cependant je ne sais pas si c'est facilement réalisable et surtout si je peux gagner en rapidité.
Ma recherche verticale se fait par défaut sur toute ma base de donnée, serait-il possible dès le choix de mon nom et de ma période via les zones de liste déroulante, de restreindre automatiquement ma plage de données où rechercher à l'aide d'une macro vba.
Comment pourrais t-on mettre ça en place? Çà m'a l'air bien compliqué.
Avez-vous déjà été confronté à ce genre de problème?
Je vous remercie d'avance pour votre aide.
Bonjour,
Effectivement pour des bases de données volumineuses, il est préférable d'abandonner la plupart des formules (idéalement toutes...)au profit des macros.
Dans ce contexte une clef comme celle de la colonne G est parfaitement inutile.
Cependant la mise au point des macros étant aussi délicate que celle des pièces d'horlogerie il est préférable d'avoir un vrai classeur de travail (avec seulement quelques enregistrements bidons)
De plus la conception de la base de donnée doit répondre à quelques règles strictes :
- pas de colonnes ni de lignes inutiles à gauche, à droite, au-dessus.
- En principe les extractions ou statistiques se font sur des pages secondaires (ou/et) des userforms...
Toutefois par exeception le classeur suivant ne fait appel à des macros mais à des formules extrêmement puissantes et rapides qui ne devraient pas poser de problèmes sur des BD structurées comme indiqué plus haut (même pour plusieurs centaines de milliers d'enregistrements et plusieurs centaines de requêtes...
Voir la feuille Extract : les cellules jaunes pilotent l'extraction demandée.
A+
Bonsoir, petite question. une base de donnée en ligne puis sur une autre feuille un TCD ne serait-ce pas plus simple ?
Frédéric38 a écrit :Bonsoir, petite question. une base de donnée en ligne puis sur une autre feuille un TCD ne serait-ce pas plus simple ?
Bonjour,
Les BD** sont parmi les outils les plus puissants d'Excel, avec les macros.
Après pour les situations les plus simples tous les outils peuvent convenir c'est juste une question de savoir faire et d'exigence...
A+
Bonsoir à vous,
J'étais en train de faire du vba (voir fichier joint). Savez-vous si quelque chose comme ça pourrais me donner plus de satisfaction? Au passage, mon VLOOKUP plante lorsque la correspondance n'existe dans mes données, pouvez-vous y jeter un œil s'il vous plaît?
Sinon, j'ignorais l'existence de BD***. J'ai hâte de mettre ça en application demain. J'espère vraiment que cela va fonctionner. Si la taille de ma BDD est importante, le nombre de requêtes est lui aussi conséquent
Mon classeur est bien structuré, ma table de paramétrage dans un onglet, ma bdd dans un autre etc... Mais il se peut aussi que l'outil en l’occurrence excel ne puisse pas être satisfaisant.
Pour ce qui est d'un tableau croisé dynamique, la mise en forme risque d'être problématique non?
Merci à vous je vous souhaite une bonne nuit,
Bonjour,
On peut adosser des requêtes BD** à du code VBA,
La quantité de données et le nombre de requête ne sont pas un problème : Les BD** commence à prouver toutes leur utilité et leur puissance quand les TCD ne servent plus à rien... Avec une douzaine de copier/coller et en 10 minutes tu peux déjà faire une trame pour plusieurs centaines de requêtes.
Les BD** prouvent toute leur puissances dans des études sur des bases de données nationales : Par exemples chiffres d'affaires par catégories de produit,s par régions, départements et par périodes > pour nourrir des graphiques...
Je ne dis pas que c'est ce qu'il y a de plus simple à manier : Si Crosoft à sorti les TCD c'est parce que la majorité des utilisateurs avait la flemme de s'initier à cet outils : Il faut reconnaitre que pour les petits problèmes de budget familial les TCD c'est bien suffisant...
Mais adossé à un peu de VBA, dès que les affaires se corsent, les BD** c'est le pied...
De plus c'est quand même très très souple, avec un peu d'entrainement on s'en lasse pas... On peut même mettre les requêtes dans une feuille cachée et les résultats dans une autre feuille (de travail)
Nota : La colonne CLE ne me semble vraiment d'aucune utilité.
Si tu es un peu juste au départ pour la mise en place de tes requêtes n'hésite pas à en redemander...
A+
Bonjour Galopin,
Merci pour toutes ces explications. Les formules BD** m'étaient jusqu'alors étrangères alors qu'il s'agit certainement de la solution.
Je vais devoir quand même repenser la structure de ma bdd. Mais bon c'est un moindre mal. Je ferais ça ce weekend.
Sinon je me permet de reposer deux questions concernant mon VLOOKUP dans mon code vba.
- Utiliser le VLOOKUP en vba, ou une rechercherV dans une feuille excel est-ce équivalent en terme de temps de MAJ?
- Dans le fichier joint, mon code plante quand la valeur recherchée n'est pas trouvé. J'ai essayer un "On error resume next" mais ça ne me donne pas satisfaction. Pouvez-vous me dépanner?
Merci d'avance,
Je vous souhaite une bonne journée,
bonjour,
Utiliser le VLOOKUP en vba, est sans intérêt dans cette situation. De même que travailler directement sur ta BD.
Outre la gestion d'erreur "normale", un moyen de gérer ce problème en VBA c'est d'utiliser "Set" qui peut accepter les valeurs d'erreur. Ensuite on traite avec :
If not "variable" is Nothing then...A mon sens il n'y a que deux options pour traiter ton problème dont je concède qu'elle ne sont pas intuitive pour l'utilisateur lambda habitué à travailler avec sa petite douzaine de fonctions usuelles.
1 - Travailler en VBA sur des Array (en mémoire) car c'est environ 100 fois plus rapide que travailler sur la BD
2 - Travailler en VBA avec les BD** mais ça demande une bonne maîtrise des 2 outils et surtout des BD** :
En effet la manière d'organier le tableau de requêtes est essentielle pour la commodité et la facilité de la programmation.
Si tu compares mes 2 premiers exemple, les 2 semblent presque identiques pourtant la disposition du 2ème est bien plus pratique du point de vue de la programmation en effet tous les résultats s'incrémentent par copier / coller dans une boucle "For..." alors que dans le premier tableau c'est impossible.
(j'ai pourtant utilisé la même technique au départ mais dans le premier tableau (pour une meilleure lisibilité) j'ai supprimé une ligne vide sur2 dans la zone de résultats -colonne F à I- )
Il faut donc bien distinguer ce qui est essentiel la structuration des données et des requêtes (qui conditionne la rapidité des calculs)
et la présentation des résultats qui doit être faite en dernier ressort.
A+
Je ne connaissais pas les formules BD donc je vais creuser mon apprentissage dessus, merci
Galopin,
Merci pour avoir répondu à ma question sur le vlookup.
Concernant ton affirmation, "la disposition du 2ème est bien plus pratique du point de vue de la programmation en effet tous les résultats s'incrémentent par copier / coller dans une boucle "For..." alors que dans le premier tableau c'est impossible." , je vais essayer de mettre ça en place.
Objectif :
1) trouver l'équivalence de ma fonction BD** en VBA
2) comprendre le paramétrage et la définition des plages
3) essayer de faire une boucle sur les lignes de mon classeur afin de faire évoluer mes arguments (plages de données, critères) de ma fonction BD**. Quand tu parles d'incrémentation par copier/coller dans une boucle "For..." il s'agit bien de cela ou vois tu les choses différemment ?
Je te tiendrais au courant de mon avancé,
Bon après midi,
Bonjour,
Hum... ça dépend un peu du type de requêtes et de leur diversité.
Il m'est difficile de te répondre sans connaître exactement la totalité de tes besoins.
Une base de donnée comme celle que je connais avec 1 000 000 d'enregistrements une dizaine de vendeurs, autant de produits sur 35 mois est insignifiante...
La même base de donnée avec un millier de vendeurs répartis sur une centaine de département va surement nécessiter un traitement un peu différent surtout si tu veux faire des regroupements dans le temps (colonnes) ou géographique (lignes...)
Dans ton exemple il s'agissait de requêtes simples :
Seul le nom du "vendeur" peut changer et le "mois"
Dans mes 2 exemples le changement s'effectue sur la cellule jaune c'est elle que tu peux remplacer par une boucle "for..."
Cependant tu peux rencontrer (à minima) 2 types de problèmes :
1 - Les calculs vont être tellement rapides que tu sera à peine rentré dans ta boucle For... qu'elle sera déjà terminée. Donc tu n'auras pas le temps de voir (ni à plus forte raison de récupérer) les résultats...
Donc ton problème sera juste d'intercepter les résultats assez vite pour les répercuter dans le tableau ad-hoc
Dans ce cas tes tableaux ne comporterons aucune formule juste des valeurs calculées une fois par semaine (ou par mois...) au moyen de la macro.
2 - Si tu n'est pas dans ce cas et que tu veux des résultats "dynamiques" qui vont se mettre à jour à chaque fois que tu rajoutes un ligne dans ta BD. Dans ce cas ta macro va te permettre de construire une batterie de requêtes formulées, (Exactement les mêmes que précédemment sauf que tu auras tous les résultats d'Alain à partir de la Ligne 2, ceux de Pierre Ligne 22 ceux de Jean ligne 42...
On peut même imaginer que chaque vendeur ait sa propre feuille de résultats... indépendante de la feuille Extract.
On est toujours dans les cas les plus simples : Chaque requête ne comporte que 2 lignes dans la zone des requêtes (colonnes A à H)
Cependant si tu es vraiment dans le cadre d'une BD costaud avec des requêtes complexes la zone de requêtes peut occuper plusieurs lignes 3, 4 ou plus...
je ne m'étendrai pas plus sur cette possibilité qui ouvre de vastes horizons : Il importe d'en savoir beaucoup plus sur les conditions et le type d'extractions pour éviter de te faire un livre sur le sujet...
Consolation : Sauf exception je ne pense pas que tu aies besoin de faire des calculs sur des Array (en l'état actuel de tes besoins exposés...)
On peut supposer que tu n'auras pas besoin de chercher d'équivalent aux fonctions BD** dans VBA. En effet VBA va juste te servir à construire tes grilles pour éviter de nombreux Copier/Coller. Donc tu pourras te servir de l'éditeur de macro pour récupérer la formule que tu auras pondu sur la première ligne...
Bon tout cela est encore un peu nébuleux pour toi sans doute. Commence à faire des essais...
Sers toi de l'aide. Regarde ce dernier fichier joint qui illustre la grande souplesse d'utilisation des requêtes.
Il est vrai que mes extractions trouveraient un équivalent bien plus simple avec des formules classiques, mais ce qui est fort c'est qu'il y aurait eu 500 000 lignes ça n'aurait pas fait de différence !
Bon dans ce fichier j'ai fait un peu fort, c'est presque un peu merdique... mais si on gratte bien...
Après c'est juste une question d'habitude...
Fait une liste de toutes les requêtes dont tu vas avoir besoin et reviens me voir avec une BD un peu moins symbolique.
A+
Bonjour,
un essai en restreignant au maximum la plage des recherchev.
eric
edit: fichier supprimé
Bonjour,
Encore une fois, merci GALOPIN pour tes explications et ton petit fichier.
J'ai pu mettre en place les formules BD** et ça fonctionne plutôt bien pour l'instant. Mes requêtes ont au max 4 critères et 2 d'entre eux auront près de 300 valeurs différentes soit autant de requêtes
Cependant, je n'ai pas pu toutes les construire car il me manque des données que je dois ré-extraire dans des entrepôts.
Je re-testerais donc la semaine prochaine pour m'assurer de l'efficacité de la chose.
Par ailleurs, j'ai remarqué que mon classeur avec mes rechercheV se mettais à jour plus rapidement sur mon pc perso (4 sec) qu'avec celui du boulot (12 sec). D'un point de vue hardware, il n'y a pas de grandes différences, je m'interroge donc. Un accès serveur, la ram, un environnement 32 bits au lieu de 64bits peuvent avoir un impact mais de la à tripler mon temps de réponse ça me semble curieux
Est-ce qu'un classeur créé avec EXCEL 2010 et plus efficace qu'un classeur EXCEL 2003 ? Ou est-ce qu'un classeur créé avec EXCEL 2010 et ouvert avec 2003 peut être plus lent?
Enfin, j'ai une dernière question concernant les zones de listes (cf ton fichier galopin), les zones de listes créées avec 2010 ne peuvent pas être utilisées avec un classeur 2003 et vice versa, n'est-ce pas? Je devrais donc les recréer si je change d'environnement ou une solution existe t-elle?
Merci eriiic, pour ta soluce de plage variable il faudrait que je test à l'occasion.
Bon weekend à tous,
bonjour,
près de 300 valeurs différentes soit autant de requêtes
C'est pas un problème.
Est-ce qu'un classeur créé avec EXCEL 2010 et plus efficace qu'un classeur EXCEL 2003
Hum... Là je vais te faire une réponse de normand... En principe un très bon classeur 2003 doit rester bon sous 2010
2003 est limité à 60000 lignes et sature plus rapidement que 2010 qui encaisse 1 000 000 de lignes sans broncher.
Après le savoir faire peut également faire la différence.
Ou est-ce qu'un classeur créé avec EXCEL 2010 et ouvert avec 2003 peut être plus lent?
Surement : ça peut même ne pas marcher du tout : La compatibilité "remontante" n'est pas top.
En principe YAPA de problème avec les zones de liste (ni les BD***) et la compatibilité remontante.
Toutes ces fonctions sont des fonctions natives d'Excel : C'est ce qui fait leur puissance.
Le truc c'est de monter le classeur sous 2010 mais de l'enregister en mode de compatibilité 97-2003. (*.xls)
En principe même dans un environnement 64 bits Excel devrait être installé en 32 bits... (A vérifier dans le ruban Fichier > Aide > A propos...)
Pour le temps de réponse d'un ordi à l'autre ça peut varier aussi en fonction d'un tas d'autres critères. Quelle priorité pour les traitement Excel. Nombre de tâches qui fonctionnent en arrière plan... % d'occupation de la mémoire, du proc, du disque dur (fragmentation...)
A+
Galopin,
J'étais trop pressé de tester alors j'ai créé mes 220 requêtes. Le temps de MAJ de mes données ne s'est pas amélioré, en tous cas de manière significative ou de manière perceptible
Le "problème" est sans doute matériel.
Tu as raison OFFICE 2010 est installé en "32bits".
Je vais enregistrer le classeur avec compatibilité 2003, on verra bien.
affaire à suivre,
Bonsoir,
ça me parait juste pas possible :
Je serais curieux de tester ça en grandeur nature.
je te mets mon adresse mail en MP
Essaie de m'envoyer ça en pièce jointe : Si ta BDD fait plusieurs Méga il faudra surement la mettre chez un hosteur
par exemple cjoint et me donner le lien.
A+
Bonjour,
Merci eriiic, pour ta soluce de plage variable il faudrait que je test à l'occasion.
A l'occasion ???
A quoi ça sert de demander si tu ne testes pas les propositions ?
Je la retire donc bien que je pense qu'il devait y avoir un gain important.
eric
Bonjour Eric,
En fait j'ai redévelopper ma base de données access selon les directives de galopin. Je n'ai plus de Champ Clé pour faire mes rechercheV de façon aisé. Donc voilà pourquoi j'ai dis à l'occasion
Bonne journée
J'avais supprimé ce champ.
eric
Bonjour,
Hum j'ai testé ton fichier :
J'y ai quand même trouvé 2916 requêtes doublées (puisque tu les ramènes ensuite vers la feuille TDB) -et non pas 200 ! -
Il aurait été préférable de poser seulement les conditions (colonnes A à P) dans la feuille de requêtes et de poser directement les requêtes (colonne R à AC) dans la feuille TDB ce que j'ai fait. J'ai d'ailleurs supprimé des incohérences dans ton TDB. (la zone F37:Q270 est incrémentée par pas de 1 au lieu d'être par pas de 2...) C'est là qu'intervienne les macros... : j'ai juste déplacés tes requêtes vers le TDB et j'ai fait une macro de 5 lignes pour supprimer une ligne sur 2...)
La mise en jour s'effectue en 6 à 10 secondes sur mon petit portable si on fait une demande de recalcul totale (en modifiant les valeurs des critères E4, E6 ou E8)
Le recalcul est instantané en cas de mise à jour de la BD...
De mon point de vue (c'était quand même le but recherché si l'on considère le titre de ton fil, c'est quand même un résultat assez bon compte tenu que ta base de donnée est relativement modeste (je m'attendais à recevoir une BD de 500 000 lignes
Nota : Les BD ne sont pas du tout optimisées dans ton exemple puisque tu ne les as pas déclarée. Il aurait été préférable de les déclarer de manière dynamique (avec DECALER)
Je n'ai pas mesuré exactement l'incidence mais je ne doute pas qu'il y en une importante puisque le temps de recalcul semble retomber à 5 ou 6 secondes maxi pour un recalcul total...
Regarder la taille de mon fichier (en pièce jointe dans mon e-mail de retour) comparé au tien... ! (Nota : incompatible avec 2003, mebon...)
A+