Discussion :

[bernard695]

Bonjour,

J'aimerai faire une répartition de la quantité (en feuille contrainte: quantité = QTE PREV ACHAT+STOCK DETENU =5000) en fonction des consommations (en feuille Donnée : somme consommation total=6750).

Autrement dit on dispose de 5000 unités de cet article et on a une demande de consommation de 6750, donc j'aimerai répartir les 5000 unités: pour chaque site et ville, quelle quantité détenir (pour ne pas dépasser les 5000 unités qu'on dispose) car on ne peut pas satisfaire la demande de 6750 en concommation.

Merci.

[ARTURO83]

Bonjour,

Solution avec l'aide d'un filtre avancé pour extraire la liste des sites et villes sans doublons, puis fait la répartition des consommations par formules.
Le tout sur la feuille "Donnée", il ne vous reste plus qu'à reporter dans la feuille "Contraintes".
Sur la feuille "Donnée", cliquez sur le bouton pour obtenir les répartitions.

le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Répartition()
    Dim f1 As Worksheet, f2 As Worksheet
    Application.ScreenUpdating = False
    Set f1 = Sheets("Donnée")
    Set f2 = Sheets("Contraintes")
    DerLig = f1.Range("A" & Rows.Count).End(xlUp).Row 'dernière ligne de la Bdd
    f1.Range("A1:B" & DerLig).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I4:J4"), Unique:=True 'extraction sans doublons des sites et villes
    DerLig_Tab = f1.Range("I" & Rows.Count).End(xlUp).Row 'dernière ligne du tableau filtré
    Range("K5:K" & DerLig_Tab).FormulaR1C1 = "=SUMPRODUCT((R2C1:R2286C1=RC[-2])*(R2C2:R2286C2=RC[-1]),(R2C7:R2286C7))" 'Conso par site et par ville
    Range("L5:L" & DerLig_Tab).FormulaR1C1 = "=RC[-1]*(1-R3C11)" 'Répartition ^par ville et par site
    Range("K4").FormulaR1C1 = "=SUM(R[1]C:R" & DerLig_Tab & "C)" 'Total conso
    Range("K3").FormulaR1C1 = "=(6750-5000)/6750" 'pourcentage
    Range("L4").FormulaR1C1 = "=SUM(R[1]C:R" & DerLig_Tab & "C)" 'max autorisé
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Le fichier
Pièce jointe 578920

Cdlt

[bernard695]

Merci beaucoup de cette solution.

Maintenant l'étape suivante est d'appliquer le même principe sur plusieurs articles.
Si possible de préciser le nom de l'article à coté pour qu'on sache c'est la répartition de quel type d'article.

Bonne journée !

[ARTURO83]

Voilà
le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Répartition()
    Dim f1 As Worksheet
    Dim DerLig As Long, DerLig_Tab As Long
    Application.ScreenUpdating = False
    Set f1 = Sheets("Donnée")
    f1.Range("J4:N10000").ClearContents
    DerLig = f1.Range("A" & Rows.Count).End(xlUp).Row 'dernière ligne de la Bdd
    f1.Range("A1:C" & DerLig).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J3:L3"), Unique:=True 'extraction sans doublons des sites, villes et articles
    DerLig_Tab = f1.Range("J" & Rows.Count).End(xlUp).Row 'dernière ligne du tableau filtré
    f1.Range("M4:M" & DerLig_Tab).FormulaR1C1 = "=SUMPRODUCT((R2C1:R" & DerLig & "C1=RC[-3])*(R2C2:R" & DerLig & "C2=RC[-2])*(R2C3:R" & DerLig & "C3=RC[-1]),(R2C7:R" & DerLig & "C7))"
    f1.Range("N4:N" & DerLig_Tab).FormulaR1C1 = "=RC[-1]*(1-R2C13)" 'Répartition ^par sites, villes et articles
    f1.Range("M3").FormulaR1C1 = "=SUM(R[1]C:R" & DerLig_Tab & "C)" 'Total conso
    f1.Range("M2").FormulaR1C1 = "=(R3C13-R1C13)/R3C13" 'pourcentage
    f1.Range("N3").FormulaR1C1 = "=SUM(R[1]C:R" & DerLig_Tab & "C)" 'max autorisé
    Set f1 = Nothing
End Sub

le fichier
Pièce jointe 578933

Cdlt

[bernard695]

Merci mais pouvez vous me préciser à quoi correspond la quantité : "mettre la quantité disponible" en colonne "M1" (D'où provient les 1000000 mis,comment l'avoir).

[ARTURO83]

pouvez vous me préciser à quoi correspond la quantité : "mettre la quantité disponible" en colonne "M1" (D'où provient les 1000000 mis,comment l'avoir).

