NB.SI, NB.SI.ENS, FREQUENCE ou tableau croisé dynamique : quelle méthode choisir pour un tableau d’effectif Excel ?

Pour créer un tableau d’effectif dans Excel, il faut d’abord savoir ce que l’on veut compter : une valeur précise, une catégorie, une tranche numérique ou une combinaison de critères. Une fois cette logique posée, Excel propose plusieurs solutions fiables : NB.SI pour un critère simple, NB.SI.ENS pour plusieurs critères, FREQUENCE pour des classes numériques, ou encore le tableau croisé dynamique pour synthétiser rapidement une base de données.

Comprendre l’effectif avant d’écrire la formule

En statistique, l’effectif correspond au nombre d’individus, de lignes ou d’observations qui possèdent une même modalité. Dans un tableau Excel, cela revient souvent à compter combien de cellules contiennent une valeur donnée. Par exemple, dans une liste de 18 salariés, on peut vouloir connaître l’effectif du service commercial, le nombre de femmes, ou la répartition des salariés par service et par civilité.

Il faut distinguer l’effectif d’une modalité et l’effectif total. Si une colonne contient les services d’une entreprise, l’effectif du service “Comptabilité” désigne uniquement le nombre de lignes associées à cette valeur. L’effectif total, lui, correspond au nombre global d’observations prises en compte dans la série statistique. Dans Excel, cet effectif total peut être obtenu avec une fonction de comptage ou par somme des effectifs détaillés.

La structure du tableau compte autant que la formule

Un calcul d’effectif fiable commence par une base propre : une ligne par observation, des colonnes clairement nommées, aucune cellule fusionnée dans la zone de données, et des valeurs homogènes. “RH”, “R.H.” et “Ressources humaines” seront interprétés comme trois modalités différentes. Avant de construire un tableau d’effectifs, mieux vaut donc harmoniser les libellés, supprimer les espaces inutiles et vérifier les cellules vides.

Imaginez vos données comme une surface que l’on prépare avant de poser un objet fragile : si elle est froissée, décalée ou irrégulière, le résultat devient moins lisible. Dans Excel, une base mal alignée produit le même effet. Une cellule vide au milieu d’une colonne, un libellé écrit de deux façons ou une plage sélectionnée trop courte peuvent fausser toute la lecture. Avant de chercher une formule plus complexe, prenez un moment pour uniformiser les données : trier, filtrer, corriger les variantes et contrôler les cellules vides.

Choisir la bonne méthode selon le type d’effectif

Excel ne propose pas une seule façon de compter, car tous les besoins ne se ressemblent pas. Le bon choix dépend du niveau de détail attendu : un critère, plusieurs critères, des tranches de valeurs ou une synthèse interactive. Cette distinction évite de forcer une formule là où un tableau croisé dynamique ferait le travail plus vite.

NB.SI : le réflexe pour un critère simple

La fonction NB.SI compte le nombre de cellules d’une plage qui répondent à un critère. Sa syntaxe est simple : =NB.SI(plage;critère). Si les services sont listés en colonne B, de B2 à B19, et que vous voulez compter les lignes contenant “Commercial”, la formule peut être : =NB.SI(B2:B19;"Commercial").

Pour rendre le tableau d’effectif plus pratique, placez les modalités à compter dans une colonne séparée. Par exemple, écrivez “Commercial”, “RH” et “Comptabilité” en D2:D4, puis utilisez en E2 : =NB.SI($B$2:$B$19;D2). Le signe dollar fige la plage de données. Vous pouvez alors recopier la formule vers le bas sans que la plage B2:B19 se décale. Cette méthode fonctionne bien quand la liste des catégories est courte et stable.

NB.SI.ENS : quand l’effectif dépend de plusieurs critères

NB.SI.ENS sert au dénombrement multicritère. Sa logique est simple : compter uniquement les lignes qui valident toutes les conditions indiquées. La syntaxe suit le modèle : =NB.SI.ENS(plage_critère1;critère1;plage_critère2;critère2).

Supposons une plage des services en B2:B19 et une plage des civilités en C2:C19. Pour compter les femmes du service “Commercial”, vous pouvez écrire : =NB.SI.ENS($B$2:$B$19;"Commercial";$C$2:$C$19;"F"). Dans une grille de répartition, par exemple de I4:L7, les références mixtes deviennent utiles : vous pouvez figer la colonne des services et la ligne des civilités afin de recopier la formule dans tout le tableau sans la réécrire. C’est la solution la plus confortable dès qu’un tableau croise deux axes de lecture.

Construire un tableau d’effectif réplicable

Un bon tableau d’effectif Excel ne se contente pas de donner un résultat ponctuel. Il doit pouvoir être recopié, mis à jour et relu facilement. C’est ici que les références absolues et mixtes font vraiment gagner du temps, surtout quand le tableau s’agrandit ou que les données changent.

Utiliser les références absolues et mixtes sans se tromper

Une référence relative comme B2 change quand vous recopiez une formule. Une référence absolue comme $B$2:$B$19 reste fixe. Une référence mixte, comme $H4 ou I$3, ne bloque qu’une partie de l’adresse : la colonne ou la ligne. Cette nuance est très utile dans un tableau de répartition.

Imaginez une matrice où les services sont listés en H4:H7 et les civilités en I3:L3. Dans la cellule I4, une formule de type =NB.SI.ENS($B$2:$B$19;$H4;$C$2:$C$19;I$3) permet de compter l’intersection entre le service de la ligne et la civilité de la colonne. En recopiant vers la droite et vers le bas, Excel adapte uniquement ce qui doit changer. Vous obtenez ainsi un tableau lisible, sans refaire chaque calcul à la main.

Ajouter un effectif total et contrôler les résultats

Après avoir calculé les effectifs par catégorie, ajoutez une ligne ou une colonne de total. La fonction SOMME suffit généralement à additionner les effectifs détaillés. Si votre base contient 18 salariés, la somme des catégories principales doit normalement donner 18, sauf si certaines lignes sont vides ou volontairement exclues.

Ce contrôle est essentiel. Si le total obtenu est 17 ou 19 alors que la population attendue est de 18 salariés, il faut vérifier les doublons, les cellules vides, les fautes de saisie ou les critères mal orthographiés. Un tableau d’effectifs n’est pas seulement un calcul : c’est aussi un outil de validation de la qualité des données. Il permet de repérer rapidement une anomalie avant qu’elle ne se propage dans tout le fichier.

Utiliser FREQUENCE ou un tableau croisé dynamique

Lorsque les données sont numériques ou lorsque vous voulez explorer rapidement une grande base, les fonctions de comptage classiques ne sont pas toujours les plus confortables. Deux alternatives se distinguent : FREQUENCE et le tableau croisé dynamique. Le premier sert à découper une série en classes, le second à résumer la base sans écrire de formule.

FREQUENCE pour répartir des valeurs par classes

La fonction FREQUENCE permet d’obtenir des statistiques de répartition. Elle est particulièrement utile pour compter des valeurs dans des intervalles : âges, notes, montants, ancienneté, scores ou salaires. Au lieu de compter une modalité textuelle, elle calcule combien de valeurs appartiennent à chaque classe.

Par exemple, si des notes sont en A2:A145 et que les bornes de classes sont en C2:C5, la fonction peut produire le nombre de notes inférieures ou égales à chaque borne. Selon la version d’Excel, la validation peut se faire comme une formule dynamique ou comme une formule matricielle. L’intérêt est de transformer une longue série numérique en distribution lisible, avec des classes clairement séparées.

Le tableau croisé dynamique pour synthétiser sans formule

Le tableau croisé dynamique est souvent la méthode la plus rapide pour obtenir une répartition. Il permet de calculer, synthétiser et analyser des données en quelques manipulations. Sélectionnez votre base, insérez un tableau croisé dynamique, placez la variable à étudier dans les lignes, puis placez cette même variable dans les valeurs en choisissant un comptage.

Pour une répartition des salariés par service, placez “Service” dans les lignes et “Service” dans les valeurs. Pour croiser service et civilité, ajoutez “Civilité” dans les colonnes. Vous obtenez alors une matrice de dénombrement sans écrire NB.SI.ENS. Cette approche est idéale quand vous devez explorer les données, filtrer, réorganiser les axes ou construire un tableau de bord dynamique. Elle est aussi pratique si la base évolue souvent.

Cas pratique : compter une répartition de salariés

Prenons une base simple de 18 salariés avec trois colonnes : Nom, Service, Civilité. L’objectif est de créer un tableau d’effectif par service, puis une répartition croisée par service et civilité. Ce cas illustre bien la différence entre un comptage simple et un comptage multicritère.

1. Nettoyez les libellés de service pour éviter les variantes d’écriture.
2. Listez les services uniques dans une zone de résultat.
3. À côté de chaque service, utilisez =NB.SI($B$2:$B$19;D2) pour obtenir l’effectif simple.
4. Créez ensuite une grille avec les services en lignes et les civilités en colonnes.
5. Dans la première cellule de la grille, utilisez =NB.SI.ENS($B$2:$B$19;$H4;$C$2:$C$19;I$3), puis recopiez.
6. Ajoutez les totaux et vérifiez que la somme finale correspond bien aux 18 salariés.

Si vous devez seulement présenter un résultat stable, les formules sont parfaites. Si vous devez analyser la base sous plusieurs angles, préférez le tableau croisé dynamique. Et si votre objectif est une répartition par tranches numériques, utilisez FREQUENCE. Le bon tableau d’effectif Excel est donc celui qui répond au niveau de détail nécessaire, sans rendre le fichier plus complexe qu’il ne doit l’être. Dans tous les cas, la clé reste la même : une base propre, une méthode adaptée et un contrôle final des totaux.