Come calcolare la media delle celle da fogli diversi in Excel?

Se è necessario calcolare la media dello stesso intervallo su diversi fogli di lavoro—ad esempio, per trovare la media delle vendite nell'intervallo A1:A10 nei fogli denominati Sheet1 fino a Sheet5—Excel fornisce una soluzione basata direttamente su formule. Questo approccio funziona meglio quando tutti i fogli hanno la stessa struttura e nomi consistenti.

Passaggi:

Seleziona una cella vuota dove vuoi ottenere il risultato (ad esempio, cella C3) e inserisci la seguente formula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Dopo aver premuto Invio, Excel restituirà il valore medio dell'intervallo specificato su tutti i fogli da Sheet1 a Sheet5.

Se è necessario fare la media di valori provenienti da intervalli diversi su vari fogli, puoi elencarli manualmente:

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

Questa versione è utile quando gli intervalli variano tra i fogli. Inseriscila nella tua cella di risultato e premi Invio.

Kutools per Excel migliora la tua capacità di estrarre e consolidare valori dalla stessa cella o intervallo su più fogli di lavoro utilizzando la sua funzione Auto-incrementa i riferimenti del foglio di lavoro. Questo è particolarmente utile quando si lavora con un gran numero di fogli con layout coerente.

Passaggi d'uso:

1. Apri un nuovo foglio di lavoro (come un foglio riassuntivo), quindi seleziona la cella in cui desideri calcolare la media, ad esempio D7.

2. Vai a Kutools > Altro (all'interno del Formula gruppo) > Auto-incrementa i riferimenti del foglio di lavoro.

3. Nella finestra di dialogo:
  - Scegli un ordine di riempimento dal Ordine di Riempimento menu a discesa (ad esempio, Riempi per Colonna, quindi per Riga).
  - Nel Elenco dei fogli di lavoro, seleziona i fogli che contengono la cella di cui vuoi fare la media.
  - Clicca Compila Intervallo, quindi chiudi la finestra di dialogo.

4. I valori delle celle selezionate verranno elencati nell'intervallo (ad esempio, D7:D11). Quindi inserisci la seguente formula in un'altra cella vuota per calcolare la media:

=AVERAGE(D7:D11)

Premi Invio per ottenere il risultato. Questo semplifica il consolidamento ma non si estende automaticamente per includere nuovi fogli aggiunti—dovrai rieseguire la funzione quando cambia l'elenco dei fogli.

In alcuni casi, potrebbe essere necessario calcolare la media di diverse celle corrispondenti su più fogli di lavoro contemporaneamente—ad esempio, riassumendo i risultati per A1, B1 e C1 da ogni foglio. Questo può diventare complicato con formule standard, ma l'utilità Unisci (fogli di lavoro e cartelle di lavoro) di Kutools per Excel semplifica notevolmente questo processo.

Come utilizzare questa funzione:

1. Clicca Kutools Plus > Unisci per aprire la procedura guidata Unisci Fogli di Lavoro.

2. Nella procedura guidata (Passaggio 1 di 3):
Seleziona Unisci e calcola dati da più cartelle di lavoro in un unico foglio di lavoro, quindi clicca Avanti per continuare.

3. Nel Passaggio 2 di 3:
  - Seleziona i fogli da includere sotto Elenco dei fogli di lavoro.
  - Usa il pulsante Sfoglia pulsante per definire l'intervallo per la media.
  - Clicca Stesso intervallo se gli intervalli sono identici tra tutti i fogli.
  - Clicca Avanti per procedere.

4. Nel Passaggio 3 di 3:
Seleziona Media dal menu a discesa Funzione Opzionalmente, configura righe/colonne etichette come necessario, quindi clicca Fine.

5. Una finestra di dialogo chiederà se desideri salvare le impostazioni correnti come scenario per utilizzi futuri. Scegli Sì o No in base alle tue esigenze.

Ora, ogni cella nel tuo intervallo di output definito rifletterà la media delle celle corrispondenti da tutti i fogli selezionati. Questo metodo è particolarmente utile per operazioni ricorrenti o quando si consolidano rapidamente grandi quantità di dati strutturati.

Per gli utenti che hanno bisogno di automatizzare la media delle celle su più fogli di lavoro—specialmente quando i nomi dei fogli non sono adiacenti, cambiano frequentemente o quando si vuole specificare gli intervalli in fase di runtime—una macro VBA può essere una soluzione efficiente. Questo metodo è ideale per utenti avanzati o cartelle di lavoro in cui i fogli vengono spesso aggiunti o rinominati.

Il seguente codice VBA ti permette di inserire dinamicamente i nomi dei fogli e gli intervalli delle celle, quindi calcola la media dell'intervallo specificato su tutti i fogli elencati. È ideale per consolidare valori da cartelle di lavoro complesse o frequentemente aggiornate.

Come configurare e utilizzare questa soluzione VBA:

1. Vai alla scheda Sviluppatore in Excel. Se non è visibile, abilitala tramite File > Opzioni > Personalizza barra multifunzione. Clicca Visual Basic per aprire l'editor. Poi vai a Inserisci > Modulo e incolla il seguente codice:

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. Per eseguire la macro, premi F5 nell'editor, oppure chiudilo e vai a Sviluppatore > Macro, seleziona AverageAcrossSheets e clicca Esegui.

3. Quando richiesto, inserisci un elenco separato da virgole di nomi di fogli (ad esempio, Sheet1,Sheet3,Riepilogo), quindi specifica l'intervallo (ad esempio, A1:A10).

4. La macro calcolerà il totale e il conteggio da ogni foglio valido e mostrerà la media in una finestra di messaggio.

Note sui parametri:

• I nomi dei fogli non distinguono tra maiuscole e minuscole ma devono corrispondere esattamente.
• L'intervallo può essere una singola cella, una colonna intera (ad esempio, B:B) o un intervallo rettangolare (ad esempio, D2:E12).
• Fogli non validi o mancanti verranno saltati silenziosamente.