Formules / VBA ? Optimisation de recherches (plages dynamiques)

Int0x

XLDnaute Occasionnel
Bonjour le forum,

Tout d'abord, merci à tous ceux qui vont prendre un peu de temps pour réfléchir à mon problème (surtout que je débute en VBA).

Je suis en train de créer un fichier sur les coûts de revient de différents articles.
L'idée générale de ce fichier:
* chaque article a un nombre d'opération pour être fabriqué (opé-1, opé-2, etc...) avec des paramètres propres à chaque opération (machine, temps, nombre d'opérateur, etc...).
* chaque opération (qui est donc liée à une machine) va alimenter l'onglet de la machine concernée (un onglet par machine, limité à 2 dans le fichier exemple).
* ainsi, le budget associé à chaque machine se réparti en fonction du temps d'occupation de la machine

Voilà, en (très) gros l'idée.

Un exemple est plus parlant:
J'ai un article 1, qui est fabriqué à l'aide de 2 opérations:
- opé 1: il passe sur la machine 1, pour un temps de 0.15 s par pièce
- opé 2: il passe sur la machine 2, pour un temps de 0.12 s par pièce

Quand je met à jour l'opération 1 (bouton MAJ Opé-1), il va automatiquement alimenter l'onglet 001 (lié à la machine 1). De même pour l'opération 2.

Je fais pareil pour tous les articles, qui vont s'incrémenter dans chaque onglet. Les budgets par machines se répartissent en fonction du temps de mobilisation, et un prix unitaire est calculé.


Mon problème:
Le fichier que j'ai réalisé fonctionne bien pour un petit nombre de ligne, comme les 47 articles de l'exemple.
Le souci, c'est que j'ai de nombreuses références (allez, disons 2500) et que du coup mes "mises à jour" sont très longues. Cela vient du fait qu'en colonne S, AA, etc...j'utilise la formule:

=RECHERCHEV(CONCATENER(A4;$L$2);INDIRECT(CONCATENER("'";DROITE(RECHERCHEV(L4;Settings!B:D;2;0);3);"'!M:O"));2;0)

Et que ce calcul, qui s'actualise sur 2500 lignes x 10 colonnes, bah forcément ca bouffe de la ressource.
Auriez-vous une idée d'optimisation ?
Comment pourrais-je obtenir les résultats de Prix / Unité (colonnes S, AA, etc...) de manière assez rapide ?

J'ai l'impression que l'aspect "alimentation des onglets" est correct, mais que je suis limité pour aller chercher le résultat (car il s'agit de recherches "dynamiques")

Par avance merci pour vos idées,
Int0x
 

Pièces jointes

  • Revient - Exemple.xls
    308 KB · Affichages: 74

Int0x

XLDnaute Occasionnel
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Bonjour Dranreb,

Dans mon idée, oui, chaque feuille (001, 002, etc...) doit être le reflet de BdD.
* BdD synthéthise l'ensemble de la production,
* les onglets 001, 002, etc...décrivent l'ensemble des machines.

A terme, il sera important de pouvoir voir qu'en supprimant un article (à budget constant), le prix unitaire des autres articles augmente (et inversement si on charge la machine).

Auriez-vous eu une idée dans le cas contraire ?
Je dois reconnaitre que toute idée est bonne à prendre :)
 

Dranreb

XLDnaute Barbatruc
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Pour l'instant je n'ai pas encore d'idée bien claire. J'essaye simplement de me pénétrer de votre classeur.
Simplement il me semblait à 1ère vue que les boutons "MAJ Opé-n" ne traitent que la cellule active, ce qui me parait loin de garantir l'objet de cette 1ère question qui m'est venue. J'envisage vaguement une MàJ en bloc de l'ensemble des feuilles 001 etc. travaillant à l'aide de tableaux de Variant pour plus de rapidité. J'y vois aussi une application possible d'un module de classe permettant des classements en mémoire.
Après tout s'il n'y avait qu'un groupe de colonne dont une en plus indiquant l'Opé, le tout étant classé par nom de poste ou numéro de machine et par CODE, les feuille 001 etc pourraient peut être se remplir l'une après l'autre de haut en bas. Enfin, en gros.

Et puis la formule pénalisante, il faudrait peut être la virer et la remplacer par un simple renvoi à la bonne cellule de la bonne feuille (formule calculée et mise en place lors de cette mise à jour globale, bien sur)

