Organiser données pour utilisation rapide par Excel

Bonjour,

J'ai beaucoup de données que je souhaite intégrer dans un fichier excel qui est déjà volumineux.

cf exemple ci-joint pour exemple des données. Le fichier ci-joint contient 10.000 références pour une situation type, mais j'ai environ 20 situations différentes à incorporer dont chacune comporte de 50.000 à 1.000.000 de références.

Ces données sont des valeurs de références qui seront utilisées dans des calculs déjà existants dans mon fichier excel.

Certains calculs nécessitent une ou plusieurs de ces valeurs.

Je souhaite optimiser le recours à ces données, afin de ralentir le moins possible les calculs en question, et le fonctionnement global de mon fichier.

Les colonnes B, C et D contiennent des fréquences (ex : 7=7%) qui peuvent éventuellement être arrondies pour regrouper des références si cela permet de gagner en performances (7->10%).

Pour appeler la bonne valeur référence, les solutions auxquelles j'ai pensé :

1. Recherchev( ; ; ;Vrai) : rechercher la valeur correspondante à la référence. Mais sur des centaines de milliers de lignes, j'ai peur que ce soit long, et je me dis qu'il y a peut-être + rapide.

2. Regrouper toutes les références dont l'arrondi de la fréquence 1 est 10 dans une cellule, puis toutes les références dont l'arrondi de la fréquence 1 est 20 dans une autre cellule, etc... en séparant chaque référence par un séparateur puis rechercher la présence de la référence avec Cherche() dans telle ou telle cellule, pour en déduire la fréquence 1. Problèmes : je ne suis pas sûr que ce soit très rapide et une seule cellule ne serait capable d'accueillir suffisamment de références pour chaque fréquences, donc il faudrait répartir sur plusieurs cellules pour une même fréquence... usine à gaz???

Avez-vous une autre idée?

Evidemment, la valeur appelée doit être celle qui correspond à la référence, et pas une autre, mais sinon, mon souci premier est vraiment la rapidité de process.

Merci,

Nicole

27data-exemple.zip (155.82 Ko)

Bonjour nicopat,

essayez le fichier joint

Bonjour,

une possibilité si vous avez Microsoft Access

est de mettre tous les info (Reference-fréquence 1-fréquence 2-fréquence 3) dans une table Access

et de faire une requête à partir l'excel.

njhub a écrit :

Bonjour nicopat,

essayez le fichier joint

Bonjour njhub,

Merci de ta proposition, c'est très intéressant !

Sais-tu si ces formules sont + rapides que du recherchev() classique?

PS : désolée pour le délai de réponse, mais les vacances sont passées par là...


sabV a écrit :

Bonjour,

une possibilité si vous avez Microsoft Access

est de mettre tous les info (Reference-fréquence 1-fréquence 2-fréquence 3) dans une table Access

et de faire une requête à partir l'excel.

Merci sabV,

Es-tu sur que c'est + rapide qu'un recherchev() dans le fichier?

Puis-je avoir plusieurs fichiers excel qui vont chercher dans la même base access en même temps sans (trop de) perte de vitesse?

Bonjour nicopat,

Désolé, je n'ai aucune idée du différentiel de vitesse d'exécution des différentes fonctions.

Une chose est sûre,

si votre colonne d'occurences à trouver est triée,

toutes les formules s'en porteront mieux,

il en ira de même pour la vitesse d'exécution

Avec la quantité de données que vous avez à traiter

vous serez bien mieux placée que moi donner un avis

sur le différentiel de vitesse d'exécution des différentes

fonctions, et vous pourrez nous informer des raisons

de votre choix, facilité d'utilisation, vitesse d'exécution,

maitrise de la syntaxe, adaptabilité...

Quand vous aurez choisi la fonction qui vous convient le mieux

sabV a écrit :

Bonjour,

une possibilité si vous avez Microsoft Access

est de mettre tous les info (Reference-fréquence 1-fréquence 2-fréquence 3) dans une table Access

et de faire une requête à partir l'excel.

sabV,

Si je veux faire l'équivalent du recherchev(), mais en allant chercher dans une DB access, comme tu le proposes, c'est quelle partie du tutoriel qui peut être utile?

La partie "Microsoft Query"?

Je ne veux pas appeler l'intégralité de ma table de données depuis access vers excel ; je voudrais seulement aller chercher dans la DB access la données correspondant à une référence.

J'imagine que c'est possible, mais je ne sais pas comment faire cela.

Et je ne sais pas non plus si c'est + rapide qu'un recherchev() et si cela supportera les requêtes simultanées.

Bonjour,

En pièce jointe LE système le plus rapide pour renvoyer un nombre considérable de données à partir de tables très volumineuses.

