Statistiques sur la répartition des effectifs avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Statistiques sur effectif et population

Dans cette mise en pratique, nous apportons une solution simple, qui plus est matricielle pour dresser des statistiques sur des effectifs ou des populations.



Dans l'exemple finalisé illustré par la capture ci-dessus, nous dressons la synthèse sur la répartition des hommes et des femmes dans chaque service. De plus, nous calculons la somme des salaires ainsi répartis. Ces statistiques imposent de recouper plusieurs conditions.

Source et présentation de l'objectif
Pour réaliser ces travaux, il est opportun de travailler à partir d'une base de données des salariés.

• Télécharger le classeur synthese-effectifs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous récupérons un tableau faisant office de base de données des employés d'une entreprise. Chaque salarié est notamment référencé sur son service, son genre et son salaire.



Sur la droite, vous notez la présence du tableau de bord dont la destinée est de livrer ces statistiques dynamiques sur la répartition des salariés, selon plusieurs contraintes à considérer. Il s'agit dans un premier temps de dénombrer les salariés par service et par genre, soit par civilité. En respectant ce découpage, nous devrons ensuite livrer la somme des salaires par service et par genre.

Ensemble de conditions
Avant d'aborder la solution matricielle, nous proposons d'apporter la solution par le biais de formules classiques. Excel offre en effet les fonctions Nb.Si.Ens et Somme.Si.Ens :

=Nb.Si.Ens(Plage1; Critère1; Plage2; Critère2; ...; PlageN; CritèreN)
=Somme.Si.Ens(Plage_somme; Plage1; Critère1; Plage2; Critère2; ...; PlageN; CritèreN)

La première permet de réaliser un dénombrement selon un ensemble de critères à recouper sur des plages de cellules respectives. Ces conditions correspondent au genre et au service à trouver dans leurs colonnes respectives. La seconde est une extension. Selon ces ensembles de critères à honorer sur ces plages respectives, elle réalise la somme des valeurs numériques correspondantes sur une autre plage, mentionnée en premier argument.

Si vous déployez la zone Nom en haut à gauche de la feuille, vous notez que chaque colonne du tableau est reconnue par son titre. Nous allons exploiter ces noms dans les formules pour simplifier la syntaxe. Nous proposons de débuter par le décompte des hommes travaillant dans le service comptabilité. Comme toujours, cette formule doit pouvoir se répliquer pour produire les autres résultats, y compris ceux des femmes.

• Sélectionner la cellule I4,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction de dénombrement multicritère, suivie d'une parenthèse, soit : Nb.Si.Ens(,
• Désigner la plage des services par son nom, soit : Service,
• Taper un point-virgule (;) pour passer dans l'argument du critère à vérifier sur cette plage,
• Désigner le premier service du tableau de bord, soit la cellule H4,
• Puis, enfoncer trois fois de suite la touche F4 du clavier, ce qui donne : $H4,

De cette manière, nous la libérons en ligne et la figeons en colonne. En effet, lorsque nous répliquerons le calcul sur les lignes du dessous, le critère doit évoluer pour interpréter le service associé. Mais lorsque nous répliquerons ce calcul sur la colonne de droite pour les femmes, le critère doit toujours être considéré dans sa colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la nouvelle plage de cellules,
• Désigner la colonne des genres par son nom, soit : Genre,
• Taper un point-virgule (;) pour passer dans l'argument du critère à honorer sur cette plage,
• Désigner le premier genre du tableau de bord en cliquant sur sa cellule I3,
• Enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : I$3,

Ainsi, nous la figeons en ligne et la libérons en colonne. En effet, en répliquant ce calcul sur la colonne de droite, le critère sur le genre doit s'adapter. En revanche, pour les lignes du dessous, la formule doit continuer de considérer ce critère sur cette ligne fixe.

• Fermer la parenthèse de la fonction Nb.Si.Ens,
• Valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette astuce permet de conserver la cellule active pour l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur les autres services,
• Puis, tirer la poignée de la sélection sur la colonne de droite pour dénombrer les femmes,

Nous obtenons la répartition des salariés par service et civilité. Le tableau source recense 18 salariés. Si vous sommez ces résultats répartis, vous atteignez le même score. Grâce aux références mixtes judicieusement exploitées, une seule formule a permis de livrer tous les résultats. La syntaxe du calcul que nous avons construit est la suivante : =NB.SI.ENS(Service; $H4; Genre; I$3).

Les résultats sur les salaires sont triviaux à obtenir. La fonction Somme.Si.Ens demande seulement une plage supplémentaire. Il s'agit de la plage des salaires à additionner. Cette somme doit se faire en tenant compte des mêmes contraintes que le dénombrement précédent. En conséquence, l'enchaînement des plages et critères est strictement identique.

