Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u gegevens kunt optellen die overeenkomen met specifieke weeknummers in Excel en Google Spreadsheets.
Som indien op weeknummer
Om te "optellen als" op weeknummer, gebruiken we de SOMMEN-functie. Maar eerst moeten we een helperkolom toevoegen die de WEEKNUMMER-functie bevat.
De Weeknummer helperkolom wordt berekend met behulp van de WEEKNUMMER-functie:
| 1 | =WEEKNUMMER(B3,1) |
Vervolgens zullen we de SOMMEN-functie gebruiken om alles op te tellen verkoop die plaatsvinden in een specifieke Weeknummer.
| 1 | =SOMMEN.(D3:D9,C3:C9,F3) |
Som indien op weeknummer - zonder helperkolom
De helperkolommethode is gemakkelijk te volgen, maar u kunt de berekening ook repliceren in een enkele formule met behulp van de SOMPRODUCT-functie in combinatie met de WEEKNUMMER-functie om de Totaal aantal verkopen door Weeknummer.
| 1 | =SOMPRODUCT(--(WEEKNUMMER(B3:B9+0,1)=E3),C3:C9) |
In dit voorbeeld kunnen we de SOMPRODUCT-functie gebruiken om gecompliceerde 'som als'-berekeningen uit te voeren. Laten we het bovenstaande voorbeeld doornemen.
Dit is onze definitieve formule:
| 1 | =SOMPRODUCT(--(WEEKNUMMER(B3:B9+0,1)=E3),C3:C9) |
Ten eerste geeft de SOMPRODUCT-functie een lijst met waarden uit de celbereiken:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Vervolgens berekent de WEEKNUMMER-functie de Weeknummer van elk van de Verkoopdata.
De WEEKNUMMER-functie is niet ontworpen om met matrixwaarden te werken, dus we moeten nul ("+0") toevoegen voor WEEKNUMMER om de waarden correct te verwerken.
| 1 | =SOMPRODUCT(--({1; 2; 2; 3; 3; 3; 4}=1), {4; 9; 1; 7; 6; 2; 5}) |
Weeknummer waarden gelijk aan 1 worden gewijzigd in TRUE-waarden.
| 1 | =SOMPRODUCT(--({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Vervolgens zetten de dubbele streepjes (-) de TRUE en FALSE-waarden om in 1s en 0s:
| 1 | =SOMPRODUCT({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
De SOMPRODUCT-functie vermenigvuldigt vervolgens elk paar vermeldingen in de arrays om een array te produceren van Aantal verkopen die hebben een Weeknummer van 1:
| 1 | =SOMPRODUCT({4; 0; 0; 0; 0; 0; 0}) |
Ten slotte worden de getallen in de array bij elkaar opgeteld:
| 1 | =4 |
Deze formule wordt dan herhaald voor de andere mogelijke waarden van Weeknummer.
Meer details over het gebruik van Booleaanse instructies en het “-” commando in een SUMPRODUCT-functie vindt u hier.
Celverwijzingen vergrendelen
Om onze formules leesbaarder te maken, hebben we de formules weergegeven zonder vergrendelde celverwijzingen:
| 1 | =SOMPRODUCT(--(WEEKNUMMER(B3:B9+0,1)=E3),C3:C9) |
Maar deze formules werken niet goed wanneer ze ergens anders in uw bestand worden gekopieerd en geplakt. Gebruik in plaats daarvan vergrendelde celverwijzingen zoals deze:
| 1 | =SOMPRODUCT(--(WEEKNUMMER($B$3:$B$9+0,1)=E3),$C$3:$C$9) |
Lees ons artikel over het vergrendelen van celreferenties voor meer informatie.
Som als op weeknummer in Google Spreadsheets
Deze formules werken in Google Spreadsheets precies hetzelfde als in Excel.
De WEEKNUMMER-functie is echter flexibeler in Google Spreadsheets dan in Excel en accepteert array-invoer en -uitvoer. Daarom is de bewerking {Array}+0 in de formule WEEKNUMMER(B3:B9+0,1) niet vereist.
De volledige SUMPRODUCT-formule kan in Google Spreadsheets worden geschreven als:
| 1 | =SOMPRODUCT(--(WEEKNUMMER($B$3:$B$9+0,1)=E3),$C$3:$C$9) |