Que pensez vous de cette approche ?
À +
 
Dernière édition:

Int0x

XLDnaute Occasionnel
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Effectivement, le bouton "Maj opé-" sert à mettre à jour l'opération de la cellule active. J'avais initialement créé une boucle For, mais ca aurait mis des années à se mettre à jour.

Autrement, les onglets reflètent bien la réalité mais c'est peut-être cette conception qui fait que la procédure est trop longue.

Exemple:
Sur l'article 1 de l'onglet BdD
- je remplis la partie gauche de l'opération (Ligne / Tps cycle / [...] / Opérateurs)
- je mets à jour cette ligne
- l'article 1 va directement aller sur l'onglet de la machine associée (ex: Machine 1)

Tous les prix unitaires des autres articles présents sur l'onglet Machine 1 (001) seront alors modifiés.
Et ces modifications seront bien visibles sur l'onglet BdD.

Cette partie semble bien fonctionner.

Pour votre approche, je suis curieux de voir ce que ca pourrait donner ! L'idée me convient, même si mes connaissances limitées en VBA ne me permettent pas d'apprécier pleinement cette proposition (disons que je sais pas comment ça fonctionnerait et ce qu'on y gagnerait :eek: )

L'objectif reste que l'utilisateur de ce fichir puisse mettre à jour ses valeurs assez rapidement.

En tout cas, merci pour votre temps passé sur ce sujet :)
Int0x
 

Dranreb

XLDnaute Barbatruc
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

disons que je sais pas comment ça fonctionnerait et ce qu'on y gagnerait
Ça consisterait dans un premier temps à empiler les 10 groupes de 8 colonnes dans un tableau unique en mémoire, en y ajoutant 3 colonnes pour le CODE, l'Article et l'Opé, puis à indexer ce tableau par machine et par code.
Ensuite une exploration séquentielle de cette table avec rupture sur la Machine irait remplir un tableau de sortie qui serait déchargé d'un seul coup, en une seule instruction d'affectation de Value, dans chaque feuille.
On y gagnerait à ce point en performance qu'on pourrait presque déclencher l'opération à chaque modification dans BdD. Mais je n'y crois pas trop quand même. Il vaudrait mieux la programmer toutes les 10 secondes (ou par un bouton aussi) et avant sauvegarde du classeur.
Mais j'ai quand même encore un peu de mal avec les quantités, temps et coûts. Tout est bien puisé de BdD ? Ou y a-t-il hélas quelque chose d'ajouté manuellement dans les feuilles machine ? Pourriez vous me trouver quelque chose, un schéma relationnel, synoptique ou je ne sais quoi, qui m'aiderait à me fixer les idées sur ces calculs ? Je n'ai compris que globalement le principe des pourcentages et de la répartition de budget, mais je m'y perd complètement dans le détail.

P.S. Ou si vous préférez, je vous écris juste le squelette du traitement global, et vous complétez vous même le code en certains endroits pour que tout soit bien calculé à sa place. Je mettrai des commentaires à chaque étape et derrières certaines instructions…
À +
 
Dernière édition:

Int0x

XLDnaute Occasionnel
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Effectivement, l'idée me convient !
Par contre, je confirme également que mes connaissances sont limitées pour arriver à un tel résultat :(

En revanche, je peux essayer d'être plus clair pour le fonctionnement du fichier.

Dans les onglets machines (001, 002, etc...), rien n'est ajouté manuellement et tout provient d'autres onglets.
Pour mon exemple, j'ai remplis les budgets à la main (C2 + C3) mais ils seront liés à un autre onglet à terme (Pilotage).

Le fichier fonctionne en 3 parties, en 3 "thèmes" de réflexion:

1- Onglet BdD:
J'alimente les infos manuellement sur cet onglet (Code / Désignation / Quantité). Ensuite, je rentre pour chaque opération, la machine utilisée, les temps de cycle chronométrés, les temps de réglages, etc...A la fin de chaque opération, il y'a une case "Prix / unité" qui va chercher le cout de l'article dans l'onglet de la machine concerné
> Gros boulot d'alimentation manuelle de base de données (données que j'ai déjà).

