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.