Microsoft 365 Transformer un sommeprod en code vba

jean_jack · 5 Août 2019

Bonjour tout le monde, je viens vous solliciter une nouvelle fois pour un soucis sur excel . Alors je m'expliques j'utilise actuellement une formule excel dans un tableaux pour mettre à jour des données et afficher des données qui sont stockés dans une base de données en fonction de la date entrée : SOMMEPROD((BASEPROD!$C$2:$C$50000=$C5)*(MOIS(BASEPROD!$A$2:$A$50000)=MOIS($V$1))*(ANNEE(BASEPROD!$A$2:$A$50000)=ANNEE($V$1))*(BASEPROD!$D$2:$D$50000)) voici la formule en question que je répète dans un certain nombre de cellules . La formule marche super bien le seule soucis c'est que vu que ma base de données est assez longue et est amenée à s’agrandir la formule qui tourne en boucle dans plusieurs cellules ralentit considérablement mon classeur. Je n'arrive plus à l'utiliser pendant deux min quand je veux faire une action. J'aimerai donc si possible remplacer la formule par un code vba pour rendre cela plus fluide.

Dans le fichier excel c'est la formule qui se trouve dans les colonnes E et F de la feuille 'RAPPORT' . Et l'idée c'est d'afficher les données en fonction de la date dans V1.

Si quelqu'un à une idée de comment faire ça serait génial.

Merci d'avance

jean_jack · 6 Août 2019

CHALET53 à dit:
Tu as combien d'enregistrement dans les feuilles concernées

J'en ai 5 à 20 selon la feuille concernée , mais en faite sa ralentit surtout ma saisie de données dans mon formulaire" ajouter une production" dès que j'essaie d'ajouter une production ça met une plomb parfois ça ne va mème plusjusqu'au bout de la procédure d'ajout. Alors que jusque la ça marchait superbien .

Hasco · 6 Août 2019

Bonjour,

J'ai fait une proposition à la question de la suppression dans l'autre fil de ce monsieur, qui semble se disperser

Bonne suite

jean_jack · 6 Août 2019

Roblochon à dit:
Bonjour,

J'ai fait une proposition à la question de la suppression dans l'autre fil de ce monsieur, qui semble se disperser

Bonne suite

Merci pour vos réponse a tous les deux j'ai obtenu réponse à ce que je voulais grace à avec vous deux.
Bonne continuation à tout les deux.

Hasco · 6 Août 2019

Re,

Perso j'aurais bien voulu voir le fichier original (du post"1) de cette discussion sur sommeprod, mais visiblement vous l'avez enlevé, ce qui n'est pas sympa pour les futurs demandeurs.

bon appétit

jean_jack · 6 Août 2019

Roblochon à dit:
Re,

Perso j'aurais bien voulu voir le fichier original (du post"1) de cette discussion sur sommeprod, mais visiblement vous l'avez enlevé, ce qui n'est pas sympa pour les futurs demandeurs.

bon apétit

Ah oui c'est vrai j'y avais pas pensé je le remet ici du coup .

CHALET53 · 6 Août 2019

Pour répondre au post #16
Le problème ne peut être lié au programme de suppression qui est indépendant des programmes d'ajoût de production
Par contre, il y a un programme événementiel ajouté à la feuille RAPPORT pour la mettre à jour si changement de date en V1
A chaque intervention dans cette feuille, le pgm événementiel se lance et si la cellule V1 n'est pas concerné il sort de la procédure
Les programmes associés à l'ajout de production interviennent à plusieurs reprises sur la feuille Rapport ce qui déclenche la procédure événementielle
Pour l'éviter, il faut ajouter au début des programmes liés lié au Userform de mise à jour l'instruction : Application.enableEvents=false
et rajouter à la fin du programme : Application.enableEvents=true
Il faut le réactiver car il ne serait plus actif en cas de changement de la date en V1
Suis-je Clair ?

jean_jack · 6 Août 2019

CHALET53 à dit:
Pour répondre au post #16
Le problème ne peut être lié au programme de suppression qui est indépendant des programmes d'ajoût de production
Par contre, il y a un programme événementiel ajouté à la feuille RAPPORT pour la mettre à jour si changement de date en V1
A chaque intervention dans cette feuille, le pgm événementiel se lance et si la cellule V1 n'est pas concerné il sort de la procédure
Les programmes associés à l'ajout de production interviennent à plusieurs reprises sur la feuille Rapport ce qui déclenche la procédure événementielle
Pour l'éviter, il faut ajouter au début des programmes liés lié au Userform de mise à jour l'instruction : Application.enableEvents=false
et rajouter à la fin du programme : Application.enableEvents=true
Il faut le réactiver car il ne serait plus actif en cas de changement de la date en V1
Suis-je Clair ?

Oui j'ai compris encore merci pour votre réponse. Le code marche super-bien.

CHALET53 · 6 Août 2019

Le petit bouton Réinitialiser Evénementiel que je t'avais proposé répond au cas suivant :
Si un programme contenant l'instruction Application.enableEvents=false plantait donc avant la fin (où normalement on réactive l'instruction), il resterait dans cet état et si un autre programme (notamment Prodédure événementielle) a besoin de cette instruction à la position True, la procédure ne serait pas activée

jean_jack · 6 Août 2019

CHALET53 à dit:
Le petit bouton Réinitialiser Evénementiel que je t'avais proposé répond au cas suivant :
Si un programme contenant l'instruction Application.enableEvents=false plantait donc avant la fin (où normalement on réactive l'instruction), il resterait dans cet état et si un autre programme (notamment Prodédure événementielle) a besoin de cette instruction à la position True, la procédure ne serait pas activée

