Choix aléatoires avec 2 conditions

[laure-33]

Bonjour à tous,

Je suis confrontée à un problème et malgré les nombreux posts sur ce sujet, je ne trouve pas ma solution...

Peut être que vous pourriez m'aider, voici mon problème :

colonne A : pièces (au nombre de 4 : chambre, salle de bain, toilette, cuisine)
colonne B : référence de d'objet (sous la forme "04239188/1-1XXX3CR2-NOM OBJET)
colonne C : état du stock (OK : en stock ; KO : pas en stock)

Sur 50 pièces, il me faudrait 10% de celles-ci (5 pièces) avec comme première condition : 50% d'objets en stock (OK) et 50% non en stock (KO) (colonne c), sans doublon.
Ma deuxième condition et d'avoir une répartition en pourcentage pour ces différentes pièces. chambre : 36% ; SDB : 26% ; toilette : 12% ; cuisine : 25%).
Je souhaiterais donc que mon échantillon représente 10% de l'ensemble des pièces référencées; qu'il y ait autant de pièces en stock ou non; que cet échantillon ait une répartition selon les taux cités ci-dessus.

Je crains qu'il faille passer par VBA pour ma deuxième condition ... Si c'est le cas, je crains de ne pas y comprendre grand chose et de faire un copié-collé adapté à mon cas. A vrai dire, la première condition est la plus importante.

Vos avis ou solutions me seraient d'une grande aide et je vous en remercie par avance ,

Laure.

 

[Victor21]

Bonjour, laure-33.

Mon avis est que le problème est soit mal posé, soit insoluble (ou pire : les deux ;( ) :
1° répartition : obtenir un nombre entier représentant 50% de 5 me semble compliqué dans le système décimal.
2° répartition : Même problème pour obtenir un nombre entier représentant respectivement 36%; 26%; 25% et 12% de 5.
A noter que la somme des pourcentages ne fait pas 100%.

 

[laure-33]

Bonjour Victor21,

Je vous remercie pour cette remarque et en effet j’aurais du préciser que le fichier joint est pour exemple et que mon échantillon initial représente 420 références.
Donc 10% représente 42 références.
Pour la deuxième condition, cela ne tombe pas sur 100% mais à 99%, admettons que les toilettes doivent représenter 13%. Ce taux n’a pas besoin d’être extrêmement précis.

Pensez-vous que cela est possible par des fonctions Excel ?

Merci

 

[Victor21]

Re, Laure-33.

C'est possible sans VBA, mais un peu fastidieux : pour 420 références
Une colonne pour les réf des chambres OK, triée aléatoirement, prendre les 8 premières lignes arrondi de (420/2/10*36%) = 8
Une colonne pour les réf des chambres KO, triée aléatoirement, prendre les 8 premières lignes
Une colonne pour les réf des salles de bain OK, triée aléatoirement, prendre les 6 premières lignes
Une colonne pour les réf des salles de bain KO triée aléatoirement, prendre les 6 premières lignes
Une colonne pour les réf des toilettes OK, triée aléatoirement, prendre les 6 premières lignes
Une colonne pour les réf des toilettes KO triée aléatoirement, prendre les 6 premières lignes
Une colonne pour les réf des cuisines OK, triée aléatoirement, prendre les 3 premières lignes
arrondi de (420/2/10*12.5%)=3
Une colonne pour les réf des cuisines KO triée aléatoirement, prendre les 3 premières lignes

 

[laure-33]

Re Victor21 !

En effet votre logique est bien mieux qu'une fonction excel même si elle est fastidieuse !

Un Grand MERCI pour votre aide !

 

[Rouge]

Bonjour,

Je vois que le sujet est passé en résolu, de mon côté, j'avais commencé à y travailler mais j'ai dû interrompre mon travail.
Je soumets tout de même ma proposition, "solution VBA", il ne reste plus qu'à cliquer sur le bouton (pour faire des essais, j'ai remplacé les références par des n°)

