C’est un commentaire récent sur l’un de mes billets de blog qui a inspiré cet article. Si beaucoup d’utilisateurs d’Excel ou de Google Sheets manipulent les formules de type « NB.SI » ou encore « SOMME.SI », très peu savent qu’il est également possible de réaliser des médianes ou des écarts-types conditionnels.
Voyons comment professionnaliser un peu votre pratique des tableurs avec un exemple simple.
Supposons que vous enseignez dans une classe où les étudiants sont divisés en deux groupes de travaux dirigés (TD). À l’issue des examens, vous stockez dans un fichier leurs notes en spécifiant leur groupe de TD. Vous souhaitez calculer des statistiques et les différencier par groupe. Nativement dans votre tableur, vous allez pouvoir utiliser :
- la formule MOYENNE.SI pour calculer la moyenne des notes du groupe 1 et la moyenne des notes du groupe 2 ;
- la formule NB.SI pour calculer le nombre d’étudiants du groupe et le nombre d’étudiants du groupe 2.
Seulement, si vous souhaitez également trouver la médiane, le maximum, le minimum ou même l’écart type par groupe, vous risquez d’être très vite bloqué.
Pour résoudre ce problème, utilisez la formule ARRAYFORMULA et imbriquez la avec la formule SI et la formule de la statistique à calculer.
Pour illustrer la méthode de calcul des statistiques conditionnelles, j’ai généré un tableur dans Google Drive. Commencez par le copier en cliquant ci-dessous :
▶︎ Copiez l’outil dans votre compte Google Drive
Voici les formules utilisées pour calculer les statistiques conditionnelles :
- médiane conditionnelle : =ARRAYFORMULA(MEDIANE(SI(B9:B17=F8;C9:C17)))
- maximum conditionnel : =ARRAYFORMULA(MAX(SI(B9:B17=F8;C9:C17)))
- maximum conditionnel : =ARRAYFORMULA(MIN(SI(B9:B17=F8;C9:C17)))
- variance conditionnelle : =ARRAYFORMULA(VAR(SI(B9:B17=F8;C9:C17)))
- écart-type conditionnel : =ARRAYFORMULA(ECARTYPE(SI(B9:B17=F8;C9:C17)))
Si nous décryptons littéralement la première formule, nous demandons au tableur de calculer la médiane des données stockées dans les cellules C9 à C17, seulement si les valeurs contenues dans les cellules B9 à B17 sont égales à F8, c’est à dire le nom du groupe.
Comme vous pouvez l’observer, les moyennes de chaque groupe sont relativement similaires. Pourtant, en analysant de plus près les statistiques et notamment l’écart-type, nous remarquons que le groupe 1 a un niveau bien plus homogène que le groupe 2. Dans le cas d’un jeu de données comportant des centaines voire des milliers de lignes, cette analyse pourra faire naitre des hypothèses intéressantes à vérifier.
À mon sens, la maitrise des tableurs et de ce type de formules est redoutable pour analyser en profondeur les chiffres d’un tableau. C’est d’autant plus important à l’heure où la collecte des données est facilitée par le développement des outils numériques.
Je vous vois venir : il existe un grand nombre d’outils qui permettent de calculer tout plein de belles statistiques en cliquant sur un bouton. C’est vrai, mais s’y restreindre comporte à mon sens trois limites principales que vous devez avoir à l’esprit :
- la construction des indicateurs est opaque et il est souvent difficile de savoir clairement comment un chiffre a été calculé. Ce qui peut-être ennuyant si vous préconisez des actions en vous appuyant sur ces chiffres ;
- l’automatisation de calcul est souvent réservée aux fonctionnalités premium des logiciels, ce qui constituera des coûts ayant un impact sur votre rentabilité ;
- la dépendance à l’outil : vous basez vos analyses sur les chiffres qu’il propose et pas en fonction de votre propre besoin.
Ceux qui utilisent Google Analytics vous le diront : rien ne vaut un export brut des données à retravailler avec Excel. Adoptez donc le bon réflexe : choisissez vos propres indicateurs, construisez-les gratuitement et soyez indépendant dans le pilotage de votre stratégie digitale 🙂
Les prénoms et les noms des étudiants sont bien évidemment des fakes et ont été générés par Fakenamegenerator.
