Discussion :

[jpma75]

Bonjour,

J'importe un fichier csv de taille variable dans un classeur. Une feuille de ce classeur traite les données dudit fichier au moyen de formules RECHERCHEV, EQUIV, INDEX, DECALER. La plage de calculs est surdimensionnée (en lignes et en colonnes) pour faire face au volume imprévisible des fichiers csv.

Dès lors que je peux calculer les dimensions réelles du fichier csv au moment de son importation, y a-t-il un moyen quelconque d'inhiber provisoirement une plage de formules pour ne pas ralentir le calcul de la feuille ?

Je crains que le réponse soit non mais je regretterais de ne pas vous l'avoir posée.

En vous remerciant par avance pour vos lumières,

Cordialement,
jp

[Philippe Tulliez]

Bonjour,
Il est possible de passer au calcul manuel le temps de l'exécution d'une procédure

Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub t()
 Dim CalculaltionStatus As Integer
  With Application
   CalculaltionStatus = .Calculation
  .Calculation = xlCalculationManual  ' Passe en mode calcul manuel
   '
   ' Ici code
   '
  .Calculation = CalculaltionStatus   ' Se remet en mode calcul initial
  End With
End Sub

[jpma75]

(Re)bonjour,

Oui, j'avais bien pensé à cette solution mais à un moment ou à un autre la feuille va bien recalculer les cellules qui contiennent des formules même si la "source" est vide ?!

Par ailleurs, le classeur met du temps à se fermer ; sans doute ne dois-je pas savoir où mettre le calcul manuel (et surtout le remettre en automatique avant la fermeture définitive...).

Dans ThisWorkbook :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
    If SaveAsUI = True Then
        MsgBox "Désolé, l'option Enregistrer sous... est impossible !", vbExclamation
        Cancel = True
    End If
    Call Options_Enregistrement
End Sub

Dans un module standard :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
Option Private Module
Sub Options_Enregistrement()
Dim Sh As Worksheet
Dim Répertoire As String, FichierIndexé As String
Dim date_svg As String
 
'    Application.Calculation = xlCalculationManual
 
    date_cal = Format(Sheets("calBis").Range("B1"), "yyyy.mm.dd")
    date_svg = Format(Now, "yyyy.mm.dd hh""h""nn")
    version = "#" & Sheets("synth_2").Range("C3")
 
    Répertoire = ThisWorkbook.Path
    FichierIndexé = "Suivi effectifs " & version & " (calendrier " & date_cal & ") " & date_svg & ".xlsm"
 
    Sheets("synth_2").Select
 
    ThisWorkbook.SaveAs Répertoire & "\" & FichierIndexé
 
'    Application.Calculation = xlCalculationAutomatic
 
End Sub

Cordialement,
jp

[laurent_ott]

Re-bonjour.
Effectivement tout dépend de la taille des données à traiter ?
Sur de petits volumes les fonctions Excel marchent bien.
Sur les gros ça devient long, et je préfère programmer en VBA. Pour les recherches on peut mémoriser la liste, la trier, puis faire une recherche dichotomique. voir tomes 1 et 6 de ma signature.
Si c'est inférieur à 65 ko on peut aussi utiliser le SQL, qui fait plein de choses, voir tome 5.
Cordialement.

[Pierre Fauconnier]

Salut.

1. Si tu utilises des tableaux structurés, tu n'as pas besoin de surdimensionner tes lignes (cfr mon tuto sur les tableaux structurés, notamment le chapitre V). Dès lors, si le tableau est vide, il n'y a pas de recalcul. L'idée est donc de récupérer le CSV et de le transférer dans le tableau structuré. Ca pourrait probablement être effectué par Power Query.
2. Le fait que ton nombre de colonnes soit variable découle pour moi d'un problème de conception⁽¹⁾ et de mise en forme des données. Les colonnes ne sont normalement pas variables.
3. Power Query pourrait réaliser une partie du job grâce aux colonnes formulées dans Power Query. Ainsi, le calcul est uniquement réalisé à l'importation des données, Power Query servant une "table à plat" vide de formules. Cette solution permettrait probablement un gain de temps à la fermeture, car sauf à modifier l'option, le classeur est recalculé à la fermeture de toute façon.

Pour moi, c'est dans ce sens qu'il faut réfléchir et avancer. Sinon, tu pourrais supprimer les formules, importer tes données, ajouter les formules par VBA puis effectuer un collage spécial valeurs pour supprimer les formules et les remplacer par les valeurs calculées.