Adossé à un poil de VBA on fait des malheurs en comptabilité... analytique et autres

Ici on recherche des valeurs mais le système est tout aussi rapide pour rechercher des SOMME, MOYENNE, etc...

Ici je n'ai traité que 10 requètes mais avec l'habitude on peut en traiter 1000 et + sans problèmes

Seule la préparation peut prendre quelques minutes une fois les emplacements de requêtes préparées la capacité de traitement est quasi illimitée : le GRAAL des comptables surbookés !

Digère sans sourciller 1 000 000 de lignes...

A+

galopin01 a écrit :

Bonjour,

En pièce jointe LE système le plus rapide pour renvoyer un nombre considérable de données à partir de tables très volumineuses.

Adossé à un poil de VBA on fait des malheurs en comptabilité... analytique et autres

Ici on recherche des valeurs mais le système est tout aussi rapide pour rechercher des SOMME, MOYENNE, etc...

Ici je n'ai traité que 10 requètes mais avec l'habitude on peut en traiter 1000 et + sans problèmes

Seule la préparation peut prendre quelques minutes une fois les emplacements de requêtes préparées la capacité de traitement est quasi illimitée : le GRAAL des comptables surbookés !

Digère sans sourciller 1 000 000 de lignes...

A+

Bonsoir Galopin,

Merci de ta réponse!

Alors si je comprends bien, pour le cas de "AzAyBy9x9z" par exemple, la formule de recherche de la Fréquence 1 est :

=BDLIRE(DECALER('C1'!$A$1;;;NBVAL('C1'!$A:$A);4);B1;$A1:$D2)

Donc à l'instant t, je ne comprends rien à cette formule mais je vais apprendre !

Je ne connaissais pas la fonction BDLIRE().

Et j'ai oublié ce qu'était DECALER()...

Je n'aurais pas beaucoup de recherches à faire en simultané, (en général, je rechercherai juste les fréquences 1, 2, et 3 pour une seule référence donnée), mais par contre, ce sera dans de très grande tables (1.000.000 de lignes).

Donc, tu dis que c'est plus rapide que toutes les autres systèmes de recherche?

Les fonctions de base de données d'Excel (BD**) sont parmi les plus anciennes d'Excel. Enfouies au cœur du programme d'origine dans un langage très proche de la machine, elles sont pour cette raison extrêmement rapides. Malheureusement elles nécessitent un petit travail de préparation (les grilles de requêtes de la feuilles "Calc") mais une fois qu'on a bien compris leur fonctionnement il y a quelques petites astuces de mise en œuvre qui rendent très rapide leur mise en œuvre.

La plupart des utilisateurs les ignorent à cause de leur relative complexité d'utilisation, ce qui à conduit Microsoft à produire au fil des années, autour de ces fonctions de bases une surcouche logicielle de fonctions plus simples à utiliser. Mais comme à chaque fois qu'on rajoute une surcouche d'interface, ce qu'on gagne en ergonomie on le perd en temps de calcul. Toutes ces nouvelles fonctions ne sont plus écrites en langage machine, mais dans des langages évolués plus faciles à manier par les programmeurs qui en pondent régulièrement, Par suite au lieu d'être compilées au coeur du noyau d'Excel, toutes ces fonctions récentes sont juste intégrées dans de simples bibliothèques de fonctions additionnelles. Ce qui explique bien souvent des temps de calculs prohibitifs.

Pour ces raisons et quelques autres encore que je n'évoquerai pas ces fonctions natives sont très performantes, et satisfont les utilisateurs les plus exigeants... sans même nécessiter de calcul sur ordre !

J'ai ainsi produit pour des comptables très demandeurs de ressources extrêmes des grilles de plusieurs milliers de requêtes capable de ventiler de multiples bases de données énormes selon des lignes d'un plan comptable analytique nécessitant des regroupements et calculs complexes et ils ont tous constaté des gains de performances étonnants.

La grille de requêtes que je t'ai préparée est simplissime 4 colonnes sur 20 lignes ce qui est ridiculement petit par rapport aux calculs potentiels qu'on peut souhaiter. J'ai ainsi vu des grilles de requètes de plusieurs dizaines de colonnes sur des milliers de lignes traiter sans faiblir des requêtes très complexes.

Pour ces raisons je pense que cette proposition te conviendra parfaitement. Au besoin si tu as besoin d'une extension ou d'une organisation un peu différente, me consulter.

DECALER assure seulement que la définition de ta base de donnée intègre toutes les lignes.

La seule restriction que je fais concerne les difficultés que j'ai eu pour la mise au point de mes requêtes :

