Transformer formule matricielle en VBA

Bonjour à tous,

J'ai la formule matricielle suivante :

Code:

---

=INDEX(LIBS;MIN(SI((MATS=B2)*(DEBS<=A2)*(FINS>=A2);LIGNE(MATS))))

---

Que je dois appliquer sur plus de 500 000 lignes. Autant dire qu'en terme de performance ça n'y est pas du tout, et que Excel a du mal à aller au bout du calcul.

Je ne m'y connais pas beaucoup en VBA, mais je sais que pour ce genre de calcul ça pourrait améliorer la vitesse du calcul, d'où ma question : Est-il possible de transformer cette formule en VBA, et si oui de quelle manière ? Un simple FORMULA peut suffire ou bien il faut carrément adapter le code ?

Bien à vous,

Sardaucar

Bonjour,

la propriété formula permet de manipuler ... une formule

la propriété formulaArray devrait t'intéresser, pour des formules matricielles

n'hésite pas à regarder l'aide en ligne :f1:


je suis sur un fichier où justement j'écris des formules matricielles avec INDEX réalisé sur un tableau structuré (ListObject dont le nom est contenant dans la variable RefTableau), si ça peut te donner un objectif à atteindre et comprendre ?

Code:

---

Cell.FormulaArray = "=INDEX(" & RefTableau & "[ID],MATCH(CONCATENATE(RC[13],RC[14],RC[11]),CONCATENATE(" & RefTableau & "[MEDIA]," & RefTableau & "[MASTER_ITEM]," & RefTableau & "[ITEM]),0))"

---

La formule est en référence relative R1C1
Attention : pas plus de 255 caractères ... sinon il faut ruser et découper sa formule en plusieurs portions pour ensuite la reconstituer

Donc, avec tes explications, j'ai tenté la sub suivante :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Private Sub test()   Dim i As Integer   For i = 2 To 10     Cells(i, 7).FormulaArray = "=INDEX(" & LIBS & ";MIN(IF((" & MATS & "=B2)*(" & DEBS & "<=A2)*(" & FINS & ">=A2);ROW(" & MATS & "))))"   Next   End Sub

---

Mais j'ai une erreur sur la classe range de la propriété formula Array :/.

MATS, LIBS et compagnie, sont des "mots" écrit dans ta formule (des plages nommées ?), et pas des variables
elles doivent donc être incluses à l'intérieur des guillemets

là, VBA te dis en gros "moi je connais pas les plages qui s'appelles MATS, LIBS etc..."

de plus, comme on le voit dans mon exemple, il faut écrire "à l'anglaise" :

- le nom des formules
- les séparateurs (en anglais, c'est la virugle qui sépare, et pas le point virgule)

donc, grosso modo, tu t'es compliqué la vie pour rien :aie:
à main levé, ça devrait ressembler à ça

Code:

---

Cells(i, 7).FormulaArray = "=INDEX(LIBS,MIN(IF((MATS=B2)*(DEBS<=A2)*(FINS>=A2),ROW(MATS))))"

---

Bonjour,

Ecrire la formule matricielle avec du VBA ne va pas améliorer la vitesse d'exécution.

Boisgontier

Ha bon ? Quelle serait la méthode alors ?

PS: Effectivement la correction fonctionne mais pas vraiment de gain de performance, j'ai bien conscience que le fichier est mal fichu, mais malheureusement je n'ai pas la main sur la manière dont m'arrivent les données, je dois donc faire avec :aie:

C'est clair que ça ne changera pas grand chose ! A la rigueur, on peut injecter toutes les formules et les évaluer en une fois plutôt que de faire de l'évaluation à chaque ajout d'une formule.

500 000 formules matricielles, c'est de toute façon constater que la conception du fichier est à revoir, mais ce n'était pas la question de cette discussion

Bonjour,

Il y en a qui vont dire que le vieux radote, mais, Excel a constamment besoin de tous ses classeurs ouverts en mémoire vive en tout temps. Et les formules matricielles sont ce qui bouffe le plus de mémoire. Et Excel se bat contre un paquet de services et autres conneries qui se prétendent tous indispensables, comme la recherche quotidienne de mises à jour pour des programmes qui sont mis à jour une fois par année.

Et puis Excel n'est pas Access junior. Tant que ton employeur sera trop stupide pour s'équiper comme du monde, laisse courir. Quand le classeur va être corrompu après un plantage magistral, il y a peut-être quelqu'un qui va penser avec sa tête au Service de Dictature informatique.

Donc tu préconise de passer par Access pour traiter ces données ? Avec deux tables distinctes et une requête de jointure ?

Je ne suis pas devin. Mais je reste convaincu qu'Excel est probablement la pire solution. À moins que de refaire le fichier Excel autrement corrige le problème.

Mais, mon opinion personnelle, c'est que c'est un problème de mémoire et tant que tu auras à zigonner avec 600 000 lignes, tu seras dans le trouble.

Access a au moins l'avantage de pouvoir travailler avec seulement des portions de fichiers en mémoire, au lieu de fichiers complets.

Après avoir écouté le vieux sage, j'ai importé mes feuilles dans Access, et un traitement par requête en jointure gauche + critère m'a permis de faire ce que je voulais, un grand merci !