Come calcolare la media in base al giorno della settimana in Excel?
In Excel capita spesso di dover calcolare la media di un elenco di numeri in base al giorno della settimana associato a ciascuna voce. Ad esempio, potresti voler analizzare i dati di vendita per trovare la media degli ordini del lunedì, nei giorni feriali o nei weekend. Questa esigenza è comune nei report di vendita, nel monitoraggio delle prestazioni o in qualsiasi contesto che richieda un’analisi basata sul tempo. Di seguito trovi diverse soluzioni pratiche per calcolare la media dei numeri in base al giorno della settimana, utilizzando formule, codice VBA e tabelle pivot. Questi approcci ti permettono di ottenere insight utili e comprendere meglio la distribuzione dei tuoi dati secondo i modelli calendariali.
↳ Giorno specifico della settimana (ad esempio, lunedì)
↳ Giorni feriali (lun–ven)
↳ Fine settimana (sab–dom)
➤ Automatizza il calcolo della media in base a Giorno della settimana con macro VBA
➤ Tabella pivot: raggruppa e calcola la media per giorno della settimana senza formule
Calcolare la media in base a Giorno della settimana con formule
Calcolare la media in base a uno specifico Giorno della settimana
Per calcolare la media dei valori associati a una particolare giornata della settimana, ad esempio tutti i lunedì, puoi sfruttare le formule matriciali di Excel o la funzione SOMMA.PRODOTTO. Questo approccio è ideale per sintetizzare le tendenze giornaliere e individuare modelli ricorrenti. Ad esempio, se desideri calcolare la media degli ordini relativi esclusivamente ai lunedì presenti nel tuo set di dati, segui questo metodo:
Inserire la seguente formula in una cella vuota:
=AVERAGE(IF(WEEKDAY(D2:D15)=2,E2:E15))Quindi premere contemporaneamente Ctrl + Maiusc + Invio. Questo indica che la formula è una formula matrice, consentendo a Excel di elaborare ogni riga singolarmente e ottenere il risultato corretto.
Note e spiegazioni:
- D2:D15 è l'elenco delle date. Assicurati che contenga valori di data validi di Excel.
- 2 indica il lunedì. I numeri per la settimana sono: domenica = 1, lunedì = 2, martedì = 3, mercoledì = 4, giovedì = 5, venerdì = 6, sabato = 7.
- E2:E15 è l'intervallo di numeri di cui si desidera calcolare la media, ad esempio quantità di ordini, vendite o metriche simili.
Suggerimenti:
- Se la versione di Excel in uso supporta le formule dinamiche (Office 365 o versioni successive), puoi inserire direttamente la formula senza dover premere Ctrl + Maiusc + Invio.
- Verifica la presenza di celle vuote o prive di date per evitare errori nella formula.
Come approccio alternativo e più flessibile, è possibile utilizzare SOMMA.PRODOTTO per ottenere lo stesso risultato. Questo metodo non richiede l’inserimento come formula matrice ed è ideale per set di dati di grandi dimensioni:
=SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*1)Dopo aver inserito questa formula in una cella, premere Invio. In questo caso, D2:D15 rappresenta il proprio intervallo di date, E2:E15 il proprio intervallo dati e 1 indica il lunedì (quando il secondo argomento della funzione WEEKDAY è 2, in modo che lunedì = 1, martedì = 2, ..., domenica = 7).
Calcolare la media in base ai giorni feriali
Per calcolare il valore medio relativo ai giorni feriali (dal lunedì al venerdì) nei propri dati, utilizzare la seguente formula matrice:
=AVERAGE(IF(WEEKDAY(D2:D15,2)={1,2,3,4,5},E2:E15))Inserisci questa formula in una cella vuota, quindi conferma con Ctrl + Maiusc + Invio.
Note e spiegazioni:
- Questa formula calcola la media esclusivamente per le righe in cui la data corrisponde a un giorno lavorativo, ovvero dal lunedì al venerdì.
- Assicurati che i valori data in D2:D15 siano validi; in caso contrario, la funzione WEEKDAY potrebbe non restituire i risultati attesi.
Suggerimenti:
- Se si preferisce evitare l’inserimento come formula matrice, utilizzare l’alternativa con SOMMA.PRODOTTO riportata di seguito.
- Verifica che la colonna delle date contenga effettive date di Excel (non testo).
Un altro modo per ottenere lo stesso risultato consiste nell’utilizzare una formula SOMMA.PRODOTTO:
=SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*1)Basta digitare questa formula e premere Invio. La formula calcolerà automaticamente la media dei valori relativi ai giorni della settimana con numero inferiore a 6, ovvero dal lunedì al venerdì.
Calcolare la media in base ai fine settimana
Per calcolare la media esclusivamente dei valori relativi al weekend (sabato e domenica), utilizzare la seguente formula matrice:
=AVERAGE(IF(WEEKDAY(D2:D15,2)={6,7},E2:E15))Inseriscila in una cella vuota e conferma con Ctrl + Maiusc + Invio.
Note e spiegazioni:
- Questa formula seleziona le date per cui la funzione WEEKDAY restituisce 6 o 7 (sabato o domenica) quando il secondo argomento è 2.
Suggerimenti:
- Per set di dati di grandi dimensioni, l'alternativa con SOMMA.PRODOTTO indicata di seguito può risultare più rapida ed evita l’inserimento come formula matrice.
- Verificare che i valori come righe vuote o date non valide siano gestiti correttamente per evitare medie impreviste.
Un'opzione più rapida con SOMMA.PRODOTTO, che funziona senza inserimento come formula matrice:
=SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*1)Come sempre, assicurati di verificare i valori delle date e controllare che non ci siano celle vuote per garantire risultati accurati.
Codice VBA – Automatizzare il calcolo della media in base a Giorno della settimana con una macro
Per gli utenti che cercano un approccio totalmente automatizzato, dispongono di dataset di grandi dimensioni o richiedono aggiornamenti frequenti, è possibile utilizzare VBA per analizzare i dati, raggruppare le voci per giorno della settimana e calcolare la media di ciascun giorno. Questo metodo è perfetto per chi desidera evitare aggiustamenti manuali delle formule e ottenere in modo rapido un riepilogo settimanale.
Vantaggi: Elimina i passaggi manuali, genera un riepilogo completo e può essere personalizzato per ulteriori elaborazioni.
Svantaggi: Richiede l'abilitazione delle macro e una conoscenza di base di VBA; potrebbe non essere adatto a fogli di lavoro altamente dinamici o basati sul cloud.
Procedura:
1. Fare clic su Strumenti sviluppatore > Visual Basic per aprire l'editor VBA. Nella finestra, scegliere Inserisci > Modulo, quindi incollare il seguente codice nel nuovo modulo:
Sub AverageOrdersByWeekday()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim cell As Range
Dim ws As Worksheet
Dim datesRange As Range, valuesRange As Range
Dim i As Long, dayKey As String
Dim sumArr(1 To 7) As Double
Dim countArr(1 To 7) As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
Set datesRange = Application.InputBox("Select the date range", xTitleId, Selection.Address, Type:=8)
Set valuesRange = Application.InputBox("Select the corresponding values range", xTitleId, "", Type:=8)
For i = 1 To datesRange.Count
If IsDate(datesRange.Cells(i).Value) Then
Dim wd As Integer
wd = Weekday(datesRange.Cells(i).Value, 2)
sumArr(wd) = sumArr(wd) + valuesRange.Cells(i).Value
countArr(wd) = countArr(wd) + 1
End If
Next i
Dim resWs As Worksheet
Set resWs = Worksheets.Add
resWs.Name = "Weekday Averages"
resWs.Cells(1, 1).Value = "Weekday"
resWs.Cells(1, 2).Value = "Average"
Dim dayNames As Variant
dayNames = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
For i = 1 To 7
resWs.Cells(i + 1, 1).Value = dayNames(i - 1)
If countArr(i) > 0 Then
resWs.Cells(i + 1, 2).Value = sumArr(i) / countArr(i)
Else
resWs.Cells(i + 1, 2).Value = "No data"
End If
Next i
End Sub2. Per eseguire la macro, fai clic sul pulsante 
oppure premi F5. Si aprirà una finestra di dialogo in cui selezionare l’intervallo di date (ad esempio, D2:D15) e il relativo intervallo dei valori (ad esempio, E2:E15).
La macro crea un nuovo foglio di lavoro che riepiloga la media per ciascun giorno della settimana. Verrà calcolata la «Media» dal lunedì alla domenica; se un determinato giorno non dispone di dati corrispondenti, verrà visualizzato «Nessun dato».
Precauzioni e suggerimenti:
- Assicurati che entrambi gli intervalli—date e valori—abbiano la stessa lunghezza e siano perfettamente allineati riga per riga.
- Le macro richiedono il salvataggio della cartella di lavoro come file abilitato alle macro (*.xlsm).
- In caso di errore, assicurati che gli intervalli non contengano voci vuote o non valide.
- È possibile modificare il codice per applicare un filtro relativo a giorni specifici della settimana o per espandere ulteriormente il riepilogo.
Tabella pivot – Utilizzare Tabella pivot per raggruppare le date per giorno della settimana e calcolare le medie senza formule
Un altro metodo per analizzare e calcolare la media dei dati in base alla settimana consiste nell’utilizzare una tabella pivot. Questo approccio è intuitivo e non richiede né formule manuali né codifica. Le tabelle pivot consentono di raggruppare dinamicamente i dati, calcolarne la media e aggiornare rapidamente i risultati al variare dei dati sottostanti.
Vantaggi: Configurazione rapida, compatibilità con set di dati di grandi dimensioni, aggiornamento automatico all’aggiunta di nuovi dati e supporto per analisi avanzate (ad esempio, filtri e ordinamenti).
Svantaggi: Richiede che i dati siano organizzati in una tabella Excel o in un intervallo strutturato e offre personalizzazioni più limitate rispetto alle soluzioni VBA.
Passaggi operativi:
1.Aggiungere una colonna di supporto per i nomi dei giorni della settimana:
In una colonna vuota (ad esempio,)F), digitare in F2:
=TEXT(D2,"dddd")Copia la formula qui sotto per adattarla alle tue righe di dati. (Presuppone che le date siano in)D2:D15.)
2.Seleziona il tuo intervallo di origine, inclusa la colonna helper (ad esempio,)D2:F15). Per risultati ottimali, convertilo in una tabella Excel (Ctrl+T), quindi mantieni attiva la selezione.
3. Vai su Inserisci > Tabella pivot. Nella finestra di dialogo Crea tabella pivot, scegli dove posizionarla (è consigliato un nuovo foglio di lavoro) e fai clic su OK.
4.Nel riquadro Campi tabella pivot:
— Trascina il campo helper Giorno della settimana (colonna F) nell'area Righe.
— Trascina il tuo campo numerico (ad esempio,)Ordini dalla colonna E) nell'area Valori.
5. Modifica l'aggregazione in media:
Fai clic sul menu a discesa nell'area Valori > Impostazioni campo > scegli Media > OK.
6. (Opzionale) Ordina i giorni della settimana da lunedì a domenica:
Fai clic con il tasto destro su qualsiasi etichetta del giorno > Ordina > Altre opzioni di ordinamento, oppure aggiungi un piccolo helper personalizzato per l’ordinamento (da 1 a 7) e ordinalo in base a questo. Puoi anche formattare i numeri tramite Impostazioni campo Impostazioni campo > Formato numero.
7. Aggiorna quando i dati cambiano:
Dopo aver aggiornato la tabella sorgente, fai clic in un punto qualsiasi della tabella pivot e scegli Aggiorna(oppure)Dati > Aggiorna tutto).
Suggerimenti e risoluzione problemi:
- Assicurati che la colonna delle date contenga date valide di Excel (non testo); in caso contrario, la formula del giorno della settimana potrebbe non funzionare correttamente.
- Se le medie sembrano errate, verificare che il campo Valori sia impostato su Media e non su Somma.
- Dopo aver apportato modifiche o aver aggiunto una riga alla tabella sorgente, utilizza Aggiorna per consentire alla tabella pivot di ricalcolare i dati.
- Per le impostazioni locali che utilizzano il punto e virgola, inserisci
=TEXT(D2;"dddd")invece della virgola.
Utilizzare una tabella pivot per l’analisi per giorno della settimana semplifica il processo e ti permette di creare report interattivi, ideali per presentazioni o per condividere informazioni con altri.
Sblocca la magia di Excel con KUTOOLS AI
- Esecuzione intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici, il tutto con comandi semplici e intuitivi.
- Formule personalizzate: crea formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA in modo semplice e immediato.
- Interpretazione delle formule: Comprendi con facilità anche le formule più complesse.
- Traduzione del testo: Superate le barriere linguistiche direttamente nei vostri fogli di calcolo.
Articoli correlati:
Come si calcola la media tra due date in Excel?
Come calcolare la media delle celle in base a più criteri in Excel?
Come si calcola la media dei primi o degli ultimi 3 valori 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