XL 2016 Formules matricielles max si deux conditions en VBA

[safranien]

Bonjour et meilleurs à toutes et tous
je vous sollicite car je n'arrive pas à retranscrire des formules matricielles en VBA. Je dispose d'un fichier de travail assez lourd et plein de calculs que j'ai réussi à optimiser relativement bien via VBA (ce fichier est en mode de calcul manuel). En PJ, un extrait de ce fichier pour la partie que je n'arrive pas à traiter. Dans les colonnes T à X, les formules matricielles max si à 2 conditions que j'utilise. Rien que pour cette partie, le temps de calcul est très long et vient donc allonger les temps de traitement de mon fichier de travail. J'espérais réussir à transposer ces formules en VBA pour que ce soit plus rapide, j'ai bien réussi à écrire qqch mais le temps de calcul est encore plus long.
Le rendu de la macro se fait dans le tableau dans les colonnes L à P.
Pourriez-vous m'aider pour que ces calculs puissent se faire beaucoup plus rapidement via VBA ?
En vous remerciant et bon week-end.

 

[mapomme]

Bonjour safranien, job75,

J'avais commencé à coder avec des dictionnaires. @job75 ayant été ultra rapide (tant en temps de réponse qu'en temps d’exécution ), j'ai complété par une méthode sans dictionary (un peu plus longue à s'exécuter).

Mais dans les deux méthodes, je construis une colonne de dates sans doublon.

Suite à votre demande supplémentaire, je ne vois pas, moi non plus, comment se passer de la colonne date ???
A moins que vous ne disposiez quelque part dans votre classeur d'une colonne avec les dates sans doublon déjà établies et qu'on pourrait lire et utiliser ?

Dans votre image, on voit des colonnes avec des dates avec doublon et plus à droite des colonnes de puissances max. Or pour des dates identiques, les valeurs des puissances max sont différentes. Quelques chose m'échappe sans doute comme à @job75.

nota: tout comme @job75, les tableaux résultats sont identiques à celui de votre fichier du message #1.

Oups! J'ai trop nettoyé pour fichier pour le téléverser . Je vais remettre les bonnes données.

 

[safranien]

Le tableau que j'ai créé correspond EXACTEMENT au tableau de vos formules matricielles du post #1.

Je n'ai ajouté aucune colonne.

prenez la peine de regarder

dans mon 1er post, le fichier exposant l'attendu final :

_ colonnes B et C : origine des données, à ne pas toucher
_ colonne J : à ne pas toucher. Sert aux calculs des formules matricielles.
_ colonnes L à P : tableau de valeur dans lequel le VBA doit inscrire les valeurs selon les calculs des formules matricielles indiquées dans le tableau exemple présent dans les colonnes T à X, formules qui utilisent la colonne J et la colonne S
_ colonne S : à ne pas toucher : colonne de dates (365 jours) crée en fonction des dates de la colonne C que je créée dans mon fichier de travail mais qui n'a pas besoin d'être touchée ici. Sert aux calculs des formules matricielles.
_ colonnes T à X : tableau de résultat exemple contenant les formules matricielles et les valeurs devant être obtenues grâce au VBA pour remplacer les formules

Dans votre rendu, le tableau de valeur est en colonnes M à Q car en colonne L vous avez ajouté une colonne date ! Elle ne sert à rien puisqu'il y a déjà les dates en colonnes S qui se retrouve en colonne T dans votre proposition. Donc ça me décale tout par rapport à la structure de mon fichier de travail.
Donc toujours selon le fichier du post 1, je cherche juste à ce que les résultats que l'on voit dans le tableau de résultat exemple avec les formules matricielles se retrouvent dans le tableau de résultats présent en colonne L à P grâce à du VBA.
Je ne peux pas faire plus explicite je pense.

 

[safranien]

Bonjour safranien, job75,

J'avais commencé à coder avec des dictionnaires. @job75 ayant été ultra rapide (tant en temps de réponse qu'en temps d’exécution ), j'ai complété par une méthode sans dictionary (un peu plus longue à s'exécuter).

Mais dans les deux méthodes, je construis une colonne de dates sans doublon.

Suite à votre demande supplémentaire, je ne vois pas, moi non plus, comment se passer de la colonne date ???
A moins que vous ne disposiez quelque part dans votre classeur d'une colonne avec les dates sans doublon déjà établies et qu'on pourrait lire et utiliser ?

Dans votre image, on voit des colonnes avec des dates avec doublon et plus à droite des colonnes de puissances max. Or pour des dates identiques, les valeurs des puissances max sont différentes. Quelques chose m'échappe sans doute comme à @job75.