100000 est un exemple de quantité disponible au même titre que vous en aviez mis 5000 dans la question initiale. C'est à vous de mettre en M1 la quantité qui est à disposition, le reste se fait tout seul dès que vous cliquez sur le bouton.

[bernard695]

Les 5000 disponible était pour un type d'article alors que maintenant on a plusieurs types d'articles donc on ne peut plus à mon avis globalisé pour dire qu'on a par exemple 1000000 en quantité disponible, car sinon il va distribuer la quantité 1000000 sans tenir compte de la contrainte de disponibilité pour chaque article.

Par exemple dans le fichier avec 1000000, si vous filtrer sur l'article "Joint", on a 5000 de disponible sur une demande de 6750 mais il ne distribue pas ces 5000 mais que 4471.

[ARTURO83]

Ok, voici une autre version
le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Répartition()
    Dim f1 As Worksheet
    Dim DerLig As Long, DerLig_Tab As Long
    Application.ScreenUpdating = False
    Set f1 = Sheets("Donnée")
    f1.Range("J2:P10000").ClearContents
    DerLig = f1.Range("A" & Rows.Count).End(xlUp).Row 'dernière ligne de la Bdd
    f1.Range("A1:C" & DerLig).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1:L1"), Unique:=True 'extraction sans doublons des sites, villes et articles
    DerLig_Tab = f1.Range("J" & Rows.Count).End(xlUp).Row 'dernière ligne du tableau filtré
    Range("M2:M" & DerLig_Tab).FormulaR1C1 = "=SUMPRODUCT((R2C1:R" & DerLig & "C1=RC10)*(R2C2:R" & DerLig & "C2=RC11)*(R2C3:R" & DerLig & "C3=RC12),(R2C7:R" & DerLig & "C7))" 'Consommation
    Range("N2:N" & DerLig_Tab).FormulaR1C1 = "=VLOOKUP(RC12,'contraintes '!C1:C3,2,0)" 'QTE PREV ACHAT
    Range("O2:O" & DerLig_Tab).FormulaR1C1 = "=VLOOKUP(RC12,'contraintes '!C1:C3,3,0)" 'STOCK DETENU
    Range("P2:P" & DerLig_Tab).FormulaR1C1 = "=RC[-2]+RC[-1]" 'Total = Qté Prev Achat + Stock
    Range("Q2:Q" & DerLig_Tab).FormulaR1C1 = "=SUMIF(R2C12:R" & DerLig & "C12,RC12,R2C13:R" & DerLig & "C13)" 'Total consommation par article
    Range("R2:R" & DerLig_Tab).FormulaR1C1 = "=IF(RC16=0,0,IF(RC17<=RC16,RC13,(RC[-2]*RC[-5])/RC[-1]))" 'répartition par quantité
    Range("M2:R" & DerLig_Tab).Value = Range("M2:R" & DerLig_Tab).Value
    Set f1 = Nothing
End Sub

Le fichier
Pièce jointe 578953

[bernard695]

Parfait ça me convient.

Merci bcp

[bernard695]

Bonjour @ARTURO83,

Je voudrais une amélioration du fichier (on va le faire étape par étape). En fait j'aimerai dans un premier temps ,dans la feuille "contraintes" automatisé ce que j'ai fait à la main (c'est à dire ramener les noms des villes et des sites qui existent sans doublon et mettre à côté de ces colonnes d'autres colonnes nommé "Nbre j droit" comme dans le fichier).

[ARTURO83]

Bonjour,

Ceci vous convient-il?
Pièce jointe 579251Pièce jointe 579252

Cdlt

[bernard695]

Bonjour,

J'aimerai maintenant dans la feuille "Donnée", créer 2 nouvelles colonnes nommées: Répartition Site et Répartition Ville avec les conditions suivantes:

1) Si dans la feuille "contraintes", à la colonne "REPARTITION II" il est marqué: SITE & VILLE ou CENTRAL , de distribuer à la fois la quantité dans les 2
colonnes nouvellement créer (Répartition Site et Répartition Ville: chaque colonne avec une quantité);

2) Si dans la feuille "contraintes", à la colonne "REPARTITION II" il est marqué: VILLE de distribuer la quantité que dans la colonne nouvellement créer Répartition Ville et de mettre la quantité 0 (zéro) pour la colonne Répartition Site;

Merci d'avance.

[ARTURO83]

Bonjour,

Si je reprends vos dires, cela signifie que, quel que soit le contenu de la colonne E, la "répartition ville" affichera toujours les quantités
Pièce jointe 579339

Cdlt

[bernard695]

En fait le contenu de la colonne E de la feuille "contraintes" nous indique à qui on doit dispatcher la quantité.

