Alléger calcul matriciel

dieu08 · 3 Décembre 2008

[SOMMEPROD] - alléger calcul matriciel

Bonjour,

Grâce à ce forum, j'ai pu mettre en place différentes formules de calculs matriciels sur un tableau de synthèse. Je me suis vite aperçu que ces calculs étaient longs et monopolisaient de la ressource.

Je faisais avec jusque là mais je suis sur un fichier dont la formule de base est :
{=SOMMEPROD((Brutes!$M$2:$M$61007=$AL14)*(Brutes!$E$2:$E$61007=E$1)*(Brutes!$A$2:$A$61007=$A$2)*(Brutes!$K$2:$K$61007))}

Je n'arrive pas au bout, c'est trop long (40 minutes pour 10 cellules).

L'onglet source (62000 lignes) est dans le même fichier. Je ne peux malheureusement pas mettre un fichier exemple car il y a des données nominatives.

La question arrive enfin :

- comment alléger mon système de calcul ? Par un cube OLAP ? Par des requêtes SQL à la place des calculs matriciels ?

(sachant que je n'y connais rien à la création d'un cube via excel)

Je vous remercie déjà de l'aide que vous pourrez m'apporter. 😉

Dieu08

dieu08 · 9 Décembre 2008

Re : Alléger calcul matriciel

Re,

Malheureusement, j'ai effectivement 61830 lignes dans mon fichier. Mais il est toujours possible que je le scinde en plusieurs fichiers.

Et oui malheureusement (encore), il arrive assez souvent qu'il y ait des "index bénéficiaire" en double dans mes données.

Dieu08

Gael · 9 Décembre 2008

Re : Alléger calcul matriciel

Re,

Ci-joint une nouvelle version.

J'ai créé une requête de comptage (feuille RQT, en H1:H2).

Pour toutes les valeurs positives des montants, la macro va modifier puis exécuter la requête de comptage.

En début de macro, la requête principale est mise à jour.

Les requêtes sont lancées avec le chemin et le nom du fichier, donc rien à modifier dans les macros.

A priori, ça marche, reste à voir les temps de réponse sur un gros fichier et à vérifier si le comptage est correct.

@+

Gael

dieu08 · 10 Décembre 2008

Re : Alléger calcul matriciel

C'est un super boulot que tu as fais là Gael ! C'est bien au-delà de ce que j'aurais pu faire seul.

Au lancement de la macro3, j'ai un blocage sur le "With Selection.QueryTable"

Sub Macro3()
'
' Macro3 Macro
' Macro enregistrée le 09/12/2008 par Gael
'

'
Range("A2").Select
Sheets("Feuil1").Select
Range("A2").Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=Excel Files;DBQ=C:\GD\excel\forum\Dieu08_V1.xls;DefaultDir=C:\GD\excel\forum;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = Array( _
"Select count (*) from (SELECT DISTINCT `Brutes$`.`Index Beneficiaire`" & Chr(13) & "" & Chr(10) & "FROM `C:\GD\excel\forum\Dieu08_V1`.`Brutes$` `Brutes$`" & Chr(13) & "" & Chr(10) _
& "WHERE (`Brutes$`.`Unite Territoriale/AGIR`='A')" _
& " AND (`Brutes$`.Concat1=", "'Allocation compensatrice tierce personne')" _
& " AND (`Brutes$`.`Mois Traitement Dep#`=1))" _
)
.Refresh BackgroundQuery:=False
End With
End Sub

Une anomalie de commande ?

Dieu08

Gael · 10 Décembre 2008

Re : Alléger calcul matriciel

Bonjour Dieu08, bonjour à tous,

Le module 2 m'a uniquement servi à faire des enregistrements de macros pour récupérer la syntaxe, il ne sert plus à rien, tu peux le supprimer.

Utilise seulement la macro cumul du module 1.

@+

Gael

dieu08 · 10 Décembre 2008

Re : Alléger calcul matriciel

Je m'en vais tester tout cela ! 🙂

dieu08 · 12 Décembre 2008

Re : Alléger calcul matriciel

Bonsoir,

J'ai eu pas mal de boulot ces derniers jours; je n'ai pas eu le temps d'approfondir mes tests. Mais je te tiens au courant Gael !

