XL 2013 Manipulations sur fichiers très volumineux

Aurelien74

XLDnaute Nouveau
Bonjour,

Je travaille souvent sur de gros fichiers Excel, avec plusieurs centaines de millier de lignes. Cela devient vite rédhibitoire pour effectuer des opérations très simples comme par exemple remplir une colonne B avec la valeur "1" pour des éléments filtrés (contenant une chaine de caractères) en colonne A. J'utilise en temps normal 2 techniques :
- cliquer-glisser de la 1ère cellule (avec la valeur 1) vers la dernière cellule de la colonne (B). Bon celle là, on l'oubli vite.
- Double clic sur la croix située dans le bord inférieur droit de la 1ère cellule. C'est le plus rapide que je connaisse, mais hélas le temps d'attente pour qu'Excel me rende la main dépasse bien souvent l'heure, ce qui se termine bien souvent par un kill.

J'aimerai savoir si vous aviez des techniques permettant d'écourter ce cas précis, et d'une manière plus générale pour gérer de gros fichiers Excel.

Merci pour votre retour,
Aurélien
 

Aurelien74

XLDnaute Nouveau
Re : Manipulations sur fichiers très volumineux

Bonjour bonoboas,

J'ai par exemple un fichier avec 10 colonnes et... 993137 lignes. Un peu plus de 200mo sur disque en csv, et environ 70mo en xlsx compression oblige.
Pour faire simple, ce sont des logs serveur avec URLs, code retour, valeurs divers. En exemple, j'ai attaché un fichier de 10 lignes anonymisé pour que tu puisses apprécier la structure. Dans mon fichier qui fait 993137 lignes, je souhaite créer par exemple une colonne K avec la valeur 1 pour toute les URLs ayant .html comme extension.

Merci pour l'aide...
 

Pièces jointes

  • classeur-exemple.xlsx
    10.3 KB · Affichages: 59
  • classeur-exemple.xlsx
    10.3 KB · Affichages: 56

job75

XLDnaute Barbatruc
Re : Manipulations sur fichiers très volumineux

Bonjour Aurelien74, bonoboas,

Pour modifier de très grands tableaux la méthode la plus rapide est d'utiliser des tableaux VBA.

Voyez le fichier joint et cette macro :

Code:
Sub Traiter()
Dim colsource$, coldest$, critere$, v, defaut$, t, i&
colsource = "F" 'colonne étudiée
coldest = "B" 'colonne à remplir
critere = "*.html" 'à adapter
v = 1
defaut = "-" 'valeur par défaut
t = Cells(1, colsource).Resize(Cells(Rows.Count, colsource).End(xlUp).Row + 1)
ReDim rest(1 To UBound(t), 1 To 1)
rest(1, 1) = Cells(1, coldest) 'titre
For i = 2 To UBound(t) - 1
  rest(i, 1) = IIf(t(i, 1) Like critere, v, defaut)
Next
Cells(1, coldest).Resize(UBound(t) - 1) = rest
End Sub
J'ai testé avec un tableau de 1 000 000 de lignes (tableau original copié 100 000 fois).

Le fichier pèse alors 35 Mo.

Sur Win 8 - Excel 2013 la macro s'exécute en 3,6 secondes.

A+
 

Pièces jointes

  • classeur-exemple(1).xlsm
    25.6 KB · Affichages: 52

job75

XLDnaute Barbatruc
Re : Manipulations sur fichiers très volumineux

Re,

Si le tableau est filtré la macro précédente donne un résultat erroné.

Il faut préalablement afficher toutes les lignes :

Code:
Sub Traiter()
Dim colsource$, coldest$, critere$, v, defaut$, t, i&
colsource = "F" 'colonne à étudier
coldest = "B" 'colonne à remplir
critere = "*.html" 'à adapter
v = 1
defaut = "-" 'valeur par défaut
On Error Resume Next
ActiveSheet.ShowAllData 'si un filtre est appliqué
On Error GoTo 0
t = Cells(1, colsource).Resize(Cells(Rows.Count, colsource).End(xlUp).Row + 1)
ReDim rest(1 To UBound(t), 1 To 1)
rest(1, 1) = Cells(1, coldest) 'titre
For i = 2 To UBound(t) - 1
  rest(i, 1) = IIf(t(i, 1) Like critere, v, defaut)
Next
Cells(1, coldest).Resize(UBound(t) - 1) = rest
End Sub
Fichier (2).

A+
 

Pièces jointes

  • classeur-exemple(2).xlsm
    26.9 KB · Affichages: 52

job75

XLDnaute Barbatruc
Re : Manipulations sur fichiers très volumineux

Re,

Une variante (2 bis) qui se contente de modifier les valeurs existantes en colonne B :

Code:
Sub Traiter()
Dim colsource$, coldest$, critere$, v, t, rest, i&
colsource = "F" 'colonne à étudier
coldest = "B" 'colonne à modifier
critere = "*.html" 'à adapter
v = 1
On Error Resume Next
ActiveSheet.ShowAllData 'si un filtre est appliqué
On Error GoTo 0
t = Cells(1, colsource).Resize(Cells(Rows.Count, colsource).End(xlUp).Row + 1)
rest = Cells(1, coldest).Resize(UBound(t))
rest(1, 1) = Cells(1, coldest) 'titre
For i = 2 To UBound(t) - 1
  If t(i, 1) Like critere Then rest(i, 1) = v
Next
Cells(1, coldest).Resize(UBound(t) - 1) = rest
End Sub
La durée d'exécution est inchangée.

A+
 

Pièces jointes

  • classeur-exemple(2 bis).xlsm
    27.4 KB · Affichages: 56

Statistiques des forums

Discussions
311 725
Messages
2 081 947
Membres
101 849
dernier inscrit
florentMIG