Comment calculer la médiane si plusieurs conditions dans Excel ?

Le calcul de la médiane d'un ensemble de données dans Excel est une opération fréquemment nécessaire dans l'analyse et la création de rapports. Bien que trouver la médiane pour une plage simple puisse être accompli rapidement en utilisant des fonctions standard d'Excel, il arrive souvent que vous ayez besoin de la valeur médiane uniquement à partir des données qui répondent à plusieurs critères spécifiques - par exemple, trouver le montant médian des ventes pour un produit particulier à une date précise dans un grand ensemble de données. Gérer de telles opérations complexes et conditionnelles avec les fonctions traditionnelles peut s'avérer difficile. Dans ce tutoriel, nous allons introduire diverses solutions pratiques pour calculer la médiane sous plusieurs conditions dans Excel, en explorant à la fois les approches basées sur des formules et l'automatisation via VBA pour des besoins avancés.

• Calculer la médiane si elle répond à plusieurs conditions
• Code VBA - Calculer la médiane avec plusieurs conditions

Calculer la médiane si elle répond à plusieurs conditions

Supposons que vous ayez une plage de données comme indiqué ci-dessous, et que votre tâche soit de déterminer la valeur médiane qui répond à deux critères : par exemple, déterminer la valeur médiane de la colonne B où la colonne A a la valeur "a" et la colonne C a la date "2-Jan". Ce scénario est particulièrement courant dans les rapports de vente, les résultats des tests en classe et d'autres analyses de données commerciales ou académiques où il est nécessaire de filtrer selon plusieurs catégories.

Pour plus de clarté, préparons la feuille de calcul comme suit : dans votre feuille Excel, entrez vos conditions et créez une mise en page similaire à l'image ci-dessous. Ici, la colonne E liste les critères pour la colonne A, et la ligne 1 des colonnes F et suivantes représente les critères de date issus de la colonne C.

Pour calculer la médiane répondant à plusieurs critères, vous pouvez utiliser une formule matricielle qui exploite les fonctions MEDIANE et SI pour construire une liste filtrée de valeurs basée sur vos conditions. Voici comment procéder :

1. Cliquez sur la cellule F2, où vous souhaitez que le résultat de la médiane apparaisse, et entrez la formule suivante :

=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))

Cette formule fonctionne en vérifiant, pour chaque ligne, si la valeur dans la colonne A correspond à la condition dans E2 et si la valeur dans la colonne C correspond à l'en-tête dans F1. Si les deux conditions sont satisfaites, elle rassemble la valeur dans la colonne B pour le calcul de la médiane.

2. Après avoir entré la formule, appuyez sur Ctrl + Maj + Entrée (et non simplement Entrée), car il s'agit d'une formule matricielle. Excel entourera automatiquement la formule d'accolades { } pour indiquer qu'il s'agit d'une formule matricielle.

3. Faites glisser la poignée de recopie du coin inférieur droit de F2 pour copier la formule dans d'autres cellules pertinentes où vous avez besoin de médianes sous différentes conditions, comme indiqué ci-dessous :

Explications des paramètres et conseils d'utilisation : Dans la formule, $A$2:$A$12 est la plage contenant la première condition (comme les noms de produits), $C$2:$C$12 est la plage pour la deuxième condition (comme les dates), et $B$2:$B$12 est la plage contenant les valeurs numériques pour lesquelles vous voulez la médiane. Ajustez ces plages selon vos besoins pour votre propre feuille de calcul. Utilisez toujours des références absolues ($ symboles) pour vous assurer que les plages ne changent pas lorsque vous copiez la formule.

Précautions : Si aucune valeur ne remplit les deux conditions, la formule renverra une erreur #NOMBRE ! Pour éviter toute confusion, vous pouvez imbriquer la formule dans SIERREUR pour renvoyer une cellule vide ou un message personnalisé :

=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")

Assurez-vous que vos données ne contiennent pas de cellules vides ou de valeurs non numériques dans la colonne médiane, car cela pourrait également affecter les résultats.