Il nous faudrait plus d'infos sur les tenants et aboutissants pour pouvoir te conseiller au mieux.


⁽¹⁾ Je peux comprendre que les données externes fluctuent en nombre de colonnes (quoi que cela révèle pour moi une mauvaise préparation des données en amont, mais on n'est pas forcément maître de cela), mais la première chose à réaliser alors est de les remettre dans un canevas dont la structure ne change pas (les colonnes restent inchangées dans ta solution après retraitement de l'import). Power Query permet presque toujours de réorganiser correctement les données, puisque c'est en fait sa raison d'être.

[jpma75]

Bonjour Pierre,

Merci pour ces remarques et suggestions.

Si tu utilises des tableaux structurés (...)

Le CSV renvoie des données par colonne de dates (nombre variable) et sur environ 1.400 lignes (nombre variable). A noter par ailleurs que certaines lignes (pour chaque colonne) sont vides. De ce que j'avais vu des TS (avec des en-têtes de colonne spécifiques, etc.), je ne vois pas comment y transposer mes données.

Ca pourrait probablement être effectué par Power Query.

Pas très à l'aise avec tout ça.

Le fait que ton nombre de colonnes soit variable découle pour moi d'un problème de conception (...)

Peut-être mais comme je ne connais pas d'avance la largeur de mon CSV, il faut bien que je prévoie des colonnes de calculs plus largement au niveau de mon classeur ?!

PS : si tu veux et si j'arrive à anonymiser certaines données, je peux t'envoyer mon fichier en mp pour que tu vois à quoi il ressemble ?!

Cordialement,
jp

[Pierre Fauconnier]

Je me doutais bien que c'était des dates, ou des valeurs temporelles, qui amenaient à un nombre fluctuant de colonnes.

En fait, il faut passer du tableau bleu au tableau vert (ici via Power Query via un dépivotage des autres colonnes, sur base des deux premières sélectionnées)




Dès lors, si on ajoute des colonnes ou des lignes, un simple clic droit/actualiser sur le tableau vert adapte le résultat qui contient toujours le même nombre de colonnes.





Tu as ainsi une source à structure variable que tu amènes dans ta solution qui requiert une structure fixe (le nombre de colonnes ne fluctue plus, le nombre de lignes ne modifie pas la structure). Tu as alors un tableau bien formé pour utiliser tous les outils d'Excel (fonctions xxx.si.ens, TCD, filtres, MFC, ...)


La transformation Bleu => Vert se réalise idéalement avec Power Query, mais peut être réalisée par VBA. Je pense que c'est vraiment sur cet axe qu'il faut travailler.


PS: Perso, je n'ai jamais compris pourquoi les "programmeurs" extraient des CSV aussi mal foutus des CRM, ERP et autres bazars en trois lettres? On ne les a jamais formés, durant leur cursus, à extraire des données qui soient utilisables par tableur? Tant mieux pour moi, je fais un partie de mon beurre en rectifiant le tir après coup. Dommage pour l'utilisateur final, qui paie deux fois le prix de son extraction...

[Pierre Fauconnier]

[...]

Pour moi, avant de partir sur une recherche dichotomique, du sql ou que sais-je, il faut travailler la conception du classeur. C'est la base des bases. Du sql ou une recherche dichotomique sur une mauvaise organisation des données, ça n'a aucun sens, ça égare le lecteur sur des pistes trop complexes par rapport à une utilisation normale d'Excel.

De nos jours et sur les versions modernes d'Excel (>= 2010), il faut maîtriser un tant soit peut les notions suivantes avec Excel:

• Tableaux structurés;
• Power Query;
• Graphiques;
• Tableaux croisés dynamiques;
• Quelques fonctions telles qu les xxx.si.ens, sommeprod, recherchex ou index/equiv, ...;

En maîtrisant peu ou prou ces outils et en comprenant comment concevoir ses classeurs, on couvre au moins 95% de l'utilisation raisonnée d'Excel⁽¹⁾, même sur de "gros" volumes de données, sans besoin de VBA pour autre chose que d'utiliser certaines tâches, et certainement pas avec des trucs comme le sql ou la recherche dichotomique qui sortent clairement du cadre des connaissances qu'un utilisateur lambda d'Excel doit avoir et qui sont vraiment du domaine du programmeur.

Une lenteur de calcul sur +/- 1400 lignes de données ne peut que découler d'une mauvaise organisation de son classeur.


