XL 2019 Améliorer le temps de recalcul

S

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
Trier par date Trier par votes
Hasco

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:
Vote positif 0 Vote négatif
sylvanu

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:
Vote positif 0 Vote négatif
S

st007

XLDnaute Barbatruc
Hasco à dit:
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
Cliquez pour agrandir...
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"
 
Vote positif 0 Vote négatif
S

st007

XLDnaute Barbatruc
sylvanu à dit:
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.
Cliquez pour agrandir...
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"
 
Vote positif 0 Vote négatif
S

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.
 
Vote positif 0 Vote négatif
S

st007

XLDnaute Barbatruc
Hasco à dit:
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.
Cliquez pour agrandir...
Donc modifié comme ceci
VB: 
= Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each (Text.Trim([Valeur])<> "" and Text.Trim([Valeur])= "Need"))
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
st007 à dit:
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
Cliquez pour agrandir...
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
Vote positif 0 Vote négatif
S

st007

XLDnaute Barbatruc
sylvanu à dit:
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 )
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour,
st007 à dit:
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
Cliquez pour agrandir...
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 ?
 
Vote positif 0 Vote négatif
job75

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
Vote positif 0 Vote négatif
S

st007

XLDnaute Barbatruc
job75 à dit:
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+
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
S

st007

XLDnaute Barbatruc
sylvanu à dit:
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 ?
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif

Discussions similaires

J
  • Question
XL 2019 Calculer une différence de temps théorique avec le temps réel
Réponses
4
Affichages
395
goube
goube
L
  • Question
Microsoft 365 TCD et référence de cellules
Réponses
7
Affichages
343
alexga78
A
A
  • Question
XL 2016 Reprendre la valeur d'une cellule dans le titre d'un graphique...
Réponses
5
Affichages
570
Orson83
Orson83
G
  • Question
Microsoft 365 Reconstitution stock a date - besoin d'aide
Réponses
0
Affichages
392
GregH
G
J
  • Question
Power Query Aide - Fonctions pour afficher le détail des inscrits (nom, prénom date de naissance) parmi une liste de différents stages.
Réponses
6
Affichages
617
Hasco
Hasco
D
  • Question
Microsoft 365 Cas réel - avec problème complexe sur de la prevision de match en evitant les doublons
Réponses
1
Affichages
590
Dock91
D
L
  • Question
XL 2019 Problématique de conservation de valeur à un instant T
Réponses
7
Affichages
537
vgendron
vgendron
J
  • Question
XL 2013 Obtenir une boucle de liste dans un tableau qui change avec le choix de l'année
Réponses
2
Affichages
386
mapomme
mapomme
O
  • Question
XL 2019 Macro pour calculer plusieurs bulletins de paie à la fois
2
Réponses
17
Affichages
2 K
olivier1984
O
P
  • Question
XL 2016 comptage de cases contigües selon critères
2
Réponses
25
Affichages
1 K
PiLVaLK
P

Membres actuellement en ligne

5 membres et 96 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 486
Messages
2 110 107
Membres
110 666
dernier inscrit
Yaya123