2- La mise à jour:
La mise à jour doit envoyer dans l'onglet associé à la machine (Ligne) les informations suivantes: code, désignation, quantité (ces 3 informations sont communes à toutes les opérations), puis selon l'opération, le temps total et le nombre d'opérateurs.
> Il ne s'agit "que" d'un code VBA ici (mais c'est ce qui me pose problème :) )

3- Les onglets machine:
Cet onglet va recevoir les infos citées au dessus. Avec un exemple, vous devriez comprendre:

Exemple:
Partons avec l'onglet 001, pour la machine 001 (admettons qu'il soit vide d'information à partir de la ligne 12).
Je prévois un budget de 10 000 € pour cette machine.

Imaginons que la mise à jour envoie:
- 5000 articles 1
- temps unitaire: 15s (on a donc un temps total de 5 000 x 15 = 75 000s (colonne F))
L'onglet calcule donc que cet article occupe 75 000s de la machine, soit 100% du temps (colonne G) (normal, l'article est tout seul).

Il calcule donc un cout de revient de 10 000 / 5 000 = 2 € / pièces

Imaginons que je rajoute un nouveau produit à cette machine, sans changer le budget:
- 3500 pièces 2
- temps unitaire: 10s (soit 35 000s)

L'onglet a donc 2 articles:
- Article 1, pour 75 000s
- Article 2, pour 35 000s

Ayant 10 000 € à répartir, je fais juste un rapport proportionnel:
Les articles 1 vont prendre 75 000 / (75000 + 35000) x 10 000 € (colonne H)
Les articles 2 vont prendre 35 000 / (75000 + 35000) x 10 000 € (colonne H)

On raisonne de la même façon pour le budget Ouv (colonnes I / J / K).
Et c'est peut-être sur ce % que ca bloque ! Je prends le temps global que je multiplie par le nombre d'opérateurs prévu. Je divise ensuite ce résultat par le temps total (sommeprod).

La colonne L est la somme des 2 répartitions de budget.
En N, on divise le résultat de L par le nombre de pièces pour avoir le coût unitaire.

On a également ajouté un % d'activité (si la machine tourne très peu, pour ne pas imputer tous les coûts sur les articles).

Est-ce un peu plus clair ou ai-je répondu à coté (c'est ce que je suis en train de me dire, mais j'me vois pas tout effacer :p ) ?
Les onglets machines sont "autonomes" et ne demandent aucune intervention manuelle.

Edit: Votre PS me convient ! Encore merci pour votre aide !
 
Dernière édition:

Dranreb

XLDnaute Barbatruc
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Est-ce un peu plus clair
Heu… de toute façon j'ai aussi progressé un peu de mon coté à la lecture des formules, dont je n'aurais d'ailleurs peut être même pas à m'occuper. Je vais me lancer dans la programmation, maintenant. Juste un détail encore: ça m'arrangerait bougrement que les colonnes I (Opérateurs) soient ramenées 3 colonnes devant, en F (derrière Temps / unité) pour avoir ensemble toutes les constantes à mettre à jour, et toutes les formules après. C'est permis ?
À +
 

Dranreb

XLDnaute Barbatruc
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Voici un premier jet.
Mais il reste des détails à régler: des articles sont en doubles, faut il additionner quelque chose ? et si oui qu'est-ce qu'on renvoie dans chaque colonne "Prix / Unité" ?
Il me reste à ajouter des commentaires dans le code pour vous aider à comprendre ce qui est important quitte à reporter à plus tard la partie high-tech de cette programmation: l'indexation.
La "001" déborde du cadre des lignes garnies de formules. Si c'était pour moi je définirais au niveau de chaque feuille une plage de lignes nommée "Tablo", dont la taille s'ajusterait par copie de lignes existantes voisines ou suppression de celles en trop.

P.S. Fichier re-joint avec commentaires du code et un bogue réparé.
Reste à fixer une politique pour les tailles de tableaux dans les feuilles machines.
Il y a pour l'instant une incohérence: les formule du tableau sont installées jusqu'à la ligne 65 (inutilement ou insuffisamment), mais des SOMME en ligne 11 vont jusqu'à la 2019 (inutilement).
Oh, je vais m'attaquer à un ajustement automatique de tout ça.
À +
 

Pièces jointes

  • RépartitionCoûtsProduction.xls
    344 KB · Affichages: 71
  • RépartitionCoûtsProduction.xls
    344 KB · Affichages: 80
  • RépartitionCoûtsProduction.xls
    344 KB · Affichages: 127
Dernière édition:

Int0x

