Come si calcola la media ogni 5 righe o colonne in Excel?
Quando si lavora con set di dati voluminosi in Excel, capita spesso di dover calcolare la media per ogni gruppo di righe o colonne — ad esempio, ogni 5 righe o ogni 5 colonne. Potresti inserire manualmente formule come =MEDIA(A1:A5), =MEDIA(A6:A10), =MEDIA(A11:A15) e così via, ma questa operazione diventa rapidamente impraticabile se l’elenco contiene centinaia o migliaia di celle. Ripetere manualmente questi passaggi richiede molto tempo ed è facilmente soggetto a errori. Fortunatamente, Excel offre diversi metodi per automatizzare questa attività, rendendo l’analisi dei dati molto più efficiente e meno noiosa. In questo articolo ti mostriamo approcci pratici per calcolare la media ogni 5 righe o colonne, tra cui soluzioni basate su formule, componenti aggiuntivi di Excel, automazione VBA e tecniche con Tabelle pivot, per aiutarti a scegliere la soluzione più adatta al tuo scenario.
Calcola la media ogni 5 righe o colonne con le formule
Calcola la media ogni 5 righe con Kutools per Excel
Calcola la media ogni 5 righe o colonne con codice VBA
Calcola la media ogni 5 righe con Tabella pivot
Calcola la media ogni 5 righe o colonne con le formule
Se preferisci utilizzare formule standard di Excel, puoi automatizzare il calcolo ogni 5 righe o colonne senza dover ricorrere a componenti aggiuntivi o script. Questo approccio si rivela particolarmente efficace per set di dati statici, nei quali devi semplicemente generare serie di medie per supportare la tua analisi. Tuttavia, è fondamentale assicurarsi di fare riferimento correttamente ai tuoi dati e gestire con cura eventuali intervalli vuoti o irregolari.
L'esempio seguente mostra come calcolare la media ogni 5 righe in una colonna:
1. Inserisci la seguente formula nella prima cella in cui vuoi visualizzare il risultato (ad esempio,)C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Qui, A2 è la cella iniziale della colonna dei dati, C2 è la cella di output della formula e 5 è l'intervallo (numero di righe da mediare). Assicurati di adattare questi riferimenti al tuo set di dati effettivo.
Dopo aver inserito la formula, premi Invio. Verrà visualizzato il primo risultato mediato. Vedi screenshot:
2.Seleziona la cella con la formula, quindi trascina il quadratino di riempimento verso il basso finché non compare un valore di errore (ad esempio,)#DIV/0! se nei dati rimanenti ci sono meno di 5 valori). In questo modo otterrai automaticamente la media per ogni gruppo di 5 righe. Vedi screenshot:
Suggerimenti e note:Puoi usare funzioni di gestione degli errori come SE.ERRORE()per nascondere i valori di errore quando i tuoi dati non si suddividono perfettamente in gruppi completi, 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)A3e trascinala verso destra):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Qui, A1 è la cella iniziale, A3 è la cella di output della formula e 5 è il numero di colonne in ciascun gruppo. Adatta i riferimenti alle celle in base alla struttura dei tuoi dati.
Dopo aver inserito la formula e premuto Invio, trascina il quadratino di riempimento verso destra fino a quando non compare un valore di errore. Vedi screenshot:
Questo metodo basato su formule è perfetto per calcoli rapidi e occasionali o quando preferisci evitare strumenti aggiuntivi. Tuttavia, se i tuoi dati cambiano dimensione o struttura, potresti dover aggiornare manualmente formule o intervalli di celle, e gestire gruppi incompleti potrebbe richiedere maggiore attenzione.
Calcola la media ogni 5 righe con Kutools per Excel
Kutools per Excel offre una pratica soluzione grafica se devi spesso calcolare medie per gruppi di righe senza dover gestire formule complesse. Grazie alle funzioni Inserisci interruzioni di pagina ogni altra riga e Statistiche pagina dati, puoi segmentare rapidamente i tuoi dati e calcolare medie in blocco con pochi clic. Questo metodo è particolarmente utile quando desideri applicare medie su intervalli ripetibili e visualizzare direttamente nel foglio di lavoro la suddivisione in gruppi.
Dopo aver scaricato e installato Kutools per Excel, segui questi passaggi:
1. Fai clic su KUTOOLS PLUS > Stampa > Inserisci interruzioni di pagina ogni altra riga. Vedi screenshot:
2. Nella finestra di dialogo Inserisci interruzioni di pagina ogni altra riga, specifica l'intervallo (ad esempio,)5) per inserire un'interruzione di pagina dopo ogni 5 righe. In questo modo, Kutools segmenta automaticamente i tuoi dati. Vedi screenshot:
3. Fai quindi clic su KUTOOLS PLUS > Stampa > Statistiche pagina dati. Vedi screenshot:
4. Nella finestra di dialogo Statistiche pagina dati, seleziona la o le colonne di cui vuoi calcolare la media, quindi scegli Media come metodo di calcolo. Vedi screenshot:
5. Fai clic su OK e Kutools inserirà immediatamente righe di subtotale con i valori medi ogni 5 righe. Vedi screenshot:
Scarica e prova gratuitamente Kutools per Excel oggi stesso!
Kutools semplifica notevolmente il raggruppamento ripetitivo dei dati e l’analisi, senza dover modificare formule o scrivere script. Tuttavia, tieni presente che gli interruzioni di pagina inserite potrebbero influire sul layout di stampa e sulla visualizzazione, quindi potresti volerle rimuovere dopo l’uso se non ti servono per la tua relazione.
Calcola la media ogni 5 righe o colonne con codice VBA
Se devi calcolare ripetutamente la media su un numero fisso di righe o colonne all’interno di set di dati ampi o in continua evoluzione, automatizzare il processo con VBA ti farà risparmiare una notevole quantità di lavoro manuale. Grazie a VBA, puoi scorrere i tuoi dati, raggrupparli secondo le tue esigenze e ottenere la media per ciascun blocco in modo rapido ed efficiente. Questo approccio è ideale per utenti avanzati o per chi gestisce dati dinamici, consentendo di evitare formule che appesantiscono il foglio di lavoro. Di seguito trovi una macro VBA generica, pronta per essere facilmente personalizzata.
Automatizza il calcolo della media ogni 5 righe:
1. Fai clic su Sviluppo > Visual Basic per aprire la finestra di Microsoft Visual Basic, Applications. Quindi, fai clic su Inserisci > Modulo e incolla il codice seguente 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, fai clic sul pulsante 
oppure premi F5. Seleziona il tuo intervallo di dati (una singola colonna), indica la cella iniziale per l’output e specifica la dimensione del gruppo (ad esempio, 5). La macro calcolerà e restituirà la media di ogni gruppo di 5 righe, posizionandole una sotto l’altra nella colonna di output indicata.
Puoi utilizzare una macro analoga per calcolare la media ogni 5 colonne lungo una riga.
Automatizza il calcolo della 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
Calcola la media ogni 5 righe con Tabella pivot
Un altro modo pratico per calcolare le medie di gruppo ogni 5 righe consiste nell’utilizzare una tabella pivot, abbinandola a una colonna di numeri sequenziali o a un indice per raggruppare i dati. Questo approccio è ideale per chi lavora con tabelle strutturate e desidera un riepilogo rapido e interattivo, senza dover ricorrere a formule complesse o componenti aggiuntivi. La tabella pivot si adatta dinamicamente alle modifiche dei dati e permette raggruppamenti flessibili, perfetti per set di dati voluminosi o per report periodici.
Ecco come eseguire questa operazione usando una colonna di appoggio e una Tabella pivot:
1.Aggiungi una colonna «Indice» o «Gruppo» accanto ai tuoi dati per contrassegnare ogni gruppo di 5 righe. Nella prima riga di dati ()B2), inserisci:
=INT((ROW()-ROW($A$2))/5)+1Questa formula etichetta ogni riga in sequenza, assegnando lo stesso numero di gruppo a ogni blocco di 5 righe. Copiala in basso, accanto al tuo set di dati.
2. Seleziona i tuoi dati e la nuova colonna Indice, quindi fai clic su Inserisci > Tabella pivot. Nella finestra di creazione della tabella pivot, conferma l’intervallo dati e scegli dove posizionare la tabella pivot.
3.Nell’elenco dei campi della nuova Tabella pivot, trascina il campo «Gruppo» nell’area Righee il tuo campo valori (ad esempio, «Vendite») nell’area Valori.
4. Fai clic sul menu a discesa nell’area Valori, scegli Valore Impostazioni campo e seleziona Media.
Ora la tua tabella pivot mostra la media ogni 5 righe dei dati originali, comodamente raggruppati grazie alla colonna di supporto.
I principali vantaggi del metodo 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 nei casi in cui i dati debbano mantenere un formato preciso o rimanere inalterati.
Articoli correlati:
Come si calcola la media dei 3 valori più alti o più bassi in Excel?
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 oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi