gestion chambres collectives sous Excel 2003 Résolu/Fermé

Question

Bonjour / bonsoir,

j'ai un problème que j'essai de résoudre depuis bientôt 6 mois, mais étant autodidacte, j'ai essayé de me débrouiller seul en bidouillant. J'ai été sur je ne sais pas combien de sites pour récupérer des macros et autres formules, mais je n'y arrive pas.

Je vous explique vite fait, ce que je veux faire et ensuite je vous explique mon problème ^_^

EXPLICATIONS : 
J'utilise Excel 2003 sous Windows XP et j'ai une base de données (BDD) en *.xls (tableau simple avec un stagiaire par ligne). Cette BDD me sert pour inscrire des gens à des stages se déroulant chez nous, soit environ 4000 personnes entre février et mai (chaque années). Je me sert également de cette BDD pour faire des publipostages avec Word 2003. Nous avons des stages de 1 jour, 2 jours et 1 stage d'une semaine qui se passe du lundi au jeudi (inclus).

Nous avons 5 chambres collectives avec 14 lits/pièces que nous mettons à disposition des stagiaires. Quand les gens s'inscrivent, on leur demande si ils souhaitent profiter de ces couchages.

MON PROBLEME : 
Je n'arrive pas à mettre en place un système pour gérer les chambres.
1) des stagiaires qui dorment 1 nuit et les autres qui dorment 2 nuits peuvent être dans la même chambre et les lits ne sont pas numérotés.
2) j'aimerais que les infos soient dans la même BDD (pas dans un autre fichier Excel)
3) j'aimerais juste attribuer une chambre (CH1, CH2, CH3,etc.) avec un système simple et que la disponibilité des chambres se gère seule et que je ne puisse pas attribuer un lit a quelqu'un si la chambre est déjà complète.


Ce n'est pas simple à expliquer et c'est la première fois que je demande sur un forum, je viens vers vous en espérant que quelqu'un me trouve une solution.

Merci par avance pour votre aide.

Benoît



Configuration: Windows 7/ Firefox 16.0

Mike-31 · Answer

Salut,

Bienvenue sur ce forum,

Ton problème est complexe mais réalisable et certainement sans macro.
Il serait bien d'avoir un exemple de ton fichier avec quelques lignes renseignées sans notes confidentielles que l'on voit sa structure, que tu peux joindre avec ce lien

https://www.cjoint.com/

Pit3ull · Answer

Bonsoir Mike et merci de t'intéresser à mon problème.
C'est sympa de ta pars car je suis conscient que ce problème est complexe.
Je me suis bien tordu le cerveau à chercher une solution ^^

J'ai mis du temps à répondre car j'ai préparer un exemple de ma base que j'ai hébergé sur le site que tu m'as donné => https://www.cjoint.com/?BKiwclGJfOP


NB important que j'ai oublié de préciser, si le stagiaire est accompagné de son conjoint, ils ne peuvent pas dormir dans les chambres donc ils ne feront pas de demandes ^^

Mike-31 · Answer

Re,

je me disais, je regarde ton problème demain matin, je commence à saturer

Pit3ull · Answer

Re ^^ 
Oui pas de problèmes, comme j'expliquais ça fais bientôt 6 mois que j'essai de bidouiller seul et comprendre comment cela pourrait fonctionner mais sans y arriver donc je peux largement attendre demain midi à mon retour à la maison pour consulter ta réponse donné. 
Après avoir parcouru le forum, je constat que tu es hyper actif et que ta nuit sera amplement mérité (LOL) 
Merci encore d'avance pour tout et je t'avoue que j'ai tout de même hâte à demain quand même ^^ 
D'ailleurs, je vais faire pareil tiens :D

Mike-31 · Answer

Re,

L'informatique est logique et pour écrire un code il faut chercher cette logique après ça va tout seul.
si je comprends bien tes explications,
stage 1 du 02/02/2013 au 02/02/2013 (ils dorment le 01/02/2013) 
stage 2 du 03/02/2013 au 03/02/2013 (ils dorment le 02/02/2013)
pour les stages qui durent 1 jour, les stagiaires dorment la veille c'est bien cela !
 
stage 3 du 02/02/2013 au 03/02/2013 (ils dorment le 01/02/2013 et le 02/02/2013)
pour les stages qui durent 2 jours, les stagiaires dorment la veille et le soir du premier jour c'est bien cela !


 stage 4 du 03/02/2013 au 05/02/2013 (ils dorment le 02/02/2013, le 03/02/2013 et le 04/02/2013)
en régle générale les stages qui durent plusieurs jours, les stagiaires dorment la veille et chaque soir sauf le dernier jour du stage, c'est bien la démarche !

 stage 5 du 04/02/2013 au 04/02/2013 (ils dorment le 03/20/2013)  
 

Pour l'attribution des chambres ou des lits, on regroupe les stagiaires par durée ou on équilibre les occupations

Pit3ull · Answer

Bien le bonjour à tous, je rentre du taf, ca sent réellement bon les week-ends ^^ 
Oups, désolé pour le hors sujet :D 


@ michel: LOL, pour le Club Med. J'ai bien rigolé en voyant çà mais non ce n'est pas le cas ^^). 


Pour le problème H/F, c'est pertinent comme question mais je n'en ai pas parlé car en règle général, il n'y a pas énormément de femme qui viennent individuellement du coup, je réserve la chambre n°6 uniquement pour elles. 

A l'inscription de cette femme, si elle me demande une possibilité d'hébergement,  je lui donne la numéro 6 et pour le reste j'utilise toutes les autres. 
Comme jusqu'à présent, c'est moi qui attribut le numéro, j'ai juste à savoir si c'est une femme ou pas et y faire attention. 

Je ne pense pas qu'il faut en faire une condition dans les formules? 
A moins que mike ai prévu que les réservations se fassent toutes automatiquement et que l'on ne puisse pas forcer la main apres? 





@mike: Oui c'est tout à fait çà, pour les 3 questions que tu me pose !  
- oui les stages qui durent 1 jour, les stagiaires dorment la veille!  
- oui quand les stages durent 2 jours, les stagiaires dorment la veille et le soir du premier jour !  
- oui en règle générale les stages qui durent plusieurs jours, les stagiaires dorment la veille et chaque soir sauf le dernier jour du stage. 



+1 pour équilibrer les occupations  

A l'attribution des chambres ou des lits, on ne regroupe pas les stagiaires par durée car ça n'a pas d'importance, ils arrivent individuellement à n'importe quelle heure la veille du stage donc on peut équilibrer les occupations et je pense que c'est plus simple à gérer car on n'est plu bloqué par les effectifs de 14. 



NB: je ne sais pas citer les messages, c'est la raison pour laquelle j'ai écris @xxx: dans mon message ^^

Cordialement, Benoît

Mike-31 · Answer

Re,

Je viens de prendre connaissance de ton post, je te regarde ça dans la foulée, sur ton tableau colonne E eniête UN NUMERO, c'est réservé au numéro de chambre ou dois je prévoir une colonne ou as tu prévu une colonne

Pit3ull · Answer

Re et pas de soucis :)   

Les colonnes P,S et T sont libre d'utilisations :)   

P = des commentaires (facultatifs), je ne l'utilise pas souvent   

et S et sont les colonnes que je n'utilisent pas et donc libres   
C'est d'ailleurs sur c'est colonnes que je mettais mes menus déroulant pour tester.   

Dans l'exemple, je les avaient mises en verte pour que tu les trouves facilement ^^

Mike-31 · Answer

Re,

En étudiant ton fichier, je remarque qu'il y a des couples "Présences conjoint(e)s "

comment gérs tu cela, il dorment dans la même chambrée ou les épouses dorment chambre 6 et les hommes dans les autres chambres

Pit3ull · Answer

Re,
Toutes les cases me servent pour mes publipostages et aussi pour compléter des statistiques pour un autre bureau.

La case "Présences conjoint(e)s" me sert pour savoir si le stagiaire souhaite venir avec son conjoint ou pas!

Si le/la conjoint(e) accompagne le stagiaire, ils ne peuvent pas disposer d'une chambre.

Explications: les stagiaires (fonctionnaires) sont couverts pénalement dans nos locaux mais pas leurs conjoint(e)s civils car le bâtiment n'est pas classé comme Établissement Recevant du Public (ERP).


En gros si ils viennent en couple = il ne demandent pas de logement
Si le stagiaire vient seul il peut demander un logement.


Je résume bien sur car pas simple ^^

Pit3ull · Answer

EDIT 

