XL 2019 Améliorer le temps de recalcul

st007

XLDnaute Barbatruc
Bonjour,
Ce morceau de fichier fonctionne selon mes besoins, puisque réduit à 20 lignes.
Or le fichier complet évolue chaque semaine et comporte désormais 2000 lignes environs par onglet steam,diesel,electrique, maglev et hyperloop
j'ai donc en feuil6 2000 formules matricielle avec la fonction indirect pour "selectionner" l"onglet de recherche ainsi que le "player" de référence en fonction de A1, B1, C1
Le but est de lister les loco dont le "player"(b1) "Need"(c1) et afficher les "player" les possédant marqué "Own"
ou en choisissant Own en C1 d'afficher les loco que le player en B1 "own" et de lister les joueurs les ayant marquer "need"

Et le soucis est devenu le temps de recalcul, avec power query, le temps de calcul est similaire et tourne aux allentours des 15 secondes. Du moins de la manière dont j'ai opéré.
Rien de vital donc, mais un point de vue extérieur me permettrais sans doutes d'évoluer, en formule, en query, ou en vba

Merci d'avance du temps consacré à mon sujet
 

Pièces jointes

  • Locos forum.xlsm
    134.3 KB · Affichages: 19

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

J'ai peut-être pas tout compris, mais voici deux requêtes
La première se contente de filtrer sur les critères en A1:A3
A1:A3 => Nommé "Critères"
La deuxième qui ensuite ramène et compte tous les players qui ne sont pas c ("Own ou collected ou needed")

La mise à jour est faite lorsqu'un critère de A1:A3 change si les 3 critères sont présents.

Attention dans vos données vous avez des espaces trainantes "Own " ou "Own " au lieu de "Own" idem pour les autres valeurs. Si vous ne corrigez pas vous pouvez faire la sélection des lignes par Text.StartsWith, comme dans le fichier joint ou Text.Contains.
Mais l'égalité est toujours préférable quand on veut gagner en temps d'exécution.

Cordialement
 

Pièces jointes

  • Locos forum.xlsm
    132.7 KB · Affichages: 4
Dernière édition:

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour S,
Sans être sur d'avoir tout compris, un essai en PJ.
1- Je choisis la feuille donnée par A1, que je met dans un array
2- Je cherche dans cet array o*ù se trouve le player donné en B1
3- Pour chaque loco où le statut est le même que C1, je répertorie tous les players qui sont "Need" pour cette même loco.
Avec :
VB:
Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Fin: If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [A1:C1]) Is Nothing Then
        ChercheLoco
    End If
Fin:
Application.ScreenUpdating = True
End Sub
Sub ChercheLoco()
    Dim tablo, Player$, OCN$, LigneEcr%, Col%, Lig%, Chaine$
    [C4:D10000].ClearContents                               ' On efface le tableau
    Application.ScreenUpdating = False                      ' On fige l'écran
    tablo = Sheets([A1] & "V").[A1].CurrentRegion           ' Tranefert feuille dan array
    Player = [B1]: OCN = Left([C1], 3)                      ' Acquisition Player et OCN ( Own,Collected,Need )
    LigneEcr = 4                                            ' Init ligne écriture
    For Col = 5 To UBound(tablo, 2)                         ' Pour tous les "Players"
        If tablo(1, Col) = Player Then                      ' Rechercher le bon player
            For Lig = 1 To UBound(tablo)                    ' Pour toutes les lignes
                If Left(tablo(Lig, Col), 3) = OCN Then      ' Si l'OCN est bon
                    Cells(LigneEcr, "C") = tablo(Lig, 2)    ' Ecrit loco
                    Chaine = ""
                        For ColNeed = 5 To UBound(tablo, 2) ' Pour tous les Players
                            If Left(tablo(Lig, ColNeed), 4) = "Need" Then   ' Si Needed alors on mémorise
                                Chaine = Chaine & tablo(1, ColNeed) & " - " ' On l'ajoute à la chaine
                            End If
                        Next ColNeed
                    If Chaine <> "" Then Cells(LigneEcr, "D") = Mid(Chaine, 1, Len(Chaine) - 3)
                    LigneEcr = LigneEcr + 1                 ' Prochaine ligne d'écriture
                End If
            Next Lig
            Exit Sub
        End If
    Next Col
    ActiveSheet.Columns.AutoFit                             'Ajustement largeurs colonnes
    ActiveSheet.Rows.AutoFit                                'Ajustement hauteurs lignes
