plantage sur fichier de 150 mo

[pascal665]

bonjour,

je cherche une solution pour travailler sur un fichier de 55 000 lignes 200 colonnes sur feuille principale
et 30 feuilles ou j'insère des données qui change chaque jour
sur ma feuille principale en fait je fais un regroupement sur les données des différentes feuilles

malheureusement il plante sans arrêt ma bécane n'est pas en cause car je viens de l'acheter et c'est une bête de course (+ de 4000 €)
j'ai passer plusieurs heures avec le support mac mais il ne savent pas tout fonctionne bien, le problème est excel ou du moins des données qui ne fonctionnent pas très bien entre elle.

J'utilise essentiellement les formules "recherchev estna et si" pour la majorité des formules

je recherche donc une personne qui est capable de me donner une solution pour que cela arrête de planter

merci beaucoup a ceux qui me repondront

 

[MJ13]

Re : plantage sur fichier de 150 mo

Bonjour Pascal

Je ne sais pas si un SGBD ne serait pas plus utile pour ton problème.

Sinon, commence par supprimer ces formules et travaille avec du VBA.

 

[pascal665]

Re : plantage sur fichier de 150 mo

bonjour,
oui je suis en train, mais Noël pour moi est un moment très important et il ne sera en fonction qu'au mois de février.
et je suis un nul en VBA
je n'arrive même pas a copier ma page principale il plante et replante sans arrêt je suis dans une grosse ..... si je ne trouve pas une solution au moins pour me dépanner
pascal

 

[Staple1600]

Bonsoir à tous

pascal665
Inutile de proposer un "petit job" 😉 en "sautant" sur les petits nouveaux qui viennent se présenter à nous 🙄

On va t'aider bénévolement. C'est à cela que sert un forum, non ?

