Définir des sous série dont la somme s'approche à la moitié

Bonsoir tout le monde;

c'est mon premier post sur le forum , et je suis très ravie qu'on puisse partager nos connaissances.

Bon, mon problème est assez simple et compliqué à la fois; je dispose de plusieurs séries représentant des surfaces, dans le but de dimensionner les équipements nécessaires à chaque surface, je dois diviser mes séries en deux sous séries de telle sorte à ce que la somme des sous séries soit la plus proche de 50%.

pour mieux comprendre, merci de consulter le fichier en jointure.

pour l'instant je fais l’opération à la main et cela me prend un temps fou.

merci beaucoup

Bonjour

Un exemple expliqué me semblerait intéressant

Cordialement

Merci de votre réponse,

sur l’exemple posté, il y'a 3 séries indépendantes l'une de l'autre (série 1,série 2 et série 3: colonnes C), chaque série contient un nombre variable de données sur la surface (Colonne D), je dois diviser chaque série en deux sous séries (ou groupes : Colonne E) en essayant que la somme de chaque groupe soit la plus proche de la moitié de la surface totale de la série.

Exemple: sur la série une : la surface totale est de 15.40 ha, la série a été divisée en deux groupes (1 et 2) de telle sorte à ce que la surface de chaque groupe soit la plus proche de 50%. Dans ce cas, j'ai pu arriver à 49%, 51% de la surface totale de la série.

Je cherche à automatiser l'opération mais je vois pas comment???????????????

Bonjour,

solution via une macro en pièce jointe

1) sélectionner les surfaces d'une série (par exemple D14:D20)

2) lancer la macro test par alt-F8

Merci beaucoup ça marche, et c'est génial, mais la macro ne traite qu'une série à la fois, je me demande si on peut la rendre plus "générale" afin qu'elle puisse traiter toutes les séries à la fois, peut être en introduisant une boucle qui se répète autant de fois que de séries ............................ de toute façon merci infiniment vous m’avez vraiment aidé. si j'arrive à modifier votre macro je vous tiendrai au courant.

encore une fois merci

Bonjour,

version adaptée pour faire le calcul pour toutes les séries. Pour déterminer une série, la macro se base sur les cellules fusionnées de la colonne C, à partir de la ligne 4.

lancer la macro test via alt-F8

Bonjour;

merci infiniment ça marche trés bien.

Crdlt

Bonjour ;

Votre macro marche très bien, mais je n’arrive pas à comprendre certaines lignes.

Est-ce que vous pouvez s’il vous plait me les expliquer, il s’agit de :

Lignes 5-6 et 7:

For i = 0 To 30

e(i) = 2 ^ i

Next i

Si j’ai bien compris 30 est le nombre maximal de valeur que la macro peut traiter ?? Quand j’augmente ce chiffre à 32 ou 40 en changeant la nature des variables (double ou lieu de long, pour un problème de déplacement de capacité, mon fichier Excel se plante???????).

A partir de la ligne 26 :

For i = 0 To 2 ^ nval - 1

sa = 0

For j = 0 To nval - 1

If i And e(j) Then ' si chiffre binaire en position j = 1

sa = sa + p(j + 1)

End If

Next j

'tous les produits ont été traités

If Abs(sa - obj) < cmin Then cmin = Abs(sa - obj): sol = i

Next i

' toutes les combinaisons ont été traitées

' on affiche la combinaison donnant le résultat minimum

i = sol

With Selection(1)

For j = 0 To nval - 1

.Offset(j, 1) = IIf(i And (2 ^ j), 1, 2)

.Offset(j, 2).Formula = "=sumif(" & Selection(1).Offset(0, 1).Address & ":" & Selection(Selection.Count).Offset(0, 1).Address & "," & Selection(j + 1).Offset(0, 1).Address & "," & Selection(1).Address & ":" & Selection(Selection.Count).Address & ")/sum(" & Selection(1).Address & ":" & Selection(Selection.Count).Address & ")"

Next j

End With

Wend

Cells(1, 10) = Timer - t

Merci beaucoup pour votre aide.

Cordialment

Bonjour,

voici le code avec quelques commentaires ...

Sub test()
    Dim i As Long, j As Long
    Dim p(30) 'tableau contenant les surfaces d'une série
    Dim e(32) As Long:  ' tableau contenant les puissances de 2, calculé une fois pour des raisons de performances
    For i = 0 To 30 ' on remplit le tableau des puissances de 2 de 2^0 à 2^30
        e(i) = 2 ^ i
    Next i
    t = Timer ' t = valeur du timer au début de la macro
    ser = 4 ' ser = 1ere ligne du tableau contenant le mot série
    While Cells(ser, "C").MergeCells = True ' tant qu'il y a une série
        Range(Replace(Cells(ser, "C").MergeArea.Address, "C", "D")).Select ' on sélectionne les cellules surface pour cette série
        ser = ser + Selection.Count ' on calcul la ligne de la série suivante
        cmin = 1E+16    ' valeur à optimiser on part d'une très grande valeur
        nval = 0 'nval = nombre de surface dans la série
        obj = 0 ' obj = objectif à atteindre
        For Each v In Selection 'pour chaque surface de la série
            nval = nval + 1 ' incrémente le nombre de surface trouvée
            p(nval) = v ' on stocke la surface dans le tableau p()
            obj = obj + v ' on totalise la surface dans objectif
        Next
        obj = obj / 2 ' on met l'objectif à la moitié de la surface = 50%
        Selection.Offset(0, 1).ClearContents ' on efface le contenu des colonnes E et F
        Selection.Offset(0, 2).ClearContents
        Application.ScreenUpdating = False

        For i = 0 To 2 ^ nval - 1 ' on parcourt toutes les combinaisons de surfaces possibles avec un nombre nval de surfaces
            sa = 0 ' sa= surface totale calculée pour le groupe 1 pour cette combinaison de surfaces
            For j = 0 To nval - 1 ' on détermine quelles surfaces à sélectionner en fonction de la valeur binaire de la combinaison
                If i And e(j) Then    ' si chiffre binaire en position j = 1, on sélectionne cette surface pour le groupe 1, (l'opérateur booléen AND ne fonctionne qu'avec des variables LONG ou INT)
                    sa = sa + p(j + 1) 'et on l'ajoute au total des surfaces
                End If
            Next j
            'la sélection des surfaces pour cette combinaison est terminée
            If Abs(sa - obj) < cmin Then cmin = Abs(sa - obj): sol = i ' on garde la combinaison qui se rapproche le plus de l'objectif

        Next i
        ' toutes les combinaisons ont été traitées
        ' on affiche la combinaison donnant le résultat minimum
        i = sol
        With Selection(1)
            For j = 0 To nval - 1

                .Offset(j, 1) = IIf(i And (2 ^ j), 1, 2) ' on indique le groupe de la surface
                ' on insère la formule qui permet de calculer le % pour le groupe auquel appartient cette surface
                .Offset(j, 2).Formula = "=sumif(" & Selection(1).Offset(0, 1).Address & ":" & Selection(Selection.Count).Offset(0, 1).Address & "," & Selection(j + 1).Offset(0, 1).Address & "," & Selection(1).Address & ":" & Selection(Selection.Count).Address & ")/sum(" & Selection(1).Address & ":" & Selection(Selection.Count).Address & ")"

            Next j
        End With
    Wend
    Cells(1, 10) = Timer - t ' on affiche le temps d'exécution en J1
End Sub

Si j’ai bien compris 30 est le nombre maximal de valeur que la macro peut traiter ?? Quand j’augmente ce chiffre à 32 ou 40 en changeant la nature des variables (double ou lieu de long, pour un problème de déplacement de capacité, mon fichier Excel se plante???????).

oui, si nécessaire d'avoir plus de 30 surfaces dans une série, il faut que je modifie le programme, la variable doit être de type LONG ou INT. mais sache que pour examiner toutes les combinaisons de 30 surfaces et plus, le temps d'exécution sera très important.

Bonjour;

Merci de ta réponse; mais j'ai toujours pas compris le pourquoi

je ne comprends tjrs pas pourquoi on calcule:

For i = 0 To 30 ' on remplit le tableau des puissances de 2 de 2^0 à 2^30

e(i) = 2 ^ i

Next i

et

For i = 0 To 2 ^ nval - 1 ' on parcourt toutes les combinaisons de surfaces possibles avec un nombre nval de surfaces

sa = 0 ' sa= surface totale calculée pour le groupe 1 pour cette combinaison de surfaces

For j = 0 To nval - 1 ' on détermine quelles surfaces à sélectionner en fonction de la valeur binaire de la combinaison

If i And e(j) Then ' si chiffre binaire en position j = 1, on sélectionne cette surface pour le groupe 1, (l'opérateur booléen AND ne fonctionne qu'avec des variables LONG ou INT)

sa = sa + p(j + 1) 'et on l'ajoute au total des surfaces

End If

Next j

En gros je pense que j'ai pas compris comment tu génères tes combinaisons ( la philosophie quoi)

Et pour ta dernière remarque je pense qu'il y'a un petit souci, car même si je change la nature de ma variable, comme tu viens de citer dans tes commentaires l'operateur AND ne va pas fonctionner?????????

Merci beaucoup pour tes réponses, j’en suis très reconnaissante.

rebonjour

je vais prendre un exemple avec 3 surfaces

j'ai 8 possibilités de répartir ces 3 surfaces en 2 groupes

j'utilise les représentations binaires des nombres correspondant à ces possibilités pour la répartition en 2 groupes, (0 correspondant à 1 groupe et 1 à l'autre)

0 qui correspond à 0 0 0 en binaire

1 qui correspond à 0 0 1 en binaire

2 qui correspond à 0 1 0 en binaire

3 qui correspond à 0 1 1 etc ...

4 qui correspond à 1 0 0

5 qui correspond à 1 0 1

6 qui correspond à 1 1 0

7 qui correspond à 1 1 1

pour déterminer si un bit est à 0 ou à 1, j'utilise l'opérateur AND de manière un peu spéciale

comment fonctionne l'opérateur AND booléen entre 2 nombres ?

0 AND 0 donne 0

0 AND 1 donne 0

1 AND 0 donne 0

1 AND 1 donne 1

par exemple résultat de 7 (1 1 1) AND 5 (1 0 1) donne 5 (1 0 1)

pour déterminer si le bit 0 (le plus à droite) est à 0 ou à 1 pour un nombre décimal donné, je fais une opération AND booléenne (qui ne donne des résultats corrects qu'avec des variables de type LONG et INT) avec la valeur décimale correspondant à un bit à 1 en position 0, ce qui correspond à 2^0 (=1)

le bit 1 (le bit en 2ème position en partant de la droite, avec 2^1(=2)

le bit 2 (le bit en 3ème position en partant de la droite, avec 2^2(=4)

etc ...

par exemple combinaison 5

bit 0 à 1? 5 AND (2^0)1 donne comme résultat 1 (<>0 donc bit à 1) -> j'associe cette surface au groupe 1

bit 1 à 1? 5 AND (2^1)2 donne comme résultat 0 (=0 donc bit à 0) -> j'associe cette surface au groupe 2

bit 2 à 1? 5 AND (2^4)4 donne comme résultat 4(<>0 donc bit à 1) -> j'associe cette surface au groupe 1

sur base de cette sélection, je fais la somme des surfaces et garde la combinaison qui est la plus proche d'une répartition 50-50.

Pour ne pas avoir à recalculer à chaque fois les puissances de 2 ( et donc améliorer les performances), je calcule ces valeurs et les mets dans un tableau. (le tableau e())