Oui j'ai bien compris sont utilité je l'ai donc gardé dans la feuille rapport et je l'ai appelé mis à jours. Comme ça en cas de bug on peu rafraîchir la feuille. Merci beaucoup.

Hasco · 6 Août 2019

Re bonjour,

Du coup j'ai pu transformer vos sommeprod en Somme.Si.Ens (plus rapide) après avoir modifié le Tableau de 'BASEPROD' réduit aux lignes occupée (2) et l'avoir nommé 'T_BaseProd'. Ce qui donne pour le tonnage du mois:
=SOMME.SI.ENS(T_BaseProd[TONNAGE];T_BaseProd[NOM];$C5;T_BaseProd[DATE];">" & FIN.MOIS($V$1;-1);T_BaseProd[DATE];"<=" & FIN.MOIS($V$1;0))

A mon avis si vous maintenez correctement vos tableaux, ces formules ne devraient pas être moins rapides que le vba.

Cordialement

CHALET53 · 6 Août 2019

Chapeau : je ne maîtrise pas les formules à ce niveau de complexité
Bravo (c'est sûrement une excellente approche pour éviter le VBA)
Je me retire sur la pointe des pieds

Hasco · 6 Août 2019

Re,

Il me vient une idée que vous pourriez exploiter et qui semble convenir à votre projet puisque vous avez office365 vous pourriez utiliser powerquery,
faire des requêtes sur vos tableaux qui sont bien structurés et en tirer toutes les informations souhaitées.
Plus besoin de vba ou de somme.si.ens. juste cliquer sur un bouton 'rafraîchir' pour la mise à jour.

Mais pour vous en faire la démonstration il nous faudrait des données exemples (une centaines de lignes par tableau, à des dates différentes) .

D'ailleurs si vos données viennent d'une base de données externe, il se pourrait bien que powerquery puisse aller les chercher et le mettre à jour directement.

A vous relire

[Edit] bonjour @CHALET53

jean_jack · 7 Août 2019

Roblochon à dit:
Re bonjour,

Du coup j'ai pu transformer vos sommeprod en Somme.Si.Ens (plus rapide) après avoir modifié le Tableau de 'BASEPROD' réduit aux lignes occupée (2) et l'avoir nommé 'T_BaseProd'. Ce qui donne pour le tonnage du mois:
=SOMME.SI.ENS(T_BaseProd[TONNAGE];T_BaseProd[NOM];$C5;T_BaseProd[DATE];">" & FIN.MOIS($V$1;-1);T_BaseProd[DATE];"<=" & FIN.MOIS($V$1;0))

A mon avis si vous maintenez correctement vos tableaux, ces formules ne devraient pas être moins rapides que le vba.

Cordialement

Merci pour cette nouvelle approche. Je la garde pour une modification future.

jean_jack · 7 Août 2019

Roblochon à dit:
Re,

Il me vient une idée que vous pourriez exploiter et qui semble convenir à votre projet puisque vous avez office365 vous pourriez utiliser powerquery,
faire des requêtes sur vos tableaux qui sont bien structurés et en tirer toutes les informations souhaitées.
Plus besoin de vba ou de somme.si.ens. juste cliquer sur un bouton 'rafraîchir' pour la mise à jour.

Mais pour vous en faire la démonstration il nous faudrait des données exemples (une centaines de lignes par tableau, à des dates différentes) .

D'ailleurs si vos données viennent d'une base de données externe, il se pourrait bien que powerquery puisse aller les chercher et le mettre à jour directement.

A vous relire

[Edit] bonjour @CHALET53

Bonjour merci pour cette suggestion, je vais remplir les tableaux avec un certain nombre de valeur et vous le envoyer pour une demonstration comme vous le proposez, si cela peut m'affranchir de vba ou de formules ça serait pas mal.

Je vous le renvoie dans la matinée. A vous relire.

jean_jack · 8 Août 2019

CHALET53 à dit:
Chapeau : je ne maîtrise pas les formules à ce niveau de complexité
Bravo (c'est sûrement une excellente approche pour éviter le VBA)
Je me retire sur la pointe des pieds

Bonjour ,
Mais non ta solution marche super bien d’ailleurs je la garde puisque ça m'évite que l'utilisateurs modifie mon classeur en modifiant une formule par accident. J'ai une nouvelle question à vous poser à moins que tu ne penses qu'il faut que je crée un nouveau post la dessus., mais j'aimerais réaliser un graphique sur 12 mois glissant qui se mettrait à jour automatiquement sur les données tonnage qui se situent dans la feuille BASEPROD. Est ce que tu penses qu'il faudrait que je stock d'abord mes données par mois dans un tableaux qui se mettrait à jour automatiquement ou je peux directement indexer mon tableaux sur la base elle mème ? dans les deux cas si tu vois comment faire ton aide serait la bienvenue.

Merci d'avance pour ta réponse et bonne journée.

Microsoft 365 Transformer un sommeprod en code vba

jean_jack

XLDnaute Junior

jean_jack

XLDnaute Junior

Hasco

XLDnaute Barbatruc

jean_jack

XLDnaute Junior

Hasco

XLDnaute Barbatruc

jean_jack

XLDnaute Junior

Pièces jointes

CHALET53

XLDnaute Barbatruc

jean_jack

XLDnaute Junior

CHALET53

XLDnaute Barbatruc

jean_jack

XLDnaute Junior

Hasco

XLDnaute Barbatruc

Pièces jointes

CHALET53

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

jean_jack

XLDnaute Junior

jean_jack

XLDnaute Junior

jean_jack

XLDnaute Junior

Discussions similaires