formule conditionnelle complexe Résolu/Fermé

Question

Bonjour, 

je ne suis pas un as sur excel 2010, mais dans le cadre de mon travail, je souhaite me simplifier la vie avec une formule conditionnelle.

J'ai un tableau excel avec en ligne des tranches de salaire et en colonne la composition familiale, quand on croise le tout cela donne un pourcentage (coefficient). extrait du tableau

Revenu de référence 	Célibataire	Célibataire	Célibataire	Couple
		                                                et 1 enfant	et 2 enfants     et 3 enfants
< 16 215	                            60                 	60	            60	               60
de 16 216 à 16 839	            58	                60	            60	               60
de 16 840 à 17 463	            56	                60	            60	               60
de 17 464 à 18 086       	    54	                60	            60	               60
de 18 087 à 18 710	            52	                60	            60	               60
de 18 711 à 19 334	            50	                59	            60	               60
de 19 335 à 19 957	            48	                58	            60	               60


le tableau fait 13 colonnes et 110 lignes (avec diverses situations, célibataire 3 enfants etc..., couple sans enfant à couple avec plus de 5 enfants)

L'idée est de permettre à celui qui saisi, de renseigner le revenu de référence et excel ferait apparaitre le coefficient selon la situation de la personne. Exemple :

revenue de référence saisi 18850, la personne est célibataire, excel doit me donner le chiffre 52.

Je vous en remercie d'avance pour votre aide




Configuration: Windows 7 / Firefox 10.0.2

chossette9 · Answer

Bonjour, 

1°) L'exemple donné est très peu clair, car le tableau n'est pas bien représenté.
Merci de joindre un exemple via le site cjoint.com (fichiers sans données confidentielles, format 97-2003). Créer un lien, le copier, et le coller dans une réponse.

2°) Si les exemples données sont faux on ne va pas y arriver :) Selon votre Exemple 18850 célibataire, pour moi je vois 50

Cordialement.

cousinhub29 · Answer

Bonjour,

En l'état, l'utilisation de ton exemple est très difficile...

Pourrais-tu joindre un fichier simplifié, sans données confidentielles, sur le site www.cjoint.com , et coller le raccourci ici?

@ te relire

marvel · Answer

Désolé, mais effectivement le tableau n'est pas claire dans le message, car la mise en forme n'est gardée. Voici le lien permettant d'avoir le tableau excel entier 

http://cjoint.com/?BCgncnFo6YE

Merci pour votre aide

cousinhub29 · Answer

Bonjour,

https://www.cjoint.com/?BCgnLDOZTj6

J'ai un peu modifié ton tableau (2 colonnes pour les revenus min et max, mise en forme conditionnelle pour colorier les lignes paires, ajout de zones nommées, que tu peux voir dans Insertion/Nom/Définir, ou Ruban "Formules", Gestionnaire de noms si tu as 2007 ou ultérieur)

Dans les cellules O3, O4 et O5, tu renseignes les données
Dans la cellule O6, une formule matricielle :

{=INDEX(quot;EQUIV(O5;revenu_min;1);EQUIV(O3&O4;situation&enfants;0))}

regarde, et reviens si tu n'arrives pas à adapter

bon courage

marvel · Answer

Un très grand merci cousinhub29, tu m'enlèves une grande épine du pied. Avec quelques modifications, cela colle tout à fait à ce que je voulais. Super boulo.

marvel · Answer

Je continue à peaufiner mon tableau, pourrais tu SVP cousinhub29, m'indiquer comment tu modifies la cellule cellule déroulante situation et  Nb enfant ? Merci d'avance

cousinhub29 · Answer

Re-,

Comme tu le vois, dans ces cellules, il y a une liste de validation.
Si tu es sous 2003 ou avant, tu fais "Données/Validation", et regarde ce qui est marqué.
Si tu es sous 2007 ou après, dans le ruban "Données/Validation des données"
Dans le cas de la cellule "Situation", les données source se situent dans la colonne S, de S1 à S2
Je te laisse regarder pour les autres
Reviens, si tu n'y arrives pas

Bon courage

marvel · Answer

Bonsoir cousinhub29, toutes tes explications me sont bénéfiques pour le moment. Tout marche super. Je dois maintenant ajouter des lignes au tableau avec des nouveaux revenus et coefficients. Quand je l'ai fait, le résultat Quotient me donne une erreur avec le mot #VALEUR!

En affichant l'étape de calcul, la formule me donne un maximum de 43 lignes alors que maintenant j'ai 109 lignes.

J'ai essayé de comprendre, mais mon cerveau a explosé. Merci pour ton aide

cousinhub29 · Answer

Bonsoir,

On voit ça demain, si cela ne te dérange pas....

Bonne soirée

marvel · Answer

Bonjour cousinhub29. As-tu eu l'occasion de voir mon problème ? Merci

cousinhub29 · Answer

Bonjour,

Pour aller plus vite, peux-tu me donner ta version Excel (2003 ou inférieure, 2007 ou Supérieure?)

Afin de prendre en compte de futures modifications, je vais t'expliquer comment déterminer des zones nommées dynamiques.

@ te relire

marvel · Answer

c'est une version excel 2010. merci

cousinhub29 · Answer

Re-,

Tout d'abord, on va maintenant travailler sur 2 onglets, le premier contenant le tableau de données, et le 2ème, les recherches.

Dans la formule, tu as vu qu'on utilise des zones nommées ("quot", "revenu_min", "situation" et "enfants")

On va donc faire en sorte que les zones nommées soient dynamiques.

Lorsque tu cliques dans le ruban "Formules" sur "Gestionnaire de noms", tu vois ces noms.

En cliquant sur "Revenu_min", tu vois en bas, dans la case "Fait référence à :" 

=Feuil1!$A$3:$A$43

Remplace par :

=DECALER(Feuil1!$A$3;;;NBVAL(Feuil1!$A:$A)-2)

Regarde le fichier joint, j'ai mis à jour, et observe bien les zones nommées (pour voir leur portée, tu cliques au milieu de la formule, lorsque tu es dans le gestionnaire de noms)

https://www.cjoint.com/?BChli71EtxW

Ainsi, même si tu ajoutes des lignes et/ou colonnes, ce sera pris en compte.

Reviens si problème(s)

Bon courage

marvel · Answer

désolé mais je n'arrive pas à télécharger le tableau, j'ai des pictogrammes et pleins de sigle.

cousinhub29 · Answer

re-,

En haut, au milieu, tu as un lien nommé "quotient_v2.xlsx"

tu cliques droit dessus, Enregistrer la cible du lien sous....

Et tu enregistres

bon courage

marvel · Answer

Merci énormément pour ton aide, tout fonctionne nikel. C'est exactement ce que je voulais. Tu es un chef.

Formule conditionnelle complexe

16 réponses

Discussions similaires