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

job75 · 9 Novembre 2014

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

Bonjour mapomme, Bebere,

Maintenant la macro Init va bien chez moi aussi.

L'Utilitaire d'analyse est toujours chargé chez moi, il y a une autre raison pour les #NOM?.

A+

mapomme · 9 Novembre 2014

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

Bonjour job75,

job75 à dit:
(...) Maintenant la macro Init va bien chez moi aussi. (...)

Merci du retour. C'est bien. De ce fait, la macro principale de tirage au sort fonctionne-t-elle aussi ?

job75 à dit:
(...) L'Utilitaire d'analyse est toujours chargé chez moi (...)

J'aurais pu et dû m'en douter !

job75 à dit:
(...) il y a une autre raison pour les #NOM?. (...)

J'ai changé en plus quelques numéros max de lignes pour des feuilles Excel 2003

Bon je vous quitte un moment pour une invitation à déjeuner. Chic! je n'ai rien à faire qu'à me laisser servir

job75 · 9 Novembre 2014

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

Re,

Oui tout fonctionne bien.

A+

job75 · 9 Novembre 2014

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

Re,

Ma macro du post #51 n'allait pas avec toutes ses boucles.

Celle-ci est très rapide, un peu moins quand même que celle de mapomme :

Code:

Sub TiragesAléatoires()
Dim durée#, Ntirage&, a1, ub1 As Byte, a2, ub2 As Byte, Nmax&, derlig&
Dim P As Range, tablo, ub&, t, i As Byte, x, y, j As Byte, z, k&, n&
durée = Timer
Ntirage = 1000
a1 = Sheets("liste").[A5:B10]
ub1 = UBound(a1) '6
a2 = Array("a", "b", "c")
ub2 = UBound(a2) '2
Ntirage = Application.Ceiling(Ntirage, ub1 * (ub2 + 1)) '1008, multiple de 6 x 3
Nmax = Ntirage / ub1 / (ub2 + 1) '56
derlig = Sheets("base").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
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))
  P.Columns(15).Offset(1).ClearContents 'RAZ colonne O, au cas où...
  P.Columns(16).Offset(1).ClearContents 'RAZ colonne P
  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 '1er tri
    .Cells = ""
    .Cells(1) = 0
    tablo = P 'matrice du tableau
    ub = UBound(tablo)
    t = .Cells 'matrice de la colonne auxiliaire
    For i = 1 To ub1
      x = a1(i, 1): y = a1(i, 2)
      For j = 0 To ub2
        z = a2(j): n = 0
        For k = 2 To ub
          If tablo(k, 3) = z And t(k, 1) = "" Then
            tablo(k, 15) = x
            tablo(k, 16) = y
            t(k, 1) = 0
            n = n + 1
            If n = Nmax Then Exit For
          End If
        Next
      Next
    Next
    .Cells = t
    P.Columns(15) = Application.Index(tablo, , 15)
    P.Columns((16)) = Application.Index(tablo, , 16)
    Union(P, .Cells).Sort .Cells '2ème tri
    .EntireColumn.Delete 'suppression de la colonne auxiliaire
  End With
  .Rows(Ntirage + 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
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox Ntirage & " tirages" & vbLf & vbLf & _
  "Durée " & Format(Timer - durée, "0.00 \s"), , "Tirages"
End Sub

Fichier (2).

A+

job75 · 10 Novembre 2014

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

Bonjour le fil,le forum,

Retirez les formules SOMMEPROD en lignes 1 à 3 qui ne servent plus à rien.

La macro précédente sur Win XP - Excel 2003 :

- Ntirage = 1008 => 4,44 secondes

- Ntirage = 59994 => 5,25 secondes.

Ce sont les 2 tris (sur 60000 lignes) qui prennent du temps.

A+

job75 · 10 Novembre 2014

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

Re,

On aura sans doute remarqué que je ne me suis pas cassé la tête pour construire la base de 60000 lignes.

Un simple copier-coller donc il y a des doublons en colonne K.

Cela n'a aucune importance puisque le problème à régler (tirage de lignes aléatoires avec répartition) n'a rien à voir avec la colonne K.

Voyez ce fichier (3) où j'ai mis des valeurs sans doublon en colonne K.

A+

julie211 · 10 Novembre 2014

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

Bonjour, job75

Merci pour ton aide, j'ai fait un test, sur colonne K, il n'y a pas de doublon, mais il a distribué seulement +600 lignes, pas tous les 1000 lignes, est-ce qu'il y a un moyen à distribuer tous les 1000 lignes pour tous les contrôleurs (32 ctrl) en nombre total et de categorie moyen ?

Cordialement,
Julie

julie211 · 10 Novembre 2014

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

Bonjour, mapomme

Merci pour ton aide, mais je comprends pas trop comment adapter le fichier ac mes criteres.
En faite, j'ai 3 categories sur colonne C, et je veux distribuer 1000 ligners à tous les contrôleurs (30 ou 32 ctrls en 3 groupe, chaque groupe a 10-11 ctrls) en nombre total et de categories moyen sans doublons, surtout me faire un copie coller en format original.
Le fichier s'initialise l'onglet "source" qui efface ma base de données, surtout les numéros de reference (col K), et l'onglet "resultat" ne copie coller pas tous les colonnes de la base; comment modifier en gardant mes donnees dans la source et les resultats?

Merci bien
Julie

job75 · 10 Novembre 2014

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

Re Julie,

Le mieux est de joindre votre fichier, avec un tableau de par exemple 5000 lignes, et surtout la feuille "liste" avec tous les Ctrl.

A+

mapomme · 10 Novembre 2014

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

Bonjour Julie,

julie211 à dit:
Bonjour, mapomme

Merci pour ton aide, mais je comprends pas trop comment adapter le fichier ac mes criteres.
En faite, j'ai 3 categories sur colonne C, et je veux distribuer 1000 ligners à tous les contrôleurs (30 ou 32 ctrls en 3 groupe, chaque groupe a 10-11 ctrls) en nombre total et de categories moyen sans doublons, surtout me faire un copie coller en format original.
Le fichier s'initialise l'onglet "source" qui efface ma base de données, surtout les numéros de reference (col K), et l'onglet "resultat" ne copie coller pas tous les colonnes de la base; comment modifier en gardant mes donnees dans la source et les resultats?

Merci bien
Julie

Tu laisses tomber l'initialisation. C'était juste pour créer un fichier de test.

Il faut dans ton fichier réel:
nommer l'onglet des données sources "Source"
nommer l'onglet des données résultat "Result"
nommer l'onglet où se trouve le tableau des Ctrl & groupes "Liste", tableau correctement rempli
puis lancer la macro tirage_mapomme

Le mieux, comme l'a proposé job75, est de joindre ton fichier réel.

A+

julie211 · 10 Novembre 2014

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

Mon fichier original a depassé la taille de capacité, j'arrives pas à joindre, voici mon fichier ac la liste en supprimant certain données mais en gardant le format original.

merci
Julie

mapomme · 10 Novembre 2014

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

(re)Bonjour Julie ,

Un fichier simplifié.

copier le module m2_mapomme dans le projet VBA dans votre fichier.
vérifier les noms des onglets (Source, Result, Liste)
lancer la macro tirage_mapomme

Le fichier fourni comporte 20 000 lignes sources, 3 catégories, 3 groupes et 31 Ctrl.

Edit : nos post se sont croisés

julie211 · 10 Novembre 2014

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

mapomme à dit:
Bonjour Julie,

Tu laisses tomber l'initialisation. C'était juste pour créer un fichier de test.

Il faut dans ton fichier réel:

nommer l'onglet des données sources "Source"

nommer l'onglet des données résultat "Result"

nommer l'onglet où se trouve le tableau des Ctrl & groupes "Liste", tableau correctement rempli

puis lancer la macro tirage_mapomme

Le mieux, comme l'a proposé job75, est de joindre ton fichier réel.

A+

Hi, Mapomme

J'ai essayé sans appliquant l'initialisation, mais la distribution seulement sur +600 lignes, non ma base de 1000 total. En plus, apres la nouvelle distribution de tirage, l'onglet verif n'actualise plus. Est-ce qu'il y a un moyen à distribuer tous les 1000 à ts les ctrls? j'ai attaché mon fichier ac l'onglet liste. En faite, comme il y a 3 categorie et 30-32 ctrls (3 groupes), c'est sur que chaque ctrl ne peut pas avoir le meme nombre doc total et le meme nombre categorie, je veux une distribution équilibré en moyenne mais qui peut finir toute la distribution (1000 lignes) sans distribueant la meme ligne aux differents ctrls.

merci
Julie

mapomme · 10 Novembre 2014

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

Re,

julie211 à dit:
(...) J'ai essayé sans appliquant l'initialisation, mais la distribution seulement sur +600 lignes, non ma base de 1000 total. (...)

C'est normal et j'avais prévenu !

Dans le post #53, j'attirais l'attention sur ce point (je cite mapomme):

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

Dans ton premier post, tu parlais de tirer au sort environ 1000 lignes parmi plus de 100 000 lignes. J'ai construit le programme en fonction de cela, en considérant que les catégories étaient à peu près réparties équitablement dans la population source et que la source était grande par rapport à l'échantillon. Manifestement, ce n'est pas le cas!

Maintenant, les choses ayant changé, il faut que j'adapte la macro.

mapomme · 10 Novembre 2014

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

Bonjour Julie,

Une version v3 simplifiée (sans la procédure d'initialisation) qui devrait :

distribuer le nombre total souhaité
la proportion de lignes tirées au sort pour chaque catégorie est à peu près égale à la proportion de lignes de la même catégorie au sein des données sources.
la macro élimine les références en double parmi les lignes tirées au sort
les TCD de la feuille "Verif" devraient être mis à jour à chaque exécution de la macro

Edit (1) : nouvelle version avec des changements minimes

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

julie211

XLDnaute Nouveau

Pièces jointes

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

julie211

XLDnaute Nouveau

julie211

XLDnaute Nouveau

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

julie211

XLDnaute Nouveau

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

julie211

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Discussions similaires