SI + RechercheV - plus simple ?

Kuss -  
michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   -
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€))

7 réponses

  1.
    Vaucluse Messages postés 27336 Date d'inscription   Statut Contributeur Dernière intervention   6 453
     
    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
    0
  2. kuss
     
    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.
    0
    1. Vaucluse Messages postés 27336 Date d'inscription   Statut Contributeur Dernière intervention   6 453
       
      .???. ben non, excusez moi, mais je n'arrive à coller un lien entre votre premier message et le tableau d'une part, et d'autre part je ne trouve pas e formule qui fasse référence à un tableau plutôt qu'à un autre dans votre modèle, elle ne s'adresse qu'à B1:B4 et a la feuille RO-SS.
      0
  3. kuss
     
    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)))))
    0
  4. Raymond PENTIER Messages postés 58211 Date d'inscription   Statut Contributeur Dernière intervention   17 480
     
    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)))))))
    0

  5. Vous n’avez pas trouvé la réponse que vous recherchez ?

    Posez votre question
  6. Patrice33740 Messages postés 8400 Date d'inscription   Statut Membre Dernière intervention   1 783
     
    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
    0
  7. Raymond PENTIER Messages postés 58211 Date d'inscription   Statut Contributeur Dernière intervention   17 480
     
    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)
    0
  8. Kuss
     
    Super , merci à vous. Je vais peaufiner tout ça.
    0
    1. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
       
      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 
      =RECHERCHEV(G2;INDIRECT("'"&I6&"'!B2:F5");EQUIV(I2;N2:N5;0)+1;0)

      la maquette
      https://mon-partage.fr/f/dq2o8IBA/

      le classeur
      0

Discussions similaires

RECHERCHEV additionne les plusieurs lignes du meme nom
pfl.bourde -
pfl.bourde -

2 réponses
[Excel] Recherche de Texte avec RechercheV
Mafia06 -
P-A -

6 réponses