Les statistiques conditionnelles (II)

Limites des fonctions .SI()

Dans un précédent article, nous avions traité des fonctions statistiques conditionnelles NB.SI() et SOMME.SI(). Ces fonctions ont une limite assez gênante : elles n’admettent qu’une seule condition. Pour reprendre l’exemple de totalisation des scores, on totalise les scores de la colonne score si la colonne équipe contient telle couleur. Dans de nombreux cas, il y a plusieurs conditions à la totalisation ou au décompte.

Depuis sa version 2007, Excel propose de nouvelles fonctions, les fonctions SOMME.SI.ENS() et NB.SI.ENS() qui peuvent prendre en compte plusieurs conditions.

Syntaxes

Examinons le cas ci-contre d’un tableau d’effectif d’une entreprise. Ce tableau, mis sous forme de tableau est nommé Personnes. Le nom Personnes[Fonction], pour prendre un exemple, désigne la colonne Fonction du tableau

On souhaite obtenir un décompte pour une fonction et une catégorie données. On créé le tableau ci-dessous comportant une case de choix de la fonction et une case de choix de la catégorie.

Les paramètres de la fonction, en nombre pair, sont une suite de couples. Le premier terme de chaque couple est une colonne, le second une valeur (ou une expression de comparaison). La formule se lit : Compter les lignes du tableau Personnes telles que la colonne Fonction prend la valeur contenue dans la cellule A2 et la colonne Catégorie prend la valeur contenue dans la cellule B2.

La fonction SOMME.SI.ENS() prend un paramètre supplémentaire placé en première position qui est le nom de colonne totalisée, ici Salaire. Le nombre de paramètres est impair.

Remarque : En utilisant pas les tableaux mis sous forme de tableau une syntaxe du type =NB.SI.ENS(Personnes!E:E;A2;Personnes!G:G;B2) aurait fourni le même résultat.

Voyons un exemple d’application.

Créer des colonnes de synthèse

Dans le tableau présenté ci-dessous, on note les versements effectués lors de l’inscription aux activités d’une association sportive. Il est possible de  répartir les règlements dans le temps. L’association collecte des chèques qui ne sont déposés qu’à la date convenue avec l’adhérent. Les chèques sont regroupés par date et déposés périodiquement à date de dépôt révolue. Les chèques déposés le même jour ont le même N°. Un chèque dont le N° est vide n’a pas encore été déposé.

Dans le tableau principal où sont enregistré les adhérents, il est intéressant de connaître la situation de chaque adhérent : quels sont les montants dus, versés et effectivement encaissés ?

Conformément à la règle de tenue de la colonne N°, le montant de la colonne A déposer se calcule en totalisant les règlements ayant un N° vide pour des règlements différents de espèce.

Remarquez la notation de la condition « différent de ». Si, dans notre premier exemple, nous avions voulu indiquer différent du contenu de A2, nous aurions écrit « <> »&A2.

Remarquez aussi l’utilisation de la chaîne vide «  » pour préciser la condition « absence de contenu ».

Gestions multi-tables et fonctions

La règle de constitution des tableaux de données est de regrouper dans des tables différentes les données appartenant à des objets différents. Dans notre exemple, nous avons deux tableaux : le tableau des adhérents et le tableau des règlements.

Cette séparation des informations est absolument essentielle mais elle nécessite de maîtriser 2 types de fonctions pour fonctionner.

Du côté du tableau des Règlements, où nous avons plusieurs règlements pour un adhérent, on peut avoir besoin d’aller chercher des informations dans le tableau Adhérents et on utilisera la fonction RECHERCHEV().

Du côté du tableau Adhérents, on a besoin d’informations synthétiques et on utilisera les fonctions .SI.ENS() pour « totaliser » des informations stockées sur plusieurs lignes dans les tableaux liés comme le tableau des Règlements.

Je vous laisse y réfléchir …