End Sub
L'éxécution est auto quand on change les valeurs de A1,B1,C1.
PS : J'ai mis votre Worksheet_Change pour le test.
Avec 1000 lignes en Diesel, sur mon PC je suis autour de la seconde.
 

Pièces jointes

  • Locos forum.xlsm
    641.4 KB · Affichages: 4
Dernière édition:

st007

XLDnaute Barbatruc
Bonjour,

J'ai peut-être pas tout compris, mais voici deux requêtes
La première se contente de filtrer sur les critères en A1:A3
A1:A3 => Nommé "Critères"
La deuxième qui ensuite ramène et compte tous les players qui ne sont pas c ("Own ou collected ou needed")

La mise à jour est faite lorsqu'un critère de A1:A3 change si les 3 critères sont présents.

Attention dans vos données vous avez des espaces trainantes "Own " ou "Own " au lieu de "Own" idem pour les autres valeurs. Si vous ne corrigez pas vous pouvez faire la sélection des lignes par Text.StartsWith, comme dans le fichier joint ou Text.Contains.
Mais l'égalité est toujours préférable quand on veut gagner en temps d'exécution.

Cordialement
je vais creuser et chercher pourquoi ta proposition est bien plus rapide que la mienne. Pour être plus clair, les "collected" m'importe peu, si je sélectionne "Own" en c1, je cherche à savoir qui les a indiquer en "Need"
 

st007

XLDnaute Barbatruc
Bonjour S,
Sans être sur d'avoir tout compris, un essai en PJ.
1- Je choisis la feuille donnée par A1, que je met dans un array
2- Je cherche dans cet array o*ù se trouve le player donné en B1
3- Pour chaque loco où le statut est le même que C1, je répertorie tous les players qui sont "Need" pour cette même loco.
Avec :
VB:
Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Fin: If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [A1:C1]) Is Nothing Then
        ChercheLoco
    End If
Fin:
Application.ScreenUpdating = True
End Sub
Sub ChercheLoco()
    Dim tablo, Player$, OCN$, LigneEcr%, Col%, Lig%, Chaine$
    [C4:D10000].ClearContents                               ' On efface le tableau
    Application.ScreenUpdating = False                      ' On fige l'écran
    tablo = Sheets([A1] & "V").[A1].CurrentRegion           ' Tranefert feuille dan array
    Player = [B1]: OCN = Left([C1], 3)                      ' Acquisition Player et OCN ( Own,Collected,Need )
    LigneEcr = 4                                            ' Init ligne écriture
    For Col = 5 To UBound(tablo, 2)                         ' Pour tous les "Players"
        If tablo(1, Col) = Player Then                      ' Rechercher le bon player
            For Lig = 1 To UBound(tablo)                    ' Pour toutes les lignes
                If Left(tablo(Lig, Col), 3) = OCN Then      ' Si l'OCN est bon
                    Cells(LigneEcr, "C") = tablo(Lig, 2)    ' Ecrit loco
                    Chaine = ""
                        For ColNeed = 5 To UBound(tablo, 2) ' Pour tous les Players
                            If Left(tablo(Lig, ColNeed), 4) = "Need" Then   ' Si Needed alors on mémorise
                                Chaine = Chaine & tablo(1, ColNeed) & " - " ' On l'ajoute à la chaine
                            End If
                        Next ColNeed
                    If Chaine <> "" Then Cells(LigneEcr, "D") = Mid(Chaine, 1, Len(Chaine) - 3)
                    LigneEcr = LigneEcr + 1                 ' Prochaine ligne d'écriture
                End If
            Next Lig
            Exit Sub
        End If
    Next Col
    ActiveSheet.Columns.AutoFit                             'Ajustement largeurs colonnes
    ActiveSheet.Rows.AutoFit                                'Ajustement hauteurs lignes
End Sub
L'éxécution est auto quand on change les valeurs de A1,B1,C1.
PS : J'ai mis votre Worksheet_Change pour le test.
Avec 1000 lignes en Diesel, sur mon PC je suis autour de la seconde.
tu as tout compris à ce que je faisais. En bien plus rapide, sur mon tableau diesel de 2900 loco, je passe de 12 à 2,.. secondes.
Pour plus de visibilité, je vais filtrer la colonne D pour supprimer les "Vides"
 

st007

XLDnaute Barbatruc
En tout cas, grand merci à vous deux, les formules matricielles ont un temps de calcul non négligeable sur de grandes plages.
Le principe est qu'il s'agit d'un jeu multi joueur, un joueur par colonne. Sur un nombre évolutif de loco, ou le but est de toutes les avoir soit en "own", soit en "collected"
chaque joueur peut acheter une loco. il la met en "Own"
chaque joueur qui en a besoin l'indique en "Need"
Donc pour mes "own" je dois savoir qui les "need"
Et dans l'autre sens, celles que je "need", savoir quel joueur les "own" pour lui demander.
 

