Alleger une formule
Bonjour,
J'ai fait un planning, le but du planning est de pouvoir savoir le temps de production et les différents changement d'un produit.
Le fichier est malheureusement trop lourd et donc met beaucoup de temps à charger car la formule ci dessous apparaît pas loin de 40 000 fois
=ARRONDI(SI(RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*2;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*3;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*4;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*5;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*6;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*7;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*8;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*9;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*10;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*11;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*12;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*13;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*14;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*15;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*16;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*17;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*18;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*19;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*20;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*21;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*22;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*23;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*24;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*25;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*26;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*27;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*28;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*29;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*30;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*31;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*32;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*33;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*34;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*35;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);SI(RECHERCHEV(F208+$C$5*36;$C$5:$NN$148;F207)=RECHERCHEV(F208+$C$5;$C$5:$NN$148;F207);F208+$C$5*36;RECHERCHEV(F208+$C$5*35;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*34;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*33;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*32;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*31;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*30;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*29;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*28;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*27;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*26;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*25;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*24;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*23;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*22;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*21;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*20;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*19;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*18;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*17;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*16;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*15;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*14;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*13;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*12;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*11;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*10;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*9;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*8;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*7;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*6;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*5;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*4;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*3;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5*2;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5;$C$5:$NN$148;376));RECHERCHEV(F208+$C$5;$C$5:$NN$148;376))*0,166666666666666;2)
J'aimerais savoir s'il y a moyen de la simlplifier par vba ou par d'autre calcul excel (document ci joint simplifier sur une seul journée et sur une base de 4 changement seulement)
Les lignes sont colorées.
Si besoin d'élèment supplémentaire, dite le.
Merci d'avance
Cordialement
Salut !
Bé !! Au vu de la formule je ne prends pas la peine de la lire, ni de charger un classeur qui la contient.
Je veux bien me pencher sur des conditions, si exprimées logiquement en bouclant la totalité des cas possibles.
Je veux bien aussi examiner les méthodes pour atteindre un objectif clairement indiqué à partir d'une situation de départ clairement définie.
Cordialement
Bonjour,
Merci de ta réponse MFerrand.
Donc tout d'abord le fichier que j'ai joint est très simplifier et ne contient que 20 fois la formule afin de montrer ce que j'essaye de faire.
Tout d'abord j'ai un planning qui se fait sur une année complète et qui se rempli au fur et à mesure par des chiffres allant de 1 à 9. Chaque chiffre veut dire quelque chose par exemple 1 c'est production, 2 préparation, ect.
Moi ce que je souhaitais faire initialement c'était de savoir par jour tous les changements qui ont eu lieu : par exemple de minuit à 7H je suis en mode 1 (production), de 7H à 18H en mode 2 (préparation), de 18h à 24h mode 1 (production).
Le planning étant rempli toutes les 10min, ça fait 144 ligne.
Voilà le document à quoi il sert. Maintenant pour la formule j'ai voulu dire que si de 00h10 à 00h20 c'est la même chose que 00h00 à 00h10 on va voir la cellule d'après et ainsi de suite jusqu'à un changement. Pour essayer de simplifier la formule j'ai fait par tranche de 6H donc j'ai quatre fois la formule par changement potentiel (25 changements maximum par jour). Pour le changement 1, je prend comme base 00h00 à chaque fois, à partir du changement 2 je prend comme base, la valeur précédente (si ça finit à 7H je prend 7H comme base de début), j'ai mis une limite pour ne pas dépasser 24h et si on finit par 24h le changement d'après prend comme valeur une cellule vide.
Çà fonctionne bien malheureusement ça me fait 4 * 25 par jour donc 100 et 100 * 365 donc 36 500 fois la formule ça fait lourd et très long à ouvrir.
J'essaie de passer par une macro et faire une boucle mais je galère un peu. Après si y a d'autre méthode, il y a moyen de simplifier la formule je suis preneur également.
J'espère que j'ai bien répondu à ta demande MFerrand.
Cordialement
On part d'une formule qui te pose problème. A la voir (de loin
Une formule est destinée à renvoyer un résultat à partir de données initiales qu'on lui fait traiter : le fait qu'elle aille chercher ici ou là ne me dit rien sur le résultat attendu, à partir de quoi, qui se trouve où...
Tu veux savoir par jour tous les changements qui ont eu lieu... Très bien ! Qu'est-ce qu'un changement, quelles sont ses caractéristiques, comment se décrit un changement particulier en ce qui le distinguera sans ambiguïté de tout autre changement. Et qu'est-ce qui permet de le détecter, qu'est-ce qu'on veut précisément recueillir...
Tu parles de planning au départ mais tu l'évoques ensuite en termes de relevé d'activités. Dans quoi est-on exactement ?
Le "planning" est rempli toutes les 10 min : avec quoi ? comment ?...
Bref, comme brouillage d'informations on aura du mal à faire mieux. Restons sur la formule : je n'ai aucune intention de la lire mais s'il n'apparaît pas possible de savoir où elle se trouve, à quelle données elle fait appel, où se trouvent ces données, quel résultat elle doit renvoyer... inutile de commencer à chercher une solution.
Cordialement
C'est un planning de production qui se rempli à la main par exemple tous les matins. Le planning se compose sur une année donc 365 jours de 24 heures à chaque fois, chaque heure est divisé par tranche de 10minute. Dedans on met des chiffre allant de 1 à 9 selon où en est la production. Chaque chiffre ce réfère à quelque chose comme j'ai dit précédemment.
Un changement c'est à chaque fois qu'on change de chiffre dans les cases par exemple de 00h00 à 7h00 on a que des 1, de 7h00 à 18h00 on a que des 2, de 18h00 à 24h00 on a que des 1. Ça fait un totale de 3 changements
Si tu ouvres mon fichier, tu verras que j'ai mis une journée toute simple. Et en dessous un tableau dans lequel tu as :
Exemple changement 1 : 00h00 à 7h00
Changement : c'est le début du changement
Etat : je recherche l'état du changement donc le chiffre qui est marqué dans le planning selon l'heure du début dans mon exemple ça serait 1
Date début production : Je vais chercher la date du jour dans mon fichier ça serait 01/01/15
Nombre de colonne début production : je vais chercher le numéro de colonne de ma plage de donnée pour la rechercheV de ma grosse formule, dans mon fichier c'est 4
Heure début production : je vais chercher le début de mon changement, lors du changement 1, le début est automatiquement 0 dans le cas d'un changement 2 ou plus, je vais chercher la fin du changement précédent
Numéro début production : je vais chercher le numéro de la ligne de la plage de donnée du début d'heure, pour le changement 1 c'est toujours 0 sinon ça va chercher la valeur dans la colonne C selon l'heure du début de changement, dans mon fichier pour l'heure 00h00 c'est 0
Nombre de ligne début production : je vais chercher le numéro de la ligne de la feuille excel du début d'heure, dans mon fichier pour l'heure 00h00 c'est la ligne 5, (ça recherche dans la colonne E)
Date fin production : Je vais chercher la date du jour dans mon fichier ça serait 01/01/15
Nombre de colonne fin production : je vais chercher le numéro de colonne de ma plage de donnée pour la rechercheV de ma grosse formule, dans mon fichier c'est 4
Heure fin production : Déterminer la fin du changement donc c'est ici qu'apparaît ma grosse formule. Pour essayer d’allégir ma formule je l'ai divisé en quatre, la borne est de 0h00 à 6h après le début du changement : exemple si le changement commence à 7h00, je ne dépasserais pas 13h dans cette cellule. Donc selon mon exemple de base c'est 6h00 qui va s'afficher ici. Dans cette formule comme je le disais, je vais chercher la fin du changement en cherchant le numéro de colonne dans ma plage de donnée puis en comparant chaque cellule avec la première cellule du changement.
Numéro fin production : je multiplie la ligne ci dessus par 6 pour avoir le numéro de ligne dans la colonne C
Heure fin production : Déterminer la fin du changement donc c'est ici qu'apparaît ma grosse formule. Pour essayer d’allégir ma formule je l'ai divisé en quatre, la borne est de 6h00 à 12h après le début du changement. Donc selon mon exemple de base c'est 7h00 qui va s'afficher ici. Dans cette formule j'utilise comme base ce que j'ai trouvé précédemment (le 6H d'il y a 2 lignes) et je vais refaire une comparaison sur une durée de 6H en plus donc ici à 7h se finit le changement, le résultat sera 7h
Numéro fin production : je multiplie la ligne ci dessus par 6 pour avoir le numéro de ligne dans la colonne C
Heure fin production : Déterminer la fin du changement donc c'est ici qu'apparaît ma grosse formule. Pour essayer d’allégir ma formule je l'ai divisé en quatre, la borne est de 12h00 à 18h après le début du changement. Donc selon mon exemple de base c'est 7h00 qui va s'afficher ici car à 7h c'était la fin du changement il reprend la même valeur.
Numéro fin production : je multiplie la ligne ci dessus par 6 pour avoir le numéro de ligne dans la colonne C
Heure fin production : Déterminer la fin du changement donc c'est ici qu'apparaît ma grosse formule. Pour essayer d’allégir ma formule je l'ai divisé en quatre, la borne est de 18h00 à 24h après le début du changement. Donc selon mon exemple de base c'est 7h00 qui va s'afficher ici car à 7h c'était la fin du changement donc il reprend la meme valeur. Et c'est sur cette ligne que je prend ma vraie valeur de fin de changement pour recommencer une autre cycle
Numéro fin production : je multiplie la ligne ci dessus par 6 pour avoir le numéro de ligne dans la colonne C
Et donc avec tout calcule moi initialement j'essaye de soutirer par jour tous les changements de la journée et pour chaque changement j'essaie de savoir : le début de changement, la fin de changement et quel était l'état de ce changement.
Il y a surement plus simple et des trucs inutiles, je suis preneur de tout pour allégir et simplifier mon document.
Est ce plus clair, ou il y a des zones d'ombres? ou des passages où je m'explique très mal? Le document en pièce jointe est allégit et peut surement aider à la compréhension de mon pavé.
Cordialement
Fichtre !
Un petit problème de définition : un changement se produit au moment où l'état se modifie, il y a un état antérieur au changement et un état postérieur. Tu tends à assimiler changement et état, ce qui ne facilite pas le traitement.
Par ailleurs, de façon à faire un petit pas dans la compréhension de ton problème, je note que l'état figure en colonne F de F5 à F148, ce qui ne m'explique pas la raison d'une recherche dans un tableau gigantesque pour détecter un changement repérable dans une seule colonne... ?
Je ne tiens pas à me préoccuper d'autre chose que l'heure de fin concernée par ta fameuse formule. Comme il me paraît difficile de travailler sur ton tableau, voici quelques pistes en matière de formulation...
Précisons d'abord : il y a un état initial, et des états modifiés suite à changement. [Ton modèle était assez peu démonstratif puisqu'un seul état sur la plage de 24h.]
J'ai donc introduit au hasard 2 changements.
Je signale au passage qu'une formule telle que : =SOMMEPROD(1/NB.SI(F5:F148;F5:F148))
me fournira le nombre d'états différents dans la plage de 24h. Elle n'est sans doute pas la plus utile pour toi, dans la mesure où un état est susceptible de se répéter sur des plages distinctes.
Plus utile est une formule qui me donnera le nombre de changements :
=SOMMEPROD((F6:F148<>F5:F147)*1)Elle renvoyait naturellement 0 avant que j'introduise des changements, et 2 ensuite.
Reste à déterminer l'heure du changement : considérant que F5 représente l'état (initial) à 0h, F148 est donc l'état à 23h50, les changements peuvent donc se produire et être détectés de F6 à F148. [NB- On ne détecte donc pas de changement à 0h puisque ton système en impose un systématique à cette heure là, que l'état ait changé ou non...]
Une formule pour détecter ces changements :
=PETITE.VALEUR(SI($F$6:$F$148=$F$5:$F$147;1000000;1)*LIGNE($F$6:$F$148);LIGNE(1:1))-5Formule matricielle (validation par Ctrl+Maj+Entrée).
On la tire pour la recopier sur la ligne suivante (puisqu'on a 2 changements (sinon système de conditions pour n'afficher que les valeurs significatives...)
Elle renvoie les numéros de ligne correspondant aux changements, qu'on diminue de 5 (F5 = 0h00), donc des valeurs pouvant être comprises de 1 à 143 (inclus) pouvant aisément être traduites en heures, de 0h10 à 23h50.
Ayant introduit un premier changement en F95, état prolongé jusqu'en F109, un second en F110, prolongé jusqu'à la fin F148, la formule me renvoie donc successivement : 90 et 105, que je peux traduire facilement en heures (division par 6, le reste [multiplié par 10] donnant les minutes). J'ai donc mes 2 changements : à 15h00 et à 17h30.
A noter que le résultat renvoyé par la formule divisé par 144 donnera directement un nombre-heure Excel affichant directement l'heure avec un format de cellule horaire.
Et je pense que ma contribution s'en tiendra là, car je n'ai nulle intention de rebâtir ton tableau pour le rendre cohérent, intelligible et simple d'utilisation, critères auxquels devrait répondre tout dispositif de contrôle ou d'évaluation qui se veut efficace.
Cordialement
Ferrand
Super ça à l'air de fonctionner, merci beaucoup pour ton aide MFerrand
je vais remettre mon fichier à jour voir si tout est nickel
J'ai juste une petite question dans la formule :
=PETITE.VALEUR(SI($F$6:$F$148=$F$5:$F$147;1000000;1)*LIGNE($F$6:$F$148);LIGNE(1:1))-5A quoi ça sert de mettre en formule matricielle? Je connais pas trop ce mode de formule.
Cordialement
Tu noteras que pour détecter les changements on compare la matrice de référence (diminuée d'une ligne) à elle-même en la décalant d'une ligne : les valeurs de même rang seront différentes dans les 2 matrices là où il y a eu changement et égales dans tous les autres cas. On utilise la fonction SI pour renvoyer un multiplicateur élevé en cas d'égalité et 1 dans le cas contraire (changement); on obtient donc une matrice de 2 nombres différents que l'on multiplie à une matrice composée des numéros de lignes. On obtient donc pour y appliquer la fonction PETITE.VALEUR une matrice dans laquelle les valeurs les plus basses seront les numéros de ligne où ont eu lieu les changements.
Si cette dernière fonction n'a pas besoin en tant que telle de validation matricielle, les fonctions qui doivent renvoyer la matrice recomposée ont besoin d'une validation matricielle pour renvoyer une matrice au lieu d'une valeur unique. Cette validation est indispensable pour obtenir le bon résultat.
Cordialement
Bonjour, juste pour te dire MFerrand que ça fonctionne parfaitement ta formule et au lieu de passer 1min à ouvrir et 1min à enregistrer les documents, ça prend 3seconde maintenant.
Merci beaucoup pour ton aide.
Cordialement
Merci du retour.
Bonne continuation.