Commence par nous montrer un extrait de ton fichier allégé et anonymisé (zippes ton classeur et joins le à ta discussion)
(une dizaine de lignes suffiront, mais laisse toutes les formules que tu utilises

PS: je plains ton Excel qui doit souffrir avec un fichier de 150 Mo 😱

 

[pascal665]

Re : plantage sur fichier de 150 mo

Merci a toi,

je suis complètement perdu car j'ai un boulot hors norme a faire et je n'y arrive plus du tout, encore mille merci si tu peux me donner un coup de main
par contre je viens de passer 30 min pour réduire le fichier
j'ai donc supprimer des feuilles mais la base est la même sur les feuilles encore présente
(enlever feuille 1 3 5 6 9 12 13 14 15 17 18 20 21 22 24 26 27 28 29 30 )
Si tu veux des précisions je suis la jusqu'a au moins 2 ou 3 h du mat
merci encore

 

[Staple1600]

Re : plantage sur fichier de 150 mo

Re

Au vue de ton fichier , pas étonnant qu'Excel soit à bout de souffle.

Comme je suppose que tu n'as pas une grande liberté d'action, je ne te proposerai pas la piste de stocker tes données dans un fichier texte et d'utiliser la fonctionnalité Données Externes (voir ici de quoi je parle)

Donc dans un premier temps, pour remplacer tes formules, utilises l'enregistreur de macros
Ce qui donne ceci par exemple

Sub Macro1Engregistreur()
Range("T3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],LISTING!C[-18]:C[-17],2,0)"
Range("T3").Select
End Sub

Mais vu le nombre de formules dans ton classuer, cela implique un boulot monstre, car il te faudra d'abord transcrire toutes tes formules en VAB grâce à l'enregistreur puis voir ce que cela donne en suivant la logique de la macro ci-dessous.

Sub Macro1Modif()
Dim derlig&
derlig = Cells(Rows.Count, "T").End(xlUp).Row
With Range("T3:T" & derlig)
.FormulaR1C1 = "=VLOOKUP(RC[-15],LISTING!C[-18]:C[-17],2,0)"
.Value = .Value
End With
End Sub

PS: Tes formules RECHERCHEV pointent sur l'intégralité des colonnes, tu aurais tout intérêt à utiliser des zones nommées dynamiques* pour borner tes plages de recherches aux cellules non vides.
(*: Faire une recherche sur le net ou sur le forum à ce sujet)

 

[pascal665]

Re : plantage sur fichier de 150 mo

merci pour ta réponse mais comme je disais plus haut je suis incompétent en VBA, je recherche désespérément quelqu'un qui pourra m'aider a réaliser ce fichier en attendant mon nouveau logiciel qui est en cours de création mais pas dispo avant février.

comme tu peux voir la création de ce fichier représente plusieurs mois de travail car depuis il a évolué mais la je pense que je suis a bout de mes capacités, pour les macro sincèrement je ne comprends pas tout
je suis blasé en fait, car autant de temps passé pour bloquer quand j'en ai le plus besoin c'est trop nul

tu comprends mieux pourquoi j'ai sauté sur le petit nouveau qui cherche un ptit boulot
j'ai passé des annonces sur leboncoin et vivastreet, même sur pole emploi je ne trouve personne, a croire que personne ne veut gagner de l'argent

pour les données externes pas possible car j'ai des erreurs et je ne peux le mettre sous csv. car il me déforme toutes mes colonnes. (erreur tabulateurs et saut de ligne je pense) mais vus la grosseur du fichier c'est chaud a corriger.

question : je dois faire une macro pour chaque cellule , SI OUI impossible a faire car dans la page listing il y a 11 000 000 de cellules.
il n'existe pas une autre solution plus simple ,
cordialement
pascal

 

[Staple1600]

Re : plantage sur fichier de 150 mo

Re

Non il ne s'agit pas de traiter chaque cellule mais plutôt les colonnes "formulées' en une seule passe.
C'est ce qu'illustre la macro ci-dessous
(je te l'ai commentée)

Sub Macro1Modif()
'Déclaration de la variable derlig
Dim derlig&
'derlig= le numéro de ligne de la dernière cellule non vide
'ici dans la colonne T
derlig = Cells(Rows.Count, "T").End(xlUp).Row
With Range("T3:T" & derlig)
'donc dans les cellules non vides de la colonne T
'on inscrit la formule RECHERCHEV en une seule fois
.FormulaR1C1 = "=VLOOKUP(RC[-15],LISTING!C[-18]:C[-17],2,0)"
'puis on transforme la formule en valeur
.Value = .Value
End With
End Sub

C'est ce principe qu'il faudrait utiliser sur l'ensemble des colonnes avec formules.
(mais je ne suis pas sur que cela rendra ton classeur immédiatement "réactif")

 

[pascal665]

Re : plantage sur fichier de 150 mo

re
tu fais référence a la colonne T c'est sur la page 7 que tu prends l'exemple (car c'est la seul qui a la colonne t EN DERNIER)

en fait les feuilles de 1 a 30 sont des données que je change chaque jours, exemple sur feuille 2
- les colonnes de A à R sont des données variables que je colle chaque jours il y en a de 800 a 11 000 lignes selon les feuilles
- les colonnes S à Y se sont des recherches de correspondance des lignes de cette feuille 2 qui sont déjà enregistrer sur la base "listing"
- les colonnes AC à AH sont des données final je je met a jours sur site après "nettoyage" en format txt

- sur feuille listing 55 000 lignes sur 200 colonnes
Données fixes de A à V ensuite sur 5 colonnes je recherche les infos sur les différentes feuilles (ref, prix, quantité, o ou 1 selon donné présente ou pas sur les colonne "prix et quantité)

- idem pour les 150 colonnes suivantes (soit les 30 feuilles )

- colonne FW nombres des feuilles possédant cet article

- colonne FX prix moyen des mag ayant cet articles (si pas de mag "erreur" donc on prend la colonne u

- colonne FY PRIX FINAL

- fz QUANTITÉ TOTAL PRODUITS DISPO

- GA contrôle prix (différence entre fy ET u)

voila pour les explications du fichier

Cordialement
Pascal

 

[Staple1600]

Re : plantage sur fichier de 150 mo

Re

J'ai pensé à cette façon de faire avec cette macro
Lister tes formules
Tu te rends sur une feuille avec des formules
Tu lances la macro
Ce qui te crée une feuille Formules
que tu renommes ensuite en FormNOMDELAFEUILLETRAITEE
Et tu recommences pour chaque feuille de ton classeur
(On pourrait envisager par la suite une macro qui fasse le boulot toute seule, mais là je vais au dodo)
Ensuite quand tu as toutes formules listées, il faudrait créer un macro sur le principe de celle de mon précédent message en remplaçant la référence de la colonne traitée et la formule à insérer puis à transformer en valeur.
Mais même en suivant cette piste, ce sera chronophage et surtout si tu te lances sur cette voie fais le sur une copie de ton classeur pas sur l'original.

Sub test()
Dim f As Worksheet, p As Range, c As Range, i&
Set f = ActiveSheet
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Formules").Delete
Sheets.Add(after:=Worksheets(Sheets.Count)).Name = "Formules"
Set p = f.UsedRange.SpecialCells(xlCellTypeFormulas)
i = 1
For Each c In p
Sheets("Formules").Cells(Rows.Count, "A").End(xlUp)(2) = c.Address(0, 0) & "-> " & c.FormulaLocal
i = i + 1
Next c
Sheets("Formules").[A1] = "Liste des formules dans " & f.Name
MsgBox i & " formules trouvée(s) dans " & f.Name
End Sub

 

[Staple1600]

Re : plantage sur fichier de 150 mo

Re

Non mon exemple (et ce n'était qu'un exemple pour te montrer le principe) c'était sur la feuille 2

Sinon si tu as besoin d'ouvrir une copie de ton fichier sans qu'il plante
Testes la macro ci-dessous SUR UNE COPIE (PAS SUR L'ORIGINAL)

Sub Valseules()
Dim s As Worksheet, c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    For Each s In Worksheets
    On Error Resume Next
        For Each c In s.UsedRange.SpecialCells(xlCellTypeFormulas)
        c.Value = c.Value
        Next c
    Next s
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Toutes les formules seront remplacées par leurs valeurs, ce qui devrait limiter le plantage et te permettre de consulter les données de ton fichier.

 

[Staple1600]

Re : plantage sur fichier de 150 mo

Re

Je me logout .
J'espère que d'autres membres du forum pourront venir à ta rescousse.
Mais sache qu'il normal qu'un fichier Excel de 150 MO avec une foultitude de formules plante.

Donc je ne vois que le VBA ou comme conseillé précédemment CSV/Données externes/ADO pour te tirer de ce guêpier.
(ou utiliser Excel et Access)

 

[pascal665]

Re : plantage sur fichier de 150 mo

Merci pour ton aide
bonne nuit
pascal

 

[Staple1600]

Re : plantage sur fichier de 150 mo

Re

Tu as essayé la macro Valseules sur une copie de ton fichier pour l'ouvrir sans qu'il plante?
Cela a-t-il fonctionné?
(voir dans le message#11)
Cela sera déjà çà de gagné 😉
Allez cette fois-ci c'est la bonne.
Gute Nacht.

 

[pascal665]

Re : plantage sur fichier de 150 mo

J'AI ESSAYÉ MAIS IL ME MARQUE UNE ERREUR DE SYNTAXE
LES LIGNES CI DESSOUS SONT EN ROUGE

For Each s In Worksheets
* * On Error Resume Next
* * * * For Each c In s.UsedRange.SpecialCells(xlCellTypeFormulas)
* * * * c.Value = c.Value
* * * * Next c
* * Next s