En faite j'ai une autre feuille pour me dire combien de conjoint(e)s participe au stage: 

La case que tu me demande correspond à FORMULE_CONJ_O_N="OUI dans la formule suivante qui se trouve sur une autre feuille: 

=SI(SOMMEPROD((FORMULE_DATE=STAGES!$E$3)*(FORMULE_CONJ_O_N="OUI"))=0;"0 conjoint";SI(SOMMEPROD((FORMULE_DATE=STAGES!$E$3)*(FORMULE_CONJ_O_N="OUI"))=1;"1 conjoint";SOMMEPROD((FORMULE_DATE=STAGES!$E$3)*(FORMULE_CONJ_O_N="OUI"))&" conjoints"))

Mike-31 · Answer

Re,  

vite fait entre deux, regarde en colonne AA à AG un début de calcul  

https://www.cjoint.com/?BKkj5wDFnWG  

si on est sur la bonne voie, on reverra les formules, restera  qu'a formuler l'attribution des chambres   
   
A+  
Mike-31  

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Pit3ull · Answer

Bonsoir et désolé de répondre si tard, mais j'ai eu un contre temps aujourd'hui et ça fait seulement 1 heure que je suis rentré. J'ai téléchargé ce que tu m'as fais et oui c'est super et c'est carrément en bonne voie.   
Il y a quelques trucs que je ne comprend pas mais étant débutant, c'est normal ^^  

Il y a une chose que j'ai oublié de te dire.  
En faite la ligne 5 sert de model au moment où j'inscris un stagiaire.  
La ligne n°5 est visuellement caché en mettant hauteur de la ligne à zéro  

Quand j'inscris un stagiaire ça se fait dans cette ordre via une macro:  

1) sélection de la page (INSCRITS)  
2) sélection de la ligne n°6  
3) insertion de ligne (ça copie la mise en formes de la ligne n°5)  
4) copie la ligne n°5 sur la ligne n°6 (ça copie les menu déroulant et incrémente de +1 les formules)  
5) remplissage automatique des cellules A6, B6, C6 et D6  
6) sélectionne la cellule E6  
7) fin de la macro  

Du coup quand j'insére une nouvelle ligne dans ce que tu m'as fais, les formules du haut (Nbre de F, Nbre de M, X stagiaires, etc.) s'incrémentent de 1 à chaque fois, et ca décale toutes les références des plages des formules.  

J'ai essayé de palier au problème en donnant des noms aux formules que tu as mises dans les cellules V1, V2, AB1, AB2, AB3 et AB4 mais ça ne fonctionne pas.  

Dans tout les cas c'est super positif tout ça car c'est en très bonne voie et en plus ça m'a permis de comprendre ce que tu as fais et j'ai beaucoup appris sur la logique à adopter.  
Même si j'avoue ne pas avoir tout compris lol.  

C'est un vrai défit, qui j'espère ne te décourage pas car j'avou est complexe.   

En tout cas c'est un super méga boulot que tu as fais là et je ne me répète jamais assez en te disant merci pour ce que tu fais.  

Cordialement, Benoît

Pit3ull · Answer

Re, j'ai récupéré mon fichier cette après midi au travail et je viens d'enlever toutes les infos confidentielles sur les stagiaires puis fais les modifications que tu as faites.  

Le document est ici => https://www.cjoint.com/?3Klem07hz1w 


Cordialement, Benoît

Mike-31 · Answer

Re, 

Je viens d'ouvrir ton fichier, c'est plus clair d'avoir ton vrai fichier et surtout plus facile. 

pour répondre à tes questions, il est possible de prendre en compte d'évolution de tes listes via le VBA ce qui solutionnera tes calculs. 

par contre j'ai remarqué quelques problèmes comme sur la feuille INSCRITS tu as installé un filtre automatique ce qui est bien, mais le problème est que si tu utilise ce filtre tu perds ta ligne 5 qui devient la dernière et le système prendra la ligne qui à pris sa place comme référence. je te regarde ça ! 

autre chose, dans ton code lorsque tu ajoutes un stage, la date de début de stage est fixée est figée au 04/02/2013, il faudrait prendre les dates saisies en ligne 4 feuille Accueil sans le (S6) qui je pense est le type de stage 1, 2 ou 4 jours 
il me faudra exactement les équivalence de ces codes. 

la date de fin en B6 est fixe au 04/02/2012 si je suis mon raisonnement, tu va écrite un code par stage alors qu'il suffirait d'ajouter un nombre de jour à la date de départ en foncton du code stage (S6) par exemple. 

Ta page Accueil est protégée, il n'est pas dans mes habitudes de faire sauter les protections peut tu retourner le fichier sans protection ou le mot de passe de la feuille et du Visual peut être que d'autres intervenants souhaites également travailler sur ton fichier pour apporter leurs concours

  
A+ 
Mike-31 

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Pit3ull · Answer

Re,
oui j'utilise les filtres automatiques de la feuille (INSCRITS) pour faire le point des stages chaque veille de stage. Je fais ça sur une copie du fichier stage.xls pour éviter les enregistrement malencontreux. Comme la hauteur de la ligne numéro 5 est définie à zéro, elle n'est pas prise en compte dans les filtres, donc pas de problème :) Plus tard, je pense tout de même, mettre un system de recherches mais pour l'instant, je me focalise sur le problème actuel :)

La page accueil sert juste de stat et les formules servent juste pour faire un point de situation.

premiere ligne STAGE 01 (CODE 01)
STAGE 01 = nom du stage
(CODE 01) = le numero du stage

2eme ligne 04/02/2013 (S6)	
04/02/2013 = jour du stage
(S6) = numéro de la semaine ou se déroule le stage

0 conjoint
0 stagiaire
0 participant

C'est 3 infos me donnes le nombre de participants total et combien de stagiaire puis combien de conjoints.
Ces formules se remplissent en fonction des case remplit et coché dans la feuille (INSCRITS).

Autant pour moi pour le mot de passe, c'est un réflexe chez moi sur ce fichier car il est en partage sur mon réseau et je ne fais même plus attention LOL

Le voici sans mots de passe ^^

https://www.cjoint.com/?3KloNjdFOCx

Attention, il y a des onglets qui se cache automatiquement à l'ouverture du fichier et ceci à chaque ouverture.

Cordialement, Benoît

Mike-31 · Answer

Re,

Je ne t'oublie pas seulement un peu boosté ces temps ci.
J'ai simplifié tes formules, teste les inscriptions au stage du 04/02/2013 voir si on s'approche de tes attentes.
j'ai un peu allégé le code VBA de la macro NEW_STAGE_01 mais il y a mieux à faire au niveau des dates de début, de fin, noms des stages et codes des stages, afin d'écrire un code utilisable pour tous tes stages et surtout ne pas à avoir à le modifier, il faudrait trouver un point commun ou un élément de calcul. Je m'explique, il faudrait à partir d'un code par exemple savoir si le stage est d'un, deux, quatre etc jours, également si c'est un stage 01, un code 01

https://www.cjoint.com/?BKnvBto82Rw

Pit3ull · Answer

Oh puré désolé je viens seulement de voir ton message ce soir car hier je suis rentrée assez tard.
C'est super génial ce que tu as fais là et ne t'inquiète pas, je suis conscient que tu n'a pas que mon problème à résoudre sur le forum et je suis également conscient que tu as une vrai vie après tout ça.
Je ne reprocherais jamais çà à quelqu'un, surtout avec l'aide énorme que tu fournis, ça serait d'autant plus déplacé ^^
Pour les macro allégé, c'est excellent, je vais les intégrer à ma base, puis je vais également mettre les formules dans mon tableau.
Elles fonctionnent super bien et c'est exactement ce que tu m'expliquais plu haut dans le topic.
Si j'ai bien compris ce que tu me demande, tu aimerais savoir si il y a une référence unique par stagiaire pour partir sur une base unique et par la suite faire le calcul?
Si c'est le cas, oui il y a plusieurs numéros unique que je demande à l'inscription.
Ce que je vais faire, je vais adapter ce que tu as fais à mon tableau puis je ferais des inscription fictive et j'encadrerais les informations et commenterais le fichier pour indiquer les cases.
j'ai pas mal bossé sur mon tableau d'inscriptions et j'ai modifié énormément l'interface en ajoutant la possibilité de gérer les inscription d'une autre façon puis j'ai ajouté une fiche pour consulter ou supprimer une personne inscrite, etc.
D'ailleurs je conseil ce tuto http://www.polykromy.com/blog/excel-base-de-donnees.htm à tout les gens qui comme moi souhaitent apprendre plus en détail certains fonctionnement d'excel 2003. Ce tuto nous permet de fabriquer une base de données de A à Z.

