Comment calculer rapidement un percentile ou un quartile en excluant les zéros dans Excel ?
Lorsqu’ils appliquent les fonctions PERCENTILE ou QUARTILE dans Excel, les utilisateurs rencontrent souvent des situations où leur Plage de données contient des Valeurs nulles. Par défaut, ces fonctions incluent les zéros dans leurs calculs, ce qui peut fausser significativement les résultats en abaissant les valeurs de percentile ou de quartile, surtout si le zéro ne représente pas une donnée pertinente dans le contexte. Pour une analyse statistique plus précise, vous souhaiterez peut-être ignorer entièrement les Valeurs nulles lors du calcul du percentile ou du quartile. Ce tutoriel vous présente plusieurs techniques pratiques pour résoudre efficacement ce problème dans Excel, notamment des formules natives, des solutions VBA et des discussions sur les scénarios d’utilisation appropriés afin de vous aider à choisir la méthode la mieux adaptée à vos besoins.
PERCENTILE ou QUARTILE en ignorant les zéros
PERCENTILE en ignorant les zéros (formule matricielle)
Pour calculer un percentile tout en ignorant les zéros, utilisez une formule matricielle qui prend en compte uniquement les valeurs strictement supérieures à zéro.
Sélectionnez une cellule vide où vous souhaitez afficher le résultat et saisissez la formule suivante :
=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)Après avoir saisi la formule, vous devez appuyer sur Ctrl + Maj + Entrée (et non simplement sur Entrée), car il s’agit d’une formule matricielle. Excel entourera alors la formule d’accolades { }, indiquant qu’elle a été correctement validée. Dans cette formule :
- A1:A13 est votre plage de données — ajustez-la selon les besoins de votre propre feuille.
- 0,3 indique le 30 epercentile. Vous pouvez modifier cette valeur pour calculer le percentile souhaité (par exemple, 0,75 pour le 75)e percentile).
Cette méthode est particulièrement utile lorsque vous souhaitez éviter que les zéros—comme les mesures manquantes ou nulles—n’influencent les résultats statistiques.
Attention : appuyer uniquement sur Entrée ne fonctionnera pas correctement ; vous devez utiliser Ctrl + Maj + Entrée. Par ailleurs, les formules contenant SI(...) à l’intérieur de fonctions d’agrégation peuvent être moins performantes sur de grands ensembles de données.
QUARTILE en ignorant les zéros (formule matricielle)
Cette approche s’applique de la même manière aux quartiles. Sélectionnez une cellule pour afficher le résultat, puis saisissez :
=QUARTILE(IF(A1:A18>0,A1:A18),1)Après avoir saisi la formule, appuyez sur Ctrl + Maj + Entrée pour la valider en tant que formule matricielle.
- A1:A18 est la plage de données de l’échantillon (à modifier si nécessaire).
- 1signifie que vous souhaitez le premier quartile (25)e percentile). Vous pouvez utiliser 2 pour la médiane ou 3 pour le troisième quartile (75 e percentile).
Assurez-vous que votre plage de données ne contient ni texte ni cellules d’erreur, car la formule ne fonctionne qu’avec des valeurs numériques. Cette solution est particulièrement adaptée aux jeux de données de taille modérée nécessitant un calcul rapide, sans avoir recours à VBA ni à des compléments.
Macro VBA pour filtrer et calculer le percentile/quartile en excluant les zéros
Vous pouvez également recourir à VBA (Visual Basic for Applications) pour automatiser le filtrage des valeurs nulles, puis calculer un percentile ou un quartile sur les données restantes. Cette solution s’avère particulièrement efficace pour traiter de grands ensembles de données ou lorsque vous devez répéter cette opération fréquemment, sans avoir à saisir manuellement des formules.
Scénarios d’application : Idéal pour les utilisateurs avancés, les tâches répétitives ou les plages complexes. En personnalisant le code, vous pouvez gérer n’importe quel indice de percentile ou de quartile, ainsi que toute zone de données.
1. Accédez à l’onglet Outils de développement dans Excel. S’il n’est pas visible, cliquez avec le bouton droit sur le ruban, choisissez Personnaliser le ruban, puis cochez Développeur. Cliquez ensuite sur Outils de développement > Visual Basic.
2. Dans la fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insertion > Module.
3. Copiez-collez le code VBA suivant dans le module :
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Cliquez sur le bouton 
ou appuyez sur F5dans la fenêtre VBA pour exécuter la macro. Une invite vous demandera de sélectionner votre plage de données (chiffres uniquement), puis de spécifier le percentile souhaité (par exemple, 0,3 pour le 30)e percentile) ainsi que l’indice de quartile (comme 1 pour le premier quartile). La macro filtrera automatiquement les valeurs nulles et affichera les résultats dans une boîte de message.
Avantages : Traite rapidement de grands volumes ou des données irrégulières, élimine entièrement les valeurs nulles et évite la saisie manuelle de formules. Permet une réutilisation et une personnalisation aisées.
Inconvénients : Nécessite l’activation des macros et une certaine familiarité avec VBA. Ne convient pas directement aux formules de feuille de calcul, sauf si converti en fonction définie par l’utilisateur (UDF).
Problèmes courants et dépannage : si vous sélectionnez des cellules non numériques ou contenant des erreurs, la macro risque de les ignorer ou d’afficher un message d’erreur. Veillez à ce que la plage de données ne contienne que des nombres (zéros et valeurs positives). Si aucune valeur non nulle n’est détectée, un message vous en avertira.
Conseils : vous pouvez personnaliser davantage le code VBA pour copier les résultats dans une cellule précise, adapter les fonctions de calcul ou automatiser le traitement sur plusieurs plages. Sauvegardez toujours votre classeur avant d’exécuter ou de modifier des macros afin d’éviter toute perte accidentelle de données.
Si vous devez étendre cette solution au calcul de percentiles ou de quartiles sur plusieurs colonnes, envisagez de modifier la macro en ajoutant une boucle sur les colonnes ou les plages concernées.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels