Alléger calcul matriciel

[dieu08]

[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

 

[Gael]

Re : Alléger calcul matriciel

Rebonjour à tous,

Détail des opérations:

Pour la requête, tu fais "données - créer une requête" puis tu choisis "Excel files" et le fichier en cours.

Tu ouvres la feuille "Brutes" comme table et tu sélectionnes uniquement les champs nécessaires:
Code unité territoriale
Concat 1 (pour avoir le libellé de l'opération)
Année
Mois
Montant prestation

Tu doubles-cliques sur "Montant prestation", choisis la fonction "Somme" puis "OK" et tu cliques sur "!" pour exécuter la requête qui donnera en SQL:

XLODBC
1
DSN=Excel Files;DBQ=C:\GD\excel\forum\Test-anonyme.xls;DefaultDir=C:\GD\excel\forum;DriverId=790;MaxBufferSize=2048;PageTimeout=5;
SELECT `Brutes$`.`Unite Territoriale/AGIR`, `Brutes$`.Concat1, `Brutes$`.`Annee Traitement Dep#`, `Brutes$`.`Mois Traitement Dep#`, Sum(`Brutes$`.`Montant Prestation Dep#`) AS 'Somme sur Montant Prestation Dep#'  FROM `C:\GD\excel\forum\Test-anonyme`.`Brutes$` `Brutes$`  GROUP BY `Brutes$`.`Unite Territoriale/AGIR`, `Brutes$`.Concat1, `Brutes$`.`Annee Traitement Dep#`, `Brutes$`.`Mois Traitement Dep#`
 
 
Unite Territoriale/AGIR Concat1     Annee Traitement Dep#   Mois Traitement Dep#    Somme sur Montant Prestation Dep#

J'ai commenté la macro:

Sub cumul()
Dim tablib(35) As String    'Tableau contenant les libellés
Dim TabA(12, 35) As Single  'Tableau pour totaliser les prestations
Dim TabB(12, 35) As Single  'Un par unité territoriale
Dim TabC(12, 35) As Single
Dim TabD(12, 35) As Single
Dim tablo As Variant        'Tableau pour récupérer les valeurs de la requête
Dim i As Long, j As Integer
' Chargement des libellés des cellules B1 à AK1
' dans le tableau des libellés
For i = 2 To 36 'i=2 pour commencer en colonne B
    tablib(i - 2) = Worksheets("Valeur").Cells(1, i).Value '(i-2) car le 1er index est 0
Next i
'Tablo va contenit toute les données renvoyées par la requête
tablo = Worksheets("RQT").Range("A2").CurrentRegion
For i = 2 To UBound(tablo) 'Pour chaque ligne de la requête à partir de 2
'on recherche le libellé dans la table lib
    For j = 0 To 34
        If tablo(i, 2) = tablib(j) Then Exit For 'la valeur de J représente l'index du libellé
    Next j
'tablo(i, 1) contient le code unité teritoriale
'Selon sa valeur on va totaliser les résultats
'dans les tables TABA à TABD
Select Case tablo(i, 1)
Case "A"
'tablo(i, 4) contient le n° de mois et on fait -1 pour le 1er index à 0
'==> 0 =Janvier, 1=février...
'et j est la colonne du libellé
TabA(tablo(i, 4) - 1, j) = TabA(tablo(i, 4), j) + tablo(i, 5) 'on ajoute la valeur de la prestation
Case "B"
TabB(tablo(i, 4) - 1, j) = TabB(tablo(i, 4), j) + tablo(i, 5)
Case "C"
TabC(tablo(i, 4) - 1, j) = TabC(tablo(i, 4), j) + tablo(i, 5)
Case "D"
TabD(tablo(i, 4) - 1, j) = TabD(tablo(i, 4), j) + tablo(i, 5)
Case Else
End Select
Next i
'Une fois tous les tableaux remplis, il ne reste
'qu'à les coller dans la feuille "Valeur"
With Worksheets("Valeur")
    .Range("B3:AJ14") = TabA    'TABA à partir de B3
    .Range("B17:AJ28") = TabB   'TABB à partir de B17
    .Range("B31:AJ42") = TabC   'etc...
    .Range("B45:AJ56") = TabD
End With
'...Et c'est terminé.
End Sub

Je te joint le fichier contenant la requête. Tu peux l'ouvrir avec le bloc notes, modifier le chemin d'accès pour l'adapter à ton environnement et lorsque tu es dans MSQuery, tu fais "fichier - fermer" puis "fichier - Ouvrir" et tu ouvres le fichier de requête puis tu fais "Fichier - Renvoyer les données vers Microsoft Excel".

@+

Gael

Finalement, je ne peux pas joindre le fichier dont l'extension n'est pas valide. Tu ouvres le bloc notes et tu colles le texte ci-dessus, puis tu sauvegardes le fichier et le renommes en ".dqy".

 

[jeanpierre]

Re : Alléger calcul matriciel

Bonjour à tous,

Note à Gaël : pour joindre un fichier, prendre l'habitude de le zipper.

Ainsi et quelque soit le format il n'y a plus de problème. Un compresseur intégré existe sous Windows depuis la version 2000.

Bon après-midi.

Jean-Pierre

 

[dieu08]

Re : Alléger calcul matriciel

Merci beaucoup.

Je vais étudier cela dès ce soir. J'adore Excel, c'est cérébralement "boostant" si je puis m'exprimer ainsi bien sûr !

J'espère tout comprendre; autrement je saurais à qui m'adresser 😀

Dieu08

 

[dieu08]

Re : Alléger calcul matriciel

Question idiote mais la partie requête dans MsQuery peut-elle être automatisée elle aussi dans une macro ?

Merci.

(que faire le samedi soir à 20:45...)

 

[Gael]

Re : Alléger calcul matriciel

Bonjour à tous,

Ce n'est pas une question idiote. La requête peut être crée par macro (tu peux créer ta requête avec l'enregistreur de macro pour voir le résultat).

Une autre solution consiste à faire seulement l'actualisation dans la macro, c'est généralement ce que j'utilise.

@+

Gaël

PS: Salut Jean-Pierre, c'est vrai que j'aurais pu zipper le fichier en .dqy pour l'envoyer mais comme le contenu était déjà dans le message, un copier-coller donne le même résultat.

Gael

 

[dieu08]

Re : Alléger calcul matriciel

Bonjour,

Je tâtonne petit à petit pour comprendre les principes avec la requête MSQUERY; par contre je m'interroge car je ne parviens pas à faire un SELECT COUNT(DISTINCT Brutes.Index_Beneficiaire)....

Aurais-tu une idée Gael ?

 

[Gael]

Re : Alléger calcul matriciel

Bonjour Dieu08,

essaye la syntaxe suivante:

Select count (*) as Nb_index from (SELECT DISTINCT (`Brutes$`.`Index Beneficiaire`)
FROM `C:\GD\excel\forum\Dieu08_V1`.`Brutes$` `Brutes$`)

en changeant bien sûr le chemin d'accès "C:\GD\excel\forum\"

@+

Gael

 

[dieu08]

Re : Alléger calcul matriciel

Uhm...ca fonctionne...néanmoins 😱😱

Je voudrais pouvoir intégrer d'autres champs à cette requête du genre :

APA pour lieu A 13 (ici nombre d'enregistrements distincts)
APA pour lieu B 26 (ici nombre d'enregistrements distincts)

Afin de pouvoir les réintégrer dans mon onglet Excel et me servir de ta macro.

Dieu08

 

[Gael]

Re : Alléger calcul matriciel

Re,

Je ne comprends pas bien ta dernière demande APA (allocation personnes agées?) pour lieu A13 😕

Peux-tu préciser?

@+

Gael

 

[dieu08]

Re : Alléger calcul matriciel

Re,

Je ne comprends pas bien ta dernière demande APA (allocation personnes agées?) pour lieu A13 😕

Peux-tu préciser?

@+

Gael

Ce que je voulais dire pour l'exemple qui nous occupe, c'est que je voudrais bien pouvoir avoir :

Champ "Concat1" Champ "Unité territoriale" et le nombre d'enregistrements distincts en face ...

J'espère que c'est plus clair ainsi ...

🙄

 

[Gael]

Re : Alléger calcul matriciel

Re,

On peut le faire en plusieurs requêtes mais dans une seule, je ne vois pas.

Mais pourquoi ne pas le faire en formules ou intégrer des compteurs dans la macro?

@+

Gael

 

[dieu08]

Re : Alléger calcul matriciel

Re,

On peut le faire en plusieurs requêtes mais dans une seule, je ne vois pas.

Mais pourquoi ne pas le faire en formules ou intégrer des compteurs dans la macro?

@+

Gael

Je ne sais quoi te dire...

J'essaie de simplifier mon système au maximum. Dans ton exemple, tu m'avais fourni la solution pour faire des sommes par l'intermédiaire de MSQUERY.

Mais au niveau de mes tableau de bord, j'ai un onglet dédié à des montants et un autre au volume des individus distincts.

 

[Gael]

Re : Alléger calcul matriciel

Re,

Peux-tu joindre un exemple avec l'onglet par volume d'individus ?

@+

Gael

 

[dieu08]

Re : Alléger calcul matriciel

Re,

Peux-tu joindre un exemple avec l'onglet par volume d'individus ?

@+

Gael

Je prépare cela demain matin ... merci pour ta disponibilité !

Voilà le fichier ... j'ai ajouté un onglet ! J'espère que tu t'y retrouveras !

 

[Gael]

Re : Alléger calcul matriciel

Re,

Ci-joint une version avec montants et valeurs mais sans tenir compte du champ "index bénéficiaire" s'il est présent x fois. Donc pas de "Distinct", la requête compte simplement le nombre de montants par unité, prestation, année et mois.

J'ai donc éliminé la difficulté 😀

2 questions:

As-tu réellement comme dans ta formule une feuille contenant 61830 lignes ou est-ce un maximum pour être sûr de tout avoir?

Y-a-t-il beaucoup d'index bénéficiaires en double?

@+

Gael