Comment calculer la moyenne de chaque groupe de 5 lignes ou colonnes dans Excel ?
Lorsque vous travaillez avec de grands jeux de données dans Excel, il est fréquent de devoir calculer des moyennes pour chaque groupe de lignes ou de colonnes – par exemple, toutes les 5 lignes ou toutes les 5 colonnes. Bien que vous puissiez insérer manuellement des formules telles que =MOYENNE(A1:A5), =MOYENNE(A6:A10), =MOYENNE(A11:A15), et ainsi de suite, cette méthode devient rapidement impraticable si votre liste contient des centaines, voire des milliers de cellules. Répéter ces opérations à la main prend du temps et comporte un risque d’erreurs. Heureusement, Excel propose plusieurs façons d’automatiser cette tâche, rendant l’analyse des données bien plus efficace et moins fastidieuse. Cet article présente plusieurs méthodes pratiques pour calculer la moyenne de chaque groupe de 5 lignes ou colonnes, notamment des approches fondées sur des formules, des compléments Excel, l’automatisation via VBA et des techniques de tableau croisé dynamique, afin de vous aider à choisir la solution la mieux adaptée à votre situation.
Calculer la moyenne de chaque groupe de 5 lignes ou colonnes à l’aide de formules
Calculer la moyenne de chaque groupe de 5 lignes avec Kutools pour Excel
Calculer la moyenne de chaque groupe de 5 lignes ou colonnes à l’aide d’un code VBA
Calculer la moyenne de chaque groupe de 5 lignes avec Tableau croisé dynamique
Calculer la moyenne de chaque groupe de 5 lignes ou colonnes à l’aide de 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 recours à des compléments ni à des scripts. Cette approche est particulièrement adaptée aux jeux de données statiques, lorsque vous avez simplement besoin de générer des séries de moyennes pour appuyer votre analyse. Veillez toutefois à référencer correctement vos données et à gérer les éventuelles cellules vides ou intervalles irréguliers.
L’exemple suivant montre comment calculer la moyenne de chaque groupe de 5 lignes dans une colonne :
1. Saisissez 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 correspond à l’intervalle (nombre de lignes à moyenner). Pensez à adapter ces références en fonction de votre jeu de données réel.
Après avoir saisi la formule, appuyez sur Entrée. Le premier résultat moyen s’affiche. Voir la capture d’écran :
2.Sélectionnez la cellule contenant la formule, puis faites glisser la poignée de recopie vers le bas jusqu’à l’apparition d’une valeur d’erreur (par exemple)#DIV/0! si les données restantes contiennent moins de 5 valeurs). Cela récupérera automatiquement les moyennes pour chaque groupe de 5 lignes. Voir la capture d’écran :
Conseils et remarques :Vous pouvez utiliser des fonctions de gestion d’erreurs comme SIERREUR()pour masquer les valeurs d’erreur lorsque vos données ne se divisent pas en groupes de taille identique, par exemple :
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Pour calculer la moyenne de chaque groupe de 5 colonnes sur une ligne, appliquez la formule suivante (à placer en)A3et à recopier vers la droite) :
=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, et 5 correspond au nombre de colonnes dans chaque groupe. Ajustez les références de cellules en fonction de la disposition de vos données.
Après avoir saisi la formule et appuyé sur Entrée, faites glisser la poignée de recopie vers la droite jusqu’à l’apparition d’une valeur d’erreur. Voir capture d’écran :
Cette méthode fondée sur les formules convient parfaitement aux calculs ponctuels rapides ou lorsque vous ne souhaitez pas utiliser d’outils supplémentaires. Cependant, si la taille ou la structure de vos données évolue, vous devrez peut-être ajuster les formules ou mettre à jour manuellement les plages de cellules, et la gestion des groupes incomplets nécessitera une attention particulière.
Calculer la moyenne de chaque groupe de 5 lignes avec Kutools pour Excel
Kutools pour Excel propose une solution graphique pratique si vous devez fréquemment calculer la moyenne de groupes de lignes sans gérer de formules complexes. Grâce aux 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 sur des intervalles réguliers et visualiser directement les regroupements dans votre feuille de calcul.
Après avoir téléchargé et installé Kutools pour Excel, suivez ces étapes :
1. Cliquez sur KUTOOLS PLUS > Impression > Insérer des sauts de page toutes les autres lignes. Voir la capture d’écran :
2. Dans la boîte de dialogue Insérer des sauts de page toutes les autres lignes, indiquez l’intervalle (par exemple,)5) pour insérer un saut de page après chaque groupe de 5 lignes. Kutools segmentera ainsi automatiquement vos données. Voir la capture d’écran :
3. Ensuite, cliquez sur KUTOOLS PLUS > Impression > Statistiques de la page de données. Voir la capture d’écran :
4. Dans la boîte de dialogue Statistiques de la page de données, sélectionnez la ou les colonnes pour lesquelles vous souhaitez calculer la moyenne, puis choisissez Moyenne comme méthode de calcul. Voir la capture d’écran :
5. Cliquez sur OK et Kutools insère instantanément des lignes de sous-total avec les valeurs moyennes à chaque intervalle de 5 lignes. Voir la capture d’écran :
Téléchargez et testez gratuitement Kutools pour Excel dès maintenant !
Kutools simplifie les tâches répétitives de regroupement et d’analyse de données, sans qu’il soit nécessaire d’ajuster des formules ni d’écrire des scripts. Cependant, veuillez noter que les sauts de page insérés peuvent affecter la mise en page à l’impression ainsi que l’affichage ; il est donc recommandé de les supprimer une fois utilisés, s’ils ne sont pas indispensables dans votre rapport.
Calculer la moyenne de chaque groupe de 5 lignes ou colonnes à l’aide d’un code VBA
Si vous devez régulièrement calculer la moyenne sur un nombre fixe de lignes ou de colonnes dans des jeux de données volumineux ou en constante évolution, automatiser cette tâche avec VBA peut vous faire gagner un temps considérable. Grâce à VBA, vous pouvez parcourir vos données en boucle, les regrouper selon vos besoins et afficher instantanément la moyenne de chaque lot. Cette approche convient particulièrement aux utilisateurs avancés ou à ceux qui travaillent avec des blocs de données dynamiques, tout en évitant d’encombrer votre feuille de calcul avec des formules. Voici une macro VBA polyvalente que vous pourrez facilement adapter à vos propres besoins.
Automatiser le calcul de la moyenne de chaque groupe de 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 la cellule de départ pour la sortie, puis indiquez la taille du groupe (par exemple, 5). La macro affichera la moyenne de chaque ensemble de 5 lignes, les unes sous les autres, dans la colonne de sortie spécifiée.
Vous pouvez utiliser une macro similaire pour calculer la moyenne de chaque groupe de cinq colonnes sur une même ligne.
Automatiser le calcul de la moyenne de chaque groupe de 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
Calculer la moyenne de chaque groupe de 5 lignes avec Tableau croisé dynamique
Une autre méthode pratique pour calculer les moyennes par groupe de 5 lignes consiste à utiliser un Tableau croisé dynamique, combiné à une colonne d’index ou d’assistance pour regrouper les données. Cette approche est particulièrement utile pour les utilisateurs qui travaillent avec des données structurées sous forme de tableau et ont besoin d’un résumé rapide et interactif sans écrire de formules ni utiliser de compléments. Un Tableau croisé dynamique gère dynamiquement les modifications des données et permet un regroupement flexible – idéal pour les grands jeux de données ou les tâches de reporting régulières.
Voici comment effectuer cette opération à l’aide d’une colonne d’assistance et d’un Tableau croisé dynamique :
1.Ajoutez une colonne « Index » ou « Groupe » à côté de vos données pour identifier chaque groupe de 5 lignes. Dans la première ligne de données ()B2), saisissez :
=INT((ROW()-ROW($A$2))/5)+1Cette formule numérote chaque ligne séquentiellement en attribuant le même identifiant de groupe à chaque bloc de 5 lignes. Étirez-la vers le bas, parallèlement à votre jeu de données.
2. Sélectionnez vos données ainsi que la nouvelle colonne d’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 l’emplacement du tableau croisé dynamique.
3. Dans la liste des champs du nouveau tableau croisé dynamique, faites glisser le champ « Groupe » vers la zone Lignes, puis votre champ de valeurs (par exemple, « Ventes ») vers la zone Valeurs.
4. Cliquez sur la flèche déroulante dans la zone Valeurs, choisissez Valeur Paramètres de champ, puis sélectionnez Moyenne.
Votre tableau croisé dynamique affiche désormais la moyenne calculée sur chaque bloc de 5 lignes des données d’origine, regroupées de façon pratique grâce à la colonne auxiliaire.
Les principaux avantages de la méthode Tableau croisé dynamique résident dans sa flexibilité et la facilité avec laquelle elle s’adapte lorsque votre Données source évolue. Toutefois, elle nécessite l’ajout d’une colonne auxiliaire et peut ne pas convenir aux situations où les données doivent conserver un formatage précis ou rester inchangées.
Articles associés :
Comment calculer la moyenne des trois premières ou dernières valeurs dans Excel ?
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels