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

Les formules constituent une approche polyvalente pour calculer la moyenne d'une plage dynamique lorsque le point de départ ou le point final de votre plage change fréquemment, comme cela arrive souvent avec les ventes mensuelles ou les totaux cumulatifs. En permettant à une cellule d'entrée de déterminer la limite de la plage dynamique, vous pouvez vous adapter rapidement aux données mises à jour sans réécrire votre formule.

Pour mettre cela en place, sélectionnez une cellule vide, comme la cellule C4, puis entrez la formule suivante :

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

Appuyez ensuite sur la touche Entrée pour voir la moyenne résultante.

Cette formule ajuste automatiquement la plage pour inclure toutes les cellules de A2 jusqu'à la ligne indiquée par C2, de sorte que lorsque la valeur de C2 change, la plage moyennée change également. Cela la rend flexible pour étendre ou contracter dynamiquement la plage de moyenne à mesure que de nouvelles données arrivent ou que vous souhaitez analyser un sous-ensemble spécifique.

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. Modifiez ces références en fonction de votre propre structure de données si nécessaire. Assurez-vous que la cellule C2 fait référence à une ligne valide, sinon vous obtiendrez des résultats inattendus ou "NA".

(2) En 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 pour la plage, que INDIRECT interprète ensuite de manière dynamique. Cependant, soyez prudent lorsque vous utilisez INDIRECT avec des classeurs fermés ou des jeux de données volumineux, car cela peut impacter la vitesse de calcul et n'est pas aussi efficace qu'INDEX pour les données volatiles.

Astuce pratique : Lorsque vos données augmentent continuellement (par exemple, en ajoutant de nouvelles lignes tous les jours), vous pouvez utiliser une fonction COUNTA ou COUNT pour définir automatiquement la référence de la cellule limite supérieure - cela garantit que votre plage dynamique couvre toujours les entrées les plus récentes.

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 guidé par une saisie utilisateur ou une cellule de synthèse. Avantages : Direct, ne nécessite pas d'outils supplémentaires. Limitation : Nécessite un ajustement manuel de la formule si les emplacements des lignes changent considérablement.

Pour les situations où votre plage dynamique est définie non par sa position mais par des critères spécifiques (tels qu'une région, une catégorie ou une étiquette définie par l'utilisateur), vous pouvez combiner des plages nommées dynamiques et des fonctions comme INDIRECT pour adapter vos calculs. Cela est particulièrement utile pour les tableaux de bord où les utilisateurs sélectionnent une option dans une liste déroulante et voient instantanément les moyennes correspondantes.

Tout d'abord, regroupez votre ensemble de données par lignes ou colonnes d'en-tête. Voici comment :

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 Noms . Dans la fenêtre contextuelle, cochez les deux options Ligne supérieure et Colonne la plus à gauche puis cliquez sur OK. Cette étape attribue automatiquement des plages nommées aux données des lignes et colonnes, ce qui simplifie les références dans les formules.

2. Dans la cellule vide choisie, saisissez cette formule :

=AVERAGE(INDIRECT(G2))

Ici, G2 est la cellule de critères où l'utilisateur tape ou sélectionne le nom d'en-tête de ligne ou de colonne. Lorsque G2 change (par exemple, de "Région1" à "Région2"), la formule calcule dynamiquement la moyenne pour la plage correspondante. Veillez à ce que les entrées dans G2 correspondent exactement aux noms définis (y compris la sensibilité à la casse) pour éviter les erreurs #REF! .

Idéal pour : Tableaux de bord de rapport, analyses pilotées par des critères. Avantages : Permet une flexibilité très élevée pour des rapports dynamiques ou des analyses mono-cellulaires interactives. Limitation : Repose sur une gestion correcte des noms et des valeurs d'entrée cohérentes.

Compter/Sommer/Moyenner automatiquement les cellules par couleur de remplissage dans Excel

Parfois, vous marquez les cellules par couleur de remplissage, puis comptez/sommez ces cellules ou calculez leur moyenne plus tard. L'utilitaire Décompte par couleur de Kutools pour Excel peut vous aider à résoudre cela facilement.

Pour des comportements dynamiques avancés, tels que faire la moyenne des N dernières lignes, basée sur plusieurs critères dynamiques, ou même combiner des données à travers 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 qui s'adapte à des structures fréquemment changeantes.

Par exemple, vous pouvez souhaiter calculer la moyenne des N dernières lignes de la colonne A, où N est saisi par l'utilisateur, ou faire la moyenne des valeurs provenant de plages non contiguës spécifiées par l'utilisateur.

1. Allez dans Outils de Développement > Visual Basic pour ouvrir l'éditeur Microsoft Visual Basic pour Applications. Ensuite, sélectionnez Insertion > Module et collez-y 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 contextuelle, saisissez le nombre de dernières lignes que vous souhaitez moyenner (par exemple 5, 10, etc.) et appuyez sur OK. Le résultat apparaîtra dans une boîte de message.

Pour moyenner avec des conditions plus complexes (par exemple, basées sur des critères ou à partir de plusieurs feuilles), vous pouvez adapter le code VBA en conséquence, par exemple en ajoutant des InputBoxes pour une valeur de critère, ou en parcourant plusieurs feuilles de calcul pour combiner les plages avant de faire la moyenne.

Cette approche offre une flexibilité maximale et peut automatiser des calculs de moyenne complexes ou répétitifs. Cependant, assurez-vous d'activer les macros et d'utiliser cette méthode dans un classeur de confiance pour éviter les risques de sécurité. Sauvegardez votre travail avant d'exécuter de nouvelles macros, et envisagez de créer des sauvegardes lors de l'automatisation des modifications.

Avantages : Permet l'automatisation, gère des scénarios complexes ou avec des grandes quantités de données, peut être adapté à une logique métier très spécifique. Inconvénients : Requiert une connaissance de base du VBA, et les procédures doivent être maintenues si la structure change.