Comment calculer la moyenne d’une plage dynamique dans Excel ?

Les formules offrent une solution polyvalente pour calculer la moyenne d’une plage dynamique, particulièrement utile lorsque le point de départ ou d’arrivée de cette plage évolue fréquemment — comme c’est souvent le cas avec les ventes mensuelles ou les totaux cumulés. En laissant une cellule d’entrée définir la limite de la plage dynamique, vous adaptez instantanément vos calculs aux nouvelles données, sans avoir à modifier votre formule.

Pour configurer cela, sélectionnez une cellule vide, telle que la cellule C4, et saisissez la formule suivante :

=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))

Appuyez ensuite sur la touche Entrée pour afficher la moyenne obtenue.

Cette formule ajuste automatiquement la plage pour inclure toutes les cellules de A2 jusqu’à la ligne indiquée en C2. Ainsi, dès que la valeur de C2 change, la plage utilisée pour le calcul de la moyenne s’adapte en conséquence — vous permettant d’élargir ou de réduire facilement cette plage au fil de l’ajout de nouvelles données ou selon la sous-partie que vous souhaitez analyser.

Remarques:

(1) Dans cette formule =IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2))) :A2 représente la première cellule de la plage à moyenner, et C2 fait référence à la cellule contenant le numéro de ligne de la dernière cellule de la plage cible. Adaptez ces références à votre propre structure de données si nécessaire. Veillez à ce que la cellule C2 renvoie un numéro de ligne valide, sous peine d’obtenir des résultats inattendus ou « #N/A ».

(2) En guise d’alternative, vous pouvez utiliser :

=AVERAGE(INDIRECT("A2:A"&C2))

Cette méthode est tout aussi efficace, car elle crée une référence textuelle de la plage que la fonction INDIRECT interprète ensuite de manière dynamique. Toutefois, évitez d’utiliser INDIRECT avec des classeurs fermés ou des jeux de données volumineux, car cela peut ralentir les calculs et s’avère moins performant qu’INDEX pour des données volatiles.

Conseil pratique : lorsque vos données s’allongent continuellement (par exemple, en ajoutant de nouvelles lignes chaque jour), utilisez les fonctions NBVAL ou NB pour définir automatiquement la référence de la borne supérieure et garantir que votre plage dynamique inclut toujours les dernières entrées.

Scénarios applicables : journaux de données quotidiens, séries chronologiques ou toute analyse où le début ou la fin de la plage est défini(e) par une saisie utilisateur ou une cellule de synthèse. Avantages : méthode directe, sans besoin d’outils supplémentaires. Limitation : nécessite un ajustement manuel de la formule si les emplacements des lignes changent radicalement.

Lorsque votre plage dynamique n’est pas définie par sa position, mais par des critères spécifiques — comme une région, une catégorie ou une étiquette personnalisée —, vous pouvez combiner des plages nommées dynamiques avec des fonctions telles qu’INDIRECT pour adapter vos calculs en temps réel. Cette approche s’avère particulièrement efficace dans les tableaux de bord : l’utilisateur sélectionne une option dans une liste déroulante et obtient instantanément les moyennes correspondantes.

Commencez par regrouper vos données selon les lignes ou les colonnes d’en-tête. Voici la marche à suivre :

1. Sélectionnez toute la zone (par exemple A1:D11), puis cliquez sur le bouton Créer à partir de la sélection dans le volet Gestionnaire de noms. Dans la boîte de dialogue qui s’ouvre, cochez les options Ligne supérieure et Colonne la plus à gauche, puis cliquez sur OK. Cette étape attribue automatiquement des plages nommées aux données organisées en lignes et en colonnes, ce qui simplifie leur utilisation dans les formules.

2. Dans la cellule vide de votre choix, saisissez la formule suivante :

=AVERAGE(INDIRECT(G2))

