Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du summerer data som tilsvarer spesifikke uketall i Excel og Google Sheets.
Sum Hvis etter ukenummer
For å "summe om" etter ukenummer, bruker vi SUMIFS -funksjonen. Men først må vi legge til en hjelperkolonne som inneholder WEEKNUM -funksjonen.
De Ukenummer hjelperkolonne beregnes ved hjelp av WEEKNUM -funksjonen:
| 1 | = UKEN (B3,1) |
Deretter bruker vi SUMIFS -funksjonen for å summere alt Salg som finner sted i en bestemt Ukenummer.
| 1 | = SUMMER (D3: D9, C3: C9, F3) |
Sum Hvis etter ukenummer - Uten hjelperkolonne
Hjelpekolonnemetoden er enkel å følge, men du kan også replikere beregningen i en enkelt formel ved å bruke SUMPRODUCT -funksjonen i kombinasjon med WEEKNUM -funksjonen for å summere Totalt antall salg av Ukenummer.
| 1 | = SUMPRODUCT (-(UKEN (B3: B9+0,1) = E3), C3: C9) |
I dette eksemplet kan vi bruke SUMPRODUCT -funksjonen til å utføre kompliserte "sum if" -beregninger. La oss gå gjennom eksemplet ovenfor.
Dette er vår siste formel:
| 1 | = SUMPRODUCT (-(UKEN (B3: B9+0,1) = E3), C3: C9) |
For det første viser SUMPRODUCT -funksjonen en rekke verdier fra celleområdene:
| 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}) |
WEEKNUM -funksjonen beregner deretter Ukenummer av hver av Salgsdatoer.
WEEKNUM -funksjonen er ikke designet for å fungere med matrisverdier, så vi må legge til null (“+0”) for at WEEKNUM skal kunne behandle verdiene riktig.
| 1 | = SUMPRODUCT (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Ukenummer verdier lik 1 endres til TRUE verdier.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Deretter konverterer de doble bindestrekene (-) de Sanne og FALSKE verdiene til 1 og 0:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
SUMPRODUCT -funksjonen multipliserer deretter hvert oppføringspar i matrisene for å produsere en rekke Antall salg som har en Ukenummer av 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
Til slutt summeres tallene i matrisen sammen:
| 1 | =4 |
Denne formelen gjentas deretter for de andre mulige verdiene av Ukenummer.
Flere detaljer om bruk av boolske setninger og kommandoen “-” i en SUMPRODUCT-funksjon finner du her.
Låsing av cellereferanser
For å gjøre formlene våre lettere å lese, har vi vist formlene uten låste cellereferanser:
| 1 | = SUMPRODUCT (-(UKEN (B3: B9+0,1) = E3), C3: C9) |
Men disse formlene fungerer ikke som de skal når de kopieres og limes inn andre steder i filen. I stedet bør du bruke låste cellereferanser slik:
| 1 | = SUMPRODUCT (-(UKEN ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Les artikkelen vår om låsing av cellereferanser for å lære mer.
Sum hvis etter ukenummer i Google Regneark
Disse formlene fungerer nøyaktig det samme i Google Regneark som i Excel.
WEEKNUM -funksjonen er imidlertid mer fleksibel i Google Regneark enn i Excel, og godtar matrisinnganger og -utganger. Derfor er ikke operasjonen {Array} +0 i WEEKNUM (B3: B9+0,1) formelen nødvendig.
Hele SUMPRODUCT -formelen kan skrives i Google Regneark som:
| 1 | =SUMPRODUKT(--(UKEN($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
