Tirage aléatoire avec contraintes pour constituer un échantillon

[RP25]

Bonjour à tous et merci d'avance pour votre aide,

Dans le cadre de mes études en école de commerce, je dois réaliser un plan d'échantillonnage pour une population correspondant à une communauté de commune aléatoire en France.

Je dois constituer un échantillon de 900 personnes qui respecte la pondération des communes en nombre d'habitants, et qui contienne un nombre équivalent de personnes de chaque tranche d'âge. Il faudrait également qu'il contienne à peu près 50/50 en homme/femme.

J'ai déjà réalisé un gros travail pour agréger les données, et j'arrive à un tableau des habitants (représentés par un N° ici) avec leur sexe, leur tranche d'age, leur commune et s'ils peuvent faire partie de l'échantillon ou non (travail préalable qui importe peu ici) --> 1er onglet du fichier attaché. Je sais également combien de personnes je veux avoir par commune (2e onglet). Le fichier joint est donné à titre indicatif car j'ai du supprimer plusieurs milliers de lignes dans le 1er Onglet

Comment maintenant faire un tirage aléatoire qui me donne 900 personnes correspondants aux critères énoncés ci-dessus (nbr par commune, parité H/F, parité des tranches d'âge et personne retenue pour l'échantillon : colonne D)?

Je sais que cela demande surement un peu de travail mais je suis preneur au moins pour une méthode. Je travaille principalement sur Mac donc une solution sans VBA serait appréciée, d'autant que je débute vraiemnt en VBA. Mais au pire je me débrouillerai pour comprendre.

Merci beaucoup de m'aider, je dois finir cela rapidement.

A+

RP

 

[RP25]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Quelqu'un pour m'aider? Le challenge est sympa non?

Dites-moi si mon premier message n'est pas assez clair.

Merci,

RP

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonjour

Ci-joint, le début du travail.... Je dis bien, le début...

En pratique :
dans la colonne I, on choisit au hasard des individus pouvant faire partie de l'échantillon.
dans la colonne J, on les classe par ordre croissant
dans la colonne K, on élimine les doublons
dans les colonnes suivantes, on donne les caractéritiques des individus
Un peu plus sur la droite, on compare les proportions échantillon/tableau initial.

En pratique, il faudrait, avec ce fichier, minimiser les cellules en gras, par exemple à l'aide du solveur.

Si cela semble aller dans le bon sens, ou, au contraire, si il faut changer quelque chose, dis le nous.

@ plus

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonsoir

Une autre possibilité en éliminant dès le début les personnes ne pouvant pas faire partie de l'échantillon : Ainsi, on peut plus facilement obtenir un échantillon de la taille correcte.

A toi de tester.

@ plus

 

[RP25]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Merci beaucoup pour cette proposition qui va clairement dans le bon sens.

Je suis en train de mouliner pour essayer d'adapter ça à mon fichier réel et je vous tiens au courant.

Toutefois, ne serait-ce pas plus simple de classer les personnes par communes, et de faire un tirage aléatoire différent pour chacune, pour avoir directement le bon nombre de personnes par commune?

Je vais bosser là dessus pour essayer de finir cela demain.

Merci encore.

A+

RP

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Ne serait-ce pas plus simple de classer les personnes par communes, et de faire un tirage aléatoire différent pour chacune, pour avoir directement le bon nombre de personnes par commune?

A+

RP

Je vais y réfléchir.

En attendant, un autre fichier, fonctionnant suivant le même principe que le fichier précédent, mais allant chercher les informations directement dans le tableau initial, en feuille 3. Résultat : moins d'affichages, moins de calculs, donc travail effectué plus rapidement (avec 900 personnes à choisir au hasard, cela sera bien utile).

@ plus

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Rebonjour

Histoire d'alléger les calculs, suppression des colonnes A et F de la feuille 1.

@ plus

P.S 1: De plus, le solveur ne peut peut être pas traiter autant de lignes en même temps : 200 sur excel 97. N'ayant pas la doc pour excel 2003, ni pour excel 2007, je ne sais pas quelle est la limite dans ces deux derniers cas.

P.S 2 : Pour le moment, je n'ai pas trop d'idée pour ce qui est du travail commune par commune. Logiquement, c'est plus intéressant puisqu'on peut imposer le nombre de personnes à prendre dans chaque commune. Le solveur aurait donc moins de boulot. Pratiquement...

 

[RP25]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

merci beaucoup pour cette aide précieuse!

ça me prend un peu de temps pour comprendre la logique et l'adapter à mon fichier. Je vais essayer avec le dernier fichier proposé.

Par contre je ne sais pas du tout comment fonctionne le solveur pour excel? Est-ce une sorte de "valeur cible"?

Merci, je m'y remets !

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Par contre je ne sais pas du tout comment fonctionne le solveur pour excel? Est-ce une sorte de "valeur cible"?

C'est exactement ça. Sur excel 2003, je devrais pouvoir m'y remettre. Par contre, sur excel 2007, comme toute cette partie a été changée, va falloir que je me creuse la tête. Et malheureusement, je n'ai pas que ça à faire... Conclusion : avec quelle version travailles tu ?

PS : Combien d'individus as tu au total ? Idem pour les communes ?


@ plus

 

[RP25]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Je travaille sur excel 2008 sur Mac, qui doit correspondre grosso modo à excel 2007 sur windows. Je peux encore récupérer mon office 2007 "normal" ce week-end, mais ma deadline pour finir ce travail est lundi...

Actuellement je me casse la tête pour adapter les formules à mes 20 500 lignes (20455 individus en tout, sur les 42 communes du 2e onglet). Je n'arrive presque à rien et chaque action prend plusieurs minutes.

Du coup j'envisage d'extraire les personnes d'une commune donnée, de mouliner avec les formules de ton fichier actuel pour avoir mon échantillon par commune. Il faudra le faire 42 fois mais ça me parait la solution la "moins pire" pour obtenir quelque chose. Il ne me restera qu'à appliquer le solveur pour les tranches d'âge, et le sexe s'égalisera plus ou moins, quitte à faire tourner la formule plusieurs fois. est-ce que ça parait une bonne idée?

En tout cas merci mille fois pour ton aide. J'ai beau aimer les challenges excel à mon niveau, je commence à saturer de travailler avec ces fichiers très volumineux.

Je laisse cela pour l'instant et je vais commencer le travail de fourmi ce soir.

A+
RP

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonsoir

Ci-joint un fichier où l'on peut imposer le nombre d'individus par commune.
Pour autant, il ne s'agit que d'un brouillon : Cela ne fonctionne pas nickel à cause des doublons.
Explication : Si pour une commune, tu imposes 5 tirages au sort, tu peux très bien avoir 4 individus et un doublon à éliminer. Il te manque donc un individu. Grrrr.
Si par précaution, tu imposes le double de tirage, 10 par exemple, cela peut très bien marcher, mais tu peux aussi te retrouver avec 8 individus et seulement deux doublons à éliminer.
J'envoie qu'en même ce fichier, histoire que tu commences à le prendre en main.

Je me demande si il ni aurait pas une méthode plus simple, complètement différente, du style tableau croisé dynamique... Pour lundi, c'est plutot juste, sauf si plus doué que moi se penche sur le sujet.

@ plus

P.S 1: J'ai dû éliminer pas mal de cellule ou de formule pour que le fichier zippé fasse moins de 49 ko. A toi d'étirer les formules vers le bas.

P.S 2 : Avant de passer au fichier réel, travailles d'abord sur un fichier de "seulement" 2 ou 3000 lignes.

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonjour

Ca y est, ça tourne.

Pour autant ce n'est pas encore tout a fait nickel. Parfois, il y a trop de doublon, et on se retrouve à la fin, avec un nombre d'individu insuffisant dans une ou des communes. Dans ce cas, refaire un échantillon jusqu'à n'avoir que des OK dans la colonne vérification.

Ici encore, j'ai dû effacer des formules, dans les cellules vertes, pour que le fichier zippé fasse moins de 49 ko. Il faut donc étirer certaines lignes vers le bas.

Je n'ai pas non plus vérifié les plages de travail, jusqu'à la ligne 50 en général, mais c'est à vérifier.

Je suis vraiment trop débordé pour peaufiner davantage ce travail. A toi de continuer le débrousaillage.

On peut certainement faire plus simple. Si je peux, dans la journée, j'essayerai de supprimer quelques calculs.

Je n'ai pas vérifié le calcul des proportions... A la force d'insérer des colonnes, des formules... on y perd son latin...

@ plus

 

[JeanMarie]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonjour RP, Cisco

Sur la base du dernier fichier de Cisco, que je tiens à féliciter par sa persévérance à résoudre le problème, malgré sa masse de boulot.

Je n'arrive presque à rien et chaque action prend plusieurs minutes.

Je me suis attaché à simplifier les formules.

Dans la feuille "Feuil1" :

• la formule matricielle de la colonne B

=SI(ESTNUM(INDEX(Feuil3!A$1:A$50;MIN(SI(NB.SI(B$2:B2;Feuil3!A$3:A$50)=0;SI(Feuil3!E$3:E$50<>"non";LIGNE(Feuil3!A$3:A$50))))));INDEX(Feuil3!A$1:A$50;MIN(SI(NB.SI(B$2:B2;Feuil3!A$3:A$50)=0;SI(Feuil3!E$3:E$50<>"non";LIGNE(Feuil3!A$3:A$50)))));"")

Dans cette formule la gestion d'erreur peut-être supprimée, si l'on utilise le format personnalisé

[=0]"";Standard.

ce qui donne, toujours en matricielle

=INDEX(Feuil3!A$1:A$50;MIN(SI(NB.SI(B$2:B2;Feuil3!A$3:A$50)=0;SI(Feuil3!E$3:E$50<>"non";LIGNE(Feuil3!A$3:A$50)))))

Cette modification va poser un problème #N/A dans la colonne C
que l'on résoud par

=SI(B3;RECHERCHEV(Feuil1!B3;Feuil3!A$3:C$50;3;0);"")

• la formule de la colonne E

Je n'ai pas compris le pourquoi de l'utilisation de la fonction ENT.

=SOMME(E$2:E3)*F$1

• la formule de la colonne E

=SI(ESTTEXTE(INDEX(D$2:D$50;SI(NON(ESTNA(EQUIV(LIGNES(G$3:G3);F$2:F$44;0)));EQUIV(LIGNES(G$3:G3);F$2:F$44;0);EQUIV(LIGNES(G$3:G3);F$2:F$44;1)+1)));INDEX(D$2:D$50;SI(NON(ESTNA(EQUIV(LIGNES(G$3:G3);F$2:F$44;0)));EQUIV(LIGNES(G$3:G3);F$2:F$44;0);EQUIV(LIGNES(G$3:G3);F$2:F$44;1)+1));"")

On supprime la gestion d'erreur par &"" placée à la fin de la formule.
EQUIV dans sa fonction "valeur_proche" à 1, utilise un comparateur de type "<=". Mais dans la formule le comparateur est de type ">=".
Dans excel il y a une fonction qui utilise ce comparateur, je vous laisse découvrir ci-dessous la formule modifiée.

=INDEX(D$2:D$50;EQUIV(1;FREQUENCE(LIGNES(G$3:G3);F$2:F$44);0))&""

• Ensuite dans les formules utilisant "RECHERCHEV($L3;.....)"

Il est inutile de rechercher dans le tableau un ID égale ""

=SI(L3="";"";RECHERCHEV($L3;Feuil3!A$3:B$50;2;0))

A un certain niveau, il est préférable de rechercher une seule fois $L3 dans le tableau en retournant la position dans la plage, par la formule =EQUIV($L3;Feuil3!A$3:A$50;0) et d'utiliser ensuite la formule =INDEX(Feuil3!A$3:A$50;...;1) pour retourner la donnée désirée. (... représente la cellule contenant la formule =EUIV(......)

On peut appliquer ces quelques principes pour les autres formules ayant une gestion d'erreur. Avant d'appliquer une gestion d'erreur, il est préférable de se poser la question sur l'origine de cette erreur.

*

• Dans les autres formules notamment celle-ci

=SI(V3<>"";NB.SI(P$3:P$26;V3)/(NBVAL(P$3:P$26)-NB.VIDE(P$3:P$26))*100;"")

il y a une partie variable "NB.SI(P$3$26;V3)" et une partie fixe "(NBVAL(P$3$26)-NB.VIDE(P$3$26))*100;"")"
La partie fixe aura le même résultat pour toutes les lignes de la colonne.
Il est donc préférable de mettre cette partie dans une cellule, et de faire appel à son contenu.

Dans le fichier joint, pas facile de se faire une idée, les valeurs calculées étant le plus souvent des "" que des données numériques ou texte. Si tu pouvais donner un cas concret.

RP essaye d'appliquer ces modifications sur ton fichier.

@+Jean-Marie

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonsoir à tous

Bonsoir Jean Marie... et merci pour tes conseils que je n'ai malheureusement pas le temps de prendre en compte sur le nouveau fichier que je met en pièce jointe (un autre jour peut être)... (Pour ce qui est des ENT, le fichier initial de RP contenait des nombres avec des décimales, en feuille 2, ce qui me posait quelques problèmes).

J'ai essayé d'éliminer le problème signalé dans mon dernier post, à savoir des fois un nombre d'individus insuffisants par commune à cause du nombre de doublons trop important. Apparemment, ça fonctionne.

Pour ce qui est du temps de calcul, après avoir fait le nettoyage préconisé par Jean Marie, il faudra aussi nommer certaines fonctions, comme j'ai commencé à le faire dans ce nouveau fichier, mais pour d'autres raisons (message d'erreur me signalant trop d'imbrications).

@ plus

 

[CISCO]

Re : Tirage aléatoire avec contraintes pour constituer un échantillon

Bonjour

Et une version sans la colonne G.

@ plus