XL 2016 Plus rapide que BdSomme ? RechercheV ?

[Michel_ja]

Bonsoir à tous,
je me permets de rouvrir le débat sur l'utilisation de BdSomme, RechercheV, ou SommeSiEns.
J'ai une base de données plutôt longue, plus de 180000 lignes et environ 40 Colonnes dont une vingtaine sont dédiées à des chiffres par années. Des ventes de voitures par modèles, par pays... J'ai une feuille où je donne la possibilité via un TCD à l'utilisateur de choisir un modèle (de voiture) et je souhaite récupérer le total des volumes dans l'ensemble des pays et pour chaque année.
J'ai pensé à faire cela avec la fonction BdSomme. Je joins une copie d'écran. Sur la partie gauche il y a les zones de critères, E16:M17 par exemple, et sur la zone N16:AG17, il y a les volumes. Le gros soucis est que j'utilise BdSomme 3 ou 4 fois pour 49 pays et donc Excel met trop de temps à refaire les calcules lorsque l'utilisateur change de modèle (Segment TCD).
Je me demande aujourd'hui s'il serait préférable de fusionner dans la base de données les colonnes des critères et puis faire une RechercheV. Mais d'après mes lectures, ça serait encore plus long compte tenu de la longueur de la base de données. Les données ne sont pas classées donc impossible de mettre vrai dans l'argument de RechercheV.
J'ai regardé la macro VBA de Boisgontier, RechvPlus65000Lignes, mais je ne connais pas assez VBA pour remonter les volumes sur une zone horizontale plutôt que verticale. De plus, je me demande comment modifier le code pour que la macroi prenne en compte l'ensemble des lignes (3 ou 4 x 49 pays). Sans doute en ajoutant une boucle ?
J'ai testé SommeSiEns mais c'est encore plus long compte tenu du nombre de critères et de la taille de la base de données.

Est-ce qu'une bonne âme pourrez m'aider ?
Je vous remercie.
Michel

 

[tbft]

Bonjour

Avez-vous essayer les formules matricielles?

 

[Michel_ja]

Tu penses à une fonction en particulier ? J'ai cru comprendre que somme.si.ens est aussi une fonction matricielle mais compte tenu de la taille de ma base de données, plus de 180000 lignes, elle ne me ferait pas gagner du temps. Je suis entrain d'explorer du coté de multiples TCD avec des colonnes ayant des concatenations. Je pense qu'avec du VBA ça irait plus vite mais c'est compliqué sur un cas comme celui-ci.
Merci de t'intéresser au sujet.
Michel

 

[Goose]

Bjr,
joint un fichier ? plus simple a comprendre !!

 

[tbft]

Il faudrait un bout de fichier pour la structure des données.

 

[Michel_ja]

Je vous joins un extrait de la base de données, il y a le bon nombre de colonnes mais il doit y avoir 180000 lignes de plus..... ce qui rend les calculs bien plus longs. Le choix du model et programme dans la feuille Analysis se fait par TCD mais compte tenu du poids du fichier limité j'ai mis une liste.
La structure ou design de la feuille Analysis a été fait comme cela parce qu'il fallait les zones de critères pour chaque requête (exemple E16:M17) mais si on peut faire toutes les requêtes dans des lignes à la suite cela me va, du moment que je puisse faire un graph individuel pour chaque region, market et pays (49). Pour moi une requête c'est une des lignes ou j'ai actuellement un BDSomme de N17:AL17.
C'est la multiplication des BDSomme des chaque cellule et multiplier par le nombre de lignes où j'ai à faire ça qui rend les calculs un peu longs lorsque chaque utilisateur change de modele ou marque en J13 et K13 (via un TCD comme le montre doc précédent)...
Merci beaucoup.
Michel

 

[tbft]

J'essayerai de regarder ça ce soir

 

[BOISGONTIER]

Bonjour,

Il faudrait vérifier sur le fichier réel:
-que les résultats pur le premier critère sont justes
-donner le temps

Ensuite, si ok ,je le ferais pour pour tous les critères

Pour une BD de 180.000 lignes et 1 ligne de critères (ans 2008 à 2032) , j'obtiens 1 sec.
Sachant que la lecture de la BD prend 0,8 sec,il faudrait au total

0,8+0,2 * 10= 2,8 sec

Est ce mieux que les 260 BDsomme()?

Sub Essai1()
  tt = Timer
  Set f = Sheets("database")
  TBl = f.Range("A2:AN" & f.[A65000].End(xlUp).Row).Value
  tblAn = [P1:AN1].Value
  ReDim TblS(1 To 1, 1 To UBound(tblAn, 2))
  crit = [E17:M17].Value
  For i = 1 To UBound(TBl)
     If TBl(i, 1) = crit(1, 1) And TBl(i, 3) = crit(1, 2) And TBl(i, 7) = crit(1, 6) And TBl(i, 11) = crit(1, 7) Then
        For k = 1 To UBound(TblS, 2)
           TblS(1, k) = TblS(1, k) + TBl(i, 15 + k)
        Next k
     End If
  Next i
  [N17].Resize(, UBound(tblAn, 2)) = TblS
  MsgBox Timer - tt
End Sub

Boisgontier

 

[bof]

Bonjour,
Plus rapide que les BDSOMME il n'y a que les BDSOMME adossé à un peu de VBA... Surtout pas les SOMME.SI.ENS et autre joyeusetés qui ne sont que des sous produits de l'original...
Je ne suis pas certain que les adosser à des segments constitue un gain de temps : Il vaut mieux multiplier les BDSOMME quitte à en mettre 500 et des les "faire tourner" avec VBA. Même dans des bases de données comptables gigantesques c'est instantané.
Je te concède volontiers que ces fonctions de base de données ne sont pas d'un maniement aisé.
Après si on n'a pas besoin de tous les résultats simultanément les Dico font des merveilles.
Pour résumer les BDSOMME ne sont surement pas en cause mais c'est sans doute "tout le reste" qui est chronophage.
A+

 

[Michel_ja]

Bonjour,
Merci les gars, merci de vous attarder sur mon cas.
Boisgontier, j'ai testé ton code. Il fonctionne très bien sur le fichier sample que tu m'as retourné mais sur mon fichier le code se lance mais il ne m'écrit rien en retour dans les cellules des années, pas de volumes, les cellules N17:AL17 restent vides.. Sur le fichier sample, ce sont bien les bons volumes. Je me demande qu'est ce qui ne va pas. J'ai ajouté quelques commentaires à ton code, pour essayer de comprendre. Les champs sont identiques, les noms des feuilles aussi. Le temps retourné est 0,015625 (du coup je ne sais pas s'il a analysé toute ma base de données).
Le tableau que tu as ajouté en AP1:AT1 est-il utile dans le code ?
Il faut lancer le code avec la feuille analysis sinon ça me supprime des cellules en N17 de la base de donnée.

Sub Essai1()
tt = Timer
Set f = Sheets("database") 'nom du fichier où se trouvent les données
TBl = f.Range("A2:AN" & f.[A65000].End(xlUp).Row).Value 'première ligne où se trouvent les nombres jusqu'à dernière colonne de la feuille database
tblAn = [P1:AN1].Value 'colonnes et première ligne où se trouvent les années dans la feuille database
Set d = CreateObject("scripting.dictionary")
ReDim TblS(1 To 1, 1 To UBound(tblAn, 2)) '??? je ne sais pas
crit = [E17:M17].Value 'Retour dans la feuille analysis, zone des critères sélectionnés
For i = 1 To UBound(TBl)
If TBl(i, 1) = crit(1, 1) And TBl(i, 3) = crit(1, 2) And TBl(i, 7) = crit(1, 6) And TBl(i, 11) = crit(1, 7) Then
For k = 1 To UBound(TblS, 2)
TblS(1, k) = TblS(1, k) + TBl(i, 15 + k)
Next k
End If
Next i
[N17].Resize(, UBound(tblAn, 2)) = TblS 'Feuille analysis, N17 est la cellule où commence le retour de calcul
MsgBox Timer - tt
End Sub


@bof. C'est vrai que j'utilise BDSomme dans un autre fichier, en très grande quantité et ça fonctionne bien. Mais la base de donnée est beaucoup plus courte. Ici elle fait 208000 lignes.

Merci les gars.

 

[BOISGONTIER]

Bonjour,

Avec une BD de 180.000 lignes et les 260 formules BDSOMME(), le temps de recalcul est de 3 sec.
En VBA, j'obtiens le même temps.
Je te conseille de passer en mode calcul manuel et d'appuyer sur F9 pour une MAJ.

Boisgontier

 

[Michel_ja]

Bonjour Boigontier. J'ai compris pourquoi ton code plus haut ne fonctionnait pas sur mon fichier; j'ai remplacé f.[A65000] par f.[A210000] et ça marche maintenant. Aussi, j'avait lancé la macro depuis la feuille database et ça me remplaçait les données dans cette même feuille et non la feuille analyse. Avec ton code, par rapport à BDSomme, je gagne quelques millisecondes je crois. J'aurai un petit service à te demander: est-ce que ça ne t’ennuierai pas de rédiger l'ensemble du code, j'image avec une boucle, pour tester le code sur l'ensemble de mes requêtes. Ma dernière ligne est la 418ème dans la feuille analysis.
J'ai une autre question: est-ce que tu penses qu'il serait possible d'avoir ton code (ou des BDSomme) qui privilègerait les calculs des premières lignes, celles situées en haut de l'écran; l'utilisateur pourrait ainsi regarder les graphiques du haut et au fur et à mesure qu'il descend avec la souris, les autres calculs seraient prêts.
La solution du calcul manuel n'est pas envisageable car en appuyant sur F9, on repart sur environ 2 minutes pour les calculs.
Merci beaucoup pour ton aide.
Michel