SI + RechercheV - plus simple ? Fermé

Question

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€))



Configuration: Windows / Chrome 59.0.3071.86

Vaucluse · Answer

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

kuss · Answer

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.

kuss · Answer

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)))))

Raymond PENTIER · Answer

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)))))))

Patrice33740 · Answer

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

Raymond PENTIER · Answer

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)

Kuss · Answer

Super , merci à vous. Je vais peaufiner tout ça.

SI + RechercheV - plus simple ?

7 réponses

Discussions similaires