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

chaelie2015 · 25 Octobre 2023

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.

job75 · 26 Octobre 2023

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+

job75 · 26 Octobre 2023

J'ai aussi ajouté D5 dans la macro Worksheet_Change.

chaelie2015 · 26 Octobre 2023

Bonjour JOB, le forum
Merci pour la réponse, maisquand je clic sur le bouton 'RAFRAICHIR' j'ai cette erreur ?

job75 · 26 Octobre 2023

Je viens de tester sur le fichier du post #2, chez moi il n'y a pas d'erreur, téléchargez de nouveau le fichier.

Bien sûr le Dictionary n'est pas accepté sur MAC.

chaelie2015 · 26 Octobre 2023

Re
Je révoque ce que j'ai fait au post #4, car c'est une erreur de ma part. J'ai désactivé ce code 'Private Sub Workbook_Open()
Merci beaucoup pour la réponse, c'est exactement ce que je cherchais.
A+

job75 · 26 Octobre 2023

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".

chaelie2015 · 26 Octobre 2023

Re
Exact, c'est BI7, pas BI17.(le classement des lots attribués à chaque soumissionnaire)

chaelie2015 · 26 Octobre 2023

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+

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é

job75 · 26 Octobre 2023

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.

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.

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.

chaelie2015 · 26 Octobre 2023

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.

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

job75 · 26 Octobre 2023

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.

chaelie2015 · 26 Octobre 2023

Bonsoir Job75, le forum
Après avoir vérifié, tout est impeccable.
NB: Je constate une erreur dans le classement de ma part sur la feuille 'souhaité' dans le fichier, et je m'excuse pour cela.
A+

Effectuez une recherche sur Excel Downloads...

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

chaelie2015

XLDnaute Accro

Pièces jointes

job75

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

chaelie2015

XLDnaute Accro

job75

XLDnaute Barbatruc

chaelie2015

XLDnaute Accro

job75

XLDnaute Barbatruc

chaelie2015

XLDnaute Accro

chaelie2015

XLDnaute Accro

Pièces jointes

job75

XLDnaute Barbatruc

chaelie2015

XLDnaute Accro

job75

XLDnaute Barbatruc

Pièces jointes

chaelie2015

XLDnaute Accro

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums