Accéder au contenu principal

Kutools for Office — Une Suite. Cinq Outils. Accomplissez Plus.

Comment calculer la moyenne tous les 5 lignes ou colonnes dans Excel ?

Author Xiaoyang Last modified

Lorsque vous travaillez avec de grands ensembles de données dans Excel, il est courant d'avoir besoin de calculs de moyennes pour chaque groupe de lignes ou colonnes - par exemple, toutes les 5 lignes ou toutes les 5 colonnes. Bien que vous puissiez insérer manuellement des formules comme =AVERAGE(A1:A5), =AVERAGE(A6:A10), =AVERAGE(A11:A15), et ainsi de suite, cela devient rapidement impraticable si votre liste contient des centaines ou des milliers de cellules. Répéter ces opérations manuellement est à la fois chronophage et sujet aux erreurs. Heureusement, Excel offre plusieurs façons d'automatiser cette tâche, rendant l'analyse des données beaucoup plus efficace et moins fastidieuse. Cet article présentera plusieurs méthodes pratiques pour calculer la moyenne tous les 5 lignes ou colonnes, y compris les approches basées sur des formules, les add-ins Excel, l'automatisation VBA et les techniques de tableau croisé dynamique, pour vous aider à choisir la meilleure solution pour votre scénario.

Moyenne tous les 5 lignes ou colonnes avec des formules

Moyenne tous les 5 lignes avec Kutools pour Excel

Moyenne tous les 5 lignes ou colonnes avec du code VBA

Moyenne tous les 5 lignes avec un Tableau Croisé Dynamique


Moyenne tous les 5 lignes ou colonnes avec des formules

Si vous préférez utiliser des formules Excel standard, vous pouvez automatiser le calcul pour chaque groupe de 5 lignes ou colonnes sans avoir besoin d'add-ins ou de scripts. Cette approche convient particulièrement aux ensembles de données statiques où vous devez simplement générer des ensembles de moyennes pour soutenir votre analyse. Cependant, il faut veiller à référencer correctement vos données et gérer les intervalles vides ou irréguliers.

L'exemple suivant montre comment calculer la moyenne de chaque groupe de 5 lignes dans une colonne :

1. Entrez la formule suivante dans la première cellule où vous souhaitez afficher le résultat (par exemple, C2) :

=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))

Ici, A2 est la cellule de départ de votre colonne de données, C2 est la cellule de sortie de la formule, et 5 est l'intervalle (nombre de lignes à moyenner). Assurez-vous d'ajuster ces références en fonction de votre ensemble de données réel.

Après avoir entré la formule, appuyez sur Entrée. Le premier résultat moyen sera affiché. Voir capture d'écran :

a screenshot of using formula to calculate the average of every5 rows

2. Sélectionnez la cellule contenant la formule, puis faites glisser la poignée de recopie vers le bas jusqu'à ce que vous rencontriez une valeur d'erreur (comme #DIV/0! s'il reste moins de 5 valeurs dans les données restantes). Cela récupérera automatiquement les moyennes pour chaque groupe de 5 lignes. Voir capture d'écran :

a screenshot showing all results

Conseils et Notes : Vous pouvez utiliser des fonctions de gestion des erreurs comme SIERREUR() pour supprimer les valeurs d'erreur si vos données ne se divisent pas parfaitement en groupes complets, par exemple :

=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")

Pour calculer la moyenne tous les 5 colonnes sur une ligne, appliquez la formule suivante (placez-la en A3 et tirez horizontalement) :

=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))

Ici, A1 est la cellule de départ, A3 est votre cellule de sortie de formule, et 5 est le nombre de colonnes dans chaque groupe. Ajustez les références de cellules selon la disposition de vos données.
Après avoir entré la formule et appuyé sur Entrée, faites glisser la poignée de recopie vers la droite jusqu'à ce que vous atteigniez une valeur d'erreur. Voir capture d'écran :

a screenshot of using formula to calculate the average of every5 columns

Cette méthode basée sur des formules est idéale pour des calculs ponctuels rapides ou lorsque vous ne voulez pas utiliser d'outils supplémentaires. Cependant, à mesure que vos données changent en taille ou en forme, vous devrez peut-être ajuster les formules ou mettre à jour manuellement les plages de cellules, et gérer les groupes incomplets pourrait nécessiter des précautions supplémentaires.


Moyenne tous les 5 lignes avec Kutools pour Excel

Kutools pour Excel fournit une solution graphique pratique si vous avez souvent besoin de faire des moyennes de groupes de lignes sans gérer des formules complexes. À travers les fonctions Insérer des sauts de page toutes les autres lignes et Statistiques de la page de données, vous pouvez segmenter rapidement vos données et calculer des moyennes par lots en quelques clics seulement. Cette méthode est particulièrement utile si vous souhaitez appliquer des moyennes à des intervalles répétitifs et visualiser directement les groupes dans votre feuille de calcul.

Après avoir téléchargé et installé Kutools pour Excel, veuillez suivre ces étapes :

1. Cliquez sur Kutools Plus > Imprimer > Insérer des sauts de page toutes les autres lignes. Voir capture d'écran :

a screenshot of enabling the Insert Page Break Every Row feature

2. Dans la boîte de dialogue Insérer des sauts de page toutes les autres lignes, spécifiez l'intervalle (par exemple, 5) pour insérer un saut de page après chaque groupe de 5 lignes. Cela permet à Kutools de segmenter automatiquement vos données. Voir capture d'écran :

a screenshot of specifying the interval of rows

3. Ensuite, cliquez sur Kutools Plus > Imprimer > Statistiques de la page de données. Voir capture d'écran :

a screenshot of enabling the Paging Subtotals feature of Kutools

4. Dans la boîte de dialogue Statistiques de la page de données, sélectionnez la ou les colonnes que vous souhaitez moyenner, puis choisissez Moyenne comme méthode de calcul. Voir capture d'écran :

a screenshot of choosing Average as the paging subtotals

5. Cliquez sur OK, et Kutools insère instantanément des lignes de sous-totaux avec des valeurs moyennes à chaque intervalle de 5 lignes. Voir capture d'écran :

a screenshot showing the average of every5 rows

Téléchargez et essayez gratuitement Kutools pour Excel dès maintenant !

Kutools facilite la réalisation rapide de regroupements et analyses de données répétitives sans avoir besoin d'ajuster les formules ou d'écrire des scripts. Cependant, veuillez noter que les sauts de page insérés peuvent affecter la mise en page et les vues d'impression, donc vous pourriez vouloir les supprimer après utilisation si ils ne sont pas nécessaires pour votre rapport.


Moyenne tous les 5 lignes ou colonnes avec du code VBA

Si vous avez besoin de calculer régulièrement la valeur moyenne pour un nombre fixe de lignes ou de colonnes dans des ensembles de données volumineux ou changeants, automatiser ce processus avec VBA peut vous faire économiser beaucoup de travail manuel. VBA vous permet de parcourir vos données, de les regrouper comme nécessaire, et de produire le résultat moyen pour chaque lot. Cette méthode convient particulièrement bien aux utilisateurs avancés ou ceux gérant des blocs de données dynamiques, et évite d’encombrer votre feuille de calcul avec des formules. Voici un macro VBA généraliste que vous pouvez facilement adapter.

Automatiser la moyenne tous les 5 lignes :

1. Cliquez sur Développeur > Visual Basic pour ouvrir la fenêtre Microsoft Visual Basic pour Applications. Ensuite, cliquez sur Insertion > Module et collez le code ci-dessous dans le module :

Sub AverageEvery5Rows()
    Dim DataRange As Range
    Dim OutputCell As Range
    Dim GroupSize As Integer, i As Integer, j As Integer
    Dim LastRow As Long, StartRow As Long
    Dim SumValue As Double, CountValue As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
    Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
    GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
    
    On Error GoTo 0
    
    If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
    
    LastRow = DataRange.Rows.Count
    StartRow = 1
    i = 0
    
    Do While StartRow <= LastRow
        SumValue = 0
        CountValue = 0
        
        For j = 0 To GroupSize - 1
            If (StartRow + j) <= LastRow Then
                SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
                CountValue = CountValue + 1
            End If
        Next j
        
        If CountValue > 0 Then
            OutputCell.Offset(i, 0).Value = SumValue / CountValue
        Else
            OutputCell.Offset(i, 0).Value = ""
        End If
        
        StartRow = StartRow + GroupSize
        i = i + 1
    Loop
End Sub

2. Pour exécuter le code, cliquez sur le Run button bouton ou appuyez sur F5. Sélectionnez votre plage de données (une seule colonne), choisissez ensuite la cellule de départ pour la sortie, et spécifiez la taille du groupe (par exemple, 5). La macro générera la moyenne pour chaque groupe de 5 lignes, placées une en dessous de l'autre dans la colonne de sortie spécifiée.

Vous pouvez utiliser une macro similaire pour faire la moyenne tous les 5 colonnes sur une ligne.

Automatiser la moyenne tous les 5 colonnes :

Sub AverageEveryNColumns()
    Dim DataRange As Range
    Dim OutputCell As Range
    Dim GroupSize As Long
    Dim totalCols As Long, totalRows As Long
    Dim startCol As Long, endCol As Long, outCol As Long
    Dim v As Variant
    Dim r As Long, c As Long
    Dim sumVal As Double, cntVal As Long
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set DataRange = Application.InputBox("Select the data range (single rows)", _
                                         xTitleId, Selection.Address, Type:=8)
    Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
                                          xTitleId, , Type:=8)
    GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
    On Error GoTo 0
    
    If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
    If GroupSize < 1 Then
        MsgBox "Group size must be >= 1.", vbExclamation
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim prevCalc As XlCalculation
    prevCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    totalCols = DataRange.Columns.Count
    totalRows = DataRange.Rows.Count
    v = DataRange.Value
    outCol = 0
    For startCol = 1 To totalCols Step GroupSize
        endCol = startCol + GroupSize - 1
        If endCol > totalCols Then endCol = totalCols
        sumVal = 0
        cntVal = 0
        For r = 1 To totalRows
            For c = startCol To endCol
                If Not IsEmpty(v(r, c)) Then
                    If IsNumeric(v(r, c)) Then
                        sumVal = sumVal + CDbl(v(r, c))
                        cntVal = cntVal + 1
                    End If
                End If
            Next c
        Next r
        If cntVal > 0 Then
            OutputCell.Offset(0, outCol).Value = sumVal / cntVal
        Else
            OutputCell.Offset(0, outCol).Value = ""
        End If
        outCol = outCol + 1
    Next startCol
CleanExit:
    Application.Calculation = prevCalc
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Moyenne tous les 5 lignes avec un Tableau Croisé Dynamique

Une autre façon pratique de calculer des moyennes de groupes tous les 5 lignes consiste à utiliser un Tableau Croisé Dynamique, combiné à une colonne de numérotation ou d’index pour grouper les données. Cette méthode est particulièrement utile pour les utilisateurs qui travaillent avec des données structurées en tableaux et ont besoin d’un résumé interactif rapide sans écrire de formules ou utiliser d’add-ins. Un Tableau Croisé Dynamique gère dynamiquement les modifications de données et prend en charge un regroupement flexible - idéal pour les grands ensembles de données ou les tâches de reporting régulières.

Voici comment effectuer cette opération en utilisant une colonne d'aide et un Tableau Croisé Dynamique :

1. Ajoutez une colonne "Index" ou "Groupe" à côté de vos données pour marquer chaque groupe de 5 lignes. Dans la première ligne de données (B2), entrez :

=INT((ROW()-ROW($A$2))/5)+1

Cette formule étiquette chaque ligne en séquence, attribuant le même numéro de groupe à chaque groupe de 5 lignes. Remplissez cette formule vers le bas le long de votre ensemble de données.

2. Sélectionnez vos données et la nouvelle colonne Index, puis cliquez sur Insertion > Tableau Croisé Dynamique. Dans la boîte de dialogue de création du Tableau Croisé Dynamique, confirmez votre plage de données et choisissez où placer le Tableau Croisé Dynamique.

3. Dans la liste des champs du nouveau Tableau Croisé Dynamique, faites glisser le champ "Groupe" vers la zone Lignes, et votre champ de valeurs (par exemple, "Ventes") vers la zone Valeurs.

4. Cliquez sur la flèche déroulante dans la zone Valeurs, choisissez Paramètres du Champ de Valeurs, et sélectionnez Moyenne.

Maintenant, votre Tableau Croisé Dynamique affiche la moyenne pour chaque groupe de 5 lignes des données d'origine, regroupées commodément par la colonne d'aide.

Les principaux avantages de la méthode du Tableau Croisé Dynamique sont sa flexibilité et ses mises à jour faciles lorsque vos données sources changent. Cependant, elle nécessite l'ajout d'une colonne d'aide, et peut ne pas convenir aux situations où les données doivent rester précisément formatées ou inchangées.


Articles connexes :

Comment calculer la moyenne des 5 dernières valeurs d'une colonne lorsqu'on ajoute de nouveaux nombres ?

Comment calculer la moyenne des 3 premières ou 3 dernières valeurs dans Excel ?


Meilleurs outils de productivité pour Office

🤖 Kutools AI Aide : Révolutionnez l'analyse de données avec : Exécution intelligente | Générer du code | Créer des formules personnalisées | Analyser des données et générer des graphiques | Appeler les Fonctions améliorées de Kutools
Fonctionnalités populaires : Trouver, mettre en évidence ou marquer les doublons | Supprimer les lignes vides | Combinez les colonnes ou les cellules sans perte de données | Arrondir sans formule...
Super RECHERCHEV : Recherche multi-critères | Recherche multi-valeurs | Recherche multi-feuilles | Correspondance floue...
Liste déroulante avancée : Créer rapidement une liste déroulante | Liste déroulante dépendante | Liste déroulante à sélection multiple...
Gestionnaire de colonnes : Ajouter un nombre spécifique de Colonnes | Déplacer des Colonnes | Alterner l’état de visibilité des Colonnes masquées | Comparer des plages & Colonnes...
Fonctionnalités phares : Mise au point de la grille | Affichage de conception | Barre de formule améliorée | Gestionnaire de Classeur & Feuille de calcul | Bibliothèque d’AutoTexte | Sélecteur de date | Merge Worksheets | Chiffrer/Déchiffrer les cellules | Envoyer un e-mail par liste | Super Filtre | Filtre spécial (filtrer les cellules avec une police en gras/italique/barré...)...
Top15 des ensembles d’outils :12 outils de texte (Ajouter du texte, Supprimer des caractères spécifiques, ...) |50+ Types de graphiques (Diagramme de Gantt, ...) |40+ Formules pratiques (Calculer l’âge en fonction de la date de naissance, ...) |19 outils d’insertion (Insérer un code QR, Insérer une image depuis le chemin, ...) |12 outils de conversion (Convertir en mots, Conversion de devises, ...) |7 outils de fusion & division (Fusion avancée des lignes, Diviser les cellules, ...) | ... et bien plus encore
Utilisez Kutools dans la langue de votre choix – disponible en Anglais, Espagnol, Allemand, Français, Chinois et plus de40 autres !

Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...


Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.

  • Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !

Tous les modules complémentaires Kutools. Une seule installation

La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.

Excel Word Outlook Tabs PowerPoint
  • Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
  • Un installateur, une licence — installation en quelques minutes (compatible MSI)
  • Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
  • Essai complet30 jours — sans inscription, ni carte bancaire
  • Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels