Hoe wekelijkse gemiddelden berekenen in Excel?

Stel dat u een aankooptabel heeft die lijkt op de onderstaande schermafbeelding. Dit voorbeeld illustreert gedetailleerd hoe u wekelijkse gemiddelden kunt berekenen met behulp van hulptabellen en ingebouwde functies in Excel. Deze methode is geschikt voor de meeste gebruikers die stapsgewijze handmatige bewerkingen prefereren en volledige zichtbaarheid willen hebben in het berekeningsproces.

Stap 1: Voeg een hulptabel toe om elke rij te labelen met zijn weeknummer:

Voer WeekNUM in Cel D1, en voer vervolgens in Cel D2 de formule =WEEKNUM(A2,2)in. (Hier verwijst A2 naar uw aankoopdatum in de Datum/Tijd-kolom. Het tweede argument 2 vertelt Excel om weken te beschouwen als startend op maandag, wat past bij de meeste zakelijke scenario's. Als uw week begint op zondag, kunt u 1 gebruiken). Sleep vervolgens de vulgreep omlaag om de weeknummers voor het hele databereik in te vullen. Nu ziet u welke week elke rij behoort.

Stap 2: Deze stap stelt u in staat om gegevens per week te groeperen, maar u wilt mogelijk identieke weken uit verschillende jaren onderscheiden. Voeg in dit geval een extra hulptabel toe voor het jaar:

Voer Jaar in Cel E1. Typ in Cel E2 =JAAR(A2) (opnieuw, A2 is uw aankoopdatumcel). Sleep de vulgreep omlaag, en uw gegevens bevatten nu week- en jaarkolommen voor nauwkeuriger groepering.

Stap 3: Bereken nu het wekelijkse gemiddelde. Voer in Cel F1 Gemiddeldein. Typ in Cel F2 de volgende formule: =ALS(EN(D2=D1,E2=E1),"",GEMIDDELDE.ALS($C$2:$C$39,$D$2:$D$39,D2,$E$2:$E$39,E2)). Sleep daarna de vulgreep omlaag door het beoogde bereik.

Deze formule berekent het gemiddelde voor bedragen in dezelfde week van hetzelfde jaar, en toont alleen het resultaat bij de eerste instantie van elke unieke combinatie (week, jaar) (andere rijen in dezelfde groep tonen blanco).

Opmerkingen:
(1) Als er meerdere invoer voor één week in hetzelfde jaar verschijnen, wordt het gemiddelde alleen weergegeven in de eerste overeenkomstige rij; andere rijen blijven blanco voor duidelijkheid.
(2) D1 en D2 verwijzen naar de weeknummerkolommen, E1 en E2 verwijzen naar de jaarkolommen, $C$2:$C$39 is het bereik met bedragwaarden die u wilt middelen, $D$2:$D$39 is de weeknummerkolom, $E$2:$E$39 is de jaarkolom; pas deze bereiken en verwijzingen aan naar gelang nodig voor uw dataset.
(3) Als u geen rekening hoeft te houden met jaren en alleen wilt middelen op basis van weeknummers, gebruik dan in F2: =GEMIDDELDE.ALS($D$2:$D$39,D2,$C$2:$C$39) en vul naar beneden. Dit geeft het eenvoudige wekelijkse gemiddelde zonder onderscheid tussen jaren. Voorbeeldresultaat hieronder weergegeven:

Tip: Voor zeer grote datasets kan het kopiëren van formules vertragen in Excel. In dergelijke gevallen raadpleegt u de VBA-automatisering of de draaitabelbenadering hieronder voor een meer schaalbare oplossing.

Als u #DEEL/0! foutmeldingen ontvangt, betekent dit meestal dat er geen overeenkomende invoer voor die week in uw dataset zijn. Zorg ervoor dat uw hulptabellen en bedragkolommen geen lege cellen of niet-overeenkomende gegevenstypen bevatten.

Deze methode introduceert Kutools voor Excel’s Geavanceerd samenvoegen van rijen hulpprogramma's, waardoor het gemakkelijk wordt om alle wekelijkse gemiddelden in bulk te berekenen zonder handmatig formules in te voeren. Kutools vereenvoudigt complexe groeperings- en gemiddelde taken, bespaart tijd en vermindert handmatige fouten - vooral bij het werken met middelgrote tot grote lijsten met herhalende groepering en berekeningen.

1. Voer in een hulptabel Week in Cel D1. Typ vervolgens in D2 =WEEKNUM(A2,2) (A2 = datumcel). Vul naar beneden om elke record te taggen met zijn weeknummer. Deze kolom stelt Kutools in staat om records per week te groeperen.

2. Selecteer de tabel die de nieuwe Week-kolom bevat, klik vervolgens op Kutools > Tekst > Geavanceerd samenvoegen van rijen. Aangezien Kutools werkt op geselecteerde bereiken, zorg ervoor dat uw selectie de relevante gegevens dekt, inclusief de kolommen om samen te voegen en de weekelijkse groeperingskolom.

3. Stel in het geopende dialoogvenster Rijen combineren op basis van kolom de volgende bewerkingen in:
(1) Klik op de Fruit-kolom en stel deze in voor Samenvoegen (met komma als scheidingsteken) om fruitnamen per week bij elkaar te houden.
(2) Voor de Bedrag-kolom, stel de bewerking in op Berekenen > Gemiddelde zodat het automatisch het wekelijkse gemiddelde geeft.
(3) Wijs de Week-kolom toe als de Hoofdsleutel zodat de groepering gebaseerd is op weken.
(4) Klik Ok om te verwerken.

Kutools zal snel alle records per week groeperen, alle gerelateerde fruitsoorten weergeven en het gemiddelde tonen in een nette nieuwe tabel, zoals hieronder weergegeven:

Voordelen: Snelle verwerking voor grote tabellen, minimale handmatige fouten en duidelijke output. Opmerkingen: Zorg ervoor dat uw hulptabellen correct zijn voordat u begint, omdat fouten daarvan de resultaten zullen beïnvloeden. Als weeknummers voor invoer in meerdere jaren worden herhaald, moet u mogelijk een Jaar-hulptabel toevoegen en groeperen op basis van zowel Week als Jaar voor nauwkeurigheid.

De Draaitabelmethode maakt gebruik van Excel's interactieve samenvattingsmogelijkheden om uw gegevens per week te groeperen en snel wekelijkse gemiddelden te berekenen. Deze aanpak is het beste voor gebruikers die visueel slepen-en-neerzetten prefereren, flexibele data-exploratie en gemakkelijk bijwerken van resultaten wanneer uw brongegevens veranderen. Het is praktisch voor grote datasets en vermijdt handmatige formules.

Stap1: Voeg een hulptabel toe aan uw tabel voor het weeknummer. Typ in de eerste cel van een lege kolom (bijvoorbeeld D2): =WEEKNUM(A2,2), verwijzend naar uw aankoopdatum. Vul deze naar beneden om elke rij te taggen met zijn week van het jaar.

Stap2: Met uw hele tabel geselecteerd (inclusief de nieuwe Week-kolom), ga naar Invoegen > Draaitabel. Bevestig in het dialoogvenster uw bereik en kies een nieuwe of bestaande werkblad voor plaatsing.

Stap3: In het deelvenster Draaitabelveldenlijst:

• Sleep de Week-kolom naar het Rijgebied.
• Optioneel, om gegevens per jaar te scheiden, sleep ook een Jaar-kolom (gemaakt met =JAAR(A2)) naar Rijen boven Week.
• Sleep de Bedrag-kolom naar het Waardengebied. Klik op de vervolgkeuzelijst > Waardeveldinstellingen en stel in op Gemiddelde.

De Draaitabel zal nu automatisch het gemiddelde bedrag voor elke week of (jaar, week) groep weergeven. U kunt de tabel op elk moment vernieuwen nadat u nieuwe gegevens hebt toegevoegd om de resultaten direct bij te werken. Deze methode is vooral flexibel voor het verkennen van andere samenvattingsstatistieken of opnieuw groeperen op basis van verschillende tijdsperiodes indien nodig.

Tips: Als uw gegevens meer dan één jaar beslaan, voeg altijd zowel Jaar als Week-velden toe om te voorkomen dat dezelfde week uit verschillende jaren wordt gecombineerd. Als u datumbereiken voor weken wilt presenteren, kunt u een berekende hulptabel toevoegen die de startdatum van elke week vermeldt voor duidelijkheid. Als u lege rijen of onverwachte resultaten ziet, controleer dan of uw hulptabellen geen gaten bevatten en dat uw tabel correct is opgemaakt als een Excels-tabel (Ctrl+T) voor de beste resultaten.

Voordelen: Geen handmatige formules, dynamisch bijwerken naarmate gegevens veranderen, ondersteunt grote datasets soepel. Beperkingen: Vereist een hulptabel voor weeknummer; originele datums moeten in een herkend datumformaat zijn.

Gemiddelde per jaar/maand/datum in Excel

Gemiddelde tijdstempels per dag in Excel

Gemiddelde per dag/maand/kwartaal/uur met draaitabel in Excel