application.sum et variable tableau

[Hervé]

Bonjour tout le monde

petite question à soumettre à votre sagacité.

J'ai une base de données (+ de 100 000 lignes et 27 colonnes) provenant d'un fichier txt.

je dois faire certains traitement sur cette base, des sommes avec critères, des moyennes, etc....

je stocke cette base dans une variable tableau (le célèbre tablo, que me jalouse josé et Mdf 😀) .

afin de simplement faire la somme d'une colonne précise, je me suis fait une fonction personnalisée, me transformant ce tableau multidimension en un tableau unidimension (ca se dit ca ?)

Mais je me demande si je ne pourrais pas simplifier et rendre plus rapide mon code, particulierement en évitant la boucle for to dans la function.

je suis donc preneur d'idée.

je joins un fichier exemple, en appuyant sur le bouton vous faites la somme de la colonne B (magique, non ? )

Option Explicit
Option Base 1
Dim tablo

Sub test()
tablo = Range("a1").CurrentRegion
MsgBox Application.Sum(redefinitablo(2))
End Sub

Public Function redefinitablo(colonne As Byte)
Dim tablofunction() As Double
Dim i As Long

ReDim tablofunction(1 To UBound(tablo))
    
'**********ce que je voudrais éviter :)
For i = 1 To UBound(tablo)
    tablofunction(i) = tablo(i, colonne)
Next i
'***************************************

redefinitablo = tablofunction
End Function

salut 🙂

 

[JeanMarie]

Re : application.sum et variable tableau

Bonjour Hervé

Application.Sum(tablo), fonctionne, mais prend en compte la globalité des valeurs numériques contenues dans toutes les colonnes du tablo, donc il ne faudrait dans ce tablo que les valeurs de la colonne B:B.

@+Jean-Marie

 

[Hervé]

Re : application.sum et variable tableau

bonjour jean marie🙂

oui en effet, application.sum (tablo) fonctionne, ce que je cherche à faire c'est d'eviter la boucle for to dans la function redefinitablo.

salut

 

[myDearFriend!]

Re : application.sum et variable tableau

Bonsoir Hervé, JeanMarie,

Se passer de la boucle sur ce point de départ (données stockées dans un tableau variant) me parait difficile, voir impossible.
Cela dit, je triche et je prends le problème à l'envers...

[SIZE=2]Option [COLOR=navy]Explicit

Sub[/COLOR] test()
[COLOR=navy]Dim [/COLOR]RgTablo[COLOR=navy] As [/COLOR]Range
      [COLOR=navy]Set[/COLOR] RgTablo = Range("a1").CurrentRegion
      MsgBox Application.Sum(redefinitablo(RgTablo, 2))
[COLOR=navy]End Sub[/COLOR]

[COLOR=navy]Public Function[/COLOR] redefinitablo(TabTemp[COLOR=navy] As [/COLOR]Range, C[COLOR=navy] As Byte[/COLOR])[COLOR=navy] As [/COLOR]Range
      [COLOR=navy]Set[/COLOR] redefinitablo = TabTemp.Columns(C)
[COLOR=navy]End[/COLOR] Function[/SIZE]

En stockant le tableau directement dans un objet Range on simplifie d'autant le problème et le code correspondant...

Cordialement,

 

[PascalXLD]

Re : application.sum et variable tableau

Bonjour

Tout d'abord il faut que je dise que je suivais ce fil mais que je ne trouvais rien.
Donc merci Didier pour cette solution.
Mais je me pose une question

Je suppose que dans le code d'Hervé le : tablo = Range("a1").CurrentRegion
n'était là que pour l'exemple

En effet Hervé disait dans son post:
J'ai une base de données (+ de 100 000 lignes et 27 colonnes) provenant d'un fichier txt.

Qui dit 100 000 lignes dit qu'il ne les recupère pas dans une feuille de calcul.
Alors peut-on adapter la solution de Didier au cas d'Hervé dans la récupération d'un txt et 100 000 lignes ??

Comme quoi je vais suivre ce fil avec attention car cela permettrait des gains de temsp dans certains cas si ton trouve une solution.

Bonne journée

 

[dg62]

Re : application.sum et variable tableau

Bonjour le Fil


Comme pascal je me pose la question des 100 000 lignes !


Hervé a le chic pour trouver des casse-têtes.


Bonne journée

 

[Hervé]

Re : application.sum et variable tableau

bonjour tout le monde


Merci didier pour ta proposition 🙂

Hélas, comme le dit pascal je ne peux guere utiliser cette méthode ne stockant pas les valeurs sur la feuille.

Voilà ce que je fais : toutes les semaines je recois un fichier txt complété de nouvelles données, je ne me sers d'excel que comme interface, je recupere le fichier txt fait des traitements dessus, et renvoie ces derniers dans un nouvel txt.

didier, tu ecris " Se passer de la boucle sur ce point de départ (données stockées dans un tableau variant) me parait difficile, voir impossible."

Ceci sous-entendrait'il que ce serait possible avec autre chose ?

en attendant, je vous rassure, ceci ne m'empeche pas de dormir.

salut

 

[dg62]

Re : application.sum et variable tableau

Salut Hervé


Moi, j'ai une solution : Access

 

[PascalXLD]

Re : application.sum et variable tableau

Re

Hervé tu pourrais envoyer ton code et peut-être juste un txt exemple avec des données bidons pour voir et faire des essais.

 

[JeanMarie]

Re : application.sum et variable tableau

Bonjour

Je pense que tu dois faire une boucle pour lire les données du fichier .txt et les stocker les valeurs dans ton tableau, tu pourrais faire la somme dans cette boucle.

Comme je l'avais proposé, une solution est de créer un tableau par champ, ce qui permet de faire la somme.

@+Jean-Marie

 

[Jeannot45]

Re : application.sum et variable tableau

Bonjour le Forum, les accros à ce fil,

Je rejoins la proposition de DG62 qui suggère d'utiliser Access
le nombre de lignes est donc résolu, le calcul se fait dans une requète très simplement
Pourquoi chercher à réinventer la roue quand Bill l'a déjà fait ???

A+

Jeannot

 

[Hervé]

Re : application.sum et variable tableau

re 🙂

vraiment content que vous vous impliquiez dans ma demande

en piece jointe, un fichier txt et un xls pour essai

les deux fichiers doivent etre dans le meme répertoire.

jean marie, j'ai bien peur que ta solution soit la seule à ma disposition pour le moment, a savoir créer 27 tableaux, et les englober dans un tableau général des l'importation des données.

jeannot, pourquoi pas access ?
tout simplement parce que je ne l'ai pas acheté et que je ne sais pas m'en servir.

enfin si vous avez des idées, n'hésiter pas.

salut

 

[myDearFriend!]

Re : application.sum et variable tableau

Bonsoir Hervé, JeanMarie, Pascal76, dg62, Jeannot45, le Forum,

Hervé, quand tu nous auras tout dit... 😀
Tout d'abord, pour les 100 000 lignes, j'étais prêt à parier que tu travaillais sur Excel 2007 pour ce fichier ! Ben, dis-donc, j'ai bien fait de ne pas parier... 😱

J'ai donc 3 solutions à te proposer cette fois (mais je pense quand même que les 3 ne te conviendront pas totalement) :

1. La première, c'est de charger Excel 2007 beta (en attendant la version finale) : 1 048 576 lignes disponibles. Avec tes 100 000 lignes de départ, ça te laisse une certaine marge...🙂
2. La deuxième, c'est de te mettre à Access.
3. La troisième n'est pas vraiment une solution, c'est plutôt une tentative d'optimisation du temps de traitement. J'ai testé en prolongeant ton fichier test.txt jusqu'à 100 000 lignes. En lançant la macro ci-dessous, je divise par 3 (et plus) le temps de traitement par rapport à ton code original.

