Comment calculer une moyenne par jour, mois, trimestre ou heure à l’aide d’un tableau croisé dynamique dans Excel ?

La fonction Tableau croisé dynamique d’Excel est un outil pratique pour résumer et analyser vos données, notamment lorsque vous devez calculer rapidement des moyennes sur des périodes discrètes telles que des jours, des mois, des trimestres ou des heures. La méthode ci-dessous élimine le filtrage manuel et les calculs répétitifs, en vous offrant un résumé interactif que vous pouvez facilement ajuster dès que vos données évoluent.

1. Sélectionnez l’intégralité de votre tableau de données source (en incluant les en-têtes), puis cliquez sur l’onglet Insertion > Tableau croisé dynamique.

2. Dans la boîte de dialogue Créer un tableau croisé dynamique qui s’affiche, choisissez Feuille de calcul existante si vous souhaitez insérer votre résumé dans la feuille active. Définissez l’Emplacement en sélectionnant une cellule où vous souhaitez que le tableau croisé dynamique apparaisse, puis cliquez sur OK.
Remarque : pour afficher le tableau croisé dynamique dans une Nouvelle feuille de calcul, sélectionnez plutôt cette option. Veillez à ce que l’emplacement choisi ne chevauche aucune donnée existante afin d’éviter les avertissements d’écrasement.

3. Dans le volet Liste des champs du tableau croisé dynamique (généralement à droite), faites glisser votre colonne Date/Heure dans la zone Lignes, et la colonne contenant les Montants (données numériques) dans la zone Valeurs. Cette configuration initiale agrège vos données par horodatage enregistré.

4. Pour organiser vos résultats par périodes spécifiques, cliquez avec le bouton droit sur une entrée de date dans le Tableau croisé dynamique, puis sélectionnez Regrouper dans le menu contextuel. Cette fonctionnalité vous permet de consolider les données en intervalles tels que des jours, des mois, des trimestres ou des heures.

5. Dans la boîte de dialogue Regroupement, choisissez la période de regroupement souhaitée en cochant l’option correspondante dans le champ Par(par exemple)Mois). Cliquez sur OK pour appliquer. Ensuite, cliquez avec le bouton droit sur la valeur Somme des montants, sélectionnez Résumer les valeurs par > Moyenne. Votre tableau croisé dynamique affiche désormais la moyenne pour chaque groupe de périodes, facilitant ainsi comparaisons et analyses immédiates.

Si votre moyenne basée sur une période doit être mise à jour avec de nouvelles données, il vous suffit d’actualiser le tableau croisé dynamique via l’onglet Analyser > Actualiser. En résumé, les tableaux croisés dynamiques offrent un moyen efficace et visuel de calculer et d’analyser des moyennes sur des intervalles temporels définis, mais peuvent nécessiter une reconfiguration manuelle si votre structure de regroupement évolue ou si le format de vos données n’est pas cohérent.

Si vous devez souvent calculer des moyennes sur des périodes spécifiques — quotidiennes, hebdomadaires, mensuelles ou annuelles — à partir de jeux de données horaires détaillés, le regroupement et le calcul manuels deviennent vite répétitifs et sujets aux erreurs. Kutools pour Excel propose des outils spécialisés qui simplifient cette tâche, notamment les fonctions Vers réel et Fusion avancée des lignes, qui rationalisent le formatage des dates et l’agrégation par lots, vous faisant gagner un temps considérable.

1. Sélectionnez les cellules contenant les dates et heures, puis formatez-les selon la période cible. Par exemple, pour obtenir des moyennes quotidiennes, sélectionnez vos données, puis accédez à l’onglet Accueil > Format de nombre > Date courte. Cette conversion d’horodatages ne conserve que la date, en supprimant l’heure.

Remarque : pour calculer une moyenne par semaine, par mois ou par année, Kutools pour Excel propose les fonctions Appliquer le format de date et Vers réel, qui permettent de convertir les horodatages au format souhaité en quelques clics seulement. Cela garantit un regroupement cohérent et un calcul précis.

Bénéficiez dès maintenant d’un essai gratuit de 30 jours avec toutes les fonctionnalités !

2. Sélectionnez l’ensemble de votre jeu de données (dates formatées et valeurs incluses), puis, dans le ruban Excel, cliquez sur Kutools > Contenu > Fusion avancée des lignes.

3. Dans la boîte de dialogue qui s’ouvre, sélectionnez votre colonne Date/Heure dans la liste, définissez-la comme Clé primaire, puis choisissez votre colonne de valeurs (par exemple Montant) et configurez-la pour Calculer > Moyenne. Cliquez sur OK pour confirmer, et Kutools calculera instantanément les moyennes pour chaque date distincte.

Les moyennes pour les périodes spécifiées sont calculées instantanément, ce qui simplifie grandement l’analyse. Si votre regroupement par date affiche des mois ou des années au lieu de jours, les résultats sont automatiquement agrégés en conséquence. Vous pouvez également reformater vos dates via Kutools > Format > Appliquer le format de date, puis finaliser avec Kutools > Vers réel.

Pour les utilisateurs qui préfèrent effectuer des calculs directement via des formules, sans recourir aux tableaux croisés dynamiques ni aux compléments, les fonctions intégrées d’Excel telles que MOYENNE.SI.ENS, SOMME.SI.ENS et NB.SI.ENS offrent une approche flexible et basée sur les cellules pour calculer des moyennes sur des périodes spécifiques. Cette méthode est particulièrement adaptée lorsque des calculs personnalisés sont nécessaires, lorsqu’il faut éviter les actualisations de tableaux, ou quand les résultats doivent s’afficher directement à côté des données.

Voici un exemple d’utilisation de formules pour calculer les valeurs moyennes quotidiennes :

1. Supposons que vos données comportent des dates en colonne A (A2:A100) et des données numériques en colonne B (B2:B100). Dans une nouvelle colonne (par exemple, cellule C2), saisissez la formule suivante pour calculer la moyenne d’un jour donné (par exemple, la date en A2) :

=AVERAGEIFS(B$2:B$100, A$2:A$100, A2)

2. Appuyez sur Entrée pour appliquer la formule. Pour calculer les moyennes de toutes les dates, recopiez-la vers le bas, à côté de vos données.Conseil
: si vous souhaitez que les moyennes quotidiennes n’apparaissent qu’une seule fois par jour, triez ou filtrez d’abord vos données afin d’afficher uniquement les dates uniques, puis appliquez la formule en conséquence.

Pour les utilisateurs qui traitent régulièrement de très grands jeux de données ou qui doivent répéter des calculs de moyenne pour différentes périodes, l’automatisation de leur flux de travail à l’aide de macros VBA peut considérablement améliorer la cohérence et l’efficacité. Les macros peuvent regrouper et calculer des moyennes par jour, mois, trimestre ou heure, éliminant totalement la répétition manuelle. Cette approche convient particulièrement aux utilisateurs avancés d’Excel et aux scénarios où les calculs doivent être exécutés fréquemment ou adaptés à de nouvelles feuilles.

1. Pour commencer, ouvrez l’éditeur VBA en cliquant sur Outils de développement > Visual Basic. Lorsque la fenêtre Microsoft Visual Basic pour Applications s’affiche, cliquez sur Insertion > Module, puis copiez le code suivant dans le module :

Sub AverageByPeriod()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim periodCol As String, valueCol As String
    Dim dict As Object
    Dim cell As Range
    Dim periodKey As String
    Dim i As Long, lastRow As Long
    Dim sumDict As Object, countDict As Object
    
    Set ws = ActiveSheet
    periodCol = "A" ' Date/Time column
    valueCol = "B" ' Value column
    lastRow = ws.Cells(ws.Rows.Count, periodCol).End(xlUp).Row
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set sumDict = CreateObject("Scripting.Dictionary")
    Set countDict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        ' Grouping by month example; change to format for day/hour/quarter if needed
        periodKey = Format(ws.Cells(i, periodCol).Value, "yyyy-mm")
        
        If Not dict.Exists(periodKey) Then
            dict.Add periodKey, dict.Count + 1
            sumDict.Add periodKey, ws.Cells(i, valueCol).Value
            countDict.Add periodKey, 1
        Else
            sumDict(periodKey) = sumDict(periodKey) + ws.Cells(i, valueCol).Value
            countDict(periodKey) = countDict(periodKey) + 1
        End If
    Next i
    
    ws.Cells(1, 4).Value = "Period"
    ws.Cells(1, 5).Value = "Average"
    
    i = 2
    Dim k As Variant
    For Each k In dict.Keys
        ws.Cells(i, 4).Value = k
        ws.Cells(i, 5).Value = sumDict(k) / countDict(k)
        i = i + 1
    Next k
End Sub

2. Après avoir collé le code, cliquez sur le bouton pour l’exécuter. La macro lira vos données (situées dans les colonnes A et B à partir de la ligne 2), les regroupera selon la période choisie (actuellement définie par mois) et affichera la moyenne de chaque groupe dans les colonnes D et E.

Conseils :

• Pour regrouper par jour, modifiez la ligne Format(..., "aaaa-mm-jj").
• Pour un regroupement trimestriel, utilisez :periodKey = "Q" & WorksheetFunction.RoundUp(Month(ws.Cells(i, periodCol).Value) /[[PH_45]]) & "-" & Annee(ws.Cells(i, periodCol).Value)
• Vérifiez toujours que vos colonnes ()periodCol, valueCol) correspondent à la structure de vos données.

Précautions :

• Si vous obtenez une erreur ou des résultats vides, vérifiez qu’il n’y a ni cellules vides ni valeurs non datées dans votre colonne de regroupement.
• Ajustez l’affectation des colonnes si nécessaire : si vos données commencent dans d’autres colonnes que A et B, mettez à jour periodCol et valueCol en conséquence.
• Pensez à enregistrer votre travail avant d’exécuter des macros pour éviter toute modification non intentionnelle de vos données.

Moyenne par année/mois/date dans Excel

Moyenne des horodatages par jour dans Excel

Moyenne hebdomadaire dans Excel