XL 2019 formule index equiv en VBA

Loïc DUBOIS · 22 Septembre 2023

Bonjour à tous,

J'espère que vous allez bien ?

Pour retrouver des données dans un tableau j'utilise la formule index equiv : =INDEX(Feuil5!$C$2:$BBA$20000;EQUIV(Feuil1!$D223232&Feuil1!AF$1;Feuil5!$A$2:$A$20000&Feuil5!$B$2:$B$20000;0);EQUIV(Feuil1!$Z223232*1;Feuil5!$C$1:$BBA$1;0))

Comme vous pouvez le voir il y a deux critères de colonne et un critère de ligne. Cette formule fonctionne très bien. Le problème étant que j'ai de gros fichiers de données actuellement (600k lignes) et le traitement est très long. En sachant que j'ai environ 32 tableaux de résultats le traitement prend un temps infini.

Dernière précision la colonne A représente le groupe (en gros je vais faire la requête groupe par groupe (ils sont possiblement dans le désordre) donc j'aimerais aussi qu'on prenne en compte cela).

En gros, on applique le code pour le groupe 2 (pour les autres groupes je veux laisser vide (en effet, je vais appliquer le code dans un second temps (donc il ne faut pas que le code change les groupes déjà fait).

Avez-vous une solution pour le faire en code VBA svp ?

Je vous joins un fichier test

Merci d'avance,

sylvanu · 22 Septembre 2023

Bonjour Loïc,
Si les calculs sont tout fait en VBA cela d'être plug lent qu'avec XL.
Par contre les formules sont recalculées à chaque nouveau calcul, d'où la lenteur.
Peut être un compromis :
1- En VBA, on met les formules dans la plage désirée
2- On copie colle les valeurs
Je pense que ce sera l'optimum en terme de temps.
On profite ainsi de la vitesse de calcul d'XL, mais il n'y a aucun recalcul posterieur.

Un ex en PJ pour la plage Feuil1 en rose :

VB:

Sub FormuleFeuil1()
    ' Formule peut être acquise directement par l'enregistreur de macro
    Formule = "=IFERROR(INDEX(Feuil5!R2C3:R31C449,MATCH(Feuil1!RC4&Feuil1!R1C,Feuil5!R2C1:R31C1&Feuil5!R2C2:R31C2,0),MATCH(Feuil1!RC26*1,Feuil5!R1C3:R1C449,0)),"""")"
    Sheets("Feuil1").[AF2].FormulaArray = Formule   ' On colle la formule
    Range("AF2").Copy                               ' On duplique cette formule sur la plage
    Range("AF2").AutoFill Destination:=Range("AF2:AF23"), Type:=xlFillDefault
    Range("AF2:AF23").AutoFill Destination:=Range("AF2:AL23"), Type:=xlFillDefault
    Range("AF2:AL23") = Range("AF2:AL23").Value     ' On colle les valeurs
    Range("AF2").Select
    Application.CutCopyMode = False
End Sub

C'est brut de fonderie, sans optimisation. Juste pour valider le principe.

Loïc DUBOIS · 22 Septembre 2023

Merci beaucoup pour ta proposition.

Sur le fichier test cela foncitonne très bien.
En revanche sur mon fichier, j'aimerais savoir ce qu'il faut que je modifie pour la taille des tableaux. En effet, ma feuil1 contient + de 250k lignes.

De plus sur la formule :

=IFERROR(INDEX(Feuil5!R2C3:R31C449,MATCH(Feuil1!RC4&Feuil1!R1C,Feuil5!R2C1:R31C1&Feuil5!R2C2:R31C2,0),MATCH(Feuil1!RC26*1,Feuil5!R1C3:R1C449,0)),"""")"

A quoi correspond les R2C3, R31C449 etc ? j'avoue ne pas comprendre la notation.

Merci d'avance,

Loïc

Loïc DUBOIS · 22 Septembre 2023

Un autre problème que je viens de voir. En faisant cette méthode, le problème est que j'ai 8 tableaux différents donc je vais faire le groupe 1 (cela va me faire 30k lignes sur les 250k) puis le groupe 2 etc.. mais en collant la formule cela va me remplacer à chaque fois le groupe précédent. Donc les valeurs retournées sur le groupe 1 par exemple vont etre écrasées. J'aimerais qu'on puisse appliquer la formule uniquement pour un groupe défini (voir colone A).

Vois-tu ce que je veux dire ?

Merci d'avance,

Loïc

sylvanu · 22 Septembre 2023

Re,
1- Pour localiser une cellule XL permet de le faire soit avec une notation A2 ou une notation L2C1.
(Ligne 2 Colonne 1)
Mais il vous suffit de mettre la bonne formule dans une cellule, d'enregistrer une macro, de vous repositionner dans la cellule puis de faire Entrée. Vous arrêtez l'enregistreur et dans la macro vous avez la formule. ( il vous faut supprimer les { et } de cette formule.

2- Il vous faut écrire une macro pour chaque type de formule avec la bonne plage sur laquelle elle doit s'appliquer, exactement comme vous le feriez à la main, puisque le VBA ne va faire que coller les formules et faire un copier coller valeurs.

Loïc DUBOIS · 22 Septembre 2023

Re,

Merci encore pour ta réponse,

JE viens d'essayer sur mon gros fichier :

Sub FormuleFeuil1()
' Formule peut être acquise directement par l'enregistreur de macro
Formule = "=IFERROR(INDEX(Feuil5!R2C3:R20000C449,MATCH(Feuil1!RC5&Feuil1!R1C,Feuil5!R2C1:R20000C1&Feuil5!R2C2:R20000C2,0),MATCH(Feuil1!RC25*1,Feuil5!R1C3:R1C449,0)),"""")"
Sheets("Feuil1").[AD2].FormulaArray = Formule ' On colle la formule
Range("AD2").Copy ' On duplique cette formule sur la plage
Range("AD2").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault
Range("AD2:AH31200").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault
Range("AD2:AH31200") = Range("AD2:AH31200").Value ' On colle les valeurs
Range("AD2").Select
Application.CutCopyMode = False
End Sub

Mon problème est que j'ai un message d'erreur :

Pourtant je n'ai juste modifié que la formule pour qu'elle soit adapté à mon fichier et les ranges...

As-tu une idée ?

Merci

Loïc DUBOIS · 22 Septembre 2023

Désolé pour les mulitples postes mais je n'ai pu cette erreur.

J'en ai une autre, le fait de copier coller en valeur de fonctionne pas.
La macro n'exécute que cela :
Sub FormuleFeuil1()
' Formule peut être acquise directement par l'enregistreur de macro
Formule = "=IFERROR(INDEX(Feuil5!R2C3:R20000C449,MATCH(Feuil1!RC5&Feuil1!R1C,Feuil5!R2C1:R20000C1&Feuil5!R2C2:R20000C2,0),MATCH(Feuil1!RC25*1,Feuil5!R1C3:R1C449,0)),"""")"
Sheets("Feuil1").[AD2].FormulaArray = Formule ' On colle la formule
Range("AD2").Copy ' On duplique cette formule sur la plage
Range("AD2").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault

De plus le script ne colle pas la formule sur la colonne AD...

sylvanu · 22 Septembre 2023

Avec FormulaArray, on ne peut pas dupliquer en même temps sur lignes et colonnes.
On duplique déjà sur les lignes puis la colonnes sur les autres.
Regardez bien sur mon ex. Dupliquez un FormulaArraysur une matrice ne marche pas.
Utilisez plutôt :

VB:

Sub FormuleFeuil1()
' Formule peut être acquise directement par l'enregistreur de macro
Formule = "=IFERROR(INDEX(Feuil5!R2C3:R20000C449,MATCH(Feuil1!RC5&Feuil1!R1C,Feuil5!R2C1:R20000C1&Feuil5!R2C2:R20000C2,0),MATCH(Feuil1!RC25*1,Feuil5!R1C3:R1C449,0)),"""")"
Sheets("Feuil1").[AD2].FormulaArray = Formule ' On colle la formule
Range("AD2").Copy ' On duplique cette formule sur la plage
' On duplique cette formule sur la colonne AD
Range("AD2").AutoFill Destination:=Range("AD2:AD31200"), Type:=xlFillDefault
' On duplique cette colonne sur l'ensemble des colonnes
Range("AD2:AD31200").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault
Range("AD2:AH31200") = Range("AD2:AH31200").Value ' On colle les valeurs
Range("AD2").Select
Application.CutCopyMode = False
End Sub

Loïc DUBOIS · 22 Septembre 2023

Super merci beaucoup Sylvanu, ca fonctionne !

sylvanu · 22 Septembre 2023

Mais ça rame !

Loïc DUBOIS · 22 Septembre 2023

Un peu oui, mais ca prend déjà beaucoup moins de temps que sans passer par ce script.

Donc merci beaucoup

XL 2019 formule index equiv en VBA

Loïc DUBOIS

XLDnaute Occasionnel

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

Loïc DUBOIS

XLDnaute Occasionnel

Loïc DUBOIS

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Loïc DUBOIS

XLDnaute Occasionnel

Loïc DUBOIS

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Loïc DUBOIS

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Loïc DUBOIS

XLDnaute Occasionnel

Discussions similaires