Simplifier une formule et mise en forme

[jeromeN95]

Bonsoir à tous,
quelqu'un(e) pourrait m'aider à simplifier une formule SVP?

=SI(B12="Linge lavé par jour";(((C7*C9)+(C8*C10))*C6*C11+(((D7*D9)+(D8*D10))*D6*D11+(((E7*E9)+(E8*E10))*E6*E11+(((F7*C9)+(F8*F10))*F6*F11+(((G7*G9)+(G8*G10))*G6*G11+(((H7*H9)+(H8*H10))*H6*H11)/1000)))))/((C9+C10)*C11);SI(B12="Linge lavé par semaine";((C7*C9)+(C8*C10))*C6+((D7*D9)+(D8*D10))*D6+((E7*E9)+(E8*E10))*E6+((F7*F9)+(F8*F10))*F6+((G7*G9)+(G8*G10))*G6+((H7*H9)+(H8*H10))*H6;SI(B12="Linge lavé par mois";(((C7*C9)+(C8*C10))*C6*C11+(((D7*D9)+(D8*D10))*D6*D11+(((E7*E9)+(E8*E10))*E6*E11+(((F7*C9)+(F8*F10))*F6*F11+(((G7*G9)+(G8*G10))*G6*G11+(((H7*H9)+(H8*H10))*H6*H11)/1000)))))/12;SI(B12="Linge lavé par an";(((C7*C9)+(C8*C10))*C6*C11+(((D7*D9)+(D8*D10))*D6*D11+(((E7*E9)+(E8*E10))*E6*E11+(((F7*C9)+(F8*F10))*F6*F11+(((G7*G9)+(G8*G10))*G6*G11+(((H7*H9)+(H8*H10))*H6*H11)/1000)))));""))))

En faite c'est une petite application pro.
J'aimerai que le résultat prenne l'unitée qui lui est associé.
--> SI c'est par jour ou par semaine --> Alors l'unité est le Kg.
--> SI c'est par mois ou par an --> Alors l'unité est la T.

La formule est pratiquement identique à chaque fois mais j'arrive pas à faire plus court.
Merci de votre aide.

 

[Grand Chaman Excel]

Re : Simplifier une formule et mise en forme

Bonsoir,

Voici la formule la plus courte que je peux trouver (vive SommeProd !!):

=(SOMMEPROD(C6:H6,C7:H7,C9:H9,C11:H11)+SOMMEPROD(C6:H6,C8:H8,C10:H10,C11:H11))*CHOISIR(EQUIV($B$12,tonnage,0),1/52/(C9+C10),1/52,1/12,1)

Pour ce qui est du changement de l'unité, je ne sais pas trop comment y arriver facilement...

A+

 

[Grand Chaman Excel]

Re : Simplifier une formule et mise en forme

En y repensant, voici une proposition pour tenir compte de ll'unité.
À noter que la valeur de la cellule est transformée en "texte".

=(SOMMEPROD(C6:H6,C7:H7,C9:H9,C11:H11)+SOMMEPROD(C6:H6,C8:H8,C10:H10,C11:H11))*CHOISIR(EQUIV($B$12,tonnage,0),1/52/(C9+C10),1/52,1/12000,1/1000) & CHOISIR(EQUIV($B$12,tonnage,0)," kg"," kg"," T"," T")

A+

 

[mapomme]

Re : Simplifier une formule et mise en forme

Bonjour jeromeN95, Grand Chaman Excel,