[SIZE=2]Sub Test()
[COLOR=green]'myDearFriend! Aout 2006[/COLOR]
[COLOR=green]'(pour Excel 2000 minimum)[/COLOR]
[COLOR=navy]Dim [/COLOR]Fichier[COLOR=navy] As Object
Dim [/COLOR]TabTemp[COLOR=navy] As Variant
Dim [/COLOR]Chemin[COLOR=navy] As String[/COLOR], T[COLOR=navy] As String
Dim [/COLOR]Cumul[COLOR=navy] As Double
Dim [/COLOR]L[COLOR=navy] As Long
Dim [/COLOR]NbCol[COLOR=navy] As Integer[/COLOR], C[COLOR=navy] As Byte[/COLOR]
      [COLOR=green]'Colonne cible[/COLOR]
      C = 3
      Chemin = ThisWorkbook.Path & "\" & "test.txt"
      [COLOR=green]'Mémorise les données[/COLOR]
      [COLOR=navy]Set[/COLOR] Fichier = Create[COLOR=navy]Object[/COLOR]("Scripting.FileSystem[COLOR=navy]Object[/COLOR]").[COLOR=navy]Open[/COLOR]TextFile(Chemin, 1)
      T = Fichier.[COLOR=navy]Read[/COLOR]All
      [COLOR=green]'Compte le nombre de colonnes[/COLOR]
      NbCol = [COLOR=navy]UBound[/COLOR](Split(Left(T, InStr(1, T, Chr(13))), ";")) + 1
      Fichier.[COLOR=navy]Close[/COLOR]
      [COLOR=green]'Cumul des données de la 3ème colonne[/COLOR]
      T = Replace(T, Chr(13), ";")
      T = Replace(T, Chr(10), "")
      TabTemp = Split(T, ";")
      [COLOR=navy]For [/COLOR]L = C - 1[COLOR=navy] To UBound[/COLOR](TabTemp) [COLOR=navy]Step[/COLOR] NbCol
         Cumul = Cumul + Val(TabTemp(L))
      [COLOR=navy]Next[/COLOR] L
      MsgBox "Total de la colonne " & C & " = " & Cumul
[COLOR=navy]End[/COLOR] Sub[/SIZE]

Cordialement,

 

[Ti_]

Re : application.sum et variable tableau

Salut à vous
Bon ben Hervé, j'ai testé une solution sur 100 000 lignes, mais problème, WorksheetFunction.Sum (ou Product ou autre) ne veut pas fonctionner sur 100 000 lignes d'un coup, donc voilà. J'avais bien pensé à tester une solution passant par un calcul intermédiaire. C'est possible, mais malheureusement aujourd'hui je suis occupé toute la journée donc je verrai au retour si rien de nouveau n'a été proposé.
Quelles opérations veux-tu faire au juste sur tes colonnes ?

 

[Hervé]

Re : application.sum et variable tableau

bonjour à tous

content je suis par la proposition de Maitre Didier, triste par la nouvelle de Maitre Ti

ti, je n'avais pas encore testé ces codes sur l'ensemble du txt, ca moulinait tellement que ca me gavait et n'avait testé que sur un fichier secondaire d'un petit milliers de lignes.

Donc mon application.sum passait sans problème. Au regard de ta remarque, je vais laisser tomber les worksheetfunction, et m'orienter donc vers des boucles classiques, d'autant plus que la proposition de didier m'en evite certaines.

didier, tu es un magicien, je t'offre mon avatar quand tu veux.

Magique ici :

Set Fichier = CreateObject("Scripting.FileSystemObject").OpenTextFile(Chemin, 1)
      T = Fichier.ReadAll

encore là :

NbCol = UBound(Split(Left(T, InStr(1, T, Chr(13))), ";")) + 1

j'ai mis un moment à la piger cette ligne

et le clou du spectacle, ici :

 T = Replace(T, Chr(13), ";")
      T = Replace(T, Chr(10), "")
      TabTemp = Split(T, ";")

Je retiens donc ta solution.

Qu'elle optimisation, waouh, c'est beau

Merci à tous les participants de ce fil.

salut