Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Nnaks · 9 Décembre 2012

Bonjour chers amis internautes,

Je me permets de solliciter votre aide pour résoudre une colle que je n'arrive pas à trouver.
Ce serait au moyen d'un résolution simple ou d'un code VBA que je pourrais éventuellement trouver la solution à mon problème que voici:
J'ai un certain nombre de clients i=20 dont la demande est Di
Un certain nombre d'usines j=10 dont la capacité est Bj
Je souhaite déterminer un facteur "Id" qui représente le nombre maximum de clients dont la demande totale ne dépasse pas la capacité d'une usine donnée.
Sur le fichier joint, on a une colonne avec les demandes des clients, un ligne avec les capacités des usines et une ligne correspondant au facteur donné.
D'après ce que j'ai pu comprendre, c'est que pour chaque "Id"(j), par exemple pour l'usine 1 dont la capacité est de 89, c'est de trouver le nombre maximal de clients dont la demande totale (en sommant leurs demandes) est inférieure ou égale à 89. Le choix de ces clients devrait être en plus précisé.... :/

Je galère et je ne vois pas comment est ce que je pourrai y arriver.
J'espère avoir bien expliqué le problème et vous remercie d'avance pour votre aide.

Staple1600 · 9 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonjour à tous

Nnaks
Comme dirait Gérard Blanc: c'est une autre histoire... ou pas?
https://www.excel-downloads.com/threads/vba-generer-une-matrice-i-j-de-donnees.197218/

Car je m'y perds un peu dans tes fils.

EDITION: Bonsoir aussi à toi mapomme

Nnaks · 9 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonjour Staple1600,

C'est exactement la même histoire, le même projet sauf que c'est une autre étape.

Si je dois éclaircir un point, dis le moi pour rendre la tâche plus facile

Merci en tout cas.

mapomme · 9 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonsoir Nnaks,

Un essai de solution avec une macro utilisant le solveur. Voici les instructions de Microsoft pour le faire:

Utilisation des fonctions VBA du Solveur

Pour pouvoir utiliser les fonctions VBA du Solveur à partir de VBA, vous devez activer le complément Solveur dans la boîte de dialogue Options Excel.

Sous Excel:
.

Cliquez sur l’onglet Fichier, puis cliquez sur Options sous l’onglet Excel.

Dans la boîte de dialogue Compléments, sélectionnez Complément Solver, puis cliquez sur OK.

Dans la boîte de dialogue Options Excel, cliquez sur Compléments.

Dans la zone de liste déroulante Gérer, sélectionnez Compléments Excel, puis cliquez sur Atteindre.

Une fois que vous avez activé le complément Solveur, Excel installe automatiquement ce dernier si ce n’est pas déjà fait et la commande Solveur est ajoutée dans le groupe Analyse sous l’onglet Données du Ruban.

Sous L'éditeur VBA:

Avant de pouvoir utiliser les fonctions VBA du Solveur dans Visual Basic Editor, vous devez établir une référence au complément Solveur.

Dans Visual Basic Editor, après avoir activé un module, cliquez sur Références dans le menu Outils,

puis sélectionnez Solveur sous Références disponibles.

Si Solveur n’apparaît pas sous Références disponibles, cliquez sur Parcourir, puis ouvrez Solver.xlam dans le sous-dossier: \Program Files\Microsoft Office\Office14\Library\SOLVER.

J'ai rajouté des cellules dans deux colonnes intermédiaires utilisées par le solveur: colonnes C et D.
J'ai rajouté des cellules dans la ligne 5 qui contiennent la capacité max des commandes par les clients.

Je suis novice en matière de solveur, je ne sais pas si j'ai utilisé ni les bons paramètres pour le solveur ni surtout la bonne méthode, mais ça a l'air de donner des résultats pas trop incohérents.

NB: il est apparu quelquefois lors du premier calcul une fenêtre 'étrange'. En cliquant sur 'continuer', la macro poursuit normalement son traitement et cette fenêtre ne surgit plus par la suite.

Nota: macro élaborée sous Excel 10 !

Rem: j'ai joint différents jeux de valeurs de capacité d'usines pour tester la vitesse. Les vitesses sont ceux de ma bien bien vieille machine maintenant (2007 - Intel Core 2 CPU 6420 @ 2.13GHz - 2 Go mémoire)

job75 · 9 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonsoir à tous,

Il me semble que le problème est très simple : on trie sur la colonne B et on somme les valeurs tant que la somme est inférieure à bj :

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, plage As Range, bj, i&
Set r = [E5:N5] 'à adapter
If Intersect(Target, r) Is Nothing Then Exit Sub
Cancel = True
Set plage = Range("B3", Cells(Rows.Count, 2).End(xlUp)) 'à adapter
If plage.Row < 3 Then Exit Sub
Application.ScreenUpdating = False
r.Resize(Rows.Count - r.Row + 1).ClearContents 'RAZ
plage.Offset(, -1).Resize(, 2).Sort plage, Header:=xlNo 'tri colonne B
For Each r In r
  bj = r(-1)
  For i = 1 To plage.Count
    If Application.Sum(plage.Resize(i)) > bj Then Exit For
  Next
  r = i - 1
  r(2).Resize(i - 1) = plage(1, 0).Resize(i - 1).Value
Next
plage.Offset(, -1).Resize(, 2).Sort plage(1, 0), Header:=xlNo 'tri colonne A
End Sub

J'ai choisi le double-clic pour lancer la macro, on peut utiliser toute autre méthode.

Fichier joint.

Edit : précision : sous la plage jaune s'affichent les numéros des clients.

A+

mapomme · 9 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonjour à tous, job75

job75 à dit:
Bonsoir à tous,

Il me semble que le problème est très simple : on trie sur la colonne B et on somme les valeurs tant que la somme est inférieure à bj :

Il me semble bien aussi! Honte à moi! (néanmoins, ça m'a permis de mieux comprendre la manipulation du solveur - on se console comme on peut)

job75 · 9 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Re,

Sur un grand tableau cette macro doit être plus rapide grâce à la variable s :

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, plage As Range, bj, s, i&
Set r = [E5:N5] 'à adapter
If Intersect(Target, r) Is Nothing Then Exit Sub
Cancel = True
Set plage = Range("B3", Cells(Rows.Count, 2).End(xlUp)) 'à adapter
If plage.Row < 3 Then Exit Sub
Application.ScreenUpdating = False
r.Resize(Rows.Count - r.Row + 1).ClearContents 'RAZ
plage.Offset(, -1).Resize(, 2).Sort plage, Header:=xlNo 'tri colonne B
For Each r In r
  bj = r(-1): s = 0
  For i = 1 To plage.Count
    s = s + Val(plage(i))
    If s > bj Then Exit For
  Next
  r = i - 1
  r(2).Resize(i - 1) = plage(1, 0).Resize(i - 1).Value
Next
plage.Offset(, -1).Resize(, 2).Sort plage(1, 0), Header:=xlNo 'tri colonne A
End Sub

Fichier (2).

A+

mapomme · 10 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonsoir à tous,

Un essai par formules (grâce à une précédente astuce empruntée à R@chid ).

nb: Les formules pour "di triées" et "Facteur Id" sont des formules matricielles.

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

edit : modifié fichier pour ôter la macro non utilisée.

mapomme · 10 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

(re)Bonsoir à tous,

Un dernier essai par formules qui liste aussi les clients.

nb: Les formules pour "di triées" et "Facteur Id" et " Σ Clients i" sont des formules matricielles.

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

edit : modifié fichier pour ôter la macro non utilisée.

mapomme · 10 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

(re) Bonjour à tous,

La méthode par solveur (v1) et la méthode de la somme des n plus petites valeurs semblent toutes les deux maximiser le nombre de clients pour une capacité donnée. Cependant la méthode du solveur maximise aussi le total des demandes des clients sélectionnés.

Les 20 clients ont pour demandes:
Client i: 1- 2- 3- 4- 5- 6- 7- 8- 9- 10-11-12-13-14-15-16-17-18-19-20
di......: 12-18-18-19-26-21-18-19-18-11-22-21-13-19-13-14-22-17-27-28

ou encore en triant:
Client i: 10-1 -13-15-16-18-2 -3 -7 -9 -4 -8 -14-6 -12-11-17-5 -19-20
di......: 11-12-13-13-14-17-18-18-18-18-19-19-19-21-21-22-22-26-27-28

Pour une capacité cible de 88,5:
La méthode "somme petites valeurs" donnent les clients: 10 - 1 - 13 - 15 - 16 - 18
soit un total de demandes égal à : 80

Pour la même capacité cible, la méthode solveur donnent les clients: 3-10-13-15-16-18
soit un total de demandes égal à : 86

Voir exemples joints:

job75 · 10 Décembre 2012

Re : Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Bonjour mapomme, le forum,

C'est un avantage indéniable du solveur de pouvoir maximiser à la fois le nombre de clients et la somme des demandes.

Peux-tu tester cependant la durée d'exécution sur un grand tableau ?

J'ai testé ma version (2) sur 60 000 lignes et des valeurs bj en E3:N3 multipliées par 3000.

Sur Win XP/Excel 2003 la durée d'exécution est de 2,70 secondes.

Avec cette version (3) la durée d'exécution passe à 1,22 seconde :

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, plage As Range, ub As Long, S, i As Long, n As Variant
Set r = [E5:N5] 'à adapter
If Intersect(Target, r) Is Nothing Then Exit Sub
Cancel = True
Set plage = Range("B3", Cells(Rows.Count, 2).End(xlUp)) 'à adapter
Application.ScreenUpdating = False
r.Resize(Rows.Count - r.Row + 1).ClearContents 'RAZ
If plage.Row < 3 Then Exit Sub
plage.Offset(, -1).Resize(, 2).Sort plage, Header:=xlNo 'tri colonne B
ub = Application.Count(plage)
If ub < 2 Then Exit Sub
S = plage.Resize(ub) 'matrice
For i = 2 To ub
  S(i, 1) = S(i, 1) + S(i - 1, 1)
Next
For Each r In r
  n = Application.Match(r(-1), S)
  If IsNumeric(n) Then
    r = n
    r(2).Resize(n) = plage(1, 0).Resize(n).Value
  End If
Next
plage.Offset(, -1).Resize(, 2).Sort plage(1, 0), Header:=xlNo 'tri colonne A
End Sub

A+

Déterminer le nombre maximal de valeurs inférieures à une cellule donnée.

Nnaks

XLDnaute Nouveau

Pièces jointes

Staple1600

XLDnaute Barbatruc

Nnaks

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Discussions similaires