Pages

13 juillet 2009

Compacité/Lisibilité d’une formule

Quand on cherche à construire une formule un tant soit peu complexe avec Excel, on a fréquemment le choix entre une formule plus compacte – mais moins facile à comprendre – et une formule plus facilement compréhensible – mais plus lourde.

Dans l’exemple que nous étudions aujourd’hui, nous devons calculer la rémunération de personnes dont le tarif horaire est indiqué en colonne A et le nombre d’heures de travail dans la journée dans la colonne B.

La rémunération de toute heure au-delà de 8 heures est majorée de 50%, et celle de toute heure au-delà de 12 heures est doublée.


La formule en C2 est :
=si(B2>12;(8*A2)+(4*1,5*A2)+((B2-12)*A2*2);si(B2>8;(8*A2)+((B2-8)*A2*1,5);A2*B2))

La formule en D2 est :
=A2*(B2+0,5*(max(0;B2-8)+max(0;B2-12)))

La formule en D2 est certes deux fois plus compacte que la formule en C2 mais – pour beaucoup de gens – elle sera nettement moins compréhensible que la formule en C2.

Remarque – Bien entendu, nous déconseillons formellement l’utilisation – comme c’est le cas dans cet exemple – de constantes cachées (les valeurs 1 ; 1,5 ; 2 ; 8 ; 12). Nous ne l’avons fait ici que pour simplifier la présentation du cas et les formules. Normalement, nous aurions mis tous ces paramètres dans ces cellules de la feuille : ainsi, les formules resteraient « bonnes » même si l’on devait un jour modifier ces valeurs.

5 commentaires:

  1. Aigle346:27 PM

    Je pense que la bonne formule devrait être :
    "=A2*(B2+0,5*MAX(0;B2-8)+0,5*MAX(0;B2-12))"

    ...sauf erreur de ma part

    bonnes vacances à tous, Hervé
    ;-)

    RépondreSupprimer
  2. Aigle348:25 AM

    Evidemment ! La factorisation était évidente. Il me manquait juste une parenthèse.

    J'ai vraiment besoin de vacances...

    RépondreSupprimer
  3. cduigou6:53 PM

    Bonsoir,
    Le problème tient surtout au fait que la complexité de la formule dépend du nombre de tranches. Imaginons ce qu'elle deviendrait pour 4, 5, 6 tranches etc..
    Dans ce genre de pb je pense qu'il est préférable de travailler avec une table et des RECHERCHEV en mode approché : la formule ne change pas avec le nombre de tranches, seule la table doit être mise à jour.
    Exemple, je construis la table "tt" (table des tranches !) suivante :
    Lim HP Coeff
    0 0 1
    8 8 1,5
    12 14 2
    1ère colonne : les limites de tranches
    2ème colonne : les heures pondérées des tranches précédentes
    3ème colonne : les coeff de majorations

    La formule du 1er cout total s'écrit alors :
    =A2*(RECHERCHEV(B2;tt;2)+RECHERCHEV(B2;tt;3)*(B2-RECHERCHEV(B2;tt;1)))

    La formule restera fixe quel que soit la taille de la table tt...

    RépondreSupprimer
  4. Certes, mais là n'était pas mon propos :)

    Je voulais juste montrer la comparaison des deux formules avec - pour la rendre plus claire - aucun élément extérieur (comme une table).

    Ma remarque illustrait au passage le fait qu'il y avait à redire pour améliorer cette formule.

    RépondreSupprimer