Lenteur calcul - SOMMEPROD
Bonjour,
Je me permet de venir vers vous car j'ai un problème de lenteur lorsque je lance les calculs manuellement. Le fichier est composé d'environ 20 feuilles avec chacune d'entres elles plus de 4500 lignes et plus de 20 colonnes. 18 feuilles de ce classeurs représentes une BDD / année sachant que sur les 20 colonnes seulement 2 font références à une fonction si basique sur 4500 lignes.
Le problème doit venir des autres feuilles qui font références aux 18 feuilles notamment avec des sommeprod selon différents critères. Je voulais savoir si il y avait une alternative à cela ? Car c'est vraiment pas agréable de travailleur sur un fichier qui mets 10 min à calculer les nouvelles valeurs quand je change un critère. Pour information mon fichier fait 8.5 Mo.
Merci de me donner vos avis sur ce problème
Bonjour,
Sans voir le fichier ça relève de la boule de cristal !
Moi je vois...
Hum... Mon avis sur les SOMMEPROD c'est que c'est un truc de feignant.
Mettre des données sur 18 feuilles c'est... une erreur de débutant !
Si tes 18 feuilles représente ta BD sur une année mets toutes tes données sur la même feuille et utilise les fonctions de base de données : Elles sont instantanées !
A+
Pour ce qui est du fichier, il est trop volumineux pour pour être posté sur le forum si jamais tu as une idée !
Effectivement j'ai 18 feuilles par années car j'ai différentes plages de noms donc c'était le plus "facile" en terme d'organisation.
Pour ce qui est des fonctions BD j'en ai pas vu me permettant de me faire la même chose que la sommeprod avec différents critères pour me sortir le nombre de valeurs.
Mais je suis ouvert à toute nouvelles propositions pour modifier mes mauvaises habitudes
bonjour
pour ta gouverne ; avec des matricielles ou apparentées ,jamais plus de 1000 lignes et, sur une feuille seulement >>>>si plus :VBA
cordialement
Bonjour,
Merci de ta réponse. Justement j'attendais de trouver une limite aux matricielles pour me lancer dans le VBA. Je crois que le moment est arrivé alors.
Effectivement le calcul manuel me permet de travailler dessus mais c'est plus un cache misère qu'autre chose finalement.
Une idée de la syntaxe VBA pour avoir l'équivalence de la fonction sommeprod ? Juste pour me donner un aperçu de la chose si possible
Utilise cjoint pour uploader le fichier et donne nous le lien.
Tu peux supprimer tout ce qui est blabla... Nom, adresse, N° de téléphone, Mail, N° de carte bleue...
A+
Une idée de la syntaxe VBA pour avoir l'équivalence de la fonction sommeprod ? Juste pour me donner un aperçu de la chose si possible
une instruction if suivi d'une addition, le tout à l'intérieur d'une boucle.
for i=première_ligne to dernière_ligne
if condition(sur ligne i) =true then total=total+valeur_a_ajouter_au_total(sur ligne i)
next iUne routine VBA n'a jamais calculé plus vite qu'une formule Excel...
La seule différence est qu'elle fixe le résultat "en dur" mais le temps de recalcul ne sera pas meilleur...
Tu peux faire une routine VBA si tes valeurs ne changent jamais (Les chiffres des ventes de l'an dernier ne changeront plus maintenant...)
donc ça soulage d'autant les autres recalculs si certaines données sont figées. Mais ce n'est pas plus efficace qu'un simple collage spécial valeur !
En revanche les fonctions de Base de données sont instantanées, même sur 100 000 lignes.
Les comptables qui ont découvert ça ont encore des cheveux ! Parce que 500 requêtes différentes BDSOMME même sur 100 000 lignes, même avec des filtres... ça leur laisse même pas le temps d'aller s'en griller une !
A+
Voici le fichier https://www.cjoint.com/c/HGmrTBFhKSh
@h2so4 Merci pour les infos et la syntaxe je vais regarder ça
@galopin01 J'ai supprimé quelques éléments dans la feuille testmatch mais le principe pour ce qui suivra est le même dans la structure en années / critères et formules
Bonsoir,
Il y a des cas où un code VBA est + rapide que des fonctions
http://boisgontierjacques.free.fr/pages_site/Dictionnaire.htm#dictionary
http://boisgontierjacques.free.fr/pages_site/recherchev.htm#FonctionRechVM
http://boisgontierjacques.free.fr/pages_site/sommeprod.htm#RemarqueMat
Ceuzin
Bonsoir,
Il y a des cas où un code VBA est + rapide que des fonctions
http://boisgontierjacques.free.fr/pages_site/Dictionnaire.htm#dictionary
http://boisgontierjacques.free.fr/pages_site/recherchev.htm#FonctionRechVM
http://boisgontierjacques.free.fr/pages_site/sommeprod.htm#RemarqueMat
Ceuzin
Quand on n'a qu'une brouette -ou qu'un âne- n'importe quel ustensile à roulette risque d'être plus rapide :
Dictionnary est un outil remarquable mais pour remplacer un sommeprod de 18 pages tu peux aller te faire voir chez ...
Bref la philosophie générale c'est pas de remplacer une mauvaise fonction par une bonne macro.
Les bonnes macros complètent et décuplent même les bonnes fonctions. De plus comme elles donnent des résultat "en dur" (sans formules) elles évitent tout un tas de recalculs qui alourdissent inutilement le classeur.
Mais même avec macro une conception foireuse reste une conception foireuse...
(Laigoh]: Malheureusement ce classeur est devenu monstrueux c'est ce qu'on appelle une "usine à gaz" incompréhensible pour le profane.
Je ne suis pas persuadé qu'on puisse en tirer quelque chose.
Déjà pour commencer il y a tellement de formules dans ce classeur qu'on ne sait pas par laquelle commencer...
Mais si je devais essayer d'en tirer quelque chose, je mettrai mes 18 feuilles dans une seule (un autre classeur) avec une colonne de plus pour l'année. Ensuite ça se discute... Je ne suis pas un fan des formules, mais à partir d'une telle base de données il y a des possibilités d'extraction et de calcul bien autre que les SOMMEPROD mon ami jmd t'en dirait surement plus à ce sujet...
Mébon ça implique de tout remettre à plat et je conçoit que ça ne t'enchante pas : C'est un sacré dilemme quand on se retrouve avec un truc monstrueux comme ça de se dire, il faut que je revois mes méthodes de travail... Entre un classeur qui rame et le travail de titan que représente une remise à plat il y a de quoi se poser des questions !
Attend un peu : Peut être que les formuleux de service auront de meilleures idées à te proposer...
Pour ma part j'avoue que je n'ai guère plus envie de me pencher sur ce bébé.
A+
Merci pour ta réponse. Je ne suis pas du tout fermé à ce genre de critiques bien au contraire car finalement ça va me permettre de faire les choses plus proprement par la suite. Si je dois tout refaire pour avoir un fichier beaucoup plus fluide, il y a aucun soucis. C’est bien pour ça que je suis venu poster ici !
D’ailleurs tu avais évoqué les fonctions BD, penses tu qu’elles sont utilisables dans ce cas présent ?
Bien que je n'ai pas du tout analysé à fond tes formules, (j'imagine cependant bien...) je n'en doute pas. Cependant il faut savoir que le revers de la médaille, c'est que si elle sont très performantes, ces fonctions demandent pas mal d'organisation et sont un peu chiantes. Il faut vraiment avoir une âme de comptable pour les utiliser !
C'est une des raisons pour lesquelles elles sont mal aimées et méconnues même par beaucoup de professionnels.
La seconde raison étant que compte tenu des progrès techniques beaucoup préfère utiliser des moyens plus intuitifs et ou il n'y a pas à se fatiguer.
Comprend bien que si billou à fait des feuilles de calcul d'un million de lignes c'est qu'il pouvait y avoir avantage à les utiliser.
Seulement ça nécessite de changer un peu ses méthode de travail :
Quand on a une base de données importante on ne s'amuse pas à bâtir une usine à gaz autour.
On fait un classeur avec juste la base de donnée et c'est tout. Et le travail d'exploitation de synthèse, de reporting se fait dans d'autres classeur au travers d'extractions : Tu le dis toi même sur les 20 colonnes certains calculs n'en sollicitent que 2...
Une piste à creuser également utiliser l'associativité et la distributivité dans les calculs. Pour toutes années antérieures les résultats ne changeront pas...
La moyenne d'une période est égale à la somme des moyennes de chaque année.
Le Top 10 (...ou 100 !) de chaque année antérieure ne changera plus : A chaque fois que c'est possible, Il peut donc s'avérer intéressant de faire la synthèse des années antérieures (et de figer les résultats de chaque année...) puis de calculer sur cette synthèse en ne recalculant que les données de la dernière année. Au lieu de faire tes calculs sur toute la bd, TAPUKA qu'a faire la synthèse de tes synthèses de chaque année. Quand c'est possible, tout ça agit considérablement sur le temps de travail au lieu de faire ta synthèse sur 100 000 lignes tu fais tes calculs sur les résultats des 20 dernières années ça change la vie !
Et puis il y a les outils modernes de reporting, TCD, et autres POWER QUERY, POWER PIVOT qui facilitent bien la vie.
Enfin YA les macros... Alors oui dans ces conditions elles permettent de bien se simplifier la vie. En modifiant juste quelques paramètres il est très facile de faire des synthèse annuelles, mensuelles hebdomadaires, par critère, items filtrés ou pas...
Après c'est juste un problème de compétence... Un gars qui sait programmer dans une douzaine de langages évolués tirera surement plus de VBA que quelqu'un qui toute sa vie n'a utilisé que des RECHERCHEV, SOMMEPROD, INDEX, EQUIV...
Tout le monde ne pilote pas VBA en virtuose et l'apprentissage peut s'avérer tellment long que beaucoup dissuadent même de s'y lancer...
TOUSSA est une affaire de neurones !
Bon je dis pas que TOUSSA est toujours possible partout. Ça demande une analyse très fine de chaque problème au fur et à mesure.
J'ai moi même bossé pendant des années sur des données d'athlétisme. C'est un vrai casse-tête avec de nombreuses catégories, et même les fédérations doivent faire appel à des équipes de développeurs pour gérer ce bazar.
Alors pour tout dire Excel est pas la Rolls des moyens de gestion de ce genre de résultat. Donc on fait des compromis entre moyens financiers, compétences et motivations pour essayer de trouver la meilleure combinaison. Dans ton cas je n'ai malheureusement pas de solution miracle : Ça dépend un peu aussi de ton âge (et celui de tes neurones...) De ta situation de famille donc du temps que tu consacres à ton ordinateur, entre quelques heures par semaines et 18 heures par jours ça fait une petite différence !
Vala... Tu sais tout de ma réflexion sur ce sujet !
A+
D'accord donc dans un premier temps je vais revoir toute ma bd. Je suis d'accord sur le fait de pouvoir faire la synthèse de tout les résultats des années antérieurs car désormais tout est figé. Cependant pour avoir le détail des résultats pour chaque joueur, cela voudrait dire que je dois intégrer toutes les valeurs figés dans ma bd on est d'accord ? J'aurais donc un bd conséquente mais beaucoup moins lourde car plus de calculs sur les 17 dernières années.
Pour ce qui est du VBA, je vais m'y pencher sérieusement car c'est quelque chose qui m'intéresse fortement et j'ai du temps à y consacrer. Pour répondre à ta question j'ai 22 ans donc je pense avoir encore des neurones disponibles pour ce genre d'apprentissage
Après je suis bien conscient de la charge de travail qui m'attend si je veux tout refaire pour avoir quelque chose de propre mais je pense que c'est nécessaire pour moi et il est encore temps de gommer mes mauvaises habitudes de "bâtisseur d'usine à gaz" !
Je gratte un petit peu dessus, mais c'est souvent difficile de comprendre la logique qui sous-tend certaines constructions...
Comment borner la BD ? S'arrêter à la colonne AB pour l'année en cours, AG pour les antérieurs ?
Garder AH et AI pour des requêtes spécifiques ?
Pour l'instant je n'ai pas poussé plus loin. Je veux déjà monter ma BD sur cette base là... Et ensuite je verrai chaque problème au pas à pas... Si ça me gonfle pas trop... Parce que c'est vrai que dans l'ensemble c'est pas ma tasse de thé : La seule chose qui m'intéresse réellement c'est au niveau conception des requêtes.
Les Formules en SI(poupées russes), RECHERCHEV et autre INDEX EQUIV, ne sont pas transposable en BD** (formules de base de données) Il faut donc toutes les éplucher pour voir si on les garde ou si on peut trouver mieux en moins gourmand...
Les autres (celles transposables) en BD** doivent elle être utilisées tel quel en mode exclusif ou peut-on les combiner en synergie avec un peu de macros ? TOUSSA doit être épluché soigneusement au cas par cas...
Après il y a aussi les requêtes que je maîtrise mal, TCD, SQL, POWER trucs... Celles là je les aime pas trop parce qu'elles produisent des résultats en vrac. Après il faut se débrouiller avec le tas d'infos pour les rentrer dans ton moule spécifique...
Cependant ça ne veut pas dire qu'il faut les oublier... Ça m'arrive aussi de temps en temps d'y recourir.
Bref instinctivement moi c'est ce que je tenterai. Monter une grande BD c'est pas compliqué au pire tu vas perdre 2 heures... Après c'est comment on fait pour reconstituer le puzzle. Mais au stade ou tu en es tu risques rien à essayer !
Le grand malade à risque très élevé d'accident majeur est toujours réceptif à des propositions de chirurgie lourde...
A+
Déjà un truc qui serait peut-être pas bête :
Tu devrais enregistrer ton fichier actuel
Enregister sous... classeur excel binaire (.xlsb) !
Je pense que tu devrais y gagner un petit peu... Au niveau poids ça devrait être env 50%
Mais ça n'influe sans doute pas sur le recalcul.
C'est quoi toutes ces formes vides dans la colonne B de ta BD Player ?
A+
Bon j'ai fini ma BD...
Je te livre une première réflexion :
Dans les annuelles (et surtout dans la BD)
Il y a avantage à modifier la formule [AH2] en :
=ET(K2=Joueur1;L2=Joueur2)
et celle de [AI] en...
=ET(K2=Joueur2;L2=Joueur1)
et ensuite à figer cette formule en VBA... (avec une évènementielle)
C'est un tout petit gain qui ne modifie en rien le résultat de "Test match" mais de petits gain en petits gains... Les petits ruisseaux... !
Bon je vais m'attaquer maintenant à un truc plus sérieux...
Heu... C'est quoi cette formule à la noix : ([D40] de Test Match)
=_xlfn.MINIFS('2018'!$L:$M;'2018'!$J:$K;$B$3)
Tu peux me déniaiser ?
J'essaie de te préparer un substitut de SOMMEPROD convaincant mais à ma sauce et je te montre le bébé. Mais j'ai encore pas mal de travail de restructuration à faire.
A+
Bonjour,
@ galopin01,
Cette fonction n'est disponible pour le moment qu'avec Office 365.
(du type SOMME.SI.ENS)
Cdlt.