Formule conditionnelle complexe
Résolu
marvel
-
marvel -
marvel -
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
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
A voir également:
- Formule conditionnelle complexe
- Excel mise en forme conditionnelle formule - Guide
- Formule si et - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Formule mathématique - Télécharger - Études & Formations
- Formule somme excel colonne - Guide
16 réponses
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 :
regarde, et reviens si tu n'arrives pas à adapter
bon courage
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
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.
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.
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
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
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
http://cjoint.com/?BCgncnFo6YE
Merci pour votre aide
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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.
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
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
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
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
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
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
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
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 à :"
Remplace par :
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
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