Microsoft 365 Lenteur excessive fichier Excel et demande d'aide

[Lyesse]

Bonjour à toutes et à tous,

J'ai besoin d'aide svp.

Je rencontre un grand problème avec le fichier "202109_Féquentation VBA.xlsb" qui prend un temps fou lors du lancement de la macro et surtout lors de l'enregistrement.

J'aurais svp besoin de votre aide pour créer une macro qui me permet de rechercher le nombre de voyageur depuis le fichier "Ex_09_2021.xlsx" colonne "O"

La formule Excel que j'utilise pour faire cela est déjà présente dans la cellule D9 / D40 / D72 et D104 pour vous aider à comprendre ce qu'il me faut.

Ce que je ne sais pas faire, c'est de lancer un UserForm contenant une ListBox en double cliquant sur une cellule qui contient une donnée pour afficher les informations suivantes (du fichier "Ex_09_2021.xlsx") dans la ListBox : Date et heure + Ligne + Course + Point de montée + Nb voyageur + Tarif + Prix.

J'essaye de trouver une solution pour vous envoyer les fichier car la base de données contient 191000 lignes, j'ai supprimé des données de la base de données pour alléger le fichier.

Je vous remercie d'avance

Cordialement Lyesse

 

[Bernard_XLD]

Bonjour lyesse, le forum

J'ai repris un peu votre code, le temps est divisé par deux sur mon ordi.

Sub LancerCalcul()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With shFrequentation
    .EnableFormatConditionsCalculation = False
    If .Range("E4") = "Ligne_1" Then
       With .Range("E1")
           .Interior.Color = RGB(0, 100, 200)
           .Font.Color = RGB(255, 255, 255)
           .Font.Bold = True
        End With
    ElseIf .Range("E4") = "Ligne_6" Then
        With .Range("E1")
           .Interior.Color = RGB(0, 100, 200)
           .Font.Color = RGB(255, 255, 255)
           .Font.Bold = True
        End With
    End If
    .Range("Donnees").ClearContents
    With .Range("D9")
        .Formula2R1C1 = "=IFERROR(IF(COUNTA([Ex_09_2021.xlsx]RawData!R3C2:R200000C2)-SUMPRODUCT((WEEKDAY([Ex_09_2021.xlsx]RawData!R3C2:R200000C2)<7)*1)>0,SUMIFS([Ex_09_2021.xlsx]RawData!C12,[Ex_09_2021.xlsx]RawData!C7,R4C8,[Ex_09_2021.xlsx]RawData!C10,RC3,[Ex_09_2021.xlsx]RawData!C2,"">=""&R4C4,[Ex_09_2021.xlsx]RawData!C2,""<=""&R5C4,[Ex_09_2021.xlsx]RawData!C3,"">=""&R8C,[Ex_09_2021.xlsx]RawData!C3,""<=""&R8C[1]),0),"""")"
        .Copy
    End With
    .Range("Donnees").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    .Range("A1").Select
    .EnableFormatConditionsCalculation = True
    .Calculate
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Mais le problème vient de la complexité de vos formules, de leur nombre, de vos MFC et de la taille de la base de données que vous avez mis dans vos formules (200000 lignes à analyser, avec 10000 on divise le temps par 6).
Si vous avez vraiment besoin d'analyser ce nombre de lignes, je pense que PowerQuery serait plus adapté.
Je ne l'utilise pas mais @Hasco en est un spécialiste , il pourra peut être vous dépanner.

Bien cordialement, @+

 

[Lyesse]

Bonsoir,
Je vous remercie pour votre réponse, je peux déjà commencer par supprimer les MFC.

Vous avez aussi eu le problème lors de l'enregistrement ou la fermeture ou le fichier passe en "Ne répond pas" et ça dure 10 minutes ?

Effectivement le fichier initial comprends 191000 ligne et comme je dois l'adapter pour tous les mois de l'année, il est susceptible de dépasser cette limite très rapidement.

Je vais essayer avec PowerQuery sinon je demanderai de l'aide à @Hasco.
Je vous remercie encore.

Bonne soirée

 

[Bernard_XLD]

Vous avez aussi eu le problème lors de l'enregistrement ou la fermeture ou le fichier passe en "Ne répond pas" et ça dure 10 minutes ?

Non, mais j'avais déjà modifié le code.

Je vous remercie pour votre réponse, je peux déjà commencer par supprimer les MFC.

Pas la peine, je les désactive pendant l'exécution de la macro avec .EnableFormatConditionsCalculation = False.

 

[Lyesse]

Ah d'accord, je vais le rajouter dans le code.
Merci

 

[Bernard_XLD]

C'est déjà dans le code du Post 2

 

[Lyesse]

Je vais rajouter ce bout de code à la macro.
Merci

 

[Lyesse]

Ah oui c'est vrai, je ne l'avais pas vu.
Là je viens d'essayer avec le code que vous m'avez donné et depuis ma 1ère réponse, le fichier est bloqué à charger la base de données complète.
Je trouve que ce n'est pas normal.
Pensez-vous que ça peut être un problème avec Excel 365, je n'ai jamais eu ça avant.
J'ai déjà fait des fichier qui faisait plus de 30 Mo et je n'ai pas eu ça.

 

[Bernard_XLD]

Re,

Je ne peux vous répondre, cela dépend vraiment de l'importance des données.
Tout dépend de ce que vous voulez faire avec vos formules, si c'est juste pour créer un horaire de bus, il serait beaucoup plus rapide de charger les données dans un tableau VB et de tout traiter en mémoire pour n'inscrire que les valeurs voulues, et ne plus utiliser de formules d'analyse sur l'ensemble des données qui seront par définition beaucoup plus lentes.

 

[Lyesse]

Au fait ce qui m'est demandé c'est de compter le nombre de fois qu'un titre (abonnement, ticket,...) est utilisé par arrêt et par horaire entre 2 dates.
Comme je vous l'ai dit, la base contient 191000 lignes car chaque passage dans le bus, une ligne est créée dans la base.
Je me doute que passer tout en VBA au lieu de rajouter des formules serait beaucoup mieux mais je ne sais pas le faire.
Si vous pouvez m'aider

 

[Lyesse]

Nous utilisons GESCAR, nous sommes un petit réseau

 

[Bernard_XLD]

Re,

Je me doute que passer tout en VBA au lieu de rajouter des formules serait beaucoup mieux mais je ne sais pas le faire.
Si vous pouvez m'aider

un peu tard pour aujourd'hui et je n'aurai pas de disponibilité avant mardi au mieux.

ce qui m'est demandé c'est de compter le nombre de fois qu'un titre (abonnement, ticket,...) est utilisé par arrêt et par horaire entre 2 dates.
Comme je vous l'ai dit, la base contient 191000 lignes car chaque passage dans le bus, une ligne est créée dans la base.

Cela me conforte dans l'idée que PowerQuery serait LA solution
J'attendrai de voir si Hasco a une proposition PowerQuery avant de me lancer dans un développement VBA qui sera peut être inutile.
Je suivrai la discussion !

Bonne nuit
Bien cordialement, @+

 

[Lyesse]

Oui, je comprend.
Pas de soucis, je vous remercie déjà pour votre aide et vos conseils.

Passez un bon week-end

 

[Hasco]

Bonjour,

J'ai ouvert vos fichiers, vu vos formules et comme je n'ai pas le temps d'interpréter tout ça, j'ai refermé.
Effectivement, PowerQuery peut-etre une solution pour interroger la base de données, fichier excel ou base de données de ERP GESCAR(que je ne connais pas).
Quoiqu'il en soit sur une BDD telle que celle-ci, un temps de latence plus ou moins important risque d'être présent.

Je vais essayer de partir sur la dernière expression de la demande :

Au fait ce qui m'est demandé c'est de compter le nombre de fois qu'un titre (abonnement, ticket,...) est utilisé par arrêt et par horaire entre 2 dates.

Sur la base du fichier Ex_09_2021.xlsx, nous pourrons tenter une requête sur un nouveau fichier.
Dans un deuxième temps si tout va bien, nous verrons comment l'intégrer dans votre fichier 202109_Frequentation.


Cordialement

 

[Hasco]

Re,

Voici un premier jet qui compte le nombre de voyageur entre le 1/09/2021 0:0:0 et le 30/09/2021 23:59:59 dans des intervalles de temps de 5 minutes pour chaque "point de montée"

Il faut voir comment vous voulez prendre en compte les regroupement par horaires, dans l'exemple joint les dates/heures sont arrondies à 5 minutes pour réduire le nombre de ligne.

Dans votre fichier 'Fréquentation' les échelles de temps ne sont pas uniformes, ce qui va complexifier les choses.

Il faut préciser votre histoire de 'Ticket' : faut-il grouper également sur cette colonne ? Ce qui semblerait un non sens ?

Un ticket (passager ?) ne pouvant être présent plusieurs fois au même moment et dans le même lieu

Dans le requêtage les termes sont importants alors traduisez vos souhaits en pensant que les concepts de votre métier ne sont pas universels.

IMPORTANT : avant d'actualiser la requête, changez le chemin vers le fichier dans la cellules idoine de le zone B2:B4 qui a été nommée 'PQ_Critères'


Cordialement