Comment calculer la moyenne des cellules provenant de différentes feuilles dans Excel ?

Si vous devez calculer la moyenne de la même plage sur plusieurs feuilles de calcul – par exemple, pour trouver la moyenne des ventes dans la plage A1:A10 sur les feuilles nommées Feuil1 à Feuil5 – Excel propose une solution directe basée sur des formules. Cette approche fonctionne mieux lorsque toutes les feuilles ont la même structure et des noms cohérents.

Étapes :

Sélectionnez une cellule vide où vous souhaitez afficher le résultat (par exemple, la cellule C3), puis entrez la formule suivante :

=AVERAGE(Sheet1:Sheet5!A1:A10)

Après avoir appuyé sur Entrée, Excel renverra la valeur moyenne de la plage spécifiée sur toutes les feuilles de Feuil1 à Feuil5.

Si vous avez besoin de faire la moyenne des valeurs de différentes plages sur plusieurs feuilles, vous pouvez les lister manuellement :

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

Cette version est utile lorsque les plages varient entre les feuilles. Entrez-la dans votre cellule de résultat et appuyez sur Entrée.

Kutools pour Excel améliore votre capacité à extraire et consolider des valeurs de la même cellule ou plage sur plusieurs feuilles de calcul en utilisant sa fonction Incrément automatique des références de feuille de calcul. Cela est particulièrement utile lorsqu'on travaille avec un grand nombre de feuilles ayant une disposition cohérente.

Étapes d'utilisation :

1. Ouvrez une nouvelle feuille de calcul (comme une feuille de synthèse), puis sélectionnez la cellule où vous souhaitez calculer la moyenne – par exemple, D7.

2. Allez dans Kutools > Plus (dans le groupe Formule ) > Incrément automatique des références de feuille de calcul.

3. Dans la boîte de dialogue :
  - Choisissez un ordre de remplissage dans le menu déroulant Ordre de remplissage (par exemple, Remplir verticalement cellule après cellule).
  - Dans la Liste des feuilles de calcul, cochez les feuilles qui contiennent la cellule dont vous voulez calculer la moyenne.
  - Cliquez sur Remplir la plage, puis fermez la boîte de dialogue.

4. Les valeurs des cellules sélectionnées seront listées dans la plage (par exemple, D7:D11). Ensuite, entrez la formule suivante dans une autre cellule vide pour calculer la moyenne :

=AVERAGE(D7:D11)

Appuyez sur Entrée pour obtenir le résultat. Cela simplifie la consolidation mais ne s'étend pas automatiquement aux nouvelles feuilles ajoutées – vous devrez relancer la fonction lorsque la liste des feuilles change.

Dans certains cas, vous devrez peut-être calculer les moyennes pour plusieurs cellules correspondantes sur plusieurs feuilles de calcul simultanément – par exemple, résumer les résultats pour A1, B1 et C1 de chaque feuille. Cela peut devenir fastidieux avec des formules standard, mais l'utilitaire Consolider (feuilles de calcul et classeurs) de Kutools pour Excel simplifie considérablement ce processus.

Comment utiliser cette fonction :

1. Cliquez sur Kutools Plus > Consolider pour ouvrir l'assistant de consolidation des feuilles de calcul.

2. Dans l'assistant (Étape 1 sur 3) :
Cochez Consolider et calculer les valeurs de plusieurs classeurs dans une seule feuille de calcul, puis cliquez sur Suivant pour continuer.

3. À l'Étape 2 sur 3 :
  - Sélectionnez les feuilles à inclure sous Liste des feuilles de calcul.
  - Utilisez le bouton Parcourir bouton pour définir la plage pour la moyenne.
  - Cliquez sur Même plage si les plages sont identiques sur toutes les feuilles.
  - Cliquez sur Suivant pour continuer.

4. À l'Étape 3 sur 3 :
Sélectionnez Moyenne dans le menu déroulant Fonction . Configurez éventuellement les étiquettes de lignes/colonnes selon vos besoins, puis cliquez sur Terminer.

5. Une boîte de dialogue vous demandera si vous souhaitez enregistrer les paramètres actuels en tant que scénario pour une utilisation future. Choisissez Oui ou Non en fonction de vos besoins.

Maintenant, chaque cellule dans votre plage de sortie définie reflètera la moyenne des cellules correspondantes de toutes les feuilles sélectionnées. Cette méthode est particulièrement utile pour les opérations récurrentes ou lors de la consolidation rapide de grandes quantités de données structurées.

Pour les utilisateurs qui ont besoin d'automatiser le calcul de la moyenne des cellules sur plusieurs feuilles de calcul – en particulier lorsque les noms des feuilles ne sont pas adjacents, changent fréquemment ou lorsque vous souhaitez spécifier des plages au moment de l'exécution – une macro VBA peut être une solution efficace. Cette méthode convient mieux aux utilisateurs avancés ou aux classeurs où les feuilles sont souvent ajoutées ou renommées.

Le code VBA suivant vous permet de saisir dynamiquement les noms des feuilles et les plages de cellules, puis calcule la moyenne de la plage spécifiée sur toutes les feuilles listées. Il est idéal pour consolider des valeurs à partir de classeurs complexes ou fréquemment mis à jour.

Comment configurer et utiliser cette solution VBA :

1. Allez dans l'onglet Développeur dans Excel. S'il n'est pas visible, activez-le via Fichier > Options > Personnaliser le ruban. Cliquez sur Visual Basic pour ouvrir l'éditeur. Puis allez dans Insérer > Module et collez le code suivant :

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. Pour exécuter la macro, appuyez sur F5 dans l'éditeur, ou fermez-le et allez dans Développeur > Macros, sélectionnez AverageAcrossSheets, et cliquez sur Exécuter.

3. Lorsque vous y êtes invité, entrez une liste de noms de feuilles séparés par des virgules (par exemple, Feuil1,Feuil3,Synthèse), puis spécifiez la plage (par exemple, A1:A10).

4. La macro calculera le total et le compte de chaque feuille valide et affichera la moyenne dans une boîte de message.

Notes sur les paramètres :

• Les noms des feuilles ne sont pas sensibles à la casse mais doivent correspondre exactement.
• La plage peut être une cellule unique, une colonne entière (par exemple, B:B), ou une plage rectangulaire (par exemple, D2:E12).
• Les feuilles invalides ou manquantes seront ignorées silencieusement.