Tout en gardant un nombre comme résultat (formule du post #2), on peut tenter le format personnalisé suivant:

[>=1000]# ##0,000 " T" ;# ##0" Kg"

Notez l'espace avant le 1ier guillement de " T" qui force la division par 1000 à l'affichage (la valeur réelle de la cellule reste bien sûr inchangée)

Exemple donné avec la virgule comme séparateur décimal.

Edit: ou avec un peu de couleur:

[Rouge][>=1000]# ##0,000 " T" ;[Bleu]# ##0" Kg"

 

[jeromeN95]

Re : Simplifier une formule et mise en forme

Bonjour Grand Chaman Excel,
Bonjour Mapomme,

Merci beaucoup pour vos réponses.

Je ne comprend pas trop le post de Mapomme,
Mais merci de votre implication.

Je garde le fichier du Post #3.

Juste une petite amélioration que j'aimerai :
Comment limiter à 2 chiffres aprés la virgule le résultat?
Par exemple, si on modifier le Poids des machines, le résultat donne 15,333333333333 T.
Je souhaiterai 15,33 T.
Idem en Kg, 823,44444444 Kg.

Merci encore pour tout, vous étes formidables....
Vive EXCEL !!!

 

[mapomme]

Re : Simplifier une formule et mise en forme

Bonjour jeromeN95,

Je ne comprend pas trop le post de Mapomme,

Je voulais simplement proposer de conserver le résultat comme un nombre et non comme du texte pour pouvoir par la suite utiliser cette valeur dans d'autres formules. Pour cela j'utilisais la formule du post #2 de Grand Chaman Excel (le résultat est un nombre de Kg) et appliquais à la cellule résultat un format de cellule personnalisé.

Ce format personnalisé permet d'afficher (cellule C12)

• si le nombre est sup. ou égal à 1000 => on affiche le nombre comme si l'unité était la tonne.
• sinon on affiche le nombre tel quel.

Avantage: le nombre est toujours en Kg et on peut l'utiliser tel quel dans d'autres formules.
L'inconvénient, c'est qu'il est indépendant de la valeur choisie dans B12 (si on choisit "Linge lavé par semaine" et que le poids dépasse la tonne le résultat sera affiché en tonne et non en Kg).



Une autre manière est de scinder la cellule résultat C13 en deux cellules C13 et D13 (on ôte la fusion).
en C13, on met la formule du post #2 de Grand Chaman Excel légèrement modifiée:

=(SOMMEPROD(C6:H6;C7:H7;C9:H9;C11:H11)+SOMMEPROD(C6:H6;C8:H8;C10:H10;C11:H11)) * CHOISIR(EQUIV($B$12;tonnage;0);1/52/(C9+C10);1/52;1/12/1000;1/1000)

et en D13, on met la formule suivante:

=CHOISIR(EQUIV($B$12;tonnage;0);"Kg";"Kg";"T";"T")

Inconvénient: le résultat réel est tantôt en Kg et tantôt en tonne. Si on utilise le résultat par la suite, il faudra des formules différentes selon que ce ce sont des Kg ou bien des tonnes.
Avantage: les poids (pour jour et semaine) sont toujours en Kg (même si on dépasse la tonne), les poids (pour mois et année) sont toujours en tonne (même si on ne dépasse pas la tonne).

 

[jeromeN95]

Re : Simplifier une formule et mise en forme

salut,,
merci mais il y a une erreur copncernant la formule :
si le nombre de semaine vaut 52, les resultats sont bon.
Par contre, des que je modifie le nombre de semaine (sur 1 machine ou plusieurs) les résultats sont faux...

 

[Grand Chaman Excel]

Re : Simplifier une formule et mise en forme

En effet,
Dans la formule proposée, j'ai supposé 52 semaines par défaut.

Si on revient à la formule originale par jour on a :
=(((C7*C9)+(C8*C10))*C6*C11+(((D7*D9)+(D8*D10))*D6*D11+(((E7*E9)+(E8*E10))*E6*E11+(((F7*C9)+(F8*F10))*F6*F11+(((G7*G9)+(G8*G10))*G6*G11+(((H7*H9)+(H8*H10))*H6*H11)/1000)))))/((C9+C10)*C11)

La formule proposée est :
=(SOMMEPROD(C6:H6,C7:H7,C9:H9,C11:H11)+SOMMEPROD(C6:H6,C8:H8,C10:H10,C11:H11))*CHOISIR(EQUIV($B$12,tonnage,0),1/52/(C9+C10),1/52,1/12000,1/1000)

qu'il faudrait remplacer par :
=(SOMMEPROD(C6:H6,C7:H7,C9:H9,C11:H11)+SOMMEPROD(C6:H6,C8:H8,C10:H10,C11:H11))*CHOISIR(EQUIV($B$12,tonnage,0),1/C11/(C9+C10),1/52,1/12000,1/1000)

On tient ainsi compte du nombre de semaine pour la machine 1 uniquement, comme dans la formule originale. Si on doit tenir compte de toutes les machines, faudra modifier le formule.

À préciser.

 

[jeromeN95]

Re : Simplifier une formule et mise en forme

Bonsoir,
effectivement, j'ai remplacer 52 par C11 naturellement, mais malheuresement, il se peut qu'une machine fonctionne 52 semaines par an et une autre 50, une 3ième à 23 semaines, ... .

 

[Grand Chaman Excel]

Re : Simplifier une formule et mise en forme

Alors, comment définir le nombre de kg par jour ou par semaine si chaque machine ne fonctionne pas la même durée?
Est-ce la moyenne des durée de fonctionnement? le maximum? le minimum? ou bien un "standard" établi (52 semaines / an).
C'est en déterminant la réponse à cette question que la formule pourra être modifiée correctement.
Avec un exemple numérique avec des semaines différentes et le résultats attendu, ce sera encore mieux.

 

[jeromeN95]

Re : Simplifier une formule et mise en forme

Bonjour,
il est vrai que des exemples sont plus parlant.
Voici donc quelques calculs avec les valeurs attendues.
J'ai utiliser une valeur fixe pour le nombre de semaine par mois afin de simplifier les calculs.

Merci.

 

[Grand Chaman Excel]

Re : Simplifier une formule et mise en forme

Bonjour,

Dans ce cas, je crois qu'il faut oublier la fonction SommeProd et utiliser une fonction personnalisée pour calculer et additionner chaque machine.

La formule à 2 paramètres :
1. la période (Linge lavé par jour, ...)
2. la plage contenant les données des machines (6 lignes x X colonnes), de Capacité à Nbr sem travail/an.

Public Function QteLave(Periode As String, Tabl As Range) As Long
    Dim i As Integer, arr As Variant
    
    arr = Tabl
    For i = LBound(arr, 2) To UBound(arr, 2)
        Select Case Periode
            Case "Linge lavé par jour": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i)) / (Tabl(4, i) + Tabl(5, i))
            Case "Linge lavé par semaine": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i))
            Case "Linge lavé par mois": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i)) * 4.33333
            Case "Linge lavé par an": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i)) * Tabl(6, i)
        End Select
    Next i

End Function

J'ai mis un exemple dans le fichier joint.
Par contre, je ne comprends pas comment arriver aux réponses attendues quand il y a des cycles le WeekEnd...
À préciser.

A+

 

[jeromeN95]

Re : Simplifier une formule et mise en forme

Bonsoir,
regarde dans ton fichier, j'avais mis en colone "I" le détail du calcul que j'utilise pour avoir le résultat en prennant en compte le nombre de semaine travailler par an. Il s'agit de le prendre en compte dans le calcul à l'année.

J'avoue que je suis un peu perdu avec tes fonction dans VBA... Je regarde avec grande attention !

 

[Grand Chaman Excel]

Re : Simplifier une formule et mise en forme

Bonsoir,

Je n'avais pas remarqué que la colonne I était cachée !
Je crois que tu as une erreur dans ta formule pour calculer la quantité par année car le nombre de semaine de travail par année n'est pas pris en compte pour les week end. (voir cellule I42)

J'ai mis dans la colonne K les résultats obtenus par ma formule et j'ai commenté le code :

' Calcule la quantité de linge lavée selon une période définie
' Le résultat retournée est en kg
' Arguments :
'   Periode = cellule qui contient du texte (ex. "Linge lavé par ...")
'   Tabl = plage de 6 lignes x N colonnes (1 colonne par machine)
'   Tabl(Ligne Y, Machine X) où les lignes doivent être, dans l'ordre :
'       1 : Capacité
'       2 : Nb cycle / jour
'       3 : Nb cycles / weekend
'       4 : nb jours / sem
'       5 : nb jours / weekend
'       6 : nb sem / an

Public Function QteLave(Periode As String, Tabl As Range) As Long
    Dim i As Integer, arr As Variant
    
    arr = Tabl
    If UBound(arr, 1) <> 6 Then Exit Function 'la plage doit avoir 6 lignes
    For i = LBound(arr, 2) To UBound(arr, 2)    'boucle sur le nombre de machines
        Select Case Periode
            Case "Linge lavé par jour": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) + Tabl(3, i))
            Case "Linge lavé par semaine": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i))
            Case "Linge lavé par mois": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i)) * 52 / 12      ' 52/12 = 4.3333 nb semaines moyen / mois
            Case "Linge lavé par an": QteLave = QteLave + Tabl(1, i) * (Tabl(2, i) * Tabl(4, i) + Tabl(3, i) * Tabl(5, i)) * Tabl(6, i)
        End Select
    Next i

End Function

En espérant que ça soit ok.
A+

 

[jeromeN95]

Re : Simplifier une formule et mise en forme

Bonsoir,
merci pour l'erreur, je n'avais jamais fait attention a cela.
Effectivement, c'est nettement mieux comme ça.

Pourrais-je te demander encore une chose ?

J'ai mis une petite phrase concernant les unitées en face de chaque type de calculs, croit tu que tu puisse mettre en code VBA les unitées?

Encore merci pour tout, ca m'avance beaucoup.