Projet Excel - Suivi de l'activité d'un organisme de formation
Bonjour à tous et toutes !
Je suis en train d'essayer de mettre en place un système + moderne dans ma structure, pour gérer le quotidien. Mais malheureusement je crois que je suis arrivé au bout de ce que je connaissais jusqu'ici sur Excel pour y parvenir, d'où cette bouteille à la mer que je vous envoi ;-)
Voici quelques infos pour mieux comprendre et pouvoir je l'espère m'aider :
Le contexte : Je travaille pour une structure de formation qui facture à l'heure ses élèves, différents types de formation (à différents tarifs) et avec différents instructeurs évidemment. Actuellement tout est géré "à l'ancienne" sur papier.. aussi invraisemblable cela soit-il. C'est à dire que par exemple les fiches élèves répertoriant toutes les heures de formation (aux différents tarifs) sont en format papier... Les instructeurs ont eux aussi besoin d'avoir un compte rendu mensuel de leurs heures effectuées (ce qui est aussi fait manuellement, mais sur un Excel.. c'est déjà ça). Voici les informations nécessaires pour la fiche élève (le plus important à mettre en place) :
| Date | Temps | Type cours | Montant | Versement | Solde |
Le projet : Je voudrais créer un/plusieurs fichiers Excel qui communiquent entres eux afin de digitaliser toutes ces données et de grandement faciliter la vie de la secrétaire. L'idée serait que les instructeurs, à l'issu de leurs cours, rempliraient un Forms pour alimenter un Excel contenant toutes les informations nécessaires au reste. En soi, il s'agirait d'une base de donnée avec plusieurs tables, malheureusement je n'ai aucune connaissances en Access et je souhaite autant que possible rester simple, sur Excel.
Le(s) souci(s) : Il y en a plusieurs. Mais le premier que j'ai déjà identifié et pour lequel je demande de l'aide en premier, est celui du "calcul automatique" des formules. Je m'explique : les prix peuvent varier avec le temps. Un élève qui reste 6 mois ou un an dans la structure, ne paiera peut-être pas le même tarif aujourd'hui que demain. Il faudrait donc que je trouve un moyen de bloquer le calcul du tarif.
Pour l'instant, je compte avoir une feuille "Tarifs" avec les tarifs horaire de chaque type de cours (par exemple "THEORIE" ou "PRATIQUE" ne sont pas au même prix).
Admettons par exemple que l'élève ait fait un cours THEORIE facturé alors 80€ de l'heure, je voudrais que le fichier cherche (RECHERCHEV...) le prix horaire d'un cours théorique, le multiplie par le temps (1,5 pour 1h30 de cours par exemple) et calcul ainsi le montant à facturer (colonne Montant dans la fiche élève). Mais que ce montant soit ensuite arrêté ! Et qu'il ne se recalcule pas dès qu'un changement de tarif sur la feuille "Tarifs" a lieu. Comme une entrée dans une base de données.
Merci d'avance, j'essaye d'être le + clair possible..
Bonjour
Qu'on travaille sur un SGBD ou Excel pour les tarifs on crée une table des tarifs avec le code du produit le tarif et la date d'entrée en vigueur, éventuellement la fin de validité (parfois plus simple pour les calculs).
Cela à les avantages de
- prévoir à l'avance les tarifs qui rentreront en vigueur ultérieurement,
- calculer avec le bon tarif les prestations à une date donnée
- mesurer l'évolution des tarifs au fil du temps et sa corrélation éventuelle avec d'autres paramètres
L'important est de bien mettre les besoins à plat avant de créer les classeurs.
PowerQuery intégré à Excel 2016 et +, en add on sur 2013, peut synthétiser n classeurs
Bonjour,
Merci pour cette réponse. Effectivement donc je vais mettre une date de début et fin de validité du tarif alors.
J'ai créé le fichier Tarifs ci-joint qui prend en compte les dates de début et fin de validités de ceux-ci.
Quelle formule employer pour rechercher le tarif de la colonne appropriée ("F-BSEA" par exemple) en fonction de la date ?
RE
Il faut travailler avec des tableaux structurés (existes depuis plus de 18 ans dans Excel.
Attention cependant les formules doivent être identiques sur toutes les lignes d'une colonne. Là tu as fait un exemple mais à noter pour l'avenir.
Les retours manuels à la ligne dans les titres peuvent compliquer certaines opérations donc à utiliser avec parcimonie, d'autant que le retour auto à la ligne suffit souvent.
Bonjour à tous,
Je me suis permis d'ajouter une liste déroulante sur les services dans la solution de chris. (onglet cours)
J'ai essayé une liste déroulante sur la date et les services (onglet tarifs), et si l'equiv de chaque critère donne un résultat, le groupement avec index bug à partir du 3éme service
Merci de votre aide
Cdt
Bonjour
Tu as inversé les paramètres de INDEX : on cherche verticalement avec de chercher horizontalement, ce qui est le cas dans l'onglet simple
INDEX(matrice;y;x)
Super, merci