Ton fichier était très pollué par des références circulaires et certainement par des scories de construction que j'ai du éliminer pour que l'ensemble puisse fonctionner. Méfiance donc si tu as des erreurs du type #NOMBRE et que tes formules sont exactes, c'est certainement du à des problèmes d'intégrité de tes données, reliquats de résidus de mise en forme de cellules zombis...

Nécessité de travailler très proprement tant sur la BDD que sur la feuille de requêtes.

A+

Merci pour l'explication Galopin !

galopin01 a écrit :

Malheureusement elles nécessitent un petit travail de préparation (les grilles de requêtes de la feuilles "Calc") mais une fois qu'on a bien compris leur fonctionnement il y a quelques petites astuces de mise en œuvre qui rendent très rapide leur mise en œuvre.

De quel travail de préparation parles-tu? Juste les claculs intermédiaires du deuillet Calc! ?

Les données ne doivent pas être classées par ordre ou quoi que se coit de la sorte?

Par ailleurs, il y a quelque chose que je ne comprends pas : si je change les valeurs des cellules F3 à F12 (par exemple en remplaçant la référence initiale par une autre référence comme "AzAyAx8w8z"), ça ne marche lus : il apparaît #NOMBRE!

En fait j'ai compris ce qui ne marchait pas : Tes données comportent de nombreux doublons.

Pour faire un BDLIRE il faut qu'il n'y ait qu'une ligne qui réponde à la requête :

#NOMBRE indique la présence de plusieurs réponses possibles.

Sinon tu fais un BDSOMME de toutes les références identiques ou une BDNB pour savoir le nombre de références identiques...

Ci joint un autre classeur qui illustre une autre manière de monter tes requêtes...

Les résultats sont indépendants du tri.

A+

21nicopat.xlsx (275.20 Ko)

J'ai modifié mon message précédent et ajouter un exemple.

Comme u peux le constater les possibilités d'exploiter les Fonctions de base de données sont nombreuses et leur rapidité est sans commune mesure avec les autres fonctions "ordinaires. A plus fortes raison si on les compare à des SOMMEPROD ou autres fonctions matricielles ou non...

La capacité d'adapter les zones de critères est infinie... Ici on n'en utilise qu'un : "référence" mais on pourrait utiliser des zones de critères complexes incluant des ET... OU et d'autres critères de champs calculés... ou des requêtes complexes comme dans le fichier joint dans mon message précédent.

Ces fonctions sont vraiment mes chouchoutes : couplées astucieusement à un peu de VBA, tu fais un tabac !

A+

Merci Galopin,

Je pense qu'ajouter les données que je souhaite utiliser (plusieurs dizaines de millions de lignes sur 6 colonnes environ) dans mon fichier excel augmenterait son poids d'environ 1 GO !!!

Donc je ne suis pas certaine que ce soit une bonne idée dans ce cas de les stocker en interne. Surtout que j'utilise 6 versions de mon fichier en parallèle. Donc peut-être vaut-il mieux les stocker dans une DB access par exemple et que mes 6 fichiers fassent des requêtes vers cette DB access, non?

Si tu as un avis à ce sujet, la discussion est ici : https://forum.excel-pratique.com/excel/gros-volume-de-donnees-t97163.html

Bonjour,

Je ne suis pas du tout expert dans l'utilisation de données Access, mais au delà d'un million de lignes l'utilisation d'Excel n'est pas conseillée...

Après tout dépend ce que tu veux extirper de tes tables. Si je puise un peu dans mes vieux souvenirs, tu as la possibilité d'extirper des données d'ACCESS vers Excel à partir de requêtes SQL, mais je n'ai plus fait ça depuis plus de 10 ans...

Tu peux également créer tes requêtes dans ACCESS et les exploiter directement dans EXCEL (Avec Données depuis ACCESS...)

On peut cependant supposer que c'est toujours possible. Il est même probable que les techniques ayant évolué il existe maintenant d'autre possibilités.

Les petits jeunes vont surement nous ouvrir des horizons insoupçonnés sur ce sujet...

A+

Bonjour nicopat, galopin01, le forum,

La plus grande difficulté est de faire de l'ordre dans les données avant de copier ces données dans une table Access,

ensuite c'est vraiment facile de faire les requêtes directement à partir d'Excel.

Sur Access 2010 les clés d'enregistrement se fait tous seul, pas besoin d'en créé une avant de coller les données,

cela facilite beaucoup la tache.

Vous pouvez faire les requêtes sur Excel à partir d'une macro ou directement avec au menu, Données, Connexions.

Voici un exemple, (désolé trop volumineux pour ici)

https://www.cjoint.com/c/GHlbswfshmi

https://www.cjoint.com/c/GHlbtzMTcKi

la connexion sera à refaire vu que le répertoire sera différent.

Rechercher des sujets similaires à "organiser donnees utilisation rapide"