⁽¹⁾ La majorité des demandes sur le forum (et chez mes clients) est relative à des données de gestion et leur analyse. On trouve bien sûr des besoins plus pointus (les fonctions d'ingéniérie, par exemple), mais les personnes ayant ces besoins sont en général autonomes sur Excel.

[laurent_ott]

Bonjour Pierre.
Effectivement il faut utiliser les fonctions intégrées d'Excel autant que possible. C'est souvent plus rapide et plus pratique (voire plus souple pour la maintenance par les utilisateurs) et donc souvent plus efficace qu'une programmation en VBA.
Sauf sur les gros volumes de données : plusieurs dizaines de milliers de lignes (ce qui ne doit pas concerner beaucoup de monde). Nous savons bien que Excel n'est pas fait pour cela, ce qui explique aussi que les fonctions intégrées ne suivent plus dans ce cas (et parfois retournent des erreurs, comme Transpose). D'où, à mon avis, le recours à la programmation, et des astuces que j'ai proposées dans le tome 6 pour avoir des temps de traitement raisonnables sur ces gros volumes.
Cordialement.

[Pierre Fauconnier]

Salut Laurent,

Comme je l'ai dit, on est dans le cas de figure que tu mentionnes (tellement de lignes de données qu'Excel a une indigestion) dans le 1% des cas d'utilisation d'Excel qui posent, éventuellement problème. Une bonne conception, une bonne maîtrise des outils et Excel peut déjà avaler des dizaines de milliers de lignes sans moufter, même avec des formules, le vba ne pouvant pas faire plus vite que Power Query (code interprété vs code compilé, sauf colonnes formulées dans PQ).

Les problèmes surviennent avec Excel à cause principalement d'une mauvaise conception, puis d'une connaissance lacunaire des outils. Résolvons cela avant de parler de sql, de recherche dichotomique et autres trucs réservés à des programmeurs, mais hors du champ de manipulation de l'utilisateur lambda. On peut raisonnablement douter qu'Excel soit un outil qui, dans son utilisation quotidienne, demande des connaissances en bases de données et en algorithmes de tri.


Je reste pour ma part persuadé que les problèmes rencontrés avec Excel proviennent, dans la plupart des cas, d'une mauvaise organisation du classeur. On reçoit des données, on les remet d'équerre si elles ne le sont pas et on utilise les outils qu'Excel met à notre disposition. 99% des problèmes sur le forum seraient morts-nés si on adaptait cette façon de voir les choses, pour ce qui est de l'utilisation raisonnée d'Excel (outil d'analyse de données et pas couteau suisse du "mauvais" programmeur). Si on doit programmer par ce qu'on est hors ou à la limite de l'outil, on s'appuie sur une conception TIP-TOP du classeur et on évite 90% des lignes de code (par exemple, en utilisant les références structurées dans son code, ...)

Le reste n'est pas du domaine de l'utilisateur Excel, mais du développeur professionnel.

[laurent_ott]

Je suis d'accord avec toi Pierre.
Et concernant les questions sur le forum, un petit tour sur les FAQ qui sont très riches, permettait aussi de répondre à pas mal de problèmes.
Cordialement.

[jpma75]

@ Pierre,

Bonsoir,

En maîtrisant peu ou prou ces outils (...) on couvre au moins 95% de l'utilisation raisonnée d'Excel (...) et certainement pas avec des trucs comme le sql ou la recherche dichotomique (...).

C'est noté !

Une lenteur de calcul sur +/- 1400 lignes de données ne peut que découler d'une mauvaise organisation de son classeur.

Idem

De nos jours et sur les versions modernes d'Excel (>= 2010), il faut maîtriser un tant soit peut les notions suivantes avec Excel:

Tableaux structurés;

Manifestement puissant(s) mais pas toujours aussi simple à mettre en oeuvre pour le novice.

Power Query;

Je viens de jeter un oeil sur ce site :

Il faut avouer que c'est tentant ! Mais quid quand l'utilisateur final se retrouve avec des CSV de tailles différentes. Et quid de l'automatisation de ces procédures ? Aujourd'hui, l'utilisateur de mon classeur est autonome pour importer le CSV et en récupérer les données voulues en 4 ou 5''.

Graphiques;

Tableaux croisés dynamiques;

Quelques fonctions telles que xxx.si.ens, sommeprod, recherchex ou index/equiv, ...;

Ne connaissais pas RECHERCHEX. Accessible uniquement avec 365 ?! J'en étais resté à INDEX/EQUIV pour remplacer avantageusement RECHERCHEV ; et depuis, j'ai vu l'excellent (désolé pour le jugement de valeur) tuto : https://excel.developpez.com/actu/18...re-Fauconnier/

Cordialement,
jp