1.2 Compter et additionner selon des critères : NB.SI.ENS, SOMME.SI.ENS, SI

NB.SI compte les cellules d'une plage qui satisfont un critère : NB.SI(plage; critères). NB.SI.ENS fait la même chose avec plusieurs conditions, chacune portant sur sa propre plage. Sa syntaxe, telle que documentée par l'éditeur :

• Les conditions s'additionnent comme un ET : une ligne n'est comptée que si toutes ses cellules respectent leur critère.
• Jusqu'à 127 paires plage / critère sont admises.
• Chaque plage doit avoir le même nombre de lignes et de colonnes que la première. Avec des colonnes de tableau (tAbsences[Service], tAbsences[Type]), c'est garanti.
• NB.SI.ENS fonctionne aussi avec une seule paire : on peut l'utiliser partout à la place de NB.SI et garder une seule syntaxe en tête.

Avec l'exemple ci-dessus, la formule renvoie le nombre de lignes du tableau des absences qui concernent à la fois le service Montage et le type Maladie. Si la liste des types a été verrouillée par une liste déroulante (chapitre 1.1), le comptage est fiable : aucune variante « maladie » / « Mal. » / « arrêt » ne lui échappe.

Le critère est l'argument qui pose le plus de questions. Il peut être un nombre, une expression, une référence de cellule ou du texte. Les règles d'écriture sont toujours les mêmes :

Pour chercher un vrai point d'interrogation ou un vrai astérisque, on le fait précéder d'un tilde (~). Et un piège à connaître : selon la documentation, si un critère fait référence à une cellule vide, NB.SI.ENS traite cette cellule comme la valeur 0. Un tableau de bord dont la cellule « service » n'est pas encore remplie affiche donc un résultat trompeur plutôt qu'une erreur.

La bonne pratique consiste à placer les critères variables dans des cellules (le service, le mois étudié, le seuil) et à les référencer en absolu. Un même tableau de bord sert alors tous les services : on change la cellule, pas la formule.

NB.SI.ENS compte des lignes ; SOMME.SI.ENS additionne une colonne de nombres pour les seules lignes qui respectent les conditions. C'est la fonction des totaux par service, par équipe, par mois.

Exemple : total des jours ouvrés d'absence du service dont le nom est en H1, pour le mois dont le premier jour est en I1 :

FIN.MOIS($I$1;0) renvoie le dernier jour du mois de la date en I1 (la fonction est détaillée au chapitre 2.1). La même colonne Début sert deux fois, avec deux bornes : c'est la façon d'exprimer « entre deux dates ».

Limite à connaître : une absence à cheval sur deux mois est comptée entière dans le mois de son début. Pour une ventilation exacte jour par jour, on découpe l'absence à la saisie (une ligne par mois) ou on calcule les jours ouvrés de la période avec les fonctions du chapitre 2.1.

Les références mixtes du chapitre 1.1 prennent ici tout leur sens. On pose les services en colonne A, les types d'absence en ligne 1, et on écrit une seule formule en B2, recopiée vers la droite et vers le bas :

$A2 fige la colonne A (le service) mais laisse la ligne varier ; B$1 fige la ligne 1 (le type) mais laisse la colonne varier. Recopiée en D4, la formule devient =NB.SI.ENS(tAbsences[Service];$A4;tAbsences[Type];D$1) : exactement ce qu'il faut.

Ce tableau se construit aussi, sans formule, avec un tableau croisé dynamique (chapitre 2.1). La version « formules » garde un intérêt : elle se met à jour sans actualisation, et sa mise en page reste stable pour un document diffusé chaque mois.

Un tableau de bord ne doit pas obliger le lecteur à comparer chaque valeur à un seuil. La fonction SI le fait pour lui :

Ici, la formule affiche une alerte quand moins de deux personnes sont affectées au poste de soudure à la date de la ligne. Le seuil de 2 est un choix d'organisation de l'atelier, pas une règle : il gagne à être placé dans une cellule de paramètres.

La documentation de l'éditeur admet l'imbrication de plusieurs SI mais la déconseille au-delà de quelques niveaux : la formule devient illisible et fragile. Pour attribuer une catégorie selon des tranches (par exemple un niveau d'alerte selon un nombre de jours), une petite table de correspondance et une fonction de recherche (chapitre 1.3) sont plus sûres.

SIERREUR : un filet, pas une correction

SIERREUR(valeur; valeur_si_erreur) renvoie le résultat de la formule, ou une valeur de remplacement si elle produit une erreur : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM? ou #NUL!.

C'est utile pour une division dont le dénominateur peut être nul en début de mois. Mais l'éditeur le rappelle : SIERREUR masque toutes les erreurs, y compris celles qui signalent une vraie faute (une colonne supprimée qui donne #REF!, un nom de fonction mal tapé qui donne #NOM?). On ne l'ajoute qu'une fois la formule vérifiée, jamais pour « faire disparaître » un message gênant.

Les exemples suivants supposent un tableau des absences tAbsences (Matricule, Service, Type, Début, Fin, Jours ouvrés) et un tableau des affectations tAffect (Date, Équipe, Poste, Matricule). Les cellules de paramètres sont sur la feuille d'analyse.

Le calcul d'un taux d'absentéisme (heures d'absence rapportées aux heures théoriques) se fait avec ces mêmes briques ; plutôt que de redéfinir une formule ici, l'outil de calcul du taux d'absentéisme du site détaille la méthode et fait le calcul pour un mois donné.