Come si calcola la media delle celle provenienti da fogli diversi in Excel?

Se devi calcolare la media dello stesso intervallo su più fogli di lavoro—ad esempio, per trovare la media delle vendite nell'intervallo A1:A10 nei fogli denominati Foglio1 fino a Foglio5—Excel offre una soluzione diretta basata su formule. Questo approccio funziona al meglio quando tutti i fogli hanno la stessa struttura e una denominazione coerente.

Passaggi:

Selezionare una cella vuota in cui si desidera visualizzare il risultato (ad esempio, la cella C3) e inserire la seguente formula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Dopo aver premuto Invio, Excel restituirà il valore medio dell’intervallo limitato in tutti i fogli da Foglio1 a Foglio5.

Se è necessario calcolare la media di valori provenienti da intervalli diversi nei vari fogli, è possibile elencarli manualmente:

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

Questa versione è ideale quando gli intervalli variano da foglio a foglio. Inseriscila nella cella del risultato e premi Invio.

Kutools per Excel potenzia la capacità di estrarre e consolidare valori dalla stessa cella o intervallo su più fogli di lavoro grazie alla sua funzionalità Auto-incrementa i riferimenti del foglio di lavoro. Questa opzione è particolarmente utile quando si lavora con un gran numero di fogli dal layout coerente.

Procedura d’uso:

1. Apri un nuovo foglio di lavoro (ad esempio, un foglio riepilogativo) e seleziona la cella in cui desideri calcolare la media—per esempio, D7.

2. Andare su Kutools > Altro(nel gruppo)Formule) > Auto-incrementa i riferimenti del foglio di lavoro.

3. Nella finestra di dialogo:
- Scegliere un Ordine di Riempimentodal menu a discesa (ad esempio,)Riempi per Colonna, quindi per Riga).
- Nella sezione Elenco dei fogli di lavoro, selezionare i fogli che contengono la cella di cui si desidera calcolare la media.
- Fare clic su Compila intervallo, quindi chiudere la finestra di dialogo.

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

=AVERAGE(D7:D11)

Premere Invio per ottenere il risultato. Questo semplifica la consolidazione, ma non si aggiorna automaticamente per includere nuovi fogli aggiunti in seguito: sarà necessario rieseguire la funzione ogni volta che l'elenco dei fogli cambia.

In alcuni casi potrebbe essere necessario calcolare contemporaneamente le medie di diverse celle corrispondenti su più fogli di lavoro—ad esempio, per riepilogare i risultati delle celle A1, B1 e C1 da ogni foglio. Questa operazione può diventare complessa con le formule standard, ma lo strumento Kutools per Excel Combina(fogli di lavoro e cartelle di lavoro) semplifica notevolmente il processo.

Come utilizzare questa funzione:

1. Fare clic su KUTOOLS PLUS > Combina 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 fai clic su Avanti per continuare.

3. Nel Passaggio 2 di 3:
- Seleziona i fogli di lavoro da includere in Elenco dei fogli di lavoro.
- Usa il pulsante Sfoglia per definire l'intervallo da mediare.
- Fai clic su Stesso intervallo se gli intervalli sono identici in tutti i fogli.
- Fai clic su Avanti per procedere.

4. Nel Passaggio 3 di 3:
Seleziona Media dal menu a discesa Funzione. Se necessario, configura le etichette di riga/colonna, quindi fai clic su Fine.

5. Verrà visualizzata una finestra di dialogo che chiede se desideri salvare le impostazioni correnti come scenario per utilizzi futuri. Scegli Sì o No in base alle tue esigenze.

Ora, ogni cella nell'Area di posizionamento dell'elenco definito mostrerà la media delle celle corrispondenti da tutti i Fogli di lavoro selezionati. Questo metodo è particolarmente utile per operazioni ricorrenti o quando si deve consolidare rapidamente grandi quantità di dati strutturati.

Per gli utenti che devono automatizzare il calcolo della media di celle su più fogli di lavoro — soprattutto quando i nomi dei fogli non sono consecutivi, cambiano frequentemente o quando si desidera specificare l’intervallo in fase di esecuzione — una macro VBA rappresenta una soluzione efficiente. Questo approccio è ideale per utenti avanzati o per cartelle di lavoro in cui i fogli vengono spesso aggiunti o rinominati.

Il seguente codice VBA consente di inserire dinamicamente nomi di fogli e intervalli di celle, quindi calcola la media del Intervallo limitato su tutti i fogli elencati. È ideale per consolidare valori da cartelle di lavoro complesse o aggiornate frequentemente.

Come configurare e utilizzare questa soluzione VBA:

1. Vai alla scheda Sviluppo in Excel. Se non è visibile, abilitala tramite File > Opzioni > Personalizza Barra multifunzione. Fai clic su Visual Basic per aprire l'editor. Quindi, vai su 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, premere F5 nell'editor oppure chiuderlo e andare su Sviluppo > Macro, selezionare AverageAcrossSheets e fare clic su Esegui.

3. Quando richiesto, inserisci un elenco di nomi di fogli di lavoro separati da virgole (ad esempio,)Foglio1,Foglio3,Riepilogo), quindi specifica l’intervallo (ad esempio, A1:A10).

4. La macro calcolerà il totale e il conteggio da ciascun foglio valido, mostrando 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, un’intera colonna (ad esempio, B:B) o un intervallo rettangolare (come D2:E12).
• I fogli non validi o mancanti verranno ignorati in modo silenzioso.