Dieu08

dieu08 · 17 Décembre 2008

Re : Alléger calcul matriciel

Je t'assure que je ne le fais pas exprès; j'ai juste survolé ta réalisation.

Ça semble à première vue correspondre à ce que j'attends.

Gael · 18 Décembre 2008

Re : Alléger calcul matriciel

Bonsoir Dieu08,

Je reste quand même assez réservé pour le temps de réponse vu le nombre de lignes à traiter.

@+

Gael

dieu08 · 22 Décembre 2008

Re : Alléger calcul matriciel

Bonsoir Gaël,

Me revoilà dans le jeu si j'ose dire.

J'ai tenté d'aménager ta requête pour le nombre de valeurs en VB; ce qui donne :

Sub Macro2()
'
' Macro2 Macro
' Macro enregistrée le 22/12/2008
'

'
Worksheets("Feuil1").Select
Range("H1").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Fichiers Excel;DBQ=C:\GD\excel\forum\Dieu08_V3.xls;DefaultDir=C:\GD\excel\forum;DriverId=790;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")), Destination:=Range("H1"))
.CommandText = Array( _
"Select DISTINCT count(`Brutes$`.`Index Beneficiaire`)" & Chr(13) & "" & Chr(10) _
& "FROM `" & ThisWorkbook.FullName & "`.`Brutes$` `Brutes$`" & Chr(13) & "" & Chr(10) _
& "WHERE (`Brutes$`.`Unite Territoriale/AGIR`='A')" & Chr(13) & "" & Chr(10) _
& "AND (`Brutes$`.Concat1='Accueil familial personne handicapée')" _
)
.Name = "RQ2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Néanmoins dès que je veux ajouter une condition de type en avant dernière ligne:

& "AND (`Brutes$`.`Mois Traitement Dep#`='1') " & Chr(13) & "" & Chr(10) _

J'ai un message d'erreur "Incompatibilité de type". J'ai un peu cherché sur le net évidemment, et je pensais que c'était une histoire de format de colonne, mais je n'arrive pas à trouver comment passer cette obstacle.

Peux-tu à nouveau m'apporter ton aide ?

Merci.

Dieu08

Gael · 22 Décembre 2008

Re : Alléger calcul matriciel

Bonsoir Dieu08, bonsoir à tous,

Essaye en rajoutant une "," comme ci-dessous. Je ne sais pas pourquoi, mais à partir d'une certaine taille de texte, il faut couper avec une ",".

Mets aussi 1 au lieu de `1`.

et supprime les & Chr(13) & "" & Chr(10) inutiles

ça doit donner:

Code:

Sub Macro2()
'
' Macro2 Macro
' Macro enregistrée le 22/12/2008
'
 
'
Worksheets("Feuil1").Select
Range("H1").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=Fichiers Excel;DBQ=C:\GD\excel\forum\Dieu08_V3.xls;DefaultD ir=C:\GD\excel\forum;DriverId=790;MaxBufferSize=20 48;PageTimeout" _
), Array("=5;")), Destination:=Range("H1"))
.CommandText = Array( _
"Select DISTINCT count(`Brutes$`.`Index Beneficiaire`)" & Chr(13) & "" & Chr(10) _
& "FROM `" & ThisWorkbook.FullName & "`.`Brutes$` `Brutes$`" & Chr(13) & "" & Chr(10) _
& "WHERE (`Brutes$`.`Unite Territoriale/AGIR`='A')"  _
& "AND (`Brutes$`.Concat1='Accueil familial" , "personne handicapée')" _
& "AND (`Brutes$`.`Mois Traitement Dep#`=1)" 
)
.Name = "RQ2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

@+

Gael

dieu08 · 23 Décembre 2008

Re : Alléger calcul matriciel

Bonjour,

Gaël, j'ai utilisé tes conseils pour faire fonctionner la macro ci-dessous. Mais je peux dire que la syntaxe SQL via VB est d'une rare complexité; je trouve que c'est vraiment très aléatoire en fonction du type de données.

J'ai rarement galéré autant pour faire fonctionner une macro :

Sub Macro2()
'
' Macro2 Macro
' Macro enregistrée le 22/12/2008 par FV
'

'

Sheets("Feuil1").Activate
Compteur1 = Application.CountA(Range("A1:A65536"))

For i = 2 To Compteur1

'Fonction InStr indique la position d un espace à partir du premier caractere à gauche
'Fonction Len indique le nombre de caractere = nbcar
Var1 = Left(Cells(i, 2), (InStr(1, Cells(i, 2), " ")) - 1)
Var2 = Right(Cells(i, 2), ((Len(Cells(i, 2)) - InStr(1, Cells(i, 2), " "))))
Var3 = Left(Cells(i, 3), (InStr(1, Cells(i, 3), " ")) - 1)
Var4 = Right(Cells(i, 3), ((Len(Cells(i, 3)) - InStr(1, Cells(i, 3), " "))))
Var5 = Cells(i, 1)

Worksheets("Feuil1").Select
Range("H" & i).Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Fichiers Excel;DBQ=C:\GD\excel\forum\Dieu08_V3.xls;DefaultDir=C:\GD\excel\forum;DriverId=790;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")), Destination:=Range("H" & i))
.CommandText = Array( _
"Select DISTINCT count(`Brutes$`.`Index Beneficiaire`)" & Chr(13) & "" & Chr(10) _
& "FROM `" & ThisWorkbook.FullName & "`.`Brutes$` `Brutes$`" & Chr(13) & "" & Chr(10) _
& "WHERE (`Brutes$`.`Unite Territoriale/AGIR`='" & Var5 & "')" _
& "AND (`Brutes$`.`Type Demande`='" & Var1 & " " & "", "" & Var2 & "')" _
& "AND (`Brutes$`.`Nature Demande`='" & Var3 & " " & "", "" & Var4 & "')" _
& "AND (`Brutes$`.`Mois Traitement Dep#`='1')" _
)
.Name = "RQ2"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Next

End Sub

Dès que le tout sera finalisé, je t'enverrai le résultat final pour que tu me dises ce que tu en penses (si tu es d'accord évidemment).

Dieu08

Gael · 23 Décembre 2008

Re : Alléger calcul matriciel

Bonjour Dieu08,

Je suis tout à fait d'accord avec toi, c'est extrêmement sensible et les messagse d'erreur ne sont d'aucune aide.

Généralement j'exécute ou je modifie la requête en auto enregistrement et je réorganise progressivement les données en faisant régulièrement des essais avec F8 pour revenir facilement en arrière si message d'erreur.

Pour le fichier finalisé, pas de soucis, je regarderai.

@+

Gael

dieu08 · 4 Janvier 2009

Re : Alléger calcul matriciel

Bonjour Gaël,

Je suis sur ton fichier depuis plusieurs jours et je n'arrive malheureusement pas à le faire "tourner". Dès que je veux rafraichir une requête, le système me dit que le moteur "microsoft jet" n'a pu trouvé la base de données....

Je ne comprends pas ce qui se passe; comme si les requêtes avaient disparues de mon fichier.

Dieu08

En fait dans le programme, il y a :

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Fichiers Excel;DBQ=" & ThisWorkbook.FullName & ";DefaultDir=" & ThisWorkbook.Path & ";DriverId=790;MaxBufferSize=2048;PageTimeout" _

Et sur le PC sur lequel je travaille il fallait mettre :

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";DefaultDir=" & ThisWorkbook.Path & ";DriverId=790;MaxBufferSize=2048;PageTimeout" _

C'est du délire total ! Je continue mes tests (sur les 60.000 lignes) !

Dieu08

Alléger calcul matriciel

dieu08

XLDnaute Occasionnel

dieu08

XLDnaute Occasionnel

Gael

XLDnaute Barbatruc

Pièces jointes

dieu08

XLDnaute Occasionnel

Gael

XLDnaute Barbatruc

dieu08

XLDnaute Occasionnel

dieu08

XLDnaute Occasionnel

dieu08

XLDnaute Occasionnel

Gael

XLDnaute Barbatruc

dieu08

XLDnaute Occasionnel

Gael

XLDnaute Barbatruc

dieu08

XLDnaute Occasionnel

Gael

XLDnaute Barbatruc

dieu08

XLDnaute Occasionnel

Discussions similaires

Alléger calcul matriciel

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

Discussions similaires

Privacy & Transparency

Privacy & Transparency