XLDnaute Occasionnel
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Voici un premier jet.
Mais il reste des détails à régler: des articles sont en doubles, faut il additionner quelque chose ? et si oui qu'est-ce qu'on renvoie dans chaque colonne "Prix / Unité" ?
Il me reste à ajouter des commentaires dans le code pour vous aider à comprendre ce qui est important quitte à reporter à plus tard la partie high-tech de cette programmation: l'indexation.
La "001" déborde du cadre des lignes garnies de formules. Si c'était pour moi je définirais au niveau de chaque feuille une plage de lignes nommée "Tablo", dont la taille s'ajusterait par copie de lignes existantes voisines ou suppression de celles en trop.

P.S. Fichier re-joint avec commentaires du code et un bogue réparé.
Reste à fixer une politique pour les tailles de tableaux dans les feuilles machines.
Il y a pour l'instant une incohérence: les formule du tableau sont installées jusqu'à la ligne 65 (inutilement ou insuffisamment), mais des SOMME en ligne 11 vont jusqu'à la 2019 (inutilement).
Oh, je vais m'attaquer à un ajustement automatique de tout ça.
À +

Bonjour Dranreb,

Première impression: "Mon dieu, c'est énorme !!"
Vraiment, super boulot ! La fluidité et rapidité d'exécution est parfaite. Je suis scotché...j'espère pouvoir arriver un jour à un tel niveau. Le code est vraiment trop évolué pour mes connaissances actuelles, c'est pourquoi je vous remercie pour tous les commentaires qui y sont associés. Je vais essayer de m'imprégner de tout ça :)

Pour répondre à vos questions, et dans l'espoir que vous ayez encore l'amabilité de peaufiner tout ça:

1- Pour les articles en double :
- ne peuvent pas figurer sur le même onglet en double l'article ET le numéro d'opé (par exemple, je ne peux pas avoir 2 fois "Article 1 / Opé 1"
- en revanche, je peux avoir 2 fois l'article si ce n'est pas les mêmes numéros d'opération

Donc il ne doit pas y'avoir de doublon si on part du principe qu'un article est identique s'il a le même code ET le même numéro d'article.

Par exemple, un article 1 peut suivre un processus:
- Opération 1: machine 1
- Opération 2: machine 2
- Opération 3: machine 3
- Opération 4: machine 1

Sur l'onglet machine 1 (001), on aurait donc:
- Article 1 - opé-1
- Article 1 - opé-4

Ainsi, chaque "Prix/Unité" est unique (il dépend de l'article ET de l'opération).
- pour la partie "Opération 1", je renvoie le Prix/Uni de l'article 1 ET opé-1 de l'onglet 001
- pour la partie "Opération 4", je renvoie le Prix/Uni de l'article 1 ET opé-4 de l'onglet 001

2- En ce qui concerne les dimensions des onglets Machines (001, 002, etc...), elles ont été créés de manières totalement arbitraire. S'il est possible de les adapter en fonction du nombre de données (si j'ai bien compris votre idée de "Tablo"), ca serait absolument génial. Je n'ai malheureusement pas les compétences

3- Pour info, la colonne M des onglets machines (Nom) ne me sert à rien. Elle ne me gène pas, mais si elle vous ennuie, il est possible de la supprimer (elle me servait pour l'ancienne méthode de recherche des Prix/Unit).

En tout cas mille merci pour le temps passé, c'est vraiment génial.
J'ai hate de voir votre automatisation (tablo) !

Je vais me mettre sur le code pour essayer de progresser un peu ;)
@bientôt,
Int0x
 
Dernière édition:

Dranreb

XLDnaute Barbatruc
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Bonjour.
Mettre des noms aux plages est une bonne habitude pour rendre les macros indépendantes des adresses de cellules qu'elles traitent.
Mais n'étant pas sûr que vous étiez d'accord sur le principe, j'avais en attendant adapté l'application d'un ajustement à une plage délimitée par la dernière cellule renseignée des colonnes G (la 1ère des formules).
Si vous décidiez de nommer "Tablo" les lignes entières couvrant les tableaux, il suffirait de remplacer en Fin machine:
VB:
Set PlgRés = FMch.[A12].Resize(FMch.[G11].End(xlDown).Row - 11, 6) ' Plage machine actuelle
par:
VB:
Set PlgRés = FMch.[Tablo].Resize(, 6) ' Plage machine actuelle

