Download eksemplet på projektmappe
Denne vejledning viser, hvordan du summerer data, der svarer til bestemte ugenumre i Excel og Google Sheets.
Sum Hvis efter ugenummer
For at “summe hvis” efter ugenummer vil vi bruge SUMIFS -funktionen. Men først skal vi tilføje en hjælperkolonne, der indeholder WEEKNUM -funktionen.
Det Ugenummer hjælperkolonne beregnes ved hjælp af WEEKNUM -funktionen:
| 1 | = UGE (B3,1) |
Dernæst vil vi bruge SUMIFS -funktionen til at summere alt Salg der finder sted i en bestemt Ugenummer.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Sum Hvis efter ugenummer - Uden hjælpekolonne
Hjælperkolonnemetoden er let at følge, men du kan også replikere beregningen i en enkelt formel ved hjælp af SUMPRODUCT -funktionen i kombination med WEEKNUM -funktionen for at summere Samlet antal salg ved Ugenummer.
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
I dette eksempel kan vi bruge SUMPRODUCT -funktionen til at udføre komplicerede "sum if" -beregninger. Lad os gå igennem eksemplet ovenfor.
Dette er vores endelige formel:
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
For det første lister SUMPRODUCT -funktionen arrayet af værdier fra celleområderne:
| 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}) |
Derefter beregner WEEKNUM -funktionen Ugenummer af hver af Salgsdatoer.
WEEKNUM -funktionen er ikke designet til at arbejde med matrixværdier, så vi skal tilføje nul (“+0”) for at WEEKNUM kan behandle værdierne korrekt.
| 1 | = SUMPRODUKT (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Ugenummer værdier lig med 1 ændres til SAND værdier.
| 1 | = SUMPRODUCT (-({SAND; FALSK; FALSK; FALSK; FALSK; FALSK}), {4; 9; 1; 7; 6; 2; 5}) |
Dernæst konverterer de dobbelte bindestreger (-) værdierne SAND og FALSK til 1'er og 0'er:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
SUMPRODUCT -funktionen multiplicerer derefter hvert par af poster i arraysne for at producere en matrix af Antal salg der har en Ugenummer af 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
Endelig summeres tallene i arrayet sammen:
| 1 | =4 |
Denne formel gentages derefter for de andre mulige værdier af Ugenummer.
Flere detaljer om brug af boolske udsagn og kommandoen “-” i en SUMPRODUCT-funktion findes her.
Låsning af cellereferencer
For at gøre vores formler lettere at læse har vi vist formlerne uden låste cellereferencer:
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
Men disse formler fungerer ikke korrekt, når de kopieres og indsættes andre steder i din fil. I stedet skal du bruge låste cellereferencer som denne:
| 1 | = SUMPRODUCT (-(WEEKNUM ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Læs vores artikel om låsning af cellereferencer for at lære mere.
Sum hvis efter ugenummer i Google Sheets
Disse formler fungerer nøjagtig det samme i Google Sheets som i Excel.
WEEKNUM -funktionen er imidlertid mere fleksibel i Google Sheets end i Excel og accepterer array -input og output. Derfor er {Array} +0 operationen i WEEKNUM (B3: B9+0,1) formlen ikke påkrævet.
Hele SUMPRODUCT -formlen kan skrives i Google Sheets som:
| 1 | =SUMPRODUKT(--(UGE($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