1)Si le contenu de la colonne E est "Ville", tout court ,la "répartition ville" affichera toujours la quantité et la "répartition site" affichera elle 0, tout le temps.
2)Si le contenu de la colonne E est "Site&Ville" ou "CENTRAL",cela nous indique qu'on doit dispatcher la quantité pour la ville et le site en même temps (chacun aura une part de la quantité).

Par exemple: si Répartition en Qté=130 et la colonne E est "Ville" alors "répartition ville"=130 et "répartition site"=0

Alors que si Répartition en Qté=130 et la colonne E est "Site&Ville" ou "CENTRAL" alors "répartition ville"= Valeur 1 et "répartition site"=Valeur 2 (avec la condition Valeur 1+Valeur 2=130)

Merci bcp

[ARTURO83]

Bonjour,

En fait le contenu de la colonne E de la feuille "contraintes" nous indique à qui on doit dispatcher la quantité.

ça, je l'ai bien compris, ce que je ne comprends pas c'est la répartition entre les SITE et VILLE lorsque la colonne E= "SITE & VILLE" ou "CENTRAL", car dans le précédent post vous avez écrit:

1) Si dans la feuille "contraintes", à la colonne "REPARTITION II" il est marqué: SITE & VILLE ou CENTRAL , de distribuer à la fois la quantité dans les 2,

ce qui laissait sous-entendre que l'on attribuait la même valeur aux 2 colonnes.

Et là, je ne comprends plus, d'où sortent ces 2 valeurs? Comment fait-on pour répartir une seule valeur sur 2 cellules?

Alors que si Répartition en Qté=130 et la colonne E est "Site&Ville" ou "CENTRAL" alors "répartition ville"= Valeur 1 et "répartition site"=Valeur 2 (avec la condition Valeur 1+Valeur 2=130)

Pour que ce soit compréhensible, donnez des exemples concrets.

Cdlt

[bernard695]

Bonjour,

A vrai dire la colonne "Répartition en Qté" de la feuille donnée ne m'intéresse pas tant que ça, elle sert de référence pour effectuer la nouvelle distribution dans les 2 nouvelles colonnes créées , "répartition ville" et "répartition Site" comme vous l'avez si bien fait dans le précédent fichier.

Mais désormais la répartition de la quantité se fait sur ces deux colonnes quitte à leur attribuer la même valeur (chacun la moitié de la valeur qu'on avait donnée à "Répartition en Qté") , pour respecter la contrainte de l'article à ne pas dépasser : 'Total = Qté Prev Achat + Stock

1) Si dans la feuille "contraintes", à la colonne "REPARTITION II" il est marqué: SITE & VILLE ou CENTRAL , de distribuer à la fois la quantité dans les 2
Cela veut dire que par exemple si initialement en "Répartition en Qté"=130, mettre "répartition ville"=65 et "répartition Site"=65 (ce qui fait 65+65=130 car "Répartition en Qté" est une répartition fictive qui nous permets de faire la vraie répartition en "répartition ville" et "répartition Site" en prenant sa moitié pour chacune des 2 nouvelles colonnes créées)

2)Par contre Si dans la feuille "contraintes", à la colonne "REPARTITION II" il est marqué: VILLE alors qu'auparavant la répartition fictive affiché "Répartition en Qté"=130 alors "répartition ville"=130 et "répartition site"=0

J'espère que vous avez compris, sinon j'essaierai à nouveau d'expliquer car ce n'est pas évident cette partie.

Merci d'avance

[ARTURO83]

désormais la répartition de la quantité se fait sur ces deux colonnes quitte à leur attribuer la même valeur (chacun la moitié de la valeur qu'on avait donnée à "Répartition en Qté")

Si la quantité=1, comment se fait la répartition?


Je répète, donnez des exemples concrets

[bernard695]

Je reviendrai avec des exemples concret plus tard.

Merci de votre aide

[bernard695]

Bonjour Artur083,

J'aimerai dans le jeu de donnée ci-joint faire une distribution

[ARTURO83]

Bonjour,

Désolé, j'ai beau lire, relire, re-relire et re-re-relire c'est franchement incompréhensible, déjà l'ordre des priorités que vous donnez ((ici priorité 713 ensuite 425 puis 342 et enfin 28) est différent de celui du tableau (425 713 342 28).
Ensuite qu'est-ce que LKMJ ?
Ou doivent s'afficher les résultats?

Vous deviez donner des exemples concrets sur le résultat attendu, pour le moment je ne comprends pas quel il doit être et votre description n'est pas claire du tout.
Pensez que celui qui lit le sujet n'est pas à votre place.

Cdlt