Category Archives: Thoughts

try to exhibit thoughts

Excel : Trouver le MAX() ou le MIN() tenant en compte une condition SI()

Alors que SOMME(), NB(), MOYENNE() ont leur version conditionnelle unique, [SOMME.SI(), NB.SI(), MOYENNE.SI()], ainsi que leur version conditionnelle multiple, [SOMME.SI.ENS(), NB.SI.ENS(), MOYENNE.SI.ENS()], il n’est plutôt pas evident pour MAX() et MIN().

Pour corriger cette lacune, nous allons inserer SI() dans MAX ou MIN. Toutes les opérations s’éffectueront dans SI(). Ce sera alors une formule matricielle (array formula). Une formule matricielle en Excel est celle qui necessite pour sa validation, la combinaison CTRL+SHIFT+ENTER.

ILLUSTRATION

  • Dans la colonne A: (A5:A20) se trouve les dates auxquelles les clients sont venus acheter
  • Dans la colonne B: (B5:B20) se trouve les noms des clients, qui peuvent se répéter si les clients sont revenus à plusieures reprises
  • Dans la colonne C: (C5:C20) se trouve les valeurs d’achats

    Nous voulons savoir pour chaque client la plus grande valeur et la plus petite valeur de la série des achats de cette période.

    À titre d’exemple, nous allons considérer un seul client qui sera placé dans E5, et alors, max et min seront placés successivement dans F5 et G5.

    SI sera une formule matricielle.

    • Condition: B5:B20=E5
    • La plage d’opération: C5:C20
    • Ainsi: SI(B5:B20=E5,C5:C20)

    La même formule matricielle sera utilisée et pour MAX() et pour MIN()

    Ainsi dans:

    • F5: =MAX(SI(B5:B20=E5;C5:C20))
    • G5: =MIN(SI(B5:B20=E5;C5:C20))

    N’oubliez pas que pour confirmer, il faut la combinaison CTRL+SHIFT+ENTER

    Excel: nb.si.ens() ~ countifs()

    Comme toutes les autres fonctions les plus utilisées, ajouter un .si ou if [comme dans somme.si(), moyenne.si(), nb.si(), …] lui permet de faire une operation à une condition, mais ajouter un .si.ens ou ifs [comme dans somme.si.ens(), moyenne.si.ens(), nb.si.ens(), …] lui permet de faire une opération à multiples conditions.

    Sa forme est:

    =NB.SI.ENS(vecteur_test_1;valeur_test_1[;vecteur_test_2;valeur_test_2; …;vecteur_test_n;valeur_test_n])

    Cela signifie que chaque vecteur a sa valeur contre laquelle on va tester.

    • Un vecteur est une plage des données. Ex. D12:D21;
    • Une valeur peut être un texte, un nombre, une date,… ou une référence cellulaire. Pour comparer on utilise les opérateurs de comparaison (= égal, > supérieur, >= supérieur ou égal, < inférieur, <= inférieur ou égal, <> inégale ou différent);
    • = égal peut être omis.
    • Les autres opérateurs doivent être placés entre “”. Ex. “<12”, “>=8”;
    • S’il s’agit d’une combinaison entre operateur et référence cellulaire, l’opérateur sera toujours placé entre “” suivi de & et la cellule. Ex. “<=” & B5, “<>” & D34;
    • L’opérateur de concaténation ‘&’ sert à relier les opérants.

    Notre cas nous impose 3 conditions

    • Seul de sexe masculin : “M” ou “=M”;
    • Âgé de moins de 25 ans : “<25”;
    • De niveau d’étude inférieur à 14 : “<14”;

    Là sont les 3 valeur_test à utilisé. Ainsi nous aurons pour chaque cas:

    1. Sexe sur colonne D: Vecteur_test_1;valeur_test_1 : D12:D21;”=M”
    2. Âgé sur colonne C: vecteur_test_2;valeur_test_2 : C12:C21;”<25″
    3. Niveau d’étude sur colonne E : vecteur_test_3;valeur_test_3 : E12:E21;”<14″

    En somme:

    =NB.SI.ENS(D12:D21;”=M”;C12:C21;”<25″;E12:E21;”<14″)

    En anglais:

    =COUNTIFS(D12:D21,”=M”,C12:C21,”<25″,E12:E21,”<14″)

    EXCEL: MOYENNE.SI() ~ AVERAGEIF()

    Problématique

    On veut calculer la moyenne de certains éléments du tableau par rapport à des caractéristiques bien définies.

    Pour cela il faut utiliser la fonction MOYENNE.SI(). ou AVERAGEIF() en Anglais.

    Utilisons moyenne.si() à trois paramètres:

    • Param1: le vecteur dans lequel on va comparer. Ici prenons la colonne de ID comme elle est spécifique à chaque travailleur. Ainsi on a : $B$8:$B$13;
    • Param2: la valeur à comparer. Ainsi on a B18. Cette valeur va rester relative;
    • Param3: le vecteur où il y a des valeurs à utiliser dans le calcul de la moyenne. Ainsi on a: $D$8:$D$13



      EN RÉSUMÉ

      Dans la cellule D18 la formule sera : 

      • En français: =MOYENNE.SI($B$8:$B$13;B18;$D$8:$D$13)
      • En anglais: =AVERAGEIF($B$8:$B13,B18,$D$8:$D$13)

      La formule de la cellule D18 sera copiée dans les cellules D19 et D20.

      Le total dans D14 est une simple sommation SOMME() ~ SUM().

      ATTENTION:

      MOYENNE.SI() n’est disponible qu’à partir de Excel 2007. Pour les versions antérieures il existe des alternatifs:

      • SOMME.SI()/NB.SI(): somme conditionnelle divisée par son compte conditionnel. La fonction SOMME.SI() a déjà été abordée.
      • MOYENNE(SI()): Le tout se passe dans la fonction si().  =MOYENNE(SI($B$8:$B13=B18,$D$8:$D$13)). Pour confirmer il faut appuyer sur CONTROL+SHIFT+ENTER. Si non ça ne va pas marcher.

      EXCEL: SOMME.SI() ~ SUMIF()

      Problématique

      On veut additionner certains éléments du tableau par rapport à des caractéristiques bien définies.

      Pour cela il faut utiliser la fonction SOMME.SI(). ou SUMIF() en Anglais.

      Utilisons somme.si() à trois paramètres:

      • Param1: le vecteur dans lequel on va comparer. Ici prenons la colonne de ID comme elle est spécifique à chaque travailleur. Ainsi on a : $B$8:$B$13;
      • Param2: la valeur à comparer. Ainsi on a B18. Cette valeur va rester relative;
      • Param3: le vecteur où il y a des valeurs à additionner. Ainsi on a: $D$8:$D$13

        EN RÉSUMÉ

        Dans la cellule D18 la formule sera : 

        • En français: =SOMME.SI($B$8:$B$13;B18;$D$8:$D$13)
        • En anglais: =SUMIF($B$8:$B13,B18,$D$8:$D$13)

        La formule de la cellule D18 sera copiée dans les cellules D19 et D20.

        Le total dans D14 et D21 est une simple sommation SOMME() ~ SUM().

        Excel : si() v. recherche() v. index(equiv())

        Problematique:

        Dans cette entreprise, les travailleurs sont classés en catégorie qui influe aussi sur le salaire horaire (s.h.) et la prime.

        Alors, étant agent payeur au service de RH, vous devez préparer un fichier de paie qui sera utilisé comme modèle.

        Ici le PS (Personnel de Saisie) ne doit entrer que le nom des travailleurs, leur catégorie et le nombre d’heures prestées. Le resté doit se calculer de soi.

        C’est pourquoi il nous faut une formule dans D9 et F9 qui:

        • Compare la valeur de categorie se trouvant dans B9 avec celles se trouvant dans la plage A3 à A6.
        • Une fois la correspondance trouvée, la formule récupère la valeur de s.h. ou de prime de la ligne de correspondance et l’insert dans D9 ou F9 selon les cas.
        • Comme cette formule sera utilisé pour copier aux autres, les références fixes seront rendues absolues.

        Nous avons trois manières de résoudre cette situation. On ne se limite pas seulement à trois avec un peu de genie!

        1. Fonction si()
        2. On passe en revue chaque condition de catégorie:

          • Si(B9=$A$3;$B$3;autre condition)
          • Si(B9=$A$4;$B$4;autre condition)
          • Si(B9=$A$5;$B$5;$B$6)

          En somme:

          =si(B9=$A$3;$B$3;si(B9=$A$4;$B$4;si(B9=$A$5;$B$5;$B$6)))

          Constatez qu’on n’a pas voulu verifier la condition dans A6 car on la considère comme condition par défaut.

        3. Fonction recherche():
        4. La fonction recherche () a deux variations, variation à 2 paramètres et variation à 3 paramètres.

          • Recherche(valeur à chercher;vecteur où on doit chercher et où on soutire la valeur): valeur à chercher B9; vecteur $A$3:$B$6 pour s.h. et $A$3:$C$6 pour prime. Ainsi on aura pour s.h.: =recherche(B9;$A$3:$B$6)
          • Recherche(valeur à chercher;vecteur où on doit chercher;vecteur où on doit soutirer la valeur). Valeur à chercher B9; vecteur où chercher $A$3:$A$6; vecteur où on soutire $B$3:$B$6 pour s.h. et $C$3:$C$6 pour prime. Ainsi on aura pour s.h.: =recherche(B9;$A$3:$A$6;$B$3:$B$6)
        5. Combinaison index(equiv())
          • On commence par comprendre la fonction equiv(). Equiv(valeur à chercher; vecteur où chercher). Cette fonction retourne le numéro de la première ligne où la correspondance est trouvée. Ce numéro n’est pas par rapport à la feuille entière mais tout simplement par rapport au vecteur considéré. Ainsi on aura: equiv(B9;$A$3:$A$6)
          • Index() a trois paramètres: vecteur donné, pour s.h. $A$3:$B$6 et pour prime $A$3:$C$6; le numéro de la ligne concernée qui nous sera donné par equiv(); le numéro de la colonne où on soutire la valeur par rapport au vecteur: 2 pour s.h. et 3 pour prime.
          • Alors la formule complète pour s.h. sera : =index($A$3:$B$6;equiv(B9;$A$3:$A$6);2)