Sorry pour le hors sujet ^^

Je te prépare çà des que possible puis je le commente et afin je le mettrais en ligne en réponse de ce poste.
Encore mille millions de mercis pour le super taf que tu à déjà fais pour moi.

Cordialement, Benoît

Mike-31 · Answer

Re,

j'ai eu un peu de temps, regarde cette nouvelle version, feuille Accueil il suffit de sélectionner le stage en cellule C3, G3, K3 etc. et les cellules du module s'actualisent.
ensuite clic sur l'image pour transférer les données feuille INCRITS.
j'ai un peu simplifié les codes en créant deux codes commun Action et Insertion ligne. 

tu peux éviter la mise en garde à la fermeture du fichier invitant l'agent à activer les macros à l'ouverture du fichier, en créant ta propre signature et en signant tes codes, j'ai écrit une procédure que tu peux lire ici

https://www.cjoint.com/?BKowMLypRmf

Pit3ull · Answer

Re,    
c'est génial ton astuce du (Fin_X) dans la formule, par contre je me pose la question sur la ligne de référence qui est 23 et passe à +1 en auto.    

Je peux changer le 23 pour mettre 10000 ou pas ?    
Si je le change dans les formules, est ce que ça va jouer ou pas?   
J'aimerais faire ça afin qu'elle ne soit pas visible et je vois tres mal quelqu'un rentrer des infos après la ligne 10000 et je ne pense pas avoir 50000 stagiaires par an (si ça m'arrive, je me pend lol)  




EDIT je n'avais pas vue ta réponse au dessus, je regarde ^^

Arf, ce n'est pas bon car la page d'accueil me sert uniquement pour mon supérieur et un autre bureau, pour qu'ils puissent avoir des stats. 
Mais comme je disais plus haut, je vais essayer de déchiffrer ce que tu as fais et je vais le mettre sur mon doc. 
De faire ça me permettras de comprendre la logique à adopter ce qui me permet d'apprendre en même temps. 


Des que j'ai fais ça , je te remet le fichier en ligne ^^



Acceuil = stats affiché + raccourcis pour lancer mes macros d'enregistrement 
INSCRITS = ma base de données de stagiaires (nom, prenoms, ect. 
STAGES = base de données des stages (infos sur le stage en lui même, dates, codes, etc.) 
LISTES = ma base de données pour mes liste déroulante 

NB: pour le niveau de sécurité, je suis toujours en moyen, sauf quand je travail en local à la maison où je suis en faible.

Mike-31 · Answer

Rez,

Tu peux insérer quelques lignes au dessus de la ligne jaune sans problème mais simplement une dizaine parce que chaque fois que tu saisis un stage une nouvelle ligne est créée et cette ligne actuelle 23 de référence descend automatiquement si tu la place en ligne 1000 et que tu saisis 5000 lignes elle se situera à la ligne 6000 et si tu dois imprimer la feuille tu auras intérêt à programmer l'impression par feuille sinon tu auras une série de feuille blanche.
Pour ma part 'insérerais une dizaine de ligne et mettrais cette hauteur de ligne à 0

Pit3ull · Answer

Re, nan, je me suis mal exprimé ^^ 
En gros au lieu de 23, est ce que je peu caler cette ligne à 10000? en modifiant les formules etc. 
La prochaine inscription de stage, elle passera automatiquement en 10001 

C'est ca que je me posais comme question sur mon post précédent (j'avais fais une édition du poste) 

NB: génial le certificat, j'apprends plein de choses utiles grâce à toi, je suis super content :)

Pit3ull · Answer

Bonsoir, Mike,
je suis désolé pour mon silence mais je suis vraiment très pris par mon travail en ce moment je ne trouve pas le temps de m'occuper de cette base de donnée.

La semaine dernière, j'ai tout de même réussit à adapter le super travail que tu m'as fais à ma base réelle.

Je commencerais à avoir plus de temps à compté de vendredi 30 donc je ferais le fichier que je citais plus haut avec tout les commentaire comme promis.

Encore désolé, je ferais une remontée du post si cela ne te dérange pas?

Cordialement, Benoît

Gestion chambres collectives sous Excel 2003

23 réponses