Cette approche basée sur des formules est adaptée lorsque vous avez des conditions relativement simples (généralement jusqu'à deux ou trois critères). Elle est rapide à mettre en place et ne nécessite aucune compétence en programmation. Cependant, pour un filtrage complexe avec des conditions dynamiques ou des ensembles de données volumineux, la maintenance ou la modification des formules matricielles peut devenir fastidieuse.

Code VBA - Calculer la médiane avec plusieurs conditions

Pour les scénarios où vous devez automatiser le calcul de la médiane conditionnelle - par exemple, lorsqu'il y a beaucoup de conditions, des ensembles de données volumineux ou lorsque les critères eux-mêmes changent fréquemment - une solution VBA peut offrir une alternative pratique. En utilisant VBA, vous pouvez créer une macro réutilisable qui calcule la médiane en fonction de n'importe quel nombre de conditions. Les solutions basées sur VBA sont particulièrement utiles si vous souhaitez rationaliser des analyses répétitives ou développer des processus Excel personnalisés pour les rapports et les tableaux de bord.

Suivez ces étapes pour utiliser VBA afin de calculer la médiane conditionnelle :

1. Cliquez sur Outils de développement > Visual Basic. Une nouvelle fenêtre Microsoft Visual Basic pour Applications s'ouvrira. Cliquez sur Insérer > Module, puis collez le code suivant dans le Module :

Sub ConditionalMedian()
    Dim DataRange As Range
    Dim CriteriaRange1 As Range
    Dim CriteriaRange2 As Range
    Dim OutputRange As Range
    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim TempArr() As Double
    Dim i As Long
    Dim j As Long
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
    Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
    Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
    Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
    Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
    Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
    
    count = 0
    For i = 1 To DataRange.Rows.count
        If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
           CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
            ReDim Preserve TempArr(count)
            TempArr(count) = DataRange.Cells(i, 1).Value
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        OutputRange.Value = "No match"
    Else
        Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
        If count Mod 2 = 1 Then
            OutputRange.Value = TempArr(count \ 2)
        Else
            OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
        End If
    End If
End Sub

Sub QuickSort(arr() As Double, first As Long, last As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Double
    Dim temp As Double
    
    i = first
    j = last
    pivot = arr((first + last) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If first < j Then
        QuickSort arr, first, j
    End If
    
    If i < last Then
        QuickSort arr, i, last
    End If
End Sub

2. Cliquez sur le bouton (ou appuyez sur F5) pour exécuter le code. Vous serez invité à sélectionner chacune des plages nécessaires et à saisir vos critères. Après avoir complété les invites, le résultat (la médiane qui remplit tous les critères) sera affiché dans la cellule cible que vous avez spécifiée.

Cette macro vous permet de sélectionner de manière flexible la plage de valeurs, les plages de critères, les valeurs des critères et l'emplacement où afficher le résultat à chaque exécution. Vous pouvez également facilement adapter le code pour inclure plus de conditions si nécessaire.

Conseils et dépannage : Lorsque vous utilisez des solutions VBA, assurez-vous que toutes les plages sélectionnées ont des longueurs égales, et que les critères correspondent au bon type de données et au bon format (par exemple, texte vs dates). Si aucune valeur ne remplit les critères, l'affichage indiquera "Pas de correspondance." Pour une meilleure stabilité, enregistrez votre classeur avant d'exécuter la macro et activez toujours les macros lorsque vous y êtes invité. Cette solution VBA convient aux utilisateurs familiers avec les paramètres de sécurité des macros et pour une utilisation dans des flux de travail Excel automatisés.

En résumé, l'approche VBA automatise les calculs complexes de médiane qui sont fastidieux ou difficiles à réaliser avec des formules seules. Elle est particulièrement bien adaptée lorsque vous traitez des conditions variables, des recalculs fréquents et des ensembles de données volumineux.

Articles connexes :

• Calculer la médiane dans une plage