Organisation du covoiturage

Question

Je cherche à créer un formulaire d'inscription sur des trajets pour organiser le covoiturage dans mon entreprise. Les agents peuvent s'inscrire sur des trajets prédéfinis (tous les dimanches et vendredis) pour aller de nos locaux (A) jusqu'au site de formation (B). Nous pouvons accueillir 8 stagiaires par trajet (minibus). Je ne veux pas d'un simple tableau à compléter pour éviter les manipulations malveillantes. Je suis donc parti sur un feuillet protégé ("Covoiturage") avec un encart de saisie (NOM/Trajet1/Trajet2), un bouton de validation et un tableau en lecture seule. Les données validées sont stockées sur un feuillet "Données" qui sera protégé et caché. Les formules que j'ai trouvées permettent de renseigner la première colonne mais je n'arrive pas à remplir les autres. Dans mon idée, la colonne 2 est renseignée si la première est pleine, etc... Cela permettrait d'afficher tous les stagiaires inscrits par trajets . Sur cette photo, le résultat souhaité... mais seule la première colonne se complète. J'imagine qu'il existe des formules plus simples ou des méthodes plus appropriées (tableau croisé dynamique, VBA...). Malheureusement, je suis débutant. C'est pour cela que je fais appel à votre aide. Comment devrais-je m'y prendre ...

LouReeD · Accepted Answer

Décomposition de la formule : =SIERREUR(INDEX(Saisies_2024&#91;NOM&#93;;PETITE.VALEUR(SI((Saisies_2024&#91;DATE&#93;=&#91;@DATE2024&#93;);LIGNE(Saisies_2024&#91;DATE&#93;);9^9);COLONNE()-5)-3);"") le SIERREUR pour éviter d'afficher un message d'erreur si l'INDEX en retourne une. SI((Saisies_2024&#91;DATE&#93;=&#91;@DATE2024&#93;);LIGNE(Saisies_2024&#91;DATE&#93;);9^9) on va tester la date de la ligne où se trouve la formule avec les dates de la colonne Date du tableau qui reprend ces dates avec le conducteur. Si c'est égal alors la fonction SI renvoie le numéro de la ligne de la donnée de la colonne DATE du tableau, sinon elle renvoi une "grande valeur" correspondant à 9 puissance 9. dans le fichier fourni en ligne 4 pour le dimanche 1/09 le SI reverra les numéros 6,7 et 8 correspondant aux cellules O6,O7 et O8. Une fonction SI ne renvoie pas de "matrice" mais comme elle est inclue dans la fonction Petite.Valeur on aura bien une matrice en retour. petite erreur de ma part, je crois que Excel 2019 sait automatiquement gérer les formules matricielles, donc pour vous la formule devra être validée par la combinaison des trois touches suivantes : CTRL+SHIFT+ENTREE, des accolades {} devraient alors encadrer la formule. Ensuite dans cette matrice de petit numéro de ligne et de grand, pour la colonne F nous recherchons le premier 01/09, pour cela il y a le paramètre "k" de cette fonction, et afin que la formule soit étirable vers la droite, on rend le "k" dynamique en le remplaçant par la fonction Colonne(). Mais en colonne F le k vaudrait 6, or nous voulons la première petite valeur, donc on fait 6-5, Colonne()-5. On a bien la première valeur, donc en colonne F et ligne 4 petite valeur correspond à une matrice représentant le résultat des tests du SI, et on demande à renvoyer la première petite valeur : PETITE.VALEUR({387420489;387420489;6;7;8;387420489;...};Colonne(F)-5) = PETITE.VALEUR({387420489;387420489;6;7;8;387420489;...};1) première petite valeur = 6 avec la fonction INDEX qui cible la colonne NOM du tableau on retourne la 6 ligne de cette colonne donc, mais ce n'est pas bon car les données de cette colonne du tableau ne commence pas en ligne 1 mais en ligne 4, donc on applique une différence de 3 : INDEX(Colonne nom du tableau; ligne à retourner -3) où ligne à retourner est le résultat de Petite Valeur = 6, donc 6-3 = 3, donc index renvoi la troisième valeur de la colonne Nom du tableau = AUGIER. Quand la formule passe en G, colonne() passe à 7, 7-5 = 2, en colonne G INDEX renvoi la deuxième petite valeur qui est 7 auquel on retire 3 = 4, quatrième ligne de la colonne Nom du tableau = LOEB etc... Votre fichier avec la formule validée en matricielle, 2019 me les supprimes lors des recopies... donc la matricielle doit être bonne en F4, à vous de la propagée sur la droite : Ceci dit comme écrit dans ma signature je suis resté en 2014... Le mieux est de s'orienter vers les "nouveaux" outils fournis avec Excel tel que PowerQuerry ! @ bientôt LouReeD...

78chris · Answer

Certains peuvent ne faire que l'aller ou le retour ? Passer plus d'une semaine sur place ...

LouReeD · Answer

78chris bonsoir, une proposition en cellule F4 : =SIERREUR(INDEX(Saisies_2024&#91;NOM&#93;;PETITE.VALEUR(SI((Saisies_2024&#91;DATE&#93;=&#91;@DATE2024&#93;);LIGNE(Saisies_2024&#91;DATE&#93;);9^9);COLONNE()-5)-3);"") à tirer vers la droite. N'hésitez pas si vous avez besoin d'explications. @ bientôt LouReeD...

78chris · Answer

Une proposition PowerQuery (intégré à Excel) On saisit toujours tes 2 ou 3 données qui s'inscrivent dans le tableau de 2 colonnes (j'ai totalement remanié ton code manifestement fait avec l'enregistreur). déclenchent la mise à jour du tableau généré par PowerQuery (le nombre de colonnes stagiaires est automatique) Tu peux éventuellement mettre ce tableau sur un autre onglet et en afficher une image forcément non modifiable. De toute façon toute modification ou ajout manuel de ce tableau disparait à la prochaine saisie.....

78chris · Answer

Mais a priori mon post est invisible... La politesse fait partie des espèces en voie de disparition.....

78chris · Answer

Le "texte" est le tien : les noms et les dates car on n'a rien d'autre dans ton exemple. De toute évidence c'est un exemple trop simplifié d'où le problème... PowerQuery existe dans Excel depuis plus de 13 ans et est plus facile à appréhender que VBA que tu ne connais pas non plus (tu peux remarquer que ton code de plus de 50 lignes a été remplacé par 15 lignes)... Certains sur ce forum qui ne connaissaient pas plus PowerQuery que toi, convaincus par son efficacité, s'y sont mis. Mais chacun fait à son idée.....

LouReeD · Answer

Moi je suis convaincu ! Mais je ne m'y suis pas mis... Ce que je regrette un peu ce sont les solutions données en PQ avec parfois (souvent ?) très peu d'explication. A mon goût cela manque de pas à pas pour appréhender les requêtes. Alors oui il y a une somme de tutos là dessus sur la toile mais dans ce cas plus de question... Je ne crois pas avoir vu de "cours" sur ce sujet sur le site, non ? Je ne décourage personne, au contraire, dans mon entourage lorsque l'on me demande une solution "VBA" je réponds souvent : OK, mais cela serait plus simple avec PQ et surtout plus pérenne dans le temps. C'est bien pour cela que je parle de 2014, car à l'époque c'était un AddIn et ma boîte ne le permettait pas... Enfin je crois... @ bientôt LouReeD...

78chris · Answer

RE Dans toutes les réponses que j'ai données au début de mon utilisation de PowerQuery, systématiquement je passais jusqu'à 30 minutes à écrire pas à pas des manips qui prennent 1 à 5 minutes. Mais j'ai fini par me lasser... Je le fais cependant toujours quand on me le demande (gentiment, lol)...

LouReeD · Answer

Il faudrait des cours vu la proportion des réponses "VBA" avec ce module ! @ bientôt LouReeD...