Tirage sur un colonne sans doublons (grd base données )

julie211 · 30 Octobre 2014

Bonjour à tous,

Je suis novice en vba et je n'ai pas été capable de trouver une solution à mon problème malgré de nombreuses discussions sur le sujet.

J'ai une grande base données (plus de 100000 lignes) et je voudrais trouver un moyen de tirer aléatoirement et sans doublon un nombre variable sur une colonne (1000 par exemple) pour les travailler indépendamment dans un autre onglet en copiant les colonnes correspondants.

Je vous joint un exemple du format de ma fiche de travail: je voudrais tirer 300 dossiers sur la colonne C ( référence) aléatoire sans doublons parmi plus de 700 dossiers au total, et après copier coller dans un autre onglet ''feuil2" qui est en même format que "feuil1".

comme j'ai une grande base de donnée, il faudrait peut-être optimiser le temps de tourne le macro.

Merci d'avance pour votre aide.

Cordialement,

Julie

mapomme · 5 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour KenDev, Staple1600 et à tous les autres ,

Staple1600 à dit:
Sinon personne pour commenter cet idée qui me trotte dans la tête
PS: En repensant à la question : je pensais à cette piste
1) Copie de l'intégralité des données (sans doublons)
puis suppression de N éléments aléatoirement pour qu'il en reste le nombre demandé

Serait-ce plus ou moins rapide que de tirer N éléments dans une liste ?

C'est juste que j'aimerai avoir vos avis avant de tenter la chose, histoire de ne pas aller titiller VBE en vain.

Stapple1600,

Je ne pense pas qu'il te soit utile d'aller titiller le VBE.
AMHA, un travail quasi analogue a déjà été fait par job75 (que je salue ) dans le post #14, dont j'ai repris le code dans le fichier de comparaison du post #41.

copie des données dans un nouveau classeur auxiliaire
tri aléatoire du tableau (ajout d'une colonne avec formule Alea() puis tri selon cette colonne puis suppression de la colonne ajoutée)
recherche des N premières lignes (avec références distinctes) à l'aide d'un dico pour les mettre dans un tableau
écriture de ce tableau sur la feuille résultat
suppression du classeur auxiliaire

Si comme ton idée trottinante le laisse subodorer, la liste initiale est sans doublon, on pourrait se passer de l'étape 3 et prendre directement les N premières lignes.

A contrario si ton idée trottinette permet les doublons, je pense que si on utilise les outils intégrés d'Excel pour la suppression des doublons, ces outils conserveront toujours la première occurrence. Par conséquent, lors du tirage au sort, on aura des lignes potentiellement jamais choisies. Ce qui n'est pas le cas avec la méthode de job75 ou celle de mapomme.

julie211 · 5 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Hi, tous

Merci bcp pour vos aide, j suis en cours de comprendre vos methodes, vraiment tres magique ac une vitesse trop rapide.

Bonne soirée

Julie

KenDev · 5 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour à tous,

@Julie,

Quand tu indiquais doublons dans ta demande, voulais tu dire :

A) Je ne souhaite pas qu'une ligne soit présente plusieurs fois dans l'échantillon
B) A + La base originale est susceptible de contenir des doublons

Les gens ont planché sur l'une ou l'autre (voire les deux) hypothèse et la différence de temps selon le cas peut être importante, A ayant de bonnes chance d'être plus rapide. Les macros B sont compatibles avec A mais pas l'inverse.

Cordialement

KD

julie211 · 7 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour, tous

Je reviens vers vous sur le meme tab, cette fois, comme j'ai tiré 1000 sur la base, je veux distribuer tous ces 1000 docs selon la categorie a b c (sur colonne c) à 6 personnes, comment distribuer plus equilibré à chacun (chacun presque meme nombre de docs total et presque meme nombre de categorie de doc) et sans doublons docs pr ces 6 personnes (remplir le n° de personnes et le n°groupe sur colonne O et P)?

Merci d avance
Julie

job75 · 7 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour julie211, le fil, le forum,

Si le nombre n de tirages aléatoires est assez grand, les répartitions sont forcément voisines.

En adaptant la macro de mon post #22 :

Code:

Sub TiragesAléatoires()
Dim n&, derlig&, P As Range
n = 300 '1000 'nombre de lignes à retenir, à adapter
Application.ScreenUpdating = False
derlig = Sheets("base").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
With Feuil3 'CodeName de la feuille de restitution
  .Rows("4:" & .Rows.Count).Delete 'RAZ
  Sheets("base").Rows("4:" & derlig).Copy .[A4]
  Set P = Intersect(.UsedRange, .Rows("4:" & derlig))
  With P.Columns(P.Columns.Count + 1) 'colonne auxiliaire à droite
    .Formula = "=RAND()"
    .Value = .Value 'supprime les formules
    .Cells(1) = 0
    Union(P, .Cells).Sort .Cells, xlAscending 'tri
    .EntireColumn.Delete 'suppression de la colonne auxiliaire
  End With
  With .[O5:P10] 'à adapter si nécessaire
    .Value = Sheets("liste").[A5:B10].Value 'à adapter si nécessaire
    .Copy .Rows(1).Resize(.Rows.Count * (Int(n / .Rows.Count) + 1))
  End With
  .Rows(n + 5 & ":" & .Rows.Count).Delete
  n = .UsedRange.Rows.Count 'ajuste la barre de défilement verticale
  .Columns.AutoFit 'ajustement de la largeur des colonnes
  .Activate
End With
End Sub

Voir le fichier joint.

Les répartitions (calculées par SOMMEPROD) sont dans les cellules B1 B2 B3 D1 D2 D3.

Si le résultat ne convient pas bien, relancer la macro.

Edit : bien sûr le nombre de "a", "b" et "c" doivent être les mêmes en colonne C, ce qui n'était pas le cas de votre fichier...

A+

job75 · 7 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Re,

On peut automatiser l'obtention du résultat optimal, il faut se fixer l'écart maximum entre les répartitions :

Code:

Sub TiragesAléatoires()
Dim durée#, n&, ecart As Byte, derlig&, P As Range, Ntirage&
durée = Timer
n = 300 '1000 'nombre de lignes à retenir, à adapter
ecart = 3 'paramétrable
Application.ScreenUpdating = False
derlig = Sheets("base").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
With Feuil3 'CodeName de la feuille de restitution
  .Rows("4:" & .Rows.Count).Delete 'RAZ
  Sheets("base").Rows("4:" & derlig).Copy .[A4]
  Set P = Intersect(.UsedRange, .Rows("4:" & derlig))
  Do 'boucle de recherche du résultat optimal
    Application.Calculation = xlCalculationManual
    With P.Columns(P.Columns.Count + 1) 'colonne auxiliaire à droite
      .Formula = "=RAND()"
      .Value = .Value 'supprime les formules
      .Cells(1) = 0
      Union(P, .Cells).Sort .Cells, xlAscending 'tri
      .EntireColumn.Delete 'suppression de la colonne auxiliaire
    End With
    With .[O5:P10] 'à adapter si nécessaire
      .Value = Sheets("liste").[A5:B10].Value 'à adapter si nécessaire
      .Copy .Rows(1).Resize(.Rows.Count * (Int(n / .Rows.Count) + 1))
    End With
    Ntirage = Ntirage + 1
    Application.Calculation = xlCalculationAutomatic
  Loop While Application.Max(.[B1:L3]) - Application.Min(.[B1:L3]) > ecart
  .Rows(n + 5 & ":" & .Rows.Count).Delete
  n = .UsedRange.Rows.Count 'ajuste la barre de défilement verticale
  .Columns.AutoFit 'ajustement de la largeur des colonnes
  .Activate
End With
MsgBox Ntirage & " tirages" & vbLf & vbLf & _
  "Durée " & Format(Timer - durée, "0.00 \s"), , "Tirages"
End Sub

Edit : j'ai ajouté les Application.Calculation dans la boucle Do pour accélérer.

Fichier (2).

Pour ecart = 3 on trouve une solution au bout d'un temps très variable.

A+

job75 · 8 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour le fil, le forum,

Avec un grand nombre de lignes la durée d'exécution d'un seul tirage est déjà longue donc il faut s'armer de patience.

Sur le fichier joint :

- 60000 lignes pour le tableau source

- n = 1002 (multiple de 6)

- ecart = 10

j'ai obtenu un résultat après 282 tirages et 506 secondes sur Win XP - Excel 2003.

A+

mapomme · 8 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour à tous,

Un essai prenant en compte les Ctrl et groupe associés. Le principe est que chaque Ctrl ait à peu près le même nombre de lignes avec la même proportion de chaque catégorie. La macro est basée sur un dictionnaire de dictionnaires.

Les caractéristiques:
.

