XL 2013 (RESOLU) Classement par zone (de 01 - 50) selon la cellule D5

chaelie2015

chaelie2015

XLDnaute Accro
Bonsoir Forum
Dans la feuille 'BdD CEO', j'ai créé 50 plages nommées, de 'Zone_Lot_01' à 'Zone_Lot_50'. Chacune de ces zones est composée de Cinque colonnes distinctes.
La première colonne contient le classement des soumissionnaires,
la deuxième colonne affiche le montant de chaque soumissionnaire,
la troisième colonne présente l'écart entre le 1er et le 2ème soumissionnaire uniquement,
la quatrième colonne indique l'attribution,
et enfin, la cinquième colonne répertorie le classement des lots attribués à chaque soumissionnaire, par exemple : Job 6 a remporté 03 lots, classés dans l'ordre Lot_02 (le 1er), Lot_03 (le 2ème) et Lot_01 (le 3ème), consultez la feuille 'Matrice d'écart' pour plus de détails).

Maintenant, l'objectif est d'afficher dans la première cellule de la 4ème colonne de chaque Zone en fonction de la valeur de la cellule D5 (nombre de lots max à attribuer, compris entre 1 et 10) de la même feuille; les données suivants :
Attribution par écart.
Attribution après alignement.
Nombre de lots saturés

En cas de situation où un soumissionnaire propose la meilleure offre pour plusieurs lots, un calcul de l'écart entre les prix des deux premiers soumissionnaires les moins disants est effectué (ce calcul se trouve dans la feuille 'Matrice d'Écart').

Le soumissionnaire classé en première position se verra attribuer le lot où l'écart calculé est le plus grand. Cependant, si le nombre limite de lots à attribuer (Le MAX est défini dans la cellule D5) est atteint, il est mentionné 'Nombre de lots saturés' dans la cellule correspondante.

Pour les lots restants, où l'écart est le plus faible, le soumissionnaire dont l'offre est en deuxième position peut être invité à aligner son offre de prix sur celle du soumissionnaire le moins disant, déjà attributaire de deux lots ‘valeur de D5). Dans ce cas, 'Attribution après alignement' est affichée dans la deuxième cellule de la 4ème colonne de chaque zone.

Vous trouverez dans le fichier joint (Usine à Gaz ;))un exemple comportant 15 soumissionnaires ayant soumissionné pour 07 lots, avec les résultats souhaités déjà inscrits dans les zones correspondantes.
J'espère que ces informations sont explicites et contribueront à résoudre mon problème🤞.
Merci d'avance pour votre aide.
 

Pièces jointes

  • BdD CEO V9 CHARLIE du 25 10 2023.xlsm
    189.2 KB · Affichages: 4
