XL 2013 Planning covoiturage

[kalem]

Bonjour à tous,

J'ai un souci qui me bloque depuis un moment et qui dépasse de très loin mes compétences sur Excel. Nous essayons de mettre en place un covoiturage avec des collègues, mais nous n'avons pas tous le même emploi du temps.
Je vais donc proposer aux collègues de répondre à un sondage Googleform dont les résultats seront ensuite copiés dans une feuille excel. En fonction de ces données, sur une deuxième feuille, Excel aurait deux missions :
- proposer les covoiturages possibles en fonction des horaires des uns et des autres,
- calculer ce que chacun doit aux autres, selon qu'il ait été conducteur ou passager.

Je rencontre pour ainsi dire des problèmes aux deux étapes :
- Pour les appariements, j'ai essayé par exemple pour lundi 8h :
=SI(NB.SI([différentes possibilités d'horaires];"8:00")>1 (c'est-à-dire, s'il trouve plusieurs personnes avec le même horaire) ;SI(ESTNUM(CHERCHE("Lundi";[plage où la personne indique les jours où il veut être conducteur];[indique le nom];FAUX); "Pas de possibilité")
Cela m'indique "pas de possibilité", alors qu'il y en a bien...

- Pour les frais, je ne parviens pas non plus à donner à chacun ce qu'il doit aux autres... (voir pièce jointe).

L'organisation de ce covoiturage est une vraie galère mais cela pourrait nous être très rentable ! Merci d'avance pour votre aide précieuse!

 

[kalem]

J'avance petit à petit... Je crois avoir résolu le deuxième souci avec la formule NB.SI.ENS (je posterai la dernière version quand ce sera plus propre). Pour le reste, je vais essayer de scinder les opérations, quitte à masquer certaines colonnes.
Par contre, je ne parviens pas à constituer des groupes de personnes. Sachant que dans la feuille 1, j'ai en B les noms des personnes et en C les horaires, je voudrais que sur la fiche planning, Excel me liste les personnes dont les horaires concordent, j'ai essayé :

=SI(C2="*"&"8h"&"*";B2;"")

Mais d'abord, ça ne fonctionne pas, et par ailleurs, même si ça marchait, il y aurait des blancs pour les personnes n'ayant pas indiqué "8h", ce qui me met en défaut pour l'étape suivante (constitution des groupes)... Si quelqu'un a une idée, je suis preneur !

 

[zebanx]

Bonjour Kalem,

Sujet intéressant pour l'utilisation de GoogleForms et de ce que tu souhaites répertorier.

Et je ne répondrai que partiellement à ta demande mais il faut bien que quelqu'un commence (!).

Au préalable, il y a me semble-t-il des incohérences dans les réponses des 3 dernières personnes.
- soit tu es passager ou conducteur sur toute la plage entre le lundi et le vendredi mais pas l'un ou l'autre,
- soit tu n'es passager ou conducteur que pour entre 1 et 5 jours sur cette plage et dans ce cas là tu n'indiques pas les plages horaires sur les jours ou tu n'es finalement ni passager ni conducteur.

Les réponses de Marty et Bruno sont ok par contre.

Après, l'idée serait peut-être de restituer sous un tableau à double entrée (heures et jour de la semaine) et l'utilisation pour chacun d'un code couleur qui permet d'identifier s'il souhaite être conducteur ou passager et à quelle heure de la journée.

Par contre, je ne peux pas t'aider pour la répartition aléatoire, je ne sais pas faire.

Mais ça permet déjà d'y voir un peu plus clair directement dans la répartition.
En espérant que cela constitue une piste exploitable pour toi pour avancer dans ce projet.

Bonne journée

 

[kalem]

Bonsoir zebanx, bonsoir le forum,

Merci pour cette réponse ! Bon, tu mets le doigt sur un souci qui me compliquait bien la vie, la distinction passager/conducteur. Dans l'idée, je pense que tout le monde est prêt à être l'un ou l'autre, dans la mesure où c'est bien spécifié et que la répartition des frais reste équitable. Je vais peut-être, du coup, enlever ce paramètre, ce qui va simplifier l'ensemble. Après, pour te répondre, j'ai rempli le googleform au hasard pour avoir des données à tester, tu as raison, j'aurais dû faire attention...

Autre souci que j'avais : les heures notées comme telles avaient tendance à donner des résultats aux formats exotiques dès que j'essayais de les inclure dans une formule, donc, je les remplace par "8h", "9h", etc.

L'idée de tout récapituler avec le tableau est excellente, je valide. Il faut maintenant que j'arrive à trouver la bonne formule pour que ce planning se remplisse tout seul. Cela me pose deux problèmes : d'une part, je n'arrive pas à faire un RECHERCHEV à gauche (j'ai vu l'astuce de combiner DECALER et EQUIV, mais je ne dois pas avoir fait la bonne manipulation), par ailleurs, il faudrait que les résultats de cette RECHERCHEV renvoie tous les passagers dans la même cellule (d'après ce que je comprends, il faut passer par du VBA...).
Ensuite, il suffira de mettre une MFC pour qu'Excel mette en surbrillance les doublons de plus d'un passager, et c'est dans la poche...

 

[zebanx]

Re-

Merci pour ton retour détaillé.
Par contre, je ne comprends pas ton dernier paragraphe sans un fichier.
Si d'autres comprennent le "recherchev à gauche", qu'ils n'hésitent pas à répondre !

++
zebanx

nb : je vais faire une demande concernant une transposition de ton tableau googleforms suivant les heures dans une grille.
Ca pourrait faire avancer le sujet (c'est un point de vue..)

 

[kalem]

Je vais essayer de mettre à jour le fichier.
Pour préciser mon propos (... mais j'ai fini par trouver !) : par exemple pour l'horaire du lundi "8h", Excel doit trouver dans les résultats du sondage toutes les cellules de la colonne C (lundi) qui contiennent "8h" et indiquer le nom du covoitureur correspondant (colonne B). Il s'agit donc bien d'un RECHERCHEV, mais vers la gauche. La solution est là :

=DECALER(B1;EQUIV("8h";C2:C8;);0)

Reste à afficher tous les résultats dans une même cellule (celle indiquant le jour et l'heure, comme tu le suggérais).

 

[zebanx]

Bonjour Kalem,

Avec l'aide des codes de Klin89, de JB, je vous propose un fichier.

Quelques précisions :
- J'ai du supprimer la colonne A (vous pouvez la déplacer dans la version finale) sur la feuille de départ car j'ai du mal à faire passer le code de KLIN89 avec.
- 3 codes couleurs : noir (conducteur), rouge (passager), vert (passager ou conducteur)
- les combinaisons sur le dernier tableau sont passées en vert (manuellement)
- il est normal d'avoir une première ligne sans heures puisque certains jours, il n'y a pas besoin de se déplacer.

Il y a 3 macros :
- la première passe de tableau à BDD
- la seconde permet de mettre une couleur dans la colonne A sur la feuille RES suivant le code couleur initial (colonne E) qui vient de la cellule de départ
- la troisième retranscrit en tableau

Cela permet d'avoir une bonne lisibilité sur le champ des possibles.

++
zebanx

 

[kalem]

Bonjour zebanx, le forum,

Merci pour ce travail, et bravo ! Je valide le code couleur, c'est astucieux et clairement identifiable. Idem pour la ligne sans heures. Il faudrait que je fasse des tests avec différents horaires, mais ça me semble fonctionner tout à fait...!!! Vu comme ça, la VBA, c'est vraiment magique

Il me reste à transposer sur ce fichier le calcul des frais. Je mets à tout hasard celui que j'avais bidouillé mais il n'est plus valide. Il faudrait maintenant faire une formule avec condition de couleur, je ne sais pas si c'est possible, je vais me pencher sur la question...

Encore merci !

 

[kalem]

Arf, je renonce, je n'ai pas assez de neurones...
On pouvait y arriver en utilisant la colonne E qui indique la couleur, mais cela conduit à refaire tout le raisonnement de la macro (rechercher les covoitureurs du même horaire et distinguer ceux qui sont conducteurs ou passagers, en traitant le cas de ceux qui peuvent être les deux...! )
A tout hasard, je renvoie le fichier en l'état, avec deux bouts de formule pour aller dans le raisonnement suivant : si une ligne indique "1" (couleur conducteur), alors il faut diviser le trajet par le nombre de covoitureurs (conducteurs + passagers)... mais ça ne marche pas...

 

[zebanx]

Re-

Ma limite s'est arrêtée.
C'est d'ailleurs un nouveau sujet intéressant.
Arriver à spliter des frais en fonction des couleurs de polices et d'une possible combinaison.

Avec un petit format comme celui-là, il doit être envisagé de le faire "manuellement" mais d'accord avec toi, si on pouvait le faire par formule, ce serait idéal.

Bon apm
zebanx

 

[kalem]

Bonjour à tous,
Je vais voir ça, zebanx, à l'usage. En fait, on espère pouvoir faire monter le groupe de covoitureurs à une quinzaine, donc très vite, les calculs de frais risquent d'être fastidieux. Mais encore une fois, on a déjà de quoi faire avec la version en l'état ! Merci encore !