Gros Fichiers... Questions de Méthode???

[La braise]

Bonjour à tous,

Ma question :
Une fois par semaine, je réalise le traitement suivant :
j'ai un fichier qui fait 50.000 lignes (ventes par références) que je croise avec un autre fichier qui fait aussi 50.000 lignes (infos sur chacune des refs) et ce sur 15/20 colonnes. (et oui, je fais chauffer Excel! )

A l'heure actuelle j'utilise VLookup dans excel ce qui est très long... (je vous laisse imaginer et même avec un core 2 duo et 2go de RAM... ça rame toujours).

De plus, pour améliorer le calul je redéfini à la main le champ exact de la zone du fichier de mapping. (qui évidevement varie en permance...)

En gros, j'ai mes formules dans chacune des colonnes de la première ligne, je recopie vers le bas pour chacune des colonnes, une par une! (je sais, je sais...).
Le but (car il y en a un) est de ne pas saturer l'ordi par un calcul énorme et pouvoir continuer à bosser en parallèle. Du coup, ça prend 5 que minutes par colonne...

Je suis en train de construire une macro qui fait tout ça.
J'ai pris l'approche identique au traitement Excel :
- identification du nb de lignes des 2 fichiers
- Traitement colonne par colonne : calcul cellule première ligne et recopie vers le bas.

Ma question (enfin...) :
Est-ce que cette approche est la meilleure?
Meilleure en temps de traitement essentiellement, j'entends.

Après la lecture attentive d'un manuel de VBA, je me pose plusieurs questions :
- un calcul colonne par colonne est-il plus efficace qu'un commande globale sur la plage entière (recherchev sur 50000 lignes et 20 colonnes pour rappel!)?
- l'AutoFill est-il plus efficace qu'une boucle?
- mon approche est-elle la plus judicieuse?

Je compte sur votre expérience sur la question pour pouvoir m'indiquer la meilleure marche à suivre

D'avance merci pour toutes vos réponses.



La Braise

 

[Macpoy]

Re : Gros Fichiers... Questions de Méthode???

bonsoir le forum,
bonsoir La braise,
d'entrée je dirais qu'une migration vers un logiciel de base de données s'impose.
genre access......
car Excel et les machines qui le supportent sont très performants, mais jusqu'à un certain point.
entre guillemets faut être raisonnable avec les requètes que l'ont peut exiger d'Excel.
désolée de n'avoir d'autres réponses à vous fournir, mais j'ai bien peur que dépassiez les capacités de notre bon vieux tableur !!!!

 

[MJ13]

Re : Gros Fichiers... Questions de Méthode???

Bonjour Labraise,Macpoy

LA braise: Il est vrai que pour des gros fichiers, il faut peut-être mieux utiliser un SGBD (Ce qui n'est pas trop ma spécialité).

Par contre tu peux avec Excel faire des requêtes sur de bon nombres de types de BD, ce qui permet en général d'aller plus vite.

Mais bon, on ne voit pas très bien comment tu travailles.
Il faudrait peut être nous en dire plus (d'où viennent les données, 50 000 lignes est ce figé ou est ce que cela peut augmenter, quelles types de requêtes fais tu....?).

Tu peux même joindre un fichier sans données confidentielles juste avec quelques lignes zippé de moins de 48,7 ko.

 

[GIBI]

Re : Gros Fichiers... Questions de Méthode???

Bonsoir,

si tu as un calcul en automatique ou en manuel (F9) le calcul se refait systématiquement pour le classeur entier (sauf si à chaque colonne tu fais un copier/coller-valeur). Par contre via une macro tu peux lancer le calcul sur pour une plage de cellule (Selection.Calculate) et morceler le travail.

Si nous en savons plus, nous pourrons ,peut-être optimishttp://www.excel-downloads.com/forum/images/icons/icon12.gifer le processus. Par exemple si ton "Recherchev" (in french) ne te sert pas ramener une valeur mieux vaut EQUIV qui est plus rapide

mieux que autofill tu peux faire plage( ).formulalocal="=RECHERCHEV(.........)" c'est immédiat mais la boucle faut oublier.
Sinon, si tu reproduit en VB ce que tu fais à la main cela fera gagner du temps de manipulation mais peu sur le temps de calcul.==> l'avantage c'est que tu lance le calcul avant d'aller manger ou le soir avant de partir (prévoies l'enregistrement en fin de macro).

alors, vite un petit exemple

GIBI


Pour Chris (voir ci-aprés) : au dessus de 65536 on passe à Office 2007

 

[chris]

Re : Gros Fichiers... Questions de Méthode???

Bonjour

Comme macpoy je conseille de changer de soft.

Si access est trop cher, il y a mysql et sql server express gratuits.

Les SGBD sont justement faits pour croiser des données : le recherchev se fait sans qu'on s'en aperçoive et ne rame pas sur 50000 enregistrements.

On peut facilement établir des passerelles entre Excel et Access mais aussi avec d'autres SGBD.

En plus 50000 n'est pas très loin de la limite des 65000 : que feras-tu quand tu l'atteindras ?

 

[La braise]

Re : Gros Fichiers... Questions de Méthode???

Merci pour vos réponses/conseils.

Je suis bien conscient qu'un SGBD serait la solution... mais faire tourner Excel pendant 15 minutes (mon souhait à terme) n'est pas un problème.

Pour expliquer le pourquoi du comment...
Mon fichier est un export de SAP. Notre hiérarchie produits/clienst d'analyse n'est pas la même que dans SAP, ce qui explique ces mappings hebdo.
La taille de de mon export évolue tout le temps, et les fichiers de mapping également.

D'où mon souhait d'avoir une formule limitée à l'essentiel (uniquement à la zone utile des différents fichiers).

GIBI,
Effecticvement dans mon traitement colonne à colonne, je fais un collage spécial après chaque calcul. Faut-il néanmoins désactiver le calcul automatique (F9)?
Ma recherchev me ramène du texte essentiellement.
Tu penses que je gagnerais en temps de traitement en utilisant la fonction EQUIV.?

Chris,
au dessus de 65536 j'ai déjà Office 2007 pour parer à cette éventualité.

Je vais vous joindre la macro que j'ai réalisé et que j'utilise.

Si vous voyerz des abbérations, n'hésitez à m'en faire part

Merci à tous.

La braise

 

[THE CAT 2007]

Re : Gros Fichiers... Questions de Méthode???

Bj à tous et toutes,

dans la macro :

tableau1=range(laPlage1)

tableau2=range(laPlage2)

voici 2 très beaux tableaux à parcourir dans tous les sens, suivant les besoins
avec l'avantage de la vitesse inhérente aux tableaux et aux boucles for next.

il y a aussi find qui est pas mal.

il y a aussi :
faire un classeur indépendant avec le code, ne pas ouvrir les 2 fichiers de données, utiliser getrows pour chaque fichier,
placer chaque getrows dans un tableau dans le classeur code et traiter ...

 

[BOISGONTIER]

Re : Gros Fichiers... Questions de Méthode???

Bonsoir,

-Si la valeur cherchée est un code et si la table est TRIEE, on peut spécifier le paramètre VRAI.
-La recherche est alors faite par DICHOTOMIE et peut être x100 + RAPIDE puisqu'il suffit de quelques accés pour retrouver le code.
-C'est TRES IMPORTANT lorsque la table est de taille importante et que la formule Recherchev() est recopiée x1000 fois (Avec FAUX , Excel consulte la table SEQUENTIELLEMENT).

JB
Formation Excel VBA JB

 

[jmd2]

Re : Gros Fichiers... Questions de Méthode???

hello à tous

SAP est déjà un gros (très gros ) SGBD
demande à ton prestataire, il sait faire ce dont tu rêves, en restant sous SAP (n'oublie pas de demander un devis avant)

ou alors, avec Windev (prix double d'Access) qui possède un accès natif aux bases SAP.
pas trop difficile d'apprentissage.

rem : suis toujours les conseils de Boisgontier, et va voir son site de temps en temps.

*****

 

[La braise]

Re : Gros Fichiers... Questions de Méthode???

Bonsoir à tous,

Merci pour toutes ces pistes que je sais suivre.
Je vous tiens au courant rapidement.