st007

XLDnaute Barbatruc
Et je me demandais ce qu'il fallait faire si autre chose était sélectionné en C1 c'est pour ça que j'ai renvoyé tous ceux qui n'était pas "Own" pour ces lignes
En cas de besoin, n'hésitez pas.
Donc modifié comme ceci
VB:
= Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each (Text.Trim([Valeur])<> "" and Text.Trim([Valeur])= "Need"))
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
En bien plus rapide, sur mon tableau diesel de 2900 loco, je passe de 12 à 2,.. secondes.
Pour plus de visibilité, je vais filtrer la colonne D pour supprimer les "Vides
Une V2 pour la route.
En évitant les écritures en feuille qui prennent du temps, en passant par un second array, sur la PJ ( et sur mon PC ) je passe de 1.125s à 86ms. Donc cela devrait être plus rapide.
( et je n'enregistre pas les locos sans demandeur )
 

Pièces jointes

  • Locos forum V2.xlsm
    642 KB · Affichages: 3

st007

XLDnaute Barbatruc
Re,

Une V2 pour la route.
En évitant les écritures en feuille qui prennent du temps, en passant par un second array, sur la PJ ( et sur mon PC ) je passe de 1.125s à 86ms. Donc cela devrait être plus rapide.
( et je n'enregistre pas les locos sans demandeur )
En effet, 85 ms en moyenne pour 2900 diesel.
Je vais cherché pour les "need" en C1 à afficher les player qui "own". D'où la petite macro qui était là pour afficher / masquer les colonnes
Encore merci.
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour,
Je vais cherché pour les "need" en C1 à afficher les player qui "own". D'où la petite macro qui était là pour afficher / masquer les colonnes
Si vous faites allusion à ça, effectivement je n'ai pas compris le but.
VB:
Columns(4).Hidden = [C1] = "Need"
Columns(5).Hidden = [C1] = "Own"
Il n'y a rien en colonne E de la Feuil6.
Quel était le but ?
 

job75

XLDnaute Barbatruc
Bonjour st007, sylvanu, le forum,

Sur la base du fichier de sylvanu une solution par formules matricielles.

Elles se recalculent en 0,5 seconde chez moi donc c'est jouable.

Mais il faut accepter d'afficher les locos sans Need.

A+
 

Pièces jointes

  • Locos forum(1).xlsx
    597.8 KB · Affichages: 3

st007

XLDnaute Barbatruc
Bonjour st007, sylvanu, le forum,

Sur la base du fichier de sylvanu une solution par formules matricielles.

Elles se recalculent en 0,5 seconde chez moi donc c'est jouable.

Mais il faut accepter d'afficher les locos sans Need.

A+
Bonjour et ravi de vous relire,
Avec l'ensemble des données, les "Noms" me permettent de gagner 2 sec par rapport aux matricielles que j'avais installées.
En rapidité pure, il semble que la v2 de Sylvanu remporte la course.
Merci de vous être intéressé à ma problématique.
 

st007

XLDnaute Barbatruc
Bonjour,

Si vous faites allusion à ça, effectivement je n'ai pas compris le but.
VB:
Columns(4).Hidden = [C1] = "Need"
Columns(5).Hidden = [C1] = "Own"
Il n'y a rien en colonne E de la Feuil6.
Quel était le but ?
Dans le fichier originel , a trop vouloir réduire la taille du fichier, son utilité n'est plus si flagrante. Voir même pas du tout. (le player17 diesel need fait basculer, j'aurais du ajouter des exemples)
Si je place "own" en c1, la colonne E est masquée (puisque je n'ai pas l'utilité de qui d'autre les "Own"
la colonne D affiche qui les "Need" (c'est le but de savoir qui en a besoin)

Si je place "Need" en C1, la colonne D est masquée (puisque je n'ai pas l'utilité de qui d'autre les "Need"
la colonne E affiche qui les "Own" (c'est le but de savoir qui les Own, pour leur demander)

Dans votre V2, en plaçant, diesel, player34, Need
Vous listez les players qui need aussi,
mon but est de lister dans ce cas les players qui "own"

Mon fichier est donc lent puisque comporte en réalité 3 colonne de 2900 ligne de matricielle, la petite macro qui masque/affiche la colonne selon la valeur de C1
 

Discussions similaires