nota: tout comme @job75, les tableaux résultats sont identiques à celui de votre fichier du message #1.

Oups! J'ai trop nettoyé pour fichier pour le téléverser . Je vais remettre les bonnes données.

Bonjour mapomme

merci pour votre intérêt pour m'aider.

Dans mon fichier exemple, les données en B et C sont des données toutes les 10 mn.
En colonne S, il y a bien une seule date unique journalière issue de la colonne C. C'est pour cela que je ne comprends pas la nécessité de créer une nouvelle colonne avec des dates uniques qui vient se mettre à un endroit que je ne pourrai pas utiliser dans mon fichier de travail et qui en plus fait doublon avec la colonne S.
Il me semblait qu'en regardant les formules matricielles, on voyait assez bien à quoi celles-ci font référence en plus de mes explications, notamment au fait qu'elles recherchent la valeur max atteinte selon chaque jour affiché en colonne S.

 

[mapomme]

Re,

Remis le bon fichier ici dans le message #16.

 

[safranien]

merci mapomme mais même conclusion qu'avec la proposition de job75.
Ma colonne de Dates, en S, est générée dans mon fichier de travail. Dans cette colonne, j'ai bien 365 jours unique.
De fait, pourquoi ajouter une colonne de dates en L sachant que mon tableau de valeur doit être en colonnes L à P ? Le code ne peut il pas utiliser les dates déjà présentes en colonnes S ??
De plus, mon tableau de valeur est déjà formaté (quadrillé, coloré etc), pas besoin de mise en forme avec la macro. Enfin, j'ai besoin d'avoir des 0 plutot que des cellules vides.
Donc, au final, même si la derniere date visible en colonne C est le 21/04/2021, je dois retrouver dans mon tableau de résultats, des valeurs jusqu'à cette date puis que des 0 pour les dates suivantes (puisque pas de correspondance avec les dates de la colone C), les dates étant en Colonne S.
La longueur du tableau de valeur est calée sur la longueur de la colonne de Dates en S.

Le rendu après exécution du code doit donc être :

 

[mapomme]

Re,