Ici, G2 est la cellule de critère dans laquelle les utilisateurs saisissent ou sélectionnent le nom de l’en-tête de ligne ou de colonne. Dès que G2 change (par exemple, de « Région1 » à « Région2 »), la formule calcule automatiquement la moyenne de la plage correspondante. Veillez toujours à ce que les valeurs saisies dans G2 correspondent exactement aux noms définis (y compris la casse) pour éviter les erreurs #REF !

Idéal pour : les tableaux de bord et les analyses pilotées par critères. Avantages : permet de créer des rapports dynamiques extrêmement flexibles ou d’effectuer une analyse directement dans une seule cellule grâce à l’interaction utilisateur. Limitation : nécessite une gestion rigoureuse des noms et une cohérence stricte des valeurs saisies.

Compter/sommer/calculer automatiquement la moyenne des cellules par Couleur de remplissage dans Excel

Il arrive parfois que vous mettiez en évidence des cellules à l’aide de la Couleur de remplissage, puis que vous souhaitiez ultérieurement les compter, en faire la somme ou en calculer la moyenne. L’utilitaire Compter par couleur de Kutools pour Excel résout ce problème en un clin d’œil !

Pour des comportements dynamiques avancés, tels que la moyenne des N dernières lignes, la moyenne basée sur plusieurs critères dynamiques, ou même la combinaison de données provenant de plusieurs feuilles, vous pouvez créer une macro VBA personnalisée. Cette méthode est particulièrement utile lorsque les formules intégrées deviennent trop complexes pour votre scénario, ou lorsque vous avez besoin d’une automatisation capable de s’adapter à des structures en constante évolution.

Par exemple, vous souhaiterez peut-être calculer la moyenne des N dernières lignes de la colonne A, où N est saisi par l’utilisateur, ou encore la moyenne de valeurs issues de plages non contiguës définies par l’utilisateurPlage limitée.

1. Accédez à Outils de développement > Visual Basic pour ouvrir l’éditeur Microsoft Visual Basic pour Applications. Ensuite, sélectionnez Insertion > Module et collez le code VBA suivant :

Sub DynamicAverage_LastNRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim N As Long
    Dim result As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    N = Application.InputBox("How many last rows to average?", xTitleId, 5, Type:=1)
    
    If N <= 0 Or N > lastRow - 1 Then
        MsgBox "Invalid input for N!", vbExclamation
        Exit Sub
    End If
    
    Set rng = ws.Range("A" & lastRow - N + 1, "A" & lastRow)
    result = Application.WorksheetFunction.Average(rng)
    
    MsgBox "Average of the last " & N & " rows in column A: " & result, vbInformation
End Sub

2. Cliquez sur le bouton pour exécuter la macro. Dans la boîte de dialogue qui s’affiche, saisissez le numéro de la dernière ligne dont vous souhaitez calculer la moyenne (par exemple : 5, 10, etc.), puis cliquez sur OK. Le résultat s’affichera dans une boîte de message.

Pour calculer des moyennes selon des conditions plus complexes (par exemple, en fonction de critères spécifiques ou à partir de plusieurs feuilles), vous pouvez adapter le code VBA en conséquence — par exemple, en intégrant des InputBox pour saisir une valeur de critère ou en parcourant plusieurs feuilles de calcul afin de fusionner la plage avant de calculer la moyenne.

Cette approche offre une flexibilité maximale et permet d’automatiser des calculs de moyenne dynamiques complexes ou répétitifs. Toutefois, assurez-vous d’activer les macros et d’utiliser cette méthode uniquement dans un classeur de confiance afin d’éviter tout risque de sécurité. Sauvegardez votre travail avant d’exécuter de nouvelles macros et envisagez de créer des sauvegardes lorsque vous automatisez des modifications.

Avantages : permet l’automatisation, gère des scénarios de données complexes ou volumineux, et peut être adapté à des logiques métier très spécifiques. Inconvénients : nécessite des connaissances de base en VBA, et les procédures doivent être mises à jour si la structure des données évolue.