Vous êtes-vous déjà demandé comment calculer les percentiles pour chaque groupe dans Google Sheets ? Malheureusement, il n’existe pas de fonction dédiée à cette tâche. Mais ne vous inquiétez pas, nous pouvons utiliser une combinaison de fonctions pour obtenir le résultat souhaité.
Pourquoi calculer les percentiles groupe par groupe dans Google Sheets ?
Imaginons que vous ayez un tableau contenant les notes des élèves de deux classes différentes. Si vous souhaitez obtenir le 75e centile de toutes les notes, vous pouvez simplement utiliser la fonction PERCENTILE.
Syntaxe : =percentile(toutes_les_notes, 0,75)
Cependant, lorsque vous souhaitez calculer le centile pour chaque classe, vous ne pouvez pas utiliser cette fonction seule. C’est là que la combinaison de fonctions intervient.
Avant de passer à un exemple concret, voici quelques détails supplémentaires. Vous connaissez probablement les fonctions statistiques AVERAGE, MIN, MAX, MEDIAN, RANK, etc. Contrairement à ces fonctions, la fonction PERCENTILE n’est pas la plus couramment utilisée.
Je pense donc qu’il est judicieux de vous donner une idée de cette fonction statistique. En bref, la médiane d’un ensemble de nombres correspond au 50e centile.
Par exemple, la médiane des nombres 5 et 6 sera de 5,5. Sans surprise, le 50e centile de ces nombres sera également cette même valeur. Veuillez noter que les deux fonctions utilisent une interpolation pour déterminer la valeur au 50e centile.
Comment calculer le k-ième centile pour chaque groupe dans Google Sheets ?
Le k-ième centile est une valeur comprise entre 0 et 1 (0 % à 100 %), inclus (par exemple, vous pouvez spécifier 0,6 ou 60 % comme k-ième centile).
Voici un exemple et une formule pour calculer le 60e centile de toutes les notes :
=percentile(C2:C18,0.6)
Résultat : 81,20
Cela signifie que 81,20 est la note correspondant au 60e centile de cet ensemble. Autrement dit, 60 % des étudiants ont obtenu une note inférieure à 81.
Calcul du centile groupe par groupe dans Google Sheets
Référez-vous à l’image ci-dessus pour obtenir le résultat dans la plage de cellules F3:F5. Voici les étapes à suivre :
- La première étape consiste à obtenir les groupes (dans notre cas, les classes dans la colonne B) sans doublons. Pour cela, insérez la formule suivante dans la cellule E3 :
=sort(unique(B2:B))
Cette formule renverra les grades : Grade 1, Grade 2 et Grade 3.
- Maintenant, nous pouvons commencer à calculer le centile pour chaque groupe dans Google Sheets. Pour cela, comme je l’ai mentionné précédemment, nous pouvons utiliser une combinaison des fonctions FILTER et PERCENTILE.
Saisissez la formule suivante dans la cellule F3 et faites-la glisser jusqu’à F4 et F5 :
=percentile(filter($C$2:$C,$B$2:$B=E3),0.6)
La formule FILTER filtre les notes dans la plage C2:C en fonction du grade dans la cellule E3, c’est-à-dire $B$2:$B=E3. La fonction PERCENTILE retourne ensuite le 60e centile de ce groupe filtré.
Cette combinaison de PERCENTILE et IF peut également être utilisée :
=ArrayFormula(percentile(if($B$2:$B=E3,$C$2:$C),0.6))
Ici, la formule IF renvoie les notes si le grade correspond, sinon FALSE.
Création d’une colonne pour les percentiles
Voici un autre exemple où nous calculons le centile pour chaque groupe dans une nouvelle colonne pour toutes les lignes.
N’avez-vous pas compris ? Jetez un œil à la colonne D dans l’exemple ci-dessous. Le changement se situe dans la partie de la formule de critère de la fonction FILTER.
Dans l’exemple précédent, la fonction FILTER utilisait la sortie UNIQUE comme critère pour filtrer les notes. Ici, elle utilise plutôt la condition de la colonne B elle-même.
=percentile(filter($C$2:$C,$B$2:$B=B2),0.6)
Note : Pour le calcul, les données n’ont pas besoin d’être triées.
Voilà, c’est tout ! J’espère que cet article vous a été utile pour calculer les percentiles groupe par groupe dans Google Sheets. Rendez-vous sur Crawlan.com pour plus d’astuces et de conseils sur Google Sheets.
Merci de votre attention et bonne chance !