Donc c'est bien ce que j'avais pressenti. Vous disposez d'une colonne S avec les dates à prendre en compte.
Nous ne pouvions pas le deviner. Il est extrêmement rare d’avoir des référence à droite des formules qui en dépendent (d'ailleurs Excel est plus rapide quand les formules ont leurs antécédents dans les cellules à gauche et au-dessus de la cellule contenant la formule)

Mon fichier a été bâti avant de comprendre ce qui se passe dans votre tableau...

C'est plus clair mais il faut remanier le code du moins introduire une verrue

 

[safranien]

je ne l'ai pas écrit dans mon poste #1, pensant que vous regarderiez la formule matricielle qui fait bien référence à la colonne S. Cependant je l'ai précisé par la suite, dans le post #4 après le premier retour de job75.

Je prends note du fait qu'il vaudrait mieux déplacer la colonne de dates S avant le tableau de valeur. Si j'arrive à comprendre les codes que vous me proposez, j'essaierai de modifier mon fichier de travail en conséquence mais mon problème est que mon fichier de travail comporte beaucoup d'autres codes et formules ce qui me donnera pas mal de boulot. Si, avec la colonne de dates à droite, le temps d'exécution est allongé d'une demi-seconde, ça m'ira très bien en comparaison aux 45 secondes que met les fichier à calculer toutes les formules matricielles aujourd'hui ....

Je commence à regarder les codes et merci énormément pour le détail des explication des lignes.
Ce qui m'amène à dire que faire la recopie des colonnes C, J et B sur L, M et N ne sera pas possible car j'ai d'autres données sous ce tableau de valeurs qui vont s'en retrouver effacées. Si j'ai bien compris le code excel, il est question de recopier ces données, de faire un tri dégressif, un filtre unique et recopier les valeurs dans le tableau. C'est une bonne astuce à laquelle je n'aurais pas pensé. Mais ce qui me fait comprendre qu'il n'y a pas d moyen simple de retranscrire en VBA une formule matricielle simple c'est ça ?

 

[Hasco]

Bonjour,

Voici une solution par Power Query.
J'ai nommé des plages Prm_Dates et Prm_Tranches pour en passer les valeurs comme paramètres aux requêtes.
La requête TableEq n'est là que pour reconstituer une table avec les colonnes disparates de 'Puissance' 'Date' et 'Tranche'.
Le résultat est dans la deuxième feuille.
On peut facilement en rapatrier les valeurs dans votre tableau au besoin, mettre des 0 pour remplacer les vides et ce que vous voudrez (dans les limites de PQ)

Cordialement

 

[safranien]

Bonjour Hasco

merci pour votre contribution. Je ne me suis pas encore penché sur l'utilisation de PQ et encore moins la manière d'inclure du PQ dans mon fichier de travail. Cependant, je retiens votre proposition qui me sera à coup sûr forte utile pour d'autres sujets sur lesquels je travaille.

Pour ce cas d'exemple, j'aimerais vraiment obtenir le résultat attendu par VBA pour que ce soit homogène avec le reste de mon fichier.

Merci

 

[job75]

Re, salut mapomme, Hasco,

Si l'on veut que les dates soient restituées en colonne S au lieu de L la modification est facile, fichier (4) :

Sub Maximum()
Dim F As Worksheet, dest As Range, dates As Range, d As Object, dd As Object, tablo, ncol%, j%, i&, dat, n&, resu(), nn&, col%, a(), b(), c
Set F = Sheets("Feuil1") 'nom de la feuille à adapter
Set dest = F.[L14:P14] 'ligne des en-têtes, à adapter
Set dates = F.[S14] 'en-tête de la colonne des dates, à adapter
Set d = CreateObject("Scripting.Dictionary")
Set dd = CreateObject("Scripting.Dictionary")
tablo = F.[B14].CurrentRegion.Resize(, 9) 'matrice, plus rapide
'---liste des  en-têtes de colonnes---
With dest
    ncol = .Columns.Count
    For j = 1 To ncol
        d(.Cells(j).Value) = j 'repère le numéro de colonne
    Next j
End With
'---tableau des résultats---
For i = 2 To UBound(tablo)
    dat = tablo(i, 2)
    If Not dd.exists(dat) Then
        n = n + 1
        dd(dat) = n 'mémorise le numéro
        ReDim Preserve resu(1 To ncol, 1 To n)
    End If
    nn = dd(dat)
    col = d(tablo(i, 9))
    If col And IsNumeric(tablo(i, 1)) Then If CDbl(tablo(i, 1)) > resu(col, nn) Then resu(col, nn) = CDbl(tablo(i, 1))
Next i
If n = 0 Then Exit Sub
'---transposition des résultats---
ReDim a(1 To n, 1 To ncol)
ReDim b(1 To n, 1 To 1)
c = dd.keys 'liste des dates
For i = 1 To n
    b(i, 1) = c(i - 1)
    For j = 1 To ncol
        a(i, j) = resu(j, i)
Next j, i
'---restitution---
Application.ScreenUpdating = False
If F.FilterMode Then F.ShowAllData 'si la feuille est filtrée
With dest.Offset(1).Resize(n)
    .Value = a
    .Replace "", 0
End With
dates(2).Resize(n) = b
End Sub

 

[mapomme]

Re,

Adaptation de la méthode sans dictionary (avec une verrue )

• la colonne S sert de référence de date et la macro n'y touche pas
• les max pour chaque ligne se réfèrent à la date de la même ligne en colonne S

 

[job75]

Contrairement à ce que vous semblez désirer safranien, je maintiens qu'il faut effacer les valeurs existantes sous les valeurs restituées, fichier (5) :

'---restitution---
Application.ScreenUpdating = False
If F.FilterMode Then F.ShowAllData 'si la feuille est filtrée
With dest.Offset(1).Resize(n)
    .Value = a
    .Replace "", 0
    .Rows(1).Offset(n).Resize(F.Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
dates(2).Resize(n) = b
dates(2).Offset(n).Resize(F.Rows.Count - n - dates.Row).ClearContents 'RAZ en dessous
End Sub

 

[safranien]

Re, salut mapomme, Hasco,

Si l'on veut que les dates soient restituées en colonne S au lieu de L la modification est facile, fichier (4) :

Sub Maximum()
Dim F As Worksheet, dest As Range, dates As Range, d As Object, dd As Object, tablo, ncol%, j%, i&, dat, n&, resu(), nn&, col%, a(), b(), c
Set F = Sheets("Feuil1") 'nom de la feuille à adapter
Set dest = F.[L14:P14] 'ligne des en-têtes, à adapter
Set dates = F.[S14] 'en-tête de la colonne des dates, à adapter
Set d = CreateObject("Scripting.Dictionary")
Set dd = CreateObject("Scripting.Dictionary")
tablo = F.[B14].CurrentRegion.Resize(, 9) 'matrice, plus rapide
'---liste des  en-têtes de colonnes---
With dest
    ncol = .Columns.Count
    For j = 1 To ncol
        d(.Cells(j).Value) = j 'repère le numéro de colonne
    Next j
End With
'---tableau des résultats---
For i = 2 To UBound(tablo)
    dat = tablo(i, 2)
    If Not dd.exists(dat) Then
        n = n + 1
        dd(dat) = n 'mémorise le numéro
        ReDim Preserve resu(1 To ncol, 1 To n)
    End If
    nn = dd(dat)
    col = d(tablo(i, 9))
    If col And IsNumeric(tablo(i, 1)) Then If CDbl(tablo(i, 1)) > resu(col, nn) Then resu(col, nn) = CDbl(tablo(i, 1))
Next i
If n = 0 Then Exit Sub
'---transposition des résultats---
ReDim a(1 To n, 1 To ncol)
ReDim b(1 To n, 1 To 1)
c = dd.keys 'liste des dates
For i = 1 To n
    b(i, 1) = c(i - 1)
    For j = 1 To ncol
        a(i, j) = resu(j, i)
Next j, i
'---restitution---
Application.ScreenUpdating = False
If F.FilterMode Then F.ShowAllData 'si la feuille est filtrée
With dest.Offset(1).Resize(n)
    .Value = a
    .Replace "", 0
End With
dates(2).Resize(n) = b
End Sub

Bonjour Job75, merci pour ce retour mais il n'y a pas de sujet sur la restitution des dates en colonne S ou autre. Cette colonne S de dates, je la génère déjà dans mon fichier de travail via un autre process, comme dit précédemment.

Re,

Adaptation de la méthode sans dictionary (avec une verrue )

• la colonne S sert de référence de date et la macro n'y touche pas
• les max pour chaque ligne se réfèrent à la date de la même ligne en colonne S

Bonjour mapomme

super, ça avance bien. Que faut-il comprendre exactement par "verrue" ?
Et je comprends qu'il n'y a pas d'autres moyens aussi rapides et efficaces de faire ce calcul matriciel en VBA qu'en recopiant toutes les données, les filtrer etc ?
Comme dit également, sous mon tableau de valeurs où s'inscrivent les résultats des calculs, j'ai d'autres données et ton code semble m'effacer toutes les données des colonnes L à P (à partir de L7 me semble-t-il). Dans mon fichier de travail, ce tableau de valeurs s'étend de L15 à P380.
Est-il possible d'effacer uniquement cette plage plutôt que la totalité du contenu des colonnes ?
En PJ, votre fichier que j'ai tenté de modifier : j'ai réussi à faire démarrer l'effacement à partir de L15 plutôt que L7, mais je ne trouve pas comment dire d'effacer jusqu'à la ligne 380.
J'ai également changé d'endroit la recopie des colonnes de valeurs que j'ai mise en AA, AB et AC. A ce sujet, est ce possible de faire la recopie uniquement des valeurs plutôt que de toute la mise en forme ? En effet, j'aimerais mettre en gris ces 3 colonnes pour indiquer qu'il ne faut rien y saisir et que tout reste en gris.
Pour info, dans mon fichier de travail, mes colonnes B et C peuvent aller au maximum jusqu'à la ligne 52710. J'imagine que cela va affecter le temps de traitement de la recopie de toutes les valeurs pour faire le filtre etc J'essaierai de tester dans la journée.
Est-ce que ces premières modifications vous semblent correctes et pouvez vous m'aider à compléter ?

 

[mapomme]

Est-ce que ces premières modifications vous semblent correctes et pouvez vous m'aider à compléter ?

Bonjour,,

Oui c'est faisable. Juste un peu de temps...

 

[safranien]

Pas de souci, je ne suis pas pressé et je vous suis déjà reconnaissant de m'aider.

J'ai tenté d'inclure le code dans mon fichier de travail, en déplaçant vers les colonnes CA à CC la recopie des valeurs des colonnes B, C et J et comme je le craignais, je me retrouve avec une erreur que je n'ai pas dans le fichier que vous m'avez retourné.
Mes données de la colonne B proviennent d'un outil nommé PI Data Link. Est ce que ça pourrait venir de là, si vous connaissez ?
J'ai ensuite essayé de recopier les données des colonnes B,C et J vers une autre feuille vierge mais là encore, je dois mal faire quelque chose car j'ai des erreurs qui surviennent également.
Comme je le disais, ma feuille de calculs de mon fichier de travail est vraiment remplie de multiples données et calculs et je me dis que cette histoire de tri ne doit pas plaire ?