aller sur la feuille Accueil, saisir le nombre de lignes de la source à créer et choisir un taux de valeurs de références distinctes puis cliquer sur le bouton Initialiser la source
rem: le format des références est différent de celui du fichier d'origine. la macro fonctionnera avec tout type de références.
sur la feuille Liste, saisir les Ctrl et leur groupe associé
sur la feuille Source, cliquer sur le bouton Tirage au sort mapomme..., saisir le nombre de références distinctes à tirer au sort puis cliquer sur OK
rem: le tirage au sort élimine les doublons de références
en fin d’exécution, la macro affiche la feuille Verif. Cette feuille affiche quatre TCD qui permettent de vérifier le nombre de ligne tirées au sort, l'équipartition par catégorie, l'équipartition par groupe (en prenant en compte, bien sûr, le nombre de Ctrl par groupe), l'équipartition des catégories par Ctrl.
rem: le 3ième TCD permet, en cliquant sur une cellule de la colonne Nombre de Ctrl de créer automatiquement une feuille avec les lignes des contrôles à effectuer.
la durée d'exécution est relativement rapide même avec bécane âgée de plus de 7 ans (mais je l'aime bien ) -> voir l'image jointe

En espérant, ne pas m'être planté...

nb: je n'ai pas fait toutes les vérifications et j'ai considéré que l'échantillon tiré au sort est suffisamment 'petit' par rapport à la population globale pour que la macro se débrouille pour faire ce pour quoi elle a été programmée

mapomme · 8 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données ) - excel 2003

Bonsoir à tous,

Pour ceux qui ont Excel 2003, j'ai essayé de supprimer les spécificités propres à 2010.

Je ne peux pas tester le fichier sous 2003 car je ne possède plus cette version. Si quelqu'un pouvait me faire un retour...

Edit: Suite aux retours de job75, le fichier est dans le post # 58.

job75 · 8 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour mapomme,

Sur Excel 2003 RemoveDuplicates n'existe pas donc bug sur ta macro Init.

A+

mapomme · 8 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonsoir job75,

Merci, job75, pour ce retour . J'avais déjà oublié que j'utilisais une feuille cachée avec removeduplicates pour compter les doublons à la fin de l'initialisation des lignes sources. Grâce à ton retour, j'ai supprimé cette feuille et compté les valeurs distinctes via un dictionary.

J'ai supprimé le fichier Excel 2003 du post #53 pour l'insérer ici. J'espère que tout fonctionnera comme il faut sous Excel 2003...

Edit: cette v2a pour Excel 2003 présentait encore un bogue -> utilisation d'une fonction absente de 2010. J'ai donc supprimé cette version pour la v3c dans post # 58.

job75 · 8 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Re,

Init ne beugue pas mais dans la feuille "Source" il n'y a que des valeurs d'erreur #NOM?...

A+

mapomme · 9 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonsoir job75,

job75 à dit:
(...) Init ne beugue pas mais dans la feuille "Source" il n'y a que des valeurs d'erreur #NOM? (...)

C'est sans doute qu'il y a une fonction Excel 2010 qu' Excel 2003 ne reconnaît pas. La fonction la plus exotique que j'utilise est : ALEA.ENTRE.BORNES().

Dans ce cas, me semble-t-il, il faut activer l’utilitaire d'analyse au sein de 2003.

Dans le fichier joint, j'ai remplacé les ALEA.ENTRE.BORNES() par une expression ne faisant appel qu'à ALEA() qui doit être une fonction native dans Excel 2003.

Bebere · 9 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

bonjour Julie,Job,Mapomme
chez moi çà fonctionne impec(excel2003)

mapomme · 9 Novembre 2014

Re : Tirage sur un colonne sans doublons (grd base données )

Bonjour à toutes et tous,

Bebere à dit:
(...) chez moi çà fonctionne impec(excel2003)

Merci Bebere pour ce retour positif. Ce n'est pas toujours facile de déboguer "à l'aveugle" mais job75 m'a bien aidé en décrivant les bogues rencontrés sous Excel 2003. Espérons que chez lui, cela fonctionnera également !

Si j'avais du courage, j'appliquerais les mêmes modifications au fichier Excel 2010 (paramétrage de la dernière ligne d'une feuille et non pas "en dur", ne pas utiliser RemoveDuplicates, supprimer la feuille masquée) mais je n'en ai guère ce matin

Bon dimanche à tous,

Tirage sur un colonne sans doublons (grd base données )

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Impliqué

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

Discussions similaires