SI + RechercheV - plus simple ?
Kuss
-
michel_m Messages postés 18903 Statut Contributeur -
michel_m Messages postés 18903 Statut Contributeur -
Bonjour,
Je bosse actuellement sur un "tarificateur" sous excel, il est presque fini mais j'aimerai voir s'il n'est pas possible d'améliorer mes formules.(En sachant qu'en l'état tout fonctionne bien)
Pour que vous compreniez mieux, je vais coller une formule et vous expliquer :
=SI(Tarificateur!D1="Régime1";SI(OU(Tarificateur1!F1=amisZ1);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;2;0);SI(OU(Tarificateur!F1=amisZ2);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;7;0);SI(OU(Tarificateur!F1=amisZ3);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;12;0);SI(OU(Tarificateur!F1=amisZ4);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;17;0);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;22;0)))));SI(Tarificateur!D1="Régime2";SI(OU(Tarificateur!F1=amisZ1);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;2;0);SI(OU(Tarificateur!F1=amisZ2);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;7;0);SI(OU(Tarificateur!F1=amisZ3);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;12;0);SI(OU(Tarificateur!F1=amisZ4);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;17;0);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;22;0)))));SI(OU(Tarificateur!F1=amisZ1);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;2;0);SI(OU(Tarificateur!F1=amisZ2);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;7;0);SI(OU(Tarificateur!F1=amisZ3);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;12;0);SI(OU(Tarificateur!F1=amisZ4);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;17;0);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;22;0)))))))
En gros vous voyez que c'est un "embriquement" pas possible de SI et de RechercheV.
Je me demandais simplement s'il n'existait pas une autre formule qui remplierai ce rôle ?
Pour comprendre ce que fais ma formule je vous le met en texte (simplifié) :
Vérifier la valeur de D1 : Régime1, Régime2 ou autre.
Une fois D1 "identifié", on fait pareil avec F1 (amisZ1,Z2,Z3,Z4 ou autre)
Selon les valeurs de D1 et F1 ma RechercheV ne se fait pas sur la même plage de cellules. (Et je recherche la valeur de B1 dans cette plage, qui est l'age du client)
Croyez vous qu'on puisse faire plus clair/simple avec Excel pour ce genre de "tarificateur" ?
(En gros je dois établir mon tarif selon, le régime de la personne (3 possibles) et son département (tous les départements sont répartis en 5 zones). Chaque régime dispose d'un tarif pour chaque zone, tarif basé sur l'age du client. Voila pourquoi je recherche l'age dans des plages de cellules différents (ex: Régime1, Zone1, age 50 = 85€ ou Régime2, Zone5, age 55 = 76€))
Je bosse actuellement sur un "tarificateur" sous excel, il est presque fini mais j'aimerai voir s'il n'est pas possible d'améliorer mes formules.(En sachant qu'en l'état tout fonctionne bien)
Pour que vous compreniez mieux, je vais coller une formule et vous expliquer :
=SI(Tarificateur!D1="Régime1";SI(OU(Tarificateur1!F1=amisZ1);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;2;0);SI(OU(Tarificateur!F1=amisZ2);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;7;0);SI(OU(Tarificateur!F1=amisZ3);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;12;0);SI(OU(Tarificateur!F1=amisZ4);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;17;0);RECHERCHEV(Tarificateur!B1;NSSS!A1:Z42;22;0)))));SI(Tarificateur!D1="Régime2";SI(OU(Tarificateur!F1=amisZ1);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;2;0);SI(OU(Tarificateur!F1=amisZ2);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;7;0);SI(OU(Tarificateur!F1=amisZ3);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;12;0);SI(OU(Tarificateur!F1=amisZ4);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;17;0);RECHERCHEV(Tarificateur!B1;NSTNS!A1:Z42;22;0)))));SI(OU(Tarificateur!F1=amisZ1);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;2;0);SI(OU(Tarificateur!F1=amisZ2);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;7;0);SI(OU(Tarificateur!F1=amisZ3);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;12;0);SI(OU(Tarificateur!F1=amisZ4);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;17;0);RECHERCHEV(Tarificateur!B1;NSEA!A1:Z42;22;0)))))))
En gros vous voyez que c'est un "embriquement" pas possible de SI et de RechercheV.
Je me demandais simplement s'il n'existait pas une autre formule qui remplierai ce rôle ?
Pour comprendre ce que fais ma formule je vous le met en texte (simplifié) :
Vérifier la valeur de D1 : Régime1, Régime2 ou autre.
Une fois D1 "identifié", on fait pareil avec F1 (amisZ1,Z2,Z3,Z4 ou autre)
Selon les valeurs de D1 et F1 ma RechercheV ne se fait pas sur la même plage de cellules. (Et je recherche la valeur de B1 dans cette plage, qui est l'age du client)
Croyez vous qu'on puisse faire plus clair/simple avec Excel pour ce genre de "tarificateur" ?
(En gros je dois établir mon tarif selon, le régime de la personne (3 possibles) et son département (tous les départements sont répartis en 5 zones). Chaque régime dispose d'un tarif pour chaque zone, tarif basé sur l'age du client. Voila pourquoi je recherche l'age dans des plages de cellules différents (ex: Régime1, Zone1, age 50 = 85€ ou Régime2, Zone5, age 55 = 76€))
A voir également:
- SI + RechercheV - plus simple ?
- Iphone 14 simple - Guide
- Simple pdf - Télécharger - PDF
- Simple ocr - Télécharger - Bureautique
- Simple file locker - Télécharger - Sécurité
- Simple comic - Télécharger - Vie quotidienne
7 réponses
Bonjour
si vous pouvez déposer un modèle ici (même en bidonnant des noms si confidentialité
https://www.cjoint.com/
ce serait plus facile à traiter parce qu'avec votre formule, c'est assez tordu à comprendre
ceci dit, je pense que l'on devrait s'en sortir avec le code INDIRECT, mais il faudrait plus d'info pour être précis
à vous lire
crdlmnt
si vous pouvez déposer un modèle ici (même en bidonnant des noms si confidentialité
https://www.cjoint.com/
ce serait plus facile à traiter parce qu'avec votre formule, c'est assez tordu à comprendre
ceci dit, je pense que l'on devrait s'en sortir avec le code INDIRECT, mais il faudrait plus d'info pour être précis
à vous lire
crdlmnt
Voila :
http://www.cjoint.com/c/GFmqDyp5T2O
C'est une version simplifié avec seulement 2 pages de données, mais ça devrait vous permettre de comprendre.
En page1 (Tarificateur), c'est seulement des formules SOMME, ce qui vous intéresse se trouve en page2 (RO_SS) dans le tableau vous verrez les formules dont je vous parlais.
http://www.cjoint.com/c/GFmqDyp5T2O
C'est une version simplifié avec seulement 2 pages de données, mais ça devrait vous permettre de comprendre.
En page1 (Tarificateur), c'est seulement des formules SOMME, ce qui vous intéresse se trouve en page2 (RO_SS) dans le tableau vous verrez les formules dont je vous parlais.
La partie que je vous ai envoyé est différente de la première formule que j'ai collé, mais la logique est la même.
Page RO_SS, S26 :
=SI(OU(Tarificateur!D1="TNS";Tarificateur!D1="Exp. Agricole");SI(OU(Tarificateur!F1=FMASanteZ1);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;2;0);SI(OU(Tarificateur!F1=FMASanteZ2);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;6;0);SI(OU(Tarificateur!F1=FMASanteZ3);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;10;0);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;14;0))));
/.. Jusqu'ici on allait cherché la valeur sur la page RO_TNS : RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;2;0) Donc je recherche B1 (l'age du client) dans la page RO_TNS, cellule A1 à Q101, colonne 2. (Colonne 2 pour la formule 100 en 2017)
/..Pour la suite on ira chercher sur RO_SS
SI(OU(Tarificateur!F1=FMASanteZ1);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;6;0);SI(OU(Tarificateur!F1=FMASanteZ2);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;10;0);SI(OU(Tarificateur!F1=FMASanteZ3);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;14;0);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;2;0)))))
Page RO_SS, S26 :
=SI(OU(Tarificateur!D1="TNS";Tarificateur!D1="Exp. Agricole");SI(OU(Tarificateur!F1=FMASanteZ1);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;2;0);SI(OU(Tarificateur!F1=FMASanteZ2);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;6;0);SI(OU(Tarificateur!F1=FMASanteZ3);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;10;0);RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;14;0))));
/.. Jusqu'ici on allait cherché la valeur sur la page RO_TNS : RECHERCHEV(Tarificateur!B1;RO_TNS!A1:Q101;2;0) Donc je recherche B1 (l'age du client) dans la page RO_TNS, cellule A1 à Q101, colonne 2. (Colonne 2 pour la formule 100 en 2017)
/..Pour la suite on ira chercher sur RO_SS
SI(OU(Tarificateur!F1=FMASanteZ1);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;6;0);SI(OU(Tarificateur!F1=FMASanteZ2);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;10;0);SI(OU(Tarificateur!F1=FMASanteZ3);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;14;0);RECHERCHEV(Tarificateur!B1;RO_SS!A1:Q101;2;0)))))
Salut kuss.
Tu peux diminuer la longueur de ta formule en définissant les noms
"age" pour la cellule TarificateurB1, "RO" pour la cellule TarificateurD1,
"ami" pour la cellule TarificateurF1,
NSS pour la plage NSSS!A1:Z42, NST pour la plage NSTNS!A1:Z42 et
NSE pour la plage NSEA!A1:Z42.
Ainsi ta formule de 1120 caractères passerait à 645 caractères :
=SI(RO="Régime1";
SI(OU(ami=amisZ1);RECHERCHEV(age;NSS;2;0);
SI(OU(ami=amisZ2);RECHERCHEV(age;NSS;7;0);
SI(OU(ami=amisZ3);RECHERCHEV(age;NSS;12;0);
SI(OU(ami=amisZ4);RECHERCHEV(age;NSS;17;0);RECHERCHEV(age;NSS;22;0)))));
SI(RO="Régime2";
SI(OU(ami=amisZ1);RECHERCHEV(age;NST;2;0);
SI(OU(ami=amisZ2);RECHERCHEV(age;NST;7;0);
SI(OU(ami=amisZ3);RECHERCHEV(age;NST;12;0);
SI(OU(ami=amisZ4);RECHERCHEV(age;NST;17;0);RECHERCHEV(age;NST;22;0)))));
SI(OU(ami=amisZ1);RECHERCHEV(age;NSE;2;0);
SI(OU(ami=amisZ2);RECHERCHEV(age;NSE;7;0);
SI(OU(ami=amisZ3);RECHERCHEV(age;NSE;12;0);
SI(OU(ami=amisZ4);RECHERCHEV(age;NSE;17;0);RECHERCHEV(age;NSE;22;0)))))))
Tu peux diminuer la longueur de ta formule en définissant les noms
"age" pour la cellule TarificateurB1, "RO" pour la cellule TarificateurD1,
"ami" pour la cellule TarificateurF1,
NSS pour la plage NSSS!A1:Z42, NST pour la plage NSTNS!A1:Z42 et
NSE pour la plage NSEA!A1:Z42.
Ainsi ta formule de 1120 caractères passerait à 645 caractères :
=SI(RO="Régime1";
SI(OU(ami=amisZ1);RECHERCHEV(age;NSS;2;0);
SI(OU(ami=amisZ2);RECHERCHEV(age;NSS;7;0);
SI(OU(ami=amisZ3);RECHERCHEV(age;NSS;12;0);
SI(OU(ami=amisZ4);RECHERCHEV(age;NSS;17;0);RECHERCHEV(age;NSS;22;0)))));
SI(RO="Régime2";
SI(OU(ami=amisZ1);RECHERCHEV(age;NST;2;0);
SI(OU(ami=amisZ2);RECHERCHEV(age;NST;7;0);
SI(OU(ami=amisZ3);RECHERCHEV(age;NST;12;0);
SI(OU(ami=amisZ4);RECHERCHEV(age;NST;17;0);RECHERCHEV(age;NST;22;0)))));
SI(OU(ami=amisZ1);RECHERCHEV(age;NSE;2;0);
SI(OU(ami=amisZ2);RECHERCHEV(age;NSE;7;0);
SI(OU(ami=amisZ3);RECHERCHEV(age;NSE;12;0);
SI(OU(ami=amisZ4);RECHERCHEV(age;NSE;17;0);RECHERCHEV(age;NSE;22;0)))))))
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonjour,
Déjà, tu peux remplacer :
SI(OU(Tarificateur!F1=FMASanteZ1);
par
SI(Tarificateur!F1=FMASanteZ1;
ça économise 12 x 4 = 72 caractères supplémentaires
Déjà, tu peux remplacer :
SI(OU(Tarificateur!F1=FMASanteZ1);
par
SI(Tarificateur!F1=FMASanteZ1;
ça économise 12 x 4 = 72 caractères supplémentaires
Oui, Patrice ; cela m'avait échappé.
Mais je propose une toute autre approche :
1) Dans une cellule disponible, on saisit la formule
=SI(ami=amisZ1;2;
SI(ami=amisZ2;7;
SI(ami=amisZ3;12;
SI(ami=amisZ4;17;22))))
et on définit le nom Y pour la cellule.
2) La formule principale se réduit à
=SI(RO="Régime1";RECHERCHEV(age;NSS;Y;0);
SI(RO="Régime2";RECHERCHEV(age;NST;Y;0);
RECHERCHEV(age;NSE;Y;0)))
https://www.cjoint.com/c/GFnbgspEcWm
C'est bien, la retraite ! Surtout aux Antilles ... :-) Raymond (INSA, AFPA, CF/R)
Bonjour tt le monde
Un autre PRINCIPE simplifié en fonction des 3 param^tres : régime, amis, Age ( impossible d'^tre + précis vu la pièce jointe de Kuss)
en utilisant "indirect" comme le préconisait notre ami Vaucluse, RechercheV et équiv
la formule unique proposée à adapter
la maquette
https://mon-partage.fr/f/dq2o8IBA/
le classeur
Un autre PRINCIPE simplifié en fonction des 3 param^tres : régime, amis, Age ( impossible d'^tre + précis vu la pièce jointe de Kuss)
en utilisant "indirect" comme le préconisait notre ami Vaucluse, RechercheV et équiv
la formule unique proposée à adapter
=RECHERCHEV(G2;INDIRECT("'"&I6&"'!B2:F5");EQUIV(I2;N2:N5;0)+1;0)
la maquette
https://mon-partage.fr/f/dq2o8IBA/
le classeur