C'est du moins la modif minimale. Mais ça irait beaucoup plus loin si on voulait bénéficier de la plage nommée pour pouvoir changer librement la taille en lignes des parties entête: non seulement la référence à la ligne 13 devrait être remplacée par une référence à la ligne 2 de Tablo, mais il faudrait localiser la plage Tablo plus haut dans le code, en Début machine, car ça toucherait aussi les numéros de lignes de renvois vers les colonnes O pour les "Prix / unité" de FBdD.

J'oubliais: vous pouvez supprimer les colonnes M (Nom) qui ne servent plus à rien. Vous pouvez faire ce que vous voulez dans les formules au delà des colonnes F. Aie! Non ce n'est pas tout à fait vrai: ça change aussi le renvoi, instruction:
VB:
FBdD.Cells(L + 3, C + 7).Formula = "='" & NomFMch & "'!$N$" & Ls + 11
Pour une totale liberté, il faudrait des noms aux colonnes aussi.

Cordialement.
 

Pièces jointes

  • RépartitionCoûtsProduction.xls
    312 KB · Affichages: 63
  • RépartitionCoûtsProduction.xls
    312 KB · Affichages: 68
  • RépartitionCoûtsProduction.xls
    312 KB · Affichages: 61
Dernière édition:

Int0x

XLDnaute Occasionnel
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Merci Dranreb, c'est top ;)

Une question: parfois, quand je change une machine, j'ai un #ref (sur un autre article) qui apparait lors de la mise à jour (cf pj). Par contre, il disparait si je refais une mise à jour !

(par exemple, si je change tous les derniers articles par des "machine 2" et que je mets à jour, je vais avoir plusieurs #REF en haut (sur les 1ers articles) et je n'aurais aucun prix sur certains articles que je viens de modifier...par contre, si je reclique une 2ème fois, tout rendre dans l'ordre)

Auriez-vous une idée ?
(après plusieurs essais, je suis obligé de cliquer 2 fois pour que tout se calcule bien)

En tout cas, un énorme merci :)
 

Pièces jointes

  • RépartitionCoûtsProduction.xls
    307 KB · Affichages: 62
  • RépartitionCoûtsProduction.xls
    307 KB · Affichages: 63
  • RépartitionCoûtsProduction.xls
    307 KB · Affichages: 64

Dranreb

XLDnaute Barbatruc
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

En effet, c'est très ennuyeux… je n'avais pas pensé à ça… on ne peut pas toujours penser absolument à tout.
C'est un bug sérieux: je mets en place les formules avant de supprimer ou ajouter des lignes, ce qui change les dites formules !
Je ne peux pas non plus faire l'ajustement avant car je ne connais qu'à la fin mon Ls de ligne de fin.
Pas d'autre solution que d'inscrire autre chose ou ailleurs et corriger après l'ajustement.
Je regarde ça.

Bon. Ça ne me plait qu'à moitié (ou aux 3/4 quand même). Je mets des 'µ" à la place des "=" dans les formules pour qu'elle ne soient plus vues comme telles, et je corrige en bloc à la fin.
À +
 

Pièces jointes

  • RépartitionCoûtsProduction.xls
    302.5 KB · Affichages: 60
  • RépartitionCoûtsProduction.xls
    302.5 KB · Affichages: 49
  • RépartitionCoûtsProduction.xls
    302.5 KB · Affichages: 61
Dernière édition:

Int0x

XLDnaute Occasionnel
Re : Formules / VBA ? Optimisation de recherches (plages dynamiques)

Encore mille merci Dranreb !
Pour être franc, je ne m'attendais pas à aussi bien !

Dernière question: il n'est pas impossible que je sois amené à devoir ajouter 2 colonnes pour chaque opération (à droite de prix / Unit). Serait-ce compliqué à modifier ?

Exemple dans le fichier joint: la colonne "Prix 2" de chaque opération irait chercher la valeur "Prix2" dans l'onglet machine associé (juste à coté du prix/unit).

Serait-ce long / compliqué à mettre en place ?
Auquel cas, je travaillerais avec cette version qui est déjà parfaite :)

Encore merci pour votre patience et le temps passé sur ce fichier !
Au plaisir,
Int0x
 

Pièces jointes

  • RépartitionCoûtsProduction +2col.xls
    350 KB · Affichages: 105

Discussions similaires