Option Compare Text
    Dim DerLig As Long, Der_OK_KO As Long, Der_KO As Long, Lig As Long, Qte As Long
    Dim Nb_OK_KO As Long
    Dim Stock As String, Piece As String, Col_Qte As String, Col As String
    
Sub Repartition()
    Application.ScreenUpdating = False
    DerLig = Range("A" & Rows.Count).End(xlUp).Row
    
    Randomize
    For o = 1 To 2
        Select Case o
            Case 1
                Stock = "OK"
                For s = 1 To 4
                    Select Case s
                        Case 1
                            Piece = "chambre"
                            Col = "I"
                            Col_Qte = "M"
                        Case 2
                            Piece = "cuisine"
                            Col = "H"
                            Col_Qte = "L"
                        Case 3
                            Piece = "toilette"
                            Col = "G"
                            Col_Qte = "K"
                        Case 4
                            Piece = "salle de bain"
                            Col = "F"
                            Col_Qte = "J"
                    End Select
                    Traitement
                Next s
            Case 2
                Stock = "KO"
                For s = 1 To 4
                    Select Case s
                        Case 1
                            Piece = "chambre"
                            Col = "M"
                            Col_Qte = "M"
                        Case 2
                            Piece = "cuisine"
                            Col = "L"
                            Col_Qte = "L"
                        Case 3
                            Piece = "toilette"
                            Col = "K"
                            Col_Qte = "K"
                        Case 4
                            Piece = "salle de bain"
                            Col = "J"
                            Col_Qte = "J"
                    End Select
                    Traitement
                Next s
        End Select
    Next o
 
    Range("E11:E1000,S1:U1000").ClearContents
End Sub

Sub Traitement()
    Range("E11:E1000").ClearContents
    Range("S1:U1000").ClearContents
    Range("A1:C1").AutoFilter
    ActiveSheet.Range("A1:C" & DerLig).AutoFilter Field:=1, Criteria1:=Piece
    ActiveSheet.Range("A1:C" & DerLig).AutoFilter Field:=3, Criteria1:=Stock
    Der_OK_KO = Range("_FilterDataBase").Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
    Range("_FilterDataBase").Resize(, 3).SpecialCells(xlCellTypeVisible).Copy
    Range("S1").Select
    ActiveSheet.Paste
    ActiveSheet.ShowAllData
    Qte = Cells(3, Col_Qte)
    
    Lig = 11
    For i = 2 To Qte + 1
        Nb_OK_KO = Int((Der_OK_KO * Rnd) + 2)
        If Nb_OK_KO > Der_OK_KO Then Nb_OK_KO = Der_OK_KO
        If Application.WorksheetFunction.CountIf(Range("E11:E" & Lig - 1), Nb_OK_KO) = 0 Then
            Cells(Lig, "E") = Nb_OK_KO
            Cells(Lig, Col) = Cells(Nb_OK_KO, "T")
            Lig = Lig + 1
        Else
            i = i - 1
        End If
    Next i
End Sub

Cdlt

 

[laure-33]

Bonjour Rouge,

Je vous remercie pour votre travail qui a dû vous demander beaucoup de temps !
Je n’y comprend pas grand chose en VBA et donc la première solution fut beaucoup plus compréhensible pour moi.

Merci encore !!

 

[Rouge]

Bonjour,

"Je n’y comprend pas grand chose en VBA et donc la première solution fut beaucoup plus compréhensible "
Qu'est-ce qui vous intéresse en priorité, obtenir la réponse à vos questions (peut importe la façon dont on l'obtient) ou bien vouloir comprendre à tout prix le code utilisé?

Il vous arrive, je suppose, d'utiliser des calculettes (ou autre moyens modernes) pour faire des petits calculs pour votre travail ou pour vos activités personnelles, vous vous contentez du résultat sans savoir ce qui se passe en arrière plan, et pourtant, c'est bien du code informatique qui fait marcher tout ça, et c'est le dernier de vos soucis de savoir comment ça fonctionne, et bien là c'est pareil, pourquoi refuser d'utiliser un outil automatisé sous prétexte que vous ne savez pas comme c'est fait.