• En cellule K4, construire la formule suivante : =Somme.Si.Ens(Salaire; Service; $H4; Genre; I$3),
• La valider puis la répliquer sur les trois lignes du dessous et sur la colonne adjacente,

Nous obtenons bien la somme des salaires tenant compte de la répartition des salariés par service et civilité. Il est opportun de retenir ces résultats pour les comparer avec ceux que produiront les formules matricielles.

Calcul matriciel de répartition
Nous souhaitons maintenant confirmer ces résultats avec le raisonnement matriciel. En effet, la syntaxe n'est pas plus complexe. De plus et comme vous le savez désormais, les techniques matricielles offrent souvent des solutions que les techniques classiques ne peuvent aboutir. L'intérêt est donc de poursuivre l'entraînement au travers de ces cas pratiques.

La fonction SommeProd dans sa version classique, multiplie les valeurs respectives de plusieurs matrices (des colonnes bien souvent). A l'issue, elle additionne ces valeurs résultantes. L'idée comme nous l'avons déjà démontré, consiste à lui passer des matrices soumises à conditions. A chaque fois que les critères sont recoupés sur les lignes respectives, la formule répond par le chiffre 1 et par le chiffre 0 le cas échéant. Comme ces chiffres sont ensuite naturellement additionnées par la fonction SommeProd, il en résulte le dénombrement pour la répartition des salariés. Les conditions consistent à vérifier la civilité et le service sur les colonnes associées.

• Sélectionner tous les précédents résultats, soit la plage de cellules I4:L7,
• Enfoncer la touche Suppr pour éliminer toutes les formules,
• Puis, sélectionner seulement la cellule I4,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Saisir le nom de la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

Nous affichons ainsi l'assistant pour la fonction SommeProd. Il va nous aider à comprendre le raisonnement matriciel.

• Dans la zone Matrice1, ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Désigner la plage des services par son nom, soit : Service,
• Taper le symbole égal (=) pour annoncer la première condition à satisfaire,
• Désigner le premier service du tableau de bord, soit la cellule H4,
• Comme pour le calcul précédent, enfoncer trois fois la touche F4, ce qui donne : $H4,
• Fermer la parenthèse de cette première matrice conditionnelle,

Aussitôt et comme vous pouvez le voir, des indicateurs booléens apparaissent sur l'extrémité droite de l'assistant. Ils repèrent (Vrai) les positions des enregistrements satisfaisant la condition. Nous devons recouper cette contrainte avec celle sur la civilité.

• Taper le symbole de l'étoile (*) pour annoncer la contrainte à recouper,
• Ouvrir une parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner la plage des civilités par son nom, soit : Genre,
• Taper le symbole égal (=) pour annoncer la condition à satisfaire,
• Sélectionner la première civilité du tableau de bord en cliquant sur sa cellule I3,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : I$3,
• Fermer la parenthèse de cette seconde matrice conditionnelle,

Puisque la syntaxe est considérée comme aboutie, les indicateurs refont surface sur la droite de la boîte de dialogue. Mais grâce au recoupement des conditions, les valeurs booléennes ont été transformées en chiffres. Le chiffre 1 repère un enregistrement satisfaisant les deux contraintes. Le chiffre 0 repère un enregistrement non concordant. Ces chiffres vont être additionnés par la fonction SommeProd. Il va en résulter le dénombrement de la répartition des salariés.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider la formule matricielle,
• Double cliquer sur la poignée du résultat pour répliquer la logique sur les autres services,
• Tirer la poignée de la sélection sur la colonne de droite pour dénombrer les femmes,

Comme vous pouvez le voir, les résultats sont strictement identiques à ceux fournis par la fonction Nb.Si.Ens. De plus, la syntaxe du calcul n'est pas plus complexe :

=SOMMEPROD((Service=$H4)*(Genre=I$3))

Et comme vous l'avez noté, nous n'avons pas eu besoin de valider cette formule par le fameux raccourci clavier. En effet, la fonction SommeProd est une fonction matricielle. En conséquence, elle raisonne naturellement sur des matrices, ligne à ligne.

Le calcul restant se déduit naturellement. Il suffit de multiplier les matrices conditionnelles par celle des salaires. Chaque chiffre 1, représentant un enregistrement concordant, sera multiplié par le salaire de la même ligne. A l'issue, la fonction SommProd les additionnera.

• En cellule K4, construire la formule matricielle suivante :

=SOMMEPROD((Service = $H4)*(Genre = I$3)*Salaire)

• La répliquer sur la ligne du dessous et la colonne de droite,

Comme vous pouvez le voir, avec un raisonnement différent, nous aboutissons strictement aux mêmes résultats.