Macro qui remplace =si(esterreur(recherchev(

[fbussi]

Bonjour à tous et à toutes,
Je suis un nouveau membre, j'ai fait pas mal de recherche sur le forum et n'arrive pas à trouver une solution à mon problème.
Je travail sur de gros fichier excel qui comporte les tarifs de différent fournisseur. La société où je travail gère 100000 références et mes fournisseurs gèrent chacun plus de 800000 références.
J'utilise actuellement la formule excel =si(esterreur(recherchev(... et ça prend un temps fou (plusieurs heures) à me donner les tarifs. Je pense qu'une macro m'aiderai à traiter la recherche plus rapidement mais je n'y connait pas grand chose dans ce language.
Je vous ai joint un fichier d'exemple, la Feuil1 correspondrait au tarif de mon fournisseur et la Feuil2 a ma base de donnée, ma formule excel se trouve en Feuil2 cellule B2.
Je vous remercie d'avance de votre aide à tous et à toutes.
Fabien

 

[JBARBE]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonsoir à tous,

Je ne pense pas qu'une macro puisse améliorer la rapidité de la recherche et cela compte tenu du nombre important de lignes à traiter !

A moins d'avoir une superbe bécane performante !

bonne soirée !

 

[fbussi]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonjour,
Merci de votre réponse, j'ai 4 coeurs intel xeon 3.5 ghz.
On m'a dit que c'était pas trop mal pour ce que je fais.
Auriez-vous une autre solution ou le problème vient uniquement du nombre à traiter?
Bonne Soirée
Fabien

 

[Modeste geedee]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonsour®
Excel est d'abord un tableur (feuille de calculs, chiffrier ...)
il peut servir de base de données pour des "tables" simples :
- sans relation au sens "Base de données Relationnelles"
- sans contrôle d'intégrité
- sans sécurité relationnelle

si l'on veut l'un ou l'autre de ces cas il faut passer par un gestionnaire de base de données (SGBD et SGBDR)
type ACCESS ou équivalent.

on pourra également alors depuis EXCEL interroger ces bases de données via SQL QUERY

 

[fbussi]

Re : Macro qui remplace =si(esterreur(recherchev(

Et là tout se complique pour moi, car je ne sais encore faire des recherches d'un logiciel à l'autre, je peux créer met tarif fournisseur dans access, ça je sais faire mais le reste je ne connais pas. Vous pensez vraiment que l'adaptation de ma formule en VBA ne reglera pas mon problème ou tout au moins le diminuer ?

 

[JBARBE]

Re : Macro qui remplace =si(esterreur(recherchev(

Et là tout se complique pour moi, car je ne sais encore faire des recherches d'un logiciel à l'autre, je peux créer met tarif fournisseur dans access, ça je sais faire mais le reste je ne connais pas. Vous pensez vraiment que l'adaptation de ma formule en VBA ne reglera pas mon problème ou tout au moins le diminuer ?

Compte tenu des réponses de Modeste geedee et de moi-même, la réponse est évidente car la macro en VBA ne va certainement pas amélioré la vitesse d'exécution !

bonne soirée

 

[chris]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonjour à tous

Je partage totalement l'avis de Jbarbe et Modeste Geedee que je salue .

Mais déjà ta formule peut à mon avis être allégée

=SI(ESTERREUR(RECHERCHEV($A2;Feuil1!$A:$B;2;FAUX));AZ800;(RECHERCHEV($A2;Feuil1!$A:$B;2;FAUX)))

Qu'est sensée contenir AZ800 ?

Depuis 2007 on dispose de SIERREUR, ce qui donnerait :

=SIERREUR(RECHERCHEV($A2;Feuil1!$A:$B;2;FAUX);AZ800)

On dispose aussi de la possibilité de déclarer les listes en tableaux : je n'ai pas testé les gains ou non en vitesse de calcul mais cela éviterait de travailler sur des colonnes complètes.

Je testerais déjà la solution suivante :

• lier chacune des listes comme table liée dans Access
• créer une requête mettant en relation les 2 tables
• utiliser la requête dans Excel

Éventuellement le même test en important les listes comme tables Access et mesurer l'écart de temps avec le 1er test...

 

[fbussi]

Re : Macro qui remplace =si(esterreur(recherchev(

Merci à vous trois pour vos réponse,
Chris, je vais essayer la formule que tu m'as donner afin de voir si je gagne déjà du temps de calcul, et ensuite je testerai la deuxième solution qui plus compliqué pour moi car access, je ne connais pas trop.
Encore merci beaucoup de votre aide.

 

[fbussi]

Re : Macro qui remplace =si(esterreur(recherchev(

Chris, je n'avais pas vu ta question. Je ne sais pas trop, j'ai récupérer cette formule sur un fichier fournisseur.

 

[fbussi]

Re : Macro qui remplace =si(esterreur(recherchev(

Je viens de tester la formule ça me met un message d'erreur comme quoi la formule n'est pas correct

 

[chris]

Re : Macro qui remplace =si(esterreur(recherchev(

Re

Sorry, erreur de copie . J'ai rectifié mon post #7 avec SIERREUR

 

[Paf]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonsoir à tous

Si j'ai tout bien compris,un essai de code avec tableau et dictionary.

on met les référence fournisseurs dans un dictionary, les références de la feuille qui sert de base de données dans un tableau.

On met ce tableau à jour à l'aide du dictionary, puis on colle ce tableau à jour à l'emplacement de l'ancien.

 Sub fbussi()
Dim WSFour As Worksheet, WSBD As Worksheet
Dim DicoF, TabloBD, TabloFour
Dim DerligF as long, DerLigBD as long
Dim i as long, Start

Start = Timer
Set DicoF = CreateObject("Scripting.Dictionary")

Set WSFour = Worksheets("Feuil1")
Set WSBD = Worksheets("Feuil2")

DerligF = WSFour.Range("A" & Rows.Count).End(xlUp).Row
DerLigBD = WSBD.Range("A" & Rows.Count).End(xlUp).Row

'creation dico fournisseur
TabloFour = WSFour.Range("A2:B" & DerligF)
  For i = LBound(TabloFour) To UBound(TabloFour)
    DicoF(TabloFour(i, 1)) = TabloFour(i, 2)
  Next i

'creation tableau BD
TabloBD = WSBD.Range("A2:B" & DerLigBD)

'Mise à jour du tableau BD
For i = LBound(TabloBD) To UBound(TabloBD)
    TabloBD(i, 2) = DicoF(TabloBD(i, 1))
Next


' "collage" du nouveau tableau dans la feuille
WSBD.Range("A2").Resize(UBound(TabloBD, 1), UBound(TabloBD, 2)) = TabloBD

MsgBox Timer - Start
    
End Sub

testé sur 1 feuille fournisseur 60 000 lignes une feuille Base de donnée 60 000 lignes : environ 10 secondes

à voir avec des volumes plus importants

A+

 

[Dranreb]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonsoir.
Sais pas, mais ça vaudrait peut être quand même le coup de vérifier si, avec de tels volumes, ça ne gagnerait pas des secondes à chercher les liaisons anticipées, même si moi je ne les prends toujours que par pur principe car je trouve les liaisons tardives complètement idiotes…

 

[Paf]

Re : Macro qui remplace =si(esterreur(recherchev(

Re bonjour à tous

J'ai oublié de préciser pour l'emploi du code proposé, il est nécessaire d'activer la référence Microsoft Scripting Runtime sous l'éditeur VB.

@ Dranreb, après essais de diverses déclarations et augmentation des lignes à 65 000, pas vu de différences notables.


Bonne journée

 

[Staple1600]

Re : Macro qui remplace =si(esterreur(recherchev(

Bonjour à tous

Paf
Tel que ton code est écrit (en late binding), il n'est pas obligatoire que la référence soit cochée
Le code fonctionnera sans la référence cochée.

PS:Mais on peut la cocher pour avoir le bénéfice de l'autocomplétion du code VBA quand on utilise le Dictionary dans VBE.