Formule Excel ou macro à partir de plusieurs variables et conditions
Bonjour
Je cherche dans le tableau ci-joint à calculer un montant en fonction de 2 critères fixes puis de plusieurs variables
La colonne dans laquelle je cherche à calculer le montant est la colonne « hybride » (colonne W de l’onglet « base de données »)
L’autre onglet « hypothèses » montre la grille tarifaire à prendre en compte
Il y a au départ 2 conditions fixes (les mêmes que pour les colonnes « hypothèse barème » et « taux d’effort ») :
- Si la colonne « total » (colonne T) est < ou égale à 0, alors la colonne W est égale à 0.
- Si la colonne « tarifs / réductions » (colonne R) est égale à « tarif frais de dossier », alors la colonne W est égale à 30
En dehors de ces 2 conditions, il s’agit de calculer le montant à appliquer en fonction des éléments suivants :
- Type de tarifs (colonne D)
- QF mens (colonne K)
- Tarifs / réduction (colonne R)
La table à prendre en compte dans l’onglet « hypothèses » est le tableau qui va de la cellule BW1 à la cellule CR21.
Selon le tarif / réduction (colonne R) pratiqué et le type de tarifs (colonne D) indiqué, le montant à indiquer colonne V correspond au QF mens (colonne K) multiplié par le taux d’effort indiqué dans la colonne CC, CF, CI, CL, CO ou CR de l’onglet "hypothèses", la colonne à retenir étant celle qui correspond au nom de l’activité figurant dans la colonne « tarifs / réductions (colonne R)
Par exemple : si un ménage relève du tarif « Ville », a un QF mens de 1.070, et se voit pratiquer un tarif courant, alors le montant à appliquer correspondra à QF mens * cellule CF6 de l’onglet « hypothèses » (il s’agit de la cellule CF6 car 1.070 se situe dans la tranche QF n°5 – cf. colonnes BX et BY de l’onglet « hypothèses » ; la tranche de QF correspondant à la valeur du QF mens se calcule automatiquement colonne M de l’onglet « base de données »)
Autre exemple : si un ménage relève du tarif « Hors Ville », a un QF mens de 1.843, et a un tarif cumul, alors le montant à appliquer correspondra à QF mens * cellule CO19 de l’onglet « hypothèses » (il s’agit de la cellule CO19 car 1.843 se situe dans la tranche QF n°8 – cf. colonnes BX et BY de l’onglet « hypothèses »)
Il existe 2 « variantes » à cette règle, pour la première et pour la dernière tranche de la grille :
- Lorsque le QF mens (colonne K) est égal à la cellule BX2 (tarif « Ville ») ou BX12 (tarif « Hors Ville ») de l’onglet « hypothèses », la valeur que doit renvoyer la cellule correspondante colonne W est celle du tarif minimum du tarif concerné (par exemple : cellule CG2 pour le tarif « pratiques collectives », pour le type de tarif « Ville », ou cellule CJ12 pour le tarif « Tarif troisième cycle renforcé PPES », pour le type de tarif « Hors Ville »).
Le QF maxi de la tranche doit lui renvoyer la valeur du tarif maxi de la tranche. Ainsi, lorsque le QF mens (colonne K) est égal à la cellule BY2 (tarif « Ville ») ou BY12 (tarif « Hors Ville ») de l’onglet « hypothèses », la valeur que doit renvoyer la cellule correspondante colonne W est celle du tarif maximum de la tranche concernée du tarif concerné (par exemple : cellule CH2 pour le tarif « pratiques collectives », pour le type de tarif « Ville », ou cellule CK12 pour le tarif « Tarif troisième cycle renforcé PPES », pour le type de tarif « Hors Ville »).
- A l’inverse, lorsque le QF mens (colonne K) est égal ou supérieur à la cellule BY11 (tarif « Ville ») ou BY21 (tarif « Hors Ville ») de l’onglet « hypothèses », la cellule W doit renvoyer le tarif maximum du tarif concerné (par exemple : cellule CE11 pour le tarif « courant », pour le type de tarif « Ville », ou cellule CQ21 pour le tarif « Tarif location », pour le type de tarif « Hors Ville »).
J’avais déjà sollicité le forum pour cette formule ; j’avais eu une réponse sous forme de macro qui m’a été très utile, mais je n’avais spécifié ces 2 variantes, donc la macro n’en tenait pas compte.
Ne maitrisant pas les macros pour ma part, j’ai produit une formule que vous pourrez voir colonne V : celle-ci fonctionne pour toutes les tranches de 2 à 9 du type de tarif « Ville » (colonne D), mais elle ne résout pas 2 problèmes :
1. Elle ne tient pas compte du type de tarifs « Hors Ville » » (si je prends l’exemple de la ligne 212, la cellule W212 renvoie 1a valeur de 813,3568548, ce qui correspond au produit de la cellule K212 et de la cellule CF5 de l’onglet « hypothèses » ; or la cellule K212 devrait être multipliée par la cellule CF15, correspondant à la tranche 4 du type de tarifs « Hors Ville ».
2. elle ne tient pas compte non plus des 2 variantes exposées plus haut, sur la 1ère et la dernière tranche de chaque tarif.
Difficulté supplémentaire… : les valeurs de la plage CA2 / CR21 sont amenées à changer pour réaliser des simulations. Dans ces simulations, la valeur du QF mens peut être amené à changer de tranche. Par exemple : avec les tranches actuelles, un QF mens de 1.000 correspond à la tranche de 4, mais elle pourrait très bien se retrouver dans la tranche 2, 3, 5, 6 ou autres en fonction des simulations réalisées. C’est donc un paramètre dont il faut tenir compte : il faudrait donc que la formule ou la macro tienne compte des éventuels « glissements » de tranches.
Merci d’avance pour votre aide ; j’ai essayé d’être le plus détaillé possible au risque d’être un peu « lourd » à la lecture.
Je reste bien sûr disponible s’il y a des questions
Bien cordialement
erratum par rapport à mon précédent message: vous pouvez voir la formule que j'ai produite colonne W et non colonne V
Merci
Salut Almapier,
Tes explications sont vraiment très longues et je n’ai pas lu jusqu’au bout. Mais l’on continuera la discussion sur la base du fichier que je te fournis ici.
J’ai repris la macro que t’avais fournie Optimix sur ton autre fil et je l’ai modifiée selon ce que j’ai compris de tes problèmes. Ca a été assez compliqué, puisque tu as modifié l’ordre des colonnes et certains de tes textes. A éviter par la suite, si possible.
Les données de la colonne W sont a chaque fois effacées et recalculées.
Tu dis que les valeurs de la plage CA2 / CR21 sont amenées à changer pour réaliser des simulations, mais je pense plutôt que ce sont les valeurs dans les colonnes BX et BY de la feuille ‘’Hypothèses’’ qui sont importantes. Ma macro tient compte d’un éventuel ‘’glissement’’ dans ces colonnes. Ou alors je n’ai pas compris de quel glissement tu parles. Fais éventuellement des essais de cela et redis-moi si nécessaire.
Mais fait bien entendu des essais de la totalité des résultats ; si ça ne joue pas, indique-moi une ou deux lignes qui ne sont pas correctes et indique-moi comment auraient dû être calculés les résultats attendus.
A te relire.
Bonjour,
bien reçu, je te remercie.
Je vais prendre le temps d'effectuer plusieurs tests et je reviendrai vers toi pour te dire ce qu'il en est
Merci
Bonsoir,
encore merci pour le temps passé et l'envoi du fichier.
Voici mes retours ci-dessous:
Certaines valeurs renvoyées sont ok et d'autres non. Tout d'abord, il y a un invariant: lorsque la colonne R est égal à "tarif frais de dossier", alors la colonne W est égale à 30. Cela est valable quels que soient les valeurs des autres critères.
Pour le reste, je te donne plusieurs exemple de lignes pour lesquelles la valeur renvoyée colonne W est incorrecte:
- ligne 17: la valeur de W17 devrait être de 256,90: D17 est égal à "Ville"; la tranche QF hybride (cellule M17) est égale à 2, et R17 est égal à "tarif courant". On va donc chercher dans le tableau de l'onglet hypothèses le taux d'effort qui correspond à ces 3 critères. En l’occurrence, cela correspond ici à la cellule CF3 de l'onglet hypothèses, que l'on multiplie par la cellule K17
- ligne 100: la valeur de W100 devrait être de 270,59: D100 est égal à "Hors Ville"; la tranche QF hybride (cellule M17) est égale à 3, et R17 est égal à
"tarif troisième cycle renforcé PPES". Le taux d'effort qui correspond à ces 3 critères est la cellule CI14 de l'onglet hypothèses, que l'on multiplie par la cellule K100
Par ailleurs, il y a une autre règle "intangible": il ne peut pas y avoir de valeur dans la colonne W (hors "tarif frais de dossier" colonne R) qui soit inférieur aux tarifs mini indiqués (correspondant aux colonnes CA, CD, CG, CJ, CM ou CP de l'onglet hypothèses, selon le tarif pratiqué)
Par exemple, sur la ligne 2: la cellule W2 est < au tarif mini correspondant au type de tarifs (D2) et au tarif (R2) appliqué. Dans ce cas, la cellule doit renvoyer le tarif mini correspondant à ces 2 critères, soit ici la cellule CJ2 (791€) de l'onglet hypothèses
Autre exemple ligne 366: W366 devrait être égal à 66€ (cellule CG12), D366 étant égal à "Hors Ville" et R366 étant égal à "tarif pratiques collectives"
Dans le même ordre d'idées, il ne peut pas y avoir de valeur dans la colonne W qui soit supérieur aux tarifs maxi indiqués (correspondant aux colonnes CB, CE, CH, CK, CN ou CQ de l'onglet hypothèses, selon le tarif pratiqué). Ainsi, lorsque que QF mens (colonne K) est > au QF maxi de la table (cellule BY11 ou cellule BY21), c'est le tarif maxi concerné qui s'applique.
Par exemple, ligne 278: W278 devrait être égal à 1.067€ (cellule CE11 de l'onglet hypothèses), D278 étant égal à "Ville" et R278 étant égal à "tarif courant".
Autre exemple ligne 303: W303 devrait être égal à 621€ (cellule CH21 de l'onglet hypothèses), D303 étant égal à "Hors Ville" et R303 étant égal à "tarif pratiques collectives"
Pour les glissements, je pense en fait qu'il faut rattacher la colonne M qui se calcule automatiquement, avec la colonne BW de l'onglet hypothèses, puis en utilisant une formule ou un code qui viendrait récupérer le bon taux d'effort à appliquer quand la valeur de M est = à la valeur de BW, + quand la valeur de D est = à BZ et quand la valeur de R est = à BI, BJ, BK, BL, BM ou BN
J'espère que ces exemples seront aidant; n'hésite pas à revenir vers moi si ce n'était pas le cas
Encore merci
Cordialement
Sur la base de tes informations, j'ai découvert quelques erreurs que j'avais faites et qui devraient être corrigées.
Concernant ton exemple de la ligne 100, tu dis qu'il faudrait tenir compte de la donnée de la cellule CI14 de la feuille ''hypothèses'' alors que je pense que c'est plutôt celle de la cellule CL14, vu le texte en colonne R de la feuille de base.
Pour aider à s'en sortir, j'ai provisoirement reporté quelques infos en colonnes Z à AC de la feuille de base.
Pour cette ligne 100, tu vois en plus que le montant minimum correspondant à ce "Tarifs / Réductions" de la colonne R n'est pas atteint et que c'est ce minimum qui est inscrit. Es-tu certain de ton indication en R100 ?
A te relire.
PS :
On pourrait imaginer ajouter en colonne X des informations telles que le taux appliqué ou si le minimum ou le maximum a été atteint (clique sur l'image pour la voir un tout petit peu mieux).
Bonjour,
merci pour ce nouvel envoi.
J'ai balayé rapidement et tout me semble correct!
Pour la ligne 100, tu as raison, c'était bien la cellule CL14 qu'il fallait prendre en compte, excuse-moi pour l'erreur...
PS: les infos colonne X seraient la cerise sur le gâteau, mais les indications colonnes Z à AC sont déjà très utiles, et je ne voudrais pas abuser de ton temps. Pour moi la mission est déjà largement remplie en l'état!
il faut que je prenne le temps de faire des simulations avec des QF qui diffèrent pour voir si tout fonctionne dans tous les cas de figure; si c'est le cas je validerais ta dernière réponse comme étant celle ayant résolu le problème posé!
Un grand merci à toi
Bien cordialement
Tant mieux si le résultat correspond à ton attente
Afin de pouvoir te montrer une image de l'amélioration proposée, j'ai déjà dû effectuer les modifications nécessaires. Je peux dont te fournir cette version_3 (_v3) en plus, ci-jointe.
J'ai réuni dans la colonne X de ce dernier fichiers, trois des quatre données qu'il y avait dans les colonnes Z à AC du fichier _v2. Si tu trouves que les données indiquant l'adresse des cellules utilisées de la feuille ''hypothèses'' sont également utiles, on pourrait les ajouter dans la colonne Y.
Au point où nous en sommes, ce serait bien dommage que tu aies encore besoin de quelque chose et que tu n'oses pas le demander afin de ne pas abuser de mon temps ! Je suis très volontiers à ta disposition pour tout ce dont tu pourrais encore nécessiter.
Amicalement.
Bonjour,
merci beaucoup, pour moi la V2 convient très bien et me permet de travailler!
Question annexe: en fonction des simulations réalisées, je serai peut-être amené à individualiser les tranches de QF en fonction du tarif.
Par exemple avoir une 1ère tranche de 0 à 400 pour le tarif éveil, et de 150 à 300 pour le tarif courant.
Est-ce que je pourrais te solliciter de nouveau si le cas de figure se présente?
Encore merci pour ton aide!
Salut l'Ami,
Il me semble que j'ai déjà répondu dans la dernière phrase de mon dernier message
A la revoyure !