Come calcolare la media ogni 5 righe o colonne in Excel?
Quando si lavora con grandi set di dati in Excel, è comune aver bisogno di calcoli di media per ogni gruppo di righe o colonne, come ogni 5 righe o ogni 5 colonne. Anche se si potrebbero inserire manualmente formule come =MEDIA(A1:A5), =MEDIA(A6:A10), =MEDIA(A11:A15) e così via, questo diventa rapidamente impraticabile se l'elenco contiene centinaia o migliaia di celle. Ripetere manualmente queste operazioni è sia dispendioso in termini di tempo che soggetto a errori. Fortunatamente, Excel offre diversi modi per automatizzare questa attività, rendendo l'analisi dei dati molto più efficiente e meno noiosa. Questo articolo introdurrà diversi metodi pratici per calcolare la media ogni 5 righe o colonne, inclusi approcci basati su formule, add-in di Excel, automazione VBA e tecniche delle Tabelle Pivot, per aiutarti a scegliere la soluzione migliore per il tuo scenario.
Calcolare la media ogni 5 righe o colonne con le formule
Calcolare la media ogni 5 righe con Kutools per Excel
Calcolare la media ogni 5 righe o colonne con il codice VBA
Calcolare la media ogni 5 righe con la Tabella Pivot
Calcolare la media ogni 5 righe o colonne con le formule
Se preferisci utilizzare le formule standard di Excel, puoi automatizzare il calcolo per ogni 5 righe o colonne senza la necessità di add-in o scripting. Questo approccio è particolarmente adatto per set di dati statici in cui devi semplicemente generare insiemi di medie per supportare la tua analisi. Tuttavia, fai attenzione a fare riferimento correttamente ai tuoi dati e gestire eventuali intervalli vuoti o irregolari.
Il seguente esempio dimostra come calcolare la media di ogni 5 righe in una colonna:
1. Inserisci la seguente formula nella prima cella in cui desideri il risultato (ad esempio, C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Qui, A2 è la cella iniziale della tua colonna di dati, C2 è la cella di output della formula e 5 è l'intervallo (numero di righe da mediare). Assicurati di regolare questi riferimenti in base al tuo set di dati effettivo.
Dopo aver inserito la formula, premi Invio. Il primo risultato medio verrà visualizzato. Vedi screenshot:
2. Seleziona la cella della formula, quindi trascina la maniglia di riempimento verso il basso finché non incontri un valore di errore (come #DIV/0! se ci sono meno di 5 valori nei dati rimanenti). Questo recupererà automaticamente le medie per ciascun gruppo di 5 righe. Vedi screenshot:
Suggerimenti e Note: Puoi utilizzare funzioni di gestione degli errori come SE.ERRORE() per sopprimere i valori di errore se i tuoi dati non si adattano perfettamente a gruppi di dimensione uguale, ad esempio:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Per calcolare la media ogni 5 colonne lungo una riga, applica la seguente formula (inseriscila in A3 e trascina attraverso):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Qui, A1 è la cella iniziale, A3 è la tua cella di output della formula e 5 è il numero di colonne in ciascun gruppo. Regola i riferimenti delle celle secondo necessità per il layout dei tuoi dati.
Dopo aver inserito la formula e premuto Invio, trascina la maniglia di riempimento verso destra fino a raggiungere un valore di errore. Vedi screenshot:
Questo metodo basato su formule è ideale per calcoli veloci una tantum o quando non vuoi usare strumenti aggiuntivi. Tuttavia, man mano che i tuoi dati cambiano in dimensione o forma, potresti dover modificare le formule o aggiornare manualmente gli intervalli di celle, e gestire gruppi incompleti potrebbe richiedere ulteriori accortezze.
Calcolare la media ogni 5 righe con Kutools per Excel
Kutools per Excel fornisce una soluzione grafica conveniente se hai spesso bisogno di mediare gruppi di righe senza gestire formule complesse. Attraverso le funzioni Inserisci interruzioni di pagina ogni riga e Statistiche pagina dati, puoi segmentare rapidamente i tuoi dati e calcolare medie in batch in pochi clic. Questo metodo è particolarmente utile se vuoi applicare medie su intervalli ripetibili e visualizzare direttamente il raggruppamento nel tuo foglio di lavoro.
Dopo aver scaricato e installato Kutools per Excel, segui questi passaggi:
1. Clicca Kutools Plus > Stampa > Inserisci interruzioni di pagina ogni riga. Vedi screenshot:
2. Nella finestra di dialogo Inserisci interruzioni di pagina ogni riga, specifica l'intervallo (ad esempio, 5) per inserire un'interruzione di pagina dopo ogni 5 righe. Questo permette a Kutools di segmentare automaticamente i tuoi dati. Vedi screenshot:
3. Successivamente, clicca Kutools Plus > Stampa > Statistiche pagina dati. Vedi screenshot:
4. Nella finestra di dialogo Statistiche pagina dati, seleziona la/e colonna/e che vuoi mediare, poi scegli Media come metodo di calcolo. Vedi screenshot:
5. Clicca OK, e Kutools inserirà istantaneamente righe di subtotali con valori medi a ogni intervallo di 5 righe. Vedi screenshot:
Scarica e prova gratuitamente Kutools per Excel Ora!
Kutools rende facile completare il raggruppamento e l'analisi dei dati ripetitivi senza dover modificare le formule o scrivere script. Tuttavia, tieni presente che le interruzioni di pagina inserite potrebbero influire sul layout di stampa e sulle viste, quindi potresti volerle rimuovere dopo l'uso se non sono necessarie per la tua relazione.
Calcolare la media ogni 5 righe o colonne con il codice VBA
Se hai bisogno di calcolare ripetutamente il valore medio per ogni numero fisso di righe o colonne attraverso grandi set di dati o dati in continua evoluzione, automatizzare questo processo con VBA può risparmiare una notevole quantità di lavoro manuale. VBA ti consente di scorrere i tuoi dati, raggrupparli come necessario e generare il risultato medio per ogni blocco. Questo metodo è particolarmente adatto per utenti avanzati o coloro che gestiscono blocchi di dati dinamici, ed evita che le formule ingombrino il tuo foglio di lavoro. Di seguito è riportata una macro VBA di uso generale che puoi facilmente adattare.
Automatizza la media ogni 5 righe:
1. Clicca Sviluppatore > Visual Basic per aprire la finestra Microsoft Visual Basic per Applicazioni. Poi, clicca Inserisci > Modulo e incolla il codice qui sotto nel modulo:
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. Per eseguire il codice, clicca il 
pulsante o premi F5. Seleziona il tuo intervallo di dati (una colonna), quindi scegli la cella iniziale per l'output e specifica la dimensione del gruppo (es., 5). La macro genererà la media per ogni set di 5 righe, una sotto l'altra nella colonna di output specificata.
Puoi utilizzare una macro simile per calcolare la media ogni 5 colonne lungo una riga.
Automatizza la media ogni 5 colonne:
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
Calcolare la media ogni 5 righe con la Tabella Pivot
Un altro modo pratico per calcolare le medie di gruppo ogni 5 righe è utilizzando una Tabella Pivot, combinandola con una colonna di numerazione sequenziale o indice per raggruppare i dati. Questo metodo è particolarmente utile per gli utenti che lavorano con dati strutturati, basati su tabelle, e hanno bisogno di un riepilogo rapido e interattivo senza scrivere formule o utilizzare add-in. Una Tabella Pivot gestisce dinamicamente le modifiche ai dati e supporta raggruppamenti flessibili—ideali per grandi set di dati o compiti di reporting regolari.
Ecco come eseguire questa operazione utilizzando una colonna di supporto e una Tabella Pivot:
1. Aggiungi una colonna "Indice" o "Gruppo" accanto ai tuoi dati per contrassegnare ciascun gruppo di 5 righe. Nella prima riga di dati (B2), inserisci:
=INT((ROW()-ROW($A$2))/5)+1Questa formula etichetta ciascuna riga in sequenza, assegnando lo stesso numero di gruppo a ogni 5 righe. Riempisci questa formula lungo il tuo set di dati.
2. Seleziona i tuoi dati e la nuova colonna Indice, poi clicca Inserisci > Tabella Pivot. Nella finestra di dialogo di creazione della Tabella Pivot, conferma l'intervallo dei dati e scegli dove posizionare la Tabella Pivot.
3. Nell'elenco dei campi della nuova Tabella Pivot, trascina il campo "Gruppo" nell'Righe e il tuo campo di valori (ad esempio, "Vendite") nell'Valori.
4. Clicca la freccia giù nell'area Valori, scegli Impostazioni campo valori e seleziona Media.
Ora, la tua Tabella Pivot mostra la media per ogni 5 righe dei dati originali, convenientemente raggruppate dalla colonna di supporto.
I vantaggi chiave del metodo della Tabella Pivot sono la sua flessibilità e la facilità di aggiornamento quando i tuoi dati di origine cambiano. Tuttavia, richiede l'aggiunta di una colonna di supporto e potrebbe non essere adatto a situazioni in cui i dati devono rimanere precisamente formattati o inalterati.
Articoli correlati:
Come calcolare la media degli ultimi 5 valori di una colonna man mano che nuovi numeri entrano?
Come calcolare la media dei primi o ultimi 3 valori in Excel?
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in