XL 2013 Demande d'aide pour gérer un très gros fichier

[alexis mechmech]

Bonjour à tous ,
Je suis actuellement étudiant en Master écologie et ceci est mon premier post, surement pas le dernier d'ailleurs ! Je n'ai pas de gros bagage concernant Excel mais je reste curieux et pret à apprendre.

Dans le cadre d'un stage, je dois effectuer des analyses multivariés qui se basent sur des données brutes de recencement d'espèces en région Franche-comté.
Je souhaite donc avoir un petit coup de pouce étant donné que mon fichier (tiré de la base de données) contient un peu plus de 700000 lignes et presque 600 colonnes .

- La modification des cellules peut s'avérer très longue alors que le fichier ne contient aucune fonction, puis-je améliorer cela aisément (64bits ?) ?

- De plus, je souhaite créer un tableur synthétique afin de l'envoyer sur R pour des traitements stats, dans ce cadre je voudrais éliminer les lignes redondantes mais cela ne fonctionne pas du fait d'un manque de mémoire (je crois) de mon pc portable. Y a t il une solution envisageable ?

Enfin, je voudrais savoir s'il est possible de faire correspondre automatiquement toutes les données de présence d'une espèce sur une ligne. Je m'explique, je dispose de ce type de feuille :

Zone1 Zone 2 Zone 3
Espece 1 1
Espece 1 1
Espece 1 1
Espece 2 1
(où 1 correspond à une présence)

et je souhaiterais obtenir ce type de représentation
Zone1 Zone 2 Zone 3
espece 1 1 1 1
Espece 2 1


Voilà, je vous serait très reconnaissant pour une aide quelquelle soit !!!

 

[Calvus]

Bonsoir Alexis et bienvenue,

Je pense que tu pourras trouver ici toute l'aide nécessaire, mais à question floue tu auras forcément des réponses floues.

Je t'invite donc à poster un fichier exemple de ce que tu souhaites afin que l'on puisse te venir en aide.
Il n'est nul besoin de poster un fichier avec 700000 lignes et presque 600 colonnes, mais seulement quelques unes, dont il sera facile de les adapter à un gros fichier, pour peu que la structure soit identique.

A+

 

[alexis mechmech]

Merci pour ta réponse Calvus.
Voici donc un exemple tiré de ma feuille de calcul avec une seconde feuille décrivant ce que je cherche a réaliser, à condition que la mémoire du pc me le permette...

 

[cathodique]

Bonjour,

Je n'ai fait qu'adapter un code de Jacques Boisgontier ICI.

Vois si ça te va. ci-joint ton fichier en retour.

 

[alexis mechmech]

Bonjour !
tout d'abords merci à vous deux pour votre aide et le temps passé dessus, c'est super !
Je vais donc tester tout ça dès ce soir.

Il me reste donc une question, existe-t-il un moyen de fluidifier la manipulation de mon tableur ? En effet l'ordi plante régulièrement bien qu'il n'y ait pas de fonctions dans ma table, pas de fonctions en boucle etc... (j'ai fait le tour des sujets mais n'ai rien trouvé d'applicable à mon problème).
Dois-je bosser sur un autre pc ? utiliser une version plus performante qu' XL2013 ? Désactiver certains paramettres... ?

Bonne journée !

 

[alexis mechmech]

Me revoici, j'ai eu le temps de me pencher un peu dessus entre midi et 13h.

Du coup le code fonctionne parfaitement, je dois maintenant l'adapter à mon tableur, je souhaite donc avoir votre avis quant aux modifications que j'y apporte, ces dernières sont en rouge dans le texte (j'ai découvert comment ouvrir les macros il y a 10min donc soyez indulgents s'il vous plait ).
Votre avis m'est encore une fois très utile car il m'évitera de perdre un temps précieux, si je commence a tout taper et que cela ne fonctionne pas je risque de lâcher une larme. D'ailleurs est-il est possible d'automatiser l'écriture de la zone que doit prendre en compte ce macro ?
------------------------------------------------------------------
Option Explicit

Private Rcd() As Variant
Private Req As String
Private Tvide(0, 0) As Variant


Sub test()
Dim T As Variant

T = Get_List("Feuil1")
With Sheets("Feuil2")
.UsedRange.ClearContents
.Range("A1").Resize(UBound(T, 1), UBound(T, 2)) = T
.Select
End With
End Sub


' ***** LISTES ************************************************************************************
Function Get_List(Tbl As String) As Variant()

Tvide(0, 0) = ""
Req = "SELECT taxon, sum(A24) as A24_, sum(A25) as A25_, " & _
" sum(A26) as A26_, sum(AA08) as AA08_, " & _
" sum(AA09) as AA09_, sum(AA10) as AA10_, " & _
" sum(AA11) as AA11_, sum(AA12) as AA12_, " & _
" sum(AA13) as AA13_, sum(AA14) as AA14_, " & _
" sum(AA16) as AA16_, sum(AA17) as AA17_" & _
> ........................ la même chose jusqu'à
> " sum(XYZ700000) as XYZ700000_, sum(XYZ700001) as XYZ700001_" & _
" FROM [" & Tbl & "$] GROUP BY taxon"
If Query > 0 Then Get_List = Rcd Else Get_List = Tvide

End Function


Function Query(Optional Ndf As String = vbNullString) As Long
Dim Cnx As Object, Rst As Object, T As Variant
Dim Col As Integer, i As Long, j As Long

On Error GoTo errhdlr
If Ndf = vbNullString Then Ndf = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

Set Cnx = CreateObject("ADODB.Connection")
Cnx.Provider = "MSDASQL"
Cnx.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
"DBQ=" & Ndf & "; ReadOnly=False;"

If Left(Req, 6) = "SELECT" Then
Set Rst = CreateObject("ADODB.Recordset")
Rst.Open Req, Cnx, 3
Query = Rst.RecordCount
Col = Rst.Fields.Count
ReDim Rcd(Query + 1, Col)
For j = 0 To Col - 1
Rcd(0, j) = Rst.Fields(j).Name
Next j

If Not Query = 0 Then
Rst.MoveFirst
T = Rst.GetRows
For i = 0 To Query - 1
For j = 0 To Col - 1
Rcd(i + 1, j) = IIf(IsNull(T(j, i)), vbNullString, T(j, i))
Next j
Next i
End If
Else
Cnx.Execute Req
Query = 0
End If

Cnx.Close
Set Rst = Nothing
Set Cnx = Nothing
Exit Function

errhdlr:
If Not Rst Is Nothing Then If Rst.State = 1 Then Rst.Close
If Not Cnx Is Nothing Then If Cnx.State = 1 Then Cnx.Close
Set Rst = Nothing
Set Cnx = Nothing
Query = -1
Debug.Print Err.Description
End Function

 

[alexis mechmech]

Ouah super !
je vais regarder le script attentivement histoire de comprendre le fonctionnement des requetes.

Merci beaucoup a vous deux !