Dans le même raisonnement, vous préférez les formules excel, mais vous posez-vous la question de savoir comment fonctionne une fonction telle que SOMME, INDEX, EQUIV, etc.. Non! et pourtant vous les utilisez.

L'avantage du VBA, quand on commence à le maîtriser un peu, est sa simplicité d'utilisation, hormis qu'il soit en anglais,, mais ça c'est commun à tous les langages.

Pour apprendre le VBA, il y a pleins de livres qui traitent de ce sujet, vous en trouverez en librairies ou en grandes surface.
Il y a aussi, dans excel, l'enregistreur de macros. Lorsque vous le mettez en route, toutes les actions que vous faites s'enregistrent dans un module. Après avoir arrêter l'enregistrement, Il suffit par la suite de taper ALT+ F11 pour accéder au module et lire le code ainsi enregistré. Il est assez aisé d'en comprendre la syntaxe puisque il n'a fait que transcrire les opérations que vous venez de faire auparavant.
Enfin il y a internet et plus particulièrement les forums comme celui-ci, qui sont une mine d'informations.

Cdlt

 

[Rouge]

Suite à vos remarques et en complément de ce que je viens de dire précédemment.
Je vous retourne le fichier avec des commentaires dans le code pour que vous puissiez essayer de comprendre. Ce qui m'a permis de corriger une petite anomalie, Donc ce fichier annule et remplace le précédent.

 

[Victor21]

Bonsoir, @Rouge

Loin de moi l'idée de défendre "ma" proposition qui est très loin d'être la meilleure, je réagis toutefois sur votre commentaire :

[...]Qu'est-ce qui vous intéresse en priorité, obtenir la réponse à vos questions (peut importe la façon dont on l'obtient) ou bien vouloir comprendre à tout prix le code utilisé?
[...]
pourquoi refuser d'utiliser un outil automatisé sous prétexte que vous ne savez pas comme c'est fait.
[...]

Que se passera-t-il si pour une raison quelconque le code que vous ne maîtrisez pas plante, et bien entendu au moment précis où, selon les principes décrits par Murphy, votre N+1 a un besoin subit, irrépressible et inextinguible de cette étude ?
Bien sûr qu'on ne peut pas tout maîtriser, mais autant choisir le risque minimum, et rester maître de ce pourquoi on est payé.

En tous cas, bravo pour votre solution, et pour les commentaires que vous avez pris la peine de joindre.

 

[Rouge]

Bonsoir Victor21,

Vous avez entièrement raison de penser EXCEL avant de penser VBA.
Il nullement question de vouloir imposer ma proposition, loin de là, s'en est une en plus de la vôtre et c'est surtout pour éviter toutes les manipulations, c'est aussi pour la rapidité d'exécution et aussi que je trouvais le sujet intéressant.

"Que se passera-t-il si pour une raison quelconque le code que vous ne maîtrisez pas plante, et bien entendu au moment précis où, selon les principes décrits par Murphy, votre N+1 a un besoin subit, irrépressible et inextinguible de cette étude ?
Bien sûr qu'on ne peut pas tout maîtriser, mais autant choisir le risque minimum, et rester maître de ce pourquoi on est payé."

Mais alors, on n'écrit plus de code. Que fait-on quand Windows ou Excel plantent?

Entre autre, on peut faire la comparaison avec une voiture, on s'assied, on démarre et on roule, on fait confiance au constructeur. Pour ma part, je ne maîtrise pas la mécanique et ne suis pas à l'abri d'une panne, dois-je pour autant laisser la voiture au garage?

Merci pour l'appréciation.

Bien cordialement




.

 

[Victor21]

Re, @Rouge

[...]Mais alors, on n'écrit plus de code.

Si on a écrit le code, il est permis de penser qu'on le maîtrise. En revanche si on utilise un code incompréhensible pondu sur le net par un inconnu dont les connaissances et la fiabilité nous sont étrangères, autant oser piloter à très grande vitesse une voiture de course, bricolée par un amateur, sans ceinture de sécurité, sans même savoir s'il a pensé à mettre des freins.