Comment calculer la moyenne tous les 5 lignes ou colonnes dans Excel ?
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 :
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 :
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 :
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 :
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 :
3. Ensuite, cliquez sur Kutools Plus > Imprimer > Statistiques de la page de données. Voir capture d'écran :
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 :
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 :
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 Sub2. Pour exécuter le code, cliquez sur le 
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)+1Cette 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 3 premières ou 3 dernières valeurs dans Excel ?
Meilleurs outils de productivité pour Office
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.
- 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