Somme imbriquée sans doublons !

[gerardphi]

Bonsoir,
Je viens vers vous, car je me gratte la tête depuis une semaine sans résultat!!!

Je dois créer un planning indisponibilité de machine en fonction de date de maintenance et calculer un coût de l’arrêt.

Ci-joint une extraction d'un fichier (quelques ligne/ 1600 lignes!!!)
Chaque mois, j’extrais des données d’une base de "Maintenance machine" où sont répertorié :
La ville, dates début et fin, n° de machine, types d’indispo et le coût/ mois.
Les dates se chevauchent, s’imbriquent les une par rapport aux autres !!!

Mon souhait, obtenir le tableau 2.
En ne prenant en compte qu’une seule fois une maintenance (date début/Fin) si celle-ci a déjà été comprise en compte avant !
Exemple : BB15 est déjà pris en compte dans BB13, donc il faut prendre que BB13 (colonne/ligne) dans la somme car même machine!
Puis calculer pour chaque période (date de début par rapport fin du mois) le coût de la maintenance mensuelle (voir les formules dans le tableau 2)
Pour info : tenir compte des n° de colonnes!!!!

Je vous remercie!!!!!
Cdt

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

Pas beaucoup de réponses, pourtant cela ne me semble pas fort compliqué.
A moins que ce soit la compréhension de la question qui pose problème.

Délais
En ligne 33 : du 13/09/2010 au 31/12/2010
En ligne 36 : du 14/10/2010 au 05/11/2010
On ne tient pas compte du deuxième délai, car compris sous le premier
Mais si on avait :
En ligne 33 : du 13/09/2010 au 05/11/2010
En ligne 36 : du 14/10/2010 au 31/12/2010
faudrait-il tenir compte d'un délai global du 13/09/2010 au 31/12/2010 sous la ligne 33 ou le calcul doit-il être scindé sous les deux lignes, si oui comment ?

Ville
Les numéros de machines sont-ils attribués par ville ou au niveau de l'entreprise.
En d'autres mots, peut-on avoir le même numéro de machine dans deux villes différentes ?

Coût
Pourquoi des dates d’indisponibilité n'ont-elles pas de coût ?
Par exemple sous BB18

Durée
D'après les calculs sous la pièce jointe le coût est calculé 24 heures/24 et 7 jours/7.
Donc les machines ne s'arrêtent jamais, est-ce bien cela ?

Il me semble qu'une seule formule pour la plage AS26:BD38 devrait être réalisable.

A te lire.

 

[gerardphi]

Re : Somme imbriquée sans doublons !

Bonjour,

Pas beaucoup de réponses, pourtant cela ne me semble pas fort compliqué.
A moins que ce soit la compréhension de la question qui pose problème.

Délais
En ligne 33 : du 13/09/2010 au 31/12/2010
En ligne 36 : du 14/10/2010 au 05/11/2010
On ne tient pas compte du deuxième délai, car compris sous le premier
Mais si on avait :
En ligne 33 : du 13/09/2010 au 05/11/2010
En ligne 36 : du 14/10/2010 au 31/12/2010
faudrait-il tenir compte d'un délai global du 13/09/2010 au 31/12/2010 sous la ligne 33 ou le calcul doit-il être scindé sous les deux lignes, si oui comment ?

Ville
Les numéros de machines sont-ils attribués par ville ou au niveau de l'entreprise.
En d'autres mots, peut-on avoir le même numéro de machine dans deux villes différentes ?

Coût
Pourquoi des dates d’indisponibilité n'ont-elles pas de coût ?
Par exemple sous BB18

Durée
D'après les calculs sous la pièce jointe le coût est calculé 24 heures/24 et 7 jours/7.
Donc les machines ne s'arrêtent jamais, est-ce bien cela ?

Il me semble qu'une seule formule pour la plage AS26:BD38 devrait être réalisable.

A te lire.

Je vois que tu as mis le doigt sur la difficulté !!!
Réponse:
En ligne 33 : du 13/09/2010 au 05/11/2010
En ligne 35 : du 14/10/2010 au 31/12/2010
faudrait-il tenir compte d'un délai global du 13/09/2010 au 31/12/2010 sous la ligne 33 ou le calcul doit-il être scindé sous les deux lignes, si oui comment ?
Voir l'exemple Feuil1-
Exemple2; est le cas ou ce ne sont pas les 2 mêmes machines (3 & 5)

Les n° machines sont par ville => on a plusieurs fois les même n°.

Elle fonctionne 24h/24h
Les coût qui =0, c'est que cette indispo n’influence pas sur la prod (nettoyage et peinture extérieur..)

Les coûts sont à définir par ville ... il y en 70 !!!! Donc il faudrait une formule qui détermine le coût pour Lille : AS23:BD25 et Nantes : AS26:BD38, etc
Je ferais après un TCD pour analyser les données

Pouvez-vous mettre les résultats dans la colonne BF:BQ (12 mois)

Merci
Cdt

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

J'en encore quelques difficultés de compréhension.

Peux-tu relire ma question concernant les "Délais", car ta réponse ne me semble pas répondre à ma question ?
Je ne vois pas à quoi correspond Feuil1 - Exemple 2

Peux-tu expliquer la différence entre "total", "partiel" et "toute chaîne" ?

Je comprends pourquoi le calcul ne doit pas être effectué en BB35, mais ne comprends pas pourquoi il faut l'effectuer en BC35.

Peux-il il y avoir plus de deux interventions annuelles par machine (partiel) ?

 

[gerardphi]

Re : Somme imbriquée sans doublons !

Bonjour,

J'en encore quelques difficultés de compréhension.

Peux-tu relire ma question concernant les "Délais", car ta réponse ne me semble pas répondre à ma question ?
Je ne vois pas à quoi correspond Feuil1 - Exemple 2

Peux-tu expliquer la différence entre "total", "partiel" et "toute chaîne" ?

Je comprends pourquoi le calcul ne doit pas être effectué en BB35, mais ne comprends pas pourquoi il faut l'effectuer en BC35.

Peux-il il y avoir plus de deux interventions annuelles par machine (partiel) ?

Ok ... c'est ma pièce jointe qui n'est pas partie!!!!

Réponse:
En ligne 33 : du 13/09/2010 au 05/11/2010
En ligne 35 : du 14/10/2010 au 31/12/2010
faudrait-il tenir compte d'un délai global du 13/09/2010 au 31/12/2010 sous la ligne 33 ou le calcul doit-il être scindé sous les deux lignes, si oui comment ?

Voir Feuil2-Exemple1 et l'exemple2: est le cas ou ce ne sont pas les 2 mêmes machines (3 & 5)

Totalité de la chaîne : arrêt de toute l'unité de prod.
Total : une machine peut avoir 2 petites unités de prod (Partiel = 1 machine et total = 2 machines)

En effet, il y avoir plusieurs intervention/ indispo sur une machine dans l'année.

A toi de voir s'il serait + facile de créer une autre feuille !!!!

On cherche aussi à connaître le coût de chaque indispo

Cdt

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

Avec ta nouvelle pièce jointe ton explication devient compréhensible.
Sous la première pièce jointe il n'y avait pas de Feuil2 !

Le mieux serait donc que la formule à mettre en place n'affiche rien en Feuil2!H4 et I3, car les additions en colonne K sont incorrectes
=SOMME(K3:K4) renvoie 95465, tandis que =SOMME(G5:J5) renvoie 78690

Avant de continuer j'aimerai aussi avoir réponse à la question posée sous mon message précédent :
Je comprends pourquoi le calcul ne doit pas être effectué en BB35, mais ne comprends pas pourquoi il faut l'effectuer en BC35.

A te lire et bonne soirée, car pas de réponse de ma part avant demain

 

[gerardphi]

Re : Somme imbriquée sans doublons !

La Feuil2 n'était pas dans le premier post mais dans celui de dimanche ... le post n'est pas partie avec la pièce jointe!!!

BB35 : il ne faut pas prendre cette valeur, si elle est imbriqué dans une autre date mais j'en ai besoin pour connaître le coût de l'indispo en ligne.
Attention: on peut avoir
Nantes; 3; 13/10/2010 au 15/11/2010
Nantes; 3; 01/11/2010 au 28/11/2010 (cette indispo est prise en compte du 16 au 28/11)

J'ai besoin de connaître le coût d'une indispo en ligne et par mois en colonne.

Je pense qu'en partant du tableau1 de la feuil1, distribuer sur 2 feuilles : le coût d'une indispo en ligne et l'autre en colonne par mois.

Cdt

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

Une première approche en pièce jointe.

Les formules nommées dans les colonnes BE et BF recalculent les dates existantes en colonnes AH et AI, en supprimant les chevauchements de dates.

Les colonnes BG et BH testent les dates qui ont été modifiées.
En ligne 35 les deux dates ont été modifiées (FAUX), car leur soustraction doit être égale à 0, étant donné que cette période est déjà comprise sous la ligne 33.

Les colonnes BG et BH seront supprimées car il sera fait appel aux formules nommées directement dans les formules à mettre en place en AS23:BD38

Ces formules nommées doivent être testées sur une plus grande plage de données pour vérifier leur bon fonctionnement, je te laisse faire.

Mais il y a autre chose !

Le format en AI9 n'est pas bon, c'est du texte.
La correction a été apportée en AI29.

Le délai entre le 01/12/2010 et le 31/12/2010 n'est pas 31 jours, mais 30 jours.
Lorsqu'il n'y a pas d'heures mentionnées, Excel considère que l'heure est égale à 0:00.
En conséquence, entre le 01/12/2010 à 00:00 et le 31/12/2010 à 00:00 il n'y a que 30 jours.
D'où des erreurs dans les calculs en BD29, BD33, BC37, ... et il y en a peut-être d'autres.
En saisissant 31/12/2010 24:00 Excel transforme automatiquement cette valeur en 01/01/2010

Dès que les deux formules nommées ont été testées, on pourra passer à l'étape suivante : une formule unique pour la plage AS23:BD38

A te lire.

 

[gerardphi]

Re : Somme imbriquée sans doublons !

Bonsoir, bonjour ....

Petite difficulté:
Les fichiers et les onglets non pas les mêmes nom que dans la formule déb et fin. La formule ne fonctionne pas en "dehors" de l'onglet Feuil1

J'ai "tiré" les formules sur le tableau1. Pourquoi on n'a pas la même chose que sur le tableau2 puisque le test se fait sur les dates.

"Toute la chaîne" : peut vouloir dire "arrêt total" de la prod ou diminution de production de la chaîne sur toute la durée (problème d'alimenter la matière première.)

Q: pour une date qui chevauche une autre date avant ou après, on pourra avoir : (vrai,faux) ou (faux,vrai)

Cdt

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

Les fichiers et les onglets non pas les mêmes nom que dans la formule déb et fin. La formule ne fonctionne pas en "dehors" de l'onglet Feuil1
Des formules nommées ne sont valables que dans un même fichier.
En modifiant le nom du fichier, pas de problème, mais en faisant appel au nom attribué dans une autre fichier il faut préalablement nommer les formules dans cet autre fichier.
En modifiant le nom de l'onglet, pas de problème, les formules s'adaptent automatiquement.
Vois sous la pièce jointe, j'ai modifié le nom de l'onglet "Feuil1" en "bleu", et cela fonctionne.

J'ai "tiré" les formules sur le tableau1. Pourquoi on n'a pas la même chose que sur le tableau2 puisque le test se fait sur les dates.
Parce que la référence de la première ligne du tableau est modifiée.
Il faut donc remplacer dans la formule les 23 par des 3 et tout fonctionne, vois la pièce jointe.
On peut nommer les formules sur base des tableaux, par exemples deb1 et fin1, deb2 et fin2.

"Toute la chaîne" : peut vouloir dire "arrêt total" de la prod ou diminution de production de la chaîne sur toute la durée (problème d'alimenter la matière première.)

Là je ne comprends plus !
"peut vouloir dire" donc ce n'est pas certain, car cela peut aussi vouloir dire autre chose.
C'est quoi cet "autre chose" et quelle est son influence sur les calculs des dates ?
Puisqu'il y a un arrêt total du 23/06/2010 au 31/12/2010 (AH29:AI29), comment se fait-il que des coûts sont encore comptabilisés durant cette période ?

Je n'ai pas reçu de réponse quant à la façon de déterminer les jours.
Vouloir compter 30 jours pour un délai du 01/12/2010 au 31/12/2010 est contraire à la logique informatique.
Vois-tu une solution ?

Ne crois-tu pas qu'il faudrait commencer par :
- énoncer de façon claire, précise et complète toutes les contraintes à respecter
- essayer de comprendre les formules proposées afin de pouvoir les adapter à d'autres cas de figure
- répondre aux questions posées.

A le lire.

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonsoir,

En attendant ta réponse à mon message précédent, j'ai quelque peu progressé en écrivant une formule qui renvoie les résultats (3ème tableau).

Les résultats que j'obtiens ne sont pas tous les mêmes que ceux qui tu avais calculés, vois la pièce jointe.
Peux-tu vérifier les cellules avec fond vert clair ?

A titre d'exemple : AU26
Du 01/03/2010 00:00 au 04/03/2010 10:00
Je compte 3 jours de 24 heures + 10 heures, soit (3*24)+10 = 82 heures au coefficient 100 (AU6) donne 8200 et non 10600

A te lire.

 

[gerardphi]

Re : Somme imbriquée sans doublons !

Je comprends la problématique de différencier pour vous qui n'êtes pas dans l'entreprise, si une "Toute la chaîne" veut dire une prod à 0 (arrêt complet de prod) ou une limitation de prod (problème d'approvisionnement). Pour nous, lorsqu'il y a un coût c'est une limitation de prod. Ce qui explique que nous ayons un coût en AX29:BD29.
J'ai modifié la requête pour différencier "Toute la chaîne" d'une "limitation". A l'avenir il y aura dans la colonne "Disponibilité" Limitation voir AK9 et AK29.
Dans ton dernier fichier-V5 tu n'utilisé pas cette colonne!

Après réflexion: pour les dates dont le format est en texte.
Par convention mettre le jour à 24h00.
Fichier-V5_En effet l'écart est lier au +1jr que j'ai ajouté dans la formule car j'avais fait un test de calcul avec le 31/12/2010 au format texte (pas fait le lien!!!!) et il me manquait un jour sans le vérifier sur les autres dates....ceci explique l'écart de 24h

Merci pour ta collaboration
A minuit .... je ne suis plus très opérationnel!!!! Je vais demain, non tout à l'heure modifier les cellules pour tester 1500 ligne
Il n'y avait aucun jugement dans mon post!!!!!!
PS bien vu le test des dates!!!!

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

Dans ton dernier fichier-V5 tu n'utilises pas cette colonne !
C'est parce que je ne sais pas ce que je dois en faire.
Sous ton fichier "Planning Machine V2" tu n'en tiens pas compte non plus, donc pas d'exemple concret qui permettrait d'en comprendre le but.

En ce qui concerne les dates sans heures, le plus simple reste de saisir la date et 24:00
mais il y a moyen de contourner ce problème.
Exemple :
A1 : 31/12/2010
B1 : =A1+(MOD(A1;1)=0) renverra 01/01/2011

 

[gerardphi]

Re : Somme imbriquée sans doublons !

Bonjour,

Tes formules fonctionnent très bien!!!

Mais il y a un cas qui n'était pas dans mon exemple!!!!

Lorsque l'on change de ville et que l'on a le même n° de machine, la formule renvoie la date du 03/12/2010 (AI45) en BE46 au lieu du 01/01/2010 (AH46)
J'ai mis un 2 en AJ46, alors qu'il n'y avait rien avant.

Peux tu prendre ce cas dans ta formule!!!!!
Merci!
Cdt

 

[hoerwind]

Re : Somme imbriquée sans doublons !

Bonjour,

Sous la pièce jointe :
- les formules en BE43:BF58 sont adaptées à ta remarque formulée sous ton dernier message
- ces formules sont nommées deb et fin
- il est fait appel à ces deux noms dans les formules de la plage AS43:BD58
- la plage BE43:BF58 peut donc être effacée

Vois si il y a encore autre chose