Solution
job75
La macro d'après ce que j'ai compris mais elle ne donne pas les attributions souhaitées :
VB: 
Sub Tri()
    Dim Nom, limite, nlot%, coldest%, nlig%, d As Object, tablo, j%, i%, lot$, x$, k%
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'désactive les évènements
    Application.Calculation = xlCalculationManual 'calcul manuel
    Nom = [B7].Resize(50) 'matrice
    limite = [D5]
    For nlot = 1 To 50
        coldest = 6 * nlot + 51
        Cells(7, coldest).Resize(50) = Nom
        Cells(7, coldest + 1).Resize(50) = Cells(7, nlot + 3).Resize(50).Value
        Cells(7, coldest).Resize(50, 2).Sort Cells(7, coldest + 1), xlAscending, Header:=xlNo 'tri
        nlig = Application.Count(Cells(7, coldest +...
Trier par date Trier par votes
job75

job75

XLDnaute Barbatruc
Bonjour chaelie2015, le forum,

Pour remplir les Attributions il faut compléter la macro Tri, elle utilise maintenant le Dictionary :
VB: 
Sub Tri()
    Dim Nom, limite, d As Object, nlot%, coldest%, nlig%, x$, i%
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'désactive les évènements
    Application.Calculation = xlCalculationManual 'calcul manuel
    Nom = [B7].Resize(50) 'matrice
    limite = [D5]
    Set d = CreateObject("Scripting.Dictionary")
    For nlot = 1 To 50
        coldest = 6 * nlot + 51
        Cells(7, coldest).Resize(50) = Nom
        Cells(7, coldest + 1).Resize(50) = Cells(7, nlot + 3).Resize(50).Value
        Cells(7, coldest).Resize(50, 2).Sort Cells(7, coldest + 1), xlAscending, Header:=xlNo 'tri
        nlig = Application.Count(Cells(7, coldest + 1).Resize(50))
        If nlig < 50 Then Cells(7, coldest).Offset(nlig).Resize(50 - nlig) = "" 'efface les noms sans montant
        Cells(7, coldest + 2).Resize(50, 2) = ""
        If nlig > 1 Then Cells(7, coldest + 2) = Cells(8, coldest + 1) - Cells(7, coldest + 1)
        If nlig Then
            If Cells(7, coldest + 4) <= limite Then
                Cells(7, coldest + 3) = "Attribution sur écart."
                x = Cells(7, coldest)
                d(x) = d(x) + 1 'compte les attributions
            Else
                Cells(7, coldest + 3) = "Nombre de lots saturés"
            End If
        End If
    Next nlot
    '---complète les attributions---
    For nlot = 1 To 50
        coldest = 6 * nlot + 51
        If Cells(7, coldest + 3) = "Nombre de lots saturés" Then
            For i = 8 To 56
                x = Cells(i, coldest)
                If x = "" Then Exit For
                If d(x) < limite Then
                    Cells(i, coldest + 3) = "Attribution après alignement."
                    d(x) = d(x) + 1 'complète le comptage
                    Exit For
                Else
                    Cells(i, coldest + 3) = "Nombre de lots saturés"
                End If
            Next
        End If
    Next nlot
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'réactive les évènements
End Sub
A+
 

Pièces jointes

  • BdD CEO V9 CHARLIE du 25 10 2023.xlsm
    188.4 KB · Affichages: 3
Vote positif 0 Vote négatif
chaelie2015

chaelie2015

XLDnaute Accro
Bonjour JOB, le forum
Merci pour la réponse, maisquand je clic sur le bouton 'RAFRAICHIR' j'ai cette erreur ?
CHARLIE 1.png
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Pour ceux qui n'auraient pas compris il est utile de préciser à quoi servent les valeurs comme BI7 (3).

Si cette valeur est inférieure ou égale à la limite D5 on entre en BH7 le texte "Attribution par écart.".

Dans le cas contraire on y entre "Nombre de lots saturés".
 
Dernière édition:
Vote positif 0 Vote négatif
chaelie2015

chaelie2015

XLDnaute Accro
job75 à dit:
Bonjour chaelie2015, le forum,

Pour remplir les Attributions il faut compléter la macro Tri, elle utilise maintenant le Dictionary :
VB: 
Sub Tri()
    Dim Nom, limite, d As Object, nlot%, coldest%, nlig%, x$, i%
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'désactive les évènements
    Application.Calculation = xlCalculationManual 'calcul manuel
    Nom = [B7].Resize(50) 'matrice
    limite = [D5]
    Set d = CreateObject("Scripting.Dictionary")
    For nlot = 1 To 50
        coldest = 6 * nlot + 51
        Cells(7, coldest).Resize(50) = Nom
        Cells(7, coldest + 1).Resize(50) = Cells(7, nlot + 3).Resize(50).Value
        Cells(7, coldest).Resize(50, 2).Sort Cells(7, coldest + 1), xlAscending, Header:=xlNo 'tri
        nlig = Application.Count(Cells(7, coldest + 1).Resize(50))
        If nlig < 50 Then Cells(7, coldest).Offset(nlig).Resize(50 - nlig) = "" 'efface les noms sans montant
        Cells(7, coldest + 2).Resize(50, 2) = ""
        If nlig > 1 Then Cells(7, coldest + 2) = Cells(8, coldest + 1) - Cells(7, coldest + 1)
        If nlig Then
            If Cells(7, coldest + 4) <= limite Then
                Cells(7, coldest + 3) = "Attribution sur écart."
                x = Cells(7, coldest)
                d(x) = d(x) + 1 'compte les attributions
            Else
                Cells(7, coldest + 3) = "Nombre de lots saturés"
            End If
        End If
    Next nlot
    '---complète les attributions---
    For nlot = 1 To 50
        coldest = 6 * nlot + 51
        If Cells(7, coldest + 3) = "Nombre de lots saturés" Then
            For i = 8 To 56
                x = Cells(i, coldest)
                If x = "" Then Exit For
                If d(x) < limite Then
                    Cells(i, coldest + 3) = "Attribution après alignement."
                    d(x) = d(x) + 1 'complète le comptage
                    Exit For
                Else
                    Cells(i, coldest + 3) = "Nombre de lots saturés"
                End If
            Next
        End If
    Next nlot
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'réactive les évènements
End Sub
A+
Cliquez pour agrandir...
Bonjour Job
Après de nombreuses vérifications et simulations de cas, j'ai constaté que je dois ajuster la procédure d'attribution de la manière suivante :
En ce qui concerne la répartition des lots lorsque le soumissionnaire est saturé, l'idée est de maintenir le lot non attribué, plutôt que de repartir depuis le début du premier lot. Dans l'exemple que vous avez fourni, supposons que D5=1. En se basant sur la matrice, dans la première colonne (N°1), on a 'Lot_02 - Attribution sur écart' pour JOB 2, 'Lot_03 - Attribution sur écart' pour JOB 4, et 'Lot_06 - Attribution sur écart' pour JOB 7. Par conséquent, JOB 2, JOB 4 et JOB 7 sont saturés.

Dans ce cas, on passe à la deuxième colonne de la matrice. Comme JOB 2 est saturé, l'idée est d'attribuer le 'LOT_5 - Attribution après alignement' au deuxième soumissionnaire dans le Lot_5, soit JOB 1. Cela s'applique également au 'LOT_7'. Par exemple, JOB 4 est saturé, le 'LOT_7' peut être attribué au deuxième soumissionnaire disponible s'il n'est pas saturé malheureusement JOB 7 aussi est saturé je passe a JOB 11,'Attribution après alignement' et ainsi de suit (colonne par colonne de la matrice) .
Merci et désolé
 

Pièces jointes

  • BdD CEO V9 JOB OK du 25 10 2023.xlsm
    196.7 KB · Affichages: 3
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
chaelie2015 à dit:
En se basant sur la matrice, dans la première colonne (N°1), on a 'Lot_02 - Attribution sur écart' pour JOB 2, 'Lot_03 - Attribution sur écart' pour JOB 4, et 'Lot_06 - Attribution sur écart' pour JOB 7. Par conséquent, JOB 2, JOB 4 et JOB 7 sont saturés.
Cliquez pour agrandir...
Je ne comprends pas : JOB 2 n'est concerné sur aucun lot et JOB 4 est concerné sur Lot_04.
chaelie2015 à dit:
Dans ce cas, on passe à la deuxième colonne de la matrice. Comme JOB 2 est saturé, l'idée est d'attribuer le 'LOT_5 - Attribution après alignement' au deuxième soumissionnaire dans le Lot_5, soit JOB 1.
Cliquez pour agrandir...
Pour Lot_05 le 2ème soumissionnaire n'est pas JOB 1 mais JOB 11 !!!

Merci de corriger pour que je sois sûr de comprendre ce que vous voulez faire.
 
Vote positif 0 Vote négatif
chaelie2015

chaelie2015

XLDnaute Accro
job75 à dit:
Je ne comprends pas : JOB 2 n'est concerné sur aucun lot et JOB 4 est concerné sur Lot_04.

Pour Lot_05 le 2ème soumissionnaire n'est pas JOB 1 mais JOB 11 !!!

Merci de corriger pour que je sois sûr de comprendre ce que vous voulez faire.
Cliquez pour agrandir...
Re
J'ai ajusté les montants à des fins d'illustration, ce qui signifie que le fichier n'est pas le même que précédemment.
J'ai inclus une nouvelle feuille dans le document pour observer les résultats souhaités,
je m'excuse de ne pas avoir prévenu que les données (les montants) dans le fichier ont été modifiées
Merci
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
La macro d'après ce que j'ai compris mais elle ne donne pas les attributions souhaitées :
VB: 
Sub Tri()
    Dim Nom, limite, nlot%, coldest%, nlig%, d As Object, tablo, j%, i%, lot$, x$, k%
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'désactive les évènements
    Application.Calculation = xlCalculationManual 'calcul manuel
    Nom = [B7].Resize(50) 'matrice
    limite = [D5]
    For nlot = 1 To 50
        coldest = 6 * nlot + 51
        Cells(7, coldest).Resize(50) = Nom
        Cells(7, coldest + 1).Resize(50) = Cells(7, nlot + 3).Resize(50).Value
        Cells(7, coldest).Resize(50, 2).Sort Cells(7, coldest + 1), xlAscending, Header:=xlNo 'tri
        nlig = Application.Count(Cells(7, coldest + 1).Resize(50))
        If nlig < 50 Then Cells(7, coldest).Offset(nlig).Resize(50 - nlig) = "" 'efface les noms sans montant
        Cells(7, coldest + 2).Resize(50, 3) = ""
        If nlig > 1 Then Cells(7, coldest + 2) = Cells(8, coldest + 1) - Cells(7, coldest + 1)
    Next nlot
    '---Attributions---
    Set d = CreateObject("Scripting.Dictionary")
    tablo = Sheets("Matrice d'écart").[BC2].Resize(50, 10)
    For j = 1 To 10
        For i = 1 To 50
            If Nom(i, 1) = "" Then Exit For
            lot = tablo(i, j)
            If lot <> "" Then
                coldest = Application.Match(lot, Rows(4), 0) + 1
                If j <= limite Then
                    Cells(7, coldest + 3) = "Attribution par écart."
                    x = Cells(7, coldest)
                    d(x) = d(x) + 1 'comptage
                Else
                    Cells(7, coldest + 3) = "Nombre de lots saturés"
                    For k = 8 To 56
                        x = Cells(k, coldest)
                        If x = "" Then Exit For
                        If d(x) < limite Then
                            Cells(k, coldest + 3) = "Attribution après alignement."
                            d(x) = d(x) + 1 'complète le comptage
                            Exit For
                        Else
                            Cells(k, coldest + 3) = "Nombre de lots saturés"
                        End If
                    Next k
                End If
            End If
    Next i, j
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'réactive les évènements
End Sub
J'ai effacé les formules des cellules telles que BI7, elles sont devenues inutiles.
 

Pièces jointes

  • BdD CEO V10 JOB OK du 25 10 2023.xlsm
    200 KB · Affichages: 4
Vote positif 0 Vote négatif
Solution

Discussions similaires

L
  • Résolu(e)
XL 2016 Pb publipostage conditionnelle avec base de données Excel
Réponses
1
Affichages
246
Linda42
L
chaelie2015
  • Résolu(e)
XL 2013 (RESOLU) Modification Manuelle et changement Automatique
Réponses
9
Affichages
697
job75
job75
D
  • Question
Microsoft 365 Calculer l'écart individuel de chaque n° en fonction d'une règle définie
Réponses
4
Affichages
461
drenek35
D
Z
  • Résolu(e)
Microsoft 365 (résolu) Etablir un classement avec des ex-aequo sur la base d'une autre valeur
Réponses
2
Affichages
275
zedude
Z
chaelie2015
  • Résolu(e)
XL 2013 (RESOLU) Affichage des en-têtes de tableau selon l'ordre
2
Réponses
20
Affichages
1 K
chaelie2015
chaelie2015
ralph45
  • Question
Microsoft 365 [RESOLU] Gestion en masse de fichiers JPG selon sélection sur un tableur
Réponses
4
Affichages
332
ralph45
ralph45
S
  • Question
Microsoft 365 Référer à la première cellule non vide en haut
Réponses
3
Affichages
432
ShrekL
S
S
  • Question
Microsoft 365 A partir de la 1ere cellule non vide, récupérer le mois
Réponses
4
Affichages
151
Sissou71
S
D
  • Question
XL 2016 Classement BLIND Test 30 équipes
Réponses
5
Affichages
534
Modeste geedee
Modeste geedee
G
  • Résolu(e)
XL 2021 départage æquo classement sportif
Réponses
11
Affichages
604
gigi71960
G

Membres actuellement en ligne

2 membres et 32 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
315 087
Messages
2 116 084
Membres